Connection Server vrom SQL-Server to Teradata

[psychodad71]

Hi,

I'd like to set up a Connection-Server with SQL_Server 2000. Therefore I used the Enterprise Manager to connect via ODBC (tdata32.dll Version 2.8.2) over OLE (Security) to teradata. The connection seems to work fine. I see all views and tables on the teradata database. But when i tried to make a view from the sql-server database the provider returned that it will not be supportet. Does it work with the wincli32.dll ? But how ? There is no driver in my odbc-connection-manager.

Many thanks, and sorry for my English...

Uli

 

[dnoeth]

Could you please post the CREATE VIEW and the error message?

Dieter

 

[psychodad71]

Simple view:


create view as

select *
from connectionserver.database.schema.table

SQL-Server message:

ODBC-Fehler:
Die Operation konnte nicht ausgeführt werden, da der OLE DB-Provider MSDASQL keine verteilte Transaktion beginnen konnte

ODBC-Error: OLE-DB Provider could not handle shared transactions...)

Tdata.dll:

Option out of range

Uli

 

[dnoeth]

A common problem is caused by the qualified tablename, but this will result in a different error message.
So i can't help you, i can easily create a view without errors, e.g.

create view test as
select databasename, tablename from teradata..dbc.tables

Dieter

 

[psychodad71]

View:

create view test as

select l4_kopf.*
from infonet.view_da2.view_da2.l4_kopf

Error message:

Server: Nachr.-Nr. 7399, Schweregrad 16, Status 1, Prozedur test, Zeile 3
Der OLE DB-Provider 'MSDASQL' meldete einen Fehler. Der Provider unterstützt die erforderliche Methode nicht.
[OLE/DB provider returned message: Das Verfahren wird von diesem Provider nicht unterstützt.]
[OLE/DB provider returned message: [NCR][TDATA DLL] Table qualifiers not supported by data source]
OLE DB-Fehlertrace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80040e53: Der Provider unterstützt die erforderliche Methode nicht.].


Seems to be a driver problem, isn't it ? Select * from table via odbc is no problem...

Uli

 

[dnoeth]

As i said, it's the qualified name, you specified too much ;-)

try
select l4_kopf.*
from infonet..view_da2.l4_kopf

Dieter

 

[psychodad71]

Yes, thank you, it seems to work.

But there's another problem. The performance is not the best, beacause of 1.9 Mio Rows per table. I tried with a stored procedure, but it's not even better ! Any ideas ?

Uli

 

[dnoeth]

What are you trying to achieve?
Do you really retrieve all rows with every select?
Please provide more details, especially your selects.

Dieter

 

[psychodad71]

I'd like to select 10 rows with a simple query. The source table contains 1,9 Mio rows. When I execute the query over the connection-server the result takes about 2 Minutes. Compared to the result over odbc (< 2 Sec) the performance is not the best. Is there any possibility to tune the connection-server connection ?

my stored procedure:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE [F_Infonet]
(@nr varchar)

AS select *
from infonet..d2.VP_L
where original_id = @nr
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Uli

 

[dnoeth]

Did you look at the plan?
This will result in a remote query executed on Teradata *without* WHERE-condition, so 1.9 Mio rows will be send across the network and then a filter &quot;original_id = @nr&quot; is applied on SQL Server.

You should try OPENQUERY/OPENROWSET for pass-through queries.

Dieter