June 9, 2009 at 9:25 am
I need to create a linked server that points to a SQL Server, but I need to named it differently, so I can't use the regular 'Server Type: SQL Server', I need to pick one of the providers. So I try to pick 'Microsoft OLE DB Provider for SQL Server' but for some reason it always defaults to 'SQL Native client'. What is the difference between those two?, how can I force it to take OLE DB Provider?
June 9, 2009 at 9:29 am
Here's the script I use to create linked servers to SQL Server, when I want the name to be something other than the actual name of the server or instance.
-- Drop Any Existing Link
IF EXISTS ( SELECT
srv.name
FROM
sys.servers srv
WHERE
srv.server_id != 0
AND srv.name = N'MyLinkedServer Name' )
EXEC master.dbo.sp_dropserver @server = N'MyLinkedServer Name',
@droplogins = 'droplogins';
-- Create New Link
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer Name',
@srvproduct = N'', @datasrc = N'MyServerInstance', @provider = N'SQLNCLI';
-- Add Default Login (if applicable)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer Name',
@useself = N'False', @locallogin = NULL, @rmtuser = N'SQLLogin',
@rmtpassword = 'SQLLoginPassword';
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'collation compatible', @optvalue = N'false';
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'data access', @optvalue = N'true';
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'dist', @optvalue = N'false';
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'pub', @optvalue = N'false';
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'rpc', @optvalue = N'true';
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'rpc out', @optvalue = N'false';
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'sub', @optvalue = N'false';
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'connect timeout', @optvalue = N'0';
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'collation name', @optvalue = null;
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'lazy schema validation', @optvalue = N'false';
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'query timeout', @optvalue = N'0';
GO
EXEC master.dbo.sp_serveroption @server = N'MyLinkedServer Name',
@optname = N'use remote collation', @optvalue = N'true';
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2009 at 10:04 am
Thanks GSquared. The script is useful but what what I need is to force the provider to be OLEDB and not SQL native client... do you know what is the difference between those two?, one programmer here is having a problem with distributed queries and he blames the SQL Native provider, he claims that SQL OLEDB provider is the one we should use, but when I create the linked server it always defaults to SQL native client. Thanks!
June 9, 2009 at 11:17 am
as i understand it, when a linked server is created between two 2005/08 servers, the linked server will always use the native client SQLNCLI provider instead of SQLOLEDB; if you create a link that features a 2000 server, only then will it use the other driver.
I don't believe you have a choice, but I use linked servers all the time, and I've never had an issue where the linked server was using SQLNCLI.
I'm trying the sample script above, because it also implies you can alias the name of the linked server, even if it is a SQL server...i've never been able to do that either.
Lowell
June 9, 2009 at 11:26 am
ok i learned i can alias a linked server, excellent...
but even though i explicitly said to use a SQLOLEDB provider, when i look as sys.servers, SQLNCLI is being used, as I suspected:
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',@srvproduct = N'', @datasrc = N'DBSQL2K5', @provider = N'SQLOLEDB';
-- Add Default Login (if applicable)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer',
@useself = N'False',
@locallogin = NULL,
@rmtuser = N'sa',
@rmtpassword = 'NotARealPassword';
Lowell
June 9, 2009 at 11:37 am
Antero Ruiz (6/9/2009)
Thanks GSquared. The script is useful but what what I need is to force the provider to be OLEDB and not SQL native client... do you know what is the difference between those two?, one programmer here is having a problem with distributed queries and he blames the SQL Native provider, he claims that SQL OLEDB provider is the one we should use, but when I create the linked server it always defaults to SQL native client. Thanks!
SQLNCLI is an OLEDB provider, per Books Online. Check out the article on sp_addlinkedserver. The dev probably doesn't understand what he's saying.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2009 at 11:47 am
wouldn't problems with distributed transactions be because the "Distributed Transaction Coordinator" service one one or both servers are not running / on manual start instead of automatic?
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply