Stored procedure in OLE DB Source

Question

Stored procedure in OLE DB Source

lucazav

SSC Eights!

Points: 981
More actions
May 19, 2008 at 10:49 am

#121430

Hi all! I'm new in SqlServerCentral and this is my first topic 😀 .
I simply tried to link an OLE DB Souce to a Stored Procedure in order to work with its resultset... So I switch the "Data Access Mode" to "SQL Command" and I write "EXEC dbo.MyStoredProc 'stringParameter' " onto the "SQL Command text". But when I click on the Columns tab of OLE DB Source Editor, I can't see any column available... Where is the mistake?
Thank you very much.
Luca

Viewing 11 posts - 1 through 10 (of 10 total)

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

chileu17 Hall of Fame Points: 3093 More actions · Answer 1

hi, I don't understand very well with question, but if you are gonna export your data to an excel spread sheet, you will have to create like the table that will hold the data, but it should work just fine :crazy:

Well hope I can help in any way 😀

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 2

I've had good luck with simple SPs, but with complicated SPs that had a lot of selects, I don't think it handles that very well.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Jeffrey Williams SSC Guru Points: 90298 More actions · Answer 3

Are you using any temp tables in the stored procedure? That can cause some problems - as well as multiple resultsets.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

lucazav SSC Eights! Points: 981 More actions · Answer 4

crever (5/21/2008)
I've had good luck with simple SPs, but with complicated SPs that had a lot of selects, I don't think it handles that very well.

Yes! It has a lot of SELECTs... and it takes at least 15 seconds... Is it a problem?

lucazav SSC Eights! Points: 981 More actions · Answer 5

No temp tables and only one resultset... The strange think is that the "Preview..." works fine, but it isn't so for the columns selection 🙁

chileu17 Hall of Fame Points: 3093 More actions · Answer 6

why don't you try to export it to plain text file? so you could know what is getting or how is working :crazy: it is really weird I have several sp's with big select statements on them and it works just fine.

What version of sql server have you got?

lucazav SSC Eights! Points: 981 More actions · Answer 7

We have SQL Server 2005 installed 1 week ago and now I'm practicing with SSIS. I developed a lot of Stored Procedures in the previous SQL Server 2000 and now I need to work with their result set in SSIS packages... 🙁

lucazav SSC Eights! Points: 981 More actions · Answer 8

I got the solution!! :w00t:

Simply, at the top of the OLE DB Source SQL statement and before the EXEC MySP line, I added the following: SET FMTONLY OFF !!

It seems to work fine: now I can see the columns on the Columns tab.

Hope this help anyone having the same problem

Jeff Ericson-369457 SSC Rookie Points: 27 More actions · Answer 9

yeah baby, worked like a charm, what led you to FMT Only?

lucazav SSC Eights! Points: 981 More actions · Answer 10

lucazav

SSC Eights!

Points: 981

September 15, 2008 at 5:30 am

#871672

You can find the solution HERE.

Enjoy!! 😀