July 26, 2012 at 1:44 pm
Hi,
I have an application running on SQL 2005 that we want to create a data warehouse on a new SQL Server 2012 box. I don't have SQL2012 installed yet, but I'm wondering if SQL2005 is capable of linking to a SQL2012 server? I'm OK with having to install a SQL2005 service pack if necessary, but I'm just wondering if it's actually possible. Colleagues in my organization are skeptical that it will work at all.
Thx
Ron
July 30, 2012 at 9:53 pm
You can. I just tried it. You'll just need to install the 2012 Native Client to get the provider.
Code to create a Linked Server that uses the logged in user to authenticate to the remote server:
USE [master]
GO
/****** Object: LinkedServer [.\SQL2012] Script Date: 7/30/2012 9:51:47 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'.\SQL2012', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'.\SQL2012',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'.\SQL2012', @optname=N'use remote collation', @optvalue=N'true'
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 31, 2012 at 12:54 am
Thanks! We are in the process of installing SQL2012 and we'll be able to do our own test in a few days, but thanks for confirming!
Ron
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply