Linked Server to Teradata - SQL2005

Wed Nov 30, 2005 by Phil Streiff

Maybe this is known among those who have to connect to Teradata stores from SQL Server but I needed to accomplish this from SQL Server 2005 and didn't find any published resources on how to do it. With a little experimentation, this is what I came up with:

1) Fetch the Teradata OLEDB driver from teradata.com and install it on the SQL Server 2005 system.

2) In SQL Management Studio (AKA Enterprise Manager) create a new Linked Server with the following configuration:
-Linked Server: choose any name (ex: ENCORE)
-Provider: OLE DB Provider for Teradata (select from dropdown list)
-Product Name: Database name (ex: databasename)
-Data Source: Server name (ex: server1.corp.domain.com)
-Provider String: (leave blank)

3) Type in your Teradata username & password in the appropriate textboxes on the Security tab:

4) You can build a SQL query like this:

SELECT
field1
, field2

FROM

ENCORE..B8_PROD_VIEW.table_name
--(notice the 4 part naming string with second element skipped)

But using the OPENQUERY method will be much faster:

SELECT *
FROM OPENQUERY(ENCORE, 'SELECT field1 FROM B8_PROD_VIEW.table_name'

Viola! you have a dataset from the remote Teradata store returned inside of SQL Server and ready to perform distributed queries like joining to local tables.

Hope this helps!

-pj

Legacy Comments


Jdang 2006-01-17	re: Setting up Teradata Linked Server Hi, Are there any way for me to hide the first 3 parts (maybe in connection string) since I just want to write a short sql in my code like: Select field1, field2 ..... from tableName. If you know how to get away with " 4 part naming string", please email the solution to me at jdang98@yahoo.com Regards, JDang

Hao Zhang 2006-01-23	re: Setting up Teradata Linked Server Hi, PJ: Thank you very much for the information. When I created a linked server fro Oracle database. I was confuse about the Product Name, and couldn't get connection. After I read you article, it is very clear to explain the Product Name. I got my linked server work. HZ

Sally Ann Hubbard 2006-02-05	re: Setting up Teradata Linked Server Thank you for your info, however, I have had difficulty applying it on my test workstation. By any chance is your Teradata and SQL Server using integrated security? By databasename, do you mean the actual databasename or the Teradata TDP name? I have tried all different combinations without success. I can use the OLEDB for ODBC and get the servers linked. And I have successfully connected the Teradata via .NET provider and OLEDB within the SSIS suite. I get a 7303 error when I try to use the OLEDB within the Linked server environment. Thanks for any additional help you can provide.

Phil Streiff 2006-02-28	re: Setting up Teradata Linked Server Sally: Do you have the Teradata OLEDB driver installed on the SQLServer machine and selecting the "OLE DB Provider for Teradata" option from the dropdown list of providers? I did not use integrated security to establish my connection. I clicked on the Security tab and entered the Teradata username/password under the login "be made using this security context" radiio button section. I'm not sure what you mean by database name or TDP name but if you were connecting via the Teradata client, Queryman, it is the name of the database that appears in the left-pane next to the database symbol (cylinder). You may need to also click on the "Provider Options" button and make sure "Allow in-process" and "Index as Access path" checkboxes are checked. Let me know if you have further questions. Phil

Connecting to a MUMPS (M21) Serv 2006-03-01	re: Teradata Linked Server from SQL Server 2005 Hi PJ, Have you any experience linking to a MUMPS (M21) database? I am able to get the link working and can perform a select on the tables I am interested in but the query will only return two rows in my dataset. Is this a SQL setting for ODBC? I can connect to the same M21 database using the same ODBC driver via MS Access 2000 and return all of thre rows no problem. Any help much appreciated. Mark

dinesh 2006-03-06	re: Teradata Linked Server from SQL Server 2005 I want connect one Database fron another Database in sql server.How is Possible

Sally Ann Hubbard 2006-03-11	re: Teradata Linked Server from SQL Server 2005 Thanks, Phil. You must be using an older version. My ODBC System DSN has properties for the server information (we use DNS) and when using OLEDB modules from NCR there is a TDP name. In VB I specify the TDP name and the default databasename (Hiccups if the word catalog is used). I will try modifying the provider options and let you know.

Sally Ann Hubbard 2006-03-11	re: Teradata Linked Server from SQL Server 2005 That did it, Phil, allowing the inprocess and index paths. Thanks a lot!

Todd 2006-04-12	re: Teradata Linked Server from SQL Server 2005 Where is the "Provider Options" button in the SQL Server 2005 management studio ? I'm using the express edititon (CTP) and can't find these options. Its described as a 'button' here, so wondering where it is ?

Bill 2006-08-23	re: Teradata Linked Server from SQL Server 2005 the info for teradata oledb driver on teradata.com does not mention compatability with win2003??

Jonny Magee 2007-01-18	re: Teradata Linked Server from SQL Server 2005 I want to move data from an M21 database to SQL Server... Do I need to download the drivers above or can I link direct? Could someone please outline the steps for me? Many thanks, Jonny (sorry if its exactly the same as above)

Louis 2007-01-19	re: Teradata Linked Server from SQL Server 2005 I finally figured out the Linked server setup, and until now have not had any issues. However, since the OPENQUERY does not allow for a dynamic SQL statement, I have had to resort to the 4 part naming. The issue I have is with using date criteria. The following is inside a Store Procedure in SQL Server. @firstdayofmonth is of type DATETIME. But I have also tried with CHAR and VARCHAR. @firstdayofmonth = '1/1/2007' SELECT week, first_day_of_week FROM ENCORE..Schedule.Weeks WHERE first_day_of_week <= @firstdayofmonth AND last_day_of_week >= @firstdayofmonth I get the following error. Invalid date supplied for Weeks.first_day_of_week Any idea of how to resolve this. When using the OPENQUERY method, I can either use DATE '2007-01-01' or '01/01/2007' (date, format 'mm/dd/yyyy'). Thanks for any help.

Josh 2007-01-23	where do I get ole db provider for teradata? Please help! Can't find drivers for Teradata OLEDB or ODBC to use with .net 2005. Can someone point me in the right direction? Thanks in advance.

Phil Streiff 2007-03-01	re: Teradata Linked Server from SQL Server 2005 Josh: You can download the Teradata drivers here: http://www.teradata.com/t/page/130536 -Phil

Kiran 2007-07-19	re: Linked Server to Teradata - SQL2005 Hi Phil, I created a linked server X to Teradata using the above said method(using the oledb provider for Teradata) for a Sql Server 2005 DB. But, when I try to run a query from the Management Studio using the openquery statement, i get an error - Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "TDOLEDB.1" for linked server "X" reported an error. Access denied. Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "TDOLEDB.1" for linked server "X". Any advice would be greatly appreciated!

Bill 2007-08-06	re: Linked Server to Teradata - SQL2005 I get the same error. The results tab shows the actual column names from the table, when using "select *" but it does not return any data. Any luck yet?

Andy 2007-09-13	re: Linked Server to Teradata - SQL2005 I am trying to set up the Linked 2005 Server to Teradata, and have got most of the way there. My problem is that using both methods (OPENQUERY and SELECT with 4-part naming string) seems to submit only the SELECT * part to Teradata with the rest of the SQL waiting until the data is all pulled back to SQL Server. This is a problem when working with big tables! It puts a huge strain on the network, and also runs me out of temp space in Teradata. Is there a way to get ALL the SQL passed to Teradata? I am looking to create a Report Model for Report Builder or Pro-Clarity. I have a dummy SQL Server 2005 database set up with views of the Teradata tables through the Linked Server. Any help would be greatly appreciated!

Amit Tiwari 2008-01-07	re: Linked Server to Teradata - SQL2005 )<br>Could not find server 'PACS' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.<

Vince 2008-01-30	re: Linked Server to Teradata - SQL2005 Hello, I also get the following error Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "TDOLEDB.1" for linked server "TERAANSI" reported an error. Access denied. Msg 7301, Level 16, State 2, Line 1 Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "TDOLEDB.1" for linked server "TERAANSI". I installed the driver 01.05.00.02 and it works with QTADO any hints ? thanks

kiwipeet 2008-08-04	re: Linked Server to Teradata - SQL2005 I've just installed the Teradata SQL assistant, and have MSSQL 2005 running on XP. I want to connect to Teradata as a linked server. I will be exporting/copying record sets around 5-6million rows. I assumed I could use the Teradata ODBC driver, but people here are discussing OLEDB. Is there any significant advantage in using one method over the other? (I don't know what the upper limit is for ODBC.) Any informaiton or suggestions would be greatly appreciated. Regards, Peter

BG 2008-11-20	re: Linked Server to Teradata - SQL2005 Does anybody have experience connecting 64Bit (!) SQL 2005 SP2 instance to Teradata (V2R6.1), as there is no 64 Bit OLEDB for Teradata (and maybe will never be) and I dont't get Teradata ODBC x8664 13.0.0.0 with MSDASQL 2.82.4250.0 64Bit (OLEDB-Provider for ODBC) to work (not sure which parameters to set using "exec sp_addlinkedserver" and what values these parameters should represent)? Best regards, B.G.

Lizardo 2009-01-15	re: Linked Server to Teradata - SQL2005 saludos

CS 2009-07-22	re: Linked Server to Teradata - SQL2005 Thanks for saving me a huge headache! I was able to get this to work on SQL 2005 X64 SP2 to I don't know the instance of Teradata after by ODBC (13.00.00.02 for X64) connection stopped working. I used the 13.00.00.00 OLEDB 32 bit and it worked. I just had to figure out the connection information.

Chris 2010-03-25	re: Linked Server to Teradata - SQL2005 Where does one obtain the OLE DB PROVIDER for TERADATA and how is it installed so that it shows up as one of the 'Providers'?

Bonus dei casinò virtuali 2010-04-10	re: Linked Server to Teradata - SQL2005 Having SQL Delta means no longer do you miss that last minute change added to your development database even if your production database is not available, all you need to do is compare an offline snaphot of the live database to your development database and then generate the change script...

Maulik 2010-04-16	re: Linked Server to Teradata - SQL2005 I have the same question as Chris has. I have downloded TERADATA OLE DB drivers from TERADATA website. And installed also successfully. I can access that drivers from my SSIS OLE DB Source, that means it does installed correctly. But I cannot find this OLEDB Driver for TERADATA in my linked server wizard of SSMS. My Environment is SQL Server 2008 x64 and Windows Vista Business x64. Please someone help me!!!!! Thanks

alle Casinos online 2010-09-27	re: Linked Server to Teradata - SQL2005 When I created a linked server fro Oracle database. I was confuse about the Product Name, and couldn't get connection. After I read you article, it is very clear to explain the Product Name. I got my linked server work. Thanks for provide a nice information with pictorial.Pictorial through easy understand.

Sridhar 2011-02-16	re: Linked Server to Teradata - SQL2005 Thanks for the information. It is quite helpful.

Alan 2011-11-18	re: Linked Server to Teradata - SQL2005 Hi PJ, Have you any examples\experience of linking to a Teradata server that has LDAP authentication. We have our linked server MS SQl 2005 SP3 working fine without ldap. The teradata group is about to deploy LDAP authentication and so far in testing we are unable to get the linked server to work.