SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Can VBScript be called from SQL Server stored proc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

twl55
Starting Member

USA
19 Posts

Posted - 09/07/2011 :  21:20:43  Show Profile  Reply with Quote
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

USA
5071 Posts

Posted - 09/07/2011 :  21:40:30  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

twl55
Starting Member

USA
19 Posts

Posted - 09/07/2011 :  22:35:12  Show Profile  Reply with Quote
I apologize for what is undoubtedly a stupid question -

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

Thanks

twl55
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 09/07/2011 :  22:37:58  Show Profile  Visit russell's Homepage  Reply with Quote
Start
Administrative Tools
Services
Right click SQL Server, click properties, and go the the login tab.
Go to Top of Page

twl55
Starting Member

USA
19 Posts

Posted - 09/07/2011 :  23:16:20  Show Profile  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 09/08/2011 :  01:56:45  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

twl55
Starting Member

USA
19 Posts

Posted - 09/08/2011 :  08:44:02  Show Profile  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 09/08/2011 :  08:45:34  Show Profile  Visit russell's Homepage  Reply with Quote
Sounds like a permissions issue. Made sure UAC is disabled?

Edited by - russell on 09/08/2011 08:45:57
Go to Top of Page

twl55
Starting Member

USA
19 Posts

Posted - 09/08/2011 :  12:01:14  Show Profile  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 09/08/2011 :  12:14:52  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

twl55
Starting Member

USA
19 Posts

Posted - 09/08/2011 :  12:47:56  Show Profile  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 09/08/2011 :  13:41:10  Show Profile  Visit russell's Homepage  Reply with Quote
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.

Edited by - russell on 09/08/2011 13:42:04
Go to Top of Page

Cindyaz
Yak Posting Veteran

USA
73 Posts

Posted - 10/07/2011 :  11:27:09  Show Profile  Reply with Quote
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'


Edited by - Cindyaz on 12/06/2011 04:19:42
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000