-1

I am migrating a Classic ASP application to a newer SQL Server database server.

The new server is a Windows 2019 Server (version 10.0.17763). I have an ADODB connection (version 10.0) to the SQL Server 2019 database (database is migrated from an older version).

It seems that the application runs almost perfectly, but among the dozens of ADODB queries that work fine is one that returns corrupted data.

UPDATED: It turns out that the only columns affected are indeed nvarchar(max) columns. As such, this is a duplicate of this question. My original question's detail follows:

If I run the stored procedure in SQL Management Studio, the results are perfect:

SELECT  dbo.[Staff.Reports].[Report Title], replace(dbo.[Staff.Reports].[Description],char(34),'') as RepDesc
FROM    dbo.[Staff.Reports] LEFT OUTER JOIN dbo.[Staff.Reports Permissions] ON dbo.[Staff.Reports].[Report ID] = dbo.[Staff.Reports Permissions].[Report ID]
WHERE   dbo.[Staff.Reports Permissions].[Staff ID] = @StaffID

Returns:

Report Title RepDesc
Achievements Admin Update OAA, and other Achievements
AdRef By Engineers AdRef By Engineers
Adref Status Count Ad Campaign report by ref no.

etc.

However, when the identical query is run through the Classic ASP code, using the ADODB connection, the data returned becomes corrupted:

Report Title RepDesc
Achievements Admin ] 0
AdRef By Engineers
Adref Status Count 0

Out of interest, the description column is an nvarchar(max) type. I established the content of the output by logging it to a text output file - it's not an issue with the display of the data.

The output is the same number of characters, but they are mostly null and control characters.

I have tried deleting and recreating the query, but am at a loss as to what is happening.

Can anyone help, please?

Thanks for your interest, AlwaysLearning. All queries are run using the same 'GetRecordset' function, which is basically:

Set oConnection = CreateObject("ADODB.Connection")
oConnection.Open "Data Source=TheDatabase;UID=Fred;Password=" & strPassword
oConnection.CursorLocation = 3
Set CreateConnection = oConnection

Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = CreateConnection
objComm.CommandType = adCmdStoredProc
objComm.CommandText = cQuery
objComm.CommandTimeout = 1800

For iThisParameter = 1 to UBound(aParameters)
    Set objParam = objComm.CreateParameter("@par" & iThisParameter, adVarChar, adParamInput, 1024)
    objComm.Parameters.Append objParam
    objComm.Parameters("@par" & iThisParameter) = aParameters(iThisParameter)
Next
Set GetRecordset = objComm.Execute
RS200Phil
  • 1
  • 3
  • _the description column is an nvarchar(max) type_ and _they are mostly null and control characters_ I think we'd need to see some of the Classic ASP code used around the SQL query. It sounds like it's assuming that that UCS-2 data in the `nvarchar(max)` column (where every other byte is generally going to be `0x00`) is some other character encoding and it's trying to render that using code page semantics. – AlwaysLearning Feb 26 '22 at 06:52
  • Thanks very much for your thoughts. What you describe sounds exactly the sort of scenario. I have added the surrounding code to the question. Can I force the encoding within the Classic ASP code? – RS200Phil Feb 26 '22 at 07:05
  • Run the exact same query in VBScript (or VBA, if you want the comfort of an IDE), just to rule out the thought that it might be ADODB's fault. – Tomalak Feb 26 '22 at 08:40
  • Does this answer your question? [Convert UTF-8 String Classic ASP to SQL Database](https://stackoverflow.com/questions/21866225/convert-utf-8-string-classic-asp-to-sql-database) – user692942 Feb 26 '22 at 09:14
  • Hi Tomalak. I have now changed the query to run directly from within the Classic ASP code, rather than call the SPROC. This returns the corrupt data in exactly the same way. I am pretty convinced that it's the code page now - I just don't know how to fix that. – RS200Phil Feb 26 '22 at 09:20
  • @RS200Phil the duplicate explains it. It’s an encoding mismatch issue. – user692942 Feb 26 '22 at 09:26
  • Thanks user692942. Guessing a bit, i tried adding "charset=utf-8" and "charset=ucs2" to the connection string. Neither was right, so I'll read on. However, do you think I should look at how the encoding is on the SQL database, to ensure it matches the original format? – RS200Phil Feb 26 '22 at 09:42
  • I really meant "take it out of ASP", not "run it in ASP in a different way". The point is to figure out what part of the mechanics are at fault here, because the solution depends on knowing. ADODB delivers *strings*, it does not deliver UTF-8 byte-streams. I don't think that reading how to decode UTF-8 in ASP Classic will help here, because that's likely not the issue. – Tomalak Feb 26 '22 at 09:48
  • 2
    I found this thread ["Strange problem with nvarchar(max) fields and Classic ASP"](https://stackoverflow.com/questions/3112093/strange-problem-with-nvarcharmax-fields-and-classic-asp), which seems related. (Generally speaking, you have ported the database to a new SQL Server. If you converted an existing column to `nvarchar(max)`, convert it back to what it was - or use `ntext` and ignore the deprecation warning.) – Tomalak Feb 26 '22 at 09:50
  • That's a really interesting read - thanks again. Lots of thoughts there. I will try a few things in a while and let you know how it goes. I need a couple of hours away from the screen now! – RS200Phil Feb 26 '22 at 10:35
  • Does this answer your question? [Strange problem with nvarchar(max) fields and Classic ASP](https://stackoverflow.com/q/3112093) – user692942 Feb 28 '22 at 09:18

1 Answers1

0

You should use the modern, supported driver for ADO to connect to SQL Server.

Using ADO with OLE DB Driver for SQL Server

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thanks very much, David / Dan. I'll give that a go. I read the info in the link and it seems very relevant, so I'm hopeful! Will let you know how it goes. – RS200Phil Feb 28 '22 at 08:11
  • This was the solution :). Thanks very much indeed to all of you. Without the help of you all I don't think I would have found it. The issue is now fully resolved. Thank you all for your pointers. – RS200Phil Feb 28 '22 at 08:51
  • @RS200Phil in which case mark your question as a [duplicate of this question](https://stackoverflow.com/a/3112421/692942) which was answered 10 years ago. – user692942 Feb 28 '22 at 09:20
  • 1
    Sorry 692, in my mind, when I searched the questions, I didn't find that one - I hadn't fully appreciated the issue until I had everyone's responses. I'll mark as a duplicate and hopefully it will help someone else. With respect to it being a 10-year-old answer - I totally agree. The sooner these guys can offload their legacy systems the sooner I can fully retire :) With you on that one - and they're working on it. – RS200Phil Mar 01 '22 at 11:51