November 11, 2013 at 11:52 am
I am trying to query from a linked server where a fields is in a list of values queried from my local server. I am not sure how to do this. here is what I have
select * from Openquery(LinkedServer, 'select * from MFFRDB.MBPROD73.F0411 a where a.rpdoc in (select documentnumber from localserver.db_docs_acct.dbo.temp1_checkvoucher) and a.rpdct in (''PD'',''PV'') and a.RPTORG in (''DARA'',''ANGELF'',''HOLLE'',''ALEXJ'',''STANTOND'',''TYLERM'',''STEPHANIEM'',''DWUNDERL'',''BRANDIM'',''SHERRYH'',''BRITTANI'',''CHALYS'',''CINDE'')')
here is the error:
OLE DB provider "MSDASQL" for linked server "LinkedServer" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0107 - DB_DOCS_ACCT too long. Maximum 10 characters.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * from MFFRDB.MBPROD73.F0411 a where a.rpdoc in (select documentnumber from localserver.db_docs_acct.dbo.temp1_checkvoucher) and a.rpdct in ('PD','PV') and a.RPTORG in ('DARA','ANGELF','HOLLE','ALEXJ','STANTOND','TYLERM','STEPHANIEM','DWUNDERL','BRANDIM','SHERRYH','BRITTANI','CHALYS','CINDE')" for execution against OLE DB provider "MSDASQL" for linked server "LinkedServer".
Any help would be great..
November 12, 2013 at 11:32 am
terinoe (11/11/2013)
I am trying to query from a linked server where a fields is in a list of values queried from my local server. I am not sure how to do this. here is what I haveselect * from Openquery(LinkedServer, 'select * from MFFRDB.MBPROD73.F0411 a where a.rpdoc in (select documentnumber from localserver.db_docs_acct.dbo.temp1_checkvoucher) and a.rpdct in (''PD'',''PV'') and a.RPTORG in (''DARA'',''ANGELF'',''HOLLE'',''ALEXJ'',''STANTOND'',''TYLERM'',''STEPHANIEM'',''DWUNDERL'',''BRANDIM'',''SHERRYH'',''BRITTANI'',''CHALYS'',''CINDE'')')
here is the error:
OLE DB provider "MSDASQL" for linked server "LinkedServer" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0107 - DB_DOCS_ACCT too long. Maximum 10 characters.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * from MFFRDB.MBPROD73.F0411 a where a.rpdoc in (select documentnumber from localserver.db_docs_acct.dbo.temp1_checkvoucher) and a.rpdct in ('PD','PV') and a.RPTORG in ('DARA','ANGELF','HOLLE','ALEXJ','STANTOND','TYLERM','STEPHANIEM','DWUNDERL','BRANDIM','SHERRYH','BRITTANI','CHALYS','CINDE')" for execution against OLE DB provider "MSDASQL" for linked server "LinkedServer".
Any help would be great..
I suppose what you have written could work if LinkedServer has the ability to execute the subquery on LocalServer - I suspect this is not true. The easiest way around this would be to get the data from LinkedServer, filtered on columns that exist there, then filter that set by the columns that exist on LocalServer, like this:
SELECT x.*
FROM (SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM MFFRDB.MBPROD73.F0411 a WHERE a.rpdct IN (''PD'',''PV'') AND a.RPTORG IN (''DARA'',''ANGELF'',''HOLLE'',''ALEXJ'',''STANTOND'',''TYLERM'',''STEPHANIEM'',''DWUNDERL'',''BRANDIM'',''SHERRYH'',''BRITTANI'',''CHALYS'',''CINDE'')')) x
WHERE x.rpdoc IN (SELECT documentnumber FROM localserver.db_docs_acct.dbo.temp1_checkvoucher)
That may mean you are bringing more data than you'd like from LinkedServer over to LocalServer, but linked servers are a pain like that.
The other alternative I can think of is to build the whole SELECT FROM OPENQUERY() construct dynamically, but if the subquery on LocalServer returns too many documentnumber values, the query argument of the OPENQUERY() may be too long.
Jason Wolfkill
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply