DreamCarr
Winter Sucks.
Premium
join:2001-04-16
6D 6F 6F 00
| uploading data to SQL Server 2005 timeout issues
Hi Guys,
I've got an access 97 back-end that I'm trying to replicate to a new remote SQL Server 2005 database.
The data I'm working with isn't huge, one table is roughly 12k rows, the other is roughly 40k.
My problem is this: I'm using SQLBULKCOPY to upload the data en masse, and it keeps timing out. I've set the bulkcopy's connection and command to timeout after 5 minutes, which should be more than enough time (I've uploaded a csv of the same data in 2 minutes, its only 8 megs for the 40k table.)
I've got the 40k table to do batches of 5000 rows, and the 12k table to run in just one batch.
Am I doing this wrong? I could iterate through my data one row at a time with insert commands, but that takes roughly 20 mins for the 12k table, and I didn't want to run the 40k in that manner -- it'd take all day!
Please note I'm using a hosted server, so I have no control over the configuration of the server, just my code.
Details:
programming lang: VB.net 3.5 sp1
SQLServer: 2005
Net Connection: 1gb internal, 768u/3000d external
method of copying data: SQLBULKCOPY from dataset, which is derived from an Access97 datastore.
Please feel free to ask for more detail if you need it.
fake edit: the code has now timed out after setting the command/connection timeout to 10 minutes.
--
never apoligize for being who you are, and never respect anyone that says you should. |
|
sunpost
join:2003-11-23
Allentown, PA
clubs:
| Maybe the following KB article can help:
»support.microsoft.com/?kbid=913177
Do you have many indexes on the tables that could be slowing down the upload? Seems unlikely that number of rows would cause an issue though.
--
00:11:AE:00:CA:54 |
|
DreamCarr
Winter Sucks.
Premium
join:2001-04-16
6D 6F 6F 00
| reply to DreamCarr
Hmm, I'll have to check that out tomorrow at work, but I'm using .NET 3.5 so it might not apply. That is almost the exact error though.
As for indexes, the only ones I have are on numeric primary keys - but something to double check.
Thanks!
--
never apoligize for being who you are, and never respect anyone that says you should. |
|
