DLM Build Error

GordonMPG

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!

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?

GordonMPG

Hello Robert,

This works! Thank you.

Rob C

That's great to hear!

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

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.