**** Need Help in setting OPENQUERY() with linked servers ****

Hello!
I'm a newbie running queries against linked servers. Someone had
already
setup the linked servers in SQL2000 to go access AS400/DB2 and ORACLE
tables.
Let's say there is linked server in SQL2000 called "MyLinked_AS400" and
I have
the following tables in which I need to retrieve data. These are
ficticious data.
MyAS400_DB.Table01, with these columns PRODUCT_ID, PROD_DESC, PROD_QTY
MyAS400_DB.Table02, with these columns PRODUCT_ID, ORG_DESC, ORG_QTY
and I am trying to setup a tsql statement to join these two tables and
pass
it to OPENQUERY() but getting errors. I don't remember the error right
now but it
said something about "The provider did not provide any additional
information"
The error seems to be that of when you misspelled a column name.
I had something like this:
SELECT * OPENQUERY(MyLinked_AS400,
'SELECT
Table01.PROD_DESC,
Table02.ORG_DESC
FROM MyAS400_DB.Table1 INNER JOIN
MyAS400_DB.Table2 ON
Table01.PRODUCT_ID = Table02.PRODUCT_ID
WHERE Table01.PRODUCT_ID = ''123''
FOR FETCH ONLY WITH UR
') AS dt
--
I have been getting all kinds of strange problems today partly because
I am new in
setting up these type of statements. What's wrong with the above code?
Is there
a better way to write this and still using "MyLinked_AS400"?
I had another statement like this:
SELECT * OPENQUERY(MyLinked_AS400,
'SELECT *
Table01.ZIP
FROM MyAS400_DB.Table1
WHERE Table01.ZIP = ''21157''
FOR FETCH ONLY WITH UR
') AS dt
--
It produced one record. But if I had ''19020'', it failed with the
same exact
error produced in the above statement. Does it mean if there is no row
matching
this condition, I need to code it differently?
I need you, Gurus, out there to help me. I'd greatly appreciate if you
can include a copy to my work email in below.
Thank you in advance!
Sydney
sluu@.nfiinteractive.comHi
The most obvious problem is that you are missing FROM in the SELECT * FROM
OPENQUERY(...)
I am not sure of your AS400 queries, have you checked that they work
directly on the AS400?
If you posted the error message and number it may be clearer what the errors
are without having your systems!
John
"sydney.luu@.gmail.com" wrote:

> Hello!
> I'm a newbie running queries against linked servers. Someone had
> already
> setup the linked servers in SQL2000 to go access AS400/DB2 and ORACLE
> tables.
> Let's say there is linked server in SQL2000 called "MyLinked_AS400" and
> I have
> the following tables in which I need to retrieve data. These are
> ficticious data.
> MyAS400_DB.Table01, with these columns PRODUCT_ID, PROD_DESC, PROD_QTY
> MyAS400_DB.Table02, with these columns PRODUCT_ID, ORG_DESC, ORG_QTY
> and I am trying to setup a tsql statement to join these two tables and
> pass
> it to OPENQUERY() but getting errors. I don't remember the error right
> now but it
> said something about "The provider did not provide any additional
> information"
> The error seems to be that of when you misspelled a column name.
> I had something like this:
> --
> SELECT * OPENQUERY(MyLinked_AS400,
> 'SELECT
> Table01.PROD_DESC,
> Table02.ORG_DESC
> FROM MyAS400_DB.Table1 INNER JOIN
> MyAS400_DB.Table2 ON
> Table01.PRODUCT_ID = Table02.PRODUCT_ID
> WHERE Table01.PRODUCT_ID = ''123''
> FOR FETCH ONLY WITH UR
> ') AS dt
> --
> I have been getting all kinds of strange problems today partly because
> I am new in
> setting up these type of statements. What's wrong with the above code?
> Is there
> a better way to write this and still using "MyLinked_AS400"?
> I had another statement like this:
> --
> SELECT * OPENQUERY(MyLinked_AS400,
> 'SELECT *
> Table01.ZIP
> FROM MyAS400_DB.Table1
> WHERE Table01.ZIP = ''21157''
> FOR FETCH ONLY WITH UR
> ') AS dt
> --
> It produced one record. But if I had ''19020'', it failed with the
> same exact
> error produced in the above statement. Does it mean if there is no row
> matching
> this condition, I need to code it differently?
> I need you, Gurus, out there to help me. I'd greatly appreciate if you
> can include a copy to my work email in below.
> Thank you in advance!
> Sydney
> sluu@.nfiinteractive.com
>