Memory Exception Occurred while fetching data from SQL for Bulk insert
0
I just tried to bulk insert data which results to system.out.memory exception was thrown Even though the Ram as 260 GB and SQL Standard Edition 2017. Records was around 1 million , please give me any solution with my below code
DataTable table = new DataTable();
// read the table structure from the database
SqlDataAdapter adapter = new SqlDataAdapter("select * from xxxxx", constr);
adapter.SelectCommand.CommandTimeout = 0;
adapter.Fill(table);
Console.WriteLine();
Console.WriteLine(table.Rows.Count);
Console.WriteLine(" Table End time..");
Console.WriteLine(DateTime.Now);
Console.WriteLine("end----)";
Console.WriteLine();
SqlBulkCopy bulk = new SqlBulkCopy(constr2);
bulk.DestinationTableName = "xxxxxx";
bulk.WriteToServer(table);
Console.WriteLine()
sql
sql-server vb.net asked Nov 23 '18 at 12:49
user2302158user2302158
77210
add a comment |
0
I just tried to bulk insert data which results to system.out.memory exception was thrown Even though the Ram as 260 GB and SQL Standard Edition 2017. Records was around 1 million , please give me any solution with my below code
DataTable table = new DataTable();
// read the table structure from the database
SqlDataAdapter adapter = new SqlDataAdapter("select * from xxxxx", constr);
adapter.SelectCommand.CommandTimeout = 0;
adapter.Fill(table);
Console.WriteLine();
Console.WriteLine(table.Rows.Count);
Console.WriteLine(" Table End time..");
Console.WriteLine(DateTime.Now);
Console.WriteLine("end----)";
Console.WriteLine();
SqlBulkCopy bulk = new SqlBulkCopy(constr2);
bulk.DestinationTableName = "xxxxxx";
bulk.WriteToServer(table);
Console.WriteLine()
sql
sql-server vb.net asked Nov 23 '18 at 12:49
user2302158user2302158
77210
The title says that a timeout occurred and the question says that memory was exhausted. Maybe you should edit your question so that it doesn't contradict itself.
– jmcilhinney
Nov 23 '18 at 13:06
So, when you did some proper testing and started of fetching a small number of records and gradually increased that number until it failed, what was the largest number of records you could successfully retrieve?
– jmcilhinney
Nov 23 '18 at 13:30
The best way is to do it all through the database with database links. If not, your option would be to do it in batch of say 100,000 records.
– the_lotus
Nov 23 '18 at 13:37
There's no need to buffer all the rows in client memory like you're doing. Instead open a DataReader on the source query and pass that to SqlBulkCopy. SqlBulkCopy.WriteToServer(DbDataReader) docs.microsoft.com/en-us/dotnet/api/….
– David Browne - Microsoft
Nov 23 '18 at 14:28
The maximum rows I can fetch us around 585922 anything more than that results memory exception
– user2302158
Nov 24 '18 at 14:07
add a comment |
0
0
0
I just tried to bulk insert data which results to system.out.memory exception was thrown Even though the Ram as 260 GB and SQL Standard Edition 2017. Records was around 1 million , please give me any solution with my below code
DataTable table = new DataTable();
// read the table structure from the database
SqlDataAdapter adapter = new SqlDataAdapter("select * from xxxxx", constr);
adapter.SelectCommand.CommandTimeout = 0;
adapter.Fill(table);
Console.WriteLine();
Console.WriteLine(table.Rows.Count);
Console.WriteLine(" Table End time..");
Console.WriteLine(DateTime.Now);
Console.WriteLine("end----)";
Console.WriteLine();
SqlBulkCopy bulk = new SqlBulkCopy(constr2);
bulk.DestinationTableName = "xxxxxx";
bulk.WriteToServer(table);
Console.WriteLine()
sql
sql-server vb.net asked Nov 23 '18 at 12:49
user2302158user2302158
77210
I just tried to bulk insert data which results to system.out.memory exception was thrown Even though the Ram as 260 GB and SQL Standard Edition 2017. Records was around 1 million , please give me any solution with my below code
DataTable table = new DataTable();
// read the table structure from the database
SqlDataAdapter adapter = new SqlDataAdapter("select * from xxxxx", constr);
adapter.SelectCommand.CommandTimeout = 0;
adapter.Fill(table);
Console.WriteLine();
Console.WriteLine(table.Rows.Count);
Console.WriteLine(" Table End time..");
Console.WriteLine(DateTime.Now);
Console.WriteLine("end----)";
Console.WriteLine();
SqlBulkCopy bulk = new SqlBulkCopy(constr2);
bulk.DestinationTableName = "xxxxxx";
bulk.WriteToServer(table);
Console.WriteLine()
sql
sql-server vb.net sql
sql-server vb.net asked Nov 23 '18 at 12:49
user2302158user2302158
77210
asked Nov 23 '18 at 12:49
user2302158user2302158
77210
edited Nov 23 '18 at 13:12
user2302158
asked Nov 23 '18 at 12:49
user2302158user2302158
77210
asked Nov 23 '18 at 12:49
user2302158user2302158
77210
asked Nov 23 '18 at 12:49
user2302158user2302158
77210
77210
The title says that a timeout occurred and the question says that memory was exhausted. Maybe you should edit your question so that it doesn't contradict itself.
– jmcilhinney
Nov 23 '18 at 13:06
So, when you did some proper testing and started of fetching a small number of records and gradually increased that number until it failed, what was the largest number of records you could successfully retrieve?
– jmcilhinney
Nov 23 '18 at 13:30
The best way is to do it all through the database with database links. If not, your option would be to do it in batch of say 100,000 records.
– the_lotus
Nov 23 '18 at 13:37
There's no need to buffer all the rows in client memory like you're doing. Instead open a DataReader on the source query and pass that to SqlBulkCopy. SqlBulkCopy.WriteToServer(DbDataReader) docs.microsoft.com/en-us/dotnet/api/….
– David Browne - Microsoft
Nov 23 '18 at 14:28
The maximum rows I can fetch us around 585922 anything more than that results memory exception
– user2302158
Nov 24 '18 at 14:07
add a comment |
The title says that a timeout occurred and the question says that memory was exhausted. Maybe you should edit your question so that it doesn't contradict itself.
– jmcilhinney
Nov 23 '18 at 13:06
So, when you did some proper testing and started of fetching a small number of records and gradually increased that number until it failed, what was the largest number of records you could successfully retrieve?
– jmcilhinney
Nov 23 '18 at 13:30
The best way is to do it all through the database with database links. If not, your option would be to do it in batch of say 100,000 records.
– the_lotus
Nov 23 '18 at 13:37
There's no need to buffer all the rows in client memory like you're doing. Instead open a DataReader on the source query and pass that to SqlBulkCopy. SqlBulkCopy.WriteToServer(DbDataReader) docs.microsoft.com/en-us/dotnet/api/….
– David Browne - Microsoft
Nov 23 '18 at 14:28
The maximum rows I can fetch us around 585922 anything more than that results memory exception
– user2302158
Nov 24 '18 at 14:07
The title says that a timeout occurred and the question says that memory was exhausted. Maybe you should edit your question so that it doesn't contradict itself.
– jmcilhinney
Nov 23 '18 at 13:06
The title says that a timeout occurred and the question says that memory was exhausted. Maybe you should edit your question so that it doesn't contradict itself.
– jmcilhinney
Nov 23 '18 at 13:06
So, when you did some proper testing and started of fetching a small number of records and gradually increased that number until it failed, what was the largest number of records you could successfully retrieve?
– jmcilhinney
Nov 23 '18 at 13:30
So, when you did some proper testing and started of fetching a small number of records and gradually increased that number until it failed, what was the largest number of records you could successfully retrieve?
– jmcilhinney
Nov 23 '18 at 13:30
The best way is to do it all through the database with database links. If not, your option would be to do it in batch of say 100,000 records.
– the_lotus
Nov 23 '18 at 13:37
The best way is to do it all through the database with database links. If not, your option would be to do it in batch of say 100,000 records.
– the_lotus
Nov 23 '18 at 13:37
There's no need to buffer all the rows in client memory like you're doing. Instead open a DataReader on the source query and pass that to SqlBulkCopy. SqlBulkCopy.WriteToServer(DbDataReader) docs.microsoft.com/en-us/dotnet/api/….
– David Browne - Microsoft
Nov 23 '18 at 14:28
There's no need to buffer all the rows in client memory like you're doing. Instead open a DataReader on the source query and pass that to SqlBulkCopy. SqlBulkCopy.WriteToServer(DbDataReader) docs.microsoft.com/en-us/dotnet/api/….
– David Browne - Microsoft
Nov 23 '18 at 14:28
The maximum rows I can fetch us around 585922 anything more than that results memory exception
– user2302158
Nov 24 '18 at 14:07
The maximum rows I can fetch us around 585922 anything more than that results memory exception
– user2302158
Nov 24 '18 at 14:07
add a comment |
1 Answer
1
active
oldest
votes
0
Maybe something like this?
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:your_pathBook1.xls;Extended Properties=Excel 8.0;")
ExcelConnection.Open()
Dim expr As String = "SELECT * FROM [Sheet1$]"
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()
Dim ConnString As String = "Data Source=DB_Name;Initial Catalog=Northwind.MDF;Trusted_Connection=True;"
SQLconn.ConnectionString = ConnString
SQLconn.Open()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
bulkCopy.DestinationTableName = "tblTest"
Try
objDR = objCmdSelect.ExecuteReader
bulkCopy.WriteToServer(objDR)
ExcelConnection.Close()
'objDR.Close()
SQLconn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Using
End Sub
End Class
answered Nov 24 '18 at 15:18
ryguy72ryguy72
4,1861819
Iam not using Excel here, I am just retrieving data from one database and inserting to another
– user2302158
Nov 26 '18 at 2:06
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53447042%2fmemory-exception-occurred-while-fetching-data-from-sql-for-bulk-insert%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Maybe something like this?
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:your_pathBook1.xls;Extended Properties=Excel 8.0;")
ExcelConnection.Open()
Dim expr As String = "SELECT * FROM [Sheet1$]"
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()
Dim ConnString As String = "Data Source=DB_Name;Initial Catalog=Northwind.MDF;Trusted_Connection=True;"
SQLconn.ConnectionString = ConnString
SQLconn.Open()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
bulkCopy.DestinationTableName = "tblTest"
Try
objDR = objCmdSelect.ExecuteReader
bulkCopy.WriteToServer(objDR)
ExcelConnection.Close()
'objDR.Close()
SQLconn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Using
End Sub
End Class
answered Nov 24 '18 at 15:18
ryguy72ryguy72
4,1861819
Iam not using Excel here, I am just retrieving data from one database and inserting to another
– user2302158
Nov 26 '18 at 2:06
add a comment |
0
Maybe something like this?
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:your_pathBook1.xls;Extended Properties=Excel 8.0;")
ExcelConnection.Open()
Dim expr As String = "SELECT * FROM [Sheet1$]"
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()
Dim ConnString As String = "Data Source=DB_Name;Initial Catalog=Northwind.MDF;Trusted_Connection=True;"
SQLconn.ConnectionString = ConnString
SQLconn.Open()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
bulkCopy.DestinationTableName = "tblTest"
Try
objDR = objCmdSelect.ExecuteReader
bulkCopy.WriteToServer(objDR)
ExcelConnection.Close()
'objDR.Close()
SQLconn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Using
End Sub
End Class
answered Nov 24 '18 at 15:18
ryguy72ryguy72
4,1861819
Iam not using Excel here, I am just retrieving data from one database and inserting to another
– user2302158
Nov 26 '18 at 2:06
add a comment |
0
0
0
Maybe something like this?
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:your_pathBook1.xls;Extended Properties=Excel 8.0;")
ExcelConnection.Open()
Dim expr As String = "SELECT * FROM [Sheet1$]"
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()
Dim ConnString As String = "Data Source=DB_Name;Initial Catalog=Northwind.MDF;Trusted_Connection=True;"
SQLconn.ConnectionString = ConnString
SQLconn.Open()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
bulkCopy.DestinationTableName = "tblTest"
Try
objDR = objCmdSelect.ExecuteReader
bulkCopy.WriteToServer(objDR)
ExcelConnection.Close()
'objDR.Close()
SQLconn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Using
End Sub
End Class
answered Nov 24 '18 at 15:18
ryguy72ryguy72
4,1861819
Maybe something like this?
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:your_pathBook1.xls;Extended Properties=Excel 8.0;")
ExcelConnection.Open()
Dim expr As String = "SELECT * FROM [Sheet1$]"
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()
Dim ConnString As String = "Data Source=DB_Name;Initial Catalog=Northwind.MDF;Trusted_Connection=True;"
SQLconn.ConnectionString = ConnString
SQLconn.Open()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
bulkCopy.DestinationTableName = "tblTest"
Try
objDR = objCmdSelect.ExecuteReader
bulkCopy.WriteToServer(objDR)
ExcelConnection.Close()
'objDR.Close()
SQLconn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Using
End Sub
End Class
answered Nov 24 '18 at 15:18
ryguy72ryguy72
4,1861819
answered Nov 24 '18 at 15:18
ryguy72ryguy72
4,1861819
answered Nov 24 '18 at 15:18
ryguy72ryguy72
4,1861819
answered Nov 24 '18 at 15:18
ryguy72ryguy72
4,1861819
4,1861819
Iam not using Excel here, I am just retrieving data from one database and inserting to another
– user2302158
Nov 26 '18 at 2:06
add a comment |
Iam not using Excel here, I am just retrieving data from one database and inserting to another
– user2302158
Nov 26 '18 at 2:06
Iam not using Excel here, I am just retrieving data from one database and inserting to another
– user2302158
Nov 26 '18 at 2:06
Iam not using Excel here, I am just retrieving data from one database and inserting to another
– user2302158
Nov 26 '18 at 2:06
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53447042%2fmemory-exception-occurred-while-fetching-data-from-sql-for-bulk-insert%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
The title says that a timeout occurred and the question says that memory was exhausted. Maybe you should edit your question so that it doesn't contradict itself.
– jmcilhinney
Nov 23 '18 at 13:06
So, when you did some proper testing and started of fetching a small number of records and gradually increased that number until it failed, what was the largest number of records you could successfully retrieve?
– jmcilhinney
Nov 23 '18 at 13:30
The best way is to do it all through the database with database links. If not, your option would be to do it in batch of say 100,000 records.
– the_lotus
Nov 23 '18 at 13:37
There's no need to buffer all the rows in client memory like you're doing. Instead open a DataReader on the source query and pass that to SqlBulkCopy. SqlBulkCopy.WriteToServer(DbDataReader) docs.microsoft.com/en-us/dotnet/api/….
– David Browne - Microsoft
Nov 23 '18 at 14:28
The maximum rows I can fetch us around 585922 anything more than that results memory exception
– user2302158
Nov 24 '18 at 14:07