ODBC Linked server in SQL 2005

ALawrence007
ALawrence007 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Developer and Administrator
Commented:
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

Author

Commented:
Worked First Time!!
Thanks

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