I am using a table variable inside a stored procedure that I am trying to execute from an OLE Datasource task in IS. I know there was a problem doing this in DTS, which would result in an Invalid Pointer error. I am not getting that error, but I am getting an error that says "[OLE DB Source [55]] Error: A rowset based on the SQL command was not returned by the OLE DB provider." The stored procedure runs fine on it's own. Any thoughts?

No answers for you, but I'm experiencing the same issue. Stored proc returns a recordset from a table variable and works fine on its own. When trying to use it as a data source within SSIS, I'm getting ... [Component1 records [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider. [DTS.Pipeline] Error: component "Component1 " (1) failed the pre-execute phase and returned error code 0xC02092B4. Have you stumbled upon anything yourself?

Again, no help and I've just found this thread. I'm experiencing exactly the same problem today.I'm executing the stored proc from the OLE DB Source in the DataFlow. The last stage of the s/p is to do a SELECT (and join) from two Table variables.When these tables are defined as variables, the columns appear in the OLE DB source, but I get exactly the same runtime error as describe in the previous posts.If I change the stored proc to use temp tables (#) then the OLEDB source shows no columns.The only way I've got this to work is for the S/P to create "proper" static tables, and then drop them at a later stage.Greg.

Actually, "static" tables doesn't work either ... e.g. Here's an s/p .. create procedure greg_testasIF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[gregtest]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)DROP TABLE [dbo].[gregtest]create table gregtest(pk int, val varchar(100))insert gregtest values (1, 'one')insert gregtest values (2, 'two')insert gregtest values (3, 'three')insert gregtest values (4, 'four')insert gregtest values (5, 'five') select pk, val from gregtest I have a package with a single dataflow task. The dataflow has an OLEDB Source with the SQL command set to greg_test. This maps to a flat file destination. Simple! When I run the package, this is part of the output; Progress: 2006-02-17 15:34:20.73 Source: Data Flow Task Pre-Execute: 0% completeEnd ProgressLog: Name: OnError Computer: ISD-PC Operator: ISD\pc Source Name: Data Flow Task Source GUID: {2FABFD9D-0C23-4515-82E6-B5F6F63165A2} Execution GUID: {A71061E6-8F29-4315-BE39-D50C2DF56B2F} Message: A rowset based on the SQL command was not returned by the OLE DB provider. Start Time: 2006-02-17 15:34:20 End Time: 2006-02-17 15:34:20End LogLog: Name: OnError Computer: ISD-PC Operator: ISD\pc Source Name: Package2 Source GUID: {A20FB25B-63BF-442A-B037-80637568910C} Execution GUID: {A71061E6-8F29-4315-BE39-D50C2DF56B2F} Message: A rowset based on the SQL command was not returned by the OLE DB provider. Start Time: 2006-02-17 15:34:20 End Time: 2006-02-17 15:34:20End LogError: 2006-02-17 15:34:20.86 Code: 0xC02092B4 Source: Data Flow Task OLE DB Source [204] Description: A rowset based on the SQL command was not returned by the OLE DB provider.End ErrorLog: Name: OnError Computer: ISD-PC Operator: ISD\pc Source Name: Data Flow Task Source GUID: {2FABFD9D-0C23-4515-82E6-B5F6F63165A2} Execution GUID: {A71061E6-8F29-4315-BE39-D50C2DF56B2F} Message: component "OLE DB Source" (204) failed the pre-execute phase and returned error code 0xC02092B4. Start Time: 2006-02-17 15:34:20 End Time: 2006-02-17 15:34:20End Log Any ideas?

I would have thought this was a fundemental requirement in SSIS. Any takers on my problem?Greg.

Same exact issue, do any of the board masters really have no input on this?? I find it odd that not being an isolated occurence no one has dealt with and conquered this problem.Adrian

I have not heard nor learned anything new on this since my original post. I was able to work around the problem by inserting all of my interim results in a permanent table, in one stored procedure, and then in another step of the SSIS package use a second stored procedure to select a portion of those rows and export them in this case to a flat text file. I am "glad" that I am not the only one with this issue, and that someone at my company at least new immediately that it was related to the table variable.

Most troubling thing is it doesn't matter if you're using a table variable or a static table, as is in my case. I've had issues in the past where a sproc that was being used as a data source took to long to run SSIS would whine about not having any metadata to populate the columns tab....but this time the metadata is there and it sees it! I'm going to try to keep re-arranging my sproc, since it doesn't seem to like anything other than nice vanilla select statement :)

I will tell you the answer, but you will not be happy about it. Add "SET NOCOUNT ON" to the beginning of your stored proc, and your problem should disappear. Worked for me, anyway. Peace.

Nice one. Guess that makes sense when you think about it ...Cheers,Greg.

Exact same problem. Set NOcount on didn't help at all.Seems SSIS can't do stored procedures at all. Can't believe there is no answer for this!My stored procedure "cross tabs" a bunch of data into a temp table and then selects from it.Adding SET FMTONLY OFF to the command text causes SQL server to see the metadata, but when you execute the package you get: "[OLE DB Source [109]] Error: A rowset based on the SQL command was not returned by the OLE DB provider. "

John Dieter wrote: Exact same problem. Set NOcount on didn't help at all.Seems SSIS can't do stored procedures at all. Can't believe there is no answer for this!My stored procedure "cross tabs" a bunch of data into a temp table and then selects from it.Adding SET FMTONLY OFF to the command text causes SQL server to see the metadata, but when you execute the package you get: "[OLE DB Source [109]] Error: A rowset based on the SQL command was not returned by the OLE DB provider. "I've used sprocs in OLE DB Sources with no issues. Can you share your sproc DDL at all so that we can review it?

Worked for me to set both of these: SET NOCOUNT ONSET ANSI_WARNINGS OFF Good luck to you all.

SET NOCOUNT ON is working for me.

Ray Myers,Thanks it's works for me.

SET NOCOUNT ONALSO REMOVE ANY PRINT STATEMENTS IN YOUR SP

It did not work for me at first either. I was reading it all wrong. I was trying to put the "set nocount on" within my proc. I finally realized that I had to put it in my SSIS package. So it looks like this...SET NOCOUNT ONEXEC sprocThis worked like a charm. Thanks to all of you for your help!!

Thanks, you are the one who gave a clear answer to this question.........:)

Excellent!!!I also encountered the same exact issue. This post saved my day. Thanks folks. You guys are just amazing.....SET NOCOUNT ONEXEC sprocThis should be the syntax and it must be given in SSIS OLE DB Source from where the Stored Procedure is called...Thanks a million... Raajakumari

I had the same problem. SET NOCOUNT ON got me working :) Thanks... Ravi

Thanks alot... It worked fine when I use SET NOCOUNT ON SET ANSI_WARNINGS OFFbeefore executing the proc..

Worked for me! Thanks!

This has worked for me. But you need to add these two lines to the OLEDB component not in teh SP itself i.e SET NOCOUNT ON SET ANSI_WARNINGS OFF exec usp_CUBE_LoadStoredProc '20100101' ,'20100131' Good luck :)