Can VBScript be called from SQL Server stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Can VBScript be called from SQL Server stored proc

Author

Topic

twl55
Starting Member

19 Posts

Posted - 2011-09-07 : 21:20:43

Can a VBScript be executed via xp_cmdshell?

I have the following:

DECLARE @FilePath VARCHAR(160)

SET @FilePath = 'C:\Users\admin\'

-- Run VBScript to update
set @doscmd = @FilePath + 'ReformatSheet.vbs "' + @FilePath + 'xyz.xls"'
EXEC master..xp_cmdshell @doscmd

If I run it from the command window, it works perfectly. If I execute the stored procedure, it gets hung up and seems to execute forever.

Any ideas?

Thanks

twl55

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-07 : 21:40:30

Make sure the SQL Agent account has permissions on all resources the script touches.

Also, make sure that there is an explicit end statement in the script.

If the script runs on a win server 2008 box, be sure UAC is disabled.

twl55
Starting Member

19 Posts

Posted - 2011-09-07 : 22:35:12

I apologize for what is undoubtedly a stupid question -

How do I find out what the SQL Agent account is?

Thanks

twl55

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-07 : 22:37:58

Start
Administrative Tools
Services
Right click SQL Server, click properties, and go the the login tab.

twl55
Starting Member

19 Posts

Posted - 2011-09-07 : 23:16:20

This is the VBScript I'm trying to execute:

Dim args, objExcel

Set args = WScript.Arguments
Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open args(0)
objExcel.Visible = True

objExcel.Run "ReformatSheet"

objExcel.ActiveWorkbook.Saved = True

objExcel.ActiveWorkbook.Close(0)

objExcel.Quit

Set objExcel = Nothing

End

Which resources would the SQL Agent need permission to access? Obviously the Excel workbook filename passed as args(0). And the .vbs file itself being executed from the stored procedure. Is there anything else? Some sort of permission to create an Excel.Application object?

Thanks

twl55

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 01:56:45

Are you using a domain account? If so, I'd try logging in and executing the script under that account.

Curious...what are you doing with the workbook. This seems a better job for SSIS, or a .Net App than an SP.

twl55
Starting Member

19 Posts

Posted - 2011-09-08 : 08:44:02

The desire is to run issue an SQL statement in SQL Server, directing the result set into an Excel Spreadsheet. I do that using OPENROWSET. Works great...

except, all numeric and date fields in the SQL show up in Excel as text fields. They don't sort correctly, are left-justified, etc.

I tried to find a way to have OPENROWSET populate Excel with the correct types, but have had no success.

So, I wrote an Excel VBA macro to do all the appropriate reformatting. Works fine when I run it by hand.

But, I'd like it automated. So, after some research I developed a VBScript which runs Excel, executes the reformatting macro and then closes Excel. When I run the VBScript from the Command WIndow, it performs the reformatting and closes everything without user intervention.

Then, I tried to run that same command line on the VBScript from the stored procedure and have had no success.

twl55

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 08:45:34

Sounds like a permissions issue. Made sure UAC is disabled?

twl55
Starting Member

19 Posts

Posted - 2011-09-08 : 12:01:14

UAC is disabled.

The SQL Server Agent is logging on as "Local System account".

I am logging in and running the VBScript successfully as the account
"admin".

When I right-click and look at the Security Properties for all the files involved (.vbs script, .xls spreadsheet) it says SYSTEM has Full Control.

What else might I need to set privilege on.

Thanks for the help on this. I'm very new at trying to do this.

twl55

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 12:14:52

since it hung, are there still instances of excel running? use task manager on the server to see this.

I'd try running SQL Agent with a domain account too.

twl55
Starting Member

19 Posts

Posted - 2011-09-08 : 12:47:56

After it hangs up, I end up killing the stored procedure execution by closing SQL Server, and an EXCEL.EXE task remains running.

Is there anywhere I can go (log, etc) to see what it's having trouble with.

Also, could this line be part of the issue:

objExcel.Visible = True

twl55

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 13:41:10

could be. I'd definitely remove that.

You may need to output something to a log file after every line, then you'll know where its hanging.

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-07 : 11:27:09

Not sure if this will help (and i have not tested). Can you try executing the .vbs file using cscript.exe and use it in the sp? Effectively you will be executing cscript.exe (with your vbs as parameter).
sp_cmdshell 'cscript.exe C:\Users\admin\ReformatSheet.vbs'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources