Potential "duh" question - ODBC
-
So, I wanted to ask an ODBC question. I know ODBC means open database connectivity and in old school days we used to create "ODBC connections" in windows to databases and MS Access files and stuff. Today, if I have a linked server in SQL Server that talks to Oracle using built in Oracle drivers using the tnsnames.ora file - is that still an "ODBC" Connection? I mean I suppose it is in that its two databases talking to each other but my stupid brain seems to think an ODBC driver would be different than doing a direct connection using Oracle. My system uses some linked servers to push and pull data between systems and this guy who is web service happy keeps telling me not use ODBC connections.... and I dunno my brain just keeps associating ODBC connections with setting up an ODBC connection in Windows and that what I'm doing is somewhat different. But yea, the Sr. DBA who has been working on SQL Server since version 6.0 should probably know this (me) ... but for some reason I'm having a brain fart. 🙂
- amy26 - Friday, March 17, 2017 4:32 AM
It depends on the driver you're using but most are now OLE DB. The older version would be ODBC. I would guess that's the guy may be talking about - use OLEDB driver instead of ODBC. Both use tnsnames file so that the database can be found.
The whole setting up the connection thing - maybe you're thinking back to setting up DSNs and using the ODBC administrator. That's how many/most were done years ago. And so many people did this with Access front ends to Oracle since Oracle reports pretty much sucked. But setting them up DSN connections like that isn't done too often anymore. Do a search on your PC for ODBC and you should be able to find it - program called ODBC data sources.
But you have always had some layer between SQL and Oracle (the driver or provider) for them to talk to each other since they don't speak the same languages. So the connection is like a translator.Sue
- amy26 - Friday, March 17, 2017 4:32 AM
Yes it is an ODBC connection. If you go under start<programs<admin tools<ODBC Connections < click ADD. You should be able to see the MySQL drivers.
MCSE SQL Server 2012\2014\2016
- Sue_H - Friday, March 17, 2017 8:27 AM
Right that was my thinking too... ODBC was older and the "old way" of setting up connections with DSNs. I of course do not have any of these connections established as DSNs. We are using the linked server capability within SQL Server which connects with Oracle drivers using the TNSNames from Oracle. I just wanted clarification that using a linked server in this manner was in fact "not" an ODBC connection but like you said, its an OLE DB connection right? And that is technically "different" than an ODBC?
- amy26 - Monday, March 20, 2017 7:17 AM
Both can be used with linked servers, both would need the tnsnames file. You get both with ODAC. So your linked server could be either really. Most of the client pieces come with both ODBC and OLE DB drivers, you use them essentially the same way so you need to pay attention to which one you are actually using. Check the provider used for the linked servers - Msdora or OraOLEDB are both OLE DB. Select * from sys.servers is probably the easiest way to go through those - lists the provider for each linked server.
Sue
-
Right yea I'm using 'OraOLEDB.Oracle'. So that is not an ODBC connection.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply