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
Table Variables in Stored Procedure
MiLambert
Adrian
DexIT
Cheers,
Greg.
Bay Tran
Greg.
hyng
Actually, "static" tables doesn't work either ... e.g. Here's an s/p ..
create procedure greg_test
as
IF 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% complete
End Progress
Log:
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:20
End Log
Log:
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:20
End Log
Error: 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 Error
Log:
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:20
End Log
Any ideas
BrendaLL
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. "
petervelosy
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
Burbank Steve
Worked for me to set both of these:
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
Good luck to you all.
Sean Sandys - MS
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
thn
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.
Aces
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.
ingsgr01
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.
ykagoma