Passing result sets: time

Showing posts with label time. Show all posts

Friday, March 30, 2012

Peer-to-peer replication and Oracle real application clusters

Hello,
I have a question I hope can be answered fully.
Some time last year, I attended a Microsoft presentation in Dallas and a sql
server specialist from microsoft (I think his name was srikan) mentioned
during Q&A that SQL Server 2005 has peer-to-peer replication which is a
comparable solution to oracle's real application cluster with some
restriction.
I recently attended another presentation also at Microsoft but this time by
their partner that are suppose to be SQL server experts. This speaker said
that it is not a comparable solution. He gave several reasons and they
seemed to make sense but then again, a microsoft technical specialist said
differently last year.
Did the product change? Who should I listen to? My co-workers say I should
listen to microsoft because they created the product but the other guy seems
to know a lot also and said he used to be an oracle dba. I am confused.
Anybody can help here? I don't want to ask Oracle because they want a lot of
information from me before they will answer and their newsgroup people are
quite unfriendly.
Thank you.
aK.
No, peer-to-peer replication is not a replacement to RAC. Unfortunately,
the TS would be wrong in a literal interpretation. If you look at the basic
data flow, there is some similarity in the solution which disappears when
you start looking a lot deeper.
I don't know a huge amount about RAC, but I understand the basics. With RAC
you are essentially plugging N servers into an Oracle architecture that
access a single database. What you wind up with is theoretically pooling
all of your hardware resources together (memory, processors, network I/O)
which can be basically treated by an application as a single massive server.
They still point to a single database on the backend. You still have to
deal with all of the issues related to changes coming from multiple servers
into a single database which now has to resolve conflicting changes on the
fly and several other implementation related issues.
Peer-to-peer replication is a logical extension to bi-directional
transactional replication. This handles the data layer only. The
replication engine is used to replicate like it says "from everyone - to
everyone". There is ZERO conflict detection or resolution, so your data
changes must be partitioned. If two users were to change the same piece of
data on different servers at the same time, it would create a huge
synchronization issue.
What problem are you trying to solve?
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Angie" <NO_Angie_Kong_SPAM@.hotmail.com> wrote in message
news:ORao5BrMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a question I hope can be answered fully.
> Some time last year, I attended a Microsoft presentation in Dallas and a
> sql server specialist from microsoft (I think his name was srikan)
> mentioned during Q&A that SQL Server 2005 has peer-to-peer replication
> which is a comparable solution to oracle's real application cluster with
> some restriction.
> I recently attended another presentation also at Microsoft but this time
> by their partner that are suppose to be SQL server experts. This speaker
> said that it is not a comparable solution. He gave several reasons and
> they seemed to make sense but then again, a microsoft technical specialist
> said differently last year.
> Did the product change? Who should I listen to? My co-workers say I should
> listen to microsoft because they created the product but the other guy
> seems to know a lot also and said he used to be an oracle dba. I am
> confused.
> Anybody can help here? I don't want to ask Oracle because they want a lot
> of information from me before they will answer and their newsgroup people
> are quite unfriendly.
> Thank you.
>
> aK.
>
sql

Peak During Time Period

[vbcol=seagreen]
of agents logged in concurrently during a specified time period. <<
Why not VIEWs? SQL is a non-procdural language after all. If you had
followed minimal netiquette, would this table lok liket his?
CREATE TABLE HelpDeskLogs
(agent_id CHAR(5) NOT NULL
REFERENCES Agents(agent_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
finish_time DATETIME, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (agent_id, start_time));
[vbcol=seagreen]
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this: <<
Let's fill up a table of ranges:
CREATE TABLE ReportPeriods
(period_scale CHAR(2) DEFAULT '15' NOT NULL,
CHECK (period_scale IN ('15', '30', '45', 'HR', 'DY')
start_time DATETIME NOT NULL,
finish_time DATETIME NOT NULL, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (period_scale, start_time));
In standard SQL, we have a predicate for durations like this:
SELECT COUNT(agent_id) AS active_agents
FROM ReportPeriods AS P, HelpDeskLogs AS L
WHERE (P.start_time, P.finish_time)
OVERLAPS (L.start_time, L.finish_time);
That predicate gets translated into this:
(P.start_time > L.start_time
AND NOT (P.start_time >= L.finish_time
AND P.finish_time >= L.finish_time))
OR (L.start_time > P.start_time
AND NOT (L.start_time >= P.finish_time
AND L.finish_time >= P.finish_time))
OR (P.start_time = L.start_time
AND (P.finish_time <> L.finish_time
OR P.finish_time = L.finish_time))
Yes, it is a bit weird because it has to handle NULLs in the general
case.
You might also want to look up Rick Snodgrass at the University of
Arizona. he has a copy of his book on Temporal quereis in SQL in PDF
on his university website.
|||"Dave" <dave@.court-technologies.com> wrote in message
news:7bbc1b13.0411250202.57f40780@.posting.google.c om...
> Hi
> I am hoping someone might be able to help me out with this.
> I am writing a helpdesk system which records agents logging in and out
> of the system.
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.
> Can anyone make any suggestions? The time period the report user can
> choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.
> Thanks in advance
CREATE TABLE LoginPeriods
(
agent_id VARCHAR(20) NOT NULL,
time_in DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
CHECK (time_in <= CURRENT_TIMESTAMP),
time_out DATETIME NOT NULL DEFAULT '99991231'
CHECK (time_out <= CURRENT_TIMESTAMP OR time_out = '99991231'),
PRIMARY KEY (time_in, agent_id),
CHECK (time_in < time_out)
)
-- Your sample data
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A1', '20041122 14:02', '20041122 17:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A2', '20041122 09:00', '20041122 17:12')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A3', '20041122 10:25', '20041122 16:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A4', '20041122 11:02', '20041122 12:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A5', '20041122 16:00', '20041122 17:30')
-- Digits 0-9
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
-- Nonnegative integers to some suitable upper bound
-- Used in representing the sequence of time periods from
-- begin to end datetimes
CREATE VIEW NonnegativeIntegers (n)
AS
SELECT Ones.d + 10 * Tens.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
-- For each time period between begin and end datetimes,
-- return login periods that overlap
CREATE FUNCTION LoginPeriodsBetween
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AS begin_time,
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time) AS end_time,
agent_id,
CASE WHEN time_in <=
DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
THEN DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
ELSE time_in
END AS time_in,
CASE WHEN time_out <=
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
THEN time_out
ELSE DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
END AS time_out
FROM NonnegativeIntegers AS I
LEFT OUTER JOIN
LoginPeriods AS LP
ON time_out > DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AND
time_in < DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
WHERE I.n < DATEDIFF(MINUTE, @.begin_time, @.end_time) / @.period_mins
)
-- Maximum number of concurrent agent logins per time period
CREATE FUNCTION MaxConcurrentAgents
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT begin_time, end_time, MAX(concurrent_agents) AS concurrent_agents_tally
FROM (SELECT LP1.begin_time, LP1.end_time,
LP1.agent_id,
LP1.time_in, LP1.time_out,
COUNT(LP2.agent_id) AS concurrent_agents
FROM LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP1
LEFT OUTER JOIN
LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP2
ON LP1.begin_time = LP2.begin_time AND
LP1.end_time = LP2.end_time AND
LP1.time_in >= LP2.time_in AND
LP1.time_in < LP2.time_out
GROUP BY LP1.begin_time, LP1.end_time, LP1.agent_id,
LP1.time_in, LP1.time_out) AS CA
GROUP BY begin_time, end_time
)
-- Maximum number of concurrent agent logins for each 30 minute
-- period between the specified begin and end datetimes
-- Note that no logins for a time period will be indicated by a 0 tally
SELECT begin_time, end_time, concurrent_agents_tally
FROM MaxConcurrentAgents('20041122 09:00', '20041122 18:00', 30)
ORDER BY begin_time
begin_time end_time concurrent_agents_tally
2004-11-22 09:00:00.000 2004-11-22 09:30:00.000 1
2004-11-22 09:30:00.000 2004-11-22 10:00:00.000 1
2004-11-22 10:00:00.000 2004-11-22 10:30:00.000 2
2004-11-22 10:30:00.000 2004-11-22 11:00:00.000 2
2004-11-22 11:00:00.000 2004-11-22 11:30:00.000 3
2004-11-22 11:30:00.000 2004-11-22 12:00:00.000 3
2004-11-22 12:00:00.000 2004-11-22 12:30:00.000 3
2004-11-22 12:30:00.000 2004-11-22 13:00:00.000 2
2004-11-22 13:00:00.000 2004-11-22 13:30:00.000 2
2004-11-22 13:30:00.000 2004-11-22 14:00:00.000 2
2004-11-22 14:00:00.000 2004-11-22 14:30:00.000 3
2004-11-22 14:30:00.000 2004-11-22 15:00:00.000 3
2004-11-22 15:00:00.000 2004-11-22 15:30:00.000 3
2004-11-22 15:30:00.000 2004-11-22 16:00:00.000 3
2004-11-22 16:00:00.000 2004-11-22 16:30:00.000 4
2004-11-22 16:30:00.000 2004-11-22 17:00:00.000 3
2004-11-22 17:00:00.000 2004-11-22 17:30:00.000 3
2004-11-22 17:30:00.000 2004-11-22 18:00:00.000 0
JAG

Peak During Time Period

Hi
I am hoping someone might be able to help me out with this.
I am writing a helpdesk system which records agents logging in and out
of the system.
I need to write a stored procedure which will show the peak number of
agents logged in concurrently during a specified time period. Within
the time period, the person viewing the report should be able to
specify intervals at which to see the data.
Eg. There is already a table in the system which holds logged
in/logged out data like
22/11/2004 14:02 - 22/11/2004 17:30
22/11/2004 09:00 - 22/11/2004 17:12
22/11/2004 10:25 - 22/11/2004 16:30
22/11/2004 11:02 - 22/11/2004 12:30
22/11/2004 16:00 - 22/11/2004 17:30
The report user can then say for example they want to view data
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this:
10/11/2004 00:00 - 10/11/2004 00:15
10/11/2004 00:15 - 10/11/2004 00:30
10/11/2004 00:30 - 10/11/2004 00:45
10/11/2004 00:45 - 10/11/2004 01:00 etc etc
Against each of these time slots, I need to work out the peak number
of concurrent agents logged in using the first table.
Can anyone make any suggestions? The time period the report user can
choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.
Thanks in advance[posted and mailed, please reply in news]
Dave (dave@.court-technologies.com) writes:
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.
The normal recommendation for this sort of post is to include:
o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired output given the sample.
This makes it easy to post a tested solution, since the dirty work is
already set up, and it's only to cut and paste.
This time I did it for you, because the problem seemed interesting enough.
First I set up a table of numbers. This is a one-column table with numbers
1 to whatever the limit (80000 in this case, that's 55 days). The I packed
the actual query in a stored procedure to easily permit for parameters.
@.len is the length of the reporting interval in minutes.
The query has a number of nested derived tables. The innermost gives
the number of agents logged in at any given minute. The middle table,
normalizes the minute to the start of the reporting interval, and
the outermost, get the maximum count for each interval.
Further testing is recommended!
CREATE TABLE sessions (start datetime NOT NULL,
stop datetime NULL)
go
SET DATEFORMAT dmy
go
SELECT TOP 80000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
go
INSERT sessions (start, stop)
SELECT '22/11/2004 14:02', '22/11/2004 17:30' UNION
SELECT '22/11/2004 09:00', '22/11/2004 17:12' UNION
SELECT '22/11/2004 10:25', '22/11/2004 16:30' UNION
SELECT '22/11/2004 11:02', '22/11/2004 12:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 17:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 16:05' UNION
SELECT '22/11/2004 16:06', '22/11/2004 16:10'
go
CREATE PROCEDURE get_peaks @.start datetime,
@.stop datetime,
@.len smallint AS
SELECT intstart, intstop = dateadd(mi, @.len, intstart), MAX(cnt)
FROM (SELECT intstart = dateadd(mi, @.len *
(datediff(mi, @.start, a.minute) / @.len), @.start),
a.cnt
FROM (SELECT mi.minute, cnt = COUNT(s.start)
FROM (SELECT minute = dateadd(mi, n, @.start)
FROM numbers
WHERE n <= datediff(mi, @.start, @.stop)) AS mi
LEFT JOIN sessions s
ON mi.minute BETWEEN s.start AND s.stop
GROUP BY mi.minute) AS a
) AS b
GROUP BY intstart
ORDER BY intstart
go
EXEC get_peaks '20041122 08:00', '20041122 18:00', 15
go
DROP TABLE numbers
DROP TABLE sessions
DROP PROCEDURE get_peaks
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||>> I am writing a helpdesk system which records agents logging in and
out of the system. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Does your boss, who is paying you, hide this
information and expect you to get your job done?
You might also want to learn that the only format for temporal data in
Standard SQL is ISO-8601 (yyyy-mm-dd hh:mm:ss.sss...) and start using
it; you can never tell, other systems just might follow iSO standards
:)
>> I need to write a stored procedure which will show the peak number
of agents logged in concurrently during a specified time period. <<
Why not VIEWs? SQL is a non-procdural language after all. If you had
followed minimal netiquette, would this table lok liket his?
CREATE TABLE HelpDeskLogs
(agent_id CHAR(5) NOT NULL
REFERENCES Agents(agent_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
finish_time DATETIME, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (agent_id, start_time));
>> The report user can then say for example they want to view data
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this: <<
Let's fill up a table of ranges:
CREATE TABLE ReportPeriods
(period_scale CHAR(2) DEFAULT '15' NOT NULL,
CHECK (period_scale IN ('15', '30', '45', 'HR', 'DY')
start_time DATETIME NOT NULL,
finish_time DATETIME NOT NULL, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (period_scale, start_time));
In standard SQL, we have a predicate for durations like this:
SELECT COUNT(agent_id) AS active_agents
FROM ReportPeriods AS P, HelpDeskLogs AS L
WHERE (P.start_time, P.finish_time)
OVERLAPS (L.start_time, L.finish_time);
That predicate gets translated into this:
(P.start_time > L.start_time
AND NOT (P.start_time >= L.finish_time
AND P.finish_time >= L.finish_time))
OR (L.start_time > P.start_time
AND NOT (L.start_time >= P.finish_time
AND L.finish_time >= P.finish_time))
OR (P.start_time = L.start_time
AND (P.finish_time <> L.finish_time
OR P.finish_time = L.finish_time))
Yes, it is a bit weird because it has to handle NULLs in the general
case.
You might also want to look up Rick Snodgrass at the University of
Arizona. he has a copy of his book on Temporal quereis in SQL in PDF
on his university website.|||"Dave" <dave@.court-technologies.com> wrote in message
news:7bbc1b13.0411250202.57f40780@.posting.google.com...
> Hi
> I am hoping someone might be able to help me out with this.
> I am writing a helpdesk system which records agents logging in and out
> of the system.
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.
> Can anyone make any suggestions? The time period the report user can
> choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.
> Thanks in advance
CREATE TABLE LoginPeriods
(
agent_id VARCHAR(20) NOT NULL,
time_in DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
CHECK (time_in <= CURRENT_TIMESTAMP),
time_out DATETIME NOT NULL DEFAULT '99991231'
CHECK (time_out <= CURRENT_TIMESTAMP OR time_out = '99991231'),
PRIMARY KEY (time_in, agent_id),
CHECK (time_in < time_out)
)
-- Your sample data
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A1', '20041122 14:02', '20041122 17:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A2', '20041122 09:00', '20041122 17:12')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A3', '20041122 10:25', '20041122 16:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A4', '20041122 11:02', '20041122 12:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A5', '20041122 16:00', '20041122 17:30')
-- Digits 0-9
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
-- Nonnegative integers to some suitable upper bound
-- Used in representing the sequence of time periods from
-- begin to end datetimes
CREATE VIEW NonnegativeIntegers (n)
AS
SELECT Ones.d + 10 * Tens.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
-- For each time period between begin and end datetimes,
-- return login periods that overlap
CREATE FUNCTION LoginPeriodsBetween
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AS begin_time,
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time) AS end_time,
agent_id,
CASE WHEN time_in <= DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
THEN DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
ELSE time_in
END AS time_in,
CASE WHEN time_out <= DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
THEN time_out
ELSE DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
END AS time_out
FROM NonnegativeIntegers AS I
LEFT OUTER JOIN
LoginPeriods AS LP
ON time_out > DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AND
time_in < DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
WHERE I.n < DATEDIFF(MINUTE, @.begin_time, @.end_time) / @.period_mins
)
-- Maximum number of concurrent agent logins per time period
CREATE FUNCTION MaxConcurrentAgents
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT begin_time, end_time, MAX(concurrent_agents) AS concurrent_agents_tally
FROM (SELECT LP1.begin_time, LP1.end_time,
LP1.agent_id,
LP1.time_in, LP1.time_out,
COUNT(LP2.agent_id) AS concurrent_agents
FROM LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP1
LEFT OUTER JOIN
LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP2
ON LP1.begin_time = LP2.begin_time AND
LP1.end_time = LP2.end_time AND
LP1.time_in >= LP2.time_in AND
LP1.time_in < LP2.time_out
GROUP BY LP1.begin_time, LP1.end_time, LP1.agent_id,
LP1.time_in, LP1.time_out) AS CA
GROUP BY begin_time, end_time
)
-- Maximum number of concurrent agent logins for each 30 minute
-- period between the specified begin and end datetimes
-- Note that no logins for a time period will be indicated by a 0 tally
SELECT begin_time, end_time, concurrent_agents_tally
FROM MaxConcurrentAgents('20041122 09:00', '20041122 18:00', 30)
ORDER BY begin_time
begin_time end_time concurrent_agents_tally
2004-11-22 09:00:00.000 2004-11-22 09:30:00.000 1
2004-11-22 09:30:00.000 2004-11-22 10:00:00.000 1
2004-11-22 10:00:00.000 2004-11-22 10:30:00.000 2
2004-11-22 10:30:00.000 2004-11-22 11:00:00.000 2
2004-11-22 11:00:00.000 2004-11-22 11:30:00.000 3
2004-11-22 11:30:00.000 2004-11-22 12:00:00.000 3
2004-11-22 12:00:00.000 2004-11-22 12:30:00.000 3
2004-11-22 12:30:00.000 2004-11-22 13:00:00.000 2
2004-11-22 13:00:00.000 2004-11-22 13:30:00.000 2
2004-11-22 13:30:00.000 2004-11-22 14:00:00.000 2
2004-11-22 14:00:00.000 2004-11-22 14:30:00.000 3
2004-11-22 14:30:00.000 2004-11-22 15:00:00.000 3
2004-11-22 15:00:00.000 2004-11-22 15:30:00.000 3
2004-11-22 15:30:00.000 2004-11-22 16:00:00.000 3
2004-11-22 16:00:00.000 2004-11-22 16:30:00.000 4
2004-11-22 16:30:00.000 2004-11-22 17:00:00.000 3
2004-11-22 17:00:00.000 2004-11-22 17:30:00.000 3
2004-11-22 17:30:00.000 2004-11-22 18:00:00.000 0
--
JAGsql

Peak During Time Period

[vbcol=seagreen]
of agents logged in concurrently during a specified time period. <<
Why not VIEWs? SQL is a non-procdural language after all. If you had
followed minimal netiquette, would this table lok liket his?
CREATE TABLE HelpDeskLogs
(agent_id CHAR(5) NOT NULL
REFERENCES Agents(agent_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
finish_time DATETIME, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (agent_id, start_time));
[vbcol=seagreen]
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this: <<
Let's fill up a table of ranges:
CREATE TABLE ReportPeriods
(period_scale CHAR(2) DEFAULT '15' NOT NULL,
CHECK (period_scale IN ('15', '30', '45', 'HR', 'DY')
start_time DATETIME NOT NULL,
finish_time DATETIME NOT NULL, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (period_scale, start_time));
In standard SQL, we have a predicate for durations like this:
SELECT COUNT(agent_id) AS active_agents
FROM ReportPeriods AS P, HelpDeskLogs AS L
WHERE (P.start_time, P.finish_time)
OVERLAPS (L.start_time, L.finish_time);
That predicate gets translated into this:
(P.start_time > L.start_time
AND NOT (P.start_time >= L.finish_time
AND P.finish_time >= L.finish_time))
OR (L.start_time > P.start_time
AND NOT (L.start_time >= P.finish_time
AND L.finish_time >= P.finish_time))
OR (P.start_time = L.start_time
AND (P.finish_time <> L.finish_time
OR P.finish_time = L.finish_time))
Yes, it is a bit weird because it has to handle NULLs in the general
case.
You might also want to look up Rick Snodgrass at the university of
Arizona. he has a copy of his book on Temporal quereis in SQL in PDF
on his university website.|||"Dave" <dave@.court-technologies.com> wrote in message
news:7bbc1b13.0411250202.57f40780@.posting.google.com...
> Hi
> I am hoping someone might be able to help me out with this.
> I am writing a helpdesk system which records agents logging in and out
> of the system.
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.
> Can anyone make any suggestions? The time period the report user can
> choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.
> Thanks in advance
CREATE TABLE LoginPeriods
(
agent_id VARCHAR(20) NOT NULL,
time_in DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
CHECK (time_in <= CURRENT_TIMESTAMP),
time_out DATETIME NOT NULL DEFAULT '99991231'
CHECK (time_out <= CURRENT_TIMESTAMP OR time_out = '99991231'),
PRIMARY KEY (time_in, agent_id),
CHECK (time_in < time_out)
)
-- Your sample data
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A1', '20041122 14:02', '20041122 17:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A2', '20041122 09:00', '20041122 17:12')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A3', '20041122 10:25', '20041122 16:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A4', '20041122 11:02', '20041122 12:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A5', '20041122 16:00', '20041122 17:30')
-- Digits 0-9
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
-- Nonnegative integers to some suitable upper bound
-- Used in representing the sequence of time periods from
-- begin to end datetimes
CREATE VIEW NonnegativeIntegers (n)
AS
SELECT Ones.d + 10 * Tens.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
-- For each time period between begin and end datetimes,
-- return login periods that overlap
CREATE FUNCTION LoginPeriodsBetween
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AS begin_time,
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time) AS end_time,
agent_id,
CASE WHEN time_in <=
DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
THEN DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
ELSE time_in
END AS time_in,
CASE WHEN time_out <=
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
THEN time_out
ELSE DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
END AS time_out
FROM NonnegativeIntegers AS I
LEFT OUTER JOIN
LoginPeriods AS LP
ON time_out > DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AND
time_in < DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
WHERE I.n < DATEDIFF(MINUTE, @.begin_time, @.end_time) / @.period_mins
)
-- Maximum number of concurrent agent logins per time period
CREATE FUNCTION MaxConcurrentAgents
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT begin_time, end_time, MAX(concurrent_agents) AS concurrent_agents_tal
ly
FROM (SELECT LP1.begin_time, LP1.end_time,
LP1.agent_id,
LP1.time_in, LP1.time_out,
COUNT(LP2.agent_id) AS concurrent_agents
FROM LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP1
LEFT OUTER JOIN
LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP2
ON LP1.begin_time = LP2.begin_time AND
LP1.end_time = LP2.end_time AND
LP1.time_in >= LP2.time_in AND
LP1.time_in < LP2.time_out
GROUP BY LP1.begin_time, LP1.end_time, LP1.agent_id,
LP1.time_in, LP1.time_out) AS CA
GROUP BY begin_time, end_time
)
-- Maximum number of concurrent agent logins for each 30 minute
-- period between the specified begin and end datetimes
-- Note that no logins for a time period will be indicated by a 0 tally
SELECT begin_time, end_time, concurrent_agents_tally
FROM MaxConcurrentAgents('20041122 09:00', '20041122 18:00', 30)
ORDER BY begin_time
begin_time end_time concurrent_agents_tally
2004-11-22 09:00:00.000 2004-11-22 09:30:00.000 1
2004-11-22 09:30:00.000 2004-11-22 10:00:00.000 1
2004-11-22 10:00:00.000 2004-11-22 10:30:00.000 2
2004-11-22 10:30:00.000 2004-11-22 11:00:00.000 2
2004-11-22 11:00:00.000 2004-11-22 11:30:00.000 3
2004-11-22 11:30:00.000 2004-11-22 12:00:00.000 3
2004-11-22 12:00:00.000 2004-11-22 12:30:00.000 3
2004-11-22 12:30:00.000 2004-11-22 13:00:00.000 2
2004-11-22 13:00:00.000 2004-11-22 13:30:00.000 2
2004-11-22 13:30:00.000 2004-11-22 14:00:00.000 2
2004-11-22 14:00:00.000 2004-11-22 14:30:00.000 3
2004-11-22 14:30:00.000 2004-11-22 15:00:00.000 3
2004-11-22 15:00:00.000 2004-11-22 15:30:00.000 3
2004-11-22 15:30:00.000 2004-11-22 16:00:00.000 3
2004-11-22 16:00:00.000 2004-11-22 16:30:00.000 4
2004-11-22 16:30:00.000 2004-11-22 17:00:00.000 3
2004-11-22 17:00:00.000 2004-11-22 17:30:00.000 3
2004-11-22 17:30:00.000 2004-11-22 18:00:00.000 0
JAG

Peak During Time Period

I am hoping someone might be able to help me out with this.

I am writing a helpdesk system which records agents logging in and out
of the system.

I need to write a stored procedure which will show the peak number of
agents logged in concurrently during a specified time period. Within
the time period, the person viewing the report should be able to
specify intervals at which to see the data.

Eg. There is already a table in the system which holds logged
in/logged out data like

22/11/2004 14:02 - 22/11/2004 17:30
22/11/2004 09:00 - 22/11/2004 17:12
22/11/2004 10:25 - 22/11/2004 16:30
22/11/2004 11:02 - 22/11/2004 12:30
22/11/2004 16:00 - 22/11/2004 17:30

The report user can then say for example they want to view data
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this:

10/11/2004 00:00 - 10/11/2004 00:15
10/11/2004 00:15 - 10/11/2004 00:30
10/11/2004 00:30 - 10/11/2004 00:45
10/11/2004 00:45 - 10/11/2004 01:00 etc etc

Against each of these time slots, I need to work out the peak number
of concurrent agents logged in using the first table.

Can anyone make any suggestions? The time period the report user can
choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.

Thanks in advance[posted and mailed, please reply in news]

Dave (dave@.court-technologies.com) writes:
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.

The normal recommendation for this sort of post is to include:

o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired output given the sample.

This makes it easy to post a tested solution, since the dirty work is
already set up, and it's only to cut and paste.

This time I did it for you, because the problem seemed interesting enough.
First I set up a table of numbers. This is a one-column table with numbers
1 to whatever the limit (80000 in this case, that's 55 days). The I packed
the actual query in a stored procedure to easily permit for parameters.
@.len is the length of the reporting interval in minutes.

The query has a number of nested derived tables. The innermost gives
the number of agents logged in at any given minute. The middle table,
normalizes the minute to the start of the reporting interval, and
the outermost, get the maximum count for each interval.

Further testing is recommended!

CREATE TABLE sessions (start datetime NOT NULL,
stop datetime NULL)
go
SET DATEFORMAT dmy
go
SELECT TOP 80000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
go
INSERT sessions (start, stop)
SELECT '22/11/2004 14:02', '22/11/2004 17:30' UNION
SELECT '22/11/2004 09:00', '22/11/2004 17:12' UNION
SELECT '22/11/2004 10:25', '22/11/2004 16:30' UNION
SELECT '22/11/2004 11:02', '22/11/2004 12:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 17:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 16:05' UNION
SELECT '22/11/2004 16:06', '22/11/2004 16:10'
go
CREATE PROCEDURE get_peaks @.start datetime,
@.stop datetime,
@.len smallint AS

SELECT intstart, intstop = dateadd(mi, @.len, intstart), MAX(cnt)
FROM (SELECT intstart = dateadd(mi, @.len *
(datediff(mi, @.start, a.minute) / @.len), @.start),
a.cnt
FROM (SELECT mi.minute, cnt = COUNT(s.start)
FROM (SELECT minute = dateadd(mi, n, @.start)
FROM numbers
WHERE n <= datediff(mi, @.start, @.stop)) AS mi
LEFT JOIN sessions s
ON mi.minute BETWEEN s.start AND s.stop
GROUP BY mi.minute) AS a
) AS b
GROUP BY intstart
ORDER BY intstart
go
EXEC get_peaks '20041122 08:00', '20041122 18:00', 15
go
DROP TABLE numbers
DROP TABLE sessions
DROP PROCEDURE get_peaks

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I am writing a helpdesk system which records agents logging in and
out of the system. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Does your boss, who is paying you, hide this
information and expect you to get your job done?

You might also want to learn that the only format for temporal data in
Standard SQL is ISO-8601 (yyyy-mm-dd hh:mm:ss.sss...) and start using
it; you can never tell, other systems just might follow iSO standards
:)

>> I need to write a stored procedure which will show the peak number
of agents logged in concurrently during a specified time period. <<

Why not VIEWs? SQL is a non-procdural language after all. If you had
followed minimal netiquette, would this table lok liket his?

CREATE TABLE HelpDeskLogs
(agent_id CHAR(5) NOT NULL
REFERENCES Agents(agent_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
finish_time DATETIME, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (agent_id, start_time));

>> The report user can then say for example they want to view data
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this: <<

Let's fill up a table of ranges:

CREATE TABLE ReportPeriods
(period_scale CHAR(2) DEFAULT '15' NOT NULL,
CHECK (period_scale IN ('15', '30', '45', 'HR', 'DY')
start_time DATETIME NOT NULL,
finish_time DATETIME NOT NULL, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (period_scale, start_time));

In standard SQL, we have a predicate for durations like this:

SELECT COUNT(agent_id) AS active_agents
FROM ReportPeriods AS P, HelpDeskLogs AS L
WHERE (P.start_time, P.finish_time)
OVERLAPS (L.start_time, L.finish_time);

That predicate gets translated into this:

(P.start_time > L.start_time
AND NOT (P.start_time >= L.finish_time
AND P.finish_time >= L.finish_time))
OR (L.start_time > P.start_time
AND NOT (L.start_time >= P.finish_time
AND L.finish_time >= P.finish_time))
OR (P.start_time = L.start_time
AND (P.finish_time <> L.finish_time
OR P.finish_time = L.finish_time))

Yes, it is a bit weird because it has to handle NULLs in the general
case.

You might also want to look up Rick Snodgrass at the University of
Arizona. he has a copy of his book on Temporal quereis in SQL in PDF
on his university website.|||"Dave" <dave@.court-technologies.com> wrote in message
news:7bbc1b13.0411250202.57f40780@.posting.google.c om...
> Hi
> I am hoping someone might be able to help me out with this.
> I am writing a helpdesk system which records agents logging in and out
> of the system.
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.
> Can anyone make any suggestions? The time period the report user can
> choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.
> Thanks in advance

CREATE TABLE LoginPeriods
(
agent_id VARCHAR(20) NOT NULL,
time_in DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
CHECK (time_in <= CURRENT_TIMESTAMP),
time_out DATETIME NOT NULL DEFAULT '99991231'
CHECK (time_out <= CURRENT_TIMESTAMP OR time_out = '99991231'),
PRIMARY KEY (time_in, agent_id),
CHECK (time_in < time_out)
)

-- Your sample data
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A1', '20041122 14:02', '20041122 17:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A2', '20041122 09:00', '20041122 17:12')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A3', '20041122 10:25', '20041122 16:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A4', '20041122 11:02', '20041122 12:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A5', '20041122 16:00', '20041122 17:30')

-- Digits 0-9
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9

-- Nonnegative integers to some suitable upper bound
-- Used in representing the sequence of time periods from
-- begin to end datetimes
CREATE VIEW NonnegativeIntegers (n)
AS
SELECT Ones.d + 10 * Tens.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens

-- For each time period between begin and end datetimes,
-- return login periods that overlap
CREATE FUNCTION LoginPeriodsBetween
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AS begin_time,
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time) AS end_time,
agent_id,
CASE WHEN time_in <=
DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
THEN DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
ELSE time_in
END AS time_in,
CASE WHEN time_out <=
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
THEN time_out
ELSE DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
END AS time_out
FROM NonnegativeIntegers AS I
LEFT OUTER JOIN
LoginPeriods AS LP
ON time_out > DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AND
time_in < DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
WHERE I.n < DATEDIFF(MINUTE, @.begin_time, @.end_time) / @.period_mins
)

-- Maximum number of concurrent agent logins per time period
CREATE FUNCTION MaxConcurrentAgents
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT begin_time, end_time, MAX(concurrent_agents) AS concurrent_agents_tally
FROM (SELECT LP1.begin_time, LP1.end_time,
LP1.agent_id,
LP1.time_in, LP1.time_out,
COUNT(LP2.agent_id) AS concurrent_agents
FROM LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP1
LEFT OUTER JOIN
LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP2
ON LP1.begin_time = LP2.begin_time AND
LP1.end_time = LP2.end_time AND
LP1.time_in >= LP2.time_in AND
LP1.time_in < LP2.time_out
GROUP BY LP1.begin_time, LP1.end_time, LP1.agent_id,
LP1.time_in, LP1.time_out) AS CA
GROUP BY begin_time, end_time
)

-- Maximum number of concurrent agent logins for each 30 minute
-- period between the specified begin and end datetimes
-- Note that no logins for a time period will be indicated by a 0 tally
SELECT begin_time, end_time, concurrent_agents_tally
FROM MaxConcurrentAgents('20041122 09:00', '20041122 18:00', 30)
ORDER BY begin_time

begin_time end_time concurrent_agents_tally
2004-11-22 09:00:00.000 2004-11-22 09:30:00.000 1
2004-11-22 09:30:00.000 2004-11-22 10:00:00.000 1
2004-11-22 10:00:00.000 2004-11-22 10:30:00.000 2
2004-11-22 10:30:00.000 2004-11-22 11:00:00.000 2
2004-11-22 11:00:00.000 2004-11-22 11:30:00.000 3
2004-11-22 11:30:00.000 2004-11-22 12:00:00.000 3
2004-11-22 12:00:00.000 2004-11-22 12:30:00.000 3
2004-11-22 12:30:00.000 2004-11-22 13:00:00.000 2
2004-11-22 13:00:00.000 2004-11-22 13:30:00.000 2
2004-11-22 13:30:00.000 2004-11-22 14:00:00.000 2
2004-11-22 14:00:00.000 2004-11-22 14:30:00.000 3
2004-11-22 14:30:00.000 2004-11-22 15:00:00.000 3
2004-11-22 15:00:00.000 2004-11-22 15:30:00.000 3
2004-11-22 15:30:00.000 2004-11-22 16:00:00.000 3
2004-11-22 16:00:00.000 2004-11-22 16:30:00.000 4
2004-11-22 16:30:00.000 2004-11-22 17:00:00.000 3
2004-11-22 17:00:00.000 2004-11-22 17:30:00.000 3
2004-11-22 17:30:00.000 2004-11-22 18:00:00.000 0

--
JAG

PDF Textbox/Hyperlink Issue

Hello -
Long time listener, first time caller...
I'm having trouble with something that I haven't seen posted, and I'm
wondering if this is a known issue, or if not, if there is any
possible solution.
When I create a textbox and do not set the "Navigation/JumpToURL"
property, then run the report and export to PDF Format the textbox
renders correctly.
If I do set a value in the "Navigation/JumpToURL" and then render the
report to PDF, the textbox renders correctly but the hyperlink extends
well below the bottom of the text (almost as if a LF was inserted).
There seems to be no way to adjust this behavior.
It's worth mentioning that any other rendering format works properly -
but my users want the smooth, familiar PDF interface. The MHTML
format would work ok if IE didn't chop it up into multiple pages, and
Excel will do if there is no other way, but my first choice is PDF.
I have tried setting all the properties I can think of, including line
height, textbox height, font size, table spacing, etc. to no avail.
Any ideas?
Thanks,
~ScottI'm not seeing this. If you stick a border on the textbox, does the
hyperlink go beyond the border of the textbox?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott" <petersonscott@.aasd.k12.wi.us> wrote in message
news:4955c3e2.0409251012.659cfe54@.posting.google.com...
> Hello -
> Long time listener, first time caller...
> I'm having trouble with something that I haven't seen posted, and I'm
> wondering if this is a known issue, or if not, if there is any
> possible solution.
> When I create a textbox and do not set the "Navigation/JumpToURL"
> property, then run the report and export to PDF Format the textbox
> renders correctly.
> If I do set a value in the "Navigation/JumpToURL" and then render the
> report to PDF, the textbox renders correctly but the hyperlink extends
> well below the bottom of the text (almost as if a LF was inserted).
> There seems to be no way to adjust this behavior.
> It's worth mentioning that any other rendering format works properly -
> but my users want the smooth, familiar PDF interface. The MHTML
> format would work ok if IE didn't chop it up into multiple pages, and
> Excel will do if there is no other way, but my first choice is PDF.
> I have tried setting all the properties I can think of, including line
> height, textbox height, font size, table spacing, etc. to no avail.
> Any ideas?
> Thanks,
> ~Scott|||Hi Brian -
Yes, actually, it does - not on the right or left, or even the top - it's
always on the bottom. This has caused a lot of confusion as users click on a
student name and are taken to a different student's record.
After reading your reply, I decided to go in and play around with things to
see if I could get it to display properly. What I eventually discovered is
that the padding of the textbox can be adjusted in such a way that makes it
appear nearly normal - previously, it had been set to 2,0,2,0 (left, right,
top, bottom, respectively), and the settings that made it work were 2,2,3,6.
Now, it doesn't overlap anymore, but it does increase the white space of the
report.
We're running Reporting Services SP1, using Adobe Reader 6.0.1, though all
versions of Adobe I've tried seem to display the same thing.
Does that help?
Thanks,
~Scott
"Brian Welcker [MSFT]" wrote:
> I'm not seeing this. If you stick a border on the textbox, does the
> hyperlink go beyond the border of the textbox?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Scott" <petersonscott@.aasd.k12.wi.us> wrote in message
> news:4955c3e2.0409251012.659cfe54@.posting.google.com...
> > Hello -
> >
> > Long time listener, first time caller...
> >
> > I'm having trouble with something that I haven't seen posted, and I'm
> > wondering if this is a known issue, or if not, if there is any
> > possible solution.
> >
> > When I create a textbox and do not set the "Navigation/JumpToURL"
> > property, then run the report and export to PDF Format the textbox
> > renders correctly.
> >
> > If I do set a value in the "Navigation/JumpToURL" and then render the
> > report to PDF, the textbox renders correctly but the hyperlink extends
> > well below the bottom of the text (almost as if a LF was inserted).
> > There seems to be no way to adjust this behavior.
> >
> > It's worth mentioning that any other rendering format works properly -
> > but my users want the smooth, familiar PDF interface. The MHTML
> > format would work ok if IE didn't chop it up into multiple pages, and
> > Excel will do if there is no other way, but my first choice is PDF.
> >
> > I have tried setting all the properties I can think of, including line
> > height, textbox height, font size, table spacing, etc. to no avail.
> >
> > Any ideas?
> >
> > Thanks,
> >
> > ~Scott
>
>

Wednesday, March 28, 2012

PDF Formatting Problem

Dear All,
I have written a database application in SQL and am using SQL Reporting
Services (for the first time) to generate the reports. I have come across a
problem to which I cannot see a solutions.
As part of the database application we store 'Visit Reports' which sales
people use to record information about customer that they have visited. I
have written a report which should display each record on a seperate page.
However, when someone has written a particularly long report and this spans 2
or more pages the report formats correctly on the screen, but when you export
to pdf you have the header information on 1 page (let's say the top 1/3 of
the page) and the detail of the report moves on to the next page. What I
want to happen is for the report to move on to a new page when appropriate
(i.e. when the 1st page is full).
Has anyone come across this? Is there a solution?
Thanks in advance,
Mike SwannIt looks like your report is either wider than 8Â½" or taller than 11".
Remember to take into account the body of your report, and the height of
header and footer (if there are any). I keep a safety margin of Â½" to 1" to
avoid such issues.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Mike Swann" <MikeSwann@.discussions.microsoft.com> wrote in message
news:C806740E-EE41-4E99-9F71-4D0610E6B7F2@.microsoft.com...
> Dear All,
> I have written a database application in SQL and am using SQL Reporting
> Services (for the first time) to generate the reports. I have come across
> a
> problem to which I cannot see a solutions.
> As part of the database application we store 'Visit Reports' which sales
> people use to record information about customer that they have visited. I
> have written a report which should display each record on a seperate page.
> However, when someone has written a particularly long report and this
> spans 2
> or more pages the report formats correctly on the screen, but when you
> export
> to pdf you have the header information on 1 page (let's say the top 1/3 of
> the page) and the detail of the report moves on to the next page. What I
> want to happen is for the report to move on to a new page when appropriate
> (i.e. when the 1st page is full).
> Has anyone come across this? Is there a solution?
> Thanks in advance,
> Mike Swann|||Thanks for your response.
However, this is a UK A4 format and that is all correct. It is purely when
the report data forces it on to 2 pages. If there is only enough data for 1
page then it will convert to a pdf perfectly well.
Any other ideas?
Thanks
Mike
"Alain Quesnel" wrote:
> It looks like your report is either wider than 8Â½" or taller than 11".
> Remember to take into account the body of your report, and the height of
> header and footer (if there are any). I keep a safety margin of Â½" to 1" to
> avoid such issues.
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "Mike Swann" <MikeSwann@.discussions.microsoft.com> wrote in message
> news:C806740E-EE41-4E99-9F71-4D0610E6B7F2@.microsoft.com...
> >
> > Dear All,
> >
> > I have written a database application in SQL and am using SQL Reporting
> > Services (for the first time) to generate the reports. I have come across
> > a
> > problem to which I cannot see a solutions.
> >
> > As part of the database application we store 'Visit Reports' which sales
> > people use to record information about customer that they have visited. I
> > have written a report which should display each record on a seperate page.
> > However, when someone has written a particularly long report and this
> > spans 2
> > or more pages the report formats correctly on the screen, but when you
> > export
> > to pdf you have the header information on 1 page (let's say the top 1/3 of
> > the page) and the detail of the report moves on to the next page. What I
> > want to happen is for the report to move on to a new page when appropriate
> > (i.e. when the 1st page is full).
> >
> > Has anyone come across this? Is there a solution?
> >
> > Thanks in advance,
> >
> > Mike Swann
>|||You say that your header occupies 1/3 of the page. Did you try a smaller
header? Or no header at all?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Mike Swann" <MikeSwann@.discussions.microsoft.com> wrote in message
news:D41B4154-8BDA-4534-9B60-3619D81E995E@.microsoft.com...
> Thanks for your response.
> However, this is a UK A4 format and that is all correct. It is purely
> when
> the report data forces it on to 2 pages. If there is only enough data for
> 1
> page then it will convert to a pdf perfectly well.
> Any other ideas?
> Thanks
> Mike
>
> "Alain Quesnel" wrote:
>> It looks like your report is either wider than 8Â½" or taller than 11".
>> Remember to take into account the body of your report, and the height of
>> header and footer (if there are any). I keep a safety margin of Â½" to 1"
>> to
>> avoid such issues.
>>
>> Alain Quesnel
>> alainsansspam@.logiquel.com
>> www.logiquel.com
>>
>> "Mike Swann" <MikeSwann@.discussions.microsoft.com> wrote in message
>> news:C806740E-EE41-4E99-9F71-4D0610E6B7F2@.microsoft.com...
>> >
>> > Dear All,
>> >
>> > I have written a database application in SQL and am using SQL Reporting
>> > Services (for the first time) to generate the reports. I have come
>> > across
>> > a
>> > problem to which I cannot see a solutions.
>> >
>> > As part of the database application we store 'Visit Reports' which
>> > sales
>> > people use to record information about customer that they have visited.
>> > I
>> > have written a report which should display each record on a seperate
>> > page.
>> > However, when someone has written a particularly long report and this
>> > spans 2
>> > or more pages the report formats correctly on the screen, but when you
>> > export
>> > to pdf you have the header information on 1 page (let's say the top 1/3
>> > of
>> > the page) and the detail of the report moves on to the next page. What
>> > I
>> > want to happen is for the report to move on to a new page when
>> > appropriate
>> > (i.e. when the 1st page is full).
>> >
>> > Has anyone come across this? Is there a solution?
>> >
>> > Thanks in advance,
>> >
>> > Mike Swann
>>

Friday, March 23, 2012

pda *.sdf database

Everyone keeps telling me to ask this question in a differeent forum hope this time I have the right one.....!!!!!!!

I've created a small database to bind to my datagrid...*.sdf..

When I edit it all it gives me option's is to change column headings, I need to add 30 rows and don't know how!!!!!!!!!!!

The datagrid will be initialized in my form load sub. It will contain some static data (the user will not change anything just select)the user selectred a number from within the data grid that is then used later for a computation. Ideally I would like to not bind any database to this but initialize the entire grid within the form load procedure!!!!!!!!!!!! MSFlexGrid was much easier in this respect..........

No matter what i do it seems i can only add a header row to the database and I need to add 25 more rows How do I do that?

Mitch

Mitch5713 wrote:

Everyone keeps telling me to ask this question in a differeent forum hope this time I have the right one.....!!!!!!!
I've created a small database to bind to my datagrid...*.sdf..
When I edit it all it gives me option's is to change column headings, I need to add 30 rows and don't know how!!!!!!!!!!!
The datagrid will be initialized in my form load sub. It will contain some static data (the user will not change anything just select)the user selectred a number from within the data grid that is then used later for a computation. Ideally I would like to not bind any database to this but initialize the entire grid within the form load procedure!!!!!!!!!!!! MSFlexGrid was much easier in this respect..........
No matter what i do it seems i can only add a header row to the database and I need to add 25 more rows How do I do that?
Mitch

No, completely the wrong forum I'm afraid.

Correct me if I'm wrong but a DataGrid is a .Net control to be used in an ASP.net page or WinForms app, right? That has nothing to do with SSIS I'm afraid.

-Jamie

Wednesday, March 21, 2012

Paused? I don't have no stinking time for pausing!

Ok, why are my indices all paused?
Ran out of diskspace the other day. Stopped the Service, moved the indices
to another drive (had a little problem with 1-2 of them which I believe I've
fixed.)
Have restarted, and every full text index on the machine, including one in a
different DB is in a paused state.
They have data and a search on them returns data.
But select fultextcatalogproperty('foo','populatestatus') returns 2.
What's up with that?
(This is on SQL 2000. Thankfully finally I'm moving off of SQL 7.0).
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
MSSearch will pause for several reasons; low disk space; memory pressures,
or if you are running on battery power.
Use the gatherlog utility to see if it gives any more hints as to why they
are paused. You can find it in C:\Program Files\Common Files\MSSearch\Bin
and it is called gthrlog.vbs. Copy this file to %windir%\system32
Then open a command prompt and navigate to the C:\Program Files\Microsoft
SQL Server\MSSQL\FTData\SQLServer\Gatherlogs
then do a dir /od locate a recent log and do this
cscript.exe gthrlog.vbs SQL0000600005.1.gthr
Replacing SQL0000600005.1.gthr with the name of your log.
This should reveal more details as to why MSSearch is pausing.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Axfge.25950$ia6.18501@.twister.nyroc.rr.com...
> Ok, why are my indices all paused?
> Ran out of diskspace the other day. Stopped the Service, moved the
indices
> to another drive (had a little problem with 1-2 of them which I believe
I've
> fixed.)
> Have restarted, and every full text index on the machine, including one in
a
> different DB is in a paused state.
> They have data and a search on them returns data.
> But select fultextcatalogproperty('foo','populatestatus') returns 2.
> What's up with that?
> (This is on SQL 2000. Thankfully finally I'm moving off of SQL 7.0).
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O0yvusjVFHA.3176@.TK2MSFTNGP12.phx.gbl...
> MSSearch will pause for several reasons; low disk space; memory pressures,
> or if you are running on battery power.
> Use the gatherlog utility to see if it gives any more hints as to why they
> are paused. You can find it in C:\Program Files\Common Files\MSSearch\Bin
> and it is called gthrlog.vbs. Copy this file to %windir%\system32
> Then open a command prompt and navigate to the C:\Program Files\Microsoft
> SQL Server\MSSQL\FTData\SQLServer\Gatherlogs
> then do a dir /od locate a recent log and do this
> cscript.exe gthrlog.vbs SQL0000600005.1.gthr
> Replacing SQL0000600005.1.gthr with the name of your log.
>
Ok, to reduce the number of variables, I did this on the full text index in
a different database that I did not touch: (i.e. it's not due to me moving
stuff, etc...)
Results are:
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
5/10/2005 11:22:04 AM Add The gatherer has started
5/10/2005 11:22:12 AM Add The recovery has completed
5/10/2005 11:38:52 AM Add The gatherer has started
5/10/2005 11:39:04 AM Add The recovery has completed
5/10/2005 12:13:52 PM Add The gatherer has started
5/10/2005 12:14:02 PM Add The recovery has completed
5/10/2005 1:28:02 PM Add The gatherer has started
5/10/2005 1:28:10 PM Add The recovery has completed
5/10/2005 2:03:02 PM Add The gatherer has started
5/10/2005 2:03:12 PM Add The recovery has completed
Yet, the index is still paused.
Just tried to STOP the population on this paused index.
The tail of the new gather file shows:
5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004418 Add
Error fe
tching URL, (80040d54 - The filtering has been aborted because of a user
action,
such as stopping the crawl. )
5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004c9a Add
Error fe
tching URL, (80040d54 - The filtering has been aborted because of a user
action,
such as stopping the crawl. )
5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/000043e6 Add
Error fe
tching URL, (80040d54 - The filtering has been aborted because of a user
action,
such as stopping the crawl. )
5/11/2005 5:59:14 PM Add Completed Incremental crawl
And the index still shows itself as paused.
Looking at a gather file on the index (which I did move) in the original DB:
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
5/10/2005 11:22:04 AM Add The gatherer has started
5/10/2005 11:22:10 AM Add The initialization has completed
5/10/2005 11:38:52 AM Add The gatherer has started
5/10/2005 11:39:00 AM Add The initialization has completed
5/10/2005 12:13:52 PM Add The gatherer has started
5/10/2005 12:13:58 PM Add The initialization has completed
5/10/2005 1:28:02 PM Add The gatherer has started
5/10/2005 1:28:08 PM Add The initialization has completed
5/10/2005 2:03:02 PM Add The gatherer has started
5/10/2005 2:03:08 PM Add The initialization has completed
And
select fulltextcatalogproperty('foo_bar_1998','populatest atus') as
populatestatus , count(*) as '1998',
fulltextcatalogproperty('foo_bar_1998','itemcount' ) as itemcount,
fulltextcatalogproperty('foo_bar_1998','IndexSize' ) as IndexSize,
fulltextcatalogproperty('foo_bar_1998','UniqueKeyC ount') as
UniqueKeycount,fulltextcatalogproperty('foo_bar_19 98','Logsize') as
LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge') as
PopulateCompletionAge from foo_bar_1998 (nolock)
returns:
Populate status 1998 itemcount, indexssize, uniquekeycount,
logsize populatecompletionage
2 441 442 2
72004 328 482772780
Now, this index had been fully populated before any of these problems
happened, so it should be fine. (i.e. no reason to pause, etc.)
I'm bewildered at this point.
Any other thoughts. (I mean worse comes to worse I'll simply destroy the
indices and rebuild them, but then I'll lose a week or two of time waiting
for the larger ones.)

> This should reveal more details as to why MSSearch is pausing.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:Axfge.25950$ia6.18501@.twister.nyroc.rr.com...
> indices
> I've
in
> a
>
|||I am not sure what you mean by restarted in your original post? Do you mean
a reboot? If not, I would reboot your server to see if this clears the
condition. It is baffling.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:cnvge.26735$ia6.20761@.twister.nyroc.rr.com... [vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O0yvusjVFHA.3176@.TK2MSFTNGP12.phx.gbl...
pressures,[vbcol=seagreen]
they[vbcol=seagreen]
Files\MSSearch\Bin[vbcol=seagreen]
Files\Microsoft
> Ok, to reduce the number of variables, I did this on the full text index
in
> a different database that I did not touch: (i.e. it's not due to me moving
> stuff, etc...)
> Results are:
> Microsoft (R) Windows Script Host Version 5.6
> Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
> 5/10/2005 11:22:04 AM Add The gatherer has started
> 5/10/2005 11:22:12 AM Add The recovery has completed
> 5/10/2005 11:38:52 AM Add The gatherer has started
> 5/10/2005 11:39:04 AM Add The recovery has completed
> 5/10/2005 12:13:52 PM Add The gatherer has started
> 5/10/2005 12:14:02 PM Add The recovery has completed
> 5/10/2005 1:28:02 PM Add The gatherer has started
> 5/10/2005 1:28:10 PM Add The recovery has completed
> 5/10/2005 2:03:02 PM Add The gatherer has started
> 5/10/2005 2:03:12 PM Add The recovery has completed
>
> Yet, the index is still paused.
> Just tried to STOP the population on this paused index.
> The tail of the new gather file shows:
> 5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004418 Add
> Error fe
> tching URL, (80040d54 - The filtering has been aborted because of a user
> action,
> such as stopping the crawl. )
> 5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004c9a Add
> Error fe
> tching URL, (80040d54 - The filtering has been aborted because of a user
> action,
> such as stopping the crawl. )
> 5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/000043e6 Add
> Error fe
> tching URL, (80040d54 - The filtering has been aborted because of a user
> action,
> such as stopping the crawl. )
> 5/11/2005 5:59:14 PM Add Completed Incremental crawl
> And the index still shows itself as paused.
>
> Looking at a gather file on the index (which I did move) in the original
DB:[vbcol=seagreen]
> Microsoft (R) Windows Script Host Version 5.6
> Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
> 5/10/2005 11:22:04 AM Add The gatherer has started
> 5/10/2005 11:22:10 AM Add The initialization has completed
> 5/10/2005 11:38:52 AM Add The gatherer has started
> 5/10/2005 11:39:00 AM Add The initialization has completed
> 5/10/2005 12:13:52 PM Add The gatherer has started
> 5/10/2005 12:13:58 PM Add The initialization has completed
> 5/10/2005 1:28:02 PM Add The gatherer has started
> 5/10/2005 1:28:08 PM Add The initialization has completed
> 5/10/2005 2:03:02 PM Add The gatherer has started
> 5/10/2005 2:03:08 PM Add The initialization has completed
> And
> select fulltextcatalogproperty('foo_bar_1998','populatest atus') as
> populatestatus , count(*) as '1998',
> fulltextcatalogproperty('foo_bar_1998','itemcount' ) as itemcount,
> fulltextcatalogproperty('foo_bar_1998','IndexSize' ) as IndexSize,
> fulltextcatalogproperty('foo_bar_1998','UniqueKeyC ount') as
> UniqueKeycount,fulltextcatalogproperty('foo_bar_19 98','Logsize') as
> LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge') as
> PopulateCompletionAge from foo_bar_1998 (nolock)
> returns:
> Populate status 1998 itemcount, indexssize, uniquekeycount,
> logsize populatecompletionage
> 2 441 442 2
> 72004 328 482772780
> Now, this index had been fully populated before any of these problems
> happened, so it should be fine. (i.e. no reason to pause, etc.)
>
> I'm bewildered at this point.
> Any other thoughts. (I mean worse comes to worse I'll simply destroy the
> indices and rebuild them, but then I'll lose a week or two of time waiting
> for the larger ones.)
>
>
> message
believe[vbcol=seagreen]
one
> in
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OImpBB2VFHA.2196@.TK2MSFTNGP09.phx.gbl...
> I am not sure what you mean by restarted in your original post? Do you
mean
> a reboot? If not, I would reboot your server to see if this clears the
> condition. It is baffling.
Well, restarted means just that, stopping and restarting all the services in
question.
No joy.
So today I rebooted the box, and still no joy.
So I completely removed one index, including the catalog and recreated it
from scratch.
Now at least the status is 1 - population in progress.
Yet after 4 hours, nothing has been indexed.
Very strange.

> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:cnvge.26735$ia6.20761@.twister.nyroc.rr.com...
> pressures,
> they
> Files\MSSearch\Bin
> Files\Microsoft
> in
moving[vbcol=seagreen]
> DB:
as[vbcol=seagreen]
waiting[vbcol=seagreen]
> believe
> one
7.0).
>
|||when you run profiler does it show lots of activity from PKM, specifically
calls to sp_fulltext_getdata.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Awtie.39724$ia6.30323@.twister.nyroc.rr.com...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OImpBB2VFHA.2196@.TK2MSFTNGP09.phx.gbl...
> mean
> Well, restarted means just that, stopping and restarting all the services
in[vbcol=seagreen]
> question.
> No joy.
> So today I rebooted the box, and still no joy.
>
> So I completely removed one index, including the catalog and recreated it
> from scratch.
> Now at least the status is 1 - population in progress.
> Yet after 4 hours, nothing has been indexed.
> Very strange.
>
> message
why[vbcol=seagreen]
index[vbcol=seagreen]
> moving
user[vbcol=seagreen]
user[vbcol=seagreen]
user[vbcol=seagreen]
original[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge')[vbcol=seagreen]
> as
the[vbcol=seagreen]
> waiting
the[vbcol=seagreen]
including[vbcol=seagreen]
2.
> 7.0).
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eEZZSm0WFHA.2128@.TK2MSFTNGP14.phx.gbl...
> when you run profiler does it show lots of activity from PKM, specifically
> calls to sp_fulltext_getdata.
Well, I sorta "gave in"
I had a suspicion that a 4-5 of the full-text indices I moved may have been
the source of the problem (because I ended up having to hand edit their
registry entries.)
Removing those and the remaining ones started to work.
So now just running an incremental build on the ones that are behind.
Thanks for the help Hilary.

> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:Awtie.39724$ia6.30323@.twister.nyroc.rr.com...
services[vbcol=seagreen]
> in
it[vbcol=seagreen]
> why
> index
> user
> user
> user
crawl[vbcol=seagreen]
> original
> completed
> completed
> completed
> completed
> completed
> LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge')
uniquekeycount,[vbcol=seagreen]
problems[vbcol=seagreen]
> the
in[vbcol=seagreen]
> the
> including
returns
> 2.
>

Tuesday, March 20, 2012

Path of Tasks

Hi,

no results during my search of the web, maybe one of yours can help.

Is there a possibility (during design time is OK) to find out, which TaskHost is before and after a CustomTask (by wiring in ssis-designer) and which type of wire it is (Error, Finish, Success).

Maybe the order of executables is providing this information, when there are no props or methods (and i found none), but then you don't know anything about the type.

Any ideas?

Thanks

Found it out by myself.

TaskHost.Parent.PrecedenceConstraints gives a list of connections between the Executables.

|||

Hi,

but there is no possibility to check via Validate().

Is there a possibility to tell a task in designer, that it has been connected or unconnected. And from this point to have access to the PrecedenceConstraints-Property / TaskHost

Thanks

Thorsten

patching

What is the differnce between a Service Pack and Security Patches that come
out over time? What about Hot Fixes? Is Microsoft the only sources of
patches? Should I test the patch on a demo server before I place it in
production or should I just trust Microsoft?Hi
A SP is a fully end to end tested release that implements fixes and
enhancements to the base product. SP's are cumulative (then contain all
prior SP's fixes plus more).
A Security patch is a hotfix that addresses high priority fixes that if not
applied, could lead to compromised security.
A Hotfix is an update, issued to fix a specific problem that somebody had
encountered and Microsoft needs to fix as it could affect other users. A
Hotfix has limited support and does not have the massive amount of
regression testing as a Service Pack would as it is not possible in the
short time frames needed.
NEVER get a hotfix/SP/Security pack from any other source than Microsoft.
Microsoft never e-mails files either. There is always a link to their site
from where you can download it. You never know who has tampered with the
files if you don't get them yourself from MS.
With any release, always put it on test servers before production ones.
Functionality can change or behavior you were used to could be altered in a
way that your applications were not expecting.
www.microsoft.com/sql
www.microsoft.com/security
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:FECD7708-9C36-4810-9EF8-D011ADB6B021@.microsoft.com...
> What is the differnce between a Service Pack and Security Patches that
come
> out over time? What about Hot Fixes? Is Microsoft the only sources of
> patches? Should I test the patch on a demo server before I place it in
> production or should I just trust Microsoft?|||Hello Mike,
thanks for the answer. I am getting flack for wanting to test Service Packs
before they go into production.
Rich
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> A SP is a fully end to end tested release that implements fixes and
> enhancements to the base product. SP's are cumulative (then contain all
> prior SP's fixes plus more).
> A Security patch is a hotfix that addresses high priority fixes that if no
t
> applied, could lead to compromised security.
> A Hotfix is an update, issued to fix a specific problem that somebody had
> encountered and Microsoft needs to fix as it could affect other users. A
> Hotfix has limited support and does not have the massive amount of
> regression testing as a Service Pack would as it is not possible in the
> short time frames needed.
> NEVER get a hotfix/SP/Security pack from any other source than Microsoft.
> Microsoft never e-mails files either. There is always a link to their site
> from where you can download it. You never know who has tampered with the
> files if you don't get them yourself from MS.
> With any release, always put it on test servers before production ones.
> Functionality can change or behavior you were used to could be altered in
a
> way that your applications were not expecting.
> www.microsoft.com/sql
> www.microsoft.com/security
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:FECD7708-9C36-4810-9EF8-D011ADB6B021@.microsoft.com...
> come
>
>|||Hi
SP4 will break applications if they use XP functions that access the
registry. How much fun will you have if your app uses it and it dies because
of this?
If they don't accept that you need to test compatibility, then maybe you are
working for the wrong company.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E9805C2D-3522-4194-BCB4-58307D1E8422@.microsoft.com...
> Hello Mike,
> thanks for the answer. I am getting flack for wanting to test Service
Packs[vbcol=seagreen]
> before they go into production.
> Rich
> "Mike Epprecht (SQL MVP)" wrote:
>
not[vbcol=seagreen]
had[vbcol=seagreen]
Microsoft.[vbcol=seagreen]
site[vbcol=seagreen]
in a[vbcol=seagreen]
of[vbcol=seagreen]
in[vbcol=seagreen]|||Always test service packs before putting them into production. Although
service packs go through more testing than hotfixes there are so many
different environments out there that unless you test it in your specific
environment you can't be sure you're not going to hit an issue. Certainly in
our environment we have come across some very weird bugs just because of how
we have our servers configured (weirdest one yet was down to having an
affinity mask that was a multiple of 7 <g> )
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
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E9805C2D-3522-4194-BCB4-58307D1E8422@.microsoft.com...[vbcol=seagreen]
> Hello Mike,
> thanks for the answer. I am getting flack for wanting to test Service
> Packs
> before they go into production.
> Rich
> "Mike Epprecht (SQL MVP)" wrote:
>

Friday, March 30, 2012

Peer-to-peer replication and Oracle real application clusters

Peak During Time Period

Peak During Time Period

Peak During Time Period

Peak During Time Period

PDF Textbox/Hyperlink Issue

Wednesday, March 28, 2012

PDF Formatting Problem

Friday, March 23, 2012

pda *.sdf database

Wednesday, March 21, 2012

Paused? I don't have no stinking time for pausing!

Tuesday, March 20, 2012

Path of Tasks

patching

Passing result sets

Blog Archive

About Me