using stored proc as source for csv export

[jcaulder]

I am new to DTS and need to use a stored procedure as the source for creating a csv through transformation. I can't seem to get any combination to work. The transformation object only works with connection objects as valid sources.

I saw an earlier post that referenced

http://www.sqldts.com

but I cannot access that page/server for some reason. Anybody else got another web site or a quick pointer on how to get this to work.

All I need is to take the rowset returned from the stored procedure and build a csv file from it.

TIA!

J

 

[JohnDTampaBay]

You will still need to set up the connection object for the source database - probably where the stored procedure is located. In the transformation object under Source, you are given the option of Table/View or Query. Choose query and put in the name of the stored procedure to be run.


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[jcaulder]

I had tried that but I keep getting errors when I try to go to the 'destination' tab. The stored procedure is parameterized so I hard coded the parameters in the 'query' statement just to try and get things going. The query looks like this:

execute up_rpt_Depart '','','','2003-09-01','2003-09-02',6130

When I click on the 'destination' tab I get 'Incorrect Syntax near the word 'AND'' four times. I assume it has something to do with the parameters so I guess I'll have to figure out how to pass these in appropriately.

Is it not possible to hard code parameters to test this? This same statement works in Query Analyzer so it seems it should work here. I'm probably overlooking something.

Thanks for the reply!!

 

[JohnDTampaBay]

Hmmm, that is a peculiar error message. If the procedure call works in Query Analyzer, I would expect it to work in DTS. Strange. We're both missing something, I guess.


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[jcaulder]

The exact error message I receive is:

Error Source: Microsoft OLE DB Provider for SQL Server

Error Description: Incorrect syntax near the keyword 'and',
Incorrect syntax near the keyword 'and',
Incorrect syntax near the keyword 'and',
Incorrect syntax near the keyword 'and',
Incorrect syntax near the keyword 'and'.

Context: Error calling GetColumnInfo. Your provider does not support all the interfaces/methods required by DTS.

I am calling a stored procedure with parameters in my query. Running the identical command in Query Analyzer returns no errors and executes successfully.

Any ideas on what would cause this?

 

[JohnDTampaBay]

Not a clue. This one's beyond me.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[tlbroadbent]

These instructions are for SQL Server 2000.

You should be able to setup a simple data transformation with a SQL Server connection, a text file connection and the Transform Data task. In the Transform Data, choose SQL Query as the Source and place the Exec SP statement in the query box. Click the Preview button to make sure the query is correct.

Next, click on the destination tab. Click the Define Columns button. Click Populate From Source and the click on the Execute button.

Click on the Transformations tab to verify the columns are properly mapped. If they are try to execute the package.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm