Error message when running vbscript on SQL Server

External Since: Dec 11, 2008 Posts: 1

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 = " <judges>" or OldFileLine = "<judges>" 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 = " </judges>" or OldFileLine = "</judges>" 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!

External Since: Nov 11, 2006 Posts: 12

Might be this applies.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Feed...kID=356

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"smiths37" wrote:
>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 = " <judges>" or OldFileLine = "<judges>" 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 = " </judges>" or OldFileLine = "</judges>" 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!
>