CLI - Wrong return code

rakshitpatel

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

Chris Auckland

Thanks for your post.

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.

rakshitpatel

Thanks. Yes @db2 was a typo. The user do have permission. I am running locally. It does create the script.

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.

Chris Auckland wrote:

Thanks for your post.

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.

Brian Donahue

Have you checked the file date on the script file that sqlcompare.exe produces? Maybe it is the result of a previous comparison. If you want to repeatedly overwrite the same script file, you have to add "/force" to the end of your command-line.

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..."