March 1, 2011 at 8:07 am
Hi,
Below is an error I received from a C# winfroms app SQLCLient that is basically reading and modifying sql server views and tables using command text and stored procs. It times out when trying to bulk copy to a view and I inherited this program from someone who told me it never timed out before and did not know why it may be happening.
Here is the error thrown:
Error Importing \\SAHQFS01\data\WellMed\DataRapRaf\SubmissionsV2\PHC\228\Response\RSP.FLAT.1336621, System.Data.SqlClient.SQLException: Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowRxceptionAndWarning(TdsParserStateObject stateObj)...
Any ideas, I am setting break points in the C# app but really hoping someone out there has had timeout issues like this that they resloved in the past.
I tried running it today and yesterday and various times of day and it times out in less that 2 minutes , I have run jobs against the server in the middle of the day that took 25 minutes. So I just do not know. I am looking everywhere but suggestions would help, this files needs to be processed by tomorrow latest. eeeks
Super Thanks in Advance
March 1, 2011 at 2:49 pm
Do you have a timeout setting on the connection string? If not, put one there. I'm pretty sure if you set it to zero it will be infinite. But, if you're hitting a distributed transaction, then you need to modify the timeout for distributed transaction in the Distributed Transaction Coordinator (DTC).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 1, 2011 at 4:04 pm
Thanks,
It is resolved.
There is a separate timeout property for bulk copies I added this line and ran fine w/o a time out: sbc.BulkCopyTimeout = 0; //adam
bgImportResponses.ReportProgress(1, "Copying text file to vwImport (Staging table)");
using (SqlBulkCopy sbc = new SqlBulkCopy(SubmissionManager.Properties.Settings.Default.DataRapRafConnectionString))
{
sbc.BulkCopyTimeout = 0; //adam
sbc.NotifyAfter = IMPORT_BATCH_SIZE;
sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);
sbc.ColumnMappings.Add(0, "Data");
sbc.DestinationTableName = "dbo.vwImport";
sbc.WriteToServer(dt);
}
March 1, 2011 at 5:26 pm
Bulk insert into a view? Why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply