I've created a linked server to an Excel spreedsheet. It's ok and working in SQL Server I can query excel file in query analyzer.
But when I'm trying to access this link server in my asp page:
Set pishbini = Server.CreateObject("ADODB.Recordset")
pishbini.ActiveConnection = MM_semmet_STRING
pishbini.Source = "SELECT * FROM OPENQUERY(SemnanmetWeekly, 'SELECT * FROM [DayPish$]')"
pishbini.CursorType = 0
pishbini.CursorLocation = 2
pishbini.LockType = 1
pishbini.Open()
SemnanmetWeekly is the name of linked server in SQL Server I'm getting this error in my asp page:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SemnanmetWeekly" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
/Daypish.asp, line 15
IUSR (IIS user) has full access to excel file, I have played with all the different options in in my Link Server Options like impersonating or accessing excel file with administrator account, but error remained the same!
I prefer to not change the code, since I'm only doing administration stuff on this server. it has been working on an old server but since we moved it to new server it stopped working. anything I'm missing here?
Not related to permissions. I think you logged in to Sql Server with Windows Authentication, everything is okay, you're the boss. But, if the connection in ASP is SQL authenticated, you get this error. So, you should add a linked server login with a query similar to following.
Exec master..sp_addlinkedsrvlogin
@rmtsrvname = N'SemnanmetWeekly',
@useself = N'False',
@locallogin = N'sa', /* replace your own - same as sql server authenticated account */
@rmtuser = NULL,
@rmtpassword = NULL
Then you can connect to SemnanmetWeekly in your ASP application.