Issue - system_user becomes system_user() which is incorrect

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Issue - system_user becomes system_user() which is incorrect

Postby tranquilizer » Fri Jan 18, 2013 8:28 pm

I have a table creation script within which there is a column with a default constraint defined with a value of system_user.

After doing a sql compare, the syncronization script changes the default constraint definition to system_user() which is incorrect.

There is no system_user() function in sql server. It should just be system_user.

Example :-
Original script is -
CREATE TABLE [dbo].[Test]
(
[Test_ID] [int] IDENTITY(1,1) NOT NULL,
[User_Created] [varchar](100) NOT NULL DEFAULT (system_user)
)

After sql compare with a database which does not have this table, the syncronization script output is -
CREATE TABLE [dbo].[Test]
(
[Test_ID] [int] IDENTITY(1,1) NOT NULL,
[User_Created] [varchar](100) NOT NULL DEFAULT (system_user())
)

This syncronization script when executed on target server will throw error because there is no such function as system_user().

Please investigate.

Thanks!
tranquilizer
 
Posts: 2
Joined: Fri Jan 18, 2013 8:19 pm

Postby chriskelly » Mon Jan 21, 2013 4:51 pm

Thank you for your post.

My thinking at the moment is that the SQL Compare engine is having problems interpreting the default value as a string " DEFAULT (system_user) ".

The usual form for this in t-sql is
Name varchar(255) DEFAULT 'string'

While SQL Server is able to correctly interpret the form of
Name varchar(255) DEFAULT (Sandnes)
as a string. The SQL Compare engine has not been designed to do so. Can you therefore try changing your original script (or write a test object) using the single quotes instead of the brackets and then test it with our tool.

Please can you let me know how you get on.
Chris Kelly
Technical Support Engineer
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK

Postby tranquilizer » Mon Jan 21, 2013 9:18 pm

Thank you for taking a look. But I am not sure if you understood the issue correctly.

I am not defining the default to be of string type value 'system_user'
I am defining the default to be of value returned by the sql server function system_user
and therefore the below definition -

[User_Created] [varchar](100) NOT NULL DEFAULT system_user
tranquilizer
 
Posts: 2
Joined: Fri Jan 18, 2013 8:19 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest