Microsoft KB Archive/202621

= FIX: SQL PassThrough May Return Incorrect String =

Article ID: 202621

Article Last Modified on 6/30/2004

-

APPLIES TO


 * Microsoft Visual Basic 4.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 3
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 4
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 5
 * Microsoft Data Access Objects 3.51

-



This article was previously published under Q202621



SYMPTOMS
When you use the DbSQLPassThrough constant in Data Access Objects (DAO) version 3.51, if the text is exactly 256 characters long, the last character is truncated and replaced with a "0" character. The same behavior also occurs if the text is exactly 511, 766, or 1,021 characters (and so on).



CAUSE
This error occurs because of UNICODE conversion problems that are located in the DAO/Jet SQLPassThrough engine.



RESOLUTION
You can use one of the following workarounds:
 * Upgrade to DAO 3.6/Jet 4.0.
 * Use ODBCDirect to perform the query.
 * Use Remote Data Objects (RDO) to perform the query.
 * Use ActiveX Data Objects (ADO) to perform the query.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

This bug was corrected in DAO 3.6/Jet 4.0. To use DAO 3.6 in your application, select the DAO 3.6 reference instead of the DAO 3.51 reference.



Steps to Reproduce Behavior
  Use the SQL script below to create a SQL Server table in the PUBS database. /*** Object: Table dbo.tblTest ***/ CREATE TABLE dbo.tblTest ( Test text NOT NULL ) GO

insert into tblTest (Test) VALUES (SPACE(255)) insert into tblTest (Test) VALUES (SPACE(256))  Create a Visual Basic 6.0 Standard EXE project. Form1 is created by default. Add a Command button to Form1. Add a reference to the Microsoft DAO 3.51 Object Library.  Insert the following code in the Command1_Click event.

Note You must change SERVER=<your SQL Server>, UID= and PWD= to the appropriate servername, username, and password before you run this code. Make sure that the changed UID has the appropriate permissions to perform the required operations on the specified database. Dim DB As Database Dim WS As Workspace Dim RS As Recordset Dim ssql As String, ConnectString As String

ConnectString = "ODBC;DRIVER={SQL SERVER};SERVER=<your SQL Server>;" & _ "DATABASE=pubs;UID= ;PWD= ;" Set WS = Workspaces(0) Set DB = WS.OpenDatabase("", dbDriverPrompt, False, ConnectString) ssql = "Select * from tblTest" Set RS = DB.OpenRecordset(ssql, dbOpenSnapshot, dbSQLPassThrough) If RS.RecordCount > 0 Then Do Until RS.EOF Debug.Print Right(RS("Test"), 10) & ":" & Len(RS("Test")) & _ ":" & Asc(Right(RS("Test"), 1)) 'Notice that any item of text that is 256, 511, 766, 1,021... 'returns character 0 in the last character, and the 'actual last character is lost. RS.MoveNext Loop End If   RS.Close DB.Close WS.Close Set RS = Nothing Set DB = Nothing Set WS = Nothing </li> Modify the connection string to point to your SQL Server.</li> Notice that when you get the data back, it is truncated to the first character.</li></ol>

Additional query words: GetChunk AppendChunk ODBCDirect Direct SQLPassthrough

Keywords: kbbug kbfix kbdatabase KB202621

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.