Stored procedure as a source error

Question

Post reply

Stored procedure as a source error

stricknyn

SSCertifiable

Points: 6403
More actions
August 12, 2009 at 12:35 pm

#135201

Hi all,
I am completely stumped on why this is happening. I have a dataflow task which is using a stored procedure as the source:
SET FMTONLY Off
exec [dbo].[UM_AuthReporting_DLFormat] 510,25,'','B',-1,1,'8/10/2009 12:00:00 AM','8/10/2009 12:00:00 AM','X','G',1,-1
The error below is what I get when I execute this task:
[OLE DB Source [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.
If I run the exact same thing in SQL Mgmt Studio it runs just fine.
Now if I flip SET FMTONLY off to SET FMTONLY On in the data flow task, it will run but of course no data is returned because I believe SET FMTONLY On only returns column information. Can anyone see the problem here?
Thanks,
Strick

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

What do you get in SSMS when you run this:

SET FMTONLY ON

exec [dbo].[UM_AuthReporting_DLFormat] 510,25,'','B',-1,1,'8/10/2009 12:00:00 AM','8/10/2009 12:00:00 AM','X','G',1,-1

SET FMTONLY Off

What is your SP doing?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

John Rowan SSC Guru Points: 56440 More actions · Answer 2

Here's a good read for you:

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61824/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

stricknyn SSCertifiable Points: 6403 More actions · Answer 3

Found the issue guys. Needed SET NOCOUNT ON in the sp.

Thanks for your help.

Strick

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Yep, that'll do it. Good to hear you've got it working.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

corey.collins Valued Member Points: 67 More actions · Answer 5

I have had this issue occur and even set fmtonly off and set nocount on didnt fix it. The only solution was to change the data source to an ADO Net Source instead of OLE DB source.