I am exporting xml data to sql server 2008 database.
.net code: How to pass XML from C# to a stored procedure in SQL Server 2008?
Query to insert: Querying a xml in sql server 2008
Everthing is working fine if the file size is small. Now the xml file size is above 5mb. It showing timeout error. How to resolve this.
Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.
Gee开发者_如何学Ctha.
try to use SqlBulkCopy Class
: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
this will be a better option if the size of data is more.it will insert GBs of data in fraction of seconds.
For solving your error try this: SqlCommand.CommandTimeout
You can change the timeout of the command, for example:
command.CommandTimeout = 3600;
The value is measured in seconds.
However, you may need to alter the XML import SQL. You can often get better performance by using the OPENXML
clause instead of the nodes
XML data type method. See the documentation for information and examples about OPENXML
.
精彩评论