vbscript / sql

[vsantoro]

I created a sql server odbc connection, tested and works.
I am using this data exporter code i got online to run a sql query in sql server via vbscript and export to a csv file.

But I keep getting error..
line 47
char 2
provider is not specified and there is no designated default provider.
adodb.connection

I already created a odbc connection and tested it, it works. here is code.

option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "sqlserver" ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "3.xx.xx.xx" ' Hostname of the database server
dbName = "dbname" ' Name of the database/SID
dbUser = "123456" ' Name of the user
dbPass = "5678978" ' Password of the above-named user
outputFile = "c:\output.csv" ' Path and file name of the output CSV file
email = "email@me.here" ' Enter email here should you wish to email the CSV file (as attachment); if no email, leave it as empty string ""
subj = "Email Subject" ' The subject of your email; required only if you send the CSV over email
body = "Put a message here!" ' The body of your email; required only if you send the CSV over email
smtp = "mail.server.com" ' Name of your SMTP server; required only if you send the CSV over email
smtpPort = 25 ' SMTP port used by your server, usually 25; required only if you send the CSV over email
sqlStr = "select * from task where task_id ='37110'" ' SQL statement you wish to execute
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''



dim fso, conn

'Create filesystem object
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if

' Subprocedure to generate data. Two parameters:
' 1. fPath=where to create the file
' 2. sqlstr=the database query
sub MakeDataFile(fPath, sqlstr)
dim a, showList, intcount
set a = fso.createtextfile(fPath)

set showList = conn.execute(sqlstr)
for intcount = 0 to showList.fields.count -1
if intcount <> showList.fields.count-1 then
a.write """" & showList.fields(intcount).name & ""","
else
a.write """" & showList.fields(intcount).name & """"
end if
next
a.writeline ""

do while not showList.eof
for intcount = 0 to showList.fields.count - 1
if intcount <> showList.fields.count - 1 then
a.write """" & showList.fields(intcount).value & ""","
else
a.write """" & showList.fields(intcount).value & """"
end if
next
a.writeline ""
showList.movenext
loop
showList.close
set showList = nothing

set a = nothing
end sub

' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)

' Close
set fso = nothing
conn.close
set conn = nothing

if email <> "" then
dim objMessage
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Test Email from vbs"
objMessage.From = email
objMessage.To = email
objMessage.TextBody = "Please see attached file."
objMessage.AddAttachment outputFile

objMessage.Configuration.Fields.Item ("

http://schemas.microsoft.com/cdo/configuration/sendusing")

= 2
objMessage.Configuration.Fields.Item ("

http://schemas.microsoft.com/cdo/configuration/smtpserver")

= smtp
objMessage.Configuration.Fields.Item ("

http://schemas.microsoft.com/cdo/configuration/smtpserverport")

= smtpPort

objMessage.Configuration.Fields.Update

objMessage.Send
end if

'You're all done!! Enjoy the file created.
msgbox("Data Writer Done!")

 

[markdmac]

Too hard to tell from your post if the above is one script or two? What is line 47 for you?

I hope that helps.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.