COMSysApp DCOM got error

How can I resolve the error listed below.
Thank You,
DCOM got error "The service cannot be started, either because it is disabled
or because it has no enabled devices associated with it. " attempting to
start the service COMSysApp with arguments "" in order to run the server:Joe,
This is a SQL Server group - you are more likely to get an answer if you
post in a DCOM related group.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> How can I resolve the error listed below.
> Thank You,
>
> DCOM got error "The service cannot be started, either because it is disabl
ed
> or because it has no enabled devices associated with it. " attempting to
> start the service COMSysApp with arguments "" in order to run the server:

COMSysApp DCOM got error

How can I resolve the error listed below.
Thank You,
DCOM got error "The service cannot be started, either because it is disabled
or because it has no enabled devices associated with it. " attempting to
start the service COMSysApp with arguments "" in order to run the server:
Joe,
This is a SQL Server group - you are more likely to get an answer if you
post in a DCOM related group.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> How can I resolve the error listed below.
> Thank You,
>
> DCOM got error "The service cannot be started, either because it is disabled
> or because it has no enabled devices associated with it. " attempting to
> start the service COMSysApp with arguments "" in order to run the server:

COMSysApp DCOM got error

How can I resolve the error listed below.
Thank You,
DCOM got error "The service cannot be started, either because it is disabled
or because it has no enabled devices associated with it. " attempting to
start the service COMSysApp with arguments "" in order to run the server:Joe,
This is a SQL Server group - you are more likely to get an answer if you
post in a DCOM related group.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> How can I resolve the error listed below.
> Thank You,
>
> DCOM got error "The service cannot be started, either because it is disabled
> or because it has no enabled devices associated with it. " attempting to
> start the service COMSysApp with arguments "" in order to run the server:sqlsql

Computing Business Hours

I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.

Essentially I have a number of different alternatives, and have found some potential solutions, including:

www.dbforums.com/arch/7/2003/9/914261

However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun

Additionally, I would like to remove Holiday's from the calculation for service level as well.

I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.

The DTD I have for the Holiday table is as follows:

CREATE Table Holidays (HolidayDate DateTime)
GO
Insert Into Holidays (HolidayDate) Values ('12-25-2004')

Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates

Thank you in advanceyour original question was

I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am.

Given your examples of business hours for Monday and Saturday what would your answer look like for the above question?

16 hours for Monday?
0 hours for Saturday?

What happens if one of these days is a holiday?|||Originally posted by Paul Young
your original question was

I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am.

Given your examples of business hours for Monday and Saturday what would your answer look like for the above question?

16 hours for Monday?
0 hours for Saturday?

What happens if one of these days is a holiday?

Based upon the previous example (8-5) the service level would be 3 hrs. Based upon the "flexible" (and actual scenario) it would have been 1 hour. Call center closed at 5pm and opened at 10am on Tuesday.

NET: I am trying to compute the number of hours the call center is open during a range of time.

Thanks|||okay, lets stick to Monday then...

if the call is received at 04:00 pm and the call center closes at 05:00pm then there was one hour on Monday that the call could have been worked. The call center opened on Tuesday at 10:00 am and the call was process during the first our hour so add an additional hour, that's two hours in total. I am missing something as I don't see three or one hours as the answer.

I am almost finished with an example but would like to present a close answer. could you help with the above question?|||Originally posted by Paul Young
okay, lets stick to Monday then...

if the call is received at 04:00 pm and the call center closes at 05:00pm then there was one hour on Monday that the call could have been worked. The call center opened on Tuesday at 10:00 am and the call was process during the first our hour so add an additional hour, that's two hours in total. I am missing something as I don't see three or one hours as the answer.

I am almost finished with an example but would like to present a close answer. could you help with the above question?

Sorry, the original question posed two scenarios:

Typical 8-5M-F
Reality (variable hours) I.E. Monday8a-5p , 10a-7p T, 8-5 W Th F, 10-2 Sat, 10-12 Sun.

Thus, the 3hrs versus 1hr.

Let's eliminate the "Typical" scenario, as I have examples of that. What I am struggling with is the reality (variable) open hours scenario, and how to best report SLA's when the center is open.

Sorry for the confuzzzzzion :)|||okay, still not sure I understand how you are calculating days but maybe you can use this:

declare @.HolidayCalendar table(Holiday datetime)
declare @.BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)

declare @.dtFrom datetime
, @.dtTemp datetime
, @.dtTo datetime
, @.DOW tinyint
, @.HolidayCount tinyint
, @.Hours tinyint

insert into @.HolidayCalendar values('17-Mar-2004')
insert into @.BusinessHours values(1, 1000, 1200)
insert into @.BusinessHours values(2, 0800, 1700)
insert into @.BusinessHours values(3, 1000, 1900)
insert into @.BusinessHours values(4, 0800, 1700)
insert into @.BusinessHours values(5, 0800, 1700)
insert into @.BusinessHours values(6, 0800, 1700)
insert into @.BusinessHours values(7, 1000, 1400)

select @.dtFrom = '17-Mar-2004 12:00'
, @.dtTo = '18-Mar-2004 10:00'
, @.HolidayCount = 0
, @.Hours = 0

select ' Start', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

--
-- If the problem was solved in one day then only need to calculate hours for a single day
--
if datediff(day,@.dtFrom,@.dtTo) = 0 begin
set @.Hours = datediff(hour,@.dtFrom,@.dtTo)
end else begin
--
-- Test for @.dtFrom being a holiday, whould need to move time to start of first business day after the holiday
--
if exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
while exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
--
-- Move @.dtFrom 1 day closer to a business day
--
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end
end

select ' @.dtFrom = Holiday', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being after business hours, would want to move to start of next day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) > (CloseAt/100)) begin
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end

select ' @.dtFrom after hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being before business hours, would want to move to start of this business day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) < (OpenAt/100)) begin
select @.dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@.dtFrom,120))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end

select ' @.dtFrom before hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the ploblem was submmited
-- provided the adjusted dates, @.dtFrom & @.dtTo are not on the same day
--
if datediff(day,@.dtFrom,@.dtTo) > 0 begin
set @.dtTemp = convert(varchar(10),@.dtFrom,120)
select @.Hours = datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end
select ' Hours for 1st day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Step through each day, if it is a holiday skip else add the number of business hours for that day
--
set @.dtTemp = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
while datediff(day,@.dtTemp,@.dtTo) > 0 begin
if not exists(select * from @.HolidayCalendar where Holiday = @.dtTemp) begin
select @.Hours = @.Hours + (CloseAt - OpenAt) / 100
from @.BusinessHours
where DOW = datepart(weekday,@.dtTemp)
end
set @.dtTemp = dateadd(day,1,@.dtTemp)
end

select 'Hours for days between', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the problem was resolved
--
set @.dtTemp = convert(varchar(10),@.dtTo,120)
select @.Hours = @.Hours + datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo)
from @.BusinessHours
where DOW = datepart(weekday,@.dtTo)
select ' Hours for Lst day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'

end

select ' End', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

this is NOT the optimal code but any changes made would depend on your implamintation. I hope this get you going in the right direction.|||THANK YOU SO MUCH! I haven't fully digested this yet, but at first glance it appears to be EXACTLY what I needed.

Thank you again.

Mike

Originally posted by Paul Young
okay, still not sure I understand how you are calculating days but maybe you can use this:

declare @.HolidayCalendar table(Holiday datetime)
declare @.BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)

declare @.dtFrom datetime
, @.dtTemp datetime
, @.dtTo datetime
, @.DOW tinyint
, @.HolidayCount tinyint
, @.Hours tinyint

insert into @.HolidayCalendar values('17-Mar-2004')
insert into @.BusinessHours values(1, 1000, 1200)
insert into @.BusinessHours values(2, 0800, 1700)
insert into @.BusinessHours values(3, 1000, 1900)
insert into @.BusinessHours values(4, 0800, 1700)
insert into @.BusinessHours values(5, 0800, 1700)
insert into @.BusinessHours values(6, 0800, 1700)
insert into @.BusinessHours values(7, 1000, 1400)

select @.dtFrom = '17-Mar-2004 12:00'
, @.dtTo = '18-Mar-2004 10:00'
, @.HolidayCount = 0
, @.Hours = 0

select ' Start', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

--
-- If the problem was solved in one day then only need to calculate hours for a single day
--
if datediff(day,@.dtFrom,@.dtTo) = 0 begin
set @.Hours = datediff(hour,@.dtFrom,@.dtTo)
end else begin
--
-- Test for @.dtFrom being a holiday, whould need to move time to start of first business day after the holiday
--
if exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
while exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
--
-- Move @.dtFrom 1 day closer to a business day
--
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end
end

select ' @.dtFrom = Holiday', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being after business hours, would want to move to start of next day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) > (CloseAt/100)) begin
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end

select ' @.dtFrom after hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being before business hours, would want to move to start of this business day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) < (OpenAt/100)) begin
select @.dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@.dtFrom,120))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end

select ' @.dtFrom before hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the ploblem was submmited
-- provided the adjusted dates, @.dtFrom & @.dtTo are not on the same day
--
if datediff(day,@.dtFrom,@.dtTo) > 0 begin
set @.dtTemp = convert(varchar(10),@.dtFrom,120)
select @.Hours = datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end
select ' Hours for 1st day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Step through each day, if it is a holiday skip else add the number of business hours for that day
--
set @.dtTemp = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
while datediff(day,@.dtTemp,@.dtTo) > 0 begin
if not exists(select * from @.HolidayCalendar where Holiday = @.dtTemp) begin
select @.Hours = @.Hours + (CloseAt - OpenAt) / 100
from @.BusinessHours
where DOW = datepart(weekday,@.dtTemp)
end
set @.dtTemp = dateadd(day,1,@.dtTemp)
end

select 'Hours for days between', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the problem was resolved
--
set @.dtTemp = convert(varchar(10),@.dtTo,120)
select @.Hours = @.Hours + datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo)
from @.BusinessHours
where DOW = datepart(weekday,@.dtTo)
select ' Hours for Lst day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'

end

select ' End', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

this is NOT the optimal code but any changes made would depend on your implamintation. I hope this get you going in the right direction.|||I tried to keep this overly simple and did not worry about efficiency. My goal was to get you thinking about the problem in a diffrent light. If you have a question about any section drop me a line or post it here.

Please post back with your final solution.|||Paul,

Thanks a million. I doubt I would have ever come up with the solution that you presented. I have tweeked it a little, and am posting the code change.

Biggest Delta, is I removed the same day logic. This was making the assumption that an interaction starting and ending the same day was the duration of the interaction. This would have "dinged" the call center in three instances. Interaction came in before start of day and handled during day. If for some reason the day was a Holiday. Or, if the interaction was handled after business hours. (That time after close, but before the workers go home).

Additionally, I converted the computations into Seconds.

Finally, I handled when the interaction was handled with NO open business hours. I.E. Came in after close and handled before open.

However, the logic you provided was invaluable.

Thanks agian

HERE'S THE CODE

--okay, still not sure I understand how you are calculating days but maybe you can use this:

--code:------------------------
declare @.HolidayCalendar table(Holiday datetime)
declare @.BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)

declare @.dtFrom datetime
, @.dtTemp datetime
, @.dtTo datetime
, @.DOW tinyint
, @.HolidayCount tinyint
, @.Hours tinyint
, @.Seconds int

insert into @.HolidayCalendar values('17-Mar-2004')
insert into @.BusinessHours values(1, 1000, 1200)
insert into @.BusinessHours values(2, 0800, 1700)
insert into @.BusinessHours values(3, 1000, 1900)
insert into @.BusinessHours values(4, 0800, 1700)
insert into @.BusinessHours values(5, 0800, 1730)
insert into @.BusinessHours values(6, 0800, 1700)
insert into @.BusinessHours values(7, 1000, 1400)

select @.dtFrom = '17-Mar-2004 07:00:00' --3
, @.dtTo = '18-Mar-2004 07:00:00' --7
, @.HolidayCount = 0
, @.Hours = 0
, @.Seconds = 0

select ' Start', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds As 'Seconds'

--
-- If the problem was solved in one day then only need to calculate hours for a single day
--

--
-- Test for @.dtFrom being a holiday, whould need to move time to start of first business day after the holiday
--
if exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
while exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
--
-- Move @.dtFrom 1 day closer to a business day
--
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end
end

select ' @.dtFrom = Holiday', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Test for @.dtFrom being after business hours, would want to move to start of next day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) > (CloseAt/100)) begin
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end

select ' @.dtFrom after hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Test for @.dtFrom being before business hours, would want to move to start of this business day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) < (OpenAt/100)) begin
select @.dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@.dtFrom,120))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end

select ' @.dtFrom before hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Calculate the number of hours for the day the ploblem was submmited
-- provided the adjusted dates, @.dtFrom & @.dtTo are not on the same day
--
if datediff(day,@.dtFrom,@.dtTo) > 0 begin
set @.dtTemp = convert(varchar(10),@.dtFrom,120)
select @.Hours = CASE WHEN (datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp)) < 0) Then 0
ELSE datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp))
END,
@.Seconds = CASE WHEN (datediff(ss,@.dtFrom,dateadd(minute, Floor(CloseAt/100 * 60) + Right(CloseAt, 2),@.dtTemp)) < 0) THEN 0
ELSE datediff(ss,@.dtFrom,dateadd(minute, Floor(CloseAt/100 * 60) + Right(CloseAt, 2),@.dtTemp))
END
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end
select ' Hours for 1st day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds As 'Seconds'
--
-- Step through each day, if it is a holiday skip else add the number of business hours for that day
--
set @.dtTemp = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
while datediff(day,@.dtTemp,@.dtTo) > 0 begin
if not exists(select * from @.HolidayCalendar where Holiday = @.dtTemp) begin
select @.Hours = @.Hours + (CloseAt - OpenAt) / 100,
@.Seconds = @.Seconds + Floor((CloseAt - OpenAt) / 100 * 60 * 60) + (Right((CloseAt - OpenAt), 2) * 60)
from @.BusinessHours
where DOW = datepart(weekday,@.dtTemp)
end
set @.dtTemp = dateadd(day,1,@.dtTemp)
end

select 'Hours for days between', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Calculate the number of hours for the day the problem was resolved
--
Declare @.SubResult int
Declare @.SubDate datetime
Declare @.SubREsult2 int

set @.dtTemp = convert(varchar(10),@.dtTo,120)
select @.Hours = @.Hours + CASE WHEN datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo) < 0 THEN 0
ELSE datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo)
END,
@.Seconds = @.Seconds + CASE WHEN datediff(ss,dateadd(ss, Floor((OpenAt/100 * 60 * 60)) + (Right(OpenAt, 2) * 60), @.dtTemp), @.dtTo) < 0 THEN 0
ELSE datediff(ss,dateadd(ss, Floor((OpenAt/100 * 60 * 60)) + (Right(OpenAt, 2) * 60), @.dtTemp), @.dtTo)
END
from @.BusinessHours
where DOW = datepart(weekday,@.dtTo)
select ' Hours for Lst day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'

--end

select ' End', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds As 'Seconds'|||Originally posted by BMWM3LUV
Paul,

Thanks a million. I doubt I would have ever come up with the solution that you presented. I have tweeked it a little, and am posting the code change.

Biggest Delta, is I removed the same day logic. This was making the assumption that an interaction starting and ending the same day was the duration of the interaction. This would have "dinged" the call center in three instances. Interaction came in before start of day and handled during day. If for some reason the day was a Holiday. Or, if the interaction was handled after business hours. (That time after close, but before the workers go home).

Additionally, I converted the computations into Seconds.

Finally, I handled when the interaction was handled with NO open business hours. I.E. Came in after close and handled before open.

However, the logic you provided was invaluable.

Thanks agian

HERE'S THE CODE

Glad to help! I have been told I am not quite right, maybe that is why I enjoy working this type of problem.|||If it makes you sleep better, you are ALL RIGHT in my book. I searched/begged/and pleaded for this solution for months.

Finally, my work told me I didn't have any more time to "research" this. So, I ultimately had to tell the customer that this report was going to cost them 12k.

Now that I have the query, I can put together the front end and re-sell it! If I turn a profit, I will make sure to share the wealth.

Mike|||Originally posted by BMWM3LUV
If it makes you sleep better, you are ALL RIGHT in my book. I searched/begged/and pleaded for this solution for months.

Finally, my work told me I didn't have any more time to "research" this. So, I ultimately had to tell the customer that this report was going to cost them 12k.

Now that I have the query, I can put together the front end and re-sell it! If I turn a profit, I will make sure to share the wealth.

Mike

Cool, Do you own an M3 or lust after one? If you you do then you own me a ride! I am a Mercedes Benz guy but wouldn't pass up a ride in a bemer!|||Own 98 M3 4-door. Incredible car. Joys of being a computer geek.|||How about just as select statement? Also independent of datefirst setting.

CREATE TABLE WorkHours(
DayNum tinyint PRIMARY KEY CHECK(DayNum BETWEEN 1 AND 7),
StartTime smalldatetime CHECK(StartTime BETWEEN 0 AND 1),
EndTime smalldatetime CHECK(EndTime BETWEEN 0 AND 1),
DayName char(9))

insert WorkHours
select 1, '8:00 AM', '5:00 PM', 'Monday'
union all select 2, '10:00 AM', '7:00 PM', 'Tuesday'
union all select 3, '8:00 AM', '5:00 PM', 'Wednesday'
union all select 4, '8:00 AM', '5:00 PM', 'Thursday'
union all select 5, '8:00 AM', '5:00 PM', 'Friday'
union all select 6, '10:00 AM', '2:00 PM', 'Saturday'
union all select 7, '10:00 AM', '12:00 PM', 'Sunday'

CREATE TABLE Holidays(
Holiday datetime PRIMARY KEY CHECK(Holiday = CONVERT(char(8),Holiday,112)),
Descr char(12))

insert Holidays
select '20040101','New Years'
union all select '20040119', 'ML King Day'
union all select '20040216', 'President''s'
union all select '20040411', 'Easter'
union all select '20040531', 'Memorial'
union all select '20040704', 'Independence'
union all select '20040906', 'Labor'
union all select '20041011', 'Columbus'
union all select '20041111', 'Veteran''s'
union all select '20041125', 'Thanksgiving'
union all select '20041225', 'Christmas'

CREATE PROC p_ProcessHours @.Opened datetime, @.Closed datetime, @.Hours int OUTPUT AS
IF @.Opened > @.Closed RETURN -1
SELECT @.Hours = --weeks between dates
(SELECT (DATEDIFF(d,@.Opened,@.Closed)/7)*SUM(DATEDIFF(hh,StartTime,EndTime))
FROM WorkHours)
- --holidays
(SELECT ISNULL(SUM(DATEDIFF(hh,StartTime,EndTime)),0)
FROM Holidays h JOIN WorkHours w ON DATEDIFF(d,0,h.Holiday)%7+1 = DATEDIFF(d,0,DayNum)
WHERE Holiday > @.Opened AND Holiday < @.Closed)
+ --hours of dates in same week
(SELECT SUM(DATEDIFF(hh,StartTime,EndTime))
FROM
(SELECT DayNum, StartTime, EndTime
FROM WorkHours
UNION ALL
SELECT DayNum+7,StartTime,EndTime
FROM WorkHours)x
WHERE DayNum BETWEEN DATEDIFF(d,0,@.Opened)%7+1 AND DATEDIFF(d,0,@.Closed)%7
+ CASE WHEN DATEDIFF(d,0,@.Opened)%7 > DATEDIFF(d,0,@.Closed)%7 THEN 8 ELSE 1 END)
- --hours not in opened date
ISNULL(
(SELECT DATEDIFF(hh,StartTime,CASE WHEN CONVERT(varchar,@.Opened,14) < EndTime THEN CONVERT(varchar,@.Opened,14) ELSE EndTime END)
FROM WorkHours
WHERE DayNum = DATEDIFF(d,0,@.Opened)%7+1 AND DATEDIFF(hh,StartTime,CONVERT(varchar,@.Opened,14)) > 0
AND NOT EXISTS
(SELECT *
FROM Holidays
WHERE Holiday = CONVERT(char(8),@.Opened,112))),0)
- --hours not in closed date
ISNULL(
(SELECT DATEDIFF(hh,CASE WHEN CONVERT(varchar,@.Closed,14) > StartTime THEN CONVERT(varchar,@.Closed,14) ELSE StartTime END,EndTime)
FROM WorkHours
WHERE DayNum = DATEDIFF(d,0,@.Closed)%7+1 AND DATEDIFF(hh,CONVERT(varchar,@.Closed,14),EndTime) > 0
AND NOT EXISTS
(SELECT *
FROM Holidays
WHERE Holiday = CONVERT(char(8),@.Closed,112))),0)

Componente Services + win xp sp2

Hi.
I've intalled windows xp service pack 2, since there I cant' view Component
Services -> Transaction Statistics from a remote server.
I'm not using XP Firewall.
Any ideia?
Thanks
EsioI found the problem...
RPC changed.
http://msdn.microsoft.com/security/productinfo/XPSP2/networkprotection/restrict_remote_clients.aspx
Esio
"Esio Nunes" <esio_nunes@.hotmail.com> escreveu na mensagem
news:uznYoqk%23EHA.4092@.TK2MSFTNGP09.phx.gbl...
> Hi.
> I've intalled windows xp service pack 2, since there I cant' view
> Component Services -> Transaction Statistics from a remote server.
> I'm not using XP Firewall.
> Any ideia?
> Thanks
> Esio
>

Componente Services + win xp sp2

Hi.
I've intalled Windows XP service pack 2, since there I cant' view Component
Services -> Transaction Statistics from a remote server.
I'm not using XP Firewall.
Any ideia?
Thanks
EsioI found the problem...
RPC changed.
http://msdn.microsoft.com/security/...te_clients.aspx
Esio
"Esio Nunes" <esio_nunes@.hotmail.com> escreveu na mensagem
news:uznYoqk%23EHA.4092@.TK2MSFTNGP09.phx.gbl...
> Hi.
> I've intalled Windows XP service pack 2, since there I cant' view
> Component Services -> Transaction Statistics from a remote server.
> I'm not using XP Firewall.
> Any ideia?
> Thanks
> Esio
>

completed successfully, but not sending and no error.

hi, i have a message queue system using sql 2005 service broker.

the code and setup is the same on both dev and live database. but

soon after i restored a live backup to dev. the queue stopped

working on dev, live is ok thou. after some trouble shooting, i

found that the server is not sending the message at all, but it says

"Command(s) completed successfully" without any error messages.

setup:

--

create message type TestQueryMessage validation = none

create contract TestQueryContract (TestQueryMessage sent by initiator)

create queue TestSenderQueue

create service TestSenderService on queue TestSenderQueue

create queue TestQueueReceiver

create service TestServiceReceiver on queue TestQueueReceiver (TestQueryContract)

send message:

-

declare @.conversationhandle uniqueidentifier;

begin dialog @.conversationhandle

from service [TestSenderService]

to service 'TestServiceReceiver'

on contract [TestQueryContract]

with encryption = off;

send on conversation @.conversationhandle

message type [TestQueryMessage] ('blah blah blah');

result:

-

Command(s) completed successfully.

but when i do "select * from TestQueueReceiver", there's nothing. and i sure nothing else had picked up the messages.

please advise. thanks a lot.

Look in sys.transmission_queue on the sender's database. The transmission_status column should indicate the problem. Most likely the broker was not enabled back after the restore operation.

HTH,
~ Remus

compensating delete on replication database!

Hi
I have Merge Publication with just one Subscriber running on SQL 2000
which has been Service packed fully.
Looking at the previous posts I did uncheck 'Enforce relationship for
replication' on the Table Relationships hoping that the deletion of
records will not happen.
But for some unknown reason the records in the FK relationship is
deleted, which is causing all sorts of problems. There are no Triggers
on the database, and I could not find a pattern for the deletion it
happens randomly.
E.G. We have a 'Contacts' and 'Addresses' Table, one contact can have
more than 1 address. But for some reason the addresses are being deleted
for the contact, i am not sure where it is happening whether in
Subscriber or Publisher, but we loose the record.
Does any one has a good solution for this?
Thanks in advance.
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Do you have any user triggers at all?
Perhaps you are experiencing the compensating commands situation :
http://support.microsoft.com/default...Product=sql2k?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)!
|||Thanks Paul for the link. But I do not have any user triggers at all?
Since the 'Enforce relationship for replication' is unchecked, I am a
bit puzzled about how this is happening.
Can I apply the hotfix (the link you have sent) to the production
server? Is it safe?
Thanks once again.
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Paul
The Hotfix is available only in SP4 Beta and not sure when it is going
to be released. In the meantime the database is loosing the records all
the way through. Can you suggest any intermediate solutions? somethings
I need to check etc..
Thanks
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||The two suggestions weren't related - user triggers was just one other
posibility.
If it is a compensation issue, you should be able to reproduce. An address
added successfully at one site will be deleted if it cannot be applied at
the other due to a PK/FK error, constraint error etc. So, you need to check
that the schemas are identical wrt these tables (SQLCompare?). If you can
find an address that repeatedly gets removed then tracking/removing the
constraint should be simple.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)!
|||Hi Paul
Thanks for the reply. There is not pattern these records are deleted and
I am not able to re-create it as well.
The Addresses Table has 2 FK relationship one to the AddressType Table
and one to the Contact Table were the records are all present. It is
only the Address records that gets deleted.
Any other areas to look into, please...
Thanks
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Paul
I am really sorry, there is a Trigger created by another developer on
the Addresses Table, which maintaines the Ref. Intergerity. Could this
be the cause for this 'compensating deletion', if so how to overcome
this, do we need to remove the triggers?
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Compatibility of SQL Server 7.0 with Windows 2K

Is SQL Server 7.0 compatible with the Windows 200 operating system? Are
there certain patches or service packs that need to be applied to SQL to
make this compatibility work? Thanks.

Larry"lmclaus" <lmclaus@.sandia.gov> wrote in message
news:bka37u$j4c$1@.sass2141.sandia.gov...
> Is SQL Server 7.0 compatible with the Windows 200 operating system? Are
> there certain patches or service packs that need to be applied to SQL to
> make this compatibility work? Thanks.
> Larry

http://www.microsoft.com/sql/evalua...datasheet.asp#E

Simon|||"lmclaus" <lmclaus@.sandia.gov> wrote in message
news:bka37u$j4c$1@.sass2141.sandia.gov...
> Is SQL Server 7.0 compatible with the Windows 200 operating system? Are
> there certain patches or service packs that need to be applied to SQL to
> make this compatibility work? Thanks.

Seems to be working fine here.

Ron.
--
Performance Intelligence, Inc.
Spy 4 DB2 - http://www.pireporting.com/spy4db2.html