January 16, 2014 at 10:42 am
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:
I hope you can help me since my requirement is to get SQL2012 information from SQL2008.
:hehe:
January 17, 2014 at 1:14 pm
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'
May 8, 2014 at 8:20 am
Create a linked server is quite simple. Follow the below steps-
Step 1 – Connect SQL server, using 'Database Engine' as server type along with proper Server name and authentication.
Step 2 – Into object explorer, navigate to linked server under '**Server Objects**'.
Step 3 – Right click on 'Linked Servers' - select '**New Linked Server**'. Fill all the required detail in respective dialog.
Step 4 – After filling the entire details click on Ok button, Linked server is configured and it should work. In case if it doesn't respond, once restart the SQL server.
For more detail click here
May 8, 2014 at 8:26 am
vipulsachan.kiet 1412 (5/8/2014)
Create a linked server is quite simple. Follow the below steps-Step 1 – Connect SQL server, using 'Database Engine' as server type along with proper Server name and authentication.
Step 2 – Into object explorer, navigate to linked server under '**Server Objects**'.
Step 3 – Right click on 'Linked Servers' - select '**New Linked Server**'. Fill all the required detail in respective dialog.
Step 4 – After filling the entire details click on Ok button, Linked server is configured and it should work. In case if it doesn't respond, once restart the SQL server.
For more detail click here
Thanks, but the problem is that no connection can be establish when creating a linked server from SQL 2008 R2 to a SQL 2012 🙂
May 8, 2014 at 11:46 am
Are both servers on the same domain ?
May 8, 2014 at 11:53 am
homebrew01 (5/8/2014)
Are both servers on the same domain ?
Yes they are, when I create the linked server in SQL 2012 to connect to a SQL 2008R2 Instance everything works fine. But my requirement is to create a linked server in SQL 2008R to connect to a SQL 2012 Instance because I need to concentrate information from several servers (2008R2 & 2012) into a central database(SQL 2008R2) since all the information gathering process is going to be launch from there.
I think there's no compatibility from lower to higher versions, only from higher to lower ones.
May 9, 2014 at 8:51 am
It should work still work. I had a linked server going from SQL2008 to SQL2000 and SQL 2008R2 to SQL2000 as well.
May 9, 2014 at 8:59 am
Markus (5/9/2014)
It should work still work. I had a linked server going from SQL2008 to SQL2000 and SQL 2008R2 to SQL2000 as well.
Yes I got your point and it actually works from Higher to lower version. The problem is when I create the linked server from SQL 2008R2 to connect to a sql 2012, no connection is made but if I create the linked server in sql 2012 to a sql2008R2 it works perfectly but I'm only able to access from SQL2012 to sql 2008R2 databases, when I connect into my sql 2008R2 Instance I'm not able to see this linked server I created in SQL2012.
you should try creating the linked server in SQL 2008R to connect a SQL 2012, (Lower to higher version) to see my suffering jeje, right now I'm using another alternative to acheive this connection :hehe:
May 9, 2014 at 9:07 am
Sorry, I meant I created linked servers up version... from 2000 to 2008 and 2000 to 2008R2. I also have one going from 2005 to 2008R2 as well. We don't have SQL2012 in house to try it here.
May 9, 2014 at 9:12 am
Markus (5/9/2014)
Sorry, I meant I created linked servers up version... from 2000 to 2008 and 2000 to 2008R2. I also have one going from 2005 to 2008R2 as well. We don't have SQL2012 in house to try it here.
Oh I see :-), maybe I was using a wrong driver for the connection, anyway, I had to change this to Openrowset and it has been working fine so far to acheive my requirements.
Thanks everybody for your support 🙂
July 21, 2014 at 7:47 am
Hey I have the same issue but only if I try and use a Connection "using a security context", if I use the "login's current security content" it works from 2008R2 to 2012.
Everything works fine from 2012 to 2008r2, did you get an answer?
August 9, 2015 at 7:00 am
A solution is in this long winded reply: mulitple solutions actually,
I was having a very similar issue creating the linked server via the SSMS gui approach, (i.e. from the lower version db environment to the higher db version environment, by right clicking in SSMS on "linked Servers" and following that wizard)
My environment is a local workgroup with two PC's connected, Machine A is windows xp sp3 running Sql Server 2008 R2 developer edition and Machine B is Windows 8.1 running Sql Server 2014 Developer edition.
The linked server creation attempt from A to B would fail to connect. I "seemed" to try every option the gui provided for security. (but as it turns out, not every possible combination between the security page and the General page)
I then used the middle method for sp_addlinkedserver mentioned earlier in this thread.
EXEC master.dbo.sp_addlinkedserver @server = N'MachineB\MSSQLSERVER2014', @srvproduct=N'sql_server', @provider=N'SQLNCLI10', @datasrc=N'MachineB\MSSQLSERVER2014'
The linked server creation succeeded. The remote catalogs were now visible and I can query the remote db via the linked server name, (fully qualified reference)
The above presumes that all the little issues for configuring workgroup or domain connectivity via tcp/ip and programs and ports are set up correctly.
I do not know for sure, but I would imagine that the lower to higher type of setup has some little issues when done through the SSMS gui.
Looking at the properties of the successfully created Linked Server, I see that the result of using sp_addlinkedserver method was to set the security context as "Be made using the logins current security context" (3rd option in that list). In the general page of the linked server properties the type of connection was "Other data source" with the first three properties populated, (as per the sp_addlinkedserver command used)
Provider: Sql Server Native Client 10.0
Product Name: sql_server
Data Source: MachineB\MSSQLSERVER2014
(that MSSQLSERVER2014 is just the default instance name I did with the 2014 install, and with "MachineB\" it is just the full instance name.)
Using that as a template I then made a connection through the SSMS gui using those same options but a new linked server name and it worked fine.
So, it is not really a Gui bug, more like a user error (exactly like a user error). In anyone's defense though, the options that seem natural in the gui method do not work. Unless of course you are already very familiar with how to tweak the setup.
Please excuse the long winded explanation. But, I often find it hard to follow loose or very short explanations, and as a result try to be as specific as I can be, hoping you can infer the right approach for your particular environment.:-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply