question

respawn avatar image
respawn asked

How to call vbs scrpt from TSQL and email output?

Short overview of my issue: We have about 100 users writing to a database and sometimes blocking occurs. I have a stored procedure that sends a notification when an user is blocking enough users that the total time is more than 10 minutes (and this runs in a job that checks every 5 minutes). So, the notification that is emailed has their username (their 5+2 userid), total wait time of other blocked users, and what they are running. Separately, I have a vbs script that if you supply a username (5+2) then it will look up in LDAP and respond with the corresponding Name (first and Last) and phone number. IE: c:\>UserLookup.vbs smithjo John Smith, (555) 555-5555 c:\> So, right now, I get an email with a 5+2 userid and that there is significant blocking occuring. I then have to run this vbs script and supply the userid to get their name and number so I can call them to figure out why they are hung up (they usually are) and get them fixed up (and stop blocking other users). I would like to see if I could incorporate the vbs execution in my SQL code and include that in the email notification. Would save a lot of valuable time in resolving the issue if I already had their name and number soon as the blocking notification showed up. Any ideas on how to get this accomplished? Thanks in advance for any assistance!
tsqlvbscript
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

I think there are two separate issues here. First, to directly answer your question, xp_cmdshell will let you call out to your script and retrieve its output (as long as its output is in the form of text written to the standard console at any rate). Remember you will have to use sp_config to enable xp_cmdshell and that this will expand your attack surface area (in other words, reduce security), so be sure you want to do this.

The second question is, are you sure this is the way you want to do it? You may get overall better results form a more generalized, polished tool such as SQL Monitor https://www.red-gate.com/products/dba/sql-monitor/

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image Jeff Moden commented ·

Contrary to popular belief, enabling xp_CmdShell doesn't actually expand your attack surface area (reduce security) because only the people that can use it are the ones that can enable it. If an attacker gets in as someone that can't enable it, they won't be able to use it. If an attacker gets in as someone that can enable it, you have a whole lot more to worry about than what they might be able to do with xp_CmdShell (which they can also enable).

And sorry about answering and almost 9 year old post. :D

1 Like 1 ·
ThomasRushton avatar image
ThomasRushton answered
Another option is that you could set up a SQL Server "Linked Server" to LDAP. There's some (admittedly old) documentation available [here][1], and [this is the documentation for something in SQL 2008][2]. That'd remove the pain of having to call external scripts and parse the output... Just a thought. [1]: http://msdn.microsoft.com/en-us/library/aa772380(v=vs.85).aspx [2]: http://msdn.microsoft.com/en-us/library/ms190803.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dvroman avatar image
dvroman answered
Another option is to use SSIS which supports both the SQL interface and the vbs interface.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.