linked server vs and ODBC

[DrSql]

Is there an advantage of using a linked server vs and ODBC connection built on the machine? If so, does this change when it is an Oracle connection?

Thanks!


Dr. Sql
vpathisseril@yahoo.com
Good Luck.

 

[mrdenny]

A linked server and an ODBC connection are different thinks.

ODBC is a connection protocal, the Open DataBase Connection Protocol. It's a standardised method of connecting to a database platform. It is the older slower database connection method. OLE-DB is faster than ODBC.

A linked server is a connection to another database server that allows you to access the remote data via T/SQL. It can use ODBC, OLE-DB, or any other connection method provided the database vendor provides drivers that you can use.

This all applies to SQL, Oracle, MySQL, Sybase, etc.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[DrSql]

So what would be diefference in ODBC vs linked server when creating a DTS package. Any performance?


Dr. Sql
vpathisseril@yahoo.com
Good Luck.

 

[Catadmin]

Given that ODBC is older and slower, if you can link to a server in EM, the Linked Server is the way to go.

BTW, if it is a package from one SQL Server to another SQL Server (both MS), I would advise avoiding the ODBC connection unless absolutely necessary. There's really no reason to use one, AFAIK, and your performance, assuming a persistant network connection, should be fairly good.

Mind you, I'm biased. @=)

Here's a direct quote from Books Online:

ODBC
When connecting to an ODBC data source, consider the following:

The Microsoft OLE DB Provider for ODBC requires a unique key on all destination tables with a BLOB data column when performing export operations.


When using the Microsoft OLE DB provider for ODBC with the SQL Server ODBC driver, all BLOB columns should be arranged after columns with other data types in a source rowset. You can use a SELECT statement to rearrange the BLOB columns to the end of the source rowset. The DTS Import/Export Wizard performs this operation automatically.


Important When using the Microsoft OLE DB Provider for ODBC with the SQL Server ODBC driver, attempts to preview stored procedures fail with a connection busy error. This problem does not occur if you use the Microsoft OLE DB Provider for SQL Server.

If a Microsoft ODBC Driver for SQL Server connection is being shared by multiple threads, the connection may fail, returning the error message "Connection is busy with results for another hstmt". In some cases, this affects packages built with the DTS Import/Export wizard. Use one of the following approaches to address this problem:
Set the MaxConcurrentSteps property to 1 to eliminate contending threads.


Create additional ODBC connections to eliminate connection sharing.


Use the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) to connect to the database. If you need to connect to a SQL Server 6.5 database, run Instcatl.sql to enable access with the Microsoft OLE DB Provider for SQL Server.

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

DrSQL,
Linked servers have nothing to do with DTS packages. ODBC would be the connection method that the DTS package uses to connect to the SQL Server to run the commands within the DTS package. You don't want to use ODBC when connection the Microsoft SQL Servers unless you are connection to a SQL Server version 6.5 and below.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)