Why does my script seem to randomly truncate strings?

Question

The script below basically takes a source table and a target table and copies all the records. It works fairly well, but one recurring issue I'm having is that it seems to truncate some strings. Here is one of the error outputs:

Error Number: -2147217900 Description: [Microsoft][ODBC SQL Server Driver][SQL S erver]Unclosed quotation mark after the character string 'STRINGSEGMENT^urn:uuid:e9 e91fe151-5w4c-12e1-bac5-25b3a0'.

INSERT INTO TableName VALUES ('23189','23189','','','1^^','','12/5/2013 3:37:2 2 PM','fieldvalue','','somethinghere','somethinghere','12/5/2013 9:37:22 AM','123456','1234568798','STRINGSEGMENT^urn:uuid: e91fe151-5w4c-12e1-bac5-25b3a0

Query is 584 characters long

If you look at the source data, the string that is truncated looks something like this:

STRINGSEGMENT^urn:uuid:e91fe151-5w4c-12e1-bac5-25b3a0004b00^STRINGSEGMENT

So it's cutting it off after the 53rd character (highlighted). The entire length of tSQL is only 584 characters long.

Why is this happening?

WScript.Echo "Setting Vars..."
Dim sConnect, tConnect, resultSet, r
Dim sDSN, sUserName, sPassWord
Dim tDSN, tUserName, tPassWord
Dim value
 
sDSN      = "mydsn"
sUsername = "myusername"
sPassword = "mypassword"
tDSN      = "LOCAL"
tUsername = "myusername"
tPassword = "mypassword"
sTable    = "sourceTable"
tTable    = "targetTable"

sSQL      = "" 'see below
sDSN      = "DSN=" & sDSN & ";UID=" & sUsername & ";PWD=" & sPassword & ";"
tSQL      = "Select TOP 1 ID FROM " & tTable & " ORDER BY ID Desc"
tDSN      = "DSN=" & tDSN & ";UID=" & sUsername & ";PWD=" & sPassword & ";"



Set sConnect = CreateObject("ADODB.Connection")
WScript.Echo "Opening connection to source..."
sConnect.Open sDSN

Set tConnect = CreateObject("ADODB.Connection")
WScript.Echo "Opening connection to target..."
tConnect.Open tDSN

WScript.Echo "Finding Current Record..."
Set r = tConnect.Execute(tSQL)
On Error Resume Next
r.MoveFirst
if r.eof Then currentRecord = 1
Err.Clear

Do While Not r.eof
  currentRecord = r("ID") + 1
  r.MoveNext
Loop
r.Close

sSQL ="Select * FROM " & sTable & " WHERE ID >= " & currentRecord

WScript.Echo "Beginning shadow at record " & currentRecord & "..."
Set resultSet = sConnect.Execute(sSQL)

resultSet.MoveFirst
Do While Not resultSet.eof
  On Error Resume Next
  tSQL = "INSERT INTO " & tTable & " VALUES ('"
  For i = 0 To resultSet.fields.Count - 1
     if NOT IsNull(resultSet(i)) Then
        value = replace(resultSet(i),"'","")
        'somewhere around here
     else
        value = ""
     End If
     tSQL = tSQL & value
     if i < resultSet.fields.Count - 1 Then 
        tSQL = tSQL & "','"
     end if
  Next
  tSQL = tSQL & "')"
  'when the error occurs, the line above doesn't seem to be processed but the line below obviously is...
  tConnect.Execute(tSQL)
  If (Err.Number <> 0) Then
    WScript.Echo "Error Number: " & Err.Number & " Description: " & Err.Description
    WScript.Echo tSQL
    WScript.Echo "Query is " & Len(tSQL) & " characters long"
    WScript.StdIn.ReadLine
    Err.Clear
  End If
  tSQL = ""
  resultSet.MoveNext
Loop

resultSet.Close
sConnect.Close
Set sConnect = Nothing

tConnect.Close
Set tConnect = Nothing
WScript.Quit(0)

It's `00` in `...-25b3a0` **00** `4b00^STRINGSEGMENT`. I think a byte with `0x00` value considered being a string terminator... (I don't know `STRINGSEGMENT` meaning and where it comes from)... — JosefZ, May 05 '15 at 15:50
Also STRINGSEGMENT is text I used to replace the actual text which is private. :) I don't think it has any relevance on this issue. — , May 05 '15 at 15:57
Is all that relevant? Why would it treat it as anything other than a string? — , May 05 '15 at 16:25
Add `Wscript.Echo tSQL` somewhere by `tSQL = tSQL & value` or at least _before_ `tConnect.Execute(tSQL)` to see your script sends good value... (or reads a wrong one?) — JosefZ, May 05 '15 at 16:29
Good idea. It's not the INSERT. It's coming out of the source that way for some reason... — , May 05 '15 at 20:46

score 0 · Answer 1 · answered May 05 '15 at 20:56

I don't know why this is happening, but here is my workaround. I will not accept this as an answer, just wanted to document.

Function allowed(n)
    allowed = true
    if n = 13 Then allowed = false
    if n = 14 Then allowed = false
End Function


Function sanitize(v,i) 'v=value i=index
    mystr = ""
    if allowed(i) Then
        if Not IsNull(v) Then
            mystr = replace(v,"'","")
        End If
    end if
    sanitize = mystr
End Function

Basically I'm just manually excluding the columns that have a problem. Notice that I identified a second one. What's really curious is that columns 12 and 13 have identical data in the source database, but column 12 copies fine.

Why does my script seem to randomly truncate strings?

1 Answers1