Running SQL Compare bat file from SQL Server fails

TShubick

I have a bat file that I run from Task scheduler or from a CMD prompt with no problem.  However, if I try to run it from SQL Server using xp_cmdshell, it fails.

From SQL Sever:
EXEC master.dbo.xp_cmdshell 'C:\scripts\FT_DEMOToSIC_DEMO.bat'

NULL

C:\Windows\system32>CD C:\Program Files (x86)\Red Gate\SQL Compare 14 

NULL

C:\Program Files (x86)\Red Gate\SQL Compare 14>sqlcompare /project:"C:\Users\Redgate\Desktop\FT_DEMO v SIC_DEMO.scp" /include:Identical /synchronize 

SQL Compare V14.6.10.20102

==============================================================================

Copyright c Red Gate Software Ltd 1999-2021

NULL

SQL Compare: expired, edition standard

Exiting since no valid license was found

NULL

From CMD 

C:\>C:\scripts\FT_DEMOToSIC_DEMO.bat

C:\>CD C:\Program Files (x86)\Red Gate\SQL Compare 14

C:\Program Files (x86)\Red Gate\SQL Compare 14>sqlcompare /project:"C:\Users\Redgate\Desktop\FT_DEMO v SIC_DEMO.scp" /include:Identical /synchronize

SQL Compare V14.6.10.20102

======================================================================================================================

Copyright c Red Gate Software Ltd 1999-2021

SQL Compare: activated, edition professional

Summary Information

=======================================================================================================================

DB1 = xxx

DB2 = xxx

Object type             Name                                                                                   DB1 DB2

-----------------------------------------------------------------------------------------------------------------------

Table                   [

Kurt_M

Hi @TShubick

Having a look at the error from the xp_cmdshell, it appears it's running on a machine that's not licensed. I would recommend remoting into the server(s) that you run this command on and sign into the products with your Redgate ID to license them. 

Let me know if that doesn't solve it.

TShubick

Kurt,
If you look at the output above, the first result was running the bat file on the server where Redgate is licensed via SQL Server.  The second result was running the exact same bat file on the same server but using a CMD window to run it.

pharrell

I came here to look for a very similar answer.

I put together a BAT file that creates snapshots of multiple databases. It runs perfectly fine on the server -- via command line, Powershell, etc. If I attempt to execute the BAT file through a SQL Agent job (using either PowerShell or CmdExec) with a proxy account that has full admin rights everywhere, I am getting an access denied error.

I know this account has full access to all locations and servers involved, in fact I use that same account (on the same server) for RG Masking and Cloning operations without issue. I noticed an article about command line and licensing, so now I am wondering if the "Access Denied" is really a licensing error under the hood. 

Kurt_M

Hi @TShubick

SQL Compare (and most of our tools) are not licensed per server, they are licensed per user. So running on the server is not sufficient, you will need to run it using the same Windows user it is licensed for.

If you have a Redgate Deploy license, then editing the .bat file and including the /allusers switch should workaround this.

@pharrell

An access denied error would most likely indicate it can't access the .bat file from wherever it's stored. I also encountered this error while testing the above script from @TShubick

I used a proxy account as well, and found it didn't have the required permissions to access it. Once I reverted to using a different account with full access (sysadmin), it worked fine.

In addition to this, there could be a licensing error involved as well, as this proxy account is, I assume, not the same account which is licensed for SQL Compare.