Linked server not registered? - 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 2005 Forums

SQL Server Administration (2005)

Linked server not registered?

Author

Topic

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-07 : 11:42:10

Trying to use OPENROWSET in 2005 to pull data from a 2000 server.
I have created a linked server named "ABC". But when I execute OPENROWSET I get an error message back:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "ABC" has not been registered.

This is not my comfort zone, so any advice on what settings I need to use is appreciated.

e4 d5 xd5 Nf6

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-07 : 12:58:21

How did you create linked server?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-07 : 13:04:49

Tried both SSMS and sp_addlinkedserver.
Linked server shows up in SSMS list of linked servers.

e4 d5 xd5 Nf6

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-07 : 13:10:03

Why use openrowset if you have linked server created? Tried with four-part name or openquery?

pootle_flump

1064 Posts

Posted - 2007-05-07 : 13:33:06

Hi Blindman

I understand OPENROWSET, OPENQUERY, OPENDATASOURCE to be an alternative to linked servers.

quote:
Originally posted by BoL for OPENROWSET

This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB.

The only advantage I know of is you can use or more of the above to include a sproc output as a derived table in a query. Otherwise just use four part naming as suggested. This works fine for me between SQL 2k dev and SQl 2k5 dev on my laptop.

HTH

pootle_flump

1064 Posts

Posted - 2007-05-07 : 13:42:17

Bg your pardon - OPENQUERY is especially for linked servers, passing the string to the linked server to execute. Is this what you meant?

BTW - works fine for me:

if exists (select null from	master.sys.servers where name = '(Local)') begin
	exec sp_dropserver @server = '(Local)'
end

exec sp_addlinkedserver @server= '(Local)'
	
select top 1 *
from	[(local)].pubs.dbo.authors

select *
from openquery([(local)], 'SELECT top 1 * from pubs.dbo.authors')

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-07 : 14:23:43

Four part naming convention works.
But I'm still foggy on why openquery is unable to see the linked server.

e4 d5 xd5 Nf6

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-08 : 10:35:14

Check mdac version on the machine you are working with.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources