SQL Server Discovery

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

Tuesday, February 28, 2006

Linked Server to Teradata - SQL2000

1.) Download and install Teradata OLE DB driver on the target server where SQL Server 2000 is hosted.

2.) Right-click the Linked Server node under Security node in Enterprise Manager and select “New Linked Server...”

3.) On the General Tab:
     -Choose a Linked Server name.
     -Select “OLE DB Provider for Teradata“ from the “Provider Name“ Dropdown list under “Other data source“.
     -Choose a name for the Linked Server in the “Product Name“ textbox.
     -Enter the name of the Teradata database in the Product Name field.
     -Enter a server domain name or IP address of the server that Teradata database is hosted on.
     -Leave Provider String field blank.

Linked Server Properties - Teradata

4.) Click the “Provider Options“ button and
     -Check the “Allow InProcess“ checkbox
     -Check the “Index as Access Path“ checkbox

Teradata Linked Server - Provider Options

5.) On the Security Tab:
     -Under login section, select “Be made using this security context” Radio Button
     -Enter your Teradata login name in the “Remote Login” field
     -Enter your Teradata password in the “With Password” field

Teradata Linked Server - Security

6.) On the Server Options make sure the following options are checked:
     - Data Access
     - Use Remote Collation

Teradata Linked Server - Server Options

7.) OK out and you should be able to select the Tables node underneath the Linked Server you just created and the tables will appear as browsable objects.

For fast and efficient distributed queries use the OPENQUERY method like this:


SELECT * 
   FROM OPENQUERY
   (
   TERADATA_DATABASE, 
   'select 
       field1
   ,   field2
   from 
      table1'
   )


This performs all the query processing on the Teradata source server before pulling it across the wire to your local SQLServer machine making the operation much faster.

Hope this helps those of you needing to perform Teradata ETL with SQLServer 2000.

posted @ Tuesday, February 28, 2006 4:02 PM | Feedback (3) |

Powered by:
Powered By Subtext Powered By ASP.NET