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:

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 fo
r
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 inform
ation? 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 ap
plication 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/develop
er.
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 disappeare
d.
> 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(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object 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 hand
s-on. Perhaps the
> application has a time-out? Did you get an error message or incorrect info
rmation? 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 fr
om Query Analyzer the
> problem is in the application and you need to talk to the app vendor/devel
oper.
> --
> 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 ser
ver 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 compar
e).
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 respondi
ng.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavi
or)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comm
andBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object 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(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comm
andBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object 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:
>

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(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object 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...
> > 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 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...
> 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(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object 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...
>> > 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 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...
> 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(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object 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...
>> > 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?
>> >
>>

Cant reference logical table within cursor

Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this...

DECLARE check_contact_fields CURSOR
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
set @.SQL = 'select ' + @.column_name + ' into ##DeletedData from deleted'
exec sp_executesql @.SQL
set @.SQL = 'select ' + @.column_name + ' into ##InsertedData from inserted'
exec sp_executesql @.SQL
if (select * from ##DeletedData) <> (select * from ##InsertedData)
select * from ##InsertedData
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

drop table ##DeletedData
drop table ##InsertedData

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'deleted'.

TIAThe error is returned correctly, because logical table deleted is not known within the execution context of dynamic SQL.|||That's what I thought. So, how can I accomplish what I'm trying to do? I can't think of another way to do it without dynamic SQL.

Thanks again|||Maybe you can tell us what you are trying to do...I can't figure it out...

AND a cursor in a trigger is never a good idea....|||Brett -

I need to record the old and new data for a set of contact related fields when a contact record is updated. The set of fields that need to be monitored are stored in the contacts_fields table (because the contact fields are dynamic). So, my thinking was that I could create an update trigger to check those columns in the contacts_fields table that are in the deleted row against the inserted row. If any of the column_name values changed, I would then only record that *specific* column that was modified.

Thanks|||Why bother...just update the whole row...what difference does it make...

What's the DDL of the table look like?

What do you mean by dynamic?|||DDL ("master" audit table):
/****** Object: Table [dbo].[contacts_history] Script Date: 8/4/2004 1:41:36 PM ******/
CREATE TABLE [contacts_history] (
[contact_history_id] [int] IDENTITY (1, 1) NOT NULL ,
[contact_id] [int] NULL ,
[who_created] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[who_modified] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fields_lastUpdated] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history] PRIMARY KEY CLUSTERED
(
[contact_history_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

DDL (data capture table):
/****** Object: Table [dbo].[contacts_history_values] Script Date: 8/4/2004 1:41:10 PM ******/
CREATE TABLE [contacts_history_values] (
[contacts_history_value_ID] [int] IDENTITY (1, 1) NOT NULL ,
[contact_history_id] [int] NULL ,
[field_id] [int] NULL ,
[old_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[current_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history_values] PRIMARY KEY CLUSTERED
(
[contacts_history_value_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Dynamic in the sense that the fields in the "contacts" table can change on the fly (there's a field management area).

Thanks|||holy crap...

You basically have "vertical" rows....

Is this a third party product, did you build this or did you inherit this

What does the concat table DDL look like?

And what do you mean the "fields" (you mean columns right) can change on the fly.

Do you mean the data or the table structure?

What does "if any of the column_name values have changed" mean?

I'm sorry...I just can't see it yet...|||Yes, the columns on the contacts table can change on the fly (but its PK, contact_id, would always exist).

I mean the table structure.

What does "if any of the column_name values have changed" mean?
--> I'm not sure if you saw what the check_contact_fields cursor looks like, but what I mean by this is that since I store all the columns that need to be audited in the contacts_fields table I could "loop" over these columns (hence the cursor) and check each column_name value (these are really just the column names) against the deleted and inserted tables. If the data for any of these columns changed I would then write the changes (along with the old data) to the contacts_history_values table.

Hope this makes sense.|||ok...I understand...not that I want to.....

Is the id 1 per "table"

Are you making up and configuring "tables" for end users through a web application?

I'd be very interested in the reason that your doing this...

As for your solution...I gotta ponder this one.....

Also because there is no ordinal position in your table, and the order of rows of data in a database is meangless...how do you know what column name has changed?

It doesn't work that way.

You column name is essentially a key in this process...actually a composite key along with contactId

So actually trying to udate the key...which to me is a bad thing...

You should go for a logical update...which mean you do no updates but rather a DELETE first, then an INSERT...

That should be rather straight forward in a trigger...|||Sorry, but I don't follow you.

Have you looked at the DDL's I posted? There's a column called "field_id" (in the contacts_history_values table) that's a FK for the contacts_fields table.
This allows the app to keep track of the column that was modified.

I think I'm more confused now... =\|||Ok good...

I think I got confused...

You want to audit the change correct?

Just do a join between inserted and deleted on contactId and fieldid and insert that results to your audit tables from inside a trigger

What happens for a new "field" or a field that has been "deleted"

You still haven't told me who built this...|||I think I found the right design but I'm having issues executing it.

DECLARE check_contact_fields CURSOR LOCAL
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
if (select '+@.column_name+' from inserted) <> (select '+@.column_name+' from deleted)
print @.column_name + ' boo'
else if (select '+@.column_name+' from inserted) = (select '+@.column_name+' from deleted)
print 'identical values'
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

This always returns "identical values" even though some of the data has changed.

Any ideas?|||What table is the Trigger on?

I don't think you want or need the cursor|||I Think you want this...can't test without the other tables DDL

CREATE TRIGGER Contact_Fields_TR1 ON Contact_Fields
AS
BEGIN
INSERT INTO contacts_history_values(contact_history_id, field_id, old_data, current_data)
SELECT i.ContactId, i.field_Id, o.column_name, i.column_name
FROM inserted i INNER JOIN deleted o
WHERE i.Contact_Id = o.Contact_Id AND i.Field_Id = o.Field_ID
END

Can't pull the subscription

The process could not read file
'd:\sql_data\MSSQL\ReplData\unc\...\20050324084140 \snapshot.pre' due to OS
error 3.
What is going on?
Wack
This message will self destruct.
Looks like you set your snapshot folder to be a local path ("d:\...")
instead of a UNC path (\\mybox\d$\sql_data\MSSQL\ReplData). You specify the
default snapshot folder when you configure your publisher/distributor but
can override this value when you create a publication.
"Ray Lewis" <dav5489@.hotmail.com> wrote in message
news:enypHuHMFHA.3420@.tk2msftngp13.phx.gbl...
> The process could not read file
> 'd:\sql_data\MSSQL\ReplData\unc\...\20050324084140 \snapshot.pre' due to OS
> error 3.
> What is going on?
> --
> Wack
> --
> This message will self destruct.
>
|||The snapshots are going to the correct place, I.E, Server X = Distributor,
Server Y = Publisher, Server Z = Subscriber.
Server Y takes a snapshot of the db and stores it on Server X. Server Z,
however, can't get to the snapshot to replicate.
Wack
This message will self destruct.
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uJ4nJ7JMFHA.1300@.TK2MSFTNGP10.phx.gbl...
> Looks like you set your snapshot folder to be a local path ("d:\...")
> instead of a UNC path (\\mybox\d$\sql_data\MSSQL\ReplData). You specify
the[vbcol=seagreen]
> default snapshot folder when you configure your publisher/distributor but
> can override this value when you create a publication.
> "Ray Lewis" <dav5489@.hotmail.com> wrote in message
> news:enypHuHMFHA.3420@.tk2msftngp13.phx.gbl...
OS
>

Can't Process cube created with ASCMD - XMLA script -

Hi,

I have an XMLA-script that I use for creating my cube. Creating the cube with the tool - ascmd - is not an issue but when I try to process the cube, I get the following message:
OLE DB error: OLE DB or ODBC error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.; 28000.

Strange because when I open the management studio -> look at my data source, all the correct information is there (server, user name, etc.), except for the password field in impersonation account editor, which is blanco.

The only way to process my AS DB is to open the data source editor and retype my password in the impersonation account editor. Once I've done that, I can process.

But as I want to automate the cube processing, I have a problem...

Anyone?

greets,
-Tom

Here's the solution:

<ImpersonationInfo>
<ImpersonationMode>ImpersonateAccount</ImpersonationMode>
<Account>mUserName</Account>
<Password>mPassword</Password>
</ImpersonationInfo>

I just had to add the password tag in my xmla-script...

This seems to be a bug, because while I was creating the cube in VS, I've set the option 'Remove passwords' in project properties to false.

Now the only thing that really bothers me is the fact that I have to put passwords in a script file (available to anyone). Anyone has an idea?

-Tom

Cant load up Reporting Services

Hi there
Im a newbie to Reporting Services and am trying it out on SQL Server 2005
I went through the installation process for Reporting Services and installed
a sample database from the Microsoft Press Book on Reporting Services.
I tried to navigate to the following location :
http://localhost/Reports/Pages/Folder.aspx
but i get the following error message
The report server cannot open a connection to the report
server database. A connection to the database is required for all requests
and processing. (rsReportServerDatabaseUnavailable) Get Online Help Login
failed for user 'ReportServer2005'. The user is not associated with a
trusted SQL Server connection.
Ive set up ReportServer2005 as a Login on SQL Server and its been set
up as a Windows login accout.
Thanks
ChrisOn May 3, 3:26 pm, "Chris Asaipillai"
<chris.asaipil...@.btinternet.com> wrote:
> Hi there
> Im a newbie to Reporting Services and am trying it out on SQL Server 2005
> I went through the installation process for Reporting Services and installed
> a sample database from the Microsoft Press Book on Reporting Services.
> I tried to navigate to the following location :
> http://localhost/Reports/Pages/Folder.aspx
> but i get the following error message
> The report server cannot open a connection to the report
> server database. A connection to the database is required for all requests
> and processing. (rsReportServerDatabaseUnavailable) Get Online Help Login
> failed for user 'ReportServer2005'. The user is not associated with a
> trusted SQL Server connection.
> Ive set up ReportServer2005 as a Login on SQL Server and its been set
> up as a Windows login accout.
> Thanks
> Chris
I've read somewhere that the User ID and Password for SSRS might be
locked. You might want to try unlocking it and restarting IIS (via
iisreset at the command prompt). Also, check the RSConfiguration file.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

can't kill process!

Strange thing. I have a spid that says the process is
"select * from user-table1". The spid is in "sleeping"
status. I can't kill the process and I have tried 20
times! It won't go away. I have stopped and started the
Sql Server services. I have rebooted the Sql Server box.
Its always there and it is causing probelms with my DB
Maint jobs. How can I get rid of the spid if Kill does
not work?
Thanks,
Brianbriang (anonymous@.discussions.microsoft.com) writes:
> Strange thing. I have a spid that says the process is
> "select * from user-table1". The spid is in "sleeping"
> status. I can't kill the process and I have tried 20
> times! It won't go away. I have stopped and started the
> Sql Server services. I have rebooted the Sql Server box.
> Its always there and it is causing probelms with my DB
> Maint jobs. How can I get rid of the spid if Kill does
> not work?
If you stop and start SQL Server, and the process is still there,
this indicates that the client program keeps reconnecting and
submits the query over and over again. This should be visible
in sysprocesses by the column last_batch always being a recent
point in time.
So you would somehow have to find that client and see what it is up to.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Run sp_who2 from Query Analyser. Located the spid in question and retrieve
the HostName, Login and ProgramName from the results. This will give you vi
tal clues as to what the culprit is. eg A service account on a particular wo
rkstation or server running
say a .NET application or component. If you can't locate the "application"
in question take this information to one of your network administrators (who
should be able to solve this one for you).

can't kill process!

Strange thing. I have a spid that says the process is
"select * from user-table1". The spid is in "sleeping"
status. I can't kill the process and I have tried 20
times! It won't go away. I have stopped and started the
Sql Server services. I have rebooted the Sql Server box.
Its always there and it is causing probelms with my DB
Maint jobs. How can I get rid of the spid if Kill does
not work?
Thanks,
Brianbriang (anonymous@.discussions.microsoft.com) writes:
> Strange thing. I have a spid that says the process is
> "select * from user-table1". The spid is in "sleeping"
> status. I can't kill the process and I have tried 20
> times! It won't go away. I have stopped and started the
> Sql Server services. I have rebooted the Sql Server box.
> Its always there and it is causing probelms with my DB
> Maint jobs. How can I get rid of the spid if Kill does
> not work?
If you stop and start SQL Server, and the process is still there,
this indicates that the client program keeps reconnecting and
submits the query over and over again. This should be visible
in sysprocesses by the column last_batch always being a recent
point in time.
So you would somehow have to find that client and see what it is up to.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Run sp_who2 from Query Analyser. Located the spid in question and retrieve the HostName, Login and ProgramName from the results. This will give you vital clues as to what the culprit is. eg A service account on a particular workstation or server running say a .NET application or component. If you can't locate the "application" in question take this information to one of your network administrators (who should be able to solve this one for you).

Can't Kill Process

We unfortunately found out that our databases were not being backed up for 95
days on one of our servers because the backup job got "stuck" on one of the
databases. I don't know why. I stopped the job but now the backups will not
work because the backup job from May is still "in process" on that one
database.
I tried killing the process but it won't work because there is "a rollback
in process". I rebooted the server but that did not help.
Does anyone have any suggestions on how I can kill this process on this one
database?
Thanks,
David McCarterFYI -
A rollback takes AT LEAST the same amount of time as it did to perform
whatever operations a transaction was doing. So, if a process has been
running for 1 hour and you decide to roll it back, then it would take at
least another hour to undo whatever it had already done.
"dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
> We unfortunately found out that our databases were not being backed up for
> 95
> days on one of our servers because the backup job got "stuck" on one of
> the
> databases. I don't know why. I stopped the job but now the backups will
> not
> work because the backup job from May is still "in process" on that one
> database.
> I tried killing the process but it won't work because there is "a
> rollback
> in process". I rebooted the server but that did not help.
> Does anyone have any suggestions on how I can kill this process on this
> one
> database?
> Thanks,
> David McCarter|||So are you saying the rollback is going to take 95 days? That's how long
the process was running before someone noticed it.
"The DBA" wrote:
> FYI -
> A rollback takes AT LEAST the same amount of time as it did to perform
> whatever operations a transaction was doing. So, if a process has been
> running for 1 hour and you decide to roll it back, then it would take at
> least another hour to undo whatever it had already done.
> "dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
> news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
> > We unfortunately found out that our databases were not being backed up for
> > 95
> > days on one of our servers because the backup job got "stuck" on one of
> > the
> > databases. I don't know why. I stopped the job but now the backups will
> > not
> > work because the backup job from May is still "in process" on that one
> > database.
> >
> > I tried killing the process but it won't work because there is "a
> > rollback
> > in process". I rebooted the server but that did not help.
> >
> > Does anyone have any suggestions on how I can kill this process on this
> > one
> > database?
> >
> > Thanks,
> > David McCarter
>

Can't Kill Process

We unfortunately found out that our databases were not being backed up for 95
days on one of our servers because the backup job got "stuck" on one of the
databases. I don't know why. I stopped the job but now the backups will not
work because the backup job from May is still "in process" on that one
database.
I tried killing the process but it won't work because there is "a rollback
in process". I rebooted the server but that did not help.
Does anyone have any suggestions on how I can kill this process on this one
database?
Thanks,
David McCarter
FYI -
A rollback takes AT LEAST the same amount of time as it did to perform
whatever operations a transaction was doing. So, if a process has been
running for 1 hour and you decide to roll it back, then it would take at
least another hour to undo whatever it had already done.
"dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
> We unfortunately found out that our databases were not being backed up for
> 95
> days on one of our servers because the backup job got "stuck" on one of
> the
> databases. I don't know why. I stopped the job but now the backups will
> not
> work because the backup job from May is still "in process" on that one
> database.
> I tried killing the process but it won't work because there is "a
> rollback
> in process". I rebooted the server but that did not help.
> Does anyone have any suggestions on how I can kill this process on this
> one
> database?
> Thanks,
> David McCarter
|||So are you saying the rollback is going to take 95 days? That's how long
the process was running before someone noticed it.
"The DBA" wrote:

> FYI -
> A rollback takes AT LEAST the same amount of time as it did to perform
> whatever operations a transaction was doing. So, if a process has been
> running for 1 hour and you decide to roll it back, then it would take at
> least another hour to undo whatever it had already done.
> "dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
> news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
>

Can't Kill Process

We unfortunately found out that our databases were not being backed up for 9
5
days on one of our servers because the backup job got "stuck" on one of the
databases. I don't know why. I stopped the job but now the backups will not
work because the backup job from May is still "in process" on that one
database.
I tried killing the process but it won't work because there is "a rollback
in process". I rebooted the server but that did not help.
Does anyone have any suggestions on how I can kill this process on this one
database?
Thanks,
David McCarterFYI -
A rollback takes AT LEAST the same amount of time as it did to perform
whatever operations a transaction was doing. So, if a process has been
running for 1 hour and you decide to roll it back, then it would take at
least another hour to undo whatever it had already done.
"dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
> We unfortunately found out that our databases were not being backed up for
> 95
> days on one of our servers because the backup job got "stuck" on one of
> the
> databases. I don't know why. I stopped the job but now the backups will
> not
> work because the backup job from May is still "in process" on that one
> database.
> I tried killing the process but it won't work because there is "a
> rollback
> in process". I rebooted the server but that did not help.
> Does anyone have any suggestions on how I can kill this process on this
> one
> database?
> Thanks,
> David McCarter|||So are you saying the rollback is going to take 95 days? That's how long
the process was running before someone noticed it.
"The DBA" wrote:

> FYI -
> A rollback takes AT LEAST the same amount of time as it did to perform
> whatever operations a transaction was doing. So, if a process has been
> running for 1 hour and you decide to roll it back, then it would take at
> least another hour to undo whatever it had already done.
> "dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
> news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
>

Cant Kill Process

Hi All,

I have a table called Test1 on a MS SQL 7 Database. Wes ran an insert on the table however it is now locking the table, so we are unable to do anything with the table.

We have tried to kill the process through both Enterprise Manager as well as Query Ananlyser however it still appears. If we try to drop the table it just sits there trying to complete this with no luck.

Is there anyway possible to be able to kill this??

Thanks
AnthonyROLLBACK can sometimes take hours. Try stop and restart MSSQLSERVER service.

Can't get MSDE installed

I just can't get MSDE installed on my machine. It hangs midway through
the process. If I kill setupre.exe the setup continues but just before
it's done there's an error "Setup failed to configure the server", and
it rolls back. I've stopped the services and processes and read the
readme very carefully. I've removed the previous version and removed
the registry entries and tried to do a clean install. Nothing I'm
finding on Google is helping. I have xp sp2 and the current mdac.
Suggestions? The log files are below.
http://jcosby.com/logs.zip
A repair install of MDAC finally did it.