Cannot execute db_change_field() to convert nvarchar to varbinary [#1830024]

Comment	File	Size	Author
#9	_sqlsrv-explicit_cast_convert-1830024-9.patch	777 bytes	Uncle_Code_Monkey

#3	1830024-sqlsvr-migrate-field.patch	1005 bytes	lyricnz

#1	1830024-sqlsvr-migrate-field.patch	1004 bytes	lyricnz

Comment #1

lyricnz CreditAttribution: lyricnz commented 2 November 2012 at 13:35

File	Size
1830024-sqlsvr-migrate-field.patch	1004 bytes

According to http://msdn.microsoft.com/en-us/library/ms187928.aspx there are a number of conversions that must be made explicitly (see the "Implicit Conversions" section). If we restrict that table to only the (13) data types used by sqlsvr, the only problematic conversions are those from date+datetime2+nchar+nvarchar+time to varbinary(max). Only the nchar+nvarchar cases are realistic.

I started this in attached patch, but (a) I don't have any SQL Server to test on, and (b) I don't know how to check the old field spec to determine if it's nchar+nvarchar (is it safe to ask for the schema from in here).

So, marking this as CNW pending someone with any clue about SQL Server at all :)

Log in or register to post comments

Comment #2

lyricnz CreditAttribution: lyricnz commented 2 November 2012 at 13:36

Status:

Active

» Needs work

Log in or register to post comments

Comment #3

lyricnz CreditAttribution: lyricnz commented 2 November 2012 at 13:53

File	Size
1830024-sqlsvr-migrate-field.patch	1005 bytes

Missing a close-bracket, at least.

Log in or register to post comments

Comment #4

katannshaw CreditAttribution: katannshaw commented 2 November 2012 at 19:36

Thanks for the patch lyricnz. I'll give it a try first thing Monday morning and I'll let you know what happens. It's much appreciated.

Log in or register to post comments

Comment #5

lyricnz CreditAttribution: lyricnz commented 3 November 2012 at 00:34

The patch is probably incomplete - it will use CAST() whether it needs to or not, when migrating to varbinary(max)

Log in or register to post comments

Comment #6

katannshaw CreditAttribution: katannshaw commented 5 November 2012 at 17:25

Ok, thanks for letting me know. Would you prefer for me to wait until it's complete, or would you like for me to test it out?

Log in or register to post comments

Comment #7

lyricnz CreditAttribution: lyricnz commented 6 November 2012 at 10:57

I have no way of testing it. Go for it.

Log in or register to post comments

Comment #8

Damien Tournoud CreditAttribution: Damien Tournoud commented 23 February 2013 at 10:31

Is there any reason not to always use an explicit cast? After all, an implicit cast is... a cast too, just implicit.

Log in or register to post comments

Comment #9

Uncle_Code_Monkey CreditAttribution: Uncle_Code_Monkey commented 19 April 2013 at 15:45

Status:

Needs work

» Needs review

File	Size
_sqlsrv-explicit_cast_convert-1830024-9.patch	777 bytes

My error happened to be when updating CTools (data field in ctools_object_cache changed from varchar to varbinary). I modified the patch to always use an explicit cast since Damien made sense with his observation. Tested on my site and verified that it fixes my issue and did not cause any others.

FYI, don't forget to apply this patch to the sqlsrv module and then copy the entire sqlsrv subfolder to %site%/includes/database so the change actually gets used.