Two different servers, both running SQL Server 2008 R2.
On both servers we run the same command:
INSERT INTO Docs(IMG64)
VALUES (CONVERT(VARBINARY(max), '/9j/4AAQSkZJRgABAQAAAQABAAD...')
Naturally, the actual value of Img64 is a lot longer than what is displayed here.
The actual data type of Img64 in table Docs is VARCHAR(MAX).
On server X, we get the following error:
Explicit conversion from data type text to varbinary(max) is not allowed. (SQLSTATE=22018) (529) (Severity=16) (MsgState=2)
On server Y, with the exact same statement to the exact same table, no error is received.
We have already tried rebuilding the table on Server Y according to the table build of Server X, but to no avail.
We have tried running the same SQL statement on the same table build in several other versions of SQL Server, including 2005, 2008, and 2014, without issue.
Can anyone help me to understand what is causing this issue? Any help would be greatly appreciated.
Note: The compatibility level of both servers (2008R2) is set to 90. The compatibility of the other servers (2005,2008,2014) is set appropriately to each different instance, respectively.
Are the databases on these two servers using different compatibility levels?
Certain conversion functions behave differently, depending on the compatibility level of your database. Compatibility level 80 (SQL Server 2000) would for instance not like the datatypes like varchar(max) and varbinary(max) which were introduced in SQL Server 2005 (compatibility level 90)