Disk I/O Errors Error Number 823

Has anyone come across this message when running SQL 2000 Failover clusters ?

I/O error 2(error not found) detected during write at offset 0x00000016d44000 in file 'K:\MSSQL\Data\Int_DB_2_Data.NDF'..

We got this message yesterday, the RAID Array was OK, the Drive could be written to using test editor directly onto the console. We restarted SQL and all came back OK. SPID 3 is the Signal Handler

Any ideas ?Have you check the SQL Error log? My guess is that you may have a torn page in that database.|||I think you should do a full backup, then use this backup version to restore it on another server and run DBCC CHECKDB or DBCC CHECKFILEGROUP to make sure there is no torn page error like joejcheng mentioned. The Error 823 most likey indicate that there is potential disk problem.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_2a0j.asp|||I am chasing a similar problem on HP hardware. Make sure you get a disk consistency check done, as sometimes HP hardware is not good about checking for bad sectors, before writing to them.|||Originally posted by MCrowley
I am chasing a similar problem on HP hardware. Make sure you get a disk consistency check done, as sometimes HP hardware is not good about checking for bad sectors, before writing to them.
Many thanks.

I am running DBCC CHECKDB with ALL_ERRORMSGS , PHYSICAL_ONLY first, then scheduled to run with REPAIR_REBUILD at the weekend.

We are using an HP SAN. I will forward this onto our H/W supplier for info.sql

Disctinct Query Help

Can anyone tell me how to write this query correctly... Right now it is returning all of the results instead of just the results with unique emails...

SELECT ID, firstname, address1, lastname, address2, city, province, postalCode, phoneNumber, email, referral, cpauseInfo, yukonInfo, dateAdded
FROM Results
WHERE email IN (SELECT DISTINCT email FROM Results)

Please help.

Your query is doing exactly what it was written to do. Post some sample data from the table and the expected output so someone here can help fix your query.

|||

This table has entries we represent contest submissions. The form wasn't built very well and it allowed users to submit results numerous times. I want to write a query that will filter the data based on email address and phone numbers. So any records that have the same email address should be deleted. Same goes for phone number.

Should I be creating a temporary table or something?

|||

I am going toguess that you want to get a resultset where the COUNT(*) of each email is 1. How this would look:

SELECT r.ID, r.firstname, r.address1, r.lastname, r.address2, r.city, r.province, r.postalCode, r.phoneNumber, r.email, r.referral, r.cpauseInfo, r.yukonInfo, r.dateAddedFROM Results rWHEREEXISTS (select 1from Results rxwhere rx.email = r.emailgroup by rx.emailhavingcount(*) = 1 )

|||

sheldonj:

This table has entries we represent contest submissions. The form wasn't built very well and it allowed users to submit results numerous times. I want to write a query that will filter the data based on email address and phone numbers. So any records that have the same email address should be deleted. Same goes for phone number.

Should I be creating a temporary table or something?

You don't need a temp table. You are attempting to clean duplicated data. Let me refer you to an article I wrote several years agohere. Here is a sample that could remove duplicates by email address.

/* 1. Query heading */DELETEFROM/* 2. Table with duplicates */ ResultsWHEREEXISTS (SELECT 1FROM/* 3. Table with duplicates, with an alias */ Results bWHERE/* 4. Join each field with *itself*. These are fields that could be Primary Keys */ b. = Results.GROUP BY/* 5. I must GROUP BY these fields because of the HAVING clause and because these are the possible PK */ b.HAVING/* 6. This is the determining factor. We can control our output from here. In this case, we want to pick records where dateAdded is greater than the MIN dateAdded */ Results.[dateAdded] >MIN(b.[dateAdded]) )

|||

Thank you David that was exactly what I was looking for

|||

I think that simplest way to do this is:

SELECT ID, firstname, address1, lastname, address2, city, province, postalCode, phoneNumber, email, referral, cpauseInfo, yukonInfo, dateAdded
FROM Results
WHERE ID IN (SELECT max(ID)
FROM Results
group by email)

it will return you only single email entries from you table.
If you would like to keep only single record with unique email in your table run

DELETE FROM Results
WHERE not ID IN (SELECT max(ID)
FROM Results
group by email)
Good luck

Disconnect all connections

Hi All,
I trying to write a script that will detach a DB copy the physical file to a new location then remount the original DB.
the Problem that i am having it detaching the DB as i get error telling me that the DB is in use.
There is an Access front end open with and ODBC connection to the SQL DB and this needed to be open to as it is where the command to create the Copy is coming from.

I have tried the following code
USE master ALTER DATABASE EclipseSQL SET SINGLE_USER with no_wait
exec sp_detach_db 'EclipseSQL'
exec xp_cmdshell 'MKDIR "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\Archive\06_2007_invoices" '

exec xp_cmdshell 'ECHO Y| cacls "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf" /p everyone:f'

exec xp_cmdshell 'xCopy "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\Archive\06_2007_invoices\" '

exec sp_Attach_db 'EclipseSQL', 'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf'

i get the following output from this

Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'EclipseSQL'
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3703, Level 16, State 2, Line 1
Cannot detach the database 'EclipseSQL' because it is currently in use.

(2 row(s) affected)

(2 row(s) affected)

(3 row(s) affected)
Msg 1801, Level 16, State 3, Line 1
Database 'EclipseSQL' already exists. Choose a different database name.

any know how i get disconnect all users no questions asked?

Try:

Code Snippet

alterdatabase EclipseSQL setread_onlywithrollback immediate

go

alterdatabase EclipseSQL setread_writewithrollback immediate

go

|||That did the trick thanks