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.
>
>

Open intial catalog?

I have a connection string that has the clause "initial catalog=XXXXXX" in
it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
opened and the login failed. Any ideas?
Thank you.
KeivnThis is the exact error message:
Cannot open database \"XXXXXXX\" requested by the login. The login
failed.\r\nLogin failed for user 'developer'
"Kevin Burton" wrote:
> I have a connection string that has the clause "initial catalog=XXXXXX" in
> it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
> opened and the login failed. Any ideas?
> Thank you.
> Keivn
>|||Looks like the user 'developer' does not have access to this database. Have
you checked that?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Kevin Burton" wrote:
> This is the exact error message:
> Cannot open database \"XXXXXXX\" requested by the login. The login
> failed.\r\nLogin failed for user 'developer'
> "Kevin Burton" wrote:
> > I have a connection string that has the clause "initial catalog=XXXXXX" in
> > it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
> > opened and the login failed. Any ideas?
> >
> > Thank you.
> >
> > Keivn
> >
> >

Open intial catalog?

I have a connection string that has the clause "initial catalog=XXXXXX" in
it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
opened and the login failed. Any ideas?
Thank you.
KeivnThis is the exact error message:
Cannot open database \"XXXXXXX\" requested by the login. The login
failed.\r\nLogin failed for user 'developer'
"Kevin Burton" wrote:

> I have a connection string that has the clause "initial catalog=XXXXXX" in
> it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
> opened and the login failed. Any ideas?
> Thank you.
> Keivn
>|||Looks like the user 'developer' does not have access to this database. Have
you checked that?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Kevin Burton" wrote:
[vbcol=seagreen]
> This is the exact error message:
> Cannot open database \"XXXXXXX\" requested by the login. The login
> failed.\r\nLogin failed for user 'developer'
> "Kevin Burton" wrote:
>

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.

Only one row returned from linked server while using INTO clause

Hello,
We have encountered the following behaviour / problem:
There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
(LINKED).
On the local server we run the following query:
SELECT * FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
ColumnA is varchar(3), ColumnB is varchar(4)
The query returns approx. 800 rows - that's correct.
However, if we run the same query using the INTO clause:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
the message is
(1 row(s) affected)
and there is only one row in #MyTemp table, what is obviously wrong.
What might be the problem?
Investigating the problem further we've found out that the problematic is
WHERE clause for ColumnB - if we rewrite it as follows:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
the query returns the correct number of rows.
But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
make much sense to me - but it works!
Would anybody be so kind to explain that behaviour?
Best regards,
Andrew
Just a guess, but ensure the collation compatibility settings for the linked
server are correct and the settings for things like Ansi Null, etc match...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eO5X4eQkFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have encountered the following behaviour / problem:
> There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
> (LINKED).
> On the local server we run the following query:
> SELECT * FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> ColumnA is varchar(3), ColumnB is varchar(4)
> The query returns approx. 800 rows - that's correct.
> However, if we run the same query using the INTO clause:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> the message is
> (1 row(s) affected)
> and there is only one row in #MyTemp table, what is obviously wrong.
> What might be the problem?
>
> Investigating the problem further we've found out that the problematic is
> WHERE clause for ColumnB - if we rewrite it as follows:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
> the query returns the correct number of rows.
> But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
> make much sense to me - but it works!
> Would anybody be so kind to explain that behaviour?
> Best regards,
> Andrew
>
>

Only one row returned from linked server while using INTO clause

Hello,
We have encountered the following behaviour / problem:
There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
(LINKED).
On the local server we run the following query:
SELECT * FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
ColumnA is varchar(3), ColumnB is varchar(4)
The query returns approx. 800 rows - that's correct.
However, if we run the same query using the INTO clause:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
the message is
(1 row(s) affected)
and there is only one row in #MyTemp table, what is obviously wrong.
What might be the problem?
Investigating the problem further we've found out that the problematic is
WHERE clause for ColumnB - if we rewrite it as follows:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
the query returns the correct number of rows.
But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
make much sense to me - but it works!
Would anybody be so kind to explain that behaviour?
Best regards,
AndrewJust a guess, but ensure the collation compatibility settings for the linked
server are correct and the settings for things like Ansi Null, etc match...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eO5X4eQkFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have encountered the following behaviour / problem:
> There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
> (LINKED).
> On the local server we run the following query:
> SELECT * FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> ColumnA is varchar(3), ColumnB is varchar(4)
> The query returns approx. 800 rows - that's correct.
> However, if we run the same query using the INTO clause:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> the message is
> (1 row(s) affected)
> and there is only one row in #MyTemp table, what is obviously wrong.
> What might be the problem?
>
> Investigating the problem further we've found out that the problematic is
> WHERE clause for ColumnB - if we rewrite it as follows:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
> the query returns the correct number of rows.
> But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
> make much sense to me - but it works!
> Would anybody be so kind to explain that behaviour?
> Best regards,
> Andrew
>
>

Only one row returned from linked server while using INTO clause

Hello,
We have encountered the following behaviour / problem:
There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
(LINKED).
On the local server we run the following query:
SELECT * FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
ColumnA is varchar(3), ColumnB is varchar(4)
The query returns approx. 800 rows - that's correct.
However, if we run the same query using the INTO clause:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
the message is
(1 row(s) affected)
and there is only one row in #MyTemp table, what is obviously wrong.
What might be the problem?
Investigating the problem further we've found out that the problematic is
WHERE clause for ColumnB - if we rewrite it as follows:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
the query returns the correct number of rows.
But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
make much sense to me - but it works!
Would anybody be so kind to explain that behaviour?
Best regards,
AndrewJust a guess, but ensure the collation compatibility settings for the linked
server are correct and the settings for things like Ansi Null, etc match...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eO5X4eQkFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have encountered the following behaviour / problem:
> There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
> (LINKED).
> On the local server we run the following query:
> SELECT * FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> ColumnA is varchar(3), ColumnB is varchar(4)
> The query returns approx. 800 rows - that's correct.
> However, if we run the same query using the INTO clause:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> the message is
> (1 row(s) affected)
> and there is only one row in #MyTemp table, what is obviously wrong.
> What might be the problem?
>
> Investigating the problem further we've found out that the problematic is
> WHERE clause for ColumnB - if we rewrite it as follows:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
> the query returns the correct number of rows.
> But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
> make much sense to me - but it works!
> Would anybody be so kind to explain that behaviour?
> Best regards,
> Andrew
>
>