SQL Server getting Lost after a large number of iterations 1

[ALWilliams]

Ive some code which reads a CSV file and line by line inserts it into an SQL server.

However, the file has ~ 12,000 entries yet when the code gets to ~ 3933 it raises the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Why would this happen??

High-Level Psudo Code

Start of CSV Loop

Read line of data from csv
Insert into table (a,b,c,d) values (1,2,3,4) from csv
execute NonQuery

End Of CSV Loop

everything is closed/set to nothing/disposed.

I get this upon connection.open...

 

[gmmastros]

Try reading 100 lines at a time and inserting them. The SQL would look like this....

[tt][blue]
Insert Into Table(a,b,c,d)
Select 1,2,3,4 Union All
Select 5,6,7,8 Union All
Select 9,0,1,2
[/blue][/tt]

This is likely to execute 100 times faster too.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pmegan]

How about pushing the lines from your csv file into a data table and using a table adapter or data adapter to update the SQL table? The inserts should be more efficient than individual inserts, you can break it up to call the insert every few hundred lines, and if there's a problem with an insert command the new lines will still be flagged as new and should get picked up with the next insert call.

 

[ALWilliams]

Thanks for the alternative suggestions but I'd still like to understand why the method Im using is failing after this many iterations.

One of the reasons I dont just dump it straight in is due to me needing to perform some work upon it (not much albeit) prior to sending to SQL.

I'm just curious as to why this method only seems to work for ~ 3300 iterations.

 

[jebenson]

Are you closing and reopening the SqlConnection with each insert? If so, try just leaving the connection open for the entire operation and closing it after the last insert.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[ALWilliams]

I am yes as i was kinda using a common sub to commit to the db.

Copied this code into the routine and only closed it at the end and not only is it quicker (which you'd expect) it works.

Cheers.