firing a microsoft application from a 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

SQL Server 2000 Forums

Transact-SQL (2000)

firing a microsoft application from a stored proc

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-27 : 12:21:42

Is there a way to launch a word doc from a stored procedure?

Thanks for any help on this.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-27 : 14:57:52

This should never be done on a server !

This works from commandline:
"C:\program files\microsoft office\office11\winword.exe" "C:\Path\Worddoc.doc"

This does not:
exec master..xp_cmdshell '"C:\program files\microsoft office\office11\winword.exe" "C:\Path\Worddoc.doc"'
(due to the space in the path)

does anyone know how to resolve the space issue ?

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-27 : 15:08:41

quote:
Originally posted by rockmoose
...does anyone know how to resolve the space issue ?...

You can find the 8 character DOS compatible directory name by using the /X option on the DIR command to find a directory path without embeded spaces.

C:\>dir prog* /x
 Volume in drive C has no label.
 Volume Serial Number is 5CBB-BBB7

 Directory of C:
01/24/2005  11:16a      <DIR>          PROGRA~1        Program Files
               0 File(s)              0 bytes
               1 Dir(s)   4,792,185,856 bytes free

C:\>cd PROGRA~1

C:\PROGRA~1>dir microsoft* /x
 Volume in drive C has no label.
 Volume Serial Number is 5CBB-BBB7

 Directory of C:\PROGRA~1

12/10/2003  06:01p      <DIR>          MI3AA1~1        Microsoft ActiveSync
11/21/2003  04:24p      <DIR>          MIA538~1        Microsoft Analysis Services
05/13/2004  11:05a      <DIR>          MICROS~1        microsoft frontpage
08/13/2004  11:03a      <DIR>          MICROS~2        Microsoft Office
08/13/2004  11:03a      <DIR>          MICROS~4        Microsoft SQL Server
05/21/2002  08:32a      <DIR>          MICROS~3        Microsoft Visual Studio
08/13/2004  11:03a      <DIR>          MICROS~2.NET    Microsoft Visual Studio .NET 2003
12/10/2003  06:02p      <DIR>          MICROS~1.NET    Microsoft.NET
               0 File(s)              0 bytes
               8 Dir(s)   4,792,185,856 bytes free

C:\PROGRA~1>cd  MICROS~2

C:\PROGRA~1\MICROS~2>dir
 Volume in drive C has no label.
 Volume Serial Number is 5CBB-BBB7

 Directory of C:\PROGRA~1\MICROS~2

08/13/2004  11:03a      <DIR>          .
08/13/2004  11:03a      <DIR>          ..
12/10/2003  06:01p      <DIR>          Clipart
12/10/2003  06:00p      <DIR>          MEDIA
05/17/2002  04:04p                 281 Microsoft Bookshelf Basics.lnk
05/17/2002  04:04p                 623 Microsoft Photo Editor.lnk
05/13/2004  11:05a      <DIR>          Office
08/13/2004  11:03a      <DIR>          Office10
01/19/2005  11:16a      <DIR>          OFFICE11
12/10/2003  06:00p      <DIR>          Templates
               2 File(s)            904 bytes
               8 Dir(s)   4,792,185,856 bytes free

C:\PROGRA~1\MICROS~2>cd OFFICE11

C:\PROGRA~1\MICROS~2\OFFICE11>dir winword.exe
 Volume in drive C has no label.
 Volume Serial Number is 5CBB-BBB7

 Directory of C:\PROGRA~1\MICROS~2\OFFICE11

08/06/2003  02:24p          12,037,688 WINWORD.EXE
               1 File(s)     12,037,688 bytes
               0 Dir(s)   4,792,185,856 bytes free

C:\PROGRA~1\MICROS~2\OFFICE11>

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-27 : 16:19:25

Thanks Michael, that was very good information for me.
doing:
xp_cmdshell 'C:\PROGRA~1\MICROS~3\office11\winword.exe'
is crap, for starters the winword process does not open a visible window

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-27 : 17:06:15

A word of warning on that: The 8 chararacter DOS directory name may vary from one machine to another, depending on the order of directory creation, so you have to walk through the directory structure to see what it is.

quote:
Originally posted by rockmoose

Thanks Michael, that was very good information for me.
doing:
xp_cmdshell 'C:\PROGRA~1\MICROS~3\office11\winword.exe'
is crap, for starters the winword process does not open a visible window

rockmoose

CODO ERGO SUM

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-27 : 18:28:02

quote:
This should never be done on a server !

And yet you post a method for doing it anyway. I don't see the logic in that at all.

Don't be surprised if you lock up your server and corrupt your databases by doing something like this.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-27 : 18:43:53

>>Don't be surprised if you lock up your server and corrupt your databases by doing something like this.

... and/or end up with 1000 copies of Word running hidden in the background on your server.

How many times do I say it over and over here at sqlteam: use the proper tools and the proper technology for the job. Don't make things complicated on yourself by trying to eat soup with a fork or cut steak with a spoon. A stored procedure running on a SQL Server has no business trying to open up a copy of MS Word !!!

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-28 : 04:47:49

The first three lines of this thread:
>> Is there a way to launch a word doc from a stored procedure?
Thanks for any help on this.
>> This should never be done on a server !
.....................................................

Ok, the logic breaks up after that.
Thankfully, Rob & Jeff cleared up any misunderstandings.

Predictably there was no way!,
that post would slip through the watchful eyes
of the relentless guardians of logic here at SqlTeam.

rockmoose

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-28 : 08:54:00

Thanks for the entertaining discussion and the useful info about the 8 char dos directory name.

I agree with the last comment. I was sourcing this for another user on the team.

Thanks

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-28 : 11:21:24

Would it be better to insert the path of the documents /files into a table and have them executed from the tables? Is this possible? I know we used to house jpgs in a table and referenced different jpgs depending on the user and preferences. Can we do this with word docs and other MS Applications?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-28 : 13:28:08

I would recommend not storing document files in SQL Server itself (including image files), store them as files and put the path in the database. Also do not use SQL Server to launch other applications, that is not what it is designed for and it will only cause problems. Your end-user app should be responsible for such a feature.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources