June 3, 2009 at 7:47 am
I've written a DTS job that uses vbscript to extract data from an Informix database and then insert it into a table in SQL Server 2000.
I'm trying to build as much logic into the code to make it as efficient as possible, but his a snag with the above error.
The error crops up on this line in the ActiveX script:
cnDW.Execute "INSERT INTO xINVserial_stg SELECT '" & varOrder & "', " & varLine & ", " & varSeq & ", " & varSuffix & ", " & LTRIM(RTRIM(varSerial)) & ",'" & varUserName & "', " & LTRIM(RTRIM(varSerial)) & ", " & varQty & ", " & ISNUMERIC(LTRIM(RTRIM(varSerial))) & ", NULL"
Specifically, it's the ISNUMERIC(LTRIM(RTRIM(varSerial))) bit, as the code works if I replace this with NULL.
The target field for this is data type SMALLINT.
The full script is:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim cnSTX, rsXMC
Set cnSTX = CreateObject ("ADODB.Connection")
cnSTX.Open "baxti"
Set rsXMC = CreateObject ("ADODB.Recordset")
rsXMC.Open "SELECT soi_ordref AS so, sts_iline AS line, sts_iseq AS seq, sts_serial AS serial, sts_qty AS qty " & _
"FROM soitem, stserial WHERE soi_ordref = sts_idocref AND soi_seq = sts_iline AND soi_linseq = sts_iseq AND sts_isource = '1' " & _
"AND soi_ordref = '" & DTSGlobalVariables("gvSalesOrder").Value & "' AND soi_suffix = " & DTSGlobalVariables("gvSuffix").Value , cnSTX, , ,adCmdText
Dim cnDW, varOrder, varLine, varSeq, varSerial, varQty, varUserName, varSuffix
Set cnDW = CreateObject ("ADODB.Connection")
cnDW.Open = "Provider=sqloledb;Data Source=(local);Initial Catalog=STXdocmnger;Integrated Security=SSPI"
Set varOrder = rsXMC("so")
Set varLine = rsXMC("line")
Set varSeq=rsXMC("seq")
Set varSerial=rsXMC("serial")
Set varQty=rsXMC("qty")
' Get username and suffix
varUserName=DTSGlobalVariables("gvUsername").Value
varSuffix= DTSGlobalVariables("gvSuffix").Value
Do Until rsXMC.EOF
cnDW.Execute "INSERT INTO xINVserial_stg SELECT '" & varOrder & "', " & varLine & ", " & varSeq & ", " & varSuffix & ", " & LTRIM(RTRIM(varSerial)) & ",'" & varUserName & "', " & LTRIM(RTRIM(varSerial)) & ", " & varQty & ", " & SNUMERIC(LTRIM(RTRIM(varSerial))) & ", NULL"
rsXMC.MoveNext
Loop
rsXMC.Close
cnSTX.Close
cnDW.Close
Set varOrder = Nothing
Set varLine = Nothing
Set varSeq = Nothing
Set varSerial = Nothing
Set varQty = Nothing
Set varUsername = Nothing
Set varSuffix = Nothing
Main = DTSTaskExecResult_Success
End Function
I've looked around for some anwers on the Net, but all I can find is some discussion about pre .NET VB equating true to -1 and false to 0, although I would have thought this would only have affected me in I used TINYINT for my target field data type. The code runs fine in Query Analyzer, so I'm a bit stuck for inspiration at the moment.
Any kind help out there?
Regards,
Mark
June 3, 2009 at 8:19 am
I think I've managed to fix this myself. If I add ABS to the code, it obviously removes the sign from the returned value and all seems well.
My new code is:
cnDW.Execute "INSERT INTO xINVserial_stg SELECT '" & varOrder & "', " & varLine & ", " & varSeq & ", " & varSuffix & ", " & LTRIM(RTRIM(varSerial)) & ",'" & varUserName & "', " & LTRIM(RTRIM(varSerial)) & ", " & varQty & ", " & ABS(ISNUMERIC(LTRIM(RTRIM(varSerial)))) & ", NULL"
Online Books is clear that the returned value from ISNUMERIC is a integer, so I still don't understand why it failed before, but I'm sure you clever people out there do.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply