SQL Server Job Logs 1

[ecobb]

I have a job scheduled to run nightly. It's pretty simple, it just calls a single Stored Procedure that updates a SQL Server table with info from a linked Oracle Server. The job runs fine.

I've noticed that whenever I schedule backups through a Database Maintenance Plan, it basically just creates a job as well. My "Backup" jobs all have log files reporting what they did, but I can't seen to find any such file for my other scheduled job. I can view the history on the job, but I was looking for something like a log file that would tell me how many records were inserted, etc...

It's no big deal, I just figured that if SQL Server was doing it for the Backup Jobs, it could do it for others as well.

Hope This Helps!

Ecobb
- I hate computers!

 

[mrdenny]

Those log files are actually created by sqlmaint.exe not but the job it self. If you want to see the output for a job step. Edit the step of the job, and go to the advanced tab.

At the bottom in the Operating system command (CMDEXEC) command options section there is an output file box. Stick a path to a file, and select overwrite or append. That will log whatever you would see in QA when you run the procedure to a text file.

Denny

 

[ecobb]

Thanks, mrdenny!!

Hope This Helps!

Ecobb
- I hate computers!

 

[mrdenny]

no problem. If it's not what your looking for, let me know.

Denny

 

[ecobb]

That seemed to work, partially. It created the log file, but all it has in it is the following entry:

Job 'Test - Transfer Oracle Data' : Step 1, 'Transfer_Data' : Began Executing 2003-11-20 00:30:00

I was kinda hoping for something like "200 rows affected" as well. I forgot to mention this is on SQL 2000.

Thanks!

Hope This Helps!

Ecobb
- I hate computers!

 

[mrdenny]

Is it running a DTS package, or T/SQL Script?

Denny

 

[ecobb]

It's running a T-SQL Script that calls a Stored Procedure. It's just one line:

Execute dbo.Transfer_Data



Hope This Helps!

Ecobb
- I hate computers!

 

[mrdenny]

in the procedure put "set nocount off" You may have set nocount on in there somewhere.

That should get it to kick out what you are looking for.

Denny

 

[ecobb]

Great! Thanks!

Hope This Helps!

Ecobb
- I hate computers!

 

[ecobb]

That still didn't work, the log file is still showing the same output with no new info. I guess it's really no big deal, I was just curious as to how many records were getting updated. I may just use @@ROWCOUNT in the procedure and insert the record count into a table.

Thanks!

Hope This Helps!

Ecobb
- I hate computers!