Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr
- '''''''''''''''''
- ' Configuration '
- '''''''''''''''''
- dbType = "oracle" ' Valid values: "oracle", "sqlserver", "mysql"
- dbHost = "dbhost" ' Hostname of the database server
- dbName = "dbname" ' Name of the database/SID
- dbUser = "username" ' Name of the user
- dbPass = "password" ' 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 user from dual" ' 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
Add Comment
Please, Sign In to add comment