Linked Server to Teradata

<< SQL Server 2005: Top 10 New Features | Home | Linked Server to Teradata - SQL2000 >>

Linked Server to Teradata - SQL2005

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

Feedback

# 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

1/17/2006 8:40 AM | Jdang

# 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

1/23/2006 1:11 PM | Hao Zhang

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

2/5/2006 10:55 AM | Sally Ann Hubbard

# 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

2/28/2006 1:50 PM | Phil Streiff

# 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

3/1/2006 12:27 PM | Connecting to a MUMPS (M21) Serv

# re: Teradata Linked Server from SQL Server 2005

I want connect one Database fron another Database in sql server.How is Possible

3/6/2006 5:39 AM | dinesh

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

3/11/2006 11:47 AM | Sally Ann Hubbard

# re: Teradata Linked Server from SQL Server 2005

That did it, Phil, allowing the inprocess and index paths. Thanks a lot!

3/11/2006 12:08 PM | Sally Ann Hubbard

# 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 ?

4/12/2006 10:29 AM | Todd

# re: Teradata Linked Server from SQL Server 2005

the info for teradata oledb driver on teradata.com does not mention compatability with win2003??

8/23/2006 1:22 PM | Bill

# 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)

1/18/2007 5:56 AM | Jonny Magee

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

1/19/2007 5:31 PM | Louis

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

1/23/2007 4:21 PM | Josh

# re: Teradata Linked Server from SQL Server 2005

Josh:

You can download the Teradata drivers here:
http://www.teradata.com/t/page/130536

-Phil

3/1/2007 7:38 AM | Phil Streiff

# 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!

7/19/2007 2:02 PM | Kiran

# 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?

8/6/2007 3:21 PM | Bill

# 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!

9/13/2007 5:18 PM | Andy

# re: Linked Server to Teradata - SQL2005

)<br>Could not find server 'PACS' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.<

1/7/2008 3:59 AM | Amit Tiwari

# 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

1/30/2008 3:54 PM | Vince

# 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

8/4/2008 6:51 PM | kiwipeet

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

11/20/2008 5:52 AM | BG

# re: Linked Server to Teradata - SQL2005

saludos

1/15/2009 7:23 PM | Lizardo

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

7/22/2009 3:53 PM | CS

# 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'?

3/25/2010 2:53 PM | Chris

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

4/10/2010 7:06 AM | Bonus dei casinò virtuali

# 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

4/16/2010 10:23 AM | Maulik

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

9/27/2010 5:12 AM | alle Casinos online

# re: Linked Server to Teradata - SQL2005

Thanks for the information. It is quite helpful.

2/16/2011 6:54 AM | Sridhar

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

11/18/2011 7:48 PM | Alan

Post Comment

Title
Name
Email
Url
Comment
Remember Me?
Enter the code shown above:

SQL Server Discovery

My Links

News

Archives

Blogs

Integration Services

SQL CLR

Tools