Phil Streiff Blog

Better, faster, cheaper …pick two.

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.

Legacy Comments


promozioni casinò in rete
2010-04-08
re: Linked Server to Teradata - SQL2000
I am trying to set up a Microsoft SQL Server 6.5 machine as a linked server to a SQL Server 7.0 database for distributed queries. I have tried using Enterprise Manager, using sp_addlinkedserver as a Microsoft OLE DB Provider for SQL Server, and using sp_addlinkedserver as a Microsoft OLE DB Provider for ODBC. If anyone has sucessfully linked a SQL Server 6.5 database to SQL Server 7.0 for distributed queries, please write with info......

mehrnoosh
2011-09-28
re: Linked Server to Teradata - SQL2000
Hi
How can create the link server from sql server 2000 to sql 2000?