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 1234»»»

A Good Word for ODBC Expand / Collapse
Author
Message
Posted Wednesday, September 9, 2009 9:16 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:04 PM
Points: 590, Visits: 2,565
Comments posted to this topic are about the item A Good Word for ODBC


Best wishes,

Phil Factor
Simple Talk
Post #785469
Posted Thursday, September 10, 2009 4:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:29 AM
Points: 229, Visits: 421
Thanks Phil, there I was thinking I was a decrepit old dinosaur sticking with my completely functional ODBC connections and eschewing Linq and suchlike on the basis that I could do everything I needed to.

These youngsters and their bleeding edge complexity, eh?
Post #785604
Posted Thursday, September 10, 2009 7:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 2, 2009 6:43 AM
Points: 57, Visits: 151
I've relied heavily on ODBC, combined with MS Access for the past 10 years. At my last company virtually all the reporting out of the Syteline ERP system was via a pair of MS Access databases that connected through the Progress DB ODBC connection. This was a small company where the number of concurrent users in Access DBs was never an issue.

Currently I use MS Access and ODBC to connect to a Syteline ERP System running on SQL Servers for custom and adhoc queries and reports. I have found this combination to be extremely fast for both development and execution.

Unfortunately, our corporate IT department refuses to allow anyone else to use these tools. They restrict users to only the existing ERP system reports and a limited number of custom queries written in a product called IEV. Their apparent reasoning is "ODBC puts too much workload on the SQL server."

However, I find this hard to believe. I recently created a set of Access/ODBC queries that extract/merge/extrapolate data to an excel spreadsheet for each of 3 companies. It takes less than 30 seconds for the queries to run and create the spreadsheet for 1 company, so all three are completed in less than 2 minutes.

Prior to my creating this application, users had to run an IEV query that only dumped data for each company (about 6 minutes to execute for a single company), then spend an hour or two merging and extrapolating data.

I have read several SQL related posts that pretty much state "never allow anyone to connect to a SQL server using MS Access and ODBC.

However, I have never seen any explanation as to why.

Can someone please explain in nice techincal terms why using a tool that ties up a SQL server for over 18 minutes (IEV for 3 companies) is better than using a tool that ties up a SQL server for less than 2 minutes (Access via ODBC)?


Post #785672
Posted Thursday, September 10, 2009 7:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:42 AM
Points: 1,930, Visits: 1,013
Good old SQL Query Analyzer that shipped with SQL Server 2000 uses Microsoft's ODBC driver to connect. The funny thing is that you can use Query Analyzer to connect to SQL Server 2000, 2005, and 2008. I actually prefer Query Analyzer over the new management studios for 2005 and 2008.
Post #785692
Posted Thursday, September 10, 2009 8:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 1,935, Visits: 2,103
I always liked the abstraction of using a DSN from the actual connection that ODBC provides, and that you could easily deploy a DSN to a client computer in a file. For SQL Server, you can setup aliases in the SQL Server Configuration Manager for abstraction, but that is propriatary to SQL Server.

Speaking of complexity in connection strings, OLE-DB connection strings used by ADO/ADO.Net are pretty horrific so I don't see any improvement there. I think the bigger problem with ODBC was older applications accessed it through RDO and DAO objects. When Microsoft developed OLE-DB and ADO, they shoved ODBC into the background by making an extra layer of complexity to the connection. Instead of (propriatary driver -> ODBC -> RDO or DAO) it was now (propriatary driver -> ODBC -> OLE-DB provider for ODBC -> ADO). That's when ODBC started getting a bad name, and as more database vendors build their own OLE-DB providers, ODBC beame less relevant. I've noticed that many non-Microsoft products tend to support ODBC better than OLE-DB, especially in the open source world.
Post #785702
Posted Thursday, September 10, 2009 8:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 1,935, Visits: 2,103
bob.willsie (9/10/2009)
...I have read several SQL related posts that pretty much state "never allow anyone to connect to a SQL server using MS Access and ODBC.

However, I have never seen any explanation as to why...


The biggest problem with that combination was always on the MS Access side, not ODBC. Many people who created Access front end applications that used ODBC data sources didn't know to use pass-through queries. If you don't use pass-through queries then Access had a tendancy to try to copy the entire table from the server to the application and then run the query locally. If you use Access with pass-through queries or with Access 2000 and later create an "Access Project", then you are using the back end database's query engine, not the local Access engine.
Post #785713
Posted Thursday, September 10, 2009 8:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 2, 2009 6:43 AM
Points: 57, Visits: 151
Chris;
Maybe your getting to what I need to know. And you have given me a chance to show my ignorance.

I typically write a string of queries that extract small chunks from the server and store them locally, then merge, extrapolate, etc. locally. I found this executes much faster than trying to do lots of complex joins and subjoins all in one query and passing that query to the server. Typically I never join more than three tables if at all possible.

Are small, highly targeted queries what you mean by "pass-thru" queries? If not, please explain.

I must confess that on the occasions I've pulled down the full contents of one or more tables, but if I do that I download the complete table(s) to a local table in my Access DB, then mix and match locally to my hearts desire. I usually on do this with extremely large historical data that I know I'll be thrashing quit a bit.

Post #785730
Posted Thursday, September 10, 2009 8:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:29 AM
Points: 229, Visits: 421
Depends what you're trying to achieve Bob.

A pass through query will just literally pass the query string to the DB, execute it, then return the results. Lord knows what Access attempts to do architecturally if you do local queries on linked tables, maybe someone else on the forum knows the answer.

There's nothing intrinsically wrong with copying a whole table or a chunk of data to the local client when you need to do further data manipulations on it - indeed it might save server resources when you have some serious number crunching/transformations to do on the data that is being returned. Having said that you should bear in mind that the SQL server is going to be a lot better at that number crunching than Access and the timing of these queries may well be important in terms of when the business needs the server performance (I run some jobs against stored procedures at night to get around this).

I just take everything on a case by case basis, if a query is putting a load on the server then see what you can do to calm things down - either by cutting the data you're bringing over (nothing worse than SELECT * when you only need a few columns), or moving your basic queries into views on the server. If server performance is more important than client performance then you're probably better off taking the data in a relatively raw form and crunching it on the client.

It sounds to me like you're not the DBA at your company, and DBAs can be pretty prescriptive where their servers are concerned, you really need to make a strong business case for running queries against the server. Personally I would be OK letting people have access to Views or Stored Procedures I had created myself if they approached me and told me what they wanted (so in your case each View would be your basic three table joins which I'd allow you to download into Access to work on further) but I would not allow ad hoc connections to the server, I'd lose control of database performance and security, and I am responsible for that.
Post #785757
Posted Thursday, September 10, 2009 9:20 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 7, 2012 9:23 AM
Points: 304, Visits: 716
This is an excellent editorial and exactly right on point. ODBC enabled many coders of my generation to make great software systems and in that, a great living. Even better ODBC offers a flexibility not found easily today. In the 90's I worked for a company where we wrote software in Visual FoxPro, and connected to any database that had a solid ODBC driver; SQL, Oracle, DB2, and more. It was "cool", flexible, and it just worked.

As well, down in the "trenches" ODBC was and is overall very easy to work with. You could "ignore the driver behind the curtain" and depend that data would flow in your app as needed. Sure, there were glitches but...

These days we do .NET and for all the wonder, hype, and ill-percieved "must have" of .NET, working directly with data is frankly woefully lacking. Especially so compared to the ODBC days. Data is astounding slow in operations that would otherwise be astoundingly simple, and one of my own biggest complaints is that you would think SQL and .NET would work well together - after all being both MS products. Not so, in fact Microsoft has miles and miles to go in the .NET world to even catch up to the ease of use and performance of ODBC.

Every year we seem to make programming and data management more and more complex with no real gain or benefit. Microsoft's long played-out excuse that this must be the case to support web apps is just simply ridiculous. As well, each year we see old technologies repackaged and then released as something "new", or even more insulting, "revolutionary".

Whatever happened to the KISS principle? ODBC was and is that. It works - so naturally, it will be discontinued and then re-released in another 10 years and sold as "new and revolutionary".


There's no such thing as dumb questions, only poorly thought-out answers...
Post #785787
Posted Thursday, September 10, 2009 9:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 2, 2009 6:43 AM
Points: 57, Visits: 151
Thanks for the info Richard.

You are correct, I am not the DBA, I am the lead system analyst for our division.

Perhaps the issue is the difference between static information needs which views and stored procedures handle nicely, and ad hoc needs.

Because many ERP systems come with limited or very generic queries and reports, it's been my experience that first I have to build ad hoc queries and reports to fill an immediate need, about 50% of which may get turned into static information needs.

Our IT department has set up a "reporting" server, however it's not working out very well because connections get blown away every 30 minutes when the server gets updated.

As far as security goes, I'm using strictly read only connections with no write permissions.

I am however very concerned about performance issues, as I wrote in my original post. And, I admit to having inadvertently written a "couple" of open ended queries in my past. However, those are few and far between, and quickly killed by the DBA once I realize what has happened.

Perhaps I am too "old school". I take the view that if a user has permissions to the data they should be able to access it and manipulate it with whatever tools are at their disposal that can make things happen the quickest and easiest.

Unfortunately, it appears to me that the move to SQL server and similar products has resulted in the exact opposite happening. We appear to have traded utility and functionality for security.
Post #785793
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse