a trigger code

I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like

Insert into T2 (F1, F2, F3)

Select (F1, F2,Type)

From T1

Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?

If you want to determine type of trigger action from single trigger then see code below:

set nocount on
go
if object_id('tr_tbl') is not null
drop table tr_tbl
go
create table tr_tbl(i int)
go
create trigger tr_test on tr_tbl for insert, update, delete as
if exists(select * from inserted) and exists(select * from deleted)
print 'Update...' -- set @.type = 'U'
else
if exists(select * from inserted)
print 'Insert...' -- set @.type = 'I'
else
print 'Delete...' -- set @.type = 'D'

... your code
go

insert tr_tbl values(1)
update tr_tbl set i = i + 1
delete tr_tbl

go

drop table tr_tbl

go

But it might be easier writing separate triggers. And you shouldn't query the base table T1 directly since you will get all rows in the table and not just the rows affected by the DML. You need to use the inserted/deleted tables based on the trigger action.

|||

This helps a lot, thanks,

One question: since there are more than one user on the system will there be a case that insert virtual table might have more than one row?

|||Yes, you should always write your trigger code such that it handles multiple rows. This will also force you to use simple set of DMLs from trigger for the logic which is more efficient than a procedural approach. If you want to enforce only singleton operations via DML then you can do so within trigger by checking for @.@.ROWCOUNT and rolling back the transaction/ DML operation.sql

a trigger code

I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like

Insert into T2 (F1, F2, F3)

Select (F1, F2,Type)

From T1

Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?

you already have the code you need. you just need to put it in a trigger for which the syntax can be found in BOL.

A transport-level error has occurred when receiving results from the server

Hi all,

I

am trying to run a stored procedure which retreives 3 lakhs of records

and updates the data and moves few of those records to some tables.

Since the number of records are more , the time taken for the storede

procedure is around 30 minutes when i directly execute it in query

analyzer.

But I need to execute it from Visula Studio.Net 2005

(c#). Whole of the application contains only one form with a single

button.When I click on the button , this stored procure has to be

executed. But I am getting an error as shown below.

"A

transport-level error has occurred when receiving results from the

server. (provider: TCP Provider, error: 0 - The specified network name

is no longer available.)"

Database used is SqlServer 2000

How to solve this error?. Any ideas are really appreciated.

Thanks and Regards,
Sukanya.

The error occured during data operation and the remote server either temporarily offline or close connection due to invalid client operation or your the permission your client to access the remote server resources has been changed, etc.

So, to identify the problem, you need :

1) Assume you were making remote connection, ping <remoteserver>, telnet <remoteserver> <sqlport>, or net view \\<remoteserver> or see firewall setting on the remote server to check whether the network is still good to make sure remote server is still reachable, and contact your network administrator to fix those problems.

2) You can give a retry by running your client app see whether the problem went away.

3) If 1) and 2) passed, you might open sql profile to nail down which client operation to cause sql server terminate connection, and check server errorlog or application event log find out any clue.

If you were making local connection, it is probably reason 3).

HTH.

Ming.

|||Hi,

I just increased the "connect timeout" in connectionstring of app.config to 1 hour. This solved my problem.

Thanks,
Sukanya

a simple insert/update trigger

Hi I am looking to create a simple trigger for both UPDATES and INSERTS
where any change to tbl1 will be inserted/updated in tbl2.
The insert works okay, with values added to both tables, however when I
perform an update, it seems to add an extra row in tbl2.
Here's the code:
CREATE TABLE [dbo].[tbl1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Team] [varchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Team] [varchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TRIGGER [tri_DataTrans] ON [dbo].[tbl1]
FOR INSERT, UPDATE
AS
INSERT INTO tbl2([ID], [name], team)
SELECT ID, [name], team FROM inserted
UPDATE tbl2
SET [Name] = Inserted.[Name],
[Team] = Inserted.[Team]
FROM Inserted
WHERE [ID] = Inserted.[ID]
---
What am I doing wrong? do I need to use IF UPDATE()?
Thanks
qh75Why do you want to use a single trigger? You can certainly do that
(modify your INSERT to insert the row only if it doesn't already exist)
but since you want totally different actions in the case of UPDATE and
INSERT it will surely be more efficient to use two triggers instead of
one.
Secondly, your tables as posted have no keys at all. Apparently even
the ID isn't declared as unique (the IDENTITY property doesn't actually
guarantee uniqueness) and the other columns are nullable. Even if that
constrain exists on ID it is definitely not safe to assume that a row
would be assigned the same ID in both tables. On the other hand you
have included the ID in the INSERT, which will fail unless you turn
IDENTITY_INSERT ON, so I'm not clear if you intended to use IDENTITY in
the second table or not.
The solution is to declare natural keys on both tables (presumably Name
and/or Team) and make those column(s) NOT NULL. Then join the two
tables on that key rather than the IDENTITY.
David Portas
SQL Server MVP
--|||David Portas wrote:
> Why do you want to use a single trigger? You can certainly do that
> (modify your INSERT to insert the row only if it doesn't already
exist)
> but since you want totally different actions in the case of UPDATE
and
> INSERT it will surely be more efficient to use two triggers instead
of
> one.
I just thought it could be performed in one trigger, basically (without
getting into the identity stuff) I was looking for some logic I could
use in the one trigger to check for both inserts and both updates.

> Secondly, your tables as posted have no keys at all. Apparently even
> the ID isn't declared as unique (the IDENTITY property doesn't
actually
> guarantee uniqueness) and the other columns are nullable. Even if
that
> constrain exists on ID it is definitely not safe to assume that a row
> would be assigned the same ID in both tables. On the other hand you
> have included the ID in the INSERT, which will fail unless you turn
> IDENTITY_INSERT ON, so I'm not clear if you intended to use IDENTITY
in
> the second table or not.
I'll add a key to the tables and try that.
Thanks for the reply.
qh|||Anyhoo, this is what I came up with:
---
CREATE TRIGGER [tri_Update] ON [dbo].[tbl1]
FOR UPDATE
AS
IF UPDATE ([Team])
BEGIN
UPDATE tbl2
SET [Team] = Inserted.[Team]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
END
IF UPDATE ([Name])
BEGIN
UPDATE tbl2
SET [Name] = Inserted.[Name]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
END
----
CREATE TRIGGER [tri_DataTrans] ON [dbo].[tbl1]
FOR INSERT
AS
INSERT INTO tbl2([ID], [name], team)
SELECT ID, [name], team FROM inserted
----
If you know of a way of combining the two triggers into one (if indeed
it can be done) please let me know.
Cheers
qh

A Real Sql Server 2005 Bug! A Real Sql Server Bug!

A growing Sql Server 2005 database performs several hours of updates each
night. This particular region of code has run fine for over a year. Now we
are getting the folllowing message every few nights causing our processing to
abort:
Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
Internal Query Processor Error: The query processor encountered an unexpected
error during execution.
This is a simplified version of the query from line 150:
select
p.PeriodStartDate,
p.PeriodType,
p.ActivityUserNumber,
p.AppNumber,
max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
max( case when ActivityType = 'FUNDED' then 1 else 0 end )
from Activity a, xxxPeriod p
where a.ActivityDate >= p.PeriodStartDate
and a.ActivityDate < p.PeriodEndDate
and a.ActivityUserNumber = p.ActivityUserNumber
and a.AppNumber = p.AppNumber
and ActivityType in (select code from Lookup where SetName =
'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber
When this simplified query is run from the management studio, it fails about
10-20% of the time.
Some Observations:
- Sometimes we get a few records in the result set prior to the failure.
- This query works on our smaller development database.
- If we change the query in any of the following ways, the query works
(e.g., 15 attempts w/o an error):
- Removing the group by and max()
- Removing one or more max statements
- Hard-code the list of activity types in place of the sub-select from
Lookup table
Version:
Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
Help!
Mike
Mike wrote:
> A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now we
> are getting the folllowing message every few nights causing our processing to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an unexpected
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber
> When this simplified query is run from the management studio, it fails about
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>
I would suspect a TEMPDB problem. Lack of space? Autogrow timeout?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Mike, please contact Microsoft product support.
Thanks,
Leo
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:168FB0C7-AF8D-4722-B8EF-FC5A8D309F47@.microsoft.com...
>A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
> we
> are getting the folllowing message every few nights causing our processing
> to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an
> unexpected
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber,
> p.AppNumber
> When this simplified query is run from the management studio, it fails
> about
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>

A Real Sql Server 2005 Bug! A Real Sql Server Bug!

A growing Sql Server 2005 database performs several hours of updates each
night. This particular region of code has run fine for over a year. Now we
are getting the folllowing message every few nights causing our processing t
o
abort:
Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
Internal Query Processor Error: The query processor encountered an unexpecte
d
error during execution.
This is a simplified version of the query from line 150:
select
p.PeriodStartDate,
p.PeriodType,
p.ActivityUserNumber,
p.AppNumber,
max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
max( case when ActivityType = 'FUNDED' then 1 else 0 end )
from Activity a, xxxPeriod p
where a.ActivityDate >= p.PeriodStartDate
and a.ActivityDate < p.PeriodEndDate
and a.ActivityUserNumber = p.ActivityUserNumber
and a.AppNumber = p.AppNumber
and ActivityType in (select code from Lookup where SetName =
'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber
When this simplified query is run from the management studio, it fails about
10-20% of the time.
Some Observations:
- Sometimes we get a few records in the result set prior to the failure.
- This query works on our smaller development database.
- If we change the query in any of the following ways, the query works
(e.g., 15 attempts w/o an error):
- Removing the group by and max()
- Removing one or more max statements
- Hard-code the list of activity types in place of the sub-select from
Lookup table
Version:
Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
Help!
MikeMike wrote:
> A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
we
> are getting the folllowing message every few nights causing our processing
to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an unexpec
ted
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumb
er
> When this simplified query is run from the management studio, it fails abo
ut
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>
I would suspect a TEMPDB problem. Lack of space? Autogrow timeout?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Mike, please contact Microsoft product support.
Thanks,
Leo
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:168FB0C7-AF8D-4722-B8EF-FC5A8D309F47@.microsoft.com...
>A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
> we
> are getting the folllowing message every few nights causing our processing
> to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an
> unexpected
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber,
> p.AppNumber
> When this simplified query is run from the management studio, it fails
> about
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>

A question about execution plans

Hello, I'm using SQL 2000 with the latest updates.
I have a large table Call_Record (5 million rows) that has three indexes:
1. A clustered index on account_no ASC, date_start DESC
2. A non-clustered index on date_end DESC
3. A non-clustered index on call_record_id ASC
I'm querying it for failed calls in the last hour:
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
The problem is that the execution plan shows that it is using index 1 to
perform this query, whereas index 2 is clearly the best choice. If I
replace the call to GetUtcDate() with a literal date constant like so:
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE (c.date_end >= '2005/11/23')
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
then it does use index (2) as expected, and executes in a fraction of
the time. My question is, why does it pick the "incorrect" index for
the first query, and is there any way to force it to pick index (2)?
MikeMike
I tried to rewrite a little bit your SELECT
DBCC FREEPROCCACHE
GO
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE c.date_end >=dateadd(hour,-1,GetUtcDate()) and c.date_end <
dateadd(day,+1,GetUtcDate()) --replace with the date that is relevant for
the searching
(GetUtcDate() - (1.0/24.0)) )
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
Do you see now any changes in the execution plan , I'd put the CI on
date_end column since your criteria is based on range date seraching and CI
is probably a good choice for it, but you'll have to test it.
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:%23ibexQI8FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Hello, I'm using SQL 2000 with the latest updates.
> I have a large table Call_Record (5 million rows) that has three indexes:
> 1. A clustered index on account_no ASC, date_start DESC
> 2. A non-clustered index on date_end DESC
> 3. A non-clustered index on call_record_id ASC
> I'm querying it for failed calls in the last hour:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> The problem is that the execution plan shows that it is using index 1 to
> perform this query, whereas index 2 is clearly the best choice. If I
> replace the call to GetUtcDate() with a literal date constant like so:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= '2005/11/23')
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> then it does use index (2) as expected, and executes in a fraction of the
> time. My question is, why does it pick the "incorrect" index for the
> first query, and is there any way to force it to pick index (2)?
> Mike|||Mike Chamberlain (none@.hotmail.com) writes:
> I have a large table Call_Record (5 million rows) that has three indexes:
> 1. A clustered index on account_no ASC, date_start DESC
> 2. A non-clustered index on date_end DESC
> 3. A non-clustered index on call_record_id ASC
> I'm querying it for failed calls in the last hour:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> The problem is that the execution plan shows that it is using index 1 to
> perform this query, whereas index 2 is clearly the best choice. If I
> replace the call to GetUtcDate() with a literal date constant like so:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= '2005/11/23')
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> then it does use index (2) as expected, and executes in a fraction of
> the time. My question is, why does it pick the "incorrect" index for
> the first query, and is there any way to force it to pick index (2)?
When making the choice between scanning a clustered index, or using a
non-clustered index + bookmark lookup, the optimizer always have a
delicate choice. If the condition on the column in the NC-index hits
few rows is small, the NC index is good. But if the condition hits many
rows, the NC index is a lot worse than the table scan, as SQL Server
would have to access many data pages more than once.
To determine which to use, SQL Server makes estimates from statistics
saved for the table. When you put in a date literal, SQL Server can see
that the query will only hit a small number of rows, and thus the index
is good.
But for the first query, the problem is that getutcdate() is a non-
deterministic function, and thus will return different values each
time. I guess, therefore, the optimizer does not care about the
expression, but uses the clustered index instead. Since you have a
condition with >= there could potentially be many rows that are
hit in the condition.
In a situation like this an index hint may be a good idea:
FROM dbo.Call_Record c WITH (INDEX = DateEnd_ix)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

A problem when Critical updates are applied

Hi there,
I'm wondering who has the same problem when you applied the recent Critical
Updates for SQL2k5. I tried three times, one hour each, but all failed to
install it.
--
Ray
MCSE+Internet, MCDBA, MCPHi Ray
"Ray" wrote:
> Hi there,
> I'm wondering who has the same problem when you applied the recent Critical
> Updates for SQL2k5. I tried three times, one hour each, but all failed to
> install it.
> --
> Ray
> MCSE+Internet, MCDBA, MCP
>
Is the account that you are logged in as privileged enough?
On a production server you would not necessarily want to automatically
install updates.
What version of SQL Server are you on?
John|||That's SQL 2k5. The reason I tried to do it is because it cannot do it
automatically.
The server was trying to update every night, but failed. After I tried to
update three times manually, I gave up, removed it from the update list so
that it won't update automatically again. My account is in the
Administrators group.
--
Ray
MCSE+Internet, MCDBA, MCP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B63E7E00-36AA-4D75-98AC-8D38E1F41DD2@.microsoft.com...
> Hi Ray
> "Ray" wrote:
>> Hi there,
>> I'm wondering who has the same problem when you applied the recent
>> Critical
>> Updates for SQL2k5. I tried three times, one hour each, but all failed to
>> install it.
>> --
>> Ray
>> MCSE+Internet, MCDBA, MCP
> Is the account that you are logged in as privileged enough?
> On a production server you would not necessarily want to automatically
> install updates.
> What version of SQL Server are you on?
> John

A problem when Critical updates are applied

Hi there,
I'm wondering who has the same problem when you applied the recent Critical
Updates for SQL2k5. I tried three times, one hour each, but all failed to
install it.
Ray
MCSE+Internet, MCDBA, MCPHi Ray
"Ray" wrote:

> Hi there,
> I'm wondering who has the same problem when you applied the recent Critica
l
> Updates for SQL2k5. I tried three times, one hour each, but all failed to
> install it.
> --
> Ray
> MCSE+Internet, MCDBA, MCP
>
Is the account that you are logged in as privileged enough?
On a production server you would not necessarily want to automatically
install updates.
What version of SQL Server are you on?
John|||That's SQL 2k5. The reason I tried to do it is because it cannot do it
automatically.
The server was trying to update every night, but failed. After I tried to
update three times manually, I gave up, removed it from the update list so
that it won't update automatically again. My account is in the
Administrators group.
Ray
MCSE+Internet, MCDBA, MCP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B63E7E00-36AA-4D75-98AC-8D38E1F41DD2@.microsoft.com...
> Hi Ray
> "Ray" wrote:
>
> Is the account that you are logged in as privileged enough?
> On a production server you would not necessarily want to automatically
> install updates.
> What version of SQL Server are you on?
> John