How to find row value not present

Hi All,
I have table with 2 cols 1)ObjectName 2)Owner (omlitted other cols for
simplicity)
ObjectName Owner
obj1 own1
obj1 own2
obj2 own1
obj2 own2
obj3 own1
obj4 own2
if ObjectName is present for own1 It should also be there for own2 &
other way around if ObjectName is present for own2 it should also be
there for own1. I want to find out rows which does not meet such
criteria. like in above example last 2 rows for obj3 & obj4.
As obj3 is present for only own1 & obj4 is present for own2 only.
Could any one guide me how to go about it?
Thanks in advance for help
VinodVinod
CREATE TABLE dbo.Stuffs (
obj_name VARCHAR(4) NOT NULL
,owner VARCHAR(4) NOT NULL
CHECK (owner IN ('own1', 'own2'))
,PRIMARY KEY (obj_name, owner)
);
INSERT INTO Stuffs VALUES('obj1', 'own1');
INSERT INTO Stuffs VALUES('obj1', 'own2');
INSERT INTO Stuffs VALUES('obj2', 'own1');
INSERT INTO Stuffs VALUES('obj2', 'own2');
INSERT INTO Stuffs VALUES('obj3', 'own1');
INSERT INTO Stuffs VALUES('obj4', 'own2');
SELECT obj_name FROM Stuffs
GROUP BY obj_name
HAVING COUNT(owner) <> 2
;
Joe|||create table foo (objectname varchar(5), owner varchar(5))
insert into foo values ('obj1','own1')
insert into foo values ('obj1','own2')
insert into foo values ('obj2','own1')
insert into foo values ('obj2','own2')
insert into foo values ('obj3','own1')
insert into foo values ('obj4','own2')
insert into foo values ('obj4','own3')
insert into foo values ('obj4','own3')
select every.*
from
(select obj.objectname, own.owner from (select distinct objectname from foo)
obj
cross join (select distinct owner from foo) own ) every
where objectname+owner not in (select objectname+owner from foo)
"vinod" <vinod.patil1@.gmail.com> wrote in message
news:1132180115.463350.278900@.g44g2000cwa.googlegroups.com...
> Hi All,
> I have table with 2 cols 1)ObjectName 2)Owner (omlitted other cols for
> simplicity)
> ObjectName Owner
> obj1 own1
> obj1 own2
> obj2 own1
> obj2 own2
> obj3 own1
> obj4 own2
> if ObjectName is present for own1 It should also be there for own2 &
> other way around if ObjectName is present for own2 it should also be
> there for own1. I want to find out rows which does not meet such
> criteria. like in above example last 2 rows for obj3 & obj4.
> As obj3 is present for only own1 & obj4 is present for own2 only.
> Could any one guide me how to go about it?
> Thanks in advance for help
> Vinod
>|||Why do you think that EVERY is a keyword in SQL?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132196174.489954.44330@.g14g2000cwa.googlegroups.com...
> Why do you think that EVERY is a keyword in SQL?
>
He doesn't think that. He thinks it's a table alias, because that
is what it is being used for.
Sincerely,
Chris O.

How to find owner of objects

I need to find the owner of jobs and stored procedures that have been
created and change all of them to be owned by sa. I have recently been
getting a randomly occuring error in some job execution due to SQL not being
able to verify if an object owner really has access.
This the notification message I receive:
STATUS: Failed
MESSAGES: The job failed. Unable to determine if the owner (NCN\dbohannon)
of job Restore DukeEDI_TLog has server access (reason: Could not obtain
information about Windows NT group/user 'NCN\dbohannon'. [SQLSTATE 42000]
(Error 8198)).
I don't understand what would cause this since my account is a sysadmin and
it is the same security that these jobs have always run under.
Thanks in advance,
DeborahThe problem you're running into is not actually a SQL issue rather a error
returning information from the DC.
See the following kb article;
241643 PRB: 8198 Error Message Returned from Job Owned by Windows NT
http://support.microsoft.com/?id=241643
Changing the job ownership to a standard SQL account forces a change in
code path, which doesn't require a query to the DC.
Each job can be changed to make the job owner someone other than the
domain\user in Enterprise Manager.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Can you please tell me why this error would be intermittent? The scheduled
job has run successfully for weeks, and then I get this error. I then ran
the job again and it ran with no problem. I'm thinking if it were truly an
account problem then it would be so every time it is run.
Plus, the server it is running on was never anything besides Windows 2000
Server.
Thanks
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:vvkveL72DHA.2588@.cpmsftngxa08.phx.gbl...

quote:

---

> The problem you're running into is not actually a SQL issue rather a error
> returning information from the DC.
> See the following kb article;
> 241643 PRB: 8198 Error Message Returned from Job Owned by Windows NT
> http://support.microsoft.com/?id=241643
> Changing the job ownership to a standard SQL account forces a change in
> code path, which doesn't require a query to the DC.
> Each job can be changed to make the job owner someone other than the
> domain\user in Enterprise Manager.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

---

|||What I have seen in the past is that the calls we make to the Domain
Controller to enumerate the groups may fail intermittantly. So, it is not
the OS that SQL Server is running on, rather the communication between SQL
and the DC. So, if the DC is having problems or is busy, the query may
start to fail.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regardsselect name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:
> I have an application wich generates database objects (mostly views) within
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regards
select name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:

> I have an application wich generates database objects (mostly views) within
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards
|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regardsselect name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:

> I have an application wich generates database objects (mostly views) withi
n
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)