Post reply

return error for sqlcmd in sql agent job

  • October 19, 2016 at 10:59 am

    #314530

    I have a simple task to export a table to a flat file on network share.

    so I schedules a sql agent job that does below:

    sqlcmd -S myserver -d mydb -E -Q "set nocount on;select studentID, guardian_contact_id from [dbo].[ParentReferral]" -o "\\myotherserver\data\import\myflatfile.dat" -W -w 700 -h-1 -s","

    THe job type is Operating system, cmdexec

    I found today the job succeeded, but when I opened the flat file, on top of the file there it error: login account which is the SQL agent service account failed to login to the database, even so, it still generated the data.

    It is strange, I thought SQL agent account by default has sysadmin permissions, so should be able to. Then I specifically added the permission, it works now.

    My question now is how to let the SQL agent job to return job failed for about case if it fails? Should I just add a -b switch?

    Thanks,

  • October 19, 2016 at 12:21 pm

    #1907500

    sqlfriends (10/19/2016)

    I have a simple task to export a table to a flat file on network share.

    so I schedules a sql agent job that does below:

    sqlcmd -S myserver -d mydb -E -Q "set nocount on;select studentID, guardian_contact_id from [dbo].[ParentReferral]" -o "\\myotherserver\data\import\myflatfile.dat" -W -w 700 -h-1 -s","

    THe job type is Operating system, cmdexec

    I found today the job succeeded, but when I opened the flat file, on top of the file there it error: login account which is the SQL agent service account failed to login to the database, even so, it still generated the data.

    It is strange, I thought SQL agent account by default has sysadmin permissions, so should be able to. Then I specifically added the permission, it works now.

    My question now is how to let the SQL agent job to return job failed for about case if it fails? Should I just add a -b switch?

    Thanks,

    Adding the -b switch to the sqlcmd call will cause the SQL Agent job step to fail, but the actual error will still be in your output file. If you need sqlcmd to login as something different than the SQL Agent Service account, then you could setup proxy credentials in SQL Agent and then in the job step Run As dropdown select the proxy instead of SQL Server Agent Service Account.

    https://technet.microsoft.com/en-us/library/ms190698(v=sql.105).aspx

  • October 19, 2016 at 3:54 pm

    #1907553

    thanks, as long as the job returns error, that is good enough, then I can go to the text file to check the error

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

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