FIX: SQL PassThrough May Return Incorrect String (202621)

MORE INFORMATION

Steps to Reproduce Behavior

1. 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))
   					

2. Create a Visual Basic 6.0 Standard EXE project. Form1 is created by default.
3. Add a Command button to Form1.
4. Add a reference to the Microsoft DAO 3.51 Object Library.
5. Insert the following code in the Command1_Click event.
   
   Note You must change SERVER=<your SQL Server>, UID=<username> and PWD=<strong password> 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=<username>;PWD=<strong password>;"
       
       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
   					

6. Modify the connection string to point to your SQL Server.
7. Notice that when you get the data back, it is truncated to the first character.