Red Gate forums :: View topic - Problem with Computed Column and integrated functions
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 1
SQL Source Control 1 forum

Problem with Computed Column and integrated functions

Search in SQL Source Control 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
gonamic



Joined: 31 May 2006
Posts: 4

PostPosted: Tue Jan 18, 2011 3:56 pm    Post subject: Problem with Computed Column and integrated functions Reply with quote

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:

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

Code:
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
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1123
Location: My desk.

PostPosted: Wed Jan 19, 2011 4:21 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1123
Location: My desk.

PostPosted: Wed Jan 19, 2011 4:42 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
gonamic



Joined: 31 May 2006
Posts: 4

PostPosted: Thu Jan 20, 2011 11:14 am    Post subject: Reply with quote

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

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
Back to top
View user's profile Send private message
techvslife



Joined: 16 Feb 2010
Posts: 6

PostPosted: Tue Feb 08, 2011 8:23 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
akeybupt2004



Joined: 01 Dec 2011
Posts: 3
Location: 美国

PostPosted: Thu Dec 01, 2011 6:16 am    Post subject: have the same problem Reply with quote

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.
Back to top
View user's profile Send private message AIM Address
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group