asked on

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

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

kevin_buchanan

ASKER

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

kevin_buchanan

ASKER

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

isdi

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

bigbillydotcom

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

kevin_buchanan

ASKER

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!

kevin_buchanan

ASKER

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

ASKER CERTIFIED SOLUTION

Computer101

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial