|
something along these lines should work:
SELECT * FROM `CPtest` WHERE optio = 1 AND NAME NOT IN (SELECT name FROM `CPtest` WHERE NOT optio = 1)
|
|
|
|
|
Take this example
declare @t table(username varchar(50),userid varchar(100))
insert into @t
select 'username1','a' union all select 'username1','b' union all
select 'username1','c' union all select 'username1','d' union all
select 'username2','a' union all select 'username3','b' union all
select 'username3','c' union all select 'username3','d' union all
select 'username4','a' union all select 'username4','b' union all
select 'username4','c' union all select 'username5','d' union all
select 'username6','a' union all select 'username7','b' union all
select 'username8','c' union all select 'username8','d'
username userid
username1 a
username1 b
username1 c
username1 d
username2 a
username3 b
username3 c
username3 d
username4 a
username4 b
username4 c
username5 d
username6 a
username7 b
username8 c
username8 d
I want to find the users who has userid only 'a'. So in this case the desired output will be username2 and username6
Query 1:
select username from @t
except
select username from @t
where userid in('b','c','d')
Query 2:
select t1.username from @t t1
left join (select username from @t where userid in('b','c','d')) x
on x.username = t1.username
where x.username is null
Query 3:
select username from (
select username,
stuff((select ',' + CAST(t2.userid as varchar(max)) from @t t2
where t2.username = t1.username for xml path('')),1,1,'') as userids
from @t t1
group by username )X
where X.userids = 'a'
In all the 3 cases the output is
username
username2
username6
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi,
The following sample failed to create the DSN,
void main()
{
//As my DB path will change frequently i am using this method.
bool aDSNCreated = false;
char temp[MAX_PATH];
sprintf(temp,"DSN=SAMPLE\0DBQ=D:\\SAMPLE.mdb\0");
aDSNCreated = SQLConfigDataSource(NULL, ODBC_ADD_SYS_DSN, "Microsoft Access Driver (*.mdb)\0", temp);
printf("%d",aDSNCreated );
}
whereas when I execute the below code; it execute successfully.Can anyone help in this why its happening?
void main()
{
bool aDSNCreated = false;
aDSNCreated = SQLConfigDataSource(NULL, ODBC_ADD_SYS_DSN, "Microsoft Access Driver(*.mdb)\0","DSN=SAMPLE\0DBQ=D:\\SAMPLE.mdb\0");
printf("%d",aDSNCreated );
}
|
|
|
|
|
Hi all,
I have table Demo:
CREATE TABLE Demo
(
ID int PRIMARY KEY,
Name char(50),
BeModified int DEFAULT(0)
)
Whenever [Name] is updated, [BeModified] will be set to 1.
I think I should create an TRIGGER AFTER UPDATE
CREATE TRIGGER UpdateRow
ON Demo
AFTER UPDATE
UPDATE Demo
Set BeModified = 1 WHERE ID = "???"
My problem is I do not know how to identify the ID of row which has been modified.
Have you got any suggestion for me?
Thank you so much!
|
|
|
|
|
I don't think you really need a trigger here. You must be updating the name through a stored procedure/SQL query. Just make the query update the bool column as well.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
As d@nish recommended. There is also a keyword in the trigger that indicates the current record, not sure what it is, I NEVER use triggers, they are evil.
As another idea I use a datetime field instead of a boolean, it tells you so much more than a simple yes/no.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I concur. This is NOT the place for a trigger. I only use triggers to store 1)disposable, 2)derived and 3)complex data used for reporting or searching. Must meet all 3 criteria. But to answer the question-
UPDATE Demo
Set BeModified = 1 WHERE ID IN (SELECT ID FROM inserted)
OR
UPDATE Demo
Set BeModified = 1
FROM Demo
INNER JOIN inserted
ON Demo.ID = inserted.ID
This is assuming sql server.
The "inserted" table is a virtual table which contains all of the fields and values from the insert, update or delete that fired the trigger.
You might think that only a record at a time is updated but in fact since it is possible to update multiple records at the same time, this may NOT be what you want. But thats what you get with triggers. If you ever have to update all of the records in that table, BeModified will be set to 1 for all. Be warned.
|
|
|
|
|
Hello,
This is Vikash.
I have an application where on starting the application it connects to SQL Server 2000.
At this point of connection I also execute the query 'Select Name From Sysdatabases' to get a list of database names.
The problem is at my PC using My SQL Server 2000, the Query is getting executed properly giving me a list of databases.
But when I install the software on one of the clients PC where my database is attached to their existing MSSQL Server 2000, it display the error message, "Invalid Object Name 'sysdatabases'".
Can somebody tell me why this is happening. Has somebody faced similar problems.
Please any help would be highly appreciated.
Thanks in Advance.
|
|
|
|
|
Check the permissions of your connection credentials. Typically a developer assigns himself god rights to their database and lose sight of the fact that a DBA will never do that on a production server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply Mycroft.
but can you tell me what credentials you are talking about.
My Connection string is :
Server=<Servername>;Database=<DBname>;Uid=<UserID>;Pwd=<Password>;
what else do I need to provide.
Is It That Sysdatabases will work only if I have access rights to all the databases in the instance.
bcoz, my client also has several other databases in this instance which I am connecting and I might not have rights to those databases.
Awaiting your reply.
|
|
|
|
|
sysdatabases lives in the master database, your credentials may not have rights to that database.
You probably need to arrange r/o access for those credentials to the master database or event the sysdatabase table if that is the only table you need.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Have you tried prepending 'sys.' to it.
eg. select * from sys.sysdatabases?
Regards,
Rob Philpott.
|
|
|
|
|
Hi Folks,
I have a ASP.NET Web intranet application that allows users to access tables and add, modify,view, or delete records. The tables, stored procedures are present in SQL 2005 on Windows 2003.
Each of my client boxes has SQL Server Management Studio. The authentication mode is Windows. What I want is any user other than administrator should not have permission to change data through back-end.
The real question is he should be able to change data only through the front-end application. If he accesses database from the Management Studio he should not be allowed to alter tables and stored procedures. My main concern is would denying permissions on tables in back-end affect the functionality of adding, modifying, and deleting data through front-end.
Please help me out.
Regards,
V1i9n6o7d
|
|
|
|
|
It is far simpler than locking down tables, though any lock down is user specfic so it won't effect your web application if done correctly.
On the database holding the back-end for your app, only add the administrator, and the website execution account as having access to the database. If you want to be doubly sure then lock down that entire SQL instance to just those two users so that no-one else can get in and add themselves.
Don't forget to add yourself though, or you won't be able to maintain the database when they ask for the enevitable changes. 
|
|
|
|
|
What is the Web execution account 
|
|
|
|
|
Each application runs under some credentials. The ASP.NET application runs on a different user-account than the one that you use to log into your Windows machine. This is done to enhance security - the application doesn't need all those permissions that you have.
The builtin ASP.NET[^] account is the one that runs the application. Alternatively, you could switch your database to use "SQL Server authentication[^]".
I are Troll 
|
|
|
|
|
v1i9n6o7d wrote: My main concern is would denying permissions on tables in back-end affect the functionality of adding, modifying, and deleting data through front-end.
Yes of course it will. You need to formulate another strategy or refine you current one. Personally I use SQL standard credentials when my app connects to the database and give the AD connections read only access.
You may be able to lock down read only by credential/app, I know profiler is aware of the application so it may be there.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can anyone help me in understanding Locking and Transaction in SQL Server?
|
|
|
|
|
Lots of information can be found here[^]
|
|
|
|
|
I would like to detect if SQL Server is 'doing anything'. I know this is vague so a bit of background:
I have a GUI that starts a long set SQL server processes (its a financial model), and in order to overcome the extremely cack network we have, I start this SQL process via a SQL agent job, so that the 'server' owns the process rather than the user running the GUI.
Now I've got control logic in the sql, updating timestamps in tables etc, but I would like a 'backup' to this.
Originally I thought about using the sql server cpu activity which I can get via WMI, and some kinda of threshold value...so if cpu >33% then the server is likely to working and not idling?
But I just wondered if there was a better way?
I'm using SQL Server 2000 and VB6 (yea you can laugh!)
|
|
|
|
|
|
Hi all,
Can any one tell me how to delete the data from the database.
Thanks in advance.
|
|
|
|
|
Whatever database you are using, it must be having a delete command. Use it.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
i want to delete by using the query.
|
|
|
|
|
Why can't you google or use the help provided with database? This is so very trivial. Anyways, here you go (assuming you are using MS SQL):
Delete from tableName where [whatever condition you want to use]
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
