Execute SQLCMD from a JOB

Question

Execute SQLCMD from a JOB

Navigation

#1 by (1 votes)

0

Good afternoon colleagues, I need your help to know what is happening with my JOB since it does not perform the action I want. I commented that I made a Stored Procedure to send a JOB call which executes a command line SQLCMD to create a file .txt with the information of a query, however you are not creating the file .txt , then I leave the information:

Stored Procedure

CREATE PROC testjob
AS
EXEC msdb.dbo.sp_start_job N'GetDataFromPagos'

SQLCMD

sqlcmd -S myserver -U "sa" -P "Password" -d clientes -Q "SELECT TOP 5 * FROM CLIENTES..PAGOS" -o C:\Employees.txt

JOB image

I do not know if I need to perform any other action, at the moment of executing my stored procedure it shows me the following message:

 Job 'GetDataFromPagos' started successfully.

Apparently it says that it starts successfully, however I do not know what is missing so that I can create the file.

Additional Note : I commented that I executed the SQLCMD from the command line ( cmd ) and it worked correctly.

Your help is appreciated. Greetings.

sql sql-server

asked by BDOM 07.08.2018 в 01:46

source

1 answer

Keep session on two routers Print a n amount of data from a PYTHON csv file

score 1 · Answer 1

Because of what you see in the JOB image, surely the account in the "Run As" does not have privileges to write the file where you specify.

What I have done is the following:

Create a credential, associating it with an existing identity in SQL Server, a LOGIN, that has write permissions on the hard disk. Here you will not run an SQL type, it must be of the Windows type. The password is from said LOGIN, not one associated with the credential.

Create a proxy, assigning the credential created in the previous step. In this proxy, activate the subsystem "Operating System (CmdExec)".

Edit the step in the JOB and in the "Run As" box, select the previously created proxy.

With the above, the job from the stored procedure must already create the file.