Thursday 20 December 2007

Stored procedures in SSIS; the need for nocount

I've been doing a lot of SSIS work lately, and met an odd problem when using stored procedures in an OLEDB source. The general idea was to have a stored procedure that did some data modification, then finished with a SELECT statement to output logging information.

When previewing the OLEDB source everything was fine, but running the package would give the following error in the pre-execute phase:
'A rowset based on the SQL command was not returned by the OLE DB provider'

It took a while to work out, but eventually the problem came down to the way that SQL Server returns information about the number of rows affected. The resolution is to put the following SQL statement at the start of the stored procedure defnition to prevent output of the number of rows:

SET NOCOUNT ON

After that it was all fine.

2 comments:

oracle procurement said...
This comment has been removed by the author.
oracle procurement said...

Thanks for posting this blog I'n reality loved it and put up some particular blogs approximately oracle........visit our internet site associated with oracle fusion procurement
for more information please check the site
Oracle Fusion Procurement online training