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.
4.) Click the “Provider Options“ button and
-Check the “Allow InProcess“ checkbox
-Check the “Index as Access Path“ checkbox
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
6.) On the Server Options make sure the following options are checked:
- Data Access
- Use Remote Collation
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.