June 4, 2018 at 12:27 pm
I am little confused. When i query sys.syservers i see provider name as 'SQLOLEDB' for some and 'SQLNCLI11' for some. We migrated from 2008R2 to 2016. Is there a good way to actually check which driver is linked server using?
In sys.sysservers it shows SQLOLEDB but the error is . I know why it is failing, i just don't know which driver is linked server actually using.
The OLE DB provider "SQLNCLI11" for linked server
June 4, 2018 at 1:23 pm
curious_sqldba - Monday, June 4, 2018 12:27 PMI am little confused. When i query sys.syservers i see provider name as 'SQLOLEDB' for some and 'SQLNCLI11' for some. We migrated from 2008R2 to 2016. Is there a good way to actually check which driver is linked server using?In sys.sysservers it shows SQLOLEDB but the error is . I know why it is failing, i just don't know which driver is linked server actually using.
The OLE DB provider "SQLNCLI11" for linked server
It's the same provider listed in sys.servers - the name is the linked server name, the linked servers will have is_linked = 1 and the provider is the provider listed.
You can also check the properties for the linked servers - right click on the linked server, select properties and the General page has the provider specified for the linked server.
You can also script out the linked server.
Sue
June 4, 2018 at 1:24 pm
Try looking at sys.servers instead of sys.sysservers. sys.servers is the newer system view and is more likely to represent the actual provider used in the link. For example, on one of my servers, sys.sysservers shows SQLOLEDB while sys.servers shows SQLNCLI for the exact same linked server.
June 4, 2018 at 2:44 pm
You guys are correct. Sys.sysservers is deprecated and shouldn't be use
I think the real question is any difference between SQLNCLI vs SQLNCLI11?
June 4, 2018 at 2:51 pm
curious_sqldba - Monday, June 4, 2018 2:44 PMYou guys are correct. Sys.sysservers is deprecated and shouldn't be useI think the real question is any difference between SQLNCLI vs SQLNCLI11?
Other than one being a specific version and the other being just the general SQL Server Native Client....with linked servers, using SQLNCLI or SQL Server will redirect to the latest version of SQL Server Native Client.
Sue
June 6, 2018 at 10:29 am
Now this is weird. I understand sys.servers is the right place to look at OLEDB provider. I have a linked server that shows SQLNCLI as provider in sys.servers but when it fails is says "OLE DB provider "SQLNCLI11" for linked server "Server2" returned message "Cannot start more transactions on this session.". I know how to fix the linked server issue but why is it saying SQLNCLI11?
June 6, 2018 at 10:43 am
curious_sqldba - Wednesday, June 6, 2018 10:29 AMNow this is weird. I understand sys.servers is the right place to look at OLEDB provider. I have a linked server that shows SQLNCLI as provider in sys.servers but when it fails is says "OLE DB provider "SQLNCLI11" for linked server "Server2" returned message "Cannot start more transactions on this session.". I know how to fix the linked server issue but why is it saying SQLNCLI11?
Because SQLNCLI will be redirected to the most current/latest version of the native client. So it was using the latest version SQLNCLI11
Sue
June 6, 2018 at 11:13 am
Sue_H - Monday, June 4, 2018 2:51 PM... using SQLNCLI or SQL Server will redirect to the latest version of SQL Server Native Client.Sue
Sue_H - Wednesday, June 6, 2018 10:43 AMBecause SQLNCLI will be redirected to the most current/latest version of the native client. So it was using the latest version SQLNCLI11Sue
One more time? 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply