SQL Server Discovery

Better, faster, cheaper ...pick two.
posts - 13, 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:


SELECT
     field1
,    field2

FROM

      ENCORE..B8_PROD_VIEW.table_name
--(notice the 4 part naming string with second element skipped)


But using the OPENQUERY method will be much faster:

SELECT *
FROM OPENQUERY(ENCORE, 'SELECT field1 FROM B8_PROD_VIEW.table_name'
 


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!

-pj

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

Powered by:
Powered By Subtext Powered By ASP.NET