DLM Build Error
GordonMPG
Posts: 11 Bronze 1
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:
I think these steps will help me resolve the issue. Right now everything is cleaned up upon failure.
Thank you!
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
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?
Redgate Software
This works! Thank you.
Redgate Software
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
Redgate Software