Finding a creator of object

Hi all,

whenever dbo is prifixed with the create script the owner becomes dbo.
If the below script
run by the sam the owner becomes dbo

create proc dbo.test
as
print 'hello'

Is there any place where SQL server keeps the record of creator?shiju (shiju.samuel@.gmail.com) writes:

Quote:

Originally Posted by

whenever dbo is prifixed with the create script the owner becomes dbo.
If the below script
run by the sam the owner becomes dbo
>
create proc dbo.test
as
print 'hello'
>
Is there any place where SQL server keeps the record of creator?

No. You would have to have trace running that captutes the Object:Created
event. In SQL 2005 you could also set up a DLL trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Find subsequent days

I have the following table:
CREATE TABLE [dbo].[ShortStay](
[SS_ID] [int] IDENTITY(1,1) NOT NULL,
[SS_WLID] [int] NULL,
[SS_From] [smalldatetime] NOT NULL,
[SS_Till] [smalldatetime] NOT NULL)
There are these records for example:
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-05', '2006-05-21')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-02', '2006-05-04')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-22', '2006-05-25')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (4, '2006-05-05', '2006-05-21')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-26', '2006-05-29')
I'm trying to find a query that returns the number of consequent days
based on a certain date:
So if I input: '2006-05-09' and SS_WLID = 3
then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
You get 2006-05-02 because the SS_Till is the day before 2006-05-05
that's why this row is ok.
You get 2006-05-29 because:
2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
2006-05-29
I hope this makes sense. So the days have to be consequent in order to
be able to be part of the count.
I have been trying to join the table to itself but I'm getting nowhere.
Thanks in advance,
Stijn Verrept.Consider creating an auxiliary Calendar table.
http://www.aspfaq.com/show.asp?id=2519
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...
>I have the following table:
> CREATE TABLE [dbo].[ShortStay](
> [SS_ID] [int] IDENTITY(1,1) NOT NULL,
> [SS_WLID] [int] NULL,
> [SS_From] [smalldatetime] NOT NULL,
> [SS_Till] [smalldatetime] NOT NULL)
>
> There are these records for example:
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-02', '2006-05-04')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-22', '2006-05-25')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (4, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-26', '2006-05-29')
> I'm trying to find a query that returns the number of consequent days
> based on a certain date:
> So if I input: '2006-05-09' and SS_WLID = 3
> then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
> You get 2006-05-02 because the SS_Till is the day before 2006-05-05
> that's why this row is ok.
> You get 2006-05-29 because:
> 2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
> 2006-05-29
> I hope this makes sense. So the days have to be consequent in order to
> be able to be part of the count.
> I have been trying to join the table to itself but I'm getting nowhere.
>
> --
> Thanks in advance,
> Stijn Verrept.|||Roji. P. Thomas wrote:

> Consider creating an auxiliary Calendar table.
> http://www.aspfaq.com/show.asp?id=2519
Thanks for the link, I already have a Calendar table but don't see how
this solves the subsequent problem, it's in none of the examples on the
site.
Kind regards,
Stijn Verrept.|||Stijn,
google up "How to simplify a query using a calendar table", hope that
helps|||How about something like this (note that it looks for consecutive days,
defined exactly as SS_From = prior SS_Till + 1). Hours, minutes, etc.,
aren't taken into consideration. Neither are overlapping dates.
DECLARE @.date DATETIME
SELECT @.date = '2006-05-09'
DECLARE @.SS_ID INT
SELECT @.SS_ID = 3
SELECT 'Begin' AS DateType, s1.Start
FROM (
SELECT MIN(SS_From) AS Start
FROM ShortStay
WHERE @.date BETWEEN SS_From AND SS_Till
AND SS_WLID = @.SS_ID
) s1
UNION ALL
SELECT 'End', s2.Finish
FROM
(
SELECT MAX(s1.SS_Till) AS Finish
FROM ShortStay s1, ShortStay s2
WHERE s1.SS_From = s2.SS_Till + 1
AND s1.SS_From <>
(
SELECT MIN(SS_From)
FROM ShortStay
WHERE SS_WLID = @.SS_ID
AND @.date BETWEEN SS_From AND SS_Till
)
AND s1.SS_WLID = s2.SS_WLID
AND s1.SS_WLID = @.SS_ID
) s2
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...
>I have the following table:
> CREATE TABLE [dbo].[ShortStay](
> [SS_ID] [int] IDENTITY(1,1) NOT NULL,
> [SS_WLID] [int] NULL,
> [SS_From] [smalldatetime] NOT NULL,
> [SS_Till] [smalldatetime] NOT NULL)
>
> There are these records for example:
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-02', '2006-05-04')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-22', '2006-05-25')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (4, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-26', '2006-05-29')
> I'm trying to find a query that returns the number of consequent days
> based on a certain date:
> So if I input: '2006-05-09' and SS_WLID = 3
> then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
> You get 2006-05-02 because the SS_Till is the day before 2006-05-05
> that's why this row is ok.
> You get 2006-05-29 because:
> 2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
> 2006-05-29
> I hope this makes sense. So the days have to be consequent in order to
> be able to be part of the count.
> I have been trying to join the table to itself but I'm getting nowhere.
>
> --
> Thanks in advance,
> Stijn Verrept.|||Hi There,
You may like to try this!!
I assume that SS_From is smaller than SS_Till
Select Min(SS_From),Max(SS_Till) from ShortStay where SS_WLID=3
And convert(varchar(6),'20060509',112) =
convert(varchar(6),SS_From,112)
And convert(varchar(6),'20060509',112) =
convert(varchar(6),SS_Till,112)
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Mike C# wrote:
> How about something like this (note that it looks for consecutive days,
> defined exactly as SS_From = prior SS_Till + 1). Hours, minutes, etc.,
> aren't taken into consideration. Neither are overlapping dates.
> DECLARE @.date DATETIME
> SELECT @.date = '2006-05-09'
> DECLARE @.SS_ID INT
> SELECT @.SS_ID = 3
> SELECT 'Begin' AS DateType, s1.Start
> FROM (
> SELECT MIN(SS_From) AS Start
> FROM ShortStay
> WHERE @.date BETWEEN SS_From AND SS_Till
> AND SS_WLID = @.SS_ID
> ) s1
> UNION ALL
> SELECT 'End', s2.Finish
> FROM
> (
> SELECT MAX(s1.SS_Till) AS Finish
> FROM ShortStay s1, ShortStay s2
> WHERE s1.SS_From = s2.SS_Till + 1
> AND s1.SS_From <>
> (
> SELECT MIN(SS_From)
> FROM ShortStay
> WHERE SS_WLID = @.SS_ID
> AND @.date BETWEEN SS_From AND SS_Till
> )
> AND s1.SS_WLID = s2.SS_WLID
> AND s1.SS_WLID = @.SS_ID
> ) s2
> "Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
> news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...|||Mike C# wrote:

> How about something like this (note that it looks for consecutive
> days, defined exactly as SS_From = prior SS_Till + 1). Hours,
> minutes, etc., aren't taken into consideration. Neither are
> overlapping dates.
> DECLARE @.date DATETIME
> SELECT @.date = '2006-05-09'
> DECLARE @.SS_ID INT
> SELECT @.SS_ID = 3
Hi Mike,
Thanks for the reply! This one is very close but it doesn't look for
any prior consecutive rows.
In the examply I gave:
So if I input: '2006-05-09' and SS_WLID = 3
then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
Your query returns '2006-05-05' and '2006-05-29'
It doesn't consider the row:
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-02', '2006-05-04')
Thanks again,
Stijn Verrept.|||People will tell you to use a Calendar table. But your real problem is
a bad design. You have no key (IDENTITY cannot ever be a key! Quit
mimicing a sequential file), and your "wl_id" can be NULL so the query
is impossible to answer. You also have no constraints and use
proprietary data types. Then on top of all of that, you have attribute
splitting -- the fact of a continous stay is spread over many rows.
Why did you use a singular name for a table with more than one element
in it? You have no idea what an identifier is and stuck it on
everything. Your table should look more like this:
CREATE TABLE ShortStays
(wl_id INTEGER NOT NULL,
arrival_date DATETIME NOT NULL,
depart_date DATETIME NOT NULL,
CHECK(arrival_date < depart_date),
PRIMARY KEY (wl_id, arrival_date));
When a stay is extended, you update it instead of mimicking a paper
hotel register form. Ideally, you should have constraint to prevent
overlaps, but that is hard to do in SQL Server since it still lacks
much of the SQL-92 features. You can do it with a TRIGGER or with an
updatable VIEW that has WITH CHECK OPTION clause and a Calendar table.
Please learn the differences between rows and records. Fail to know
that lead to attribute splitting.|||--CELKO-- wrote:

> People will tell you to use a Calendar table. But your real problem
> is a bad design. You have no key (IDENTITY cannot ever be a key!
> Quit mimicing a sequential file), and your "wl_id" can be NULL so the
> query is impossible to answer. You also have no constraints and use
> proprietary data types. Then on top of all of that, you have
> attribute splitting -- the fact of a continous stay is spread over
> many rows.
Well Joe, the table creation script given here is just a simplified
version of the real table so hold your horses. People can stay in
different rooms and can be transferred from one room to another so
updating instead of inserting is not an option here. I didn't include
the column of the room because for this query it is irrelevant!

> When a stay is extended, you update it instead of mimicking a paper
> hotel register form. Ideally, you should have constraint to prevent
> overlaps, but that is hard to do in SQL Server since it still lacks
> much of the SQL-92 features. You can do it with a TRIGGER or with an
> updatable VIEW that has WITH CHECK OPTION clause and a Calendar table.
I already have a trigger that prevents overlap so don't worry about
that.

> Please learn the differences between rows and records. Fail to know
> that lead to attribute splitting.
LOL
"In the context of a relational database, a rowalso called a record or
tuplerepresents a single, implicitly structured data item in a table."
Wikipedia.
Will learning the naming difference really solve my query? I think not.
Thanks for your very helpful reply!
Kind regards,
Stijn Verrept.

find available time slot

I have a table that looks like:
CREATE TABLE [dbo].[UserAgenda] (
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
) ON [PRIMARY]
GO
and I would like to find a time gap for the amount of time I am looking
for.
For example, I want the next available hour in the list or the next
available 15 minutes.
Any help would be appreciated.
Thanks
Richard MeetzeAm I correct that "next available" means a period of time that won't
overlap the period between StartTime and EndTime on any row in the
table? Try this:
DECLARE @.m INTEGER
SET @.m = 10
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
David Portas
SQL Server MVP
--|||David - you are correct, thanks for the help.
I have another question if you or anyone else it up for it.
Using the same table schema as above, I have the following data:
Start End
2005-01-01 08:15:00.000 2005-01-01 08:45:00.000
2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
Now I want to place an item in from 8:30 to 8:45 and I want my end
result to look like:
Start End
2005-01-01 08:15:00.000 2005-01-01 08:30:00.000
2005-01-01 08:30:00.000 2005-01-01 08:45:00.000
2005-01-01 08:45:00.000 2005-01-01 09:00:00.000
2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
I had to insert the new time in a current time and split the other time
to two parts and make it fit. Does make since?
Thanks
Richard Meetze|||David,
I think will should check for existence of rows between the matching ones.
Somethnig like:
...
and not exists (select * from UserAgenda as c where c.starttime > a.endtime
and c.endtime < b.starttime)
Example:
create table UserAgenda (
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL
)
insert into UserAgenda values('2005-02-23T08:30:00', '2005-02-23T08:35:00')
insert into UserAgenda values('2005-02-23T08:36:00', '2005-02-23T08:46:00')
insert into UserAgenda values('2005-02-23T08:47:00', '2005-02-23T08:57:00')
DECLARE @.m INTEGER
SET @.m = 10
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
and not exists (select * from UserAgenda as c where c.starttime > a.endtime
and c.endtime < b.starttime)
drop table UserAgenda
go
AMB
"David Portas" wrote:

> Am I correct that "next available" means a period of time that won't
> overlap the period between StartTime and EndTime on any row in the
> table? Try this:
> DECLARE @.m INTEGER
> SET @.m = 10
> SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
> FROM UserAgenda AS A
> LEFT JOIN UserAgenda AS B
> ON A.endtime <= B.starttime
> WHERE DATEDIFF(MI,A.endtime,
> COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
> --
> David Portas
> SQL Server MVP
> --
>|||See if it hepls you
create table UserAgenda
(
StartTime datetime not null,
EndTime datetime not null
)
insert into UserAgenda(StartTime,EndTime)values ('20000610 10:00','20000610
10:15')
insert into UserAgenda(StartTime,EndTime)values ('20000610 10:30','20000610
10:50')
insert into UserAgenda(StartTime,EndTime)values ('20000610 11:00','20000610
11:25')
insert into UserAgenda(StartTime,EndTime)values ('20000610 11:45','20000610
11:55')
insert into UserAgenda(StartTime,EndTime)values ('20000610 12:57','20000610
13:00')
insert into UserAgenda(StartTime,EndTime)values ('20000610 13:15','20000610
13:30')
insert into UserAgenda(StartTime,EndTime)values ('20000610 14:04','20000610
14:40')
SELECT
StartTime,
ISNULL(
(SELECT MIN(EndTime)
FROM UserAgenda AS S3
WHERE S3.StartTime >= S1.StartTime
AND ISNULL(
DATEDIFF(
minute,
S3.EndTime,
(SELECT MIN(StartTime)
FROM UserAgenda AS S4
WHERE S4.StartTime > S3.EndTime)), 15) <= 15),
EndTime) AS EndTime
FROM UserAgenda AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTime)
FROM UserAgenda AS S2
WHERE S2.EndTime < S1.StartTime),S1.StartTime),15)<= 15
<meetze@.gmail.com> wrote in message
news:1109184358.837536.30570@.f14g2000cwb.googlegroups.com...
> David - you are correct, thanks for the help.
> I have another question if you or anyone else it up for it.
> Using the same table schema as above, I have the following data:
> Start End
> 2005-01-01 08:15:00.000 2005-01-01 08:45:00.000
> 2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
> 2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
> 2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
> 2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
> 2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
> Now I want to place an item in from 8:30 to 8:45 and I want my end
> result to look like:
> Start End
> 2005-01-01 08:15:00.000 2005-01-01 08:30:00.000
> 2005-01-01 08:30:00.000 2005-01-01 08:45:00.000
> 2005-01-01 08:45:00.000 2005-01-01 09:00:00.000
> 2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
> 2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
> 2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
> 2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
> 2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
> I had to insert the new time in a current time and split the other time
> to two parts and make it fit. Does make since?
> Thanks
> Richard Meetze
>