2

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.

3BK
  • 1,338
  • 1
  • 8
  • 11
  • Are the databases on these two servers using different **compatibility levels** ? – marc_s Jun 23 '16 at 09:49
  • Updated the post to include compatibility levels of the various servers. Both are set to 90 at present. – 3BK Jun 23 '16 at 09:55
  • So -- if the type of `Img64` is `VARCHAR(MAX)`, why are you taking a `VARCHAR(MAX)` value (assuming the constant is >8000 characters), converting it to `VARBINARY(MAX)`, then having that implicitly converted back to `VARCHAR(MAX)`? Any rhyme or reason to that? – Jeroen Mostert Jun 23 '16 at 10:11
  • @JeroenMostert : The actual length of the string is 72,792 characters. The reason for the conversions is dependent upon the programmer, so I can't provide you with a detailed answer. I believe it has something to do with the string value not being interpreted as "varchar" but as "nvarchar" or something else in its original state. Regardless, even if the conversion is useless, it works on 4 other servers, but not on that specific server...the question here is "Why?" – 3BK Jun 23 '16 at 10:19
  • 1
    How do you run the command? Is this a literal command you're testing in Management Studio now, or is there client code involved? If it's not clear, I'm trying to zoom in on what's actually going wrong rather than "what's different with this server". Specifically, it's hard to see where the `text` type is coming in. – Jeroen Mostert Jun 23 '16 at 10:37
  • If you try to insert with the help of variable `DECLARE @str varchar(max) = '/9j/4AAQSkZJRgABAQAAAQABAAD...'; INSERT INTO Docs(IMG64) VALUES (CONVERT(VARBINARY(max), @str);` – gofr1 Jun 23 '16 at 10:41
  • @JeroenMostert : I understand your position, and thank you very much for your help. At this stage, we are discussing an actual SQL command run as an independent query in SQL Server Management Studio for SQL Server, without any actual code interaction. The original error came from a synchronization application, but we have since copied the guilty query out of the log and run it as an independent query, and the error still occurs. – 3BK Jun 23 '16 at 10:49
  • Is there a trigger on the `Docs` table on the offending server? How about replication? Are you sure that `Docs` refers to what you think it does (try `dbo.Docs` with an explicit schema just in case)? ...other than those things I'm out of ideas. Books Online explicitly documents that character literals of more than 8000 bytes are of type `VARCHAR(MAX)`, not `TEXT`, and we've already excluded compatibility level as a potential problem. Maybe the error message is misleading, but then I have no idea what *is* going wrong. – Jeroen Mostert Jun 23 '16 at 10:55
  • 1
    @gofr1 : Defining the string as a variable actually did allow the update, which got me thinking. Upon subsequent checks, it was discovered that there **was** a problem with the compatibility levels after all. Thank you for helping me find the problem. – 3BK Jun 23 '16 at 11:20
  • @marc_s : Your original comment was actually correct, but for some reason the compatibility level displayed in two different Management Studio environments was different. This leads me to believe that there is a much deeper issue here, which I will continue to investigate. Thank you very much - I will gladly mark your comment as an answer if you open it as such. – 3BK Jun 23 '16 at 11:22
  • @JeroenMostert : Thank you for your help, and I apologize for any of your time that was wasted by this error. Apparently the entire server environment in the problematic location is victim to another error, which led to this one. Connecting from outside of the server results in an incorrect compatibility level being displayed. From within the server itself, the displayed level was 80, which I changed, instantly resolving the issue. Thank you again! – 3BK Jun 23 '16 at 11:24
  • It would be interesting to know if this is a limitation of Management Studio -- support for compatibility level 80 (SQL Server 2000) was dropped some time ago in SQL Server itself, but if that also affected newer versions of SSMS that's an awful gotcha. That would mean the only reliable way to find out is `SELECT [compatibility_level] FROM sys.databases WHERE name = ''`. (And if that's all I've learned from this question, my time was only partially wasted.) – Jeroen Mostert Jun 23 '16 at 11:27
  • @JeroenMostert : `SELECT [compatibility_level] FROM sys.databases WHERE name = ''` is exactly how I found out that the problem was actually related to compatibility level. – 3BK Jun 23 '16 at 12:11

1 Answers1

1

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459