Hi,

I'm getting this:
PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.: UPDATE [{ctools_object_cache}] SET [data] = [data_old]; Array ( ) in db_change_field() (line 2988 of ...\includes\database\database.inc).

When running the update script:

1 pending update
ctools module
6008 - ctools_object_cache needs to be defined as a blob.

Form 7.x-1.2 to 7.x-1.2+45-dev

My setup:
Drupal 7.21 + ctools 7.x-1.2 + sqlsrv 7.x-1.2+6-dev
Microsoft IIS/7.5
Microsoft SQL Server 2008 R2

Thanks thorsten

Comments

thorsten.’s picture

thorsten. commented

hi again,

I'm getting the same error when updating from 7.x-1.2 to 7.x-1.3!
Setup is the same!
Any suggestions?

Thanks thorsten

merlinofchaos’s picture

merlinofchaos commented

This looks like it may be a bug in the microsoft sql driver where it doesn't know how to write the proper db_change_field to change that field over. Since this works correctly under other database types, the bug is likely there. You should move this ticket to whatever is giving you that database driver.

jcook4now’s picture

jcook4now commented

Having the same issue, per merlinofchaos, opened ticket (http://drupal.org/node/1961658) at the Drupal 7 driver for SQL Server project page (http://drupal.org/project/sqlsrv). . .

Jeff

thorsten.’s picture

thorsten. commented

At #1961658: PDOException: SQLSTATE[42000] - Implicit conversion not allowed - updating Ctools from 7.x-1.2 to 7.2-1.3, xenphibian has solved it:
Look here: http://drupal.org/node/1961658#comment-7262050

@merlinofchaos: Is the solution "ok" for now or should we wait for a better one?

Thanks thorsten

merlinofchaos’s picture

merlinofchaos commented

It's a temp store, not a cache; if you have in-process updates, that data will be lost. Usually this isn't a big deal during an update, because you've got a maintenance window and users shouldn't be in the process of editing data. So that's an acceptable workaround.

jcook4now’s picture

jcook4now commented

Thanks merlinofchaos - temporarily removing the field data_old allowed the update to proceed. I assume the temp store requires this field to be there so I recreated it after the update. FYI - the SQL Server table is being created as:
CREATE TABLE [dbo].[ctools_object_cache](
[sid] [nvarchar](64) NOT NULL,
[name] [nvarchar](128) NOT NULL,
[obj] [nvarchar](32) NOT NULL,
[updated] [int] NOT NULL,
[data_old] [nvarchar](max) NULL,
[data] [varbinary](max) NULL,

Should data_old and data both have the same type and, if so, should they both be nvarchar(max) or varbinary(max)?

Jeff

katannshaw’s picture

katannshaw commented

Having the same issue. Created an issue report that ended up being a duplicate of this one at http://drupal.org/node/1962520. Hopefully we'll hear from the SQLSrv maintainers soon. After getting merlin's answer to #6, should this issue be moved from Ctools to SQLSrv?

merlinofchaos’s picture

merlinofchaos commented

I think data_old might've been left over from sql server's attempts to move it. You don't need data_old and can safely drop the column.

katannshaw’s picture

katannshaw commented

Per your suggestion, I just deleted the data_old column after the 1st update and ran a 2nd update with success. I wish that the issue was solved with the SQLSrv module itself, but it's nice to have a good workaround that can help me and others to move on. Thanks merlinofchaos, xenphibian and thorsten.

Uncle_Code_Monkey’s picture

Uncle_Code_Monkey commented
Status: Active » Needs review

This sounds like it may have to do with another bug, recently fixed:
SQL Server driver in #1830024: Cannot execute db_change_field() to convert nvarchar to varbinary

lyricnz provided a patch for it, which I have modified slightly and found that it fixed my similar issues quite nicely.

See my comment for my modified and tested patch.

The workaround to remove any *_old fields still applies, though.

merlinofchaos’s picture

merlinofchaos commented
Status: Needs review » Closed (duplicate)

IF we're talking about a patch in another issue, let's call this a dup, not needs review.