Error 7399 - Part II - Closer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Error 7399 - Part II - Closer

Author

Topic

lc6529
Starting Member

11 Posts

Posted - 2003-02-11 : 11:27:47

After pouring through BOL, I typed in the following in the SQL Query Analyzer on my test server (SQL 2000 - NO Service Packs)

___

sp_addlinkedserver 'servername',
'Access 97',
'Microsoft.Jet.OLEDB.4.0',
'\\pcname\sharename\mdbname.mdb'

go

sp_addlinkedsrvlogin 'servername',
'false',
'sa',
'Admin',
NULL
go
___

Yahoo! It Worked! I could query my tables in SQL 2000.
After spraining my arm from patting my back. I copied the query to
diskette and pulled it up on my main SQL server (SQL 2000 - SP2)
and ran it.

When I try to view the tables I get ---- ERROR 7399.

The only difference is SP2... Any ideas?

lc6529
Starting Member

11 Posts

Posted - 2003-02-11 : 11:45:04

Verified!

I removed the linked server from my test server. Ran the Query again to verify it did indeed connect and I could query the Access
database.

I then removed the link and installed SQL SP2 and ran my Query and
when I try to view the Access tables I get Error 7399.

So something in SP2 is preventing this from working....

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-11 : 12:27:22

I'd check to see if the JET OLEDB provider is supported anymore if that's the case. Or perhaps the version of the provider has been updated/replaced.

lc6529
Starting Member

11 Posts

Posted - 2003-02-11 : 12:34:41

Good thought,

But everything worked under SQL 2000...it is when I installed service pack 2 and tried the exact same command (I stored the query in a file)...the error message came up.

I cannot think of why the JET OLE-DB provider would not work under SP2.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-11 : 13:12:49

Have you tried to see if the problem goes away with SQL2K SP3?

lc6529
Starting Member

11 Posts

Posted - 2003-02-11 : 13:39:52

Hmm, let me install SP3 and I'll let you know...

lc6529
Starting Member

11 Posts

Posted - 2003-02-11 : 13:44:14

Ahha! No need for SP3...

I found that if I set the linked server up to not use a login in
connecting to the Access DB

sp_addlinkedsrvlogin 'servername',
'false',
NULL,
NULL,
NULL
go

I could access the tables. At least this works on my test server.
To verify I connected/disconnected the linked server several times and even tried another Access DB.

Unfortunately, it failed on the production server which uses a domain account for the SQL Service (needed for email).

Sigh, more plugging away...

sthornton
Starting Member

2 Posts

Posted - 2003-04-08 : 21:04:35

HI,

I too am having the same problem.

I have found that when connected as an "sa" account tables can be seen on the server and queired in Analyser, but under domain accounts things don't work.

Have you found a solution to this?

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2003-04-09 : 15:38:27

Does configuring the linked server login like this help any? If not, the problem is probably in the jet driver which is probably updated by the MDAC portion of the service packs.

sp_addlinkedsrvlogin 'servername',
'false',
Null,
'Admin',
''
go

Jeff Banschbach
Consultant, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources