Hello,

I am using the Team City DLM build plugin to create a build script. It seems there is some errors with the change script that the tool is trying to publish to the temp database. The error is:
[16:03:03][Step 1/2] WARNING: The error 'Incorrect syntax near '('.' occurred when executing the following SQL:
[16:03:03][Step 1/2] CREATE TABLE [Recognition].[UserRecognitionLtdItemOrder]
[16:03:03][Step 1/2] (
[16:03:03][Step 1/2] [UserRecognitionID] [int] NOT NULL,
[16:03:03][Step 1/2] [MPGOrderID] [int] NULL,
[16:03:03][Step 1/2] [LtdItemOrderStatusCode] [nchar] (1) COLLATE Latin1_General_CI_AS NOT NULL,
[16:03:03][Step 1/2] [StatusDT] [datetime] NOT NULL CONSTRAINT [DF_UserRecognitionLtdItemOrder_StatusDT] DEFAULT (current_timestamp()),
[16:03:03][Step 1/2] [RecCreatedDT] [datetime] NOT NULL CONSTRAINT [DF_UserRecognitionLtdItemOrder_RecCreatedDT] DEFAULT
[16:03:03][Step 1/2] (current_timestamp())
[16:03:03][Step 1/2] ) ON [PRIMARY]
[16:03:03][Step 1/2] WARNING: The error 'Incorrect syntax near '('.' occurred when executing the following SQL:
[16:03:03][Step 1/2] CREATE TABLE [Recognition].[UserRecognitionPointTran]
[16:03:03][Step 1/2] (
[16:03:03][Step 1/2] [TranSysPointTransactionID] [int] NOT NULL,
[16:03:03][Step 1/2] [UserRecognitionID] [int] NOT NULL,
[16:03:03][Step 1/2] [RecogPointTranTypeCode] [nchar] (1) COLLATE Latin1_General_CI_AS NOT NULL,
[16:03:03][Step 1/2] [RecCreatedDT] [datetime] NOT NULL CONSTRAINT [DF_UserRecognitionPointTran_RecCreatedDT] DEFAULT (current_timestamp())
[16:03:03][Step 1/2] ) ON [PRIMARY]

I am trying to figure out how to debug this, I'd like to:
  • Prevent the error log from being discarded at C:\TeamCity\buildAgent\temp\buildTmp\DLM Automation\zagou25t.log
  • View the change script that's being applied

I think these steps will help me resolve the issue. Right now everything is cleaned up upon failure.

Thank you!
GordonMPG
0

Comments

5 comments

  • Rob C
    Hi Gordon,

    I notice in that SQL query that we have CURRENT_TIMESTAMP() - this isn't meant to have parentheses afterwards. This is down to a bug in the parser (SC-8949). There is a workaround, and that is to replace CURRENT_TIMESTAMP with GETDATE() in your table definition.

    Does that help?
    Rob C
    0
  • GordonMPG
    Hello Robert,

    This works! Thank you.
    GordonMPG
    0
  • Rob C
    That's great to hear!
    Rob C
    0
  • GordonMPG
    Rob,

    I have another error, let me know if I should create a new post please?

    [16:30:42][Step 1/2] "Latin1_General_CI_AS" in the equal to operation.
    [16:30:42][Step 1/2] Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to
    [16:30:42][Step 1/2] operation.' occurred when executing the following SQL:

    I searched the code and there is no place where Latin1_General_CI_AS is set. The default on the database is "SQL_Latin1_General_CP1_CI_AS"

    Any idea on how to fix this?

    If I were to publish to a brand new database via Visual Studio it publishes fine.


    Thank you!

    UPDATE: I was able to get passed the error using the IgnoreCollations SQL Compare option
    GordonMPG
    0
  • sam.blackburn
    We've now released a fix for SC-8939 to SQL Compare 13.7.18.11916 to our frequent updates channel.  You can receive frequent updates through an option in the Help menu.
    sam.blackburn
    0

Add comment

Please sign in to leave a comment.