Selective XML indexes problem

pablo.lorenzo

Hi

I'm having some problems with the code generated with sql source control. Part of the code generated is this

CREATE SELECTIVE XML INDEX [SXI_INTERVIEW_INTERVIEW_XML]
ON [dbo].[interview] ([interview_xml])
FOR (
INTERVIEW_SCORE = '/interview/scores/score' AS XQUERY  ,
INTERVIEW_SCORE_NAME_XPATH = '/interview/scores/score/@name' AS XQUERY 'xs:string' SINGLETON,
INTERVIEW_SCORE_TOTAL_XPATH = '/interview/scores/score/@total' AS XQUERY 'xs:string' SINGLETON,
INTERVIEW_SCORE_TOTAL_SQL = '/interview/scores/score/@total' AS SQL [nvarchar] (max) SINGLET

The red word in INTERVIEW_SCORE = '/interview/scores/score' AS XQUERY , is generting an error 126 when we tried to deploy in a new server. We are using Teamcity with sql compare.

Any idea?

Sergio R

Hi Pablo,

We have opened a support ticket to troubleshoot this issue, I will contact you by email shortly.

Thank you,

pablo.lorenzo

This is the error in TeamCity

Step 4/5: Build XXXXXXXXXX Database Package (Redgate DLM Automation Build) (8s)
[09:15:47][Step 4/5] Starting: "C:Program Files (x86)Red GateDLM Automation Suite 1sqlcisqlCI.exe" build /temporaryDatabaseUserName=dsfgdfgdsfgd/temporaryDatabaseServer=sqlserver/scriptsFolder=databaseEL_BGL_UNDERWRITING /packageId=XXXXXXXXXXXXXX /packageVersion=1.37 /additionalCompareArgs=/logLevel:Verbose /includeDocs
[09:15:47][Step 4/5] in directory: D:BuildAgentwork5addebc3b2060da3
[09:15:47][Step 4/5] sqlCI.exe -- Red Gate's SQL Continuous Integration v2.1.18.224
[09:15:47][Step 4/5] 
[09:15:47][Step 4/5] Creating scratch database sqlCI_a7d4622e-8c20-43ad-8a9e-4dc12eb8a333
[09:15:47][Creating scratch database sqlCI_a7d4622e-8c20-43ad-8a9e-4dc12eb8a333] STARTING: Creating scratch database sqlCI_a7d4622e-8c20-43ad-8a9e-4dc12eb8a333
[09:15:47][Creating scratch database sqlCI_a7d4622e-8c20-43ad-8a9e-4dc12eb8a333] COMPLETED SUCCESSFULLY: Creating scratch database sqlCI_a7d4622e-8c20-43ad-8a9e-4dc12eb8a333
[09:15:47][Step 4/5] Validating database state (4s)
[09:15:47][Validating database state] STARTING: Validating database state
[09:15:47][Validating database state] Starting: C:Program Files (x86)Red GateDLM Automation Suite 1SCSQLCompare.exe /include="Identical" /include="StaticData" /scripts1="D:BuildAgent	empuildTmpSQL CI2ccyj0na.lkndbstate" /logLevel="Verbose" /server2="sqlserver" /database2="sqlCI_a7d4622e-8c20-43ad-8a9e-4dc12eb8a333" /username2="asdfrddsddfvdf" /password2=****** /synchronize /tempInstance=****** /options="DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,UseCompatibilityLevel,UseMigrationsV2"
[09:15:48][Validating database state] SQL Compare: activated, edition: professional, serial number: 610-001-245885-1DC6
[09:15:48][Validating database state] SQL Compare Command Line V11.4.8.66
[09:15:48][Validating database state] ==============================================================================
[09:15:48][Validating database state] Copyright Copyright ¸ 1999 - 2016 Red Gate Software Ltd
[09:15:48][Validating database state] 
[09:15:48][Validating database state] Registering data sources
[09:15:51][Validating database state] Creating mappings
[09:15:51][Validating database state] Comparing
[09:15:51][Validating database state] 
[09:15:51][Validating database state] Applying Command Line Items
[09:15:51][Validating database state] 
[09:15:51][Validating database state] Checking for identical databases
[09:15:51][Validating database state] Creating SQL
[09:15:51][Validating database state] Inserting static data SQL into deployment script
[09:15:51][Validating database state] Deploying changes (from DB1 to DB2)
[09:15:52][Validating database state] Error: Synchronization of 'Scripts.state' and
[09:15:52][Validating database state] 'sqlserverinstance.sqlCI_a7d4622e-8c20-43ad-8a9e-4dc12eb8a333' failed:
[09:15:52][Validating database state] Incorrect syntax near 'XQUERY'. Incorrect syntax near 'XQUERY'. Incorrect syntax
[09:15:52][Validating database state] near 'XQUERY'. Incorrect syntax near 'XQUERY'. Error executing the following
[09:15:52][Validating database state] SQL: CREATE SELECTIVE XML INDEX [SXI_INTERVIEW_INTERVIEW_XML] ON
[09:15:52][Validating database state] [dbo].[interview] ([interview_xml]) FOR ( INTERVIEW_SCORE =
[09:15:52][Validating database state] '/interview/scores/score' AS XQUERY , INTERVIEW_SCORE_NAME_XPATH = '/intervi...
[09:15:52][Validating database state] ##teamcity[buildStatus status='FAILURE' text='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']
[09:15:52][Validating database state] FINISHED WITH ERROR: Validating database state
[09:15:55][Step 4/5] ##teamcity[buildStatus status='FAILURE' text='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']
[09:15:55][Step 4/5] Process exited with code 32
[09:15:55][Step 4/5] Step Build XXXXXXXXXXXXXXXXXXXXXXX Database Package (Redgate DLM Automation Build) failed
[09:15:55]Step 5/5: Release XXXXXXXXXXXXXXXXXXXX (OctopusDeploy: Create release)