We operate DI 11.7.3.10 on Windows 2003 and host our repositories on SQL Server 2005. Our DI servers have two different SQL Server ODBC drivers installed: “SQL Server” and “SQL Native Client”. We would like to insure that the various DI elements (JobServer, Designer, etc.) use the latter driver only. Does anyone in this forum know of a method to configure DI to pick only the latter?
Background…We incur intermittent connection drops between DI jobs and the SQL Server repository for DI. The symptoms are somewhat akin to those in postings https://bobj-board.org/t/99566 and https://bobj-board.org/t/99295. We have tried those posted suggestions and are now exploring other mitigation techniques.
Is the SQL server on the same machine as DI? (And is the repo database on the same server as DI, then?) If that’s an option, you might try that.
We recently moved to some new hardware, and the error started popping up again. Always with “Shared Memory” at the beginning. So we adjusted the SQL Server client config to disable shared memory, and force the usage of Named Pipes. (We also did the TCP Chimney fix specified in some of those postings, for good measure.) The errors have largely gone away, although performance does seem to have dropped a small amount.
Thanks for the suggestion, but our DI and SQL services operate on separate servers–with communication between them via ODBC. That is why this posting is focused on techniques for getting DI to use what we consider to be the better ODBC driver.
for Datastores what is the Datastore type ? if its MS SQL then try using ODBC and create a system DSN using SQL Native Client, and use that DSN in Datastore, you try this by adding a new configuration to the Datastore for ODBC
Manoj_D: Thanks for the idea. That should work for DataStores. However, I forgot to mention in the post that we are mostly interested in ensuring ODBC selection for communication with the DI Repositories. Do you have any suggestion for how to force ODBC selection for the Repositories?
Manoj_D: Thanks for the suggestion, but our issues are with our DI Job Servers–not the DI Designer. As an experiment, I did rename the DLL for the unwanted SQL Server driver on the servers that host the DI Job Servers. But the Job Servers would then not launch, as they apparently have an affinity for the unwanted driver. I am seeking a method for convincing the DI JobServers to use the preferred ODBC Driver.
If you intend to suggest that we “remove” the unwanted driver (sqlsrv32.dll) from our Windows 2003 installations, please note that I am unaware how to do that. So I would appreciate guidance on that as well. It seems to me, however, that this driver is an intrinsic component of the operating system.
and in particular the first link (FAQ) on that page. Straight from Microsoft: “ODBC in SQL Server Native Client and also in MDAC is a true native API for SQL Server.”
dnewton: Yes, I concur. We have found that for our other SQL Server applications, we enjoy better stability via the Native Client versus the intrinisic driver that comes with Windows. We wish to see if the improvement carries over into DI Job Servers. That is why we are seeking a method for forcing DI Job Servers to pick that the Native Client ODBC driver over the other one.
I think I am saying the opposite: That the ODBC driver for Microsoft really “is” the native driver… that they are one and the same thing, or at least share the same underpinnings.
dnewton: Sorry for misunderstanding your earlier comment. Although I suppose that there are some resources shared between the two drivers, the associated .DLL files are seemingly independent of each other. That is, I can “hide” the .dll associated with the old ODBC driver and still successfully operate a query that references the newer ODBC driver. Furthermore, we have empirical experience that the native client improves reliability in interactions with SQL Server 2005 over the older ODBC driver.
Regardless of the degree to which there are interrelationships between these drivers, I am still interested in pursuing the original question in this posting: How to get a DI Job Server to pick one ODBC driver over the other.
(BOB member since 2009-11-09)