SQL Server Discovery

Better, faster, cheaper ...pick two.
posts - 14, comments - 80, trackbacks - 0

Wednesday, November 30, 2005

Linked Server to Teradata - SQL2005

Maybe this is known among those who have to connect to Teradata stores from SQL Server but I needed to accomplish this from SQL Server 2005 and didn't find any published resources on how to do it. With a little experimentation, this is what I came up with:

1) Fetch the Teradata OLEDB driver from teradata.com and install it on the SQL Server 2005 system.

2) In SQL Management Studio (AKA Enterprise Manager) create a new Linked Server with the following configuration:
-Linked Server:  choose any name (ex: ENCORE)
-Provider:  OLE DB Provider for Teradata (select from dropdown list)
-Product Name:  Database name (ex: databasename)
-Data Source:  Server name (ex: server1.corp.domain.com)
-Provider String:  (leave blank)

 photo 1_LinkSvrGen_2K5_zps52c12685.png

 3) Type in your Teradata username & password in the appropriate textboxes on the Security tab:

 photo 2_LinkSvrSec_2K5_zpsfa18aadc.png

4) You can build a SQL query like this:

,    field2


--(notice the 4 part naming string with second element skipped)

But using the OPENQUERY method will be much faster:


Viola! you have a dataset from the remote Teradata store returned inside of SQL Server and ready to perform distributed queries like joining to local tables.


Hope this helps!


posted @ Wednesday, November 30, 2005 8:56 PM | Feedback (29) |

Powered by:
Powered By Subtext Powered By ASP.NET