ASP classic: What determines character encoding for ADODB?

Question

If there is any input or output on an ASP page this sort of thing works like a charm:

<%@ LANGUAGE="VBScript" CodePage = 65001 %>
<% 
  Response.CharSet = "UTF-8"
  Response.CodePage = 65001
%>

But if it's an ASP page executing pure VBS and I pass a non-latin character string as a parameter to an MS SQL sproc via Server.CreateObject("ADODB.Command") then it's turned into a series of ? characters somewhere between when the command is Executed and MS SQL puts it into a varchar column.

However, I do have one instance of IIS on which the same ASP pages will insert such strings into the database with UTF-8 encoding. Since I can't find the IIS setting that allows that to work, how can I force an ADODB.Command Parameter value to be converted to UTF-8?

If you are passing UTF-8 encoded characters to SQL Server they should be being placed in an `NVARCHAR` column not a `VARCHAR`, that will explain the encoding mismatch you are encountering. Parameter wise you should be using a data type of `adVarWChar` or `adLongVarWChar` respectively. — user692942, Aug 16 '16 at 21:09
@Lankymart - I'll test using that ADO data type flag right now. I know the DB column *should* be an NVARCHAR, but it's a legacy system. Any insight into [how the very same database and code could have received UTF-8-encoded strings when the ASP is run from a different IIS server](http://serverfault.com/q/796971/350508)? — feetwet, Aug 16 '16 at 22:41
Interesting question, is the same provider being used on both servers? It also depends entirely on what the page is doing i.e posting data from a form for example. — user692942, Aug 16 '16 at 23:51
@Lankymart: All this particular sequence was doing was some REST via MSXML2.ServerXMLHTTP and calling some sprocs. I just posted the fix as an answer ... though the answer really raises more questions. Maybe the biggest question is, "Where did I go wrong that I'm working on an ASP classic system?" ;) — feetwet, Aug 17 '16 at 00:43

score 1 · Accepted Answer · edited May 23 '17 at 12:33

ASP character encoding can be a fickle thing. In a codebase of 169 ASP files one had been saved with UTF-8-BOM encoding. It was deployed to one machine but not the other. All it contained was some VBS FUNCTION definitions. But any ASP that included that file with BOM encoding was immediately rendered incapable of appending an ADODB.Command Parameter in UTF-8. Instead it apparently decided – but only for ADODB.Command purposes, not for, say, JSON PUT over MSXML2.ServerXMLHTTP objects – that everything that wasn't ASCII was ?.

That's it: Save a codefile as UTF-8-BOM and ASP breaks. Save it as UTF-8 it works again.

How can you discover that one of your 169 ASP files spread across a few dozen directories is saved using the "wrong" encoding? I don't know. I got lucky, happened to know where recent work had been done, and happened to check the file's encoding in Notepad++.

Is it just having two different encodings that confuses classic ASP? I don't know, but here's at least one other answer that suggests that BOM encoding causes problems.

ASP classic: What determines character encoding for ADODB?

1 Answers1