EXPORT STORED PROC WITH PARAMETERS RESULTS TO TEXT FILE

select the results of the sp into a temp table (by changing the sp to be a (select * into #temp from ...)

and then DTS the temptable

You might need to use tempdb..tablename instead of #temp, if the DTS establishes its own connection

Please maintain your many very old open questions:

1 02/11/2004 500 NETGEAR R0318 and WIN XP  Open VPN
2 06/17/2003 500 Text Rotation Mirror Images  Open Crystal Reports
3 08/27/2003 500 GLOBAL VARIABLES FROM DTSRUN  Open Microsoft SQL Server
4 10/02/2003 500 recipient address rejected relay access ...  Open Exchange Server
5 01/07/2004 500 Exchange Setup  Open Exchange Server

Thanks.

Or you can write a VBScript to pull the data using ADO

Let me know if you need help with this.

Don't want to use VBscript for a number of reasons.  Can you set a DTS source to a temp table?

Yes you can use DTS with temp tables, but only if you use the tempdb..tablename syntax. Just choose tempdb as the database, or if its not available, just have a second local sp which does select * from tempdb..tablename

The DTS uses a source called #temp? where #temp is the table created by selecting the records from the SP with parameters?  I am not sure on the #tempdb here.  All I am trying to do is to export a bunch of records from a SP with parameters into a new CSV file each time.

Please clarify....

Why not just use BCP with the QUERYOUT option???

Here's an overview....

http://dbforums.com/arch/7/2002/11/402705

replace or copy your sp to the following
create proc mysp
as
.... body of sp as it was. .....

... final sql statement, add the into clause ...

select ....
into tempdb..tablename
where ....

... now launch DTS

exec xp_cmdshell dtscommand ....


Sorry I haven't been able to spend enough time on this to give you the "red carpet" solution. But I hope this helps.

The only thing about the above--what if two users run the package--you overwrite or contend on the temp table (that isn't so temp since you give it a name).

Yes thats true. Using the execute command, you could increment a counter which forms part of the temp table name. But thats ugly. I wasn't sure if that was a requirement. However, if you run it in a transaction, it should be safe.  I'd prefer to hear from geoenvi before discussing more on this.

But the problem here is trying to do it from the sp. Its just odd not to have client code because its 5 mins work to do in a client language like vb. If it needs to be run from sql, does that mean there's no client app ? If you're going to call DTS or BCP or whatever without having to type much, you need a client app or a script to wrap it.

geoenvi - it would be nice to have some background on the app and why you need to do this in an sp.

arbert - one more idea which you might know about. Can you create a view

create view zz as select * from openquery(oledb text file driver connection string...)

(or opendatasource) and then insert data straight into zz ? Perhaps I'm just dreaming.

back tomorrow

Still think BCP is a painless way to export the data....KISS

Agreed

arbert -- I already tried the bcp option.. Problem is that I need to pass parameters into the stored procedure and the bcp does not allow me to do that (same with DTS from ASP).  Need to pass it from ASP for this case.  If BCP allows me to use parameters to run the SP, that would work.

plq-- I agree it is only 5 mins from the client side.  I have done that many times. However, I prefer to do it from the DB for a number of reasons(explained below), hence I turned to the "experts".  It doesn't "Have" to be from SP but it is the preferred option because of security issues trying to use FSO with VB script and ADO at the web server.  Also, third party controls are not allowed on the web server hence making the upload more intense.  This file needs to be uploaded after creation (behind the scenes) to a third party FTP site.  

it works !

Also as FYI,
I have tried this

set @sqlstring = 'isql -Q ' + '"exec buildfulfillmentsource ' + '''04/01/01'''+','+'''04/01/04''' + '" -E -o C:\Results.txt'

The system just hangs

OOPs Jumped the gun.  Did not work.  I was carried away by looking at the wrong text file output

I'm a bit lost now. Thanks for the points though.

Are you having problems getting the accepted answer working ?

thanks

On again Off again.  It works.  The problem was that I was not qualifying the SP with its dbo. and it was not recognizing it.  Thanks a lot plq.  You really helped me.

My pleasure. Those points got me up to premium so well worth the effort !

"arbert -- I already tried the bcp option.. Problem is that I need to pass parameters into the stored procedure and the bcp does not allow me to do that (same with DTS from ASP).  Need to pass it from ASP for this case.  If BCP allows me to use parameters to run the SP, that would work."


Sure they do.....You call them from ASP and pass the parms when you call them....

Also, be aware that OSQL will change in Yukon (Next version of SQL Server) and be replace with SQLCMD.

Followup question here--results from this answer were not as expected:

https://www.experts-exchange.com/questions/20947206/EXPORT-STORED-PROCEDURE-WITH-PARAMETERS-RESULTS-TO-CSV-FORMAT-JUMBLED-FORMAT.html