Error message when running vbscript on SQL Server

Dave Patrick

2008-12-21 18:32:27 UTC

Might be this applies.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=356610
--
Regards,

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

Post by smiths37
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
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!