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

Native Client driver choice by SQL Agent Expand / Collapse
Author
Message
Posted Monday, November 11, 2013 12:18 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 11:20 AM
Points: 3, Visits: 31
How does the SQL Agent choose what version of the NC driver to use when connecting to linked servers? and/or how do you force the Agent to use a specific one using T-SQL?

Case in point:
I'm working on a stored proc on a Central Mgmt Server (CMS) that has linked server objects to other servers (ServerA - 2005, ServerB - 2008, ServerC - 2012).
The proc has a loop that connects to each server in CMS.master.sys.servers, gets some information/does some actions, then goes to the next server.
If the CMS connects to the servers in Ascending order (by SQL version), everything works fine.
If the CMS connects to a newer server (ServerB) and then an older one (ServerA), the job fails with error:
Msg 7314, Sev 16, State 1, Line 1 : The OLE DB provider "SQLNCLI11" for linked server "ServerA" does not contain the table ""master"."sys"."xxxxxxx"". The table either does not exist or the current user does not have permissions on that table. [SQLSTATE 42000]

The only solution I can think of is to hard code the server list into the stored proc in the correct connection order. Which is a bad way to do it, in my opinion. I'd rather have the agent recheck what driver it's using when connecting to each server, or tell it to use the 2005 NC driver for all connections.

Thoughts/Ideas?

Cheers,
J
Post #1513218
Posted Tuesday, November 12, 2013 6:22 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 11:20 AM
Points: 3, Visits: 31
Disregard problem. Turns out a variable (storing version specific info) wasn't being cleared at the end of each loop.
Post #1513436
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse