Creating a batch job which runs a stored proc -- sql server programming

Vamsi
12/22/2003 5:19:19 PM

Hi,

Thanks. I think you have missed a few lines in my post.
The Site that you have suggested me completely deals with
DTS!!
I CANNOT use DTS due do some reasons,,, is there another
way???? like may be i can create a batch job, and
schedule it in windows!!!

If I can do it with a batch job,,, how do i go about
writing the sql stmts("Exec myStoredProcedure" & "Save
outputfile") in the batch file?? Can you pls advice.

Thanks,
Vamsi

[quoted text, click to view]

John Bell
12/28/2003 5:40:24 PM

Hi

There are plenty of examples of using BCP by searching google but this will
create a CSV file that will need converting into Excel format, but can be
loaded into Excel e.g:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=OlRefT56BHA.2252%40tkmsftngp04

Other alternative is to use the sp_OA* procedures e.g
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9153

John
[quoted text, click to view]

Peter Rand
12/28/2003 8:37:45 PM

Another option might be to program the extract directly in Excel, and then
batch the Excel file. For example, you could use the Data->Import External
Data->From a database, and specify the SQL as "exec sp_yourstoredprocedure",
returning the rows to Excel. Set up a macro to save the data to a new sheet
right before you exit Excel. Then batch the Excel file. I've used this type
of extract on a number of occations and it works just fine. DTS is
preferred, but since you can't use it, as noted below, you'd have to extract
to a flat CVS file and then import into Excel. And since you want to
schedule this in Windows (not a SQL Agent job), there's no requirement that
the Excel file be on the server...

Hope this helps,
Pete

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Creating a batch job which runs a stored proc