Field List from a Stored procedure

I am writing a utility that creates Java code to access a database. I am looking for a way to get a list of fields and types that are returned by an sproc. Is there any easy way to get this from the master? Do you need to parse the SQL? This list would be like what Visual Studio.NET shows, or interdev if I remember correctly.

Thanks,

Larrynot sure what exactly you looking for. try sp_helptext sproc_name|||I am looking for a way to get a list of the columns that will be in the recordset returned by a stored procedure. I need column name and type information.

sp_helptext seems to return the full contents of the sproc, which would require that I parse this, and any linked procedures, to get the info I am looking for. This is what I am trying to avoid.|||Do your procedures do SELECT only? You can use sp_depends and retrieve the structures of all dependent tables, but this will also include fields that your procedure does not include. It'll be easier to go after views.|||The procedures I am interested in do selects only. But they pull from other procedures and views.|||I am looking for something like that as well. So far I am using sp_sproc_columns to fetch the list of params from a SQL Server stored procedure.

There's nothing that I've found that will return a vertical list of outputs though. Ultimately I would like to display the fields returned by the sproc and their data types. Hopefully I won't have to re-invent the wheel by having to parse the sproc text.

I'll post back if I find anything else.

Thanks,

Doug

Fax Delivery Extension

Are there any Fax Delivery Extensions out there?
If not does anyone have any pointers on writing one? I am taking a look a
the sample delivery extension that ships with RS for starters but wouldn't
mind some pointers from anyone who was already gone down this road.
Thanks,
NickHello Nick,
I am planning to write fax delivery extension and saw your post on this
topic. If you have found out ay solution then can you send me sample code.
Your help will be of great help.
Thanks in advance
Rohit
"Nick P @. INDATA" wrote:
> Are there any Fax Delivery Extensions out there?
> If not does anyone have any pointers on writing one? I am taking a look a
> the sample delivery extension that ships with RS for starters but wouldn't
> mind some pointers from anyone who was already gone down this road.
> Thanks,
> Nick

Fatal Network Error 4014 when writing big BLOB chunks

Using the SqlClient provider I'm trying to write big datachunks of maybe 20 MB each to SQL server to store in BLOBs using blobColumn.Write(...) using .NET 2.0 dbcommand object calling a Stored procedure

CREATE PROCEDURE [dbo].[putBlobByPK]

(

@.id dKey

, @.value VARBINARY(MAX)

, @.offset bigint

, @.length bigint

, @.ModDttm dModDttm OUT

, @.ModUser dModUser OUT

, @.ModClient dModClient OUT

, @.ModAppl dModAppl OUT

)

....

When doing this I can do this exactly 3 times than the application hangs (for ever).

When looking in the SQL Server log, I find the following to errors:

Error: 4014, Severity: 20, Status: 2.

A fatal error occurred while reading the input stream from the network. The session will be terminated.

I don't get this error on the client! OK, the session died.

What may be the problem?

I write big chunks like this to avoid many writes as the data shall be replicated later using peer to peer replication. And the more writes used for writing the total BLOB the more huge becomes the transaction log of the subscriber database.

TIA

Hannoman

After rebooting all machines (client and server) this didn't happen any more. And the client has a problem with a deferred installation - when starting the test application MSI installer tries to install something but isn't successfull. It seems, that this causes the hanging of the client machine.sql

Fatal Error Handling

Hello,

I am currently writing a T-SQL template that will be used at client sites to update their databases whenever our software product requires backend changes.

The goal of the template is to wrap up all DDL/DML with error handling, and if an error occurs during the execution of the script at a remote site, SQLMail sends our office an email with the time/cause/site/....

A pseudo version of the template is:

CREATE TABLE #ERROR_STORE AS xxxxxxxxxxxxxxxx

BEGIN TRANSACTION "xxxxxxxxx"

BEGIN TRY

DDL/DML

END TRY

BEGIN CATCH

INSERT INTO #ERROR_STORE (@.@.ERROR, xxxxxxxxxxxxx)

END CATCH

IF RECORDS_EXIST_IN(#ERROR_STORE) BEGIN

ROLLBACK TRANSACTION

SQLMAIL("send me all errors in #ERROR_STORE")

END

ELSE BEGIN

COMMIT TRANSACTION

END

DROP TABLE #ERROR_STORE

--

The problem with this approach is that any fatal errors will kill the execution of the entire query. So anything like "select * from A_TABLE_THAT_DOESNT_EXIST" will leave me helpless

I need a way (is there a way..) to manage/catch/detect a fatal error that occurs when a script of this nature is executed.

Thanks.

The answer to your question is NO, you cannot trap "table does not

exist" by any method other than checking to see if it exists first.

The error handling in SQL 2000 and 2005 is EXTREMELY limited. This is a HUGE failing of MS to fix. The TRY/CATCH in 2005 is a step in the right direction, but it only catches a limitted amount of errors, basically the things that set @.@.ERROR in 2000.

Most SQL errors are TERMINAL and stop the batch from running and you cannot trap them at all. Worse, if you have a parent stored proc calling a child stored proc, and the child fails, lets say for "table does not exist", the child proc TERMINATES on the line that caused the error, and returns to the parent as if nothing happened.|||

Yeah, the error handling in 2005 is more oriented to DML errors than DDL errors. I would look at what RedGate does with their SQL Compare tool as a good idea of how to do things (you can get their tool and look at the output, and use it too, it is a nice tool for building these kinds of differential scripts from version to version.)

Bottom line is that I would consider building a loader program that runs your scripts in an installer-like fashion and probably not just provide scripts for the user to run. Then you have error handling power at the client level.

|||

Oddly enough, my company uses SQL Compare... I was creating a console app that would clean out a few things I didnt like about it and add in a few bits that I needed (e.g. SQL Mail if errors occurred).

Correct me if I am wrong, but they wrap every DDL/DML statement into its own transaction, so some of the script can commit where other parts of the script could fail... I really dont think they would do this but thats what it looked like in the script...

BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[TimeEntries]'
GO
CREATE TABLE [dbo].[TimeEntries]
(
....
)

GO
IF @.@.ERROR<>0 AND @.@.TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @.@.TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SetTimeEntry]'
GO

etc. etc.

Also, I am going to be automating all of this eventually (not passing scripts to client site IT people), so having a loader program isnt out of the question. But how would the error detection improve by going in this direction?

Thanks!

|||Yes, that is what they do. And roll it back at the end of each of the batches if there has been an error. This way nothing gets committed, and the next transactions don't get committed either..

Fatal Error Handling

Hello,

I am currently writing a T-SQL template that will be used at client sites to update their databases whenever our software product requires backend changes.

The goal of the template is to wrap up all DDL/DML with error handling, and if an error occurs during the execution of the script at a remote site, SQLMail sends our office an email with the time/cause/site/....

A pseudo version of the template is:

CREATE TABLE #ERROR_STORE AS xxxxxxxxxxxxxxxx

BEGIN TRANSACTION "xxxxxxxxx"

BEGIN TRY

DDL/DML

END TRY

BEGIN CATCH

INSERT INTO #ERROR_STORE (@.@.ERROR, xxxxxxxxxxxxx)

END CATCH

IF RECORDS_EXIST_IN(#ERROR_STORE) BEGIN

ROLLBACK TRANSACTION

SQLMAIL("send me all errors in #ERROR_STORE")

END

ELSE BEGIN

COMMIT TRANSACTION

END

DROP TABLE #ERROR_STORE

--

The problem with this approach is that any fatal errors will kill the execution of the entire query. So anything like "select * from A_TABLE_THAT_DOESNT_EXIST" will leave me helpless

I need a way (is there a way..) to manage/catch/detect a fatal error that occurs when a script of this nature is executed.

Thanks.

The answer to your question is NO, you cannot trap "table does not exist" by any method other than checking to see if it exists first.
The error handling in SQL 2000 and 2005 is EXTREMELY limited. This is a HUGE failing of MS to fix. The TRY/CATCH in 2005 is a step in the right direction, but it only catches a limitted amount of errors, basically the things that set @.@.ERROR in 2000.

Most SQL errors are TERMINAL and stop the batch from running and you cannot trap them at all. Worse, if you have a parent stored proc calling a child stored proc, and the child fails, lets say for "table does not exist", the child proc TERMINATES on the line that caused the error, and returns to the parent as if nothing happened.

|||

Yeah, the error handling in 2005 is more oriented to DML errors than DDL errors. I would look at what RedGate does with their SQL Compare tool as a good idea of how to do things (you can get their tool and look at the output, and use it too, it is a nice tool for building these kinds of differential scripts from version to version.)

Bottom line is that I would consider building a loader program that runs your scripts in an installer-like fashion and probably not just provide scripts for the user to run. Then you have error handling power at the client level.

|||

Oddly enough, my company uses SQL Compare... I was creating a console app that would clean out a few things I didnt like about it and add in a few bits that I needed (e.g. SQL Mail if errors occurred).

Correct me if I am wrong, but they wrap every DDL/DML statement into its own transaction, so some of the script can commit where other parts of the script could fail... I really dont think they would do this but thats what it looked like in the script...

BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[TimeEntries]'
GO
CREATE TABLE [dbo].[TimeEntries]
(
....
)

GO
IF @.@.ERROR<>0 AND @.@.TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @.@.TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SetTimeEntry]'
GO

etc. etc.

Also, I am going to be automating all of this eventually (not passing scripts to client site IT people), so having a loader program isnt out of the question. But how would the error detection improve by going in this direction?

Thanks!

|||Yes, that is what they do. And roll it back at the end of each of the batches if there has been an error. This way nothing gets committed, and the next transactions don't get committed either..

fastest way to open a query?

Hi.I am writing a program in c++ with ado which has to write 2-3 times
per second in a same field.That`s because i need speed.i have to use
sql unfortunately because our webmaster will take data from here.if i
had chance to choose i would choose berkeley db.Anyway here is a piece
of my code.how should i change the open parameters or should i try
something else?
char query[100];
sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
keyValue); //example keyValue = USDGBP
bstr_t bstrQuery(query);
try {
hr = m_pRecSet->Open(_variant_t(bstrQuery),
vNull,
adOpenForwardOnly,
adLockOptimistic,
adCmdText);
if (!m_pRecSet->GetadoEOF()) {
m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
m_pRecSet->Update(vNull, vNull);
m_pRecSet->Close();
}
}
catch( _com_error &e ) {
TRACE( "Error:%08lx.\n", e.Error());
TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
}Try Select EXCHANGE_ID, MARKET_DATA_BID, MARKET_DATA_ASK FROM ...
That will reduce the amount of data being prepared and should speed it up
marginally.
<ozgecolak@.gmail.com> wrote in message
news:1129299784.233691.240390@.f14g2000cwb.googlegroups.com...
> Hi.I am writing a program in c++ with ado which has to write 2-3 times
> per second in a same field.That`s because i need speed.i have to use
> sql unfortunately because our webmaster will take data from here.if i
> had chance to choose i would choose berkeley db.Anyway here is a piece
> of my code.how should i change the open parameters or should i try
> something else?
> char query[100];
> sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
> keyValue); //example keyValue = USDGBP
> bstr_t bstrQuery(query);
> try {
> hr = m_pRecSet->Open(_variant_t(bstrQuery),
> vNull,
> adOpenForwardOnly,
> adLockOptimistic,
> adCmdText);
> if (!m_pRecSet->GetadoEOF()) {
> m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
> m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
> m_pRecSet->Update(vNull, vNull);
> m_pRecSet->Close();
> }
> }
> catch( _com_error &e ) {
> TRACE( "Error:%08lx.\n", e.Error());
> TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
> TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
> TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
> }
>|||Why bring a recordset down to the client at all? You are not reading the
values. You are making a minuimum of one trip to the server (to get a
recordset) and a maximum of two trips (to update the value if it exists).
Get rid of the recordset and limit this to a maximum of one trip to the
server. The two inefficiencies I see here are using inline sql and the
biggie of using a recordset to perform an update.
I would recommend:
1) Use a stored proc. Let SQl Server compile the execution plan saving it
from having to do it on the fly.
CREATE PROC sp_UpdateTicker @.Bid smallmoney, @.ask smallmoney, @.ID char(10)
AS
SET NOCOUNT ON
UPDATE MarketDate
SET MARKET_DATA_BID = @.Bid, MARKET_DATA_ASK = @.Ask
WHERE EXCHANGE_ID = @.ID
GO
2) Just call the ->Execute method on a connection or command object to run
the proc. Pass along the adExecuteNoRecords enum value to make sure that it
is as efficient as possible.
// Create and Configure the Command Object
pCom.CreateInstance(__uuidof(Command));
pCom->ActiveConnection = pConn;
pCom->CommandType = adCmdStoredProc ;
pCom->CommandText = _bstr_t("dbo.sp_UpdateTicker ");
// Append Parameters
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Bid"), adCurrency,
adParamInput, 8, _variant_t(bidValue)));
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Ask"), adCurrency,
adParamInput, 8, _variant_t(askValue)));
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.ID"), adChar,
adParamInput, 10, s));
// Execute the Command
pCom->Execute(NULL, NULL, adCmdStoredProc, adExecuteNoRecords);
Even if you do not have permission on the database to create a proc, at
least get away from the recordset and pass your inline sql in the
pCom->Execute(NULL, NULL, adCmdText, adExecuteNoRecords);
HTH,
John Scragg
"ozgecolak@.gmail.com" wrote:

> Hi.I am writing a program in c++ with ado which has to write 2-3 times
> per second in a same field.That`s because i need speed.i have to use
> sql unfortunately because our webmaster will take data from here.if i
> had chance to choose i would choose berkeley db.Anyway here is a piece
> of my code.how should i change the open parameters or should i try
> something else?
> char query[100];
> sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
> keyValue); //example keyValue = USDGBP
> bstr_t bstrQuery(query);
> try {
> hr = m_pRecSet->Open(_variant_t(bstrQuery),
> vNull,
> adOpenForwardOnly,
> adLockOptimistic,
> adCmdText);
> if (!m_pRecSet->GetadoEOF()) {
> m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
> m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
> m_pRecSet->Update(vNull, vNull);
> m_pRecSet->Close();
> }
> }
> catch( _com_error &e ) {
> TRACE( "Error:%08lx.\n", e.Error());
> TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
> TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
> TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
> }
>|||thanks.now it`s faster but not as fast as i need.
John Scragg yazdi:
> Why bring a recordset down to the client at all? You are not reading the
> values. You are making a minuimum of one trip to the server (to get a
> recordset) and a maximum of two trips (to update the value if it exists).
> Get rid of the recordset and limit this to a maximum of one trip to the
> server. The two inefficiencies I see here are using inline sql and the
> biggie of using a recordset to perform an update.
> I would recommend:
> 1) Use a stored proc. Let SQl Server compile the execution plan saving it
> from having to do it on the fly.
> CREATE PROC sp_UpdateTicker @.Bid smallmoney, @.ask smallmoney, @.ID char(10)
> AS
> SET NOCOUNT ON
> UPDATE MarketDate
> SET MARKET_DATA_BID = @.Bid, MARKET_DATA_ASK = @.Ask
> WHERE EXCHANGE_ID = @.ID
> GO
> 2) Just call the ->Execute method on a connection or command object to run
> the proc. Pass along the adExecuteNoRecords enum value to make sure that
it
> is as efficient as possible.
> // Create and Configure the Command Object
> pCom.CreateInstance(__uuidof(Command));
> pCom->ActiveConnection = pConn;
> pCom->CommandType = adCmdStoredProc ;
> pCom->CommandText = _bstr_t("dbo.sp_UpdateTicker ");
> // Append Parameters
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Bid"), adCurrency
,
> adParamInput, 8, _variant_t(bidValue)));
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Ask"), adCurrency
,
> adParamInput, 8, _variant_t(askValue)));
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.ID"), adChar,
> adParamInput, 10, s));
> // Execute the Command
> pCom->Execute(NULL, NULL, adCmdStoredProc, adExecuteNoRecords);
> Even if you do not have permission on the database to create a proc, at
> least get away from the recordset and pass your inline sql in the
> pCom->Execute(NULL, NULL, adCmdText, adExecuteNoRecords);
> HTH,
> John Scragg
>
> "ozgecolak@.gmail.com" wrote:
>|||I think you need to test the individual parts. This code takes only
miliseconds to run for me.
Here is a tip. If you do this inside a loop. Declare the command, set its
properties and create all parameters OUTSIDE of the loop. Then just
repeatedly assign the bid, ask and ID values to the parameters inside the
loop and call the execute inside the loop. This will save you any repeated
object creation overhead.
HTH
John
"ozgecolak@.gmail.com" wrote:

> thanks.now it`s faster but not as fast as i need.
> John Scragg yazdi:
>

Fastest way to alter table structure

I'm writing a database table designer (works like the Enterprise Manager one, but with restricted datatypes and some checks), and I have a bit of a question:

To alter the structure of a table, I'm currently using the same method as Enterprise Manager (create a temporary table with the new schema, copy the data across, drop the original table, rename the temporary table).

When altering the structure of a table with a large number of rows, but not changing the order of columns or adding columns in the middle of the table:
Would it be faster to execute multiple ALTER TABLE statements? If so, at what point would it become slower? (Say, changing lots of column data types, etc).Thanks

Probably always faster using ALTER TABLE.

I really doubt that ALTER TABLE statements would be noticably slower.

|||Thanks.

I was worried that if there were a lot of column data type changes there could be a lot of conversions, and a lot of overhead from going through the entire table multiple times.
|||I guess that could happen -depends upon the quantity and type of changes.

Failure writing properties running SSIS package from a Web Service

I am attempting to run an SSIS package from a web service. Right now both the service and package are on my local machine which is running XP. I have accessed the web service from a client application in debug mode. I am not sure if it is actually running under aspnet_wp.exe because it is XP and a development environment? (separate question)? The package fails with a series of OnError messages similar to:

The result of the expression ""/c DEL /F /Q \"" + @.DeployFolder + "\\catalog.diff.lz\""" on property "Arguments" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

An initial supposition is that the permissions of the web service are inadequate for the package. I have the authentication as "Windows" and <identity impersonate="true" /> in the Web.Config file. When I break in the debugger the Environment.UserName and Environment.UserDomainName are mine and I am an Admin on the box.
the authorization is 'deny users="?".

The article that describes basic implementation of this in a Web Service states:

With its default settings for authentication and authorization, a Web

service generally does not have sufficient permissions to access SQL

Server or the file system to load and execute packages. You may have to

assign appropriate permissions to the Web service by configuring its

authentication and authorization settings in the web.config

file and assigning database and file system permissions as appropriate.

A complete discussion of Web, database, and file system permissions is

beyond the scope of this topic.

And how!

Note that the load is fine and that this is a run time error and that the package runs correctly when run manually from SQL Server using the 'run package' menu item in the Object Explorer tree of the SQL Server Management Console.

I need to know if this is an ASP.NET issue per se or XP or if this is even a security issue. And how to solve it! This is critical path so an expeditious reply with a solution would be greatly appreciated.Can't believe I left this out but the the web service is running under Integrated Windows authentication.

Failure writing file

I get "Failure writing file" ... when my subscription runs? Does any one know
how to fix it?Can you post the error message in the ReportServerService<date>.log file.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"AbiBaby" <AbiBaby@.discussions.microsoft.com> wrote in message
news:17C09CC5-47F8-447A-B185-34143ABDAE2C@.microsoft.com...
> I get "Failure writing file" ... when my subscription runs? Does any one
know
> how to fix it?|||you should give to you network user the right to access (write) the folder
where you report are delivred.
"AbiBaby" wrote:
> I get "Failure writing file" ... when my subscription runs? Does any one know
> how to fix it?|||It has all Rights, i eneabled everyone. I wrote my own subscription service.
And it works!
"Tino [Securitas.fr]" wrote:
> you should give to you network user the right to access (write) the folder
> where you report are delivred.
> "AbiBaby" wrote:
> > I get "Failure writing file" ... when my subscription runs? Does any one know
> > how to fix it?