Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server Creation - Connect sql sever 2008 from 2000 Expand / Collapse
Author
Message
Posted Thursday, September 23, 2010 10:44 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 3:37 AM
Points: 369, Visits: 161
Hi,

Since this may be concerned as a kiddish question. But this is required.

I need to connect the sql server 2008 database from sql server 2000. Since the client has been using the 2000 version database and they want to pull some data from sql server 2008 database. I have tried to create the linked server for this with below T-SQL statements.

EXEC sp_addlinkedserver
@server = 'LS',@datasrc = 'ServerName',
@provider = 'SQLOLEDB', @srvproduct = '', @catalog = 'databaseName'

EXEC sp_addlinkedsrvlogin 'LS','false',NULL, 'Username', 'password'
EXEC sp_serveroption 'LS','data access','on'
EXEC sp_serveroption 'LS','rpc out','on'
EXEC sp_serveroption 'LS','rpc','on'


The linked server object is created. But when I am trying to fetch the objects with below query

select top 5 * from [CCM7Dev].OEDB70_intermediary.dbo.company

I have received the below error.

[color=#red][DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.[/color]

I am suspecting whether it is possible or not for below reasons.
>Version compatiability problem
>Provider mismatch or updagradation required
>Connection problem.

Note: I have used the valid credential to access the server. Openrowset method may not suit to my requirement.

Kindly advise me on this.

Thanks in advance.
Ramkumar.K


Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
Post #992472
Posted Wednesday, November 10, 2010 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:54 AM
Points: 1, Visits: 700
Didi you ever find a solution to this? I am having the same problem.
Post #1018650
Posted Wednesday, May 11, 2011 3:23 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:04 PM
Points: 11, Visits: 75
I thank to yoy for the script above, it so helpfull to me.

I have the enterprise edition for sql server 2008 and standar for 2000. No aditional problems.

Regards.
Post #1107378
Posted Thursday, May 12, 2011 4:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 15, 2011 5:01 AM
Points: 1, Visits: 7
Thanks Man. Atlease for you it has worked. .
May be the SQL Server version name you specified could help to investigate further on this.

Thanks.

Post #1107567
Posted Thursday, January 19, 2012 12:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 1:10 PM
Points: 19, Visits: 201
This solution worked fine for me as well.

Thanks a lot.

Lucas
Post #1239012
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse