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)
3) Type in your Teradata username & password in the appropriate textboxes on the Security tab:
4) You can build a SQL query like this:
--(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.
FROM OPENQUERY(ENCORE, 'SELECT field1 FROM B8_PROD_VIEW.table_name'
Hope this helps!