Are all DSN's really ODBC?

I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't really
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
"Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
Is there an even better provider to use?
It's all so confusing!!
Maury
Why use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury
|||"Keith Kratochvil" wrote:

> Why use DSN's at all?
That's what I'm asking.
Maury
|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon
|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstractions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstractions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. There exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury

Are all DSN's really ODBC?

I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't reall
y
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
" Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
Is there an even better provider to use?
It's all so confusing!!
MauryWhy use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> " Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury|||"Keith Kratochvil" wrote:

> Why use DSN's at all?
That's what I'm asking.
Maury|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstra
ctions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstr
actions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. Th
ere exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, b
ut
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's i
n
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't rea
lly
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
I
> use a new string, one like...
> " Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maurysql

Are all DSN's really ODBC?

I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't really
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
"Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
Is there an even better provider to use?
It's all so confusing!!
MauryWhy use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
--
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury|||"Keith Kratochvil" wrote:
> Why use DSN's at all?
That's what I'm asking.
Maury|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstractions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstractions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. There exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury

Anyway to Stop DSN & DSNless connections to SQL Server?

I'm trying to setup a new server but need to stop people accessing the database via Access, Excel etc

Is there any way to set SQL server up to stop connections from such applications?

Is there any way to allow only certain users to connect via a DSN

We are not using windows authentication on the server

Cheers

No, you cannot restrict connections based on the application that made them. But you can restrict connections based on the account they were made with. You can try to have your application connect to SQL Server using a specific account and not grant any connection rights to the users - then they would only be able to access the database through the application. Of course, if they would debug the application, they could figure out how it connects to SQL Server (the account and the password), but this would at least prevent accidental connections from other applications.

Thanks
Laurentiu