September 30, 2011 at 1:18 am
Hi,
I'm having errors updating huge amount of data in an sql server. Error only occurs when I have data of more than 100 rows. But the same error does not occur for Oracle server. The codes are inherited from years ago. RDO resultset is used for updating. A bunch of rows are queried first with the SELECT statement then they're UPDATED row by row.
I've found that there is a property called "rowsetsize" which is not set in the codes. Also, read from this website that default value for the rowsetsize property is 100. Since it is not set in the codes, i'm assuming that it is using the default value for sql server.
I have a few questions:
1) Why does the error occur in sql server only and not in oracle too?
2) It says in the website that "The upper limit of the RowsetSize is determined by the data source driver." Is this somehow related to my errors? How do i know the upper limit set in oracle and sql?
3) Does setting the rowsetsize affect the performance of my application? concurrowver setting (optimistic locking) is used to create the resultset, so does that mean that rowsetsize does not lock any rows?
4) Other than setting the rowsetsize and switching from RDO resultset to ADO recordset, is there any other choice of solution? Can I change any settings in the server to allow me to update more than 100 rows?
I am rather new in this. Really really appreciate some advice and ideas here.
Thanks in advance! 🙂
-D-
September 30, 2011 at 5:09 am
can you post your DDL and your query with some sample data. that we can follow.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
September 30, 2011 at 9:25 am
You said you have errors. Would you care to share those?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply