Cursor vs Set - is it possible

I always try to use a SELECT and avoid CURSOR logic - whenever possible - but this time I can't even think of a way to SET-base this operation
I've got a table that stores dates of ATTENDANCE. Only non-present dates have rows for a person
ABSENT on 04/02/2004 gets a row for a person - ABSENT again on 04/07/2004 gets a row for a person.
I want to create a UDF that returns the "consecutive number" of ABSENT entries in this table for a person, based on a starting date and going backwards.
I could simply loop a working variable with a date and do a SELECT to see if a record exists - then decrement the date by one day and check again - that was the way we did it on the mainframe VAX we are converting from..
BTW - weekends and holidays are ignored - they don't break the CONSECUTIVE counter..
Any help would be greatly appreciated...Not sure what you want, really. but you may want to try a
subquery.
select count(*) from yourtable a
where exists(select * from yourtable b where
datediff(d,a.date,b.date)=1)
>--Original Message--
>I always try to use a SELECT and avoid CURSOR logic -
whenever possible - but this time I can't even think of a
way to SET-base this operation.
>I've got a table that stores dates of ATTENDANCE. Only
non-present dates have rows for a person.
>ABSENT on 04/02/2004 gets a row for a person - ABSENT
again on 04/07/2004 gets a row for a person.
>I want to create a UDF that returns the "consecutive
number" of ABSENT entries in this table for a person,
based on a starting date and going backwards.
>I could simply loop a working variable with a date and
do a SELECT to see if a record exists - then decrement
the date by one day and check again - that was the way we
did it on the mainframe VAX we are converting from...
>BTW - weekends and holidays are ignored - they don't
break the CONSECUTIVE counter...
>Any help would be greatly appreciated...
>.
>|||Create a calendar table with dates going back as far as you need and forward
as far as you'd like (this will obviously require some maintenence going
forward, but you can give yourself a nice buffer to start with). You can
have columns for weekend or holiday designation, or just a "WorkDay" bit
column to tell you if a day is a workday or not.
Once you have that taken care of, the rest is easy... Here's a little sample
you can play with... note that although this query works, it might be
possible to do something a bit more elegant (this is very much off the
cuff):
create table #calendar(workdate datetime)
go
insert #calendar values ('20040331')
insert #calendar values ('20040401')
insert #calendar values ('20040402')
insert #calendar values ('20040405')
insert #calendar values ('20040406')
go
create table #attendance(emp char(1), dateout datetime)
go
insert #attendance values ('a', '20040401')
insert #attendance values ('a', '20040405')
insert #attendance values ('b', '20040402')
insert #attendance values ('b', '20040405')
insert #attendance values ('b', '20040406')
go
declare @.emp char(1)
declare @.startdate datetime
set @.emp = 'b'
set @.startdate = '20040406'
select count(*)
from #attendance
where
#attendance.dateout >
(select max(workdate)
from #calendar
where workdate <= @.startdate
and not exists (select *
from #attendance
where dateout=workdate
and emp=@.emp)
)
and #attendance.dateout <= @.startdate
and #attendance.emp = @.emp
go
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:98175612-F7E3-48C3-A839-949A421AE0E0@.microsoft.com...
> I always try to use a SELECT and avoid CURSOR logic - whenever possible -
but this time I can't even think of a way to SET-base this operation.
> I've got a table that stores dates of ATTENDANCE. Only non-present dates
have rows for a person.
> ABSENT on 04/02/2004 gets a row for a person - ABSENT again on 04/07/2004
gets a row for a person.
> I want to create a UDF that returns the "consecutive number" of ABSENT
entries in this table for a person, based on a starting date and going
backwards.
> I could simply loop a working variable with a date and do a SELECT to see
if a record exists - then decrement the date by one day and check again -
that was the way we did it on the mainframe VAX we are converting from...
> BTW - weekends and holidays are ignored - they don't break the CONSECUTIVE
counter...
> Any help would be greatly appreciated...|||On Tue, 6 Apr 2004 12:36:05 -0700, Steve Z wrote:
>I always try to use a SELECT and avoid CURSOR logic - whenever possible - but this time I can't even think of a way to SET-base this operation.
>I've got a table that stores dates of ATTENDANCE. Only non-present dates have rows for a person.
>ABSENT on 04/02/2004 gets a row for a person - ABSENT again on 04/07/2004 gets a row for a person.
>I want to create a UDF that returns the "consecutive number" of ABSENT entries in this table for a person, based on a starting date and going backwards.
>I could simply loop a working variable with a date and do a SELECT to see if a record exists - then decrement the date by one day and check again - that was the way we did it on the mainframe VAX we are converting from...
>BTW - weekends and holidays are ignored - they don't break the CONSECUTIVE counter...
>Any help would be greatly appreciated...
You can do this set-based with the help of a calendar table, filled
with all dates except weekends and holidays. Or (even better because
of added flexibility) all dates and a work-day indication
It's hard to help you with the query since you don't provide DDL, so
I'll try what I can do with soome guesswork.
SELECT MIN(AbsentDate)
FROM Absencies
WHERE Person = @.PersonRequested
AND AbsentDate < @.StartingDate
AND NOT EXISTS
(SELECT *
FROM Calendar
WHERE CalendarDate BETWEEN Absencies.AbsentDate
AND @.StartingDate
AND KindOfDay NOT IN ('Holiday', 'Weekend')
AND NOT EXISTS
(SELECT *
FROM Absencies AS A2
WHERE A2.Person = @.PersonRequested
AND A2.AbsentDate = Calendar.CalendarDate))
(untested, due to lack of DDL)
Note - this query might also be written with outer queries instead of
not exists. If this works tooo slow, try if that's a better solution.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I've seen other postings for building temporary calendar tables - I thought that would be the direction the answers would go
But, imagine that most people do not have a consecutive absence. Meaning that a row exists for lets say 4/6/2004, but no row exists for 4/5/2004. If I want to know that the consec count = 0 for this person, wouldn't it be way (I mean really much, much) faster to simply do a DATEADD(dd,-1,@.AttDate) and then test the Attendance_T table for the existence of a record for the id and date (both part of primary key). If the record does not exist, return 0. All this in a simple little UDF - it does one I/O and knows to return 0
If I do get a record back - then simply do another DATEADD(dd,-1,@.AttDate) - do the check again. I know we rack up another I/O, but based on caching and the way the primary key is clustered - that data is probably already buffered
People really do not have consecutive attendance - that is the normal situation
The only small rub to what I've described above is that when I do the DATEADD(dd,-1,@.AttDate) I have to check that we do not have a weekend. Two ways I can do that - DATEPART each time, or get elegant and DATEPART the first date I start with and "remember" when I encounter Sat/Sun
On top of that rub, I have to check the @.AttDate to see that it does not exist in a "permanent" table of "holidays" that is maintained. That is one extra I/O.|||On Tue, 6 Apr 2004 13:56:06 -0700, Steve Z wrote:
>I've seen other postings for building temporary calendar tables - I thought that would be the direction the answers would go.
>But, imagine that most people do not have a consecutive absence. Meaning that a row exists for lets say 4/6/2004, but no row exists for 4/5/2004. If I want to know that the consec count = 0 for this person, wouldn't it be way (I mean really much, much) faster to simply do a DATEADD(dd,-1,@.AttDate) and then test the Attendance_T table for the existence of a record for the id and date (both part of primary key). If the record does not exist, return 0. All this in a simple little UDF - it does one I/O and knows to return 0.
>If I do get a record back - then simply do another DATEADD(dd,-1,@.AttDate) - do the check again. I know we rack up another I/O, but based on caching and the way the primary key is clustered - that data is probably already buffered.
That would mean you'd use a recursive UDF. I've hardly any experience
with UDF's, but if my memory is correct, this is possible.
>People really do not have consecutive attendance - that is the normal situation.
Reading between the lines, it looks as if you expect the set-based
solutions suggested by me and others won't perform as well.
Maybe you're right, maybe not. Depends on the amount of data and of
course on what indexes are present. The onyl way to be sure what
performs best in your situation is to test both versions.
>The only small rub to what I've described above is that when I do the DATEADD(dd,-1,@.AttDate) I have to check that we do not have a weekend. Two ways I can do that - DATEPART each time, or get elegant and DATEPART the first date I start with and "remember" when I encounter Sat/Sun.
Or don't use DATEADD, but instead SELECT MAX(MyDate) FROM Calendar
WHERE MyDate < @.CurrentDate
(Yes, there's that calendar again <g>)
>On top of that rub, I have to check the @.AttDate to see that it does not exist in a "permanent" table of "holidays" that is maintained. That is one extra I/O.
So you might as well check for weekends with the same I/O and spare
yourself the hassle of the complicated date calculations.
BTW, unless your data collection goes back to the Victorian Age, you
can expect the whole calendar table to be loaded in cache. All I/O to
that table will be logical I/O.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||No, I agree with Hugo. While your singleton function call sounds simple, I
truly believe that on many rows it will cost much more than you think
compared to an outer join against a calendar table. You can always try it
and compare, I suppose, but I would be shocked if the UDF performed
faster... especially if, as Hugo suggests, the calendar table is reasonably
small and doesn't include all of time.
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:CAACAFAD-6D94-434E-8D35-A15C6A920153@.microsoft.com...
> I've seen other postings for building temporary calendar tables - I
thought that would be the direction the answers would go.
> But, imagine that most people do not have a consecutive absence. Meaning
that a row exists for lets say 4/6/2004, but no row exists for 4/5/2004. If
I want to know that the consec count = 0 for this person, wouldn't it be way
(I mean really much, much) faster to simply do a DATEADD(dd,-1,@.AttDate) and
then test the Attendance_T table for the existence of a record for the id
and date (both part of primary key). If the record does not exist, return
0. All this in a simple little UDF - it does one I/O and knows to return 0.
> If I do get a record back - then simply do another
DATEADD(dd,-1,@.AttDate) - do the check again. I know we rack up another
I/O, but based on caching and the way the primary key is clustered - that
data is probably already buffered.
> People really do not have consecutive attendance - that is the normal
situation.
> The only small rub to what I've described above is that when I do the
DATEADD(dd,-1,@.AttDate) I have to check that we do not have a weekend. Two
ways I can do that - DATEPART each time, or get elegant and DATEPART the
first date I start with and "remember" when I encounter Sat/Sun.
> On top of that rub, I have to check the @.AttDate to see that it does not
exist in a "permanent" table of "holidays" that is maintained. That is one
extra I/O.|||I wasn't suggesting a recursive UDF - simply a WHILE loop. Being a mainframe programmer from the 1980's makes me leary of things like I/O and function calls in general. Each sunction call must frame-up memory - I would keep away from that..
If the original request for help was for a UDF that simply returned a true/false response as to whether a person was absent on a prior day, would that have changed suggested techniques?
Here is the code I just completed - it tests out perfectly against the old mainframe code. The Attendance_T table contains hundreds of thousands of rows on around 8000+ students. We usually want to use this function only on a recordset that returns kids absent in a given day (800 or so/divided by the number of schools in the district). The recordset returns in flat-out no time - even on my laptop test server..
CREATE FUNCTION dbo.GetConsAtt_F (@.Stuid int, @.AttDate datetime
RETURNS int AS
BEGI
Declare @.Cnt in
Declare @.GR in
Declare @.DM in
Set @.Cnt=
Set @.DM= While @.DM= Begi
Set @.AttDate=DateAdd(dd,-1,@.AttDate
If DatePart(dw,@.AttDate) in (1,7
Set @.DM= Else Set @.DM=(Select Sum(1) From Calendar_T Where CalDate=@.AttDate
En
Set @.GR=IsNull((Select Sum(1) From Attendance_T Where StuId=@.StuId and AttDate=@.AttDate),0
If @.GR<>
Begi
Set @.Cnt= While @.GR<>
Begi
Set @.DM= While @.DM= Begi
Set @.AttDate=DateAdd(dd,-1,@.AttDate
If DatePart(dw,@.AttDate) in (1,7
Set @.DM= Else Set @.DM=(Select Sum(1) From Calendar_T Where CalDate=@.AttDate
En
Set @.GR=IsNull((Select Sum(1) From Attendance_T Where StuId=@.StuId and AttDate=@.AttDate),0
If @.GR<>0 Set @.Cnt=@.Cnt+
End
En
RETURN @.Cn
END|||I mis-posted a reply to all this - I posted it above this
post (4/6 at 5:36 starting with "I wasn't suggesting...")
Could you please respond to the questions in there?
Thanks.
>--Original Message--
>No, I agree with Hugo. While your singleton function
call sounds simple, I
>truly believe that on many rows it will cost much more
than you think
>compared to an outer join against a calendar table. You
can always try it
>and compare, I suppose, but I would be shocked if the
UDF performed
>faster... especially if, as Hugo suggests, the calendar
table is reasonably
>small and doesn't include all of time.
>
>"Steve Z" <szlamany@.antarescomputing.com> wrote in
message
>news:CAACAFAD-6D94-434E-8D35-
A15C6A920153@.microsoft.com...
>> I've seen other postings for building temporary
calendar tables - I
>thought that would be the direction the answers would go.
>> But, imagine that most people do not have a
consecutive absence. Meaning
>that a row exists for lets say 4/6/2004, but no row
exists for 4/5/2004. If
>I want to know that the consec count = 0 for this
person, wouldn't it be way
>(I mean really much, much) faster to simply do a DATEADD
(dd,-1,@.AttDate) and
>then test the Attendance_T table for the existence of a
record for the id
>and date (both part of primary key). If the record does
not exist, return
>0. All this in a simple little UDF - it does one I/O
and knows to return 0.
>> If I do get a record back - then simply do another
>DATEADD(dd,-1,@.AttDate) - do the check again. I know we
rack up another
>I/O, but based on caching and the way the primary key is
clustered - that
>data is probably already buffered.
>> People really do not have consecutive attendance -
that is the normal
>situation.
>> The only small rub to what I've described above is
that when I do the
>DATEADD(dd,-1,@.AttDate) I have to check that we do not
have a weekend. Two
>ways I can do that - DATEPART each time, or get elegant
and DATEPART the
>first date I start with and "remember" when I encounter
Sat/Sun.
>> On top of that rub, I have to check the @.AttDate to
see that it does not
>exist in a "permanent" table of "holidays" that is
maintained. That is one
>extra I/O.
>
>.
>|||On Tue, 6 Apr 2004 17:36:05 -0700, Steve Z wrote:
>I wasn't suggesting a recursive UDF - simply a WHILE loop. Being a mainframe programmer from the 1980's makes me leary of things like I/O and function calls in general. Each sunction call must frame-up memory - I would keep away from that...
Hey, I started programming on mainframes in the 80's as well. Stupid
of me to start about recursion without thinking it over first (but
maybe that's because I never tend to gove much thought to solutions I
would never choose myself).
Doo keep in mind that SQL Server is not a mainframe and not from the
80's. Learn to think set-based instead of procedural. It can be very
hard to make the switch, but once you're there, you'll find it becomes
second nature.
>If the original request for help was for a UDF that simply returned a true/false response as to whether a person was absent on a prior day, would that have changed suggested techniques'
If it was only that: probably. If more background was given: probably
not. UDF's are a mixed blessing. They can be great - but they have to
be executed for each row in the result set (or in a -often bigger-
intermediate set if the UDF is called somewhere in the WHERE clause).
Not a biggie if the UDF only does calculation, but if the UDF reads
from a table, you may impact performance. If you can find a way to do
the same without the UDF, reading the table from the query instead of
hiding table access in a UDF, you give the query optimiser more
possible execution strategies to choose from.
>Here is the code I just completed - it tests out perfectly against the old mainframe code. The Attendance_T table contains hundreds of thousands of rows on around 8000+ students. We usually want to use this function only on a recordset that returns kids absent in a given day (800 or so/divided by the number of schools in the district). The recordset returns in flat-out no time - even on my laptop test server...
Well -- it does what you want and the execution is quick enough, so
this seems like a typical case of "if it works, don't fix it". Feel
free to try if my code gives better results if you want to know, or
leave it as it is. *If* you decide to do a performance test of both
versions, I'd like to know the results.
BTW, I didn't review your complete procedure, but I couldn't help but
notice this:
> Else Set @.DM=(Select Sum(1) From Calendar_T Where CalDate=@.AttDate)
Why do you use Sum(1) instead of the (more standard) Count(*)?
> Set @.GR=IsNull((Select Sum(1) From Attendance_T Where StuId=@.StuId and AttDate=@.AttDate),0)
Do you know that IsNull is non-standard proprietary syntax? You should
replace it with Coalesce - that function does everything that IsNull
does, plus it is more flexible, plus it is ANSI standard and thus more
portable and guaranteed to be supporteed in future version of SQL
Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the info..
We developed our own RDBMS proprietary database on VAX mainframes - lots of code in assembler - I always want to know the internals of what goes on. I've even used RALLY and SYBASE mainframe SQL tools..
The basic need here is to list the lets say 500 kids who are absent from school today - simple SELECT on the ATTENDANCE_T table - join in some demographic data - show the name, grade, homeroom, etc
We do all these queries in STORED PROCEDURES - that are called by our VB client tool
The old VAX report included this "number of consecutive days" absent figure. So, here I am with 500 rows from a table of 100,000 rows - all keyed by STUDENT ID+DATE OF ATTENDANCE. I couldn't imagine how to join or cross join or whatever would be possible to find out if the kids was absent the day before - I didn't think I wanted to go into a subquery. Past that, couldn't imagine going for number of days
I'm really curious though about impact - won't the SQL engine build the result set and then execute the UDF for each row
I can't really wrap my head around how I could "join" in a temporary table of weekends and holidays - would I do that in a subquery - wouldn't that execute for each row anyway
I got used to SUM(1) - it returns NULL if not records were found - I like that. Is there a cost difference? I would have thought the engine would do the same thing for both cases. When we run reports that total the number of kids in a class we do the following for TOTAL MALES, TOTAL FEMALES and TOTAL STUDENTS
Sum(Case When Gender='M' Then 1 else 0 End), Sum(Case When Gender<>'M' Then 1 else 0 End), Sum(1
I was unaware of ISNULL issues - will look into COALESCE - from what I see, it's exactly the same syntax - just you can have more than two arguments..
Thanks again...|||On Wed, 7 Apr 2004 15:01:06 -0700, Steve Z wrote:
>The old VAX report included this "number of consecutive days" absent figure. So, here I am with 500 rows from a table of 100,000 rows - all keyed by STUDENT ID+DATE OF ATTENDANCE. I couldn't imagine how to join or cross join or whatever would be possible to find out if the kids was absent the day before - I didn't think I wanted to go into a subquery. Past that, couldn't imagine going for number of days.
Subqueries are not "always bad". Correalted subqueries do tend to be
slower, though. From what I've heard, the query optimiser seems to be
better at joins than at subqueries. Most subqueries with EXISTS or IN
can be replaced by inner joins; subqueries with NOT EXISTS or NOT IN
can sometimes be replaced by outer joins.
>I'm really curious though about impact - won't the SQL engine build the result set and then execute the UDF for each row?
Maybe my previous post was unclear on this. In your case, the UDF is
called only in the select list, so it will probably only be called for
the rows that make it into a select list. But I intended that part of
my message as a general discussion of UDF's; in other situations than
your query, UDF's will be used in the where clause.
>I can't really wrap my head around how I could "join" in a temporary table of weekends and holidays - would I do that in a subquery - wouldn't that execute for each row anyway?
Don't make the dates table temporary! Make it a normal table.
CREATE TABLE Calendar
(SchoolDay datetime primary key clustered)
go
-- Could use a clever trick to fill Calendar table
-- set-based, but it's a one-time thing so why bother?
SET NOCOUNT ON
DECLARE @.SchoolDay datetime
SET @.SchoolDay = '20010101' -- or another start day of your choice
WHILE (@.SchoolDay < '20071231') -- or another end day of your choice
BEGIN
IF DATENAME(weekday,@.SchoolDay) NOT IN ('Saturday', 'Sunday')
INSERT Calendar(SchoolDay) VALUES (@.SchoolDay)
SET @.SchoolDay = DATEADD(day,1,@.SchoolDay)
END
-- SET NOCOUNT OFF
go
Run this once, then manually delete the holidays from this table.
Remember to add some new dates and delete dates you no longer need
once every few years.
Now, the following stored procedure should list all students that are
absent on @.DateArg, including the number of consecutive absency days
(based on my original query in this thread).
CREATE PROC ListAbsency
@.DateArg datetime
AS
SELECT now.StuId, COUNT(*) AS Duration
FROM Attendance_T AS now
INNER JOIN Attendance_T AS before
ON before.StuId = now.StuId
AND before.AttDate <= @.DateArg
WHERE now.AttDate = @.DateArg
AND NOT EXISTS
(SELECT *
FROM Calendar
WHERE SchoolDay BETWEEN before.AttDate AND @.DateArg
AND NOT EXISTS
(SELECT *
FROM Attendance_T AS btwn
WHERE btwn.StuId = now.StuId
AND btwn.AttDate = Calendar.SchoolDay))
GROUP BY now.StuId
go
If you make sure there is an index (preferably, the clustered index,
but that might hamper performance of other queries) on Attendance_T,
columns AttDate + StuId or StuId + AttDate (not sure which order will
give the best results), this should be as fast as it gets. All where
conditions can be met without having to read the table data, as only
indexed fields are used.
>I got used to SUM(1) - it returns NULL if not records were found - I like that.
I don't understand why you're happy with NULL when there are no
records found. NULL means unknown. If there are no records found, the
total number of records is not unknown, it's 0 (what COUNT(*) will
give you).
Re-reading your code, I now see that you use IsNull to convert the
result of SUM(1) from NULL to 0 if no records are found, so you
obviously don't like that as much as you say <g>.
> Is there a cost difference? I would have thought the engine would do the same thing for both cases.
You'd have to check the execution plan for that.
> When we run reports that total the number of kids in a class we do the following for TOTAL MALES, TOTAL FEMALES and TOTAL STUDENTS.
>Sum(Case When Gender='M' Then 1 else 0 End), Sum(Case When Gender<>'M' Then 1 else 0 End), Sum(1)
The Sum(Case ...) for males and females are okay, but I'd change the
last Sum(1) to Count(*). And add COALESCE to list the total number of
males/females as 0 instead of NULL (unknown) for all-male or
all-female classes.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
Thanks for the extensive answers - I really appreciate the input. When I have some time (and we are really busy), I'll do a comparative test - it might be a few weeks. I'll create a new post if I come up with anything interesting
Steve

Cursor vs Set - is it possible

I always try to use a SELECT and avoid CURSOR logic - whenever possible - bu
t this time I can't even think of a way to SET-base this operation.
I've got a table that stores dates of ATTENDANCE. Only non-present dates ha
ve rows for a person.
ABSENT on 04/02/2004 gets a row for a person - ABSENT again on 04/07/2004 ge
ts a row for a person.
I want to create a UDF that returns the "consecutive number" of ABSENT entri
es in this table for a person, based on a starting date and going backwards.
I could simply loop a working variable with a date and do a SELECT to see if
a record exists - then decrement the date by one day and check again - that
was the way we did it on the mainframe VAX we are converting from...
BTW - weekends and holidays are ignored - they don't break the CONSECUTIVE c
ounter...
Any help would be greatly appreciated...Not sure what you want, really. but you may want to try a
subquery.
select count(*) from yourtable a
where exists(select * from yourtable b where
datediff(d,a.date,b.date)=1)

>--Original Message--
>I always try to use a SELECT and avoid CURSOR logic -
whenever possible - but this time I can't even think of a
way to SET-base this operation.
>I've got a table that stores dates of ATTENDANCE. Only
non-present dates have rows for a person.
>ABSENT on 04/02/2004 gets a row for a person - ABSENT
again on 04/07/2004 gets a row for a person.
>I want to create a UDF that returns the "consecutive
number" of ABSENT entries in this table for a person,
based on a starting date and going backwards.
>I could simply loop a working variable with a date and
do a SELECT to see if a record exists - then decrement
the date by one day and check again - that was the way we
did it on the mainframe VAX we are converting from...
>BTW - weekends and holidays are ignored - they don't
break the CONSECUTIVE counter...
>Any help would be greatly appreciated...
>.
>|||Create a calendar table with dates going back as far as you need and forward
as far as you'd like (this will obviously require some maintenence going
forward, but you can give yourself a nice buffer to start with). You can
have columns for weekend or holiday designation, or just a "WorkDay" bit
column to tell you if a day is a workday or not.
Once you have that taken care of, the rest is easy... Here's a little sample
you can play with... note that although this query works, it might be
possible to do something a bit more elegant (this is very much off the
cuff):
create table #calendar(workdate datetime)
go
insert #calendar values ('20040331')
insert #calendar values ('20040401')
insert #calendar values ('20040402')
insert #calendar values ('20040405')
insert #calendar values ('20040406')
go
create table #attendance(emp char(1), dateout datetime)
go
insert #attendance values ('a', '20040401')
insert #attendance values ('a', '20040405')
insert #attendance values ('b', '20040402')
insert #attendance values ('b', '20040405')
insert #attendance values ('b', '20040406')
go
declare @.emp char(1)
declare @.startdate datetime
set @.emp = 'b'
set @.startdate = '20040406'
select count(*)
from #attendance
where
#attendance.dateout >
(select max(workdate)
from #calendar
where workdate <= @.startdate
and not exists (select *
from #attendance
where dateout=workdate
and emp=@.emp)
)
and #attendance.dateout <= @.startdate
and #attendance.emp = @.emp
go
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:98175612-F7E3-48C3-A839-949A421AE0E0@.microsoft.com...
> I always try to use a SELECT and avoid CURSOR logic - whenever possible -
but this time I can't even think of a way to SET-base this operation.
> I've got a table that stores dates of ATTENDANCE. Only non-present dates
have rows for a person.
> ABSENT on 04/02/2004 gets a row for a person - ABSENT again on 04/07/2004
gets a row for a person.
> I want to create a UDF that returns the "consecutive number" of ABSENT
entries in this table for a person, based on a starting date and going
backwards.
> I could simply loop a working variable with a date and do a SELECT to see
if a record exists - then decrement the date by one day and check again -
that was the way we did it on the mainframe VAX we are converting from...
> BTW - weekends and holidays are ignored - they don't break the CONSECUTIVE
counter...
> Any help would be greatly appreciated...|||On Tue, 6 Apr 2004 12:36:05 -0700, Steve Z wrote:

>I always try to use a SELECT and avoid CURSOR logic - whenever possible - b
ut this time I can't even think of a way to SET-base this operation.
>I've got a table that stores dates of ATTENDANCE. Only non-present dates h
ave rows for a person.
>ABSENT on 04/02/2004 gets a row for a person - ABSENT again on 04/07/2004 g
ets a row for a person.
>I want to create a UDF that returns the "consecutive number" of ABSENT entr
ies in this table for a person, based on a starting date and going backwards
.
>I could simply loop a working variable with a date and do a SELECT to see i
f a record exists - then decrement the date by one day and check again - tha
t was the way we did it on the mainframe VAX we are converting from...
>BTW - weekends and holidays are ignored - they don't break the CONSECUTIVE
counter...
>Any help would be greatly appreciated...
You can do this set-based with the help of a calendar table, filled
with all dates except weekends and holidays. Or (even better because
of added flexibility) all dates and a work-day indication
It's hard to help you with the query since you don't provide DDL, so
I'll try what I can do with soome guesswork.
SELECT MIN(AbsentDate)
FROM Absencies
WHERE Person = @.PersonRequested
AND AbsentDate < @.StartingDate
AND NOT EXISTS
(SELECT *
FROM Calendar
WHERE CalendarDate BETWEEN Absencies.AbsentDate
AND @.StartingDate
AND KindOfDay NOT IN ('Holiday', 'Weekend')
AND NOT EXISTS
(SELECT *
FROM Absencies AS A2
WHERE A2.Person = @.PersonRequested
AND A2.AbsentDate = Calendar.CalendarDate))
(untested, due to lack of DDL)
Note - this query might also be written with outer queries instead of
not exists. If this works tooo slow, try if that's a better solution.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I've seen other postings for building temporary calendar tables - I thought
that would be the direction the answers would go.
But, imagine that most people do not have a consecutive absence. Meaning th
at a row exists for lets say 4/6/2004, but no row exists for 4/5/2004. If I
want to know that the consec count = 0 for this person, wouldn't it be way
(I mean really much, much)
faster to simply do a DATEADD(dd,-1,@.AttDate) and then test the Attendance_T
table for the existence of a record for the id and date (both part of prima
ry key). If the record does not exist, return 0. All this in a simple litt
le UDF - it does one I/O an
d knows to return 0.
If I do get a record back - then simply do another DATEADD(dd,-1,@.AttDate) -
do the check again. I know we rack up another I/O, but based on caching an
d the way the primary key is clustered - that data is probably already buffe
red.
People really do not have consecutive attendance - that is the normal situat
ion.
The only small rub to what I've described above is that when I do the DATEAD
D(dd,-1,@.AttDate) I have to check that we do not have a weekend. Two ways I
can do that - DATEPART each time, or get elegant and DATEPART the first dat
e I start with and "remembe
r" when I encounter Sat/Sun.
On top of that rub, I have to check the @.AttDate to see that it does not exi
st in a "permanent" table of "holidays" that is maintained. That is one ext
ra I/O.|||On Tue, 6 Apr 2004 13:56:06 -0700, Steve Z wrote:

>I've seen other postings for building temporary calendar tables - I thought
that would be the direction the answers would go.
>But, imagine that most people do not have a consecutive absence. Meaning that a ro
w exists for lets say 4/6/2004, but no row exists for 4/5/2004. If I want to know t
hat the consec count = 0 for this person, wouldn't it be way (I mean really much, mu
ch)
faster to simply do a DATEADD(dd,-1,@.AttDate) and then test the Attendance_T
table for the existence of a record for the id and date (both part of prima
ry key). If the record does not exist, return 0. All this in a simple litt
le UDF - it does one I/O a
nd knows to return 0.
>If I do get a record back - then simply do another DATEADD(dd,-1,@.AttDate) - do the
check again. I know we rack up another I/O, but based on caching and the way the p
rimary key is clustered - that data is probably already buffered.
That would mean you'd use a recursive UDF. I've hardly any experience
with UDF's, but if my memory is correct, this is possible.

>People really do not have consecutive attendance - that is the normal situation.[/c
olor]
Reading between the lines, it looks as if you expect the set-based
solutions suggested by me and others won't perform as well.
Maybe you're right, maybe not. Depends on the amount of data and of
course on what indexes are present. The onyl way to be sure what
performs best in your situation is to test both versions.
>The only small rub to what I've described above is that when I do the DATEADD(dd,-1
,@.AttDate) I have to check that we do not have a weekend. Two ways I can do that -
DATEPART each time, or get elegant and DATEPART the first date I start with and "rem
emb
er" when I encounter Sat/Sun.
Or don't use DATEADD, but instead SELECT MAX(MyDate) FROM Calendar
WHERE MyDate < @.CurrentDate
(Yes, there's that calendar again <g> )

>On top of that rub, I have to check the @.AttDate to see that it does not exist in a
"permanent" table of "holidays" that is maintained. That is one extra I/O.
So you might as well check for weekends with the same I/O and spare
yourself the hassle of the complicated date calculations.
BTW, unless your data collection goes back to the Victorian Age, you
can expect the whole calendar table to be loaded in cache. All I/O to
that table will be logical I/O.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||No, I agree with Hugo. While your singleton function call sounds simple, I
truly believe that on many rows it will cost much more than you think
compared to an outer join against a calendar table. You can always try it
and compare, I suppose, but I would be shocked if the UDF performed
faster... especially if, as Hugo suggests, the calendar table is reasonably
small and doesn't include all of time.
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:CAACAFAD-6D94-434E-8D35-A15C6A920153@.microsoft.com...
> I've seen other postings for building temporary calendar tables - I
thought that would be the direction the answers would go.
> But, imagine that most people do not have a consecutive absence. Meaning
that a row exists for lets say 4/6/2004, but no row exists for 4/5/2004. If
I want to know that the consec count = 0 for this person, wouldn't it be way
(I mean really much, much) faster to simply do a DATEADD(dd,-1,@.AttDate) and
then test the Attendance_T table for the existence of a record for the id
and date (both part of primary key). If the record does not exist, return
0. All this in a simple little UDF - it does one I/O and knows to return 0.
> If I do get a record back - then simply do another
DATEADD(dd,-1,@.AttDate) - do the check again. I know we rack up another
I/O, but based on caching and the way the primary key is clustered - that
data is probably already buffered.
> People really do not have consecutive attendance - that is the normal
situation.
> The only small rub to what I've described above is that when I do the
DATEADD(dd,-1,@.AttDate) I have to check that we do not have a weekend. Two
ways I can do that - DATEPART each time, or get elegant and DATEPART the
first date I start with and "remember" when I encounter Sat/Sun.
> On top of that rub, I have to check the @.AttDate to see that it does not
exist in a "permanent" table of "holidays" that is maintained. That is one
extra I/O.|||I wasn't suggesting a recursive UDF - simply a WHILE loop. Being a mainfram
e programmer from the 1980's makes me leary of things like I/O and function
calls in general. Each sunction call must frame-up memory - I would keep aw
ay from that...
If the original request for help was for a UDF that simply returned a true/f
alse response as to whether a person was absent on a prior day, would that h
ave changed suggested techniques'
Here is the code I just completed - it tests out perfectly against the old m
ainframe code. The Attendance_T table contains hundreds of thousands of row
s on around 8000+ students. We usually want to use this function only on a
recordset that returns kids
absent in a given day (800 or so/divided by the number of schools in the dis
trict). The recordset returns in flat-out no time - even on my laptop test
server...
CREATE FUNCTION dbo.GetConsAtt_F (@.Stuid int, @.AttDate datetime)
RETURNS int AS
BEGIN
Declare @.Cnt int
Declare @.GR int
Declare @.DM int
Set @.Cnt=0
Set @.DM=1
While @.DM=1
Begin
Set @.AttDate=DateAdd(dd,-1,@.AttDate)
If DatePart(dw,@.AttDate) in (1,7)
Set @.DM=1
Else Set @.DM=(Select Sum(1) From Calendar_T Where CalDate=@.AttDate)
End
Set @.GR=IsNull((Select Sum(1) From Attendance_T Where StuId=@.StuId and AttDa
te=@.AttDate),0)
If @.GR<>0
Begin
Set @.Cnt=2
While @.GR<>0
Begin
Set @.DM=1
While @.DM=1
Begin
Set @.AttDate=DateAdd(dd,-1,@.AttDate)
If DatePart(dw,@.AttDate) in (1,7)
Set @.DM=1
Else Set @.DM=(Select Sum(1) From Calendar_T Where CalDate=@.AttDate)
End
Set @.GR=IsNull((Select Sum(1) From Attendance_T Where StuId=@.StuId and AttDa
te=@.AttDate),0)
If @.GR<>0 Set @.Cnt=@.Cnt+1
End
End
RETURN @.Cnt
END|||I mis-posted a reply to all this - I posted it above this
post (4/6 at 5:36 starting with "I wasn't suggesting...")
Could you please respond to the questions in there?
Thanks.

>--Original Message--
>No, I agree with Hugo. While your singleton function
call sounds simple, I
>truly believe that on many rows it will cost much more
than you think
>compared to an outer join against a calendar table. You
can always try it
>and compare, I suppose, but I would be shocked if the
UDF performed
>faster... especially if, as Hugo suggests, the calendar
table is reasonably
>small and doesn't include all of time.
>
>"Steve Z" <szlamany@.antarescomputing.com> wrote in
message
>news:CAACAFAD-6D94-434E-8D35-
A15C6A920153@.microsoft.com...
calendar tables - I
>thought that would be the direction the answers would go.
consecutive absence. Meaning
>that a row exists for lets say 4/6/2004, but no row
exists for 4/5/2004. If
>I want to know that the consec count = 0 for this
person, wouldn't it be way
>(I mean really much, much) faster to simply do a DATEADD
(dd,-1,@.AttDate) and
>then test the Attendance_T table for the existence of a
record for the id
>and date (both part of primary key). If the record does
not exist, return
>0. All this in a simple little UDF - it does one I/O
and knows to return 0.
>DATEADD(dd,-1,@.AttDate) - do the check again. I know we
rack up another
>I/O, but based on caching and the way the primary key is
clustered - that
>data is probably already buffered.
that is the normal
>situation.
that when I do the
>DATEADD(dd,-1,@.AttDate) I have to check that we do not
have a weekend. Two
>ways I can do that - DATEPART each time, or get elegant
and DATEPART the
>first date I start with and "remember" when I encounter
Sat/Sun.
see that it does not
>exist in a "permanent" table of "holidays" that is
maintained. That is one
>extra I/O.
>
>.
>|||On Tue, 6 Apr 2004 17:36:05 -0700, Steve Z wrote:

>I wasn't suggesting a recursive UDF - simply a WHILE loop. Being a mainframe progr
ammer from the 1980's makes me leary of things like I/O and function calls in genera
l. Each sunction call must frame-up memory - I would keep away from that...
Hey, I started programming on mainframes in the 80's as well. Stupid
of me to start about recursion without thinking it over first (but
maybe that's because I never tend to gove much thought to solutions I
would never choose myself).
Doo keep in mind that SQL Server is not a mainframe and not from the
80's. Learn to think set-based instead of procedural. It can be very
hard to make the switch, but once you're there, you'll find it becomes
second nature.

>If the original request for help was for a UDF that simply returned a true/false re
sponse as to whether a person was absent on a prior day, would that have changed sug
gested techniques'
If it was only that: probably. If more background was given: probably
not. UDF's are a mixed blessing. They can be great - but they have to
be executed for each row in the result set (or in a -often bigger-
intermediate set if the UDF is called somewhere in the WHERE clause).
Not a biggie if the UDF only does calculation, but if the UDF reads
from a table, you may impact performance. If you can find a way to do
the same without the UDF, reading the table from the query instead of
hiding table access in a UDF, you give the query optimiser more
possible execution strategies to choose from.

>Here is the code I just completed - it tests out perfectly against the old mainfram
e code. The Attendance_T table contains hundreds of thousands of rows on around 800
0+ students. We usually want to use this function only on a recordset that returns
kid
s absent in a given day (800 or so/divided by the number of schools in the d
istrict). The recordset returns in flat-out no time - even on my laptop tes
t server...
Well -- it does what you want and the execution is quick enough, so
this seems like a typical case of "if it works, don't fix it". Feel
free to try if my code gives better results if you want to know, or
leave it as it is. *If* you decide to do a performance test of both
versions, I'd like to know the results.
BTW, I didn't review your complete procedure, but I couldn't help but
notice this:

> Else Set @.DM=(Select Sum(1) From Calendar_T Where CalDate=@.AttDate)
Why do you use Sum(1) instead of the (more standard) Count(*)?

> Set @.GR=IsNull((Select Sum(1) From Attendance_T Where StuId=@.StuId and AttDate=@.At
tDate),0)
Do you know that IsNull is non-standard proprietary syntax? You should
replace it with Coalesce - that function does everything that IsNull
does, plus it is more flexible, plus it is ANSI standard and thus more
portable and guaranteed to be supporteed in future version of SQL
Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cursor out of time

Hi,

First of all sorry about poor english.
I have the next problem:
I have a store procedure which declares a cursor which is a select from some tables. Im surprised because the sp takes over 10 seconds on working and if i execute the select of the cursor as separate ways, it takes no more one second. Ive changed the select with another easier select and it works 0 seconds, so it seems that the problem is in the select, but only if i use a cursor to save the regs, why????

Thanks a lot.Use the "show execution plan" option to see how the server is executing the three queries. Check to see what it different between them. My guess is that there is something causing the optimizer to make a radically different choice for one or more of the queries, and that is what is making them take longer.

-PatP|||Just don't use cursors. :) What are you trying to do with the cursor?|||Thank you for answer.
I think that I meant wrong.
If I execute the select in the cursor it takes me over ten seconds but if I copy the select and paste in the query analyzer, it takes me 0 seconds. Besides this, if i change the select by other easier one, the cursor takes 0 seconds. I know the first select could be wrong but it works 0 seconds in the analyzer!!!! and inside the cursor there is not any operations.

Thanks a lot...

Cursor Help Please

I have a requirement for a user to access time sheet information by accessing a SharePoint portal listing. They will be required enter a date range. The login will be based on their domain access.

Based on the query below, I can return a result set which gives the user all rows they'll need, grouped accordingly and based on a date range. Except for a required billable percentage figure. A billable percentage is defined as billable hours / total hours * 100 or based on the query below;

day1 through 7_hr1 [Worked_hrs] where the project <> admin (@.Worked_hrs_B), divided by the total [Worked_hrs](@.Total_Worked_hrs).

I'm pretty sure I need to use a cursor which will tally all the @.Worked_hrs_NB rows, and another cursor which will tally @.Total_Worked_hrs rows and then divide the two variables * 100, to return it in the @.Billable variable.

This is where I get lost. I'm ashamed to say that my TSQL is rusty & weak at best. Rather than confuse anyone with my idea of cursor syntax, I left it out of this query, & just included the variables I assumed would fit.

A down'n'dirty cursor lesson would be most appreciated (If that's what this needs). Thanks in advance for your help.

DECLARE
@.pe_date1 AS SMALLDATETIME, --Prompt
@.pe_date2 AS SMALLDATETIME, --Prompt
@.emp_id AS CHAR (30), --Login
@.Worked_hrs_B AS INT,
@.Total_Worked_hrs AS INT,
@.Precent AS INT
SET @.pe_date1 = '6/01/2004'
SET @.pe_date2 = '7/01/2005'
SET @.emp_id = 'degajx'
************************************************************************
SET @.Worked_hrs_B = '' Here's where I get lost
SET @.Total_Worked_hrs = '' with the variables & the
SET @.Precent = '' --Make Header Info cursor to populate them.
************************************************************************
SELECT
pjlabhdr.docnbr
, pjlabhdr.pe_date
, pjlabdet.project
, pjlabdet.pjt_entity
, pjlabdet.ld_desc
, (
pjlabdet.day1_hr1 +
pjlabdet.day2_hr1 +
pjlabdet.day3_hr1 +
pjlabdet.day4_hr1 +
pjlabdet.day5_hr1 +
pjlabdet.day6_hr1 +
pjlabdet.day7_hr1
) AS [Worked_hrs]
, pjemploy.manager1
, ltle.employeename AS [Manager] --Make Header Info
, SubAcct.Descr --Make Header Info ************************************************************************
, @.Percent AS [Billable %] --An accurate return, though repeating would be fine.
************************************************************************
FROM IEM_Cut.dbo.PJLABHDR pjlabhdr
INNER JOIN
IEM_Cut.dbo.PJEMPLOY
ON pjlabhdr.employee = pjemploy.employee
Inner JOIN
labortool..laboremployee ltle (NOLOCK)
ON ltle.empid = pjemploy.manager1
LEFT OUTER JOIN
IEM_Cut.dbo.PJLABDET pjlabdet
ON pjlabhdr.docnbr = pjlabdet.docnbr
LEFT OUTER JOIN
IEM_Cut.dbo.SubAcct SubAcct
ON pjemploy.gl_subacct = SubAcct.Sub
WHERE
( pjlabdet.day1_hr1 <> 0
OR pjlabdet.day2_hr1 <> 0
OR pjlabdet.day3_hr1 <> 0
OR pjlabdet.day4_hr1 <> 0
OR pjlabdet.day5_hr1 <> 0
OR pjlabdet.day6_hr1 <> 0
OR pjlabdet.day7_hr1 <> 0
)
AND pjlabhdr.CpnyID_home = 'IEM'
AND pjlabhdr.pe_date BETWEEN CONVERT (varchar, @.pe_date1 , 107) AND CONVERT (varchar, @.pe_date2 , 107)
AND pjlabhdr.employee = @.emp_id
ORDER BY
pjlabhdr.pe_date ASC --Group
, pjlabhdr.docnbr ASC --Group
, pjlabdet.project ASC --Group
, pjlabdet.pjt_entity ASC --Group

You don't really need a cursor. You can write two queries that performs the required SUM operations and divide the results. For example:

select (select sum(...) from ....)/((select sum(...) from ...)*100.0) as billable_per

See Books Online for more details on how to write scalar queries, group by, expressions etc.

sql

Cursor Faster in a TEST Server than on a PROD Server

Hi,
I'm running a cursor (I know not quit good...but). For I don't know why, in one night the execution time step from 1h30 to 3h. I restore my database on a TEST Server (same version), the the execution time is still 1h30...
I monitored both execution, and realise that the CPU time for the CXPACKET process is 4 time higher it the PRODUCTION server than on the TEST Server....
Do you think that can be the problem?
From Neil Pike's FAQ
Q. What does a wait type of CXPacket or Exchange mean?
A. You will get this only with versions of SQL that support parallel queries, i.e. SQL 7 and later. It means that one thread of the query is waiting for a message packet from another, and the one it is waiting on is either blocked by a traditional
cause or has hit some sort of parallelism bug.
CXPacket means it is waiting on a data packet - i.e. the results of an internal query is being passed. Exchange means that it is waiting on a control packet - i.e. waiting for a child/sibling process to tell you that it is finished.
If the query doesn't complete then make sure the latest service pack is applied as there are several parallel query fixes in each one. If it still doesn't fix it then you have run into an unfixed bug and will need to contact Microsoft PSS and raise a bug
report. You should be able to work-around the problem by adding (MAXDOP=1) as a query hint which will prevent the query being parallelized.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Cursor Faster in a TEST Server than on a PROD Server

Hi,
I'm running a cursor (I know not quit good...but). For I don't know why, in
one night the execution time step from 1h30 to 3h. I restore my database o
n a TEST Server (same version), the the execution time is still 1h30...
I monitored both execution, and realise that the CPU time for the CXPACKET p
rocess is 4 time higher it the PRODUCTION server than on the TEST Server...
.
Do you think that can be the problem?From Neil Pike's FAQ
Q. What does a wait type of CXPacket or Exchange mean?
A. You will get this only with versions of SQL that support parallel
queries, i.e. SQL 7 and later. It means that one thread of the query is wait
ing for a message packet from another, and the one it is waiting on is eithe
r blocked by a traditional
cause or has hit some sort of parallelism bug.
CXPacket means it is waiting on a data packet - i.e. the results of an inter
nal query is being passed. Exchange means that it is waiting on a control pa
cket - i.e. waiting for a child/sibling process to tell you that it is finis
hed.
If the query doesn't complete then make sure the latest service pack is appl
ied as there are several parallel query fixes in each one. If it still doesn
't fix it then you have run into an unfixed bug and will need to contact Mic
rosoft PSS and raise a bug
report. You should be able to work-around the problem by adding (MAXDOP=1) a
s a query hint which will prevent the query being parallelized.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

cursor and fetch statement

can you please give me an example of using the FETCH and cursor statement

And make to be able to fetch more that one row at a time,

lets say the two database are TEST(fetch to) and TLC (fetch from) and both have Table called Mine with same fieldsSally,

I have moved your post into the forum from the MSSQL articles

MODERATOR|||

Quote:

Originally Posted by Sally1053

can you please give me an example of using the FETCH and cursor statement

And make to be able to fetch more that one row at a time,

lets say the two database are TEST(fetch to) and TLC (fetch from) and both have Table called Mine with same fields

would you mind giving a more detailed specs? maybe FETCH is not the right solutionsql

Cursor alternative

Does anybody have a way to re-write this without using a cursor? This takes a
long time to finish. The goal is to cut the processing time.
DECLARE @.the_emp_id nvarchar(50)
Begin
Declare EmpCursor Cursor For
SELECT Employees.dbo.tbl_emplist.emp_id
FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
FOR READ ONLY
end
Open EmpCursor
While(0=0) Begin
Fetch Next From EmpCursor Into @.the_emp_id
If(@.@.Fetch_Status <> 0) Break
--execute this Stored Procedures
EXEC usp_calculate_retirement @.the_emp_id
EXEC usp_calculate_benefits @.the_emp_id
EXEC usp_calculate_vacation @.the_emp_id
EXEC usp_calculate_bonuses @.the_emp_id
EXEC usp_calculate_promotion @.the_emp_id
End
Close EmpCursor
Deallocate EmpCursor
Help highly appreciated.What you have to do is look at the functionality of each of the procs that
you exec. By the looks of it, you're doing work for just one employee at a
time in those procs. You need to fuse that functionality with the SELECT on
which you declared your cursor. You can do an UPDATE with a JOIN, for
example.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92F8D983-F31D-41DB-A2AE-DBB28F545C1C@.microsoft.com...
Does anybody have a way to re-write this without using a cursor? This takes
a
long time to finish. The goal is to cut the processing time.
DECLARE @.the_emp_id nvarchar(50)
Begin
Declare EmpCursor Cursor For
SELECT Employees.dbo.tbl_emplist.emp_id
FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
FOR READ ONLY
end
Open EmpCursor
While(0=0) Begin
Fetch Next From EmpCursor Into @.the_emp_id
If(@.@.Fetch_Status <> 0) Break
--execute this Stored Procedures
EXEC usp_calculate_retirement @.the_emp_id
EXEC usp_calculate_benefits @.the_emp_id
EXEC usp_calculate_vacation @.the_emp_id
EXEC usp_calculate_bonuses @.the_emp_id
EXEC usp_calculate_promotion @.the_emp_id
End
Close EmpCursor
Deallocate EmpCursor
Help highly appreciated.|||Rewrite each of the sprocs to do their work using set-based logic rather
than processing one employee at a time. Since you call each of them with a
cursor, I bet you prolly have some cursors inside the sprocs too. Classic
mistake when developing database applications. You are not alone! :-)
The problem is that shifting your mindset to set-based from row-based can be
a VERY difficult undertaking. The two are not even in the same solar
system.
Read some beginning Transact SQL books. Hire a consultant/mentor to fix
your stuff and learn from him/her at the same time.
--
TheSQLGuru
President
Indicium Resources, Inc.
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92F8D983-F31D-41DB-A2AE-DBB28F545C1C@.microsoft.com...
> Does anybody have a way to re-write this without using a cursor? This
> takes a
> long time to finish. The goal is to cut the processing time.
> DECLARE @.the_emp_id nvarchar(50)
> Begin
> Declare EmpCursor Cursor For
> SELECT Employees.dbo.tbl_emplist.emp_id
> FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
> FOR READ ONLY
> end
> Open EmpCursor
> While(0=0) Begin
> Fetch Next From EmpCursor Into @.the_emp_id
> If(@.@.Fetch_Status <> 0) Break
> --execute this Stored Procedures
> EXEC usp_calculate_retirement @.the_emp_id
> EXEC usp_calculate_benefits @.the_emp_id
> EXEC usp_calculate_vacation @.the_emp_id
> EXEC usp_calculate_bonuses @.the_emp_id
> EXEC usp_calculate_promotion @.the_emp_id
> End
> Close EmpCursor
> Deallocate EmpCursor
> Help highly appreciated.
>|||On Aug 31, 7:32 pm, morphius <morph...@.discussions.microsoft.com>
wrote:
> Does anybody have a way to re-write this without using a cursor? This takes a
> long time to finish. The goal is to cut the processing time.
> DECLARE @.the_emp_id nvarchar(50)
> Begin
> Declare EmpCursor Cursor For
> SELECT Employees.dbo.tbl_emplist.emp_id
> FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
> FOR READ ONLY
> end
> Open EmpCursor
> While(0=0) Begin
> Fetch Next From EmpCursor Into @.the_emp_id
> If(@.@.Fetch_Status <> 0) Break
> --execute this Stored Procedures
> EXEC usp_calculate_retirement @.the_emp_id
> EXEC usp_calculate_benefits @.the_emp_id
> EXEC usp_calculate_vacation @.the_emp_id
> EXEC usp_calculate_bonuses @.the_emp_id
> EXEC usp_calculate_promotion @.the_emp_id
> End
> Close EmpCursor
> Deallocate EmpCursor
> Help highly appreciated.
create procedure calculate as
begin
exec usp_calculate_retirement
exec usp_calculate_benefits
exec usp_calculate_vacation
exec usp_calculate_bonuses
exec usp_calculate_promotion
end
for each process you now have to handle all employees.

Cursor - Structure of.

I wanted to know the internal workings of a 'cursor'.
What happens inside SQL Server from the time that it is declared, opened,
when it is used and finally closed and deallocated?
Have searched the web - but apart from examples and the pros/cons of using
them - have not come across the information that I want.
Can someone please provide this info to me or direct me to a site that has
this info?
Cheers!
SQLCatz.
I assume you are talking about Server side cursors?
This article might help you:
http://www.perftuning.com/_whitepape...ql_Cursors.pdf
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>I wanted to know the internal workings of a 'cursor'.
> What happens inside SQL Server from the time that it is declared, opened,
> when it is used and finally closed and deallocated?
> Have searched the web - but apart from examples and the pros/cons of using
> them - have not come across the information that I want.
> Can someone please provide this info to me or direct me to a site that has
> this info?
> Cheers!
> SQLCatz.
|||Wei Xiao,
Thank you for the quick response!
But, this is not what I want.
MSSql_Cursors.pdf ~ There is much more information like this available on
SQL BOL. I want the internals.
Cheers!
SQLCatz.
"wei xiao [MSFT]" wrote:

> I assume you are talking about Server side cursors?
> This article might help you:
> http://www.perftuning.com/_whitepape...ql_Cursors.pdf
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>
>
|||what specifically do you want to know?
It depends on the cursor type:
Declare cursor is just metadata operation.
static cursor is the most costly at open time, because the whole result is
generated during open. (async population makes it somewhat less a problem).
keyset requires population of the keys, so it is less expesive at open time.
Dynamic is even less expensive at oepn time, but more expensive at fetch
time. Not all cursor can be dynamic. it depends on the query.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:C634525C-5C5B-473A-B41A-CE4D7D22849C@.microsoft.com...[vbcol=seagreen]
> Wei Xiao,
> Thank you for the quick response!
> But, this is not what I want.
> MSSql_Cursors.pdf ~ There is much more information like this available on
> SQL BOL. I want the internals.
> Cheers!
> SQLCatz.
> "wei xiao [MSFT]" wrote:
rights.[vbcol=seagreen]
opened,[vbcol=seagreen]
using[vbcol=seagreen]
has[vbcol=seagreen]

Cursor - Structure of.

I wanted to know the internal workings of a 'cursor'.
What happens inside SQL Server from the time that it is declared, opened,
when it is used and finally closed and deallocated?
Have searched the web - but apart from examples and the pros/cons of using
them - have not come across the information that I want.
Can someone please provide this info to me or direct me to a site that has
this info?
Cheers!
SQLCatz.I assume you are talking about Server side cursors?
This article might help you:
http://www.perftuning.com/_whitepap...Sql_Cursors.pdf
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>I wanted to know the internal workings of a 'cursor'.
> What happens inside SQL Server from the time that it is declared, opened,
> when it is used and finally closed and deallocated?
> Have searched the web - but apart from examples and the pros/cons of using
> them - have not come across the information that I want.
> Can someone please provide this info to me or direct me to a site that has
> this info?
> Cheers!
> SQLCatz.|||Wei Xiao,
Thank you for the quick response!
But, this is not what I want.
MSSql_Cursors.pdf ~ There is much more information like this available on
SQL BOL. I want the internals.
Cheers!
SQLCatz.
"wei xiao [MSFT]" wrote:

> I assume you are talking about Server side cursors?
> This article might help you:
> http://www.perftuning.com/_whitepap...Sql_Cursors.pdf
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>
>|||what specifically do you want to know?
It depends on the cursor type:
Declare cursor is just metadata operation.
static cursor is the most costly at open time, because the whole result is
generated during open. (async population makes it somewhat less a problem).
keyset requires population of the keys, so it is less expesive at open time.
Dynamic is even less expensive at oepn time, but more expensive at fetch
time. Not all cursor can be dynamic. it depends on the query.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:C634525C-5C5B-473A-B41A-CE4D7D22849C@.microsoft.com...[vbcol=seagreen]
> Wei Xiao,
> Thank you for the quick response!
> But, this is not what I want.
> MSSql_Cursors.pdf ~ There is much more information like this available on
> SQL BOL. I want the internals.
> Cheers!
> SQLCatz.
> "wei xiao [MSFT]" wrote:
>
rights.[vbcol=seagreen]
opened,[vbcol=seagreen]
using[vbcol=seagreen]
has[vbcol=seagreen]

Cursor - Structure of.

I wanted to know the internal workings of a 'cursor'.
What happens inside SQL Server from the time that it is declared, opened,
when it is used and finally closed and deallocated?
Have searched the web - but apart from examples and the pros/cons of using
them - have not come across the information that I want.
Can someone please provide this info to me or direct me to a site that has
this info?
Cheers!
SQLCatz.I assume you are talking about Server side cursors?
This article might help you:
http://www.perftuning.com/_whitepapers/MSSql_Cursors.pdf
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>I wanted to know the internal workings of a 'cursor'.
> What happens inside SQL Server from the time that it is declared, opened,
> when it is used and finally closed and deallocated?
> Have searched the web - but apart from examples and the pros/cons of using
> them - have not come across the information that I want.
> Can someone please provide this info to me or direct me to a site that has
> this info?
> Cheers!
> SQLCatz.|||Wei Xiao,
Thank you for the quick response!
But, this is not what I want.
MSSql_Cursors.pdf ~ There is much more information like this available on
SQL BOL. I want the internals.
Cheers!
SQLCatz.
"wei xiao [MSFT]" wrote:
> I assume you are talking about Server side cursors?
> This article might help you:
> http://www.perftuning.com/_whitepapers/MSSql_Cursors.pdf
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
> >I wanted to know the internal workings of a 'cursor'.
> > What happens inside SQL Server from the time that it is declared, opened,
> > when it is used and finally closed and deallocated?
> > Have searched the web - but apart from examples and the pros/cons of using
> > them - have not come across the information that I want.
> > Can someone please provide this info to me or direct me to a site that has
> > this info?
> > Cheers!
> > SQLCatz.
>
>|||what specifically do you want to know?
It depends on the cursor type:
Declare cursor is just metadata operation.
static cursor is the most costly at open time, because the whole result is
generated during open. (async population makes it somewhat less a problem).
keyset requires population of the keys, so it is less expesive at open time.
Dynamic is even less expensive at oepn time, but more expensive at fetch
time. Not all cursor can be dynamic. it depends on the query.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:C634525C-5C5B-473A-B41A-CE4D7D22849C@.microsoft.com...
> Wei Xiao,
> Thank you for the quick response!
> But, this is not what I want.
> MSSql_Cursors.pdf ~ There is much more information like this available on
> SQL BOL. I want the internals.
> Cheers!
> SQLCatz.
> "wei xiao [MSFT]" wrote:
> > I assume you are talking about Server side cursors?
> >
> > This article might help you:
> >
> > http://www.perftuning.com/_whitepapers/MSSql_Cursors.pdf
> >
> >
> > --
> > Wei Xiao [MSFT]
> > SQL Server Storage Engine Development
> > http://blogs.msdn.com/weix
> >
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> > news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
> > >I wanted to know the internal workings of a 'cursor'.
> > > What happens inside SQL Server from the time that it is declared,
opened,
> > > when it is used and finally closed and deallocated?
> > > Have searched the web - but apart from examples and the pros/cons of
using
> > > them - have not come across the information that I want.
> > > Can someone please provide this info to me or direct me to a site that
has
> > > this info?
> > > Cheers!
> > > SQLCatz.
> >
> >
> >

Current Time of SQL-Server

Hi

is there a "SQL string" to obtain date and time of a SQLServer machine ?

thank uOn Mon, 15 Sep 2003 17:45:48 GMT in comp.databases.ms-sqlserver,
"Solli" <fabioslm@.tiscali.it> wrote:

>Hi
>is there a "SQL string" to obtain date and time of a SQLServer machine ?

GetDate()

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)

Current Time

Hi. I have a critical design issue. Not a regular time dimension case.

i work for a set of schools where each school academic has a different academic calendar. say some schools start in august, others in september.. i generated the date keys per school to populate the time dimension. so my time dimension looks like this:

DATE_KEY,

DATE_SCHOOL,

DATE_DATE,

DATE_YEAR,

DATE_MONTH,

DATE_WEEK,

DATE_DAY_NUMBER,

DATE_FISCAL_ACAD_YEAR,

DATE_FISCAL_TERM, DATE_FISCAL_WEEK,

DATE_FISCAL_WEEKDAY_NUMBER,

DATE_FISCAL_ACAD_YEAR_DAY_NUMBER,

DATE_HOLIDAY, (y/n)

DATE_WEEKEND, (y/n)

DATE_DAY_NAME,

DATE_FISCAL_TERM_FIRST_DAY,

DATE_FISCAL_TERM_LAST_DAY,

DATE_FISCAL_WEEK_FIRST_DAY,

DATE_FISCAL_WEEK_LAST_DAY

I need help in finding a design to get the current term/current week/current day so that a school manager would get his current's school week's data or term data when he logs in to the system.

Thanks

Could it be that there are only a few sets of dates applicable? In which case you could create Calendars listing the relevant dates and link this back to the school.|||

I added fields to the schools dimension stating the current acad year, term,week,and day per school. and these fields are to be updated at each ETL process. I will use them in the queries.

thanks

current date/time formula

Hi,

I would like to have a date_last_modified field for one of my SQL
tables. Instead of coding my front end to keep up with this field is
there a way to user a formula for this column in Enterprise Manager so
that each time a record is created or updated the current date/time
will be inserted/updated? I tried using getdate() but then the field
always has the current time, which is not what I am looking for. I am
looking for the time the row is updated or created. Is this possible?
Thank you!For insert new record, set the default value to getdate().

For update on an existing row. A trigger will do that. The trigger work
for insert too.|||BUT the method I preferred is using stored procedure rather than a
trigger. trigger is unforgiving. In the case you want to manually fix
some data in the database, the trigger is always triggeed.

The client app invoke the stored procedure with all the parameters
except date_last_modified. The SP fills the date_last_modified with
GETDATE().

JOHN

Current Date Member

I am trying to get an specific member from the Time Dimension.

I tried the function VBA!Date() with any success.

WITH

MEMBER MEASURES.[UniqueName] AS

'[Time].[ Time].CURRENTMEMBER.UNIQUENAME'

SELECT

{MEASURES.[UniqueName]} ON COLUMNS

,Filter( [Time].[Year].ALLMEMBERS

, ([Time].[Year].CurrentMember.membervalue

= VBA!DatePart( "yyyy", VBA!Date()))

) ON ROWS

FROM [FT FIBU]

How can I get the current date?

Try and see if function VBA!Now() will work for you.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

Current Date in Default Value

Hi,
Is it possible to add the Current Date (and Time) for the Default Value of a
Datetime-field in a table? I would like to do this without Stored
Procedures, so I guess this can be done by formatting the Default Value, but
I can't find out how :-/
Anybody knows if tihs is possible, and if so, how? :-)
Thanks a lot in advance!
Pieter
Ok I jsut foudn out that I can use CURRENT_TIMESTAMP and GETDATE() for it
:-)
Thanks anyways! :-)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:exLkPdeUEHA.544@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is it possible to add the Current Date (and Time) for the Default Value of
a
> Datetime-field in a table? I would like to do this without Stored
> Procedures, so I guess this can be done by formatting the Default Value,
but
> I can't find out how :-/
> Anybody knows if tihs is possible, and if so, how? :-)
> Thanks a lot in advance!
> Pieter
>
|||Dragu
CREATE TABLE #Test
(
[Date] DATETIME DEFAULT GETDATE()
)
GO
INSERT INTO #Test DEFAULT VALUES
GO
SELECT * FROM #Test
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:exLkPdeUEHA.544@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is it possible to add the Current Date (and Time) for the Default Value of
a
> Datetime-field in a table? I would like to do this without Stored
> Procedures, so I guess this can be done by formatting the Default Value,
but
> I can't find out how :-/
> Anybody knows if tihs is possible, and if so, how? :-)
> Thanks a lot in advance!
> Pieter
>
|||Pieter,
you might also want to investigate the CONVERT function in BOL - it has an
optional third argument specifically used with datetime values to format
them according to different locale types.
HTH,
Paul Ibison

Current Date in Default Value

Hi,
Is it possible to add the Current Date (and Time) for the Default Value of a
Datetime-field in a table? I would like to do this without Stored
Procedures, so I guess this can be done by formatting the Default Value, but
I can't find out how :-/
Anybody knows if tihs is possible, and if so, how? :-)
Thanks a lot in advance!
PieterOk I jsut foudn out that I can use CURRENT_TIMESTAMP and GETDATE() for it
:-)
Thanks anyways! :-)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:exLkPdeUEHA.544@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is it possible to add the Current Date (and Time) for the Default Value of
a
> Datetime-field in a table? I would like to do this without Stored
> Procedures, so I guess this can be done by formatting the Default Value,
but
> I can't find out how :-/
> Anybody knows if tihs is possible, and if so, how? :-)
> Thanks a lot in advance!
> Pieter
>|||Dragu
CREATE TABLE #Test
(
[Date] DATETIME DEFAULT GETDATE()
)
GO
INSERT INTO #Test DEFAULT VALUES
GO
SELECT * FROM #Test
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:exLkPdeUEHA.544@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is it possible to add the Current Date (and Time) for the Default Value of
a
> Datetime-field in a table? I would like to do this without Stored
> Procedures, so I guess this can be done by formatting the Default Value,
but
> I can't find out how :-/
> Anybody knows if tihs is possible, and if so, how? :-)
> Thanks a lot in advance!
> Pieter
>|||Pieter,
you might also want to investigate the CONVERT function in BOL - it has an
optional third argument specifically used with datetime values to format
them according to different locale types.
HTH,
Paul Ibison

Current Date in Default Value

Hi,
Is it possible to add the Current Date (and Time) for the Default Value of a
Datetime-field in a table? I would like to do this without Stored
Procedures, so I guess this can be done by formatting the Default Value, but
I can't find out how :-/
Anybody knows if tihs is possible, and if so, how? :-)
Thanks a lot in advance!
PieterOk I jsut foudn out that I can use CURRENT_TIMESTAMP and GETDATE() for it
:-)
Thanks anyways! :-)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:exLkPdeUEHA.544@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is it possible to add the Current Date (and Time) for the Default Value of
a
> Datetime-field in a table? I would like to do this without Stored
> Procedures, so I guess this can be done by formatting the Default Value,
but
> I can't find out how :-/
> Anybody knows if tihs is possible, and if so, how? :-)
> Thanks a lot in advance!
> Pieter
>|||Dragu
CREATE TABLE #Test
(
[Date] DATETIME DEFAULT GETDATE()
)
GO
INSERT INTO #Test DEFAULT VALUES
GO
SELECT * FROM #Test
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:exLkPdeUEHA.544@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is it possible to add the Current Date (and Time) for the Default Value of
a
> Datetime-field in a table? I would like to do this without Stored
> Procedures, so I guess this can be done by formatting the Default Value,
but
> I can't find out how :-/
> Anybody knows if tihs is possible, and if so, how? :-)
> Thanks a lot in advance!
> Pieter
>|||Pieter,
you might also want to investigate the CONVERT function in BOL - it has an
optional third argument specifically used with datetime values to format
them according to different locale types.
HTH,
Paul Ibison

Current Activity Window always times out on Sql Server

I always get this error 1222 time out if I go on my SQL Server and open
Enterprise Manager, go under management, Current acvtivity.
How do I set up SQL server to show me the activity.
The error is (both on server and client)
Error 1222 Lock request time otu period exceeded.
THanksThis is because it switches out of READ UNCOMMITTED about halfway through
for whatever reason. Use sp_who2 in query analyzer instead. If you're
interested in locking/blocking check out aba_lockinfo
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"freesoul777" <freesoul777@.discussions.microsoft.com> wrote in message
news:74405E36-07ED-4A3A-88D8-5E407505035E@.microsoft.com...
>I always get this error 1222 time out if I go on my SQL Server and open
> Enterprise Manager, go under management, Current acvtivity.
> How do I set up SQL server to show me the activity.
> The error is (both on server and client)
> Error 1222 Lock request time otu period exceeded.
> THanks
>

Current Activity Window always times out on Sql Server

I always get this error 1222 time out if I go on my SQL Server and open
Enterprise Manager, go under management, Current acvtivity.
How do I set up SQL server to show me the activity.
The error is (both on server and client)
Error 1222 Lock request time otu period exceeded.
THanks
This is because it switches out of READ UNCOMMITTED about halfway through
for whatever reason. Use sp_who2 in query analyzer instead. If you're
interested in locking/blocking check out aba_lockinfo
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"freesoul777" <freesoul777@.discussions.microsoft.com> wrote in message
news:74405E36-07ED-4A3A-88D8-5E407505035E@.microsoft.com...
>I always get this error 1222 time out if I go on my SQL Server and open
> Enterprise Manager, go under management, Current acvtivity.
> How do I set up SQL server to show me the activity.
> The error is (both on server and client)
> Error 1222 Lock request time otu period exceeded.
> THanks
>