SQL Server Maximum Transactional Row Insert using Classic ASP

Question

I'm running into a problem inserting rows from a file with 13,000 rows into a SQL Server 2008 database. I can take a small chunk of the file and insert it just fine. I've been able to insert as many as 4000 rows in a single transaction (yes, I'm using transactions), but when I use something higher I get a server 500 error.

Is there a limit on how many rows you can Commit or Rollback in SQL Server using an SQLOLEDB database connection in classic ASP? If so, how do I increase that?

If not, what other things should I look for in both IIS and SQL Server to ensure I can insert that many rows?

Have you considered bulk insert or bcp instead of trying to use asp to insert thousands of rows? — Aaron Bertrand
– Aaron Bertrand, Commented Jul 1, 2012 at 2:58
Yes, I have considered that, but to be honest this was the approach I implemented first. Bulk inserting is a method I may try out as an alternative. — Doug P.
– Doug P., Commented Jul 1, 2012 at 3:45
That would be concerning... you would be effectively allowing any script to execute endlessly which greatly impacts any server performance. Bad idea IMO. — Doug P.
– Doug P., Commented Jul 3, 2012 at 23:36

paulsm4 · Accepted Answer · 2012-07-01 02:49:07Z

0

I'm not aware of any limit in the #/rows for a single transaction (a "transaction" being a T-SQL "begin tran"/"commit").
There are lots of limitations in "classic ADO" (the data access API used by "classic" ASP 3.0 and VB6). But frankly, I don't recognize any of them as being applicable to this scenario.
Q: What does your actual ADO "insert" look like? Could you cut/paste the code here?
Q: Have you looked at your IIS server logs? At your MSSQL server logs? Are any ADO exceptions being generated (your are detecting client side errors, aren't you?).

answered Jul 1, 2012 at 2:49

paulsm4

123k23 gold badges175 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Doug P. Over a year ago

Aha... Taking a look at my IIS settings I see I had my script timeout setting too low. Once I bumped this up the app ran properly. Thanks for the pointers!

Collectives™ on Stack Overflow

SQL Server Maximum Transactional Row Insert using Classic ASP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related