VBScript pull records from SQL Serv & save 2 file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

VBScript pull records from SQL Serv & save 2 file

Author

Topic

abylin1
Starting Member

4 Posts

Posted - 2009-12-09 : 10:50:00

Hello,

Caveat: I am BRAND NEW to VBScript and SQL Server!

Given an IP address, username, and password, my task is to connect to a SQL Server database using a VBScript and pull all records from one table and save each row in the recordset to individual files on my local filesystem, which is running Windows Server 2003.

The table I need to pull from has columns of the following datatypes: NVARCHAR, NTEXT, IMAGE, DATETIME, INT, UNIQUEIDENTIFIER, and BIT.

So far, I have been able to connect to the database but I don't know how to loop through each row in the recordset and save each row off to a file on my filesystem. The saved file can be a txt file or xml. This is my code so far....

option explicit

const ADTYPETEXT = 2
const ADOPENKETSET = 1
const ADLOCKOPTIMISTIC = 3

dim oCn
dim oRS
dim oSt

set oCn = Wscript.CreateObject("ADODB.Connection")
oCn.ConnectionString = "Driver={SQLServer};Server=<ip address>;Uid=<username>;Pwd=<password>;"
oCn.Open

Dim strSQLQuery
strSQLQuery = "Select * from DMS_DisciplinaryReport"

Set oRS=CreateObject("ADODB.Recordset")
Set oRS = oCn.Execute(strSQLQuery)

while not oRS.eof

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-09 : 12:30:45

something like this. but some of those datatypes may not be what you expect in a text file, depending on what they're holding

option explicit

const ADTYPETEXT = 2
const ADOPENKETSET = 1
const ADLOCKOPTIMISTIC = 3

dim oCn
dim oRS
dim oSt

dim fso
Dim tst
Dim i

i = 0
Set fso = CreateObject("Scripting.FileSystemObject")


set oCn = Wscript.CreateObject("ADODB.Connection")
oCn.ConnectionString = "Driver={SQLServer};Server=<ip address>;Uid=<username>;Pwd=<password>;"
oCn.Open

Dim strSQLQuery
strSQLQuery = "Select * from DMS_DisciplinaryReport"

Set oRS=CreateObject("ADODB.Recordset")
Set oRS = oCn.Execute(strSQLQuery)

while not oRS.eof
	Set tst = fso.OpenTextFile("File" & i, 2, True)
	For Each fld in oRS.Fields
		tst.Write oRS(fld).Value & Char(9)
	Next
	tst.close
	Set tst = Nothing
	i = i + 1
	oRS.MoveNext
Wend

oRS.Close
Set oRS = Nothing
Set fso = Nothing

abylin1
Starting Member

4 Posts

Posted - 2009-12-09 : 14:01:21

Hi Russell,

Thank you for helping me get a little closer. I implemented your code but I have a few questions. First, I got an error saying "fld" is not recognized. Is "fld" the correct syntax in the For loop? When I ran your code, it output only one file in the same directory where I ran the script from, and the file was named "File0" and was of size 0 KB. I know for sure this table has at least 55,000 records in it.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-09 : 14:49:26

Add Dim fld where the other Dim statements are.

abylin1
Starting Member

4 Posts

Posted - 2009-12-09 : 15:31:52

Yep - I figured that out... I am still getting an error "Line 34: Item cannot be found in the collection corresponding to the requested name or ordinal". I did some Googling and it seems like I needed to specify the column names explicitly in my SELECT statement, so I tried to specify just one column name but am still getting that error. It is referring to the tst.Write oRS(fld).Value & Char(9) line.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-09 : 15:53:06

Ok, sorry, I should've tested it b4 posting. Thsi works:


option explicit

const ADTYPETEXT = 2
const ADOPENKETSET = 1
const ADLOCKOPTIMISTIC = 3

dim oCn
dim oRS
dim oSt

dim fso
Dim tst
Dim fld
Dim i

i = 0
Set fso = CreateObject("Scripting.FileSystemObject")


set oCn = Wscript.CreateObject("ADODB.Connection")
oCn.ConnectionString = "Driver={SQLServer};Server=<ip address>;Uid=<username>;Pwd=<password>;"

oCn.Open

Dim strSQLQuery
strSQLQuery = "Select * from DMS_DisciplinaryReport"

Set oRS=CreateObject("ADODB.Recordset")
Set oRS = oCn.Execute(strSQLQuery)

while not oRS.eof
	Set tst = fso.OpenTextFile("File" & i & ".txt", 2, True)
	For Each fld in oRS.Fields
		tst.Write oRS(fld.Name) & Chr(9)
	Next
	tst.close
	Set tst = Nothing
	i = i + 1
	oRS.MoveNext
Wend

oRS.Close
Set oRS = Nothing
Set fso = Nothing

abylin1
Starting Member

4 Posts

Posted - 2009-12-10 : 16:34:56

Thank you, this worked perfectly.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-10 : 16:42:05

you're welcome (sorry it took 3 tries lol). should add this at the end too:

oCn.Close
Set oCn = Nothing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources