question

SQL USER avatar image
SQL USER asked

How to create a SQL Job to open then close excel file?

Hi all, I want to create a sql agent job that opens an excel file and then closes it. I used type: Active X script once and Operating System (CmdExec) once but I don't know the VB code to use. In Run as, I only have SQL agent service account. I know this is so simple but I tried several codes from the internet but didn't work. Please help, Thanks
sql-server-2005sqljob
2 comments
10 |1200 characters needed characters left characters exceeded

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

@SQL USER Could you please elaborate a bit on why do you need to open Excel file and then close it? If all you need is read some data from Excel and load it into table then there is no need to explicitly open the file with file system and/or use any Excel specific libraries. If you need to modify the values of some cells then again there is no need to use file system either. You can use either of the 2: - T-SQL to select/update/delete/insert data to/from Excel file with **OPENROWSET** (in case if you have a 32-bit SQL Server, there are no 64-bit drivers unless you have Excel 2010) - task in SSIS to do the same (again subjected to 64-bit limitation) In any case, providing more details will enable someone to help you.
3 Likes 3 ·
I have an excel file which has macros in it. When I open the excel the macros update some data from sql server and then saves the updated data in another excel file. I want to create a job which opens the first file in order for the macros to work and update the second file. I used this Operating System (CmdExec) and below is the command I used but it succeeded without opening my excel. cmd /c "start C:\excel.xlsm" Thanks,
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
If you are running on Windows Server 2008{R2), Vista, Windows 7, then the services are running in a Session 0 which is not interactive and you cannot run interactive applications in the Session 0. And When you launch something from within an agent, then it is launched under the service and also in a Session 0. Therefore you cannot launch excel from withing an agent in interactive mode. In case the excel is only querying data from SQL Server and then updating other excel file, you can write an SSIS package (as @Oleg mentioned in his comment), which will read the data and update the target excel file instead of the macro in the excel you are trying to open You can check this document related to Session 0 isolation: [Impact of Session 0 Isolation on Services and Drivers in Windows][1] [1]: http://msdn.microsoft.com/en-us/windows/hardware/gg463353
10 |1200 characters needed characters left characters exceeded

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
You can do this usingOle from within T-SQL. While not directly addressing your topic, there is a sample of a T-SQL script that interacts with excelt at [ http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-convert-Excel_to_table.html][1]. MSDN also provides a good reference at [ http://msdn.microsoft.com/en-us/library/ms190501.aspx][2] Now, with that said, I recommend against going this route. I have previously tested it and regularly hung excel (albiet this is more likely due to my bad coding than to problems with SQL or excel), which would then waste memory and cause other problems until either that server was restarted or I manually opened the task manager and killed the process. Now, if I were in your situation, I would consider possibly recreating the necessary code in a more fully featured language. Depending on what these macros are doing, you may be able to redo everything in pure T-SQL without involving excel. If that is not an option, you may be able to recreate the effect using another language like Python, VB, or PowerShell. If you really want to involve Excel, then remember that Ole was designed with VB in mind and consider writing a VB program to invoke and control excel (Python and Perl are amoungst many other languages that have much better Ole capabilities than T-SQL does.) Also, if you implement this in anything besides pure T-SQL, consider having it run from somewhere other than the production server. I know from experience (though mercifully on test servers) that using Ole to Excel can rapidly cause problems for a server if something goes wrong. [1]: http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-convert-Excel_to_table.html [2]: http://msdn.microsoft.com/en-us/library/ms190501.aspx
2 comments
10 |1200 characters needed characters left characters exceeded

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

+1 I agree with you! We shouldn't forget that Excel have been the root of many virus infections around the world due to bad coding in Excel, especially with macros.
1 Like 1 ·
That is a great point. I hadn't even considered the virus/trojan angle since when I did my testing I was using Excel files I created and I assumed the original poster was doing something too.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
Running office on a sql server!? Start excel from within SQL? NO, you are begging for trouble! Even if you could, you shouldn't. You shouldn't run any gui app from within SQL server. What will happen if the gui app need to wait for user interaction? You'll end up with tons of instances. I have seen a case where a client used ole to activate external components and when the component failed, SQL created a stack dump and stopped working. Remember the most important purposes of SQL server: - Data reduction (querying to get subset of data instead of complete databases) - Data consistency (constraints, relations, etc.) - Storage(?)(there are other solutions for storage besides databases, like flatfile and excel) Keep these purposes in mind and try to adopt KISS (Keep It Simple Stupid) and you will probably avoid the worst issues. Many peoples forget these simple facts and try to do fancy stuff within SQL server. And yes you can do a lot of stuff within SQL server, and sometimes it is done best in SQL server and sometimes not. If you try to use Excel for reporting purposes, you should try to use SQL Reporting Services and schedule the report.
10 |1200 characters needed characters left characters exceeded

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.