Newbie trying to put SQL Query into a VBScript

[gbpacke...@gmail.com]

I'm new to this whole scripting / sql game and I'm trying to automate
some reporting on a project of mine. Each day, I log into 5 differnt
servers and run a small program that returns any error counts. THis
data is stored in one central sql database and is much easier when I
run an SQL query on the database, rather than to have to log in to each
of the servers each morning.

Here's my problem, I have a script that I'm making to help automate
other processes that I do, and I want to add the SQL query into the
vbscript to automatically pull from the database, then write the
results of that query to a text file.

I do not know how to do this and would REALLY appriciate anyone's help
in this matter.

Thanks.

[gbpacke...@gmail.com]

Sorry for not putting this in sooner. . .here is the code I'm trying to
make work. The error I get when I run it is: Error: Type Mismatch:
'Write'

Not sure what that is all about. . help is appriciated.

set cnn=createobject("ADODB.Connection")
cnn.open
("Provider=SQLOLEDB;server=SERVER;database=DB;uid=User;password=PWD")
set rs=cnn.execute("SELECT Retry AS Tries, COUNT(*) AS Docs FROM
dbo.DB_Batch GROUP BY Retry")
Set objFile = objFSO.OpenTextFile (("\\UNCPATH\Script_Results.txt"),
forAppending, createFile)
strWrite = rs
objFile.Write strWrite
objFile.Close
Wscript.Echo "Script is Finished with SQL Reporting. . . .moving onto
**NEXT**"

[Richard Mueller]

VBScript can use ADO to connect to SQL Server databases and run T-SQL
queries. The trick is to get the connection string. If the database uses
Windows integrated authentication, something similar to this should work.
=======
Option Explicit
Dim strConnect, adoConnection, strSQL, adoRecordset
Dim strField1, strField2

' Connection string. If the database is in the default instance, omit the
backslash
' and instance name.
strConnect = "DRIVER=SQL Server;" _
& "Trusted_Connection=Yes;" _
& "DATABASE=MyDatabase;" _
& "SERVER=MyServer\MyInstance"

' Open ADO connection to SQL Server database.
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.ConnectionString = strConnect

' Create ADO recordset object.
Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoConnection

' Construct T-SQL query.
strSQL = "SELECT Field1, Field2 " _
& "FROM MyTable " _
& "WHERE FieldID = 23"

' Run the query and create resulting recordset.
adoRecordset.Source = strSQL
adoRecordset.Open

' Enumerate the recordset.
Do Until adoRecordset.EOF
strField1 = adoRecordset.Fields("Field1").Value
strField2 = adoRecordset.Fields("Field2").Value
Wscript.Echo strField1 & ", " & strField2
adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close
adoConnection.Close
==============
If the database uses SQL Server logins, you will need to pass a username and
password in the connection string. This site may help:

http://www.connectionstrings.com/

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

[Richard Mueller]

Assuming your connection string is good, rs is a recordset object, so it
cannot be echoed. You need to loop through the recordset, even if there is
only one row.
========
Do Until rs.EOF
objFile.Write rs.Fields("Tries").Value & ", " & rs.Fields("Doc").Value
rs.MoveNext
Loop
========
Or, if you are sure the is one and only one row, this should work:

ObjFile.Write rs.Fields("Tries").Value & ", " & rs.Fields("Doc").Value

I trust that your T-SQL statement is valid and returns one row.

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

<gbpacke...@gmail.com> wrote in message
news:1161710170....@m73g2000cwd.googlegroups.com...

[gbpacke...@gmail.com]

Thanks SOOOO much, my script works great and I thank you for helping me
out!!

~B.