Options
CLI - Wrong return code
rakshitpatel
Posts: 7
SET @SQLExec = 'C:\"Program Files"\"Red Gate"\"SQL Data Compare 7"\SQLDataCompare.exe /server1:'
+ @SourceServer + ' /server2:' + @DestinationServer
+ ' /db1:' + @SourceDatabase
+ ' /db2:@db2/include:table:\['
+ @TablesToCompare + '\] /force /scriptfile:"'
+ @OutputDirectory + @OutputFileName
+ '" /q /options:nc,iid,k,its,t,ica'
EXEC @result = master.dbo.xp_cmdshell @SQLExec
@result always returns an exit code of 77
+ @SourceServer + ' /server2:' + @DestinationServer
+ ' /db1:' + @SourceDatabase
+ ' /db2:@db2/include:table:\['
+ @TablesToCompare + '\] /force /scriptfile:"'
+ @OutputDirectory + @OutputFileName
+ '" /q /options:nc,iid,k,its,t,ica'
EXEC @result = master.dbo.xp_cmdshell @SQLExec
@result always returns an exit code of 77
Comments
Exit code 77: Action cannot be completed because the user does not have permission
Does the user that is running the command have the necessary permissions to use xp_cmdshell and/or log into the database requested? Could you try running a command line comparison directly from a command prompt rather than through xp_command shell?
On a side note, and this is probably just a mis-copy when writing the post, but the /db2 switch in your command looks incorrect. I think it is meant to be + ' /db2:' + @db2 + ' /include:table:\['
I hope this helps.
I ran it from command prompt and there was no error
I ran the command from Management Studio without capturing the return code and it worked and created script.
Its works but give me wrong return code when I capture it.
If that doesn't solve the problem, then try running the command as the SQL Server user and hopefully more information will get printed to the console about where the problem had occurred.
RunAs /user:SqlServerUser "SQLDataCompare.exe /db1:test /db2:test2..."