asked
linked server
I have created a linked server to AS400. In AS400 we have table names like AT.ITEM when I write a SQL on query analyzer using openquery for e.q `SELECT * FROM OPENQUERY(AS400, 'SELECT name, id FROM AS400LIB."AT.ITEM"')` It worsk but if I write SQL like SELECT * FROM AS400... "AT.ITEM" its give an error..
I recommend you to use OPENQUERY syntax for the best performance at least if the table is large. I also recommend you to specify the columns you need (don't use the SELECT * syntax) and specify a WHERE clause that use an index if you can. You also need to specify SET QUOTED IDENTIFIER ON to be able to use the double quotes. The syntaxt to use a linked table name in T-SQL is LINKEDSERVERNAME.DATABASENAME.SCHEMA.OBJECTNAME Is the name of your object "AT.ITEM"? or is AT your schema name?
Thanks AT.ITEM is an object name because of dot in object name i'm geeting error in simple SQL.. however if I'm using Openquery with above syntax its working fine I'm getting this error if i right sim[ple SQL Server: Msg 7306, Level 16, State 2, Line 1 Could not open table 'AT.ITEM' from OLE DB provider 'MSDASQL'. The specified table does not exist. [OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - ITEM in AT type *FILE not found.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset::OpenRowset returned 0x80040e37: The specified table does not exist.].
If you run SELECT * FROM AS400...[AT.ITEM] does that work?
yes same error message
Have you tried all combinations like: SELECT * FROM AS400..AT.ITEM or SELECT * FROM AS400.."AT.ITEM"
yes did i tries with [AT.ITEM] and "AT.ITEM" both are not working.."AT.ITEM" is woorking for openquery
its working like this AS400...["AT.ITEM"]
