Error - An unhandled exception of type System.OutOfMemory Exception occurred in mscorlib.dll

Question

_{Thursday, January 25, 2018 9:53 AM}

Hi

  I am trying to execute Sql Script file Size is larger than 8 Gb. It is giving me error "An unhandled exception of type System.OutOfMemory Exception occurred in mscorlib.dll

Thanks

All replies (6)

_{Thursday, January 25, 2018 12:03 PM}

Can you split and execute in a transaction?

---

_{Thursday, January 25, 2018 12:45 PM}

Hi

  Below is the code . How u want that i split the file .  U want to split .sql file or something else. .Sql file i cannot split.

Hi is it possible thru code that it should not load full file in memory . It should do in parts

string sqlConnectionString = "Data Source=(local);Initial Catalog=Test;Integrated Security=True";

FileInfo file = new FileInfo("c:\test.sql");

string script = file.OpenText().ReadToEnd();

SqlConnection conn = new SqlConnection(sqlConnectionString);

Server server = new Server(new ServerConnection(conn));

server.ConnectionContext.ExecuteNonQuery(script);

Thanks

---

_{Thursday, January 25, 2018 4:22 PM}

the max batch size in sqlserver (the string you can send to ExecuteNonQuery) is around 256mb. so you need to split up your input file. how this is done depends on the contents. if its a series of insert statements. then use a streaming file reader, parse the inserts, and send in batches. if its text / image blobs, it will be more complex. you will next parse the blob  and write it in chucks. see:

   /en-us/sql/t-sql/queries/writetext-transact-sql

 

---

_{Thursday, January 25, 2018 4:39 PM}

Hi

   File has multiple Inserts. How i can parse it . I am trying below code but not working. It is still giving above error .

Secondly i want during execution it should display no of records processsed.

string connstring = "Data Source=(local);Initial Catalog=test;Integrated Security=True";
var fileContent = File.ReadAllText("e:\Sql\data\test.sql");
var sqlqueries = fileContent.Split(new[] { " INSERT " }, StringSplitOptions.RemoveEmptyEntries);

var con = new SqlConnection(connstring);
var cmd = new SqlCommand("query", con);
con.Open();
foreach (var query in sqlqueries)
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
con.Close();

Thanks

---

_{Friday, January 26, 2018 8:18 AM}

Hi JagjitSingh,

It is hard to believe you run SQL Script file which is large than 8 GB.

Why did not you convert the SQL Script to Stored Procedure in SQL Server, and then call the Stored Procedure from C#?

Best Regards,

Edward

---

_{Friday, January 26, 2018 9:39 AM}

You posted the same question at https://forums.asp.net/t/2135399.aspx

Please avoid to post the same question multiple times (you can ask for moving a message to another forum or mark the question in the wrong fourm as being "closed").