Sql Agent job results to text file

  • I run a stored procedure that creates script for each logon on the server. I have scheduled it in sql agent with the output goung to a text file. The problem is it is adding [SQLSTATE 01000] to the end of each line in the text file, wich means the script won't run without removing all these first.

    My question is why are [SQLSTATE 01000] entries been added when run under the agent?

    ps This does not happen if you run in query window even with output to text file.

  • You can try to play some tricks in the output of your SP, e.g. print SQL server comment (-- or /* */) after the output etc.

    This may not work becuase SQL agent always add the line in the output file: Job 'job name': Step 1, ... etc.

     

    Another way is to use cmdExec task. Run:

     isql -Sservername -dDatabasename -E -Q"exec your sp" -h-1 -oOutputFile.sql

    Check BOL for detail syntax of isql

     

  • I'm not really woried about comments in the file. its just their are about 700 lines of script to create logins all ending with [SQLSTATE 01000] . why does it add this line??

    i tried the command shell will work in query window but not under agent. checked permissions etc all seems ok.

  • I don't know why sql angent add [SQLSTATE 01000] to the log.

    But for isql, it should work. I have lots of jobs using this to generate scripts and are running everyday.

    What error you got for isql?

  • Just checked my jobs. I am using isqlw.

    Checked BOL,

    "All DB-Library applications, such as isql, work as SQL Server 6.5–level clients when connected to SQL Server 2000. They do not support some SQL Server 2000 features"

    So you can use isqlw or osql.

    Sorry about the confusing.

     

     

  • Hi,

    you might put the create-login-scripts into a temp table and bcp that out to a text file...

    regards karl

    Best regards
    karl

Viewing 6 posts - 1 through 5 (of 5 total)

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