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 from SQL Server 2008R2 to SQL Server 2012 Expand / Collapse
Author
Message
Posted Thursday, January 16, 2014 10:42 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:14 PM
Points: 38, Visits: 267
Hello folks

Is it possible to create linked servers from SQL Server 2008R2 to SQL Server 2012? I tried from SQL 2012 to SQL 2008 doing the following:

EXEC master.dbo.sp_addlinkedserver
@server=N'SQL2008Server',@srvproduct=N'''''',@provider=N'MSDASQL',
@provstr=N'DRIVER={SQL Server};SERVER=SQL2008Server;Trusted_Connection=yes;'

And it works, but when I try to do the same from SQL 2008, it creates the linked server successfully but i'm not able to see anything even if I add remotelogin.

The above provider was the only one that worked for me, using as reference:
http://social.msdn.microsoft.com/Forums/en-US/ffd0741e-ddc3-4edb-bbe7-5730c7459f8b/linked-server-sql-2012-to-sql-2008?forum=sqldataaccess

I hope you can help me since my requirement is to get SQL2012 information from SQL2008.

Post #1531702
Posted Friday, January 17, 2014 1:14 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:46 AM
Points: 845, Visits: 2,331
Try something more like the SQL2008 one of these - use the source database's version of native client as your @provider, essentially.

-- FROM 2012
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'TargetServer'
-- FROM 2008/2008R2
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'sql_server', @provider=N'SQLNCLI10', @datasrc=N'TargetServer'
-- FROM 2005
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'sql_server', @provider=N'SQLNCLI', @datasrc=N'TargetServer'

Post #1532259
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse