|
Use this statement to verify what you think happened ...
select name,default_database_name,modify_date from sys.sql_logins
If, indeed the changes were made, you will see when they happened and someone here might be able to work you through fixing it.
You may want to start by changing your administrative passwords since your DBA left. Make sure to tighten up any remote access accounts, etc.
|
|
|
|
|
David Mujica wrote: Use this statement to verify what you think happened ...
Users could access the system last Thursday; on Friday, they couldn't. The default database was changed to a database that is used for sending e-mail messages, so, they neither need nor have access to it.
The accounts (there are only a handful) have been manually updated.
David Mujica wrote: select name,default_database_name,modify_date from sys.sql_logins
Since this is a SQLServer 2000 node, the listed table didn't work, but it gave me enough information to move forward. I used the following:
select l.name, xdate1 as creation_Date, xdate2 as modify_date, l.dbid,
d.name as defaultdb
from sysxlogins l inner join
sysdatabases d on l.dbid = d.dbid
And that just opened up more questions...
One of the accounts was created in 2007, updated a minute later and not touched after that, but, they have a default database set to a database that didn't exist at the time.
As far as access rights, when someone leaves the company, their password is changed and remote access removed.
Tim
|
|
|
|
|
Hi ,ALL
If i have this Customer table :
ID | CustomerName | PhoneNum | City |Street
<code>19 jon 555 NK st.5</code>
21 Mark 236 TK KA.st
<code>37 jon 555 NK st.5</code>
45 AJ 800 LA WS.17
We note that Customer Name "Jon" added in this table tow time with deffrint IDs .
In my real database , i have Thousands of this case.
How i can write query return the douplicate record with there IDs ? , like :
ID | CustomerName | PhoneNum | City |Street
19 jon 555 NK st.5
37 jon 555 NK st.5
modified on Monday, December 7, 2009 4:18 AM
|
|
|
|
|
Do some basic research[^]
This is a common problem that has been solved over and over again. The first 2 items look interesting.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear Mycroft ,
i search befor and after post my question. but i did not find the solustion . could you help me please ?!
according my qusetion , my problem is i have tow douplicte record with deffrint ID , when i use Max(ID) or top(ID) functions i will ignor of the IDs ,becuse ID is deffirnt. and i wont to display it as it is with out ignor any IDs . how i can do it ?
|
|
|
|
|
You asked samething a few posts down. You got the reply and as able to do this as well. Now, why are you posting this again?
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...!!
|
|
|
|
|
Dear d@nish ,
Here i have different case , becuse i have two douplicate record with different ID , and when i use max() or top() function i will be ignor one of the ID ,and this couse problem for me to link to other tables.
so i need to display all the two records with there IDs.
ould you please help me to do that ?!
Thanks
|
|
|
|
|
If you will read Shameel's reply to your earlier post, you should be able to figure it out. Anyways, a general query would be:
select [colNames]
from tableName
group by [colNames]
having count(*) > 1
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...!!
|
|
|
|
|
you are right and i know that ! but
if i write this :
select [ID,colNames,Phone,...]from tableNamegroup by [colNames]having count(*) > 1
i will have 0 douplicate record !! , why ? because IDs for each douplicted records are different !!
and if use all columns without IDs ,i will got douplicted record without IDs.
please see the example in my question , i don't need to ignore any IDs.
could you please help me to find way?
Thanks .
|
|
|
|
|
As Luc mentioned, you don't need to group by ID. You will get the duplicate records then.
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...!!
|
|
|
|
|
Dear d@nish ,
see my replay to LUC.
Thanks
|
|
|
|
|
All you need is a variation on SQL code shown by others. Try this:
SELECT ID, otherfields FROM table
GROUP BY otherfields
HAVING COUNT(*) > 1
i.e. don't use MAX(ID) or TOP(ID)
and include all fields ("otherfields") that are relevant for identity checking
|
|
|
|
|
Hi Luc ,you are right,
I Wrote that what you say , but i got error that " you try to excute a query that does not include the specified expression 'ID' as part of an aggregate function"
Thanks
|
|
|
|
|
Use CTE feature of SQL server 2005.....
Example:
;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)
AS
(
SELECT
Product, SaleDate, SalePrice,
Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC)
FROM SalesHistory
)
DELETE FROM SalesCTE
WHERE Ranking > 1
|
|
|
|
|
Dear puri keemti,
i do not use SQL server , i use sql in access 2007.and i dont need to delet the record that douplicated , i need to display it as it is ,with there different IDS .
if it is not clear please see question and my replies.
could you help me ?
thanks for you brother.
|
|
|
|
|
Hi,
This query works for SQL Server2005,but try to convert for Access2007.
A)
CREATE TABLE #tmpData
(
id INT IDENTITY(1,1),
custname VARCHAR(MAX)
)
INSERT INTO #tmpData
SELECT m.Customername
FROM mytable1 m
GROUP BY m.Customername,m.phonenumber,m.city,m.street HAVING COUNT(m.Customername)>1
DECLARE @i AS INT
DECLARE @query AS nvarCHAR(MAX)
SET @i=0
SET @query = 'select * from mytable1 where customername in ('
WHILE(@i<(SELECT COUNT(*) FROM #tmpData))
BEGIN
SET @i=@i+1
SET @query=@query+(SELECT char(39)+custname + char(39) FROM #tmpData WHERE id=@i)+','
END
SET @query=SUBSTRING(@query,1,LEN(@query)-1)
SET @query=@query+')'
EXEC( @query)
DROP TABLE #tmpData
I just played around and found too way to work for Access (I tested it on Access 2003 and not for 2007 version,but i think it should work too for Access2007 version)
B)
select a.id,a.Customername , a.phonenumber, a.city, a.street
from mytable1 a
inner join
(select Customername , phonenumber, city, street
from mytable1
group by customername,phonenumber,city,street
having count(*) > 1) b
on a.Customername = b.Customername
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
modified on Monday, December 7, 2009 9:32 AM
|
|
|
|
|
Try this
Will work for Sql Server(Any version), MySql,Oracle, MSAccess
select * from myTable where customername in (
select customername
from myTable
group by customername,phonenum,city,street
having(count(customername)>1 and count(phonenum)>1 and count(city)>1 and count(street)>1))
Output:
id customername phonenum city street
19 jon 555 NK st.5
37 jon 555 NK st.5
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi,
is there any way to write a validation rule for the field in the datbase design of sql server 2005. Like ms access.
i tried to search the net but no luck. so i decided to write triggers on insert and update to validate my data.
please, advice me if this is good approach. or if there is anything else we can do in the datbase design.
(what i mean by validation rule is to for example for my numeric field i want the data to be in a specific range.)
Hussain Mohammed Saleh Attiya
ISP Technical Manager
Atyaf Telcom - Bahrain
|
|
|
|
|
You can make use of check constraint on your table for this. Something like:
Alter YourTableName
check (intCol > 0 and intCol < 100)
Although, you should try and filter out faulty data at the UI only. Why to hit database to know that it is a faulty data? It will make your application slow.
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...!!
|
|
|
|
|
Thanks,
oh yeah.. i forgot about the check
and you are right to validate the data in the UI. which i am aleady doing it. the reason why i want to do the validation in the database design level, is to make sure that we are adding the right data and also, to prevent the other developers from playing with the database. our database is shared one and we are using it many applications.
thanks for your reply
Hussain Mohammed Saleh Attiya
ISP Technical Manager
Atyaf Telcom - Bahrain
|
|
|
|
|
You need to think about what you are doing, what are you going to do when you get a validation fail, raise an error. Then what are you going to do, trap the error in your DAL/BusinessObject and pass it back to the UI.
This is called PROGRAMMING BY ERROR and is the absolute worst validation model you could build.
Put you validation is either the UI or the BusinessObject. Validation does not belong in the database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your reply.
i agree with you. but for your information, i am not waiting to get the error from that database in order to catch it in my DAL/BusinessObject.
i am already doing my validation also in the UI. what i really want to make sure is, the data are inserted right. i dont want to keep any chance for developers to play with the application UI to update the database.
now you make me confused. because i am doing the database design. here is another question: if i consider the following in my database design:
- primary keys
- Forien Keys
- Default Values
- Check Constraint
- indexes
- Database Diagrams
is it bad database design?
Hussain Mohammed Saleh Attiya
ISP Technical Manager
Atyaf Telcom - Bahrain
|
|
|
|
|
Yah, I opened my (big mouth) reply before you had replied, got delayed and posted after you.
What you are doing is correct and is the reason constraints are in the database, if you were doing this as the primary validation my argument would apply. The additional elements you raise are all valid and should be used for a well designed database, what you cannot do is expect and error.
With a FK if you decided to try and write the record and trap the error for a FK violation as the primary validation it would be wrong. Having the FK to enforce data integrity is absolutely valid.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Many ways to do validation in database
(1) Constraints
(2) SP
(3) Triggers
|
|
|
|
|
|