DBA-Simon


Hi,

I've created a stored procedure that creates a script to create a number of objects within the database (based on what existing objects are in the database). From Management Studio, this works fine, and the output is exactly as I want it.

I'm now trying to create a job that will execute this stored procedure, and deposit the results into a file somewhere on the server. When the job runs, the script is created in the correct place and is essentially ok.

However, there are a couple of questions I'd like to ask.

Why does SQL Server Agent put a header at the top of the output file I was hoping to be able to use that output file 'as is' and execute it automatically to recreate my objects when required. (Obviously, I can manually remove the header, but this is an inconvenience in this situation). How do I stop it

Also, when executed from SSMS, the output is correctly line-spaced. But, the output from the scheduled job adds an extra line between each line of text, which is, again, inconvenient. Why does it do this, and how can I prevent this (again, without manually editting the output)

Any thoughts and help greatly appreciated.

Cheers

Simon




Re: SQL Agent Jobstep output file contains automatically generated header

Jag Sandhu


Hi Simon,

The best tool to use is OSQL or SQLCMD in 2005.

This will help you generate the output file and then you can execute the file generated.

Once you got the OSQL working, you can create sql agent job with operating system command that runs the OSQL.

Not sure about the outputfile from sql agent but this will definitely work.

regards

Jag







Re: SQL Agent Jobstep output file contains automatically generated header

DBA-Simon

Thanks Jag, this was exactly what I was looking for!

Using the option -W also resolved the issue of additional lines in the output file...

Cheers

Simon






Re: SQL Agent Jobstep output file contains automatically generated header

Anonymous

Can you give me exact syntax as to how did you add option -W. I am having the same issue as you were having. I don't want the output to have the header information



Re: SQL Agent Jobstep output file contains automatically generated header

Jag Sandhu

Hi,

-W with OSQL

Allows the user to set the screen width for output. The default is 80 characters. When an output line has reached its maximum screen width, it is broken into multiple lines.

You can use the following setting:

osql -E -S servername -i sqlscriptfile.sql - w8000.

regards

Jag






Re: SQL Agent Jobstep output file contains automatically generated header

raykwatts

Is there any way, using sqlcmd, to eliminate the dashes (i.e. ---) that occur just below the column names in the header of the output file