Creation of nupkg with SQLCI fails with Could not find server 'sql2' in sys.servers

Legacy Uncategorised
1

Hi,

I’m creating nupkgs manually using SQLCI. Nupks generated fine for several databases, but one database is failing with the error “Could not find server ‘sql2’ in sys.servers.”. I am used to seeing this if a linked server has not been correctly set up. But, in this case I have no idea where I should go to correct this. Can you help? I would like to skip this validation, if that is possible.

My output:

C:\Windows\system32>sqlci Build --scriptsFolder=“C:\sandbox\databases\SQL15\mt” --outputFolder=“C:\sandbox-databases\SQL15\mt” --packageId=“mt” --packageVersion="4.47"
sqlCI.exe – Red Gate’s SQL Continuous Integration v2.1.0.811

STARTING: Creating scratch database sqlCI_32845ed6-45ae-4398-b41d-0070dda328f5
COMPLETED SUCCESSFULLY: Creating scratch database sqlCI_32845ed6-45ae-4398-b41d-0070dda328f5
STARTING: Validating database state
Starting: C:\Program Files (x86)\Red Gate\sqlCI\SC\SQLCompare.exe
/include=“Identical”
/include=“StaticData”
/scripts1=“C:\Users\cennis\AppData\Local\Temp\bag51h3k.gnu\db\state”
/server2=“np:\.\pipe\LOCALDB#05AD0935\tsql\query”
/database2=“sqlCI_32845ed6-45ae-4398-b41d-0070dda328f5”
/synchronize /options="DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,UseMigrationsV2"
SQL Compare: activated, edition: professional, serial number: **************
SQL Compare Command Line V11.1.4.44

Copyright Copyright c 1999 - 2015 Red Gate Software Ltd

Registering data sources
Creating mappings
Comparing

Applying Command Line Items

Checking for identical databases
Creating SQL
Inserting static data SQL into deployment script
Deploying changes (from DB1 to DB2)
Error: Synchronization of ‘Scripts.state’ and ‘np:\.\pipe\LOCALDB#05AD0935\tsql\query.sqlCI_32845ed6-45ae-4398-b41d-0070dda328f5’ failed: Could not find server ‘sql2’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Error executing the
following SQL: CREATE PROCEDURE [dbo].[mt_Import] AS SET …

Validating database state failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this
exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare

FINISHED WITH ERROR: Validating database state
Running SQLCompare failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare

2

I was able to get further by specifying an existing empty database on the target server. But, still receive an error related to the DTC. I’ve tried several things, including changes to MSDTC on the target, with no luck.

C:\Windows\system32>sqlci Build --scriptsFolder=“C:\sandbox-databases\SQL15\mt” --outputFolder=“C:\sandbox\mt-databases\SQL15\mt” --packageId=“mt” --packageVersion=“4.47” --temporaryDatabaseServer=QASQL15 --temporaryDatabaseName=mt
–additionalCompareArgs="/options:DecryptPost2KEncryptedObjects,ForceColumnOrder,IgnoreConstraintNames,IgnoreFileGroups,IgnoreFillFactor,IgnoreFullTextIndexing,IgnoreReplicationTriggers,IgnoreSquareBrackets,IgnoreStatistics,IgnoretSQLt,IgnoreWhiteSpace,UseClrUdtToStringForClrMigration,DisableAndReenableDdlTriggers"
sqlCI.exe – Red Gate’s SQL Continuous Integration v2.1.0.811

STARTING: Emptying temporary database
Starting: C:\Program Files (x86)\Red Gate\sqlCI\SC\SQLCompare.exe /include=“Identical” /include=“StaticData” /scripts1=“C:\Program Files (x86)\Red Gate\sqlCI\DefaultCompareOptions” /server2=“QASQL15” /database2=“mt” /synchronize /options=“DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder
,IgnoreDatabaseAndServerName,UseMigrationsV2,ForceColumnOrder,IgnoreConstraintNames,IgnoreFullTextIndexing,IgnoreReplicationTriggers,IgnoreSquareBrackets,IgnoreStatistics,IgnoretSQLt,UseClrUdtToStringForClrMigration,DisableAndReenableDdlTriggers”

SQL Compare: activated, edition: professional, serial number: *******************
SQL Compare Command Line V11.1.4.44

Copyright Copyright c 1999 - 2015 Red Gate Software Ltd

Registering data sources
Creating mappings
Comparing

Applying Command Line Items

Checking for identical databases
Creating SQL
Inserting static data SQL into deployment script
Deploying changes (from DB1 to DB2)

Summary Information

DB1 = DefaultCompareOptions
DB2 = QASQL15.movieticketsTEMP

Object type Name Records DB1 DB2

Role MSReplPAL_5_4 <<
…
…

COMPLETED SUCCESSFULLY: Emptying temporary database
STARTING: Validating database state
Starting: C:\Program Files (x86)\Red Gate\sqlCI\SC\SQLCompare.exe /include=“Identical” /include=“StaticData” /scripts1=“C:\Users\cennis\AppData\Local\Temp\pvz4x332.yxd\db\state” /server2=“QARLSSQL15” /database2=“mt” /synchronize /options="DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,UseMigrationsV2,ForceColumnOrder,IgnoreConstraintNames,IgnoreFullTextIndexing,IgnoreReplicationTriggers,IgnoreSquareBrackets,IgnoreStatistics,IgnoretSQLt,UseClrUdtToStringForClrMigration,DisableAndReenableDdlTriggers"
SQL Compare: activated, edition: professional, serial number: *************
SQL Compare Command Line V11.1.4.44

Copyright Copyright c 1999 - 2015 Red Gate Software Ltd

Registering data sources
Creating mappings
Comparing

Applying Command Line Items

Checking for identical databases
Creating SQL
Inserting static data SQL into deployment script
Deploying changes (from DB1 to DB2)
Error: Synchronization of ‘Scripts.state’ and ‘QASQL15.mt’ failed: The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “sql2” was unable to begin a distributed transaction. OLE DB provider “SQLNCLI10” for linked server “sql2” returned message “The partner transaction manager has disabled its support for remote/network transactions.”.
Error executing the following SQL: CREATE PROCEDURE [dbo].[mt_Import] AS SET …

Validating database state failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare

FINISHED WITH ERROR: Validating database state
Running SQLCompare failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare

3

Hi Cennis,

Sorry for the delay. We have notified RedGate as SQLCI is a product of theirs.
Someone should be along soon to take a look.

Vanessa

4

Hi Cennis,

Is the Distributed Transaction Coordinator service running on both the CI server and the target server? If not, you’ll have to turn them on.

Here’s an MSDN post detailing how to fix this - https://social.msdn.microsoft.com/Forums/en-US/7172223f-acbe-4472-8cdf-feec80fd2e64/the-partner-transaction-manager-has-disabled-its-support-for-remotenetwork-transactions?forum=adodotnetdataproviders

5

Thanks Rob. It turns out I had DTC running on the CI server and target server, but not on one of the linked server connections referenced within the DB code. Problem solved.