May 31, 2006 at 9:14 am
I'm trying to import data from a .xls spreadsheet with openrowset.
We're attempting to execute a stored proc that accepts a filename parameter, then builds an openrowset command for sp_executesql. The stored proc is called from an asp.net application.
We have a permissions problem that we can't track down. SQL Server service is set to run as a domain admin. The asp.net app uses integrated security. When the account executing the stored proc (in asp.net) has LOCAL admin privileges on the SQL Server box, the code executes no prob. When the user is not an admin on SQL Server box, code fails with generic Jet OLE DB error. Any ideas?
--EDIT--
Under what security context will OPENROWSET execute for a sql login?
May 31, 2006 at 3:28 pm
Openrowset is somewhat using DTC (Distributed Transaction Coordinator). Try to go to the Control Panel->Administrative Tools->Component Services -> Computers ->My Computer. Right-Click on My Computer (in Component Services) go to MSDTC tab and configure things under Security Configuration button.
Also make sure your Excel document contains data in first 8 rows. There is By Design Microsoft feature that the data type is determined by first 8 rows. The import of the whole field fails if you try to import an integer field for example with first 8 rows containing nothing. The workaround is to put a record of the approprieate data type in one of the first records. They have an article in the Knowledge Base on it.
Regards,Yelena Varsha
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply