What are the challenges you face when working across database platforms? Take the survey
Options

DLM Build Error

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!
Tagged:

Comments

  • Options
    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?
    Software Engineer
    Redgate Software
  • Options
    GordonMPGGordonMPG Posts: 11 Bronze 1
    Hello Robert,

    This works! Thank you.
  • Options
    That's great to hear!
    Software Engineer
    Redgate Software
  • Options
    GordonMPGGordonMPG Posts: 11 Bronze 1
    edited April 13, 2017 6:29PM
    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
  • Options
    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.
    Software Developer
    Redgate Software
Sign In or Register to comment.