Import script errors

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

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.

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.

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