Linked server OPENQUERY, error 'The name <> is not a valid identif

Hi,
I am working on exporting data from a remote MSSQL db using la inked server.
Both SQL server 2000.
So far I have a SELECT against a linked server with a hard coded value in
the WHERE clause and it works fine:
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
I try to put this exact query into a variable and run it:
declare @.tsql varchar(8000)
SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
EXEC @.tsql
I get an error:
The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identifie
r.
The query specified in the error runs just fine on its own (see my first
step).
I searched the web and tech groups for answers but can't find any
information on this error. Please help.
Thank you, A"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:539D21E3-6961-49BD-9892-279744A3A5C9@.microsoft.com...
> EXEC @.tsql
Add some parens:
EXEC (@.tsql)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Can you try using:
...
exec (@.tsql)
AMB
"Alexis" wrote:

> Hi,
> I am working on exporting data from a remote MSSQL db using la inked serve
r.
> Both SQL server 2000.
> So far I have a SELECT against a linked server with a hard coded value in
> the WHERE clause and it works fine:
> SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
> I try to put this exact query into a variable and run it:
> declare @.tsql varchar(8000)
> SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
> EXEC @.tsql
> I get an error:
> The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identif
ier.
> The query specified in the error runs just fine on its own (see my first
> step).
> I searched the web and tech groups for answers but can't find any
> information on this error. Please help.
> Thank you, A
>

Linked server OPENQUERY, error 'The name <> is not a valid ide

Adam,
Thank you for replying to my first message.
Now, can I pass a variable of type integer into that dynamic SELECT
statement? I know it works with character variables, but I really need to
pass in a numeric field. Or do I have to resort to conversions?
declare @.id int
select @.id = 1
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
DB_NAME.dbo.TABLE_NAME WHERE server=' + @.id + ')'
I get Incorrect syntax near '+'.
Thank you.
"Adam Machanic" wrote:

> "Alexis" <Alexis@.discussions.microsoft.com> wrote in message
> news:539D21E3-6961-49BD-9892-279744A3A5C9@.microsoft.com...
> Add some parens:
> EXEC (@.tsql)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>You'll have to build the string dynamically -- you can't pass any kind of
variable into OPENQUERY.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:9B3439B9-7AF2-4B81-A7A3-64BF5A5CAD8A@.microsoft.com...
> Adam,
> Thank you for replying to my first message.
> Now, can I pass a variable of type integer into that dynamic SELECT
> statement? I know it works with character variables, but I really need to
> pass in a numeric field. Or do I have to resort to conversions?
> declare @.id int
> select @.id = 1
> SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
> DB_NAME.dbo.TABLE_NAME WHERE server=' + @.id + ')'
> I get Incorrect syntax near '+'.
> Thank you.
>

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?

> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?
> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>sql

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
Kevin
Anyone? Buehler?

> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>

LINKED SERVER NESTED OPENQUERY SAMPLE FROM TIPS AND TRICKS DOESN'T APPEARS TO FAIL WITH MSO

Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.

I'm generally following theTips and Tricks article.

"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.

e.g.

SELECT * FROM OPENQUERY(DMServer,

'select … FROM Modell PREDICTION JOIN OPENQUERY…')

The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".

Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query

SELECT * FROM OPENQUERY(DMServer,

'SELECT

t.[CardTransactionID],

t.[PostingDate],

[Misuse Abuse Profile].[Even Dollar Purchase],

PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,

PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,

t.[BillingAmount]

FROM

[Misuse Abuse Profile]

PREDICTION JOIN

OPENQUERY([Athena Dev],

''SELECT

[CardTransactionID],

[PostingDate],

[BillingAmount],

[AccountNumber],

[SupplierStateProvinceCode],

[MerchantCategoryCode],

[PurchaseIDFormat],

[TransactionTime],

[TaxAmountIncludedCode],

[Tax2AmountIncludedCode],

[OrderTypeCode],

[MemoPostFlag],

[EvenDollarPurchase]

FROM

[dbo].[vMisuseAbuseProfile]

'') AS t

ON

[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND

[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND

[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND

[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND

[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND

[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND

[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND

[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND

[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND

[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]

')

In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".

It appears the default Analysis Services Data Source wizard selection of SQL Native Client Data Source provider cannot prepare an OPENQUERY if passed from a linked server? I changed the Datasource to Provider=SQLOLEDB.1 and the query now works.

|||this is very odd - we will look into it - thanks for reporting.|||

Hi Bill,

Could you give more details about the setup. Is the SQL server where you defined the linked server DMTest on the same machine with the Analysis Server it refers too? And also is the SQL server refered on the inner openquery datasource [Athena Dev] on a different machine than the machines with AS and SQL server with added linked server?

Thanks,

Dana Cristofor

Linked server from SQL Server 2000 to Sybase 12

:confused:
How do I tap advanced functionality like #temptable and @.var from openquery? There is a linked server from SQL Server 2000 to Sybase 12 using Sybase.ASEOLEDBProvider.
Executing an openquery statement from MS SQL will retrieve Sybase data; however, I have no advanced functionality available. When I try to use temporary #tables or declare @.vars it returns this error...
Server: Msg 7357, Level 16, State 2, Line 1, Could not process object '…OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Sybase.ASEOLEDBProvider', Query=…

Linked Server configuration:
Provider options: AllowInProcess is checked
Server options: Collation Compatible, Data Access, RPC and RPC Out are checked

Does anyone have a suggestion or alternative for this functionality? Thank you very much for your input.Use OpenQuery to execute a Sybase stored procedure?

-PatP|||Thanks, but 'exec MySybaseSP' locks up, then crashes the sql server.|||We had those Sybase drivers for a while! If you get the new ones (dated early March 2004), they play much more nicely with other vendor's toys!

-PatP|||We will try it. Thanks!|||Pat,
Thank you so much for your post! You give me hope.
Are the Sybase ASE OLE DB new drivers packaged with SDK 12.5.3 or should I look for something different?
Are you using SQL 2000 with linked server to Sybase 12.5? With these options, or something more?
Provider options: AllowInProcess is checked
Server options: Collation Compatible, Data Access, RPC and RPC Out are checked|||Somebody downloaded the "latest and greatest", possibly from the Sybase web site. The downloaded ODBC drivers fixed a lot of ill-defined problems that sometimes worked, sometimes didn't, but always made life more difficult than we really required.

-PatP

Linked server error

Hi friends,

I have written an openquery that selects data scattered over two servers in multiple tables. It uses a cursor to read the rows and then performs validations to get other data.

while running it runs for around 4000 lines of table and then fails giving following error--

Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
[OLE/DB provider returned message: Deferred prepare could not be completed.]

any help will be appreciated

regards

anurag

My assumption is that you are constructing dynamic strings. So, that would mean that you are hitting a piece of data that when concatenated into your string is throwing an error. Instead of executing the dynamic queries, have you just tried dumping them out as strings so that you can find the one that is not generating properly?|||Another possible cause, the table which you are accessing doesn't have valid permission.

linked server direct query problem

Can anyone tell me what's wrong when I was successful to use:
select * from openquery(linkedservername,'select * from
catalogname.dbo.tablename')
However, when I used direct query:
select * from linkedservername.catalogname.dbo.tablename
and it failed with this error:
++++++++++++++++++++++++++++++++++++++++
++++++++++++++++
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
returned 0x80004005: The provider did not give any information about the
error.].
++++++++++++++++++++++++++++++++++++++++
++++++++++++++++
both dbes are SQL 2000 servers. Linked server was configured to use SQLOLEDB
as the provider...Try deleting the datasource information and just leaving the provider
string.
"bNguyen" <bNguyen@.discussions.microsoft.com> wrote in message
news:44825EF1-0AD5-407C-8E61-AA9234DE2BFA@.microsoft.com...
> Can anyone tell me what's wrong when I was successful to use:
> select * from openquery(linkedservername,'select * from
> catalogname.dbo.tablename')
> However, when I used direct query:
> select * from linkedservername.catalogname.dbo.tablename
> and it failed with this error:
> ++++++++++++++++++++++++++++++++++++++++
++++++++++++++++
> OLE DB provider 'SQLOLEDB' reported an error. The provider did not give
any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterfac
e
> returned 0x80004005: The provider did not give any information about the
> error.].
> ++++++++++++++++++++++++++++++++++++++++
++++++++++++++++
> both dbes are SQL 2000 servers. Linked server was configured to use
SQLOLEDB
> as the provider...|||I am not sure I understood what you meant by deleting data source. When I
used direct query, I believe we must use four-part name in the statement to
reference linked server right? Please elaborate if you can... thanks
"Derrick Leggett" wrote:

> Try deleting the datasource information and just leaving the provider
> string.
>
> "bNguyen" <bNguyen@.discussions.microsoft.com> wrote in message
> news:44825EF1-0AD5-407C-8E61-AA9234DE2BFA@.microsoft.com...
> any
> SQLOLEDB
>
>

linked server direct query problem

Can anyone tell me what's wrong when I was successful to use:
select * from openquery(linkedservername,'select * from
catalogname.dbo.tablename')
However, when I used direct query:
select * from linkedservername.catalogname.dbo.tablename
and it failed with this error:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
returned 0x80004005: The provider did not give any information about the
error.].
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
both dbes are SQL 2000 servers. Linked server was configured to use SQLOLEDB
as the provider...
Try deleting the datasource information and just leaving the provider
string.
"bNguyen" <bNguyen@.discussions.microsoft.com> wrote in message
news:44825EF1-0AD5-407C-8E61-AA9234DE2BFA@.microsoft.com...
> Can anyone tell me what's wrong when I was successful to use:
> select * from openquery(linkedservername,'select * from
> catalogname.dbo.tablename')
> However, when I used direct query:
> select * from linkedservername.catalogname.dbo.tablename
> and it failed with this error:
> ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
> OLE DB provider 'SQLOLEDB' reported an error. The provider did not give
any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
> returned 0x80004005: The provider did not give any information about the
> error.].
> ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
> both dbes are SQL 2000 servers. Linked server was configured to use
SQLOLEDB
> as the provider...
|||I am not sure I understood what you meant by deleting data source. When I
used direct query, I believe we must use four-part name in the statement to
reference linked server right? Please elaborate if you can... thanks
"Derrick Leggett" wrote:

> Try deleting the datasource information and just leaving the provider
> string.
>
> "bNguyen" <bNguyen@.discussions.microsoft.com> wrote in message
> news:44825EF1-0AD5-407C-8E61-AA9234DE2BFA@.microsoft.com...
> any
> SQLOLEDB
>
>

linked server direct query problem

Can anyone tell me what's wrong when I was successful to use:
select * from openquery(linkedservername,'select * from
catalogname.dbo.tablename')
However, when I used direct query:
select * from linkedservername.catalogname.dbo.tablename
and it failed with this error:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
returned 0x80004005: The provider did not give any information about the
error.].
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
both dbes are SQL 2000 servers. Linked server was configured to use SQLOLEDB
as the provider...Try deleting the datasource information and just leaving the provider
string.
"bNguyen" <bNguyen@.discussions.microsoft.com> wrote in message
news:44825EF1-0AD5-407C-8E61-AA9234DE2BFA@.microsoft.com...
> Can anyone tell me what's wrong when I was successful to use:
> select * from openquery(linkedservername,'select * from
> catalogname.dbo.tablename')
> However, when I used direct query:
> select * from linkedservername.catalogname.dbo.tablename
> and it failed with this error:
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> OLE DB provider 'SQLOLEDB' reported an error. The provider did not give
any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
> returned 0x80004005: The provider did not give any information about the
> error.].
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> both dbes are SQL 2000 servers. Linked server was configured to use
SQLOLEDB
> as the provider...|||I am not sure I understood what you meant by deleting data source. When I
used direct query, I believe we must use four-part name in the statement to
reference linked server right? Please elaborate if you can... thanks
"Derrick Leggett" wrote:
> Try deleting the datasource information and just leaving the provider
> string.
>
> "bNguyen" <bNguyen@.discussions.microsoft.com> wrote in message
> news:44825EF1-0AD5-407C-8E61-AA9234DE2BFA@.microsoft.com...
> > Can anyone tell me what's wrong when I was successful to use:
> >
> > select * from openquery(linkedservername,'select * from
> > catalogname.dbo.tablename')
> >
> > However, when I used direct query:
> >
> > select * from linkedservername.catalogname.dbo.tablename
> >
> > and it failed with this error:
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > OLE DB provider 'SQLOLEDB' reported an error. The provider did not give
> any
> > information about the error.
> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
> > returned 0x80004005: The provider did not give any information about the
> > error.].
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > both dbes are SQL 2000 servers. Linked server was configured to use
> SQLOLEDB
> > as the provider...
>
>

linked server

i am able to query linked server using openquery
but when i try to access table directly it give error

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'SQLOLEDB'.

i am connecting as sa so security is not an issue

also all othere database from linked server are working this is new database which i attached

sanjitsome thoughts. I like to use domain accounts that have been registered across the linked servers. Otherwise, some ports can be blocked on the linked server.
Originally posted by sanjit
i am able to query linked server using openquery
but when i try to access table directly it give error

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'SQLOLEDB'.

i am connecting as sa so security is not an issue

also all othere database from linked server are working this is new database which i attached

sanjit

Linked Server

Here is bug in SQL 2000. In linked server, SQL query such
as "Select * from openquery (<LinkName>, 'Select * from
<Table name> where <key> in (-- list of key values --
--)')" works well as long as number of items in key
list are limited to 300 or so. In enterprise environment
where one deals with large data, this openquery fails
when items in key list are more then 1000.
Plain query with more then 1000 items in key list works
well against table in SQL server database and other
company product database (which is linked in above case)
when run separately.
In above case, other company database is linked with
provider as "Microsoft OLE DB Provider for ODBC" and data
source is a User DSN in ODBC.
Any comments...Have you tried using 4-part naming:
select
*
from
LinkedServer.MyDB.dbo.MyTable
where
PK in (1, 2, 3, 4, 5)
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Shirish Kokate" <kokateshirish@.hotmail.com> wrote in message
news:029601c3d6f2$13589cf0$a401280a@.phx.gbl...
Here is bug in SQL 2000. In linked server, SQL query such
as "Select * from openquery (<LinkName>, 'Select * from
<Table name> where <key> in (-- list of key values --
--)')" works well as long as number of items in key
list are limited to 300 or so. In enterprise environment
where one deals with large data, this openquery fails
when items in key list are more then 1000.
Plain query with more then 1000 items in key list works
well against table in SQL server database and other
company product database (which is linked in above case)
when run separately.
In above case, other company database is linked with
provider as "Microsoft OLE DB Provider for ODBC" and data
source is a User DSN in ODBC.
Any comments...|||Four part naming does not work and gives error.

quote:

---

>--Original Message--
>Have you tried using 4-part naming:
>
>select
> *
>from
> LinkedServer.MyDB.dbo.MyTable
>where
> PK in (1, 2, 3, 4, 5)
>
>--
>Tom
>----

---

--

quote:

---

>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Shirish Kokate" <kokateshirish@.hotmail.com> wrote in

---

message

quote:

---

>news:029601c3d6f2$13589cf0$a401280a@.phx.gbl...
>Here is bug in SQL 2000. In linked server, SQL query such
>as "Select * from openquery (<LinkName>, 'Select * from
><Table name> where <key> in (-- list of key values --
>--)')" works well as long as number of items in key
>list are limited to 300 or so. In enterprise environment
>where one deals with large data, this openquery fails
>when items in key list are more then 1000.
>Plain query with more then 1000 items in key list works
>well against table in SQL server database and other
>company product database (which is linked in above case)
>when run separately.
>In above case, other company database is linked with
>provider as "Microsoft OLE DB Provider for ODBC" and data
>source is a User DSN in ODBC.
>Any comments...
>

---

|||How about telling us what error you get?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Shirish Kokate" <anonymous@.discussions.microsoft.com> wrote in message
news:08c401c3da54$ce1693c0$a101280a@.phx.gbl...
Four part naming does not work and gives error.

quote:

---

>--Original Message--
>Have you tried using 4-part naming:
>
>select
> *
>from
> LinkedServer.MyDB.dbo.MyTable
>where
> PK in (1, 2, 3, 4, 5)
>
>--
>Tom
>----

---

--

quote:

---

>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Shirish Kokate" <kokateshirish@.hotmail.com> wrote in

---

message

quote:

---

>news:029601c3d6f2$13589cf0$a401280a@.phx.gbl...
>Here is bug in SQL 2000. In linked server, SQL query such
>as "Select * from openquery (<LinkName>, 'Select * from
><Table name> where <key> in (-- list of key values --
>--)')" works well as long as number of items in key
>list are limited to 300 or so. In enterprise environment
>where one deals with large data, this openquery fails
>when items in key list are more then 1000.
>Plain query with more then 1000 items in key list works
>well against table in SQL server database and other
>company product database (which is linked in above case)
>when run separately.
>In above case, other company database is linked with
>provider as "Microsoft OLE DB Provider for ODBC" and data
>source is a User DSN in ODBC.
>Any comments...
>

---

Linked Server

Here is bug in SQL 2000. In linked server, SQL query such
as "Select * from openquery (<LinkName>, 'Select * from
<Table name> where <key> in (-- list of key values --
--)')" works well as long as number of items in key
list are limited to 300 or so. In enterprise environment
where one deals with large data, this openquery fails
when items in key list are more then 1000.
Plain query with more then 1000 items in key list works
well against table in SQL server database and other
company product database (which is linked in above case)
when run separately.
In above case, other company database is linked with
provider as "Microsoft OLE DB Provider for ODBC" and data
source is a User DSN in ODBC.
Any comments...Shirish Kokate (kokateshirish@.hotmail.com) writes:

quote:

---

> Here is bug in SQL 2000. In linked server, SQL query such
> as "Select * from openquery (<LinkName>, 'Select * from
><Table name> where <key> in (-- list of key values --
> --)')" works well as long as number of items in key
> list are limited to 300 or so. In enterprise environment
> where one deals with large data, this openquery fails
> when items in key list are more then 1000.
> Plain query with more then 1000 items in key list works
> well against table in SQL server database and other
> company product database (which is linked in above case)
> when run separately.
> In above case, other company database is linked with
> provider as "Microsoft OLE DB Provider for ODBC" and data
> source is a User DSN in ODBC.

---

And the error message the statement fails with is?
What DB engine do you have on the other side?
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Linked Server

I have a linked as400 and have successfully executed queries against it.

e.g select HDRID,HDRDSC from openquery(SERVER,'select hdrid,hdrdesc from MMTTHOF.table')

I have a problem however when I try to compare a column in my local database with one on the linked server.

The error received is:
"Cannot resolve collation conflict for equal to operation."

I have played around with the Remote Collation and Collation Name settings in SQL Server but to no avail. I then used DTS to bring the table from the AS400 in to the local database and tried running a query against it linking a Char(3) field to its counterpart in the other table. The same error occured. I solved this by changing the collation on the fields in both tables to Latin_General_CI_AS (Although I understood the default dB collation to be SQL_LAtin_General_CP1_CI_AS, so I thought this would have worked).

Anyhow I'm still unable to run the query against the original AS400 table.

Any thoughts would be much appreciated guy's n gals

JBrute force is sometimes appropriate. When using a linked server, it is sometimes simplest to pick an arbitrary collation that works well, the force both of the columns to use that collation!

-PatP