July 7, 2005 at 9:33 am
I have a stored procedure to export data from SQL tables to an Excel file. I can run it in QA by using the windows authentication login. But I can not run it under another login of the database, even I tried to grant every permissions of the database to the login. Then the problem is that how can I fire off the stored procedure in a .Net application, since you can not use Window Authentication as the connection to the database! Any suggestions?
Thanks!
July 7, 2005 at 9:44 am
what account is your SQL server service running as? is it loalsystem?
it sounds like the problem may relate to the windows permissions of the SQL server service. - can the file be created by that account?
what is the error message being generated?
MVDBA
July 7, 2005 at 9:57 am
I tried to run my SQL Server Service under LocalSystem. It stil does not work.
The error message is:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
Thanks!
July 7, 2005 at 10:01 am
from SQL to Excel?
this looks a lot like msaccess
MVDBA
July 7, 2005 at 10:07 am
Anyhow, that's what my user wanted?
July 7, 2005 at 12:15 pm
Jet can be used to make an OLEDB connection to both Excel and text files. Not a common thing, but it's part of the functionality of Jet.
K. Brian Kelley
@kbriankelley
July 8, 2005 at 12:54 am
I am guessing that you are using a linked server for the Excel XLS, right?
Since you stated that the data flow is from SQL Server to Excel, then the XLS file has to exist before the linked server connection can be made. You can see my method around this issue here:
http://www.sqlservercentral.com/scripts/contributions/763.asp
Also for Jet connections you should use Admin for the user name with no password.
Andy
July 8, 2005 at 11:16 am
When I tried to used OPENROWSET to handle the INSERT instead of a LINKED SERVER, everything worked just fine.
Thanks everybody!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply