ODBC Linked server in SQL 2005

ALawrence007 used Ask the Experts™
Hi to all,

I need to set up a linked server using an ODBC connection. My server name is csdm and the user name will be awlt007.

How would I set this linked server up? Is this possible?

Thanks in advance.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Developer and Administrator
First of all you have to install the ODBC driver for the remote server (DB2, Oracle, Postres, MySQL driver etc). The you have to create a System DSN on the server with the odbcad32.exe. Then you can create an ODBC Linked server where the data source name is the given System DSN name. At the end you have to set the user and password to the remote server access.
This script do there also

EXEC master.dbo.sp_addlinkedserver 
	@server = 'MY_LINKED_SERVER', 
	@srvproduct = 'MySQLDatabase', 
	@provider = 'MSDASQL', 
	@datasrc = 'GivenSystemDSNName'

EXEC master.dbo.sp_addlinkedsrvlogin 
	@rmtsrvname = 'MY_LINKED_SERVER',
	@useself = 'False',
	@locallogin = NULL,
	@rmtuser = 'remoteusername',
	@rmtpassword = 'password'

Open in new window


Worked First Time!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial