can't retrieve data using app

Hi,
I have an application that can only sometimes retrieve data via the
application for a certain process. This option has been working correctly for
a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
against the tables in question a few weeks back and the problem disappeared.
However, the problem has now reoccured.
Does anyone have any ideas regarding the nature of the problem and how it
can be resolved?
Extremely difficult to give suggestions without more information even hands-on. Perhaps the
application has a time-out? Did you get an error message or incorrect information? Perhaps there is
a time-out and no error is presented back to the user?
Another thing you can do is to capture the SQL statement submitted by the application with Profiler
and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
problem is in the application and you need to talk to the app vendor/developer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> Hi,
> I have an application that can only sometimes retrieve data via the
> application for a certain process. This option has been working correctly for
> a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> against the tables in question a few weeks back and the problem disappeared.
> However, the problem has now reoccured.
> Does anyone have any ideas regarding the nature of the problem and how it
> can be resolved?
>
|||The error doesn't occur using sql analyzer.
I'm not sure that this would be a time out issue, as other reports
retrieving a lot more data are working correctly.
Please find the error below:
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.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at expert.ExpertProc.PhysicalDataRead()
"Tibor Karaszi" wrote:

> Extremely difficult to give suggestions without more information even hands-on. Perhaps the
> application has a time-out? Did you get an error message or incorrect information? Perhaps there is
> a time-out and no error is presented back to the user?
> Another thing you can do is to capture the SQL statement submitted by the application with Profiler
> and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
> problem is in the application and you need to talk to the app vendor/developer.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>
|||The error message clearly states:
"The timeout period elapsed prior to completion of the operation or the server is not responding."
You have to investigate why it takes a long time sometimes and sometimes not. It can be blocking
issues (use Current Activity, sp_who, sp_who2, sp_lock etc to investigate), or it might be different
execution plans (use Profiler to catch the execution plans so you can compare).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> 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.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
|||The error message clearly indicates a timeout exception. The default
SqlCommand timeout is 30 seconds but this can be overridden by the
application. The default Query Analyzer query timeout is unlimited so,
assuming the query runs over 30 seconds, that explains why you don't get the
error in Query Analyzer.
A number of factors can contribute to a timeout. These include sub-optimal
query plan, blocking and server workload. If you've ruled out blocking and
an overtaxed server, I suggest you examine the plan of the problem query.
Perhaps additional indexes may help or existing indexes need to be rebuilt
using DBCC DBREINDEX. If you still have the problem after tuning, you'll
need to increase the application command timeout.
Hope this helps.
Dan Guzman
SQL Server MVP
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> 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.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote: