Problem with Computed Column and integrated functions

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Problem with Computed Column and integrated functions

Postby gonamic » Tue Jan 18, 2011 2:56 pm


I'm currently evaluating SQL Source Control v1.1.0.19 (using SVN as backend) and found a problem with tables containing computed columns that call integrated SQL Server functions. I have the following table definition:

Code: Select all
CREATE TABLE [dbo].[testtable](
   [test1] [nvarchar](50) NOT NULL,
   [test2]  AS (hashbytes('md5',[test1]))

The table commits fine, but when I try to "Get latest" into a new devolepment database, SQL Source Control tries to execute the following script (notice the brackets around the "hashbytes" call):

Code: Select all
CREATE TABLE [dbo].[testtable]
[test1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL,
[test2] AS ([hashbytes]('md5',[test1]))

which fails with "Incorrect syntax near '(' in line 4". SQL Server doesn't seem to recognize the "hashbytes" as an internal function call anymore.
I manually checked out the repository with Tortoise, and the sql script for the table doesn't contain those brackets, so it seems to be SQL Source Control that adds them in.

Is there anything I could do to prevent this behaviour (short of having to manually edit the script file and removing the brackets around the function calls everywhere)?

Kind regards,
Oliver Drexler
Posts: 4
Joined: Wed May 31, 2006 7:57 am

Postby james.billings » Wed Jan 19, 2011 3:21 pm

Thanks for your post. I'll see if I can replicate it here myself. Did you submit an error report when the problem occurred? If you do this, it should tie it in to any other occurrences of that particular problem in our database here.
Posts: 1122
Joined: Wed Jun 16, 2010 10:10 am
Location: Red Gate

Postby james.billings » Wed Jan 19, 2011 3:42 pm

Update - I can get this to happen on my current v2 build as well.

I've raised a bug for this under reference SOC-2071 as I couldn't see it already listed as an issue, but if that changes I'll let you know.

Not sure of a workaround as yet unfortunately.
Posts: 1122
Joined: Wed Jun 16, 2010 10:10 am
Location: Red Gate

Postby gonamic » Thu Jan 20, 2011 10:14 am

Thanks for your reply. I'm glad you could reproduce this on your side ;)

Until there's a fix I'll just edit the script manually and remove the brackets from the function call each time I checkout/update one of those tables (currently we're only using the "hashbytes" function in computed columns, so this is a simple search&replace).

Posts: 4
Joined: Wed May 31, 2006 7:57 am

Postby techvslife » Tue Feb 08, 2011 7:23 pm

I have a different but VERY related problem--I think it's the same cause. I'm using Sql 2008 R2, the early release of sql source control for Git, and a shared dev model (since I'm the only dev):

I have calculated fields in a table which are defined this way:
[OpDtme] AS (dateadd(hour,(6),CONVERT([time](0),[OpDtm],(0)))),
[ClDtme] AS (dateadd(hour,(6),CONVERT([time](0),[ClDtm],(0))))

I commit, then Redgate immediately shows I need to do a new commit because the table designs have changed (they haven't changed). It shows that what it *thinks* it stored in source control is this instead (under "latest source control version" on the right pane):
[OpDtme] AS (dateadd(hour,(6),CONVERT([time](),[OpDtm],(0)))),
[ClDtme] AS (dateadd(hour,(6),CONVERT([time](),[ClDtm],(0))))

Note that redgate has a zero missing after [time] when showing what it thinks it put it in source control. (When I manually check the source control files myself, the zero is there, i.e. the source control files really do match the database.)

When I try to commit again, I get this error back (not surprising since there really have been no changes):

git commit -a -m "RedgateIssue_NoChange"
# On branch master
nothing to commit (working directory clean)
Exit code:

EDIT: Note I have sent this in to email support, as ticket Red Gate F0044337.
Posts: 6
Joined: Tue Feb 16, 2010 10:06 pm

have the same problem

Postby akeybupt2004 » Thu Dec 01, 2011 5:16 am

i come across the same problem. when can this be fixed?
manual fix the script won't resolved this problem, because the encoding problem.
if column contains non-acsii characters, the generated scripts will be decoded wrong.
Posts: 3
Joined: Thu Dec 01, 2011 4:56 am
Location: 美国

Return to SQL Source Control Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests