Setup SQL Server 2008 R2 Linked Server To the Teradata Database

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

This blog outlines the steps to create a SQL Server 2008 (64-bit) R2  Linked Server to the Teradata Database.

1. Download and install

  1. The ODBC Driver for Teradata 64-bit
  2. The OLE DB Provider for ODBC 64-bit if it is not included in your OS. You can use the steps in the "Test the ODBC Data Source" section to see a list of OLE DB Providers currently installed on your server.

2. Create an ODBC Data Source

  1. Start the ODBC Data Source Administrator
  2. Select System DSN. User DSN will not work because SQL Server service will not have access to the User DSNs.
  3. Click Add ... button
  4. Select Teradata ODBC driver
  5. Fill in the required fields in the ODBC Driver Setup for Teradata Database dialog box

3. Test the ODBC Data Source

  1. Start Windows Explorer
  2. Navigate to a temporary directory
  3. Right-Click and select "New" - > "Text Document"
  4. Change the file extension to UDL (for example Foo.UDL)
  5. Double-Click the file to display the Data Link Properties dialog box
  6. Select the Provider tab
  7. Select the Microsoft OLE DB Provider for ODBC Drivers
  8. Select the Connection tab
  9. Select Use data source name radio button 
  10. Select the Data Source created in the previous section
  11. Specify a Teradata User ID
  12. Specify a Password
  13. Click the Test Connection button

 4. Create a SQL Server Linked Server Object

  1. Start SQL Server Management Studio
  2. Expand Server Objects
  3. Expand Linked Servers
  4. Expand Providers

  1. Make Sure MSDASQL (Microsoft OLE DB Provider for ODBC Drivers) is installed on the server
  2. Right Click on Linked Servers
  3. Select New Linked Server...
  4. Assign a name to the Linked Server; I suggest a single word name like TD.
  5. Select Microsoft OLE DB Provider for ODBC Drivers
  6. Set the Product Name to Teradata Database.
  7. Set the Data Source to the ODBC DSN Name created in section 2 above.

  1. Select Security page
  2. Select Be made using this security context
  3. Set the Remote login field to the Teradata Database User Id
  4. Set the With password field to the Teradata Database Password

  1. Click OK button

 5. Test the new Linked Server

  1. Open a New Query
  2. Execute a SELECT statement using a 4 part name (LinkedServerName..DatabaseName.TableName).
    1. The Linked Server Name crated in step 3 above. 
    2. The Teradata Database does not support Catalog; leave it blank.
    3. The Database Name
    4. The Table Name
Select * from TD..NorthwindEF.Customers

 References:

15 Comments
Great instructions. Thanks a bunch for this. Helped me out tremendously.
@jbmarshalliii: Out of curiosity, how does this help you?

We would like to build a spreadsheet- or datasheet-like interface to capture data that is not already stored conveniently in an operational system. Generally, these data sources are on user desktops in Excel. We looked to Sharepoint (instead of Excel-only) to add some governance and sharing features, but we have not found the right solution to meet all our needs yet. Sharepoint can help us write directly to Teradata, but only with a form, not a datasheet interface. If you want the datasheet look and feel with Sharepoint, you have to write the data to SQL Server first, then move it separately to Teradata.

I'm wondering if setting up a linked server will help us use the datasheet view in Sharepoint and write directly to Teradata.
The windows editions under Teradata ODBC Driver for Windows download web page do not have windows 2008 R2 listed. Did anyone have issue installing the driver to windows 2008 R2 to make it work? I will give a try but want to know if someone else has tried it. Thanks.
Teradata Employee
See "Teradata Tools and Utilities Supported Platforms and Product Versions" manual:

http://www.info.teradata.com/templates/eSrchResults.cfm?frmdt=&todt=&txtrelno=&txtpid=3119&rdsort=Title&prodline=all&txtttlkywrd=&srtord=Asc

Microsoft Windows Server 2008 R1 is a supported platform even though it is not listed.
Enthusiast
I hope this blog is still active. I have plenty of questions to ask.
Microsoft SQL Server Management Studio will not recognize my driver. The connection test works and I can execute queries from SQL Assistant on my server. However, when I try to link the server using the same connection, I receive the error below. The interesting thing is that the driver is there even though the error says that it can't be found. Any ideas?

TITLE: Microsoft SQL Server Management Studio

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "WMB".
OLE DB provider "MSDASQL" for linked server "WMB" returned message "Specified driver could not be loaded due to system error 126: The specified module could not be found. (Teradata, C:\Program Files\Teradata\Client\14.00\ODBC Driver for Teradata nt-x8664\Lib\tdata32.dll).". (Microsoft SQL Server, Error: 7303)

I removed all teradata drivers and restarted the windows server. Then installed only the recent 64 bit drivers. The issue wasn't corrected until the server was booted a second time.
N/A

BIG THANKS!!!!

I am trying to add a TD linked server on SQL server. The last step of executing the new linked server is running from last 24 hours. Any idea what could cause this issue? 

Need to link TD server urgently.

Regards

Vivek

I just cancelled the execution and I am not installing the linked server to TD. 

But now the server is extremely slow and sometimes give error for not executing. I am not able to run any other packages on the SSIS. Please help.

Regards

Vivek

Is it possible to set up a SQL Server linked server using the .net Framework Provider for Teradata rather than going via ODBC?

The reason for asking is that the .net provider is significantly faster than ODBC when used in SSIS and I want to harness this power via a linked server.

The option to select '.net Framework Provider for Teradata' is not provided when setting up the linked server using the SQL Server Management Studio gui.  Is it possible to manuall add the linked server, for instance doing something like the following:

    exec sp_addlinkedserver    @server=ERNET, @srvproduct='Teradata',

                               @provider='???????', 

                               @datasrc='172.29.232.143' 

If so - what is the value for ??????? (I've tried 'TDOLEDB.1' but I get the following message: 'cannot create an instance of OLE DB provider "TDOLEDB.1" for linked server "ERNET". (Microsoft SQL Server, Error 7302)) and does @srvproduct need to be defined?  I'm using SQL Server 2014.

Thank you

David

Teradata Employee

To my knowledge SQL Server Linked Server requires an OLE DB Provider (e.g. Microsoft OLE DB Provider for ODBC). So the answer is no, you cannot use the .NET Data Provider for Teradata to create a Linked Server from SQL Server to the Teradata Database.

Hi All,

I Followed the above steps everything worked fine until the last step while creating Linked server in SQL Server, I am getting the error 

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TDTEST".

OLE DB provider "MSDASQL" for linked server "TDTEST" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed". (Microsoft SQL Server, Error: 7303)

Can anyone please help me in  solving this problem?

Thanks,

Kiran

jepatte: Thanks for instruction and patience, your suggestion works as a charm! basically after install the odbc driver, you have to reboot your server before the msdasql work in link server - 20160216

We are facing the same error as Kiran. Can you please let us know how this was resolved?

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "XYZ".

OLE DB provider "MSDASQL" for linked server "XYZ" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed". (Microsoft SQL Server, Error: 7303)