Getting error

5 pts.
Tags:
VB script
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:

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.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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 ?
    67,880 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following