Solved

Execute SQL Stored Procedure using VBScript

Posted on 2008-06-17
6
5,878 Views
Last Modified: 2010-04-21
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

0
Comment
Question by:LEAD Support
  • 4
  • 2
6 Comments
 
LVL 7

Accepted Solution

by:
Chrisedebo earned 500 total points
ID: 21811330
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
 
LVL 3

Author Comment

by:LEAD Support
ID: 21812477
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
 
LVL 3

Author Comment

by:LEAD Support
ID: 21812572
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 3

Author Closing Comment

by:LEAD Support
ID: 31468158
Provided the correct optional answer which eliminates the need for VBScript, but now need to resolve a differnet issue on the same subject.
0
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 21812671
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
 
LVL 3

Author Comment

by:LEAD Support
ID: 21812724
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

14 Experts available now in Live!

Get 1:1 Help Now