Execute SQL Stored Procedure using VBScript

Hello Experts,

I have the following stored procedure in a SQL DB and would like to write a VBScript that will execute this procedure. Basically what this procedure does is searches a DB for invoices that are delinquent. If the invoice is delinquent, the message in the procedure will show up in the customers profile and we will not offer support until their account is brought current.

I am as new as you can get when it comes to scripting and would like some help trying to figure this out. If there is more info that you need from me just let me know and I will do the best I can to get that info. Thanks in advance for any help.

leadcrew
- Adds a Delinquency Alert to Orgs w/ Delinquent Royalties
CREATE PROCEDURE SetDelinquentRoyaltyAlerts AS
 
-- Declare variables
DECLARE @Message varchar(200)
DECLARE @Temp varchar(100)
 
-- Set Delinquent Customer Messages
SET @Message = 'This customer has delinquent royalties. Check with licensing (Sharon Burns  '
SET @Message = @Message + 'licensing@LEADTOOLS.com) before making credit sale or giving technical support. '
 
-- Set Delinquent Customer Message LIKE search patterns
SET @Temp = '%' + 'This customer has delinquent royalties. Check with licensing' + '%'
 
 
/********************************************************************************************************************/
 
-- Add Delinquent Message to Alert Field of Orgs w/ Delinquent Royalties
UPDATE	Org
SET	ChangeDate = GETDATE(), ChangeUser = 'SQLDAIMON', Alert = @Message + ISNULL(Alert, '')
FROM	Org o JOIN Royalty r ON o.Org_ID = r.Org_ID
WHERE	(r.Status = 'Delinquent' OR r.Status = 'Multimedia Delinquent')
AND	(Alert NOT LIKE @Temp OR Alert IS NULL)
AND	o.CustNo IS NOT NULL
 
-- Remove Delinquent Message from Alert Field of Orgs w/ Formerly Delinquent Royalties
UPDATE	Org
SET	ChangeDate = GETDATE(), ChangeUser = 'SQLDAIMON', Alert = REPLACE(Alert, @Message, '')
FROM	Org o JOIN Royalty r ON o.Org_ID = r.Org_ID
WHERE	o.Org_ID NOT IN (SELECT DISTINCT Org_ID FROM Royalty WHERE Status IN ('Delinquent', 'Multimedia Delinquent'))
AND	Alert LIKE @Temp
 
/********************************************************************************************************************/
 
-- Cleanup After Previous UPDATE Statement
-- Set Zero-length String Alert Fields to NULL
UPDATE Org
SET ChangeDate = GETDATE(), ChangeUser = 'SQLDAIMON', Alert = NULL
WHERE Alert = ''
GO

Open in new window

LVL 3
LEAD SupportAsked:
Who is Participating?
 
ChrisedeboConnect With a Mentor Commented:
Just out of interest, why a vbscript? Is this a proof of concept?

You could use the SQL Server Agent to schedule a job to execute the store proc on a regular (eg nightly) basis.
0
 
LEAD SupportAuthor Commented:
Hi Chrisedebo,

Thanks for the response. From what I currently understand about this particular script is that we want to be able to reuse the script over and over if that makes any sense. Sorry I cant provide more info then that right now. I can try to elaborate once I get some more info.

Thanks,

leadcrew
0
 
LEAD SupportAuthor Commented:
Hello Again,

After looking at the SQL Server Agent as mentioned above, we actually do have a job that is specifically designed to do that every morning. That job is failing for some reason and I will have to look into this a bit further. Thanks for the help and I will reward points accordingly.

Thanks,

leadcrew
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LEAD SupportAuthor Commented:
Provided the correct optional answer which eliminates the need for VBScript, but now need to resolve a differnet issue on the same subject.
0
 
ChrisedeboCommented:
This should do it using osql, a command line SQL utility for SQL Server.

The below script will execute store proc "SQLStoredProc" in database "DB" on server "SERVER" using a trusted connection.

if you wish to use a different userid then you'll need the -U USERID switch with -P PSWD unless you wish to be prompted for the password.
Set WshShell = WScript.CreateObject("WScript.Shell")
 
'The server name is SERVER the database is DB and the proc is SQLStoredProc
WshShell.Run "osql -S SERVER -E -Q""EXEC DB..SQLStoredProc"" "

Open in new window

0
 
LEAD SupportAuthor Commented:
Hi Chrisedebo,

Excellent,

Thanks for the help, I really appreciate it. I will take the code and adjust accordingly.

Thanks so much again!

leadcrew
0
All Courses

From novice to tech pro — start learning today.