SQL Server Discovery

Better, faster, cheaper ...pick two.
posts - 13, comments - 80, trackbacks - 0

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)

 photo 1_LinkSvrGen_2K5_zps52c12685.png

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

 photo 2_LinkSvrSec_2K5_zpsfa18aadc.png

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

Gravatar

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

# re: Linked Server to Teradata - SQL2005

saludos
1/15/2009 7:23 PM | Lizardo
Gravatar

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

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

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

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

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

# re: Linked Server to Teradata - SQL2005

Thanks for the information. It is quite helpful.
2/16/2011 6:54 AM | Sridhar
Gravatar

# 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
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET