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:

Discussion
jbmarshalliii 1 comment Joined 12/11
21 Dec 2011

Great instructions. Thanks a bunch for this. Helped me out tremendously.

murphrp 1 comment Joined 05/09
21 Dec 2011

@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.

changliu 1 comment Joined 04/12
20 Apr 2012

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.

NetFx 4 comments Joined 09/06
24 Apr 2012

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.

Rbar 7 comments Joined 10/04
17 Aug 2012

I hope this blog is still active. I have plenty of questions to ask.

jepatte 2 comments Joined 10/12
26 Oct 2012

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)

jepatte 2 comments Joined 10/12
30 Oct 2012

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.

dirty 1 comment Joined 03/10
09 May 2014

BIG THANKS!!!!

vivek_vinayan 2 comments Joined 08/14
13 Aug 2014

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

vivek_vinayan 2 comments Joined 08/14
14 Aug 2014

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

david.pillow 1 comment Joined 07/15
01 Jul 2015

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

NetFx 4 comments Joined 09/06
01 Jul 2015

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.

Kiran.g 1 comment Joined 02/16
02 Feb 2016

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

comtest 1 comment Joined 02/16
15 Feb 2016

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

DMaheshwari80 1 comment Joined 08/15
11 May 2016

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)

You must sign in to leave a comment.