Access Linked Server: Works in SQL2000 but not SQL2005

I am runing Windows 2003 which has both SQL2000 and SQL2005.

The following works in 2000 but not 20005

Code Snippet

DECLARE @.sql nvarchar(4000)

set @.sql = 'sp_addlinkedserver @.server = N''dbAccessPO'',

@.provider = N''Microsoft.Jet.OLEDB.4.0'',

@.srvproduct = N''OLE DB Provider for Jet'',

@.datasrc = N''C:\Temp\popts.mdb'''

exec sp_executesql @.sql

set @.sql = 'sp_addlinkedsrvlogin ''dbAccessPO'', FALSE, ''sa'', ''Admin'', NULL'

exec sp_executesql @.sql

Running this:

Code Snippet

select * from dbAccessPO...MyTable

Produces this error:

Code Snippet

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO".

I login using sa for both instances.

I could really use your help.

Thanks.

Could someone please help me with this issue?

I am sure it is a permission error, but it couldn't be file permission.

I created a CLR stored procedure as well and when I tried to open the connection using oledb it gave me an unspecified error as well. Even odbc errored out.

2005 must have more strict permissions. What is funny is that I had the stored procedure working in 2005 and all of a sudden it stopped working. I even tried rebooting the machine.

I have seen sites that suggest you give read/write to the temp folder of the user account that the sql service runs under and that did not work either.

The SQL service is running under a domain account, so that is not an issue either.

Drives me crazy that it works in 2000, but not 2005. If anything, you would think it would be the other way around.

|||

Well, I tried rebooting the machine yesterday and it did not work like I said before, but today I stopped the sql service and then restarted. Now it works.

That is messed up. I sure hope M$ fixes this problem as MANY people are having the same issue.

This will be fine for now, but I plan to change the way we do backups for next year.