PDOException: SQLSTATE[42000] Implicit conversion from data type nvarchar to varbinary(max) is not allowed

Question

I got this problem with my drupal 7.24, Windows server 2012, IIS8 and the sql server 2012.

I'm using the last dev version of sqlsrv which is suppose to fix a similar bug, but I still have this one.

PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. in dblog_watchdog() (line 160 of D:\inetpub\website\modules\dblog\dblog.module).

I've migrated my DB from a mysql to the sql server through the migration tool and using the drivers 5 and 3.51 to convert the schema. (got the same error in both case) BTW I have hexadecimal data sometime instead of normal data.

function dblog_watchdog(array $log_entry) {
  Database::getConnection('default', 'default')->insert('watchdog')
    ->fields(array(
      'uid' => $log_entry['uid'],
      'type' => substr($log_entry['type'], 0, 64),
      'message' => $log_entry['message'],
      'variables' => serialize($log_entry['variables']),
      'severity' => $log_entry['severity'],
      'link' => substr($log_entry['link'], 0, 255),
      'location' => $log_entry['request_uri'],
      'referer' => $log_entry['referer'],
      'hostname' => substr($log_entry['ip'], 0, 128),
      'timestamp' => $log_entry['timestamp'],
    ))
    ->execute();
}

On the dbo.watchdog I have a column call variables which is a varbinary(max) (used to be a LONGBLOB on mysql) and got hex data now. I think the problem is coming from here. When I comment those lines (insert query), it works, but with a lot of warning like:

Warning: array_flip(): Can only flip STRING and INTEGER values! in DrupalDefaultEntityController->load() (line 173 of D:\inetpub\website\includes\entity.inc).

Warning: Cannot modify header information - headers already sent by (output started at D:\inetpub\wendy\includes\common.inc:2700) in drupal_send_headers() (line 1216 of D:\inetpub\website\includes\bootstrap.inc)

PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. in variable_set() (line 975 of D:\inetpub\website\includes\bootstrap.inc).

Anyone got an idea how to fix that?

Answer 1

I've figured out the main problem, when I was using the SQL migration tool to migrate, it was creating a database, so I devided to create mine to check. The one created by the tool was Latin1_general_CL_AS and mine was SQL_Latin1_General_CP1_CI_AS instead. That seems to have fixed the problem