TeamCity Build failing due to Linked Server VOIP

ABCDENYE

Hi guys,

I am new to Teamcity and Redgate builders. Hope this is the right forum.

I have successfully built a few new TC builds from subversion which work great but when I attempt to build another project with a linked server 'VOIP' it fails each time.
Using subversion source control.
SQL Server 2008 R2.

Log error:

[16:42:37][Validating database state] Error: Synchronization of 'Scripts.state' and
[16:42:37][Validating database state] 'np:\.pipeLOCALDB#D38A5C22 sqlquery.sqlCI_93fd7671-56f2-4268-a6b7-878912591
[16:42:37][Validating database state] 1bc' failed: Could not find server 'voip' in sys.servers

This Database I want to build, test and deploy has a linked server which appears to be failing the build.
select name from sys.servers shows the VOIP server on both Dev and Test servers.

Any help would be appreciated.

Thanks




How can I handle this?

ABCDENYE

I've got a step further.
Set up a local instance of mysql DB and added a linked server on sql server object to it. Connects fine.


But when I run the teamcity redgate ci build I hit this obstacle:


"failed: The operation could not be performed because OLE DB provider "MSDASQL" for linked server "voip" was unable to begin a distributed transaction. OLE DB provider "MSDASQL"
for linked server "voip" returned message "[MySQL][ODBC 5.1 Driver]Optional feature not supported". Error executing the following SQL: CREATE function..."

Any ideas?

MikeN

I had a similar problem (though just using sqlserver) albeit for a different reason. I got the following reply from support, which helped me with my linked server issue...

The reason for the error you are seeing is because SQL Server does not allow changes to fulltext indexes within a transaction, and SQL CI uses them by default.

Because of this, the deployment order cannot be created correctly, without splitting into multiple transactions which kind of defies the point of wrapping everything in a transaction anyway.

To get around this, CI needs to be told not use transactions in the deployment script, which can be done by specifying the NoTransactions option.

Looking at your forum post, I think you are using the TFS/Visual Studio plugin to set up CI?

If so, open the SQLCIPROJ file in Visual Studio, edit the Build step and go to the Advanced tab. Under Include SQL Compare arguments, put:

/Options:NoTransactions

Or add NoTransactions to any options you already have listed there.

This, plus a Microsoft post (can't find just now), told me that cross-database transactions get elevated to DTS transactions so you need to either enable DTS or not use transactions.

I don't know if you are using the VS plugin but, however you are creating the builds, you just need to pass in the NoTransactions flag. The uglier and more awkward way is to setup MSDTC on the build and target servers, it's a pain but might be necessary if you can't use the NoTransactions flag.

Hope that helps.