CONTEXT_INFO() used in constraints

molnara

Hello,

It looks like SQL Compare is trying to put brackets on any constraints which use CONTEXT_INFO(). Take a look at the below script output from latest schema compare:

"CONSTRAINT [DF_Account_CreatedBy] DEFAULT (CONVERT([nvarchar](128),case when [CONTEXT_INFO]() IS NULL then suser_name() else [CONTEXT_INFO]() end,(0)))"

Notice the square brackets? SQL Compare is putting these there, my original constraint in source control looks like:

"CONSTRAINT [DF_Account_CreatedBy] DEFAULT (CONVERT([nvarchar](128),case when CONTEXT_INFO() IS NULL then suser_name() else CONTEXT_INFO() end,(0)))"

I am guessing this is a bug as it does not wrap suser_name() inside square brackets.

Brian Donahue

Can you please provide the full table script? If you don't want to post it in public, you can reply to the email that support had sent confirming that you had opened a support call. Trying to isolate the default and migrating using SQL Compare did not allow me to replicate the issue. For instance

CREATE DEFAULT [DF_Account_CreatedBy] 
AS (CONVERT([nvarchar](128),case when CONTEXT_INFO() IS NULL then suser_name() else CONTEXT_INFO() end,(0)))

results in a working migration script that does not add the brackets.

Brian Donahue

I also suspect it may be important to know whether the database being migrated to or the version specified in the RedGateDatabaseInfo file is < SQL 2005.

Brian Donahue

I can't reproduce the issue in a create table query either.

CREATE TABLE [dbo].[x]
(
[y] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [DF_Account_CreatedBy] DEFAULT (CONVERT([nvarchar](128),case when context_info() IS NULL then suser_name() else context_info() end,(0)))
)