Problem with Computed Column and integrated functions

gonamic

Hello,

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:

CREATE TABLE [dbo].[testtable](
	[test1] [nvarchar](50) NOT NULL,
	[test2]  AS (hashbytes('md5',[test1]))
) ON [PRIMARY]

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):

CREATE TABLE [dbo].[testtable]
(
[test1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL,
[test2] AS ([hashbytes]('md5',[test1]))
) ON [PRIMARY]

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

James B

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.

James B

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.

gonamic

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).

Regards,
Oliver

techvslife

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):

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

EDIT: Note I have sent this in to email support, as ticket Red Gate F0044337.

akeybupt2004

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.