October 15, 2009 at 6:51 am
I am having all kinds of issues trying to setup a linked server to a teradata database. I have installed the latest drivers directly from teradata download center. I have tried setup on a 32bit machine and my production box is 64bit. I am able to connect via odbc and ssis oledb provider for teradata with no problems. So I know the connection info is good but the linked server gives me the following error message: " Cannot create an instanace of OLE DB provider "TDOLED.1" for linked server. I also made sure I installed the teradata drivers in the correct order. It seems that you need to install them in the following order libraries, GSS and the OLEDB/ODBC. I even tried testing the connection with the .udl file on both machines. I would be fine with the SSIS solution but I am running into all kinds of encryption issues that seem like a big waste of time.
Can anyone provide some direction on setting up a linked server for teradata on winserver2003/sql2005/64bit box?
Sample article that I have been using for setup:
http://weblogs.sqlteam.com/phils/archive/2005/11/30/8466.aspx
Drives I am using:
March 16, 2010 at 11:38 pm
I too am having issues trying to connect a 64 bit (x64) SQL Server 2005 to Teradata.
Has anybody had any luck?
I did get the 64 bit OLE DB for ODBC provider downloaded and installed. I also have the Teradata 64 bit ODBC driver installed (I think!).
I've done a fair amount of searching to no avail. Anybody know how to do this?
March 17, 2010 at 6:59 am
Yes, I did come up with a solution not perfect but it works. The bottom line comes down to the limitations of Teradata and 64bit. Aparently they make 64bit OLEDB or .Net drivers that claim to work. I was not able to get them to work so I gave up on the OLDEDB and went to ODBC 64bit. I was able to get the ODBC drivers to work but had issues with stored creds in SSIS and calling it from a SQL Job. So I wrote a C#.Net console app to pass a query and return a datatable that loads into sql. This sounds like a little overkill but it works and I don't have to deal with all of the other issues that I faced.
I did speak with Andy Lenord at Sql Sat 21 and he said that you could do the samething with a script task in SSIS. It's hard to get good documentation on the syntax in SSIS so I just wrote a console app instead.
All the talk about embedded Teradata drivers in 2008 is b.s. I am on Sql 2008 Enterprise and was not able to get the default Teradata drivers to work on 64bit.
The ideal solution of a linked server is not something I can get working on a 64bit box.
Here is the C#.Net code I used to create my console app:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Net.Mail;
namespace td_console
{
class Program
{
[STAThread]
static int Main(string[] args)
{
int result;
result = 0;
string myInput;
// Debuging
//myInput = "YellowPages";
//DateTime startDate = Convert.ToDateTime("2009-11-01");
//DateTime endDate = Convert.ToDateTime("2009-11-30");
//string startDate = "2009-11-02";
//string endDate = "2009-11-03";
myInput = args[0];
string startDate = args[1];
string endDate = args[2];
if (myInput != null && startDate != null && endDate != null)
{
Console.WriteLine("Processing {0}", myInput);
switch (myInput)
{
case "NPSImport":
NPSImport(startDate, endDate, out result);
Console.WriteLine("Processing of NPS Declined Estimates Teradata import completed.");
break;
case "YellowPages":
YellowPages(startDate, endDate, out result);
Console.WriteLine("Processing of Yellow Pages Teradata import completed.");
break;
}
return result;
}
else
{
Console.WriteLine("You are missing some of the required parameters");
return 0;
}
}
private static int NPSImport(string startDate, string endDate, out int result)
{
result = 0;
DataTable _dt = new DataTable();
try
{
Console.WriteLine("Connecting to Teradata database...");
string strConn = "DSN=Teradata;Database=xxxdatabasenamexxx; Uid=xxx;Pwd=xxx;";
OdbcConnection Conn = new OdbcConnection(strConn);
StringBuilder sSQL = new StringBuilder();
sSQL.AppendFormat(" select l.svc_cal_dt as ReportDate, l.rgn_no as RegionID, l.ctr_svc_un_no as CenterServiceUnitID, ");
sSQL.AppendFormat(" l.un_no as ServiceUnitID, l.nps_sp_cd as NPSGroupName, l.tps_idy_cd as NPSID, l.tps_sp_cd as TPSGroupName, ");
sSQL.AppendFormat(" l.so_no as ServiceID, svc_cus_id_no as SearsCustomerID, cn.cn_name_pref as CustomerPrefix, ");
sSQL.AppendFormat(" cn.cn_name_last as CustomerLName, cn.cn_name_1st as CustomerFName, cn.cn_name_2nd as CustomerMName , ");
sSQL.AppendFormat(" cn.cn_name_suff as CustomerSuffix, cn.cn_house_rte_no as CustomerHouseNumber, cn.cn_street_pfx as CustomerStreetPrefix, ");
sSQL.AppendFormat(" cn.cn_street_nme as CustomerStreetName, cn.cn_street_sfx as CustomerStreetSuffix, cn.cn_street_sfx_md as CustomerStreetSuffix2, ");
sSQL.AppendFormat(" cn.cn_box_no as CustomerBoxNumber, cn.cn_addr_line_2 as CustomerStreetAddress2, cn.cn_town as CustomerCity, cn.cn_st_prov as CustomerState, ");
sSQL.AppendFormat(" rtrim(ltrim(cn.cn_zip_pc)) as CustomerZip, cn.cn_tel_no as CustomerPhone ");
sSQL.AppendFormat(" from hs_perm_tbls.dashboard_attempts_v001 l ");
sSQL.AppendFormat(" inner join hs_dw_nadr_views.npjxtcn cn on l.svc_cus_id_no = cn.cn_cust_key ");
sSQL.AppendFormat(" where l.tps_idy_cd = 'HV' and l.svc_cal_cd in ('30','38') ");
sSQL.AppendFormat(" and l.svc_cal_dt between DATE'{0}' and DATE'{1}' ", startDate, endDate);
sSQL.AppendFormat(" order by l.svc_cal_dt desc; ");
OdbcCommand cmd = new OdbcCommand(sSQL.ToString(), Conn);
Conn.Open();
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
da.Fill(_dt);
}
catch (Exception ex)
{
Console.WriteLine("Error on connection to teradata {0}", ex);
//TextFile(ex.ToString());
SendMail(ex.ToString());
result = 1;
}
try
{
using (SqlConnection myConn = new SqlConnection("Server=xxxserverxxx;Database=databasename;User ID=xxxx;Password=xxxx;Trusted_Connection=False;"))
{
foreach (DataRow dr in _dt.Rows)
{
StringBuilder query = new StringBuilder();
query.Append(" Insert into dbo.StageNPSImport (ReportDate ,RegionID ,CenterServiceUnitID, ");
query.Append(" ServiceUnitID ,NPSGroupName ,NPSID ,TPSGroupName, ServiceID ,SearsCustomerID, ");
query.Append(" CustomerPrefix, CustomerLName ,CustomerFName ,CustomerMName ,CustomerSuffix, ");
query.Append(" CustomerHouseNumber ,CustomerStreetPrefix ,CustomerStreetName ,CustomerStreetSuffix, ");
query.Append(" CustomerStreetSuffix2 ,CustomerBoxNumber ,CustomerStreetAddress2 ,CustomerCity ,CustomerState, ");
query.Append(" CustomerZip ,CustomerPhone, AuditKey) ");
query.Append(" Values (@ReportDate ,@RegionID ,@CenterServiceUnitID, ");
query.Append(" @ServiceUnitID ,@NPSGroupName ,@NPSID ,@TPSGroupName, @ServiceID, @SearsCustomerID, ");
query.Append(" @CustomerPrefix, @CustomerLName ,@CustomerFName ,@CustomerMName ,@CustomerSuffix, ");
query.Append(" @CustomerHouseNumber ,@CustomerStreetPrefix ,@CustomerStreetName ,@CustomerStreetSuffix, ");
query.Append(" @CustomerStreetSuffix2 ,@CustomerBoxNumber ,@CustomerStreetAddress2 ,@CustomerCity ,@CustomerState, ");
query.Append(" @CustomerZip ,@CustomerPhone, @AuditKey) ");
SqlCommand myComm = new SqlCommand(query.ToString(), myConn);
myComm.Parameters.Add("@ReportDate", SqlDbType.DateTime).Value = dr["ReportDate"];
myComm.Parameters.Add("@RegionID", SqlDbType.VarChar).Value = dr["RegionID"];
myComm.Parameters.Add("@CenterServiceUnitID", SqlDbType.VarChar).Value = dr["CenterServiceUnitID"];
myComm.Parameters.Add("@ServiceUnitID", SqlDbType.VarChar).Value = dr["ServiceUnitID"];
myComm.Parameters.Add("@NPSGroupName", SqlDbType.VarChar).Value = dr["NPSGroupName"];
myComm.Parameters.Add("@NPSID", SqlDbType.VarChar).Value = dr["NPSID"];
myComm.Parameters.Add("@TPSGroupName", SqlDbType.VarChar).Value = dr["TPSGroupName"];
myComm.Parameters.Add("@ServiceID", SqlDbType.VarChar).Value = dr["ServiceID"];
myComm.Parameters.Add("@SearsCustomerID", SqlDbType.Int).Value = dr["SearsCustomerID"];
myComm.Parameters.Add("@CustomerPrefix", SqlDbType.VarChar).Value = dr["CustomerPrefix"];
myComm.Parameters.Add("@CustomerLName", SqlDbType.VarChar).Value = dr["CustomerLName"];
myComm.Parameters.Add("@CustomerFName", SqlDbType.VarChar).Value = dr["CustomerFName"];
myComm.Parameters.Add("@CustomerMName", SqlDbType.VarChar).Value = dr["CustomerMName"];
myComm.Parameters.Add("@CustomerSuffix", SqlDbType.VarChar).Value = dr["CustomerSuffix"];
myComm.Parameters.Add("@CustomerHouseNumber", SqlDbType.VarChar).Value = dr["CustomerHouseNumber"];
myComm.Parameters.Add("@CustomerStreetPrefix", SqlDbType.VarChar).Value = dr["CustomerStreetPrefix"];
myComm.Parameters.Add("@CustomerStreetName", SqlDbType.VarChar).Value = dr["CustomerStreetName"];
myComm.Parameters.Add("@CustomerStreetSuffix", SqlDbType.VarChar).Value = dr["CustomerStreetSuffix"];
myComm.Parameters.Add("@CustomerStreetSuffix2", SqlDbType.VarChar).Value = dr["CustomerStreetSuffix2"];
myComm.Parameters.Add("@CustomerBoxNumber", SqlDbType.VarChar).Value = dr["CustomerBoxNumber"];
myComm.Parameters.Add("@CustomerStreetAddress2", SqlDbType.VarChar).Value = dr["CustomerStreetAddress2"];
myComm.Parameters.Add("@CustomerCity", SqlDbType.VarChar).Value = dr["CustomerCity"];
myComm.Parameters.Add("@CustomerState", SqlDbType.VarChar).Value = dr["CustomerState"];
myComm.Parameters.Add("@CustomerZip", SqlDbType.VarChar).Value = dr["CustomerZip"];
myComm.Parameters.Add("@CustomerPhone", SqlDbType.VarChar).Value = dr["CustomerPhone"];
myComm.Parameters.Add("@AuditKey", SqlDbType.VarChar).Value = DBNull.Value;
myConn.Open();
myComm.ExecuteNonQuery();
myConn.Close();
}
Console.WriteLine("Writing records to destination....");
}
result = 0;
}
catch (Exception ex)
{
Console.WriteLine("Error on insert {0}", ex);
//TextFile(ex.ToString());
SendMail(ex.ToString());
return 1;
//System.Threading.Thread.Sleep(10000);
}
return result;
}
private static int YellowPages(string startDate, string endDate, out int result)
{
result = 0;
DataTable _dt = new DataTable();
try
{
Console.WriteLine("Connecting to Teradata database...");
string strConn = "DSN=Teradata;Database=xxxdatabasenamexxx; Uid=xxxx;Pwd=xxxx; LoginTimeout=500";
OdbcConnection Conn = new OdbcConnection(strConn);
StringBuilder sSQL = new StringBuilder();
sSQL.AppendFormat(" select dial_num as DialNumber, cnct_dt as CallDate, cnct_tm as CallDateTime, elaps_tm as CallElapseTime, orig_num as OrigNumber, ");
sSQL.AppendFormat(" B.cty_nm as City, B.st as State, B.zip_cd1 as ZipCode ");
sSQL.AppendFormat(" FROM(SELECTA2.* ");
sSQL.AppendFormat(" FROMhs_perm_tbls.PRC_YPSHIP200812_TFN_SHIPA1, ");
sSQL.AppendFormat(" hs_perm_tbls.smm_att_dataA2 ");
sSQL.AppendFormat(" WHERE A1.TFN=A2.Dial_Num ");
sSQL.AppendFormat(" and cnct_dt between DATE'{0}' and DATE'{1}' ) A", startDate, endDate);
sSQL.AppendFormat(" LEFT OUTER JOIN");
sSQL.AppendFormat(" hs_perm_tbls.Fone_data B");
sSQL.AppendFormat(" ON ( SUBSTR(A.orig_num,1,3) = B.area_cd");
sSQL.AppendFormat(" AND SUBSTR(A.orig_num,4,3) = B.prefix) ");
OdbcCommand cmd = new OdbcCommand(sSQL.ToString(), Conn);
Conn.Open();
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
da.Fill(_dt);
}
catch (Exception ex)
{
Console.WriteLine("Error on connection to teradata {0}", ex);
//TextFile(ex.ToString());
SendMail(ex.ToString());
result = 1;
}
try
{
using (SqlConnection myConn = new SqlConnection("Server=server;Database=databasename;User ID=xxxx;Password=xxxx;Trusted_Connection=False;"))
{
foreach (DataRow dr in _dt.Rows)
{
StringBuilder query = new StringBuilder();
query.Append(" Insert into dbo.StageYellowpages (DialNumber ,CallDate , CallDateTime, CallElapseTime, ");
query.Append(" OrigNumber ,City ,State ,ZipCode ) ");
query.Append(" Values (@DialNumber ,@CallDate ,@CallDateTime, @CallElapseTime, ");
query.Append(" @OrigNumber ,@City ,@State ,@ZipCode) ");
SqlCommand myComm = new SqlCommand(query.ToString(), myConn);
myComm.Parameters.Add("@DialNumber", SqlDbType.VarChar).Value = dr["DialNumber"];
myComm.Parameters.Add("@CallDate", SqlDbType.DateTime).Value = dr["CallDate"];
myComm.Parameters.Add("@CallDateTime", SqlDbType.Time).Value = dr["CallDateTime"];
myComm.Parameters.Add("@CallElapseTime", SqlDbType.VarChar).Value = dr["CallElapseTime"];
myComm.Parameters.Add("@OrigNumber", SqlDbType.VarChar).Value = dr["OrigNumber"];
myComm.Parameters.Add("@City", SqlDbType.VarChar).Value = dr["City"];
myComm.Parameters.Add("@State", SqlDbType.VarChar).Value = dr["State"];
myComm.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = dr["ZipCode"];
myConn.Open();
myComm.ExecuteNonQuery();
myConn.Close();
}
Console.WriteLine("Writing records to destination....");
}
result = 0;
}
catch (Exception ex)
{
Console.WriteLine("Error on insert {0}", ex);
//TextFile(ex.ToString());
SendMail(ex.ToString());
return 1;
//System.Threading.Thread.Sleep(10000);
}
return result;
}
private static void TextFile(string e)
{
try
{
TextWriter tw = new StreamWriter("td_console_exceptions.txt");
tw.WriteLine(e);
tw.Close();
}
catch (Exception ex)
{
Console.WriteLine("Error creating text file {0}", ex);
}
}
private static void SendMail(string e)
{
try
{
MailMessage myMail = new MailMessage();
myMail.To.Add("bgeige@email.com");
myMail.From = new MailAddress("reportsvc@email.com");
myMail.Subject = "Teradata Console App Failure";
myMail.Body = e;
SmtpClient smtp = new SmtpClient("smtp.email.com");
smtp.Send(myMail);
}
catch (SmtpFailedRecipientException ex)
{
Console.WriteLine("Error on email {0}", ex);
}
}
}
}
April 21, 2012 at 6:59 pm
Hi,
I am not too sure if my posting is old or not. Its been a while since I had the pleasure of working with SQL and I have been setting up Teradata Sandboxes, ODBC, OLEDB, .NET, JDBC for some very specific ETL load comparrison scenarios. From what I know of Teradata - you definitely need to install the x32 as well as x64 driver. Furthermore, it really does make a difference which one you select when configuring your linked server - I found out a few minutes ago (after going crazy for a while) that if you configure your ODBC dsn with the x32 ODBC administrator and you have x64 SQL it will provide you with some challenges because the architectures are fundamentally different.
Certainly having 2 ODBC administrators can cause you to see problems which dont really exist.
Hope this helps
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply