Solved

Use VBScript to execute a stored procedure (MS SQL 2005)

Posted on 2008-10-27
8
6,350 Views
Last Modified: 2012-08-13
I am trying to execute a stored procedure from a VBScript.  The stored procedure works from within a query window as shown:

exec msdb.dbo.sp_send_dbmail   @profile_name =  'DBMail'
              ,  @recipients =  'mymailaddress@mycompany.com'
              ,  @subject =  'SQL Transaction Log'  
              ,  @query =  'exec sp_sds_custom'
              ,  @attach_query_result_as_file =  1

I have also confirmed the @query works within the SQL console.  

So - now I am trying to run this query from a VBScript.  This script will be run pre and post execution of our Symantec Backup job.

Basically - how do run a SP with parameters from a VBScript?  The script below

Thanks!!
Const adChar          = 129 
Const adParamOutput   = 2  
Const adParamInput    = 1  
Const adCmdStoredProc = 4
const adInteger = 5
 
Dim oCon, oRs, strCon
Dim cmdStoredFunct
Dim objExcel, defaultSheets, intRowsRead
Dim objWorkbook, objWorkSheet
Dim fso
Dim strSaveAbsolute
 
strCon = "Driver={SQL Server};Server=sslmhsql004;Initial Catalog=master;Trusted_Connection=Yes;"
 
Set oCon = WScript.CreateObject("ADODB.Connection")
oCon.Open strCon
 
Set cmdStoredFunct = CreateObject("ADODB.Command")
Set cmdStoredFunct.ActiveConnection = oCon
with cmdStoredFunct
	.CommandText = "msdb.dbo.sp_send_dbmail"
	.CommandType = adCmdStoredProc
	.Parameters.Append .CreateParameter("@profile_name", adVarWChar, adParamInput, 50, "DBMail") 
	.Parameters.Append .CreateParameter("@recipients", adVarWChar, adParamInput, 50, "kbuchanan@lmh.cc") 
	.Parameters.Append .CreateParameter("@subject", adVarWChar, adParamInput, 50, "SQL Transaction Log") 
	.Parameters.Append .CreateParameter("@query", adVarWChar, adParamInput, 50, "exec sp_sds_custom") 
	.Parameters.Append .CreateParameter("@attach_query_result_as_file", adInteger, adParamInput, 50, 1) 	
end with
 
	                        	
Set oRs = WScript.CreateObject("ADODB.Recordset")
Set oRs = cmdStoredFunct.Execute
  
oCon.Close
 
Set cmdStoredFunct = Nothing
Set oRs = Nothing
Set oCon = Nothing
 
MsgBox "Script Completed"

Open in new window

0
Comment
Question by:kevin_buchanan
8 Comments
 
LVL 1

Author Comment

by:kevin_buchanan
ID: 22814895
I forgot to add:

The error I am getting is this:

Line: 30
Chart: 2
Error: Parameter object is improperly defined.  Inconsisten or incomplete information was provided.
Code: 800A0E7C
Source: ADODB.Parameters
0
 
LVL 1

Author Comment

by:kevin_buchanan
ID: 22814964
Update:

I found an extra "space" between Append.CreateParameter

But, with the "(" around the parameter information, it gave me an error about using parenethesis with a sub...so I removed the parenthesis.

Now, the error is this:
Line: 30
char: 2
Error: Wrong number of arguments or invalid property assignment: 'Parameters.Append'
Code:800A01C2
Source: Microsoft VBScript runtime error

Thanks for any help

Const adChar          = 129 
Const adParamOutput   = 2  
Const adParamInput    = 1  
Const adCmdStoredProc = 4
const adInteger = 5
 
Dim oCon, oRs, strCon
Dim cmdStoredFunct
Dim objExcel, defaultSheets, intRowsRead
Dim objWorkbook, objWorkSheet
Dim fso
Dim strSaveAbsolute
 
strCon = "Driver={Oracle in OraHome92};" & _
         "Dbq=MY_DATABASE.WORLD;QTO=F;" &_
         "User Id=MyId;Password=MyPassword;"
 
'strCon = "Driver={SQL Server};Server=sslmhsql004;Initial Catalog=master;User Id=sa;Password=damnMS;"
strCon = "Driver={SQL Server};Server=sslmhsql004;Initial Catalog=master;Trusted_Connection=Yes;"
 
 
Set oCon = WScript.CreateObject("ADODB.Connection")
oCon.Open strCon
 
Set cmdStoredFunct = CreateObject("ADODB.Command")
Set cmdStoredFunct.ActiveConnection = oCon
with cmdStoredFunct
	.CommandText = "msdb.dbo.sp_send_dbmail"
	.CommandType = adCmdStoredProc
	.Parameters.Append.CreateParameter "@profile_name", adVarWChar, adParamInput, 50, "DBMail"
	.Parameters.Append.CreateParameter "@recipients", adVarWChar, adParamInput, 50, "kbuchanan@lmh.cc"
	.Parameters.Append.CreateParameter "@subject", adVarWChar, adParamInput, 50, "SQL Transaction Log"
	.Parameters.Append.CreateParameter "@query", adVarWChar, adParamInput, 50, "exec sp_sds_custom"
	.Parameters.Append.CreateParameter "@attach_query_result_as_file", adInteger, adParamInput, 4, 1
end with
 
	                        	
Set oRs = WScript.CreateObject("ADODB.Recordset")
Set oRs = cmdStoredFunct.Execute
  
oCon.Close
 
Set cmdStoredFunct = Nothing
Set oRs = Nothing
Set oCon = Nothing
 
MsgBox "Script Completed"

Open in new window

0
 
LVL 1

Expert Comment

by:isdi
ID: 22815150
Hello,


I used your script. You will have to check it, I may have copied one or more of the variables incorrectly.

Look at the script and you should see the key requirements.


-isdi-
Const adChar          = 129 
Const adParamOutput   = 2  
Const adParamInput    = 1  
Const adCmdStoredProc = 4
const adInteger = 5
 
Dim oCon, oRs, strCon
Dim cmdStoredFunct
Dim objExcel, defaultSheets, intRowsRead
Dim objWorkbook, objWorkSheet
Dim fso
Dim strSaveAbsolute
 
strCon = "Driver={SQL Server};Server=sslmhsql004;Initial Catalog=master;Trusted_Connection=Yes;"
 
Set oCon = WScript.CreateObject("ADODB.Connection")
oCon.Open strCon
 
Set cmdStoredFunct = CreateObject("ADODB.Command")
Set cmdStoredFunct.ActiveConnection = oCon
 
'with cmdStoredFunct
'        .CommandText = "msdb.dbo.sp_send_dbmail"
'        .CommandType = adCmdStoredProc
'        .Parameters.Append .CreateParameter("@profile_name", adVarWChar, adParamInput, 50, "DBMail") 
'        .Parameters.Append .CreateParameter("@recipients", adVarWChar, adParamInput, 50, "kbuchanan@lmh.cc") 
'        .Parameters.Append .CreateParameter("@subject", adVarWChar, adParamInput, 50, "SQL Transaction Log") 
'        .Parameters.Append .CreateParameter("@query", adVarWChar, adParamInput, 50, "exec sp_sds_custom") 
'        .Parameters.Append .CreateParameter("@attach_query_result_as_file", adInteger, adParamInput, 50, 1)     
'end with
 
Dim sTemp1		' As String
 
sTemp1 = "{call msdb.dbo.sp_send_dbmail('DBMail', 'kbuchanan@lmh.cc', 'SQL Transaction Log', 'exec sp_sds_custom', 1 )}"
'sTemp1 = "{call msdb.dbo.sp_send_dbmail(" & _
'		"'DBMail', " & _
'		"'kbuchanan@lmh.cc', " & _
'		"'SQL Transaction Log', " & _
'		"'exec sp_sds_custom', " & _
'		1 & _
'	")}"
 
WITH cmdStoredFunct
	.CommandText = sTemp1
	.CommandType = adCmdStoredProc
END WITH
 
Set oRs = WScript.CreateObject("ADODB.Recordset")
 
 
' Set oRs = cmdStoredFunct.Execute
oRs.CursorType = 2			' 
oRs.LockType = 1			' adLockReadOnly = 1
oRs.Open sTemp1, oCon
'Set oRs = oCon.Execute(sTemp1)
 
oCon.Close
 
Set cmdStoredFunct = Nothing
Set oRs = Nothing
Set oCon = Nothing
 
MsgBox "Script Completed"

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 13

Expert Comment

by:bigbillydotcom
ID: 22815210
if code works in query window, why not execute it as a SQL job on a timed basis?

Also - if you can invoke the backup from a command line, you can have the SP run "upon completion" and schedule both jobs to run from SQL Server Agent - this will help avoid a lot of other "permissions" problems as well

0
 
LVL 1

Author Comment

by:kevin_buchanan
ID: 22818100
bigbillydotcom:  thanks for the suggestion.  I would run from SQL Agent, which would be much simpler, but I we use Symantec Backup Exec, and I need for this script to run before and after the backup job.  

isdi:  I will give your code a try at work tomorrow.  thanks!
0
 
LVL 1

Author Comment

by:kevin_buchanan
ID: 22992626
I couldn't get the VBScript to run.  So, I would up adding a scheduled task to launch the SP...this worked.


exec msdb.dbo.sp_send_dbmail   @profile_name =  'DBMail'
              ,  @recipients =  'kbuchanan@lmh.cc'
              ,  @subject =  'SSLMHSQL004 Data and Log Size Report'  
              ,  @query =  'exec master.dbo.sp_sds_custom'
              ,  @attach_query_result_as_file =  1
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 23023289
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question