x

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
more ▼

asked Jun 02, 2011 at 06:17 AM in Default

SQL USER gravatar image

SQL USER
12 5 6 6

@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.
Jun 02, 2011 at 08:31 AM Oleg

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,
Jun 06, 2011 at 02:27 AM SQL USER
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Jun 06, 2011 at 05:08 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

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.

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.

[2]: http://msdn.microsoft.com/en-us/library/ms190501.aspx
more ▼

answered Jun 06, 2011 at 09:53 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

+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.
Jun 07, 2011 at 11:57 PM Håkan Winther
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.
Jun 08, 2011 at 12:34 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jun 07, 2011 at 10:59 PM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x716
x86

asked: Jun 02, 2011 at 06:17 AM

Seen: 4715 times

Last Updated: Jun 02, 2011 at 06:17 AM