Solved

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

Posted on 2008-10-27
8
6,305 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now