Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-10-27
8
Medium Priority
?
6,722 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
7 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 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