Sql-server – .bat file works manually, but does nothing in SQL Server Agent job

sql serversql-server-2012sql-server-agent

I am able to run a .bat file that copies an .exe file into a specific folder, and runs it to extract some files manually.

When I use it as part of a SQL Server Agent job (step 1), I get success for the job called "Daily_Update", but it doesn't actually do anything.

The server that the SQL Server is on is different from where the file location where the files are moved and executed. I am specifying the file location with the UNC Path. \\A10 is the server with the scripts and .dat files that I am manipulating, \\A08 is the one with the SQL Server.

With the disk designator c:\ notation, I get:

The system cannot find the path specified.

When I use the UNC path name, I get:

Access is denied.

When I run the .bat file from the desktop of the server where the SQL Server lives, it works fine manually.

Any ideas why this might be happening?

Best Answer

The SQL Server user account has access to that specific folder?
Try running a simple xp_cmdshell to check that.

EXEC master.dbo.xp_cmdshell 'dir C:\<path>'

Related Solutions

Sql-server – How to succesfully run a batch file in an SQL Agent job

It looks like you need to configure the second step of your job to run as a domain account with permissions to write to the \KWS2-WEB-SERVER\Share\Reports\Uur share as well as read access to the C:\Reports\Energie\Uur folder.

You first will need to add a credential to SQL Server. From the Security > Credentials folder you will need to right click and choose New Credential... Fill in that information and click OK.

Once that is complete you will need to create a Proxy that uses this credential. From the SQL Server Agent > Proxies > Operating System (CmdExec) folder you will need to right click and choose New Proxy... Use the credential you created earlier.

Now you can configure your second job step to use the Proxy you just created using Run As: drop down of the job step page.

Sql-server – SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet:

Set-Location c:

This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file.

This article contrasts how SQLPS loads in SQL Server 2008 R2 vs. SQL Server 2012/2014. The SQL Provider is loaded using the 'Add-PSSnapin' cmdlet in SQL Server 2008 R2, while starting with SQL Server 2012, the 'Import-Module' cmdlet is used.

The author states: "The outcome of loading the module [with 'Import-Module'] is simply changing to the PSDrive you just loaded." Evidently, the 'Add-PSSnapin' cmdlet left the provider for its SQLPS session unchanged (as FileSystem). That change in cmdlet use, and the resulting session behavior, was what caused my problem.

Best Answer

Related Solutions

Sql-server – How to succesfully run a batch file in an SQL Agent job

Sql-server – SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

Related Question