Web Wiz NewsPad - Import script errors

Print Page | Close Window

Import script errors

Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz NewsPad
Forum Description: Support forum for the Web Wiz NewsPad application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=23629
Printed Date: 29 March 2026 at 6:02am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Import script errors

Posted By: Miguel
Subject: Import script errors
Date Posted: 29 June 2007 at 3:56pm

First 6 batches of 2001 addresses were fine then I started getting this error......

Microsoft OLE DB Provider for SQL Server error '80040e2f'

Cannot insert duplicate key row in object 'tblNPMembers' with unique index 'ID_Code'.

/newspad/admin_import_subscribers.asp, line 278

Also if you receive this error when importing......

Active Server Pages error 'ASP 0113'

Script timed out

/newspad/admin_import_subscribers.asp

The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeout or by changing the value in the IIS administration tools.

Replace:-

Session.Timeout

With:-

Server.ScriptTimeout

and give it a larger value than 1400 (the default in the script I had), I set IIS asp script time out to the maximum 2000000000 and my Server.ScriptTimeout in the script to 2000 as below

'Set the script timeout to 24 hours incase there are lots of emails addresses to import
Server.ScriptTimeout = 2000

-------------
::Musica Mas Fina::

Replies:

Posted By: Miguel
Date Posted: 29 June 2007 at 4:02pm

Line 278 is

'Update the database
.Update

-------------
::Musica Mas Fina::

Posted By: Miguel
Date Posted: 04 July 2007 at 5:56pm

I still had some timeouts with some of the slower imports, especially if the script has no duplicate data. The time taken to check and insert a record is quite snail like, even on a 64 bit windows server.

Server.ScriptTimeout = 2000 doesn't seem to be long enough for some reason so I've had to up it to 4000 now.

I'm still getting this error on some of the csv files
Microsoft OLE DB Provider for SQL Server error '80040e2f'
Cannot insert duplicate key row in object 'tblNPMembers' with unique index 'ID_Code'.

/newspad/admin_import_subscribers.asp, line 278

Doesn't seem to be in any set record either, just whenever it feels like giving up. Sometimes it seems to max out my CPU on the server side at 100% after a crash to which is a little bit worrying to say the least.

Luckily I have my newspad running in a different app pool to my forum so it doesn't kill it, although it does slow the forum down. I assume this is due to the CPU max out though.

-------------
::Musica Mas Fina::

Posted By: WebWiz-Bruce
Date Posted: 04 July 2007 at 6:17pm

It's not surprising really, importing members does place a massive amount of strain on the server and database.

Each members needs to be checked that they don't already exist in the database then 2 writes are required, one to enter the user data and read back their ID number and the next to write to the database the user category.

Reading for a database isn't to much problem, but a writing to the database requires the database table to be locked during the update then released again after.

This places allot of strain on the database, as most things usually read and not write to a database things general run quite quick, but with a database import there is no getting around allot of database writing needs to be done.

I would suggest that if you need to import allot of members you do it on a non-production server or at a quite time.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: Miguel
Date Posted: 04 July 2007 at 9:08pm

Yeah, it's a shame there isn't a desktop utility for this type of thing. Or maybe there is, I've seen loads of SQL import tools so might see if there's one that will work for this DB. We can choose the import field to use in the 'import from database' script so surely there's something for SQL that we can use to inject the data in there quickly. Obviously this is only any use if you have remote access to the server desktop.

I wouldn't have expected this process to use 100% CPU on a 3ghz 64 bit processor but I guess it's just SQL spacking out or something.

I have been running the import scripts at night when there is no traffic and it does seem to make quite a difference. The script obviously runs quicker and doesn't seem to have as many errors. It's messed my sleep pattern up but hopefully it will be worth it in the end.

I'll let you know how I get on emailing to this massive list.

-------------
::Musica Mas Fina::

Posted By: WebWiz-Bruce
Date Posted: 05 July 2007 at 8:56am

That would be interesting to know.

I've carried out tests myself with a database of 10,000 users which were able to send to an SMTP server queue in around 25 minutes. However did not allow the SMTP server to relay on the email as 10,000 people would be rather upset with a test email clogging up their inbox.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: Miguel
Date Posted: 09 July 2007 at 11:12am

I'll let you know how we get on. We'll be using the localhost SMTP relay rather than an external SMTP server though.

-------------
::Musica Mas Fina::

Posted By: WebWiz-Bruce
Date Posted: 09 July 2007 at 1:28pm

Best way, although if you are using Microsofts IIS SMTP server I would use the batch/delay tool as it does tend to get it's queue directory a bit clogged up as it doesn't seem to like more than 5,000 to 8,000 emails in it's queue.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: Miguel
Date Posted: 09 July 2007 at 1:39pm

Thanks Bruce, so set number of emails per batch to 7,500 with a time interval of how long?

-------------
::Musica Mas Fina::

Posted By: Miguel
Date Posted: 12 July 2007 at 5:59pm

We sent the batches of 7,500 with 1hr interval to a 16k + db and it all went out in 20 mins with no errors.

All good

-------------
::Musica Mas Fina::