June 4, 2004 at 3:59 pm
I'm inserting data into a table from a table that has around 180000 rows. My procedure writes the @@AFFECTEDROWS to a history table and clearly shows that it inserts 10000 rows each time it is run. I know there is a limit on the rows returned for views of 10000 but don't know how to turn it off. I assume it is something at the beginning of the procedure which will change the MAXROWS or something. Could someone please advise me how to overcome this problem. I've searched BOL unsuccessfully already.
June 4, 2004 at 5:33 pm
The obvious solution to the above problem is:
SET ROWCOUNT = 0
Except that the sp is loading data to a local table on the same server from a different database. The BOL article covering this implies there is not solution and this would not work unless it was for the same database.
June 5, 2004 at 5:21 am
Check if the view is declared with a TOP 10000 clause.
And by the way there is no limit for views to only return 10000 rows.
/rockmoose
You must unlearn what You have learnt
June 7, 2004 at 11:39 am
The 10000 limit may be an Access limit which I need to work around. There is no Top 10000 in the T-SQL statement shown below although I've put the TOP 100 PERCENT in to try to force the full return:
INSERT INTO dbo.tblPN
SELECT TOP 100 PERCENT tpn.PN, tpn.Cage,
CASE tpn.Category WHEN 'L' THEN 'L' WHEN 'S' THEN 'S' ELSE 'C' END AS Category, tpn.Type, tpn.SptHorizon, GETDATE() AS ThisDate
FROM ToMgmtSQL.dbo.tblPN tpn LEFT OUTER JOIN
dbo.tblPN ON tpn.PN = dbo.tblPN.PN
WHERE (LEN(ISNULL(dbo.tblPN.PN, '')) = 0)
SET @InsertedRows = @@ROWCOUNT
June 7, 2004 at 12:25 pm
The Access issue is resolved here:
Tools -> Options -> Advanced -> And here is an option indicating the maximum number of rows.
(this is for Access 2000, and I don't have an English version of Access, but the names are good guesses :-))
/rockmoose
You must unlearn what You have learnt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply