Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.
I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

fail component error notification

I have created a package which forms data from flat file to sql database.I have a data flow where all my data is processed to destination.if the package fails by any chance i want an email notification with the cause of the error.how can i do this.please let me know..I'd start with Send Mail task and the OnError event handler for the package.|||Will i be able to send mail with the list of errors occured if the do the above|||Can you specify your goal in more detail? Do you want one email with all errors listed? Do you want all errors, or just the primary error that caused the package to fail?|||I want all the errors in one email.|||

This post might help:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

I created an example that uses a Script task in the OnError event to collect all the errors, and then uses a second Script task in the OnPostExecute event to process them. You could simply add a Send Mail task to the OnPostExecute to send the email.

extraction of data from transaction log of SQL Server 2000

I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?
Check out www.lumigent.com.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"sandyinfy" <sandyinfy@.discussions.microsoft.com> wrote in message
news:4A28AAAF-D988-4ED1-9BB3-A70076CDCE79@.microsoft.com...
I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?

extraction of data from transaction log of SQL Server 2000

I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?Check out www.lumigent.com.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"sandyinfy" <sandyinfy@.discussions.microsoft.com> wrote in message
news:4A28AAAF-D988-4ED1-9BB3-A70076CDCE79@.microsoft.com...
I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?

extraction of data from transaction log of SQL Server 2000

I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?Check out www.lumigent.com.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"sandyinfy" <sandyinfy@.discussions.microsoft.com> wrote in message
news:4A28AAAF-D988-4ED1-9BB3-A70076CDCE79@.microsoft.com...
I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?