"jalie" <jalie@discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@microsoft.com...
> Hi,
>
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
>
> The statement is really simple and along the lines of the example below
>
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
>
> The stored procedure is executed by an application using ADO.
>
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
>
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
>
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ....also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem....
>
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
>
> This is driving me insane - Please help anyone?!
>