Raised from #1828950: Update to alpha7 database update fails on MSSQL and probably also root cause of #1645306: Running update script on Feeds results in error. It seems that this failure also leaves columnname_old in the database.

CommentFileSizeAuthor
#9 _sqlsrv-explicit_cast_convert-1830024-9.patch777 bytesUncle_Code_Monkey
#3 1830024-sqlsvr-migrate-field.patch1005 byteslyricnz
#1 1830024-sqlsvr-migrate-field.patch1004 byteslyricnz

Comments

lyricnz’s picture

lyricnz commented
StatusFileSize
new 1830024-sqlsvr-migrate-field.patch1004 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 :)

lyricnz’s picture

lyricnz commented
Status: Active » Needs work
lyricnz’s picture

lyricnz commented
StatusFileSize
new 1830024-sqlsvr-migrate-field.patch1005 bytes

Missing a close-bracket, at least.

katannshaw’s picture

katannshaw commented

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.

lyricnz’s picture

lyricnz commented

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

katannshaw’s picture

katannshaw commented

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?

lyricnz’s picture

lyricnz commented

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

damien tournoud’s picture

damien tournoud commented

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

Uncle_Code_Monkey’s picture

Uncle_Code_Monkey commented
Status: Needs work » Needs review
StatusFileSize
new _sqlsrv-explicit_cast_convert-1830024-9.patch777 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.

thorsten.’s picture

thorsten. commented

Thx for the patch!

gabriel.achille’s picture

gabriel.achille commented

it is working for me too. Thanks.

omegamonk’s picture

omegamonk commented
Status: Needs review » Fixed

Thanks for the patch. It has been committed to the development release.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.