Solved

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

Posted on 2008-10-27
8
6,488 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

719 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