OpenQuery Not allowing more than 8000 characters

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

SQL Server 2000 Forums

SQL Server Development (2000)

OpenQuery Not allowing more than 8000 characters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-09-28 : 06:58:28

Anil writes "Below Query fetches all customerid's from cust table and assign to variable @cidAll.The records are stored in variable @cidall as follows (1,2,3,4,5,6,7,8,9). The variable is used in the OpenQuery statement. If the variable @cidall exceeds more than 8000 char the query throwing syntax error and some of the Customer are truncated. Any suggestion or solution please help me. Thanks in Adv.

**************************************************************
declare @cidall varchar(8000)
declare @storeno
declare @strQry varchar(8000)

set @cidall='0'
set storeno=2566

SELECT @CidAll = @CidAll + convert(VARCHAR,CID) + ',' from
(SELECT DISTINCT(U.customer_id) cid from CUST U
WHERE U.flag = 1 AND store_num= replace(@storeno,' ','')) t

SET @CidAll = STUFF(@CidAll, len(@CidAll), 1, '')

--COnnecting Linked server through Oracle OLE Provider
SET @strQry = 'select * from
openQuery(MY_ORACLE,'select * from ADDRESSES t where customer_id IN (''+ @CidAll + '')''')'"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 07:04:08

Make use of INNER JOIN between the OPENQUERY and the local customer thingy.

select *
from openquery(my_oracle, 'select ...') r
inner join cust c on c.customer_id = r.customer_id
where c.storenum = @storenum
and c.flag = 1

Peter Larsson
Helsingborg, Sweden

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-28 : 07:19:28

Eeeeewwwwww, that's gonna be slow. Cross-server joins are very inefficient. Break the processing into smaller chunks that can be run sequentially:

declare @cidall varchar(8000), @storeno int, @strQry varchar(8000), @IDs TABLE(ID int NOT NULL, PRIMARY KEY(ID))

set storeno=2566

INSERT @IDs SELECT DISTINCT U.customer_id cid from CUST U WHERE U.flag = 1 AND store_num= replace(@storeno,' ','') ORDER BY U.customer_id

WHILE EXISTS(SELECT * FROM @IDs)
 BEGIN
 set @cidall=''
 SELECT TOP 500 @CidAll = @CidAll + convert(VARCHAR,ID) + ',' from  @IDs ORDER BY ID
 SET @CidAll = STUFF(@CidAll, len(@CidAll), 1, '')

 --COnnecting Linked server through Oracle OLE Provider
 SET @strQry = 'select * from openQuery(MY_ORACLE,'select * from ADDRESSES t where customer_id IN (''+ @CidAll + '')''')'"

 INSERT INTO myTable EXEC(@strQry)
 SET ROWCOUNT 500
 DELETE @IDs
 SET ROWCOUNT 0
END

Ahhhh, the memories this brings back.

I used to do this a lot about 5 years ago, it works like a charm although it looks pretty messy.

Instead of grabbing all the data at once, you pass 500 IDs at a time (or more, as long as the 8,000 character limit is respected) and get those results back. You then delete the 500 IDs you processed and get the next 500, and so on until all the IDs have been processed and deleted from the table variable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources