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. 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
CodePudding user response：
Ok, so the great news is that, after numerous different tests, I have been able to get the output at last. This was resolved by using:
Convert (nvarchar(255),RepDesc) as [RepDesc]
That's fine, but it's a huge application (which I didn't write) and I have no idea how many sprocs and views might be affected for this column. Furthermore, this isn't the only nvarchar(max) column, so it could me compound changes.
More worrying is that If I continue down this path I have no doubt that there will be an update somewhere which ends up truncating the original data value before concatenating the new one, so data will be lost.
Before I go down that road, then, I am going to try the conversion to the depracated ntext data type, to see if that resolves it. If it does, that will be my solution. The client is looking to replace the system in due course, so this should be ok for them.
If that fails I'll review the code page, too.
Thank you so much for your contributions, all. Really appreciate your pointers.
CodePudding user response：
You should use the modern, supported driver for ADO to connect to SQL Server.