Re: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

This could be caused by a transient deadly embrace or other locking issue. It might also be caused by someone using Reporting Services on the same system, or another service that hogs disk IO channels or consumes more than its share of CPU time. It could be something as simple as SQL Server building a new extent as new data pages are added.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Rick" <rfemmer@xxxxxxxxxxxxxxxxx> wrote in message news:%23Yt2HQxGIHA.6044@xxxxxxxxxxxxxxxxxxxxxxx
The command timeout is set to 120 and the connection timeout is 500, the command is executing a stored proc that inserts 1 record. The stored proc works as expected, it will return 'SUCCESS' on insert or the error message from the stored proc if it failed. As I said, It only happens randomly. And it happens on the _cn.open

Private _cn As System.Data.SqlClient.SqlConnection

_cn = New SqlConnection(connectionstring here)

strSQL.Append("EXEC usp_InsertTblLookupDataReference ")

strSQL.Append(PARAMETERS HERE)

If Not _cn.State = ConnectionState.Open Then

_cn.Open()

End If

Dim returnValue As String = ""

countParam = New SqlClient.SqlParameter("@insertSuccess", SqlDbType.NVarChar, 4000)

localSQL = New System.Data.SqlClient.SqlCommand(strSQL.ToString, _cn)

localSQL.CommandTimeout = 120

countParam.Direction = ParameterDirection.Output

localSQL.Parameters.Add(countParam)

localSQL.ExecuteNonQuery()

returnValue = localSQL.Parameters("@insertSuccess").Value.ToString

If returnValue.ToUpper <> "SUCCESS" Then

Throw New Exception("Error Inserting TblLookupDataReference record:" & returnValue & " - " & strSQL.ToString)

End If

If _cn.State = ConnectionState.Open Then

_cn.Close()

End If

"WenYuan Wang [MSFT]" <v-wywang@xxxxxxxxxxxxxxxxxxxx> wrote in message news:Z7kZ2QuGIHA.5176@xxxxxxxxxxxxxxxxxxxxxxxxx
Hello Rick,
Thanks for Norman's suggestion.

I noticed the exception was raised by SqlCommand.ExecuteNonQuery() method.
Stack Trace:
..
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Have you tried increasing the SqlCommand.Timeout property?
The default value of SqlCommand is 30 (30 seconds).
It seems this process need much more time to run. Maybe you should increase
the timeout of this command.

Hope this helps.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.




.

Relevant Pages

  • handling errors from stored procs
    ... SQL Server 2000 ... comprise the call to a stored proc ... the stored proc gets run...if so, it would be nice if the Access app ... - when stored proc encounters error, it returns that error message ...
    (comp.databases.ms-access)
  • Re: Reporting Services DISABLES DEFAULT Transaction Isolation Level!!!
    ... The fact is that the installation of Reporting ... Server when run pre- and post- Reporting Services install. ... It's like the Reporting Services team removed the read committed isolation ... I've repeated the same test twice, reinstalling SQL Server 2000 sp3a and the ...
    (microsoft.public.sqlserver.server)
  • Re: Retrieve error text from extended stored proc
    ... was only on first execution, the following executions, error number was 0. ... However, use OSQL with the -m-1 switch, and you will see that the RAISERROR ... Tibor Karaszi, SQL Server MVP ... > When I call the stored proc from Query Analyzer and force an error, ...
    (microsoft.public.sqlserver.programming)
  • Re: Project REAL webcast
    ... then just the SQL Server 2005 addins are registered. ... >> the server installation? ... >>> Services, Analysis Services, Reporting Services, Data Mining, etc. ... >>> BI SystemsTeam ...
    (microsoft.public.sqlserver.olap)
  • Re: best way to retrieve thousands of records.
    ... I understand you should have a chunky not chatty interface, ... i.e code up another stored proc that accepts ... > retrieve records from sql server. ...
    (microsoft.public.dotnet.languages.vb)
Loading