Getting error
Hi, I wrote a code in VB Script to query on database and result gets write in file, but there is one problem with on column in query, if I remove that column from query then code working very fine and runs but with that column it’s giving error “invalid procedure call or argument” That column is “dbo.V_AGENT_BEHAVIOR_LOG.PARAMETER” if I removed it then script working. I also checked Database table and if I execute query on table then it shows result but in code giving error. I am also giving information of database table entries for help: Below is the code for the same:
Please help.
Software/Hardware used:
strsql = "SELECT dbo.V_AGENT_BEHAVIOR_LOG.PARAMETER, dbo.V_AGENT_BEHAVIOR_LOG.HOST_NAME, dbo.IDENTITY_MAP.NAME FROM dbo.V_AGENT_BEHAVIOR_LOG INNER JOIN dbo.IDENTITY_MAP ON dbo.V_AGENT_BEHAVIOR_LOG.GROUP_ID = dbo.IDENTITY_MAP.ID WHERE (dbo.V_AGENT_BEHAVIOR_LOG.RULE_NAME = N'Log files written to USB drives_Write File')" dim fso, conn outputFile = "c:output.csv" ' Path and file name of the output CSV file 'Create filesystem object set fso = CreateObject("Scripting.FileSystemObject") 'Database connection info set Conn = CreateObject("ADODB.connection") Conn.ConnectionTimeout = 60 Conn.CommandTimeout = 60 conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";") sub MakeDataFile(fPath, strsql) dim a, showList, intcount set a = fso.createtextfile(fPath) set showList = conn.execute(strsql) for intcount = 0 to showList.fields.count -1 if intcount <> showList.fields.count-1 then a.write """" & showList.fields(intcount).name & """," else a.write """" & showList.fields(intcount).name & """" end if next a.writeline "" do while not showList.eof for intcount = 0 to showList.fields.count - 1 if intcount <> showList.fields.count - 1 then ‘ Error is coming on this line for PARAMETER column a.write """" & showList.fields(intcount).value & """," else a.write """" & showList.fields(intcount).value & """" end if next a.writeline "" showList.movenext loop showList.close set showList = nothing set a = nothing end sub ' Call the subprocedure call MakeDataFile(outputFile,strsql) ' Close set fso = nothing conn.close set conn = nothing if email <> "" then dim objMessage Set objMessage = CreateObject("CDO.Message") objMessage.Subject = "Test Email from vbs" objMessage.From = "test@rdd.com" objMessage.To = "test@rdd.com" objMessage.TextBody = "Please see attached file." objMessage.AddAttachment outputFile objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort objMessage.Configuration.Fields.Update objMessage.Send end if 'You're all done!! Enjoy the file created. msgbox("Data Writer Done!") //--------------------------------- Table Entries: PARAMETER HOST_NAME IDENTITY_MAP E:/ global-8c9e4616f Administrator E:/Approval List.xlsx global-8c9e4616f Administrator E:/AET/1079_5986_licence renewal.pdf global-8c9e4616f Administrator E:/AET/ABS_14_03_2015.pdf global-8c9e4616f Administrator //-------------------- Please help.
Software/Hardware used:
ASKED:
November 16, 2011 5:43 AM
UPDATED:
May 9, 2013 7:23 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
You might want to edit your question or post your script again using the editor’s “code” tool. I doubt someone will want to review it as it is right now.
As for the error, is there something different in this problematic field’s data ? Maybe special characters ?
I am dealing with a VB application that generates this error (less now than it did a while ago).
By far the most common cause is failure to deal with NULL values in the results. Adding code to check and resolve NULL results cleaned up a lot of these problems. You may want to add some explicit NULL tests for the Parameter column.
The second most common problem was code that “assumed” columns with “number” in the name only contained numeric data and tried to assign the column values to a numeric variable.
Last, if the Parameter column is defined as something larger than VARCHAR(2000) (e.g., VARCHAR(MAX), TEXT, etc.), then you may need to rewrite the VB code to deal with the data in “chunks”. Assuming you are using SQL Server, open the Books Online and search for “chunk”. There is plenty of material on how to handle large text columns.