Writing into AS400 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Writing into AS400

Author

Topic

ineedhelp
Starting Member

11 Posts

Posted - 2005-02-24 : 19:46:44

Hi

I am trying to INSERT into AS400 DB2 thru Linked Server on SQL Server connected using ODBC DSN (with Commit *NONE). My VB program (ADO) fails to even SELECT data from the linked server.

I run the queries from VB code suing ADO connection to the same ODBC DSN. I get the following errors when I pass these for querystrings to adoConnection.Execute :-

1) SELECT * FROM LINKEDSERVER.CATALOG.SCHEMA.TABLENAME

[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token . was not valid. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE

2) select * from openquery(LINKEDSERVER, 'select * from MYTABLE1')

[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token ( was not valid. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE

3) INSERT INTO LINKEDSERVER.CATALOG.SCHEMA.TABLENAME1 SELECT * FROM LINKEDSERVER.CATALOG.SCHEMA.TABLENAME2

[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token . was not valid. Valid tokens: ( ? : WITH SELECT VALUES <INTEGER> <IDENTIFIER>

4) When I run these queries from SQL Query Analyzer, all SELECT statements work but INSERT says "SQL7008 - not valid for operation"

The files are not journaled.

Any answers..
Thanks.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-24 : 19:53:11

Why would you use ADO to connect to a SQL Server to query AS/400 via a linked server? Why not just connect to AS/400 directly?

ineedhelp
Starting Member

11 Posts

Posted - 2005-02-26 : 16:14:17

Thanks. That was silly of me. I have now connected to AS400 directly using DSN thru ADO. It works.

Thanks

Danimal
Starting Member

2 Posts

Posted - 2005-05-02 : 18:10:48

I am trying to connect SQL Server to AS400, using a DSN on my SQL Server machine that points to the AS400. However, when I try inserting or updating to the AS400, I get an error at the destination, saying the failed rows don't have a searchable key.

Sooo, how do you connect to AS400 "directly using DSN through ADO?" It sounds like this would solve my problem. My programming task is almost exactly the same as this thread starter.

As always, thanks in advance for any information helping lead to a solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources