Polling large Data from the database

476 views Asked by At
  I have written stored procedure to poll the data from the database in biztalk.but the datasize is very large its around 80MB...Due to which i am getting error.Does anyone knows what configuration do i need to change to poll that much big amount of data.

in the table EDI834_5010_2300Loop i have around 35000 rows i need to chucnk the data depending upon this table

2

There are 2 answers

3
Dmitry Golubets On BEST ANSWER

What do you mean by big amount of data? Big blob fields or many rows? If latter is the reason - modify your procedure to return data in chunks and set PollWhileDataAvailable = true in adapter.

In one of my current projects I use such procedure code to get data in chunks:

DECLARE @SubsetOfChanges TABLE (ChangeID BIGINT PRIMARY KEY)

INSERT INTO @SubsetOfChanges
SELECT TOP 100 ChangeID FROM bts_DatabaseChanges WHERE Processed = 0 AND TableName = 'Producer'

SELECT p.*, changes.Operation as operation, changes.RowKey AS original_id 
FROM (SELECT * FROM bts_DatabaseChanges WHERE ChangeID IN (SELECT * FROM @SubsetOfChanges)) AS changes
JOIN [region].[dbo].crm_clsProducer p ON changes.RowKey = p.producer_id  

UPDATE bts_DatabaseChanges
SET Processed = 1
WHERE ChangeID IN (SELECT * FROM @SubsetOfChanges)

bts_DatabaseChanges is a log-table for all modifications in the DB.

0
TJ Amas On

Are you using the WCF LOB Adapters for this? If so, check te MaxReceivedMessageSize property on the bindings on your send receive port to increase from the default 65000 bytes.