Force Excel to run macro using SQL Query

问题:

I have a requirement to run the Excel file (either .xls or .xlsm) which has macros in it and it reduces the size of the other excel file and save it as different file in same folder. It works fine when I open the file manually from the folder, it runs immediately and creates the shrinked file.

But the requirement is to open the Excel from SQL query and run it. I am trying to do it with these two ways in my SQL query.

(1) EXEC [master].dbo.xp_cmdshell 'c:\temp\MyExcelShrink.xls'
(2) EXEC  xp_cmdshell  'dtexec /f c:\temp\MyPackage.dtsx'

The dtsx package has the same logic to open the Excel and run it, but none of these code working.

回答1:

When you use xp_cmdshell, the process that starts runs in the security context of the SQL Server service login. That account may not have desktop interactivity enabled or may lack other permissions that Excel requires to run macros at start-up.

See this xp_cmdshell article on the SQL Server Security Blog for more information about xp_cmdshell, including a recommendation that it shouldn't be used: "Generally speaking, you must avoid using xp_cmdshell, and if possible, you should remove any dependency on it."