OpenQuery with join

Hi

I have a SP which queries a linked server using OpenQuery function.
The remote query includes a join and an IN clause to get the desire result. ( The linked server uses Transoft ODBC driver.)

The qry looks something like this:

select * from OpenQuery(SERVER1,
'select
distinct C.item, C.operation, C.STD_OPERATION,
D.operation_desc , C.operation_desc operation_desc
from TABLE1 C left join
(select distinct operation, operation_desc from TABLE1
where operation in (select distinct STD_OPERATION from TABLE1 where
item = ''9999999'' AND STD_OPERATION <> 0)
AND item = ''STANDARD'') D
on D.operation = C.STD_OPERATION where C.item = ''9999999'' ')

When I run this Qry I get the following error:

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Transoft][TSODBC][usqlsd]')' expected here (DISTINCT)]

Any help would be greatly appreciated.
thxIs the problem that Transoft can't handle the distinct keyword? It is SQL-92 compliant, but maybe the driver can't handle it? Have you tried removing distinct and running the query again?

If this is the problem, you should be able to work around the problem using a group by clause.

Hth.

Paul Barbin

Open View from SQL Server Management Studio

Why the order is lost when I Select Open View'
My query really includes an ORDER clause and the Execute query was fine.Hi
From BOL:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned b
y
the TOP clause. The ORDER BY clause does not guarantee ordered results when
these constructs are queried, unless ORDER BY is also specified in the query
itself.
You don't say if you are using SP1 or not, there are issues with ORDER BY
when in SQL 2000 compatibility mode.
John
"515331Jack3490" wrote:

> Why the order is lost when I Select Open View'
> My query really includes an ORDER clause and the Execute query was fine.
>
>sql

Open View from SQL Server Management Studio

Why the order is lost when I Select Open View'
My query really includes an ORDER clause and the Execute query was fine.Hi
From BOL:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned by
the TOP clause. The ORDER BY clause does not guarantee ordered results when
these constructs are queried, unless ORDER BY is also specified in the query
itself.
You don't say if you are using SP1 or not, there are issues with ORDER BY
when in SQL 2000 compatibility mode.
John
"515331Jack3490" wrote:
> Why the order is lost when I Select Open View'
> My query really includes an ORDER clause and the Execute query was fine.
>
>

Only transform unique rows

Hi,

I have created a SSIS package which includes a Data Flow that will transform my data. The Data Flow Source for this Data Flow is a SQL Server table with approx. 60 mill. rows. Theses rows ought to be unique but isn't quite, so I would like to ensure that only one of each row makes it throug to my Data Flow Destination, another SQL Server table.

What is the most efficient way to ensure this - is there some Data Flow Transformation that can assure this, should I write a SQL statement that deletes all duplicates before the Data Flow or should I take a third approch?

- SuneI can see two ways to do so:

1. OLE DB source -> Sort -> OLE DB dest

Configure Sort transform to "Remove rows with duplicate sort values".

2. OLE DB source -> Aggregate -> OLE DB dest

Configure Aggregate transform to group by all columns.

No.2 might be more efficient. Would you like to try and tell us the results? I'm very interested to know.|||Actually there is a third way and that is to use the T-SQL statement to do the dedupe. This can be much more efficient than either of the 2 mentioned above since the engine is optimized to do this especially if it can use an index. I think you would have to do some benchmarks to determine which is optimal for your particular scenario.

Thanks,
Matt|||I have actually been using a T-SQL statement up until now and I has been working okay. However all of a sudden performance have degraded significantly which made me wonder if there was any alternative ways.

I hope to get around to make some benchmarks during the weekend and promise to post the results.

Thanks for your help so far.

- Sune

Only text pointers are allowed in work tables

Using SS 2000. I have a UNION ALL query that includes a TEXT column.
When I add an ORDER BY clause I get the following error.
"Server: Msg 8626, Level 16, State 1, Line 1
Only text pointers are allowed in work tables, never text, ntext, or
image columns. The query processor produced a query plan that required
a text, ntext, or image column in a work table."
So far, Google has failed to find a work around. Any suggestions?
Paradox can do this query. I cannot believe that SQL Server can't.
..Bill.
Threads active in .programming. Please do not post the same question
multiple times to multiple newsgroups.
"Bill" <no@.no.com> wrote in message
news:%23Tzghb9wFHA.3740@.TK2MSFTNGP14.phx.gbl...
> Using SS 2000. I have a UNION ALL query that includes a TEXT column.
> When I add an ORDER BY clause I get the following error.
> "Server: Msg 8626, Level 16, State 1, Line 1
> Only text pointers are allowed in work tables, never text, ntext, or
> image columns. The query processor produced a query plan that required
> a text, ntext, or image column in a work table."
> So far, Google has failed to find a work around. Any suggestions?
> Paradox can do this query. I cannot believe that SQL Server can't.
> --
> .Bill.

Only text pointers are allowed in work tables

Using SS 2000. I have a UNION ALL query that includes a TEXT column.
When I add an ORDER BY clause I get the following error.
"Server: Msg 8626, Level 16, State 1, Line 1
Only text pointers are allowed in work tables, never text, ntext, or
image columns. The query processor produced a query plan that required
a text, ntext, or image column in a work table."
So far, Google has failed to find a work around. Any suggestions?
Paradox can do this query. I cannot believe that SQL Server can't.
.Bill.Threads active in .programming. Please do not post the same question
multiple times to multiple newsgroups.
"Bill" <no@.no.com> wrote in message
news:%23Tzghb9wFHA.3740@.TK2MSFTNGP14.phx.gbl...
> Using SS 2000. I have a UNION ALL query that includes a TEXT column.
> When I add an ORDER BY clause I get the following error.
> "Server: Msg 8626, Level 16, State 1, Line 1
> Only text pointers are allowed in work tables, never text, ntext, or
> image columns. The query processor produced a query plan that required
> a text, ntext, or image column in a work table."
> So far, Google has failed to find a work around. Any suggestions?
> Paradox can do this query. I cannot believe that SQL Server can't.
> --
> .Bill.

Only text pointers are allowed in work tables

Using SS 2000. I have a UNION ALL query that includes a TEXT column.
When I add an ORDER BY clause I get the following error.
"Server: Msg 8626, Level 16, State 1, Line 1
Only text pointers are allowed in work tables, never text, ntext, or
image columns. The query processor produced a query plan that required
a text, ntext, or image column in a work table."
So far, Google has failed to find a work around. Any suggestions?
Paradox can do this query. I cannot believe that SQL Server can't.:)
--
.Bill.Threads active in .programming. Please do not post the same question
multiple times to multiple newsgroups.
"Bill" <no@.no.com> wrote in message
news:%23Tzghb9wFHA.3740@.TK2MSFTNGP14.phx.gbl...
> Using SS 2000. I have a UNION ALL query that includes a TEXT column.
> When I add an ORDER BY clause I get the following error.
> "Server: Msg 8626, Level 16, State 1, Line 1
> Only text pointers are allowed in work tables, never text, ntext, or
> image columns. The query processor produced a query plan that required
> a text, ntext, or image column in a work table."
> So far, Google has failed to find a work around. Any suggestions?
> Paradox can do this query. I cannot believe that SQL Server can't.:)
> --
> .Bill.