sp_OACreate question

[Paul]

Hi,

I am trying to use the sp_OA procs, and I have a couple of questions.

1) When SQL Server is running under a local system account, I am able to
create an instance of an MS-Excel app. When running under a domain
user account with local admin priviliges on the SQL server, I get
"Access is denied". The question is, what rights are required of the
domain account in order to use these procs?

2) Should sp_OAcreate return the same object id, day after day, time
after time, whether I am creating an instance of SQL Server or Excel?

Thanks,

Paul

[Gert E.R. Drapers]

1) The security context of sp_OA is determined by the SQL Server service
account, not by the user invoking. If you grant execute rights to the
procedure, every possible SQL Server login/user will be able to make the
call. However the invocation of the COM object will execute under the
security context of the SQL Server service account. Various reasons can
cause that you are not able to create an instance of the object, main one
not being able to read the Registry.

2) No, you can never rely on that.

3) It is a very bad idea to instantiate Excel (or other Windows UI based
applications) from SQL Server, via sp_OA. First of all a Windows application
requires a message pump to be active and to be able to handle commands, SQL
Server as a process does not have a desktop associated with the process,
hence no message pump, so you might not even be able to start a hidden
window. Second these applications can behave very poorly when ran from a
service, what if Excel raises an error via a message box. You will not be
able to detect and handle it and it will result in a hung application, under
the SQL Server process, so you are leaking resources. Third if you have
multiple people calling it to this, you are almost guaranteed that you will
serialize access, since sp_OA is IDispatch based, hence your concurrency and
scalability is in jeopardy. I can go on like this.

Main questions you should ask yourself:
1) What functionality do you need?
2) How can I achieve this?

sp_OA should be your last resort

Ge...@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2002 All rights reserved.

"Paul" <paul.-no-sp...@communication.gc.ca> wrote in message
news:aua3gd$fi...@shark.pwgsc.gc.ca...

[Greg Linwood]

Hi Paul.

Just adding to Gert's post..

Firstly - I totally agree that instantiating any GUI app from a service is
definitely a bad idea for the reasons Gert's already mentioned, but I'm
wondering if the actual cause of your problem might be that the domain user
account SQL Server's running under doesn't have network permissions to where
the .xls file is stored? To access the .xls file in any manner (sp_OACreate
or other), this would need to be configured first.

An alternative you could look into for interfacing Excel might be DTS. It is
built with this kind of integration in mind and has various features that
would work better with Excel than sp_OACreate & it's ugly cousins..

HTH

Regards,
Greg Linwood

"Gert E.R. Drapers" <Ge...@SQLDev.Net> wrote in message
news:OZ4oXUdrCHA.1080@TK2MSFTNGP10...

[Paul]

Thanks guys.

This is all brought on by the need to export to Excel the results of a
stored proc which dynamically creates a ##tmp table. I have found
another solution using xp_cmdshell and bcp.

P