SQL Server Discovery

Better, faster, cheaper ...pick two.
posts - 10, comments - 43, trackbacks - 0
<< 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

Print | posted on Wednesday, November 30, 2005 8:56 PM

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

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 6 and 1 and type the answer here:

Powered by:

Copyright © Phil Streiff