Executing Stored Procedures in a Job in MSSQL 7.0

Question

Executing Stored Procedures in a Job in MSSQL 7.0

howarkl

Grasshopper

Points: 12
More actions
September 24, 2003 at 5:11 am

#83210

I have a job scheduled in MSSQL 7.0 that I need to execute a stored procedure and create a text file containing the results of the stored procedure. I'm having trouble getting the results to save correctly to the text file. It does create the file but does not contain the data specified in the stored procedure:
I need to be able to capture the results of each of the sections of this stored procedure.
Example: (this is just a portion or one section of the stored procedure)
SELECTgetdate() as "Unique batch and que combinations"
/*Find unique batch and que combination from tableA table*/
Drop table table1
SELECT
batch, queueid
INTO
table1
FROM
tableA
GROUP BY
batch, queueid
order by
batch, queueid
I should be capturing the total batches selected.
currently i have the job setup as:
under steps: i have selected Transact SQL script
and then for the command: I have
exec (stored procedure name)
then i have specified under the advanced tab, i have the output file named and i've have it overwrite.
I hope someone can help me with this. I'm running this manually and i need to get it automated.
Oh, I have no choice but to run this under sql 7.0 right now, proprietary applications require this version.

Viewing 4 posts - 1 through 3 (of 3 total)

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

David Burrows SSC Guru Points: 65143 More actions · Answer 1

The file will contain output from the job as a result of PRINT or SELECT FROM. A SELECT...INTO will not output to the file. Therefore the output from the snippet you gave would give you



Unique batch and que combinations

---------------------------------

2003-09-24 13:49:42.637



(1 rows(s) affected)

To see then contents of the query, either remove the 'INTO table1' or add

SELECT * FROM table1

Edited by - davidburrows on 09/24/2003 06:55:46 AM

Far away is close at hand in the images of elsewhere.
Anon.

YuanQiang Mr or Mrs. 500 Points: 520 More actions · Answer 2

I would use

bcp "your query" out "yourfile.txt" -c

instead.

YuanQiang Mr or Mrs. 500 Points: 520 More actions · Answer 3

Sorry, I forgot you use SQL7. Try something like this:

bcp "select..." queryout myfile.txt -c -Syourserver -Usa -Ppassword