Anyone know how to create a DSNless connection to Teradata from VB?

[bgruver]

I'd like to try to connect without using a pre-existing DSN. When I try to open a connection to Teradata from within VB I get the following error: "Provider cannot be found."

Any suggestions?

Here's my code:
oconn.Open "Provider=Teradata;" & _
"DBCName=55.55.55.123;" & _
"Database=my_db;" & _
"Uid=user1;" & _
"Pwd=cloudy"

 

[bgruver]

I figured it out.

 

[beginnerkid]

Hi bgruver,
I am facing the same problem, could you please let me know what did you find the solution. Alos if you could let me know that unicode (japanese) character can be stored using ODBC into the Teradata. or need to convert to UTF8 then insert them into the database. (I have already defined the columns as UNICODE characterset at backend.

Thanks in advance

 

[Pinan]

I dont have the exact code in front of me, but just treat Teradata as any other ODBC source. What language are you trying to connect with?

Good luck!

 

[AB180801]

With the Teradata ODBC driver, you employ the "Kagera' MSDASQL Generic OLE DB Provider from Microsoft (comes standard with MDAC) to perform the translation from ADO to ODBC.

I use a DSNless connection

set up a connection string

mobjADOConnection.ConnectionString="PROVIDER=MSDASQL;" & _

"DRIVER={Teradata};" & _

"DBCName=" & mcstrGDWservername & ";" & _

"DefaultDatabase=" & mcstrGDWDatabasename & ";" & _

"UID=" & mcstrGDWLoginID & ";" & _

"PWD=" & mcstrGDWPassword & ";"

eg.

mobjADOConnection.ConnectionString="PROVIDER=MSDASQL;" & _

"DRIVER={Teradata};" & _

"DBCName=dbccop1;" & _

"DefaultDatabase=ProdDB;" & _

"UID=myuserid;" & _

"PWD=mypassword;"

then continue with normal ADO commands (Open the Connection, Execute a Command or Open a Recordset with an SQL string, then process the recordset)

Make sure you have MDAC (Microsoft Data Access Objects) (MDAC2.7 is latest version, but earlier versions from MDAC2.1 work fine) - it is a free download from Microsoft.

I suggest you use a name for DBCName rather than an IP address (via Hosts file etc) to enable the actual address to be changed without

re-coding impact

Regards

 

[warehousemadness]

What if I wanna link to a teradata database from SQL Server using the 'sp_addlinkedserver' Stored Procedure? Somebodys gotta have done that before. Is anyone familiar with this?