How can we help you today? How can we help you today?

Selective XML indexes problem

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?
pablo.lorenzo
0

Comments

2 comments

Sort by
  • Sergio R
    Hi Pablo,

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

    Thank you,
    Sergio R
    0
  • 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)
    pablo.lorenzo
    0

Add comment

Please sign in to leave a comment.