Inserting more then 3380 rows in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Inserting more then 3380 rows in a table

Author

Topic

hari01204
Starting Member

2 Posts

Posted - 2011-05-31 : 08:09:32

While developing a application in C# .Net, for uploading details of file having 60,000 rows, it is seen that after 3380 rows no more rows are getting added to table. I am trying this using "sa" user.
Can anyone let me know how can i enter 60k rows in table using my application? Thanks in Adv.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-31 : 08:15:43

There is no inherent limitation of 3380 rows (or any specific number) that you can insert into a table from C#.Net app that I know of. So I suspect that it has to be some type of artifact of the programming.

Is it always terminating after inserting 3380 rows? If so, it may be some string or varchar variable that you have in the code that is causing this.

If it is not exactly 3380, but somewhere in that neighborhood, it could be command timeout or connection timeout (which are 30 and 15 seconds by default).

Also, if you are inserting one row at a time from your C# code, that would be a very inefficient way of doing the inserts. There are much faster and more efficient ways to upload the data.

I don't have any other concrete suggestions without being able to see the code.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-05-31 : 08:59:09

http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/

hari01204
Starting Member

2 Posts

Posted - 2011-05-31 : 09:29:55

Connect Timeout set to 120s in connection string and Command Timeout set to 1000s.
I also tried executing Bulk Insert command as a SQL query
"bulk insert Handset_Dts_temp from 'c:\test.txt' with (FIELDTERMINATOR='|',ROWTERMINATOR = '\n')".
But this gave a error message as follows
=========================================================================================
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
=========================================================================================
Not getting what this error mean..:-(

Ya in my code I am always doing a insert after parsing the line on "|" separated delimiters.

Some Part of my code is as below

while ((line = rdr.ReadLine()) != null)
{
string value = line.Trim();
string[] parts = value.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);

//Pass the data to be inserted into DB
if (con.State == ConnectionState.Open)
{
Insert_Data(parts);
}else{
DBConn_Open();
}
}

public void DBConn_Open()
{
con = new SqlConnection();
con.ConnectionString = "Data Source = DELL-PC\\SQLEXPRESS;Initial Catalog = test_db;User ID=sa;Password=sa;MultipleActiveResultSets=true;Connect Timeout=120";
con.ConnectionString = con_string;
con.Open();
}

public void Insert_Data(string[] parts)
{
SqlCommand cmd;
cmd.CommandTimeout = 1000;
cmd = con.CreateCommand();
cmd.CommandText = "Insert into test_Dts(" + "ID,Name,Manufacturer,Band" + ") values (" + "@ID, @Name, @Manufacturer,@Band" + ")";

cmd.Parameters.AddWithValue("@ID", ID);
cmd.Parameters.AddWithValue("@Name", Name);
cmd.Parameters.AddWithValue("@Manufacturer", Manufacturer);
cmd.Parameters.AddWithValue("@Band", Band);
cmd.ExecuteNonQuery();
}

Earlier when I didn't have "CommandTimeout" not set after 3380 rows where entered I was getting a Time out error, after setting CommandTimeout to 1000 this error was fixed, but now the application remains in hanged state now.

Let me know if any suggestion. Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-31 : 09:41:13

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.

This should point you in the direction to examine your file...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources