Automating Build using SQL Compare 7 (error 77)

carlos.oliboni.amigo

Hello,

I am automating the build process using Team Foundation and one of the steps of this process is running SQL Compare 7 in order to make a script SQL from the differences between an backup file and an currently database (used by the development team).

The build process is using the following command line in order to achieve what I want:

"c:\Program Files\Red Gate\SQL Compare 7\sqlcompare" /backup1:"C:\backup.bak"  /database2:Northwind /server2:Dev01\dev01 /username2:sa /password2:adminpassword /scriptfile:"C:\ScriptDiff.sql" /force /aid

Where the backup.bak is in the build machine, which is calling SQLCompare7 and Northwind is in a dev machine.

Everytime it is executed by my build process, I got an error code 77 from the SQL Compare and my build process is interrupted. The strange part is it is creating the script I want, but it throws an error after that. I have checked the date from the script and it is changing each build I run. I have also deleted the script and it creates a new script.

While trying to troubleshot, I have ran the above command line from the command line in the build machine and it ran ok, it created the script and show me no error at all.
I have ran the RGConnectionTest.exe and I have connection with the database I am trying to compare.

When I try to compare 2 databases in the build machine, it works fine. The error happens when I tried to compare at least one database in another server.
The user account I am using has public and sysadmin server roles.

Should I have another kind of privilege? I would apreciate any help, I am running out of ideas what I am doing wrong.

Thanks in advance,
Carlos Amigo

Brian Donahue

Hi Carlos,

We have seen this return code before from SQL Compare when being run from SSMS with xp_cmdshell, although we could not determine why it happened. In both cases, the command runs successfully and outputs a script, but results in a return code 77, which means you do not have permissions. Can you try adding the /verbose switch to the command and capture the console output? This may reveal something useful. Thanks!

Brian Donahue

I've combed through the source code and still the only relevant situation I can find that would return code 77 is when you do not have access to the file or folder where the synchronization script is written. Since we have found that it does work and your continuous integration account has permissions, it could be something odd like the account not having the "bypass traverse checking" user right while trying to cross folders that it does not have access to. Does this sound like a possibility?

carlos.oliboni.amigo

Hello Brian,

Thanks for your reply.
I have checked the Bypass Traverse Checking user right in my build machine and it is with the default settings: Administrators, Users, Everyone, and a few more have this privilege and since the user account my build is running under is member of Users it seems to exclude this possibility.

While I don't find the source of this problem, I have enabled the IgnoreExitCode parameter in the command which calls SQLCompare. Then I get the Error Code returned from this command and if it is 77 I just keep ignoring the error, otherwise I throw an Error and my build process is interrupted. It is not the best solution, since I can miss an error when some permission setting prevented my build from generate an .sql script. But it is working so far and it was the only workaround I could think.

If I found another solution I will come back here to report it. Also, I would apreciate any additional help you guys can provide me.

Thanks again,
Carlos Amigo

laazyj

I'm not sure if this is the same problem you had.

I had setup SQL Compare, and SQL Data Compare, scripts as part of our automated build process and was getting error code 77 when running on our build agents.

Further investigation showed that the comparison was working but sqlcompare.exe was still returning this error causing the build to fail.

I tracked it down to the following lines in the log output:

Could not create licence distribution files. Please run as adminstrator if you wish to create these files.
Error occurred whilst writing new distribution licence to disk: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

This was logged when the utility was first run, before starting comparison, but it caused error code 77 to be returned even when the comparison completed successfully.

It seems it needed to create a license file in the Red Gate program files folder (which the build agent user account didn't have rights to do).

I fixed it by running sqlcompare.exe once as an Administrator so that the license file was created. The build scripts could then be run as a non-privileged user without error.