Error message when running vbscript on SQL Server

sql server , vbscript , 07002 , error message United States
  • 12 years ago
    I have a job on SQL server that includes vbscript, everytime I try to run it though, I get this error message: "COUNT field incorrect or syntax error [SQLSTATE 07002] (Error 0). The step failed." I'm not sure what is wrong, so could someone take a look at my code and maybe provide some insight? 1 Dim NewFile 2 Dim OldFile 3 Dim JudgesFile 4 Dim strSQL 5 Dim rsDoc 6 Dim XMLString 7 Dim fso1 8 Dim fso2 9 Dim fso3 10 Const ForWriting = 2 11 Const ForReading = 1 12 Dim JudgeFileLine 13 Dim OldFileLine 14 Dim dbconnect 15 16 'Create an instance of the File System Object for each file needed 17 Set fso1 = CreateObject ("Scripting.FileSystemObject") 18 Set fso2 = CreateObject ("Scripting.FileSystemObject") 19 Set fso3 = CreateObject ("Scripting.FileSystemObject") 20 21 'Connect to the Database 22 Set dbconnect = CreateObject("ADODB.Connection") 23 dbconnect.Open "DRIVER={SQL Server}; SERVER=localhost; UID=sa; PWD=password; Database=supreme" 24 25 'Create Recordset 26 Set rsDoc = CreateObject("ADODB.Recordset") 27 28 'Query the database to gather the presiding judge information, results are shown as xml 29 strSQL = "SELECT JudicialOfficerID" + "'" + "@id" + "'" + ", isnull(NamePrefix," + "'" + "'" + ") as prefix, isnull(FirstName," + "'" + "'" + ") as firstName, isnull(MiddleName," + "'" + "'" + ") as middleName, isnull(LastName," + "'" + "'" + ") as lastName, isnull(NameSuffix," + "'" + "'" + ") as suffix FROM JudicialOfficer LEFT JOIN JudicialStatus ON JudicialOfficer.JudicialStatusID = JudicialStatus.JudicialStatusID WHERE JudicialType=" + "'" + "Judge" + "'" + "and Title is not null AND FirstName not like" + "'" + "%Judicial District Cour%" + "'" + "AND (JudicialStatus.JudicialStatus Like" + "'"+ "Active" + "'" + ") ORDER BY FullName1 FOR XML path(" + "'"+ "judge" + "'" + "), root(" + "'" + "judges" + "'" + ")" 30 31 rsDoc.Open strSQL, dbconnect 32 33 'Create a xml file for the query results 34 Set JudgesFile = fso1.OpenTextFile("C:\CMS Application Files\Efiling XML\Judges.xml", ForWriting, True) 35 36 'Write the recordset data into the judges xml file 37 Do Until rsDoc.EOF 38 XMLString = Replace(CStr(rsDoc.Fields(0)), "><", ">" & vbCrLf & "<") 39 JudgesFile.Write XMLString 40 rsDoc.MoveNext 41 Loop 42 43 JudgesFile.close 44 45 Set rsDoc = Nothing 46 If Not dbConnect is Nothing then 47 dbConnect.Close 48 End if 49 Set dbConnect = Nothing 50 51 'Create a new XML file for writing, Open the old XML file for reading, Open the judges file for reading 52 Set JudgesFile = fso1.OpenTextFile("C:\CMS Application Files\Efiling XML\Judges.xml", ForReading, False) 53 Set NewFile = fso2.OpenTextFile("C:\CMS Application Files\Efiling XML\test.xml", ForWriting, True) 54 fso3.CopyFile "\\EflexTest\c$\jboss-3.2.3\server\default\conf\DistrictCourtFile.xml","\\cmstest\c$\CMS Application Files\Efiling\DistrictCourtFile.xml", True 55 Set OldFile = fso3.OpenTextFile("\\cmstest\c$\CMS Application Files\Efiling\DistrictCourtFile.xml", ForReading, False) 56 57 'Read the old XML file and write each line to the new xml file until reaching the judges element 58 Do Until OldFile.AtEndOfStream 59 OldFileLine = OldFile.ReadLine 60 If OldFileLine = " " or OldFileLine = "" then Exit Do 61 Else 62 NewFile.WriteLine OldFileLine 63 End If 64 Loop 65 66 'Add in the new judges element 67 Do Until JudgesFile.AtEndOfStream 68 JudgeFileLine = JudgesFile.ReadLine 69 NewFile.WriteLine JudgeFileLine 70 Loop 71 72 'Read the old xml file after the judges element until the end of the file and write each line to the new xml file 73 Do until OldFile.AtEndOfStream 74 OldFileLine = OldFile.ReadLine 75 If OldFileLine = " " or OldFileLine = "" then 76 Do until OldFile.AtEndOfStream 77 OldFileLine = OldFile.ReadLine 78 NewFile.WriteLine OldFileLine 79 Loop and Exit Do 80 End If 81 Loop 82 83 NewFile.close 84 OldFile.close 85 JudgesFile.close Thanks in advance!

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The trouble with programmers is that you can never tell what a programmer is doing until it's too late.” - Seymour Cray