May 30, 2005 at 12:51 pm
Hello all,
I set up a stored procedure to import data from an xml file to a database. The following is my code in vb.net that execute the stored precedure and write to the database:
Dim
SQLDataset As New DataSet("root")
Dim DBCommand As SqlCommand = New SqlCommand
Dim tbl As DataTable = SQLDataset.Tables(databaseA)
Dim sb As StringBuilder = New StringBuilder
Dim sw As StringWriter = New StringWriter(sb)
DBConnection.Open()
Dim DBAdapter As New SqlDataAdapter(Select * from databaseB, DBConnection)
DBAdapter.Fill(SQLDataset)
SQLDataset.WriteXml(sw, XmlWriteMode.WriteSchema)
DBCommand.Connection = DBConnection
DBCommand.CommandType = CommandType.StoredProcedure
DBCommand.CommandText = My_stored_procedure
DBCommand.Parameters.Add(
New SqlParameter("@XMLFile", System.Data.SqlDbType.NText))
DBCommand.Parameters(0).Value = sb.ToString()
DBCommand.ExecuteNonQuery()
DBConnection.Close()
Catch ex As Exception
Console.Write(ex)
DBConnection.Close()
End Try
The problem is, the above work perfectly if I specify a selection criterion with a 'where' clause in my SQL statement. If I want to import all 15000+ data from databaseB to the xml file then from xml file to databaseA, I got a timeout expired error. Any suggestion on how to improve this? Thanks a bunch!!
May 30, 2005 at 11:04 pm
Just try setting commandtimeout property of the sqlcommand object to a large number and see.
May 31, 2005 at 12:13 am
Move your sp-validation to your vb-code and use SQLBULKCOPY (vb.net).
this is a "fast load" if you have ordered your data according to the clustered index of your table.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply