Solved Executing SQL queries in VBS script

May 30, 2013 at 08:34:54
Specs: Windows 7
Firstly :
I want to execute the SP and get the resultset in the attachement for mail.
SP name : Exec rpt_XC_BUF_SUM
DB : fi4_xc_prod
Server : FACETSRPT\FACETS

I want to capture the resultset of above SP into the txt file to be attached in mail.

Secondly : I want to run the below query and capture the result set (that is count) into the Body of the mail.
DB : fi4_xc_prod
Server : FACETSRPT\FACETS
Query : select count(*) from fi4_xc_prod.dbo.XC_BUF where TBL_ORDER_ID='C'

The body of the mail shoulde be like :
Listing of today's claim batch ID's and count is attached. Total Count: (From above query).

The below is the mail script i have :


SMTPServer = "mail.xxx.com"
Recipient = "suhail_abbas@xxx.com"
Cc = "suhail_abbas@xxx.com"
From = "suhail_abbas@xxx.com"
Subject = "Test email"
Message = "Listing of today's claim batch ID's and count is attached. Total Count: (From above query)"
'attachment = Here the attachment from the above SP result set. It should be text file


GenericSendmail SMTPserver, From, Recipient, Subject, Message

Sub GenericSendmail (SMTPserver, From, Recipient, Subject, Message)

set msg = WScript.CreateObject("CDO.Message")
msg.From = From
msg.To = Recipient
msg.Cc = Cc
msg.Subject = Subject
msg.TextBody = Message
'msg.AddAttachment attachment
msg.Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msg.Configuration.Fields.Update
msg.Send
End Sub


See More: Executing SQL queries in VBS script

Report •


✔ Best Answer
June 25, 2013 at 10:06:24
Is this in addition to the existing email, or replacing it?

How To Ask Questions The Smart Way



#1
May 30, 2013 at 08:54:59
Can anyone respond to the above question please ?

Report •

#2
May 30, 2013 at 12:07:55
Well ain't we demanding?

Largely untested, because I'm not going to hunt around for a usable DB to query, and I'm not going to hunt down my mail configuration.

Const SMTPServer = "mail.xxx.com"
Const Recipient = "suhail_abbas@xxx.com"
Const Cc = "suhail_abbas@xxx.com"
Const From = "suhail_abbas@xxx.com"
Const Subject = "Test email"
Const Message = "Listing of today's claim batch ID's and count is attached. Total Count: "
Const fileName = "results.csv"
      ConnectString = "Server=myServerName\myInstanceName;Database=myDataBase" & _
  ";User Id=someUserID" & _
  ";Password=somePassword"
Const FileQuery = "Exec rpt_XC_BUF_SUM"
Const SummaryQuery = "select count(*) from fi4_xc_prod.dbo.XC_BUF where TBL_ORDER_ID='C'"


Set fso = CreateObject("Scripting.FileSystemObject")
Set com = CreateObject("ADODB.Command")
com.ActiveConnection = ConnectString
com.CommandText = FileQuery
Set rs = com.Execute
rs.MoveFirst : Set outFile = fso.OpenTextFile(fileName, 2, True)
Do Until rs.EoF
  For Each f In rs.Fields
    line = line & "," & f
  Next 'f
  outFile.WriteLine Mid(line, 2)
  rs.MoveNext : line = ""
Loop
outFile.Close

com.CommandText = SummaryQuery
Set rs = com.Execute
rs.MoveFirst 
body = Message & rs(0)

GenericSendmail SMTPserver, From, Recipient, Subject, body, fso.GetAbsolutePathName(fileName)
WScript.Quit

Sub GenericSendmail (SMTPserver, From, Recipient, Subject, Message, Attachment)
  Set msg = WScript.CreateObject("CDO.Message")
  msg.From = From
  msg.To = Recipient
  msg.Cc = Cc
  msg.Subject = Subject
  msg.TextBody = Message
  msg.AddAttachment Attachment
  msg.Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
  msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  msg.Configuration.Fields.Update
  msg.Send
End Sub

How To Ask Questions The Smart Way


Report •

#3
May 31, 2013 at 05:32:20
Thanks for the info.. Sorry if you feels that its demanding:)

Report •

Related Solutions

#4
June 13, 2013 at 04:17:38
Hi Razor,

I need your help again.. In the above VB script.. We are executing some sql queries like stored procedure. Recently i ran one SP through the VB script. It takes some tme to give the result as it has to fetch the huge data.. I got the below TimeOut error.
Please suggest me how could we handle this issue in Vb script. Thanks in advance.

C:\Documents and Settings\sabba10\Desktop\IRS.vbs(11, 1) Microsoft OLE DB Provid
er for ODBC Drivers: [Microsoft][ODBC SQL Server Driver]Timeout expired


Report •

#5
June 14, 2013 at 08:03:50
Just after com.ActiveConnection = ConnectString, add the following:
com.CommandTimeout = 0

How To Ask Questions The Smart Way


Report •

#6
June 17, 2013 at 10:52:36
Hi Razor,

One more help on this. Im taking the resultset of the query in txt file. im using the below code.

Const fileName = "ID_Cards_New_Individual.txt"
ConnectString = "Driver={SQL Server};Server=FATEST;Database=fa4_glhp_test"
Const FileQuery = "Exec ID_Cards_New_Individual '04/15/2013'"
'Const SummaryQuery = "select count(*) from fi4_xc_prod.dbo.XC_BUF where TBL_ORDER_ID='C'"


Set fso = CreateObject("Scripting.FileSystemObject")
Set com = CreateObject("ADODB.Command")
com.ActiveConnection = ConnectString
com.CommandTimeout = 90
com.CommandText = FileQuery
Set rs = com.Execute
rs.MoveFirst : Set outFile = fso.OpenTextFile(fileName, 2, True)
'outFile.WriteLine(Date)
Do Until rs.EoF
For Each f In rs.Fields
line = Line & "," & f
Next 'f
outFile.WriteLine Mid(line, 2)
rs.MoveNext : line = ""
Loop
outFile.Close


The result is coming in the below format.
0000003956,200344086,MARY,ENGLAND,

I want the result to come like below with double quotes
"0000003956","200344086","MARY","ENGLAND",

Please help...


Report •

#7
June 17, 2013 at 12:50:29
Replace line = Line & "," & f with
line = line & ",""" & f & """"

How To Ask Questions The Smart Way


Report •

#8
June 17, 2013 at 13:34:45
Thanks a lot.. Its working fine

Report •

#9
June 24, 2013 at 08:39:10
Hi Razor,

I need your help again dude..

In the VB script i want to execute the stored procedure like above we are doing, after that i want to run a select query to check whther the Stored procedure completede successfully or not.
select * from GPL_JOB_DETAIL where ProcessID = 56 and Startdate = currendate and JobStatusID = 3
JobStatusID = 3 shows the SP failed
JobStatusID = 2 Shows the SP successfull.

For JobStatusID = 3 i need to send failure notification and for JobStatusID = 2 i need to send the successfull notification.

Please do the needfull


Report •

#10
June 25, 2013 at 10:06:24
✔ Best Answer
Is this in addition to the existing email, or replacing it?

How To Ask Questions The Smart Way


Report •


Ask Question