Timeout expired. Problem in Exporting xml to sql server 2008 data table - .net

Question

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.

Geetha.

Answer 1

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

Answer 2

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.