CONTEXT_INFO() used in constraints

molnaramolnara Posts: 10
edited August 2, 2011 6:54AM in SQL Compare Previous Versions
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.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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 DonahueBrian Donahue Posts: 6,590 New member
    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 DonahueBrian Donahue Posts: 6,590 New member
    I can't reproduce the issue in a create table query either.
    CREATE TABLE &#91;dbo&#93;.&#91;x&#93;
    &#40;
    &#91;y&#93; &#91;nvarchar&#93; &#40;128&#41; COLLATE Latin1_General_CI_AS NULL CONSTRAINT &#91;DF_Account_CreatedBy&#93; DEFAULT &#40;CONVERT&#40;&#91;nvarchar&#93;&#40;128&#41;,case when context_info&#40;&#41; IS NULL then suser_name&#40;&#41; else context_info&#40;&#41; end,&#40;0&#41;&#41;&#41;
    &#41;
    
Sign In or Register to comment.