A curious case of data corruption

Dear group,

if someone could give me an idea what is going on in one of our
databases, this would really really be helpful.

We have two tables with around 2 / 3 million rows. These tables have no
key and no ID. (This major design flaw will be overcome in some later
version of the application-software working on this DB but right now i
have to live with this).

Now for the funny bit

1) I open one window in the Query-Analyzer and write some code like
Begin transaction INSERT INTO TABLE COMMIT
2) in another window i write "SELECT COUNT(*) from TABLE"

If I perform the insert then afterwards select count(*) the row-count
is incremented by two whereas the Insert-Statement said "1 row(s)
modified.

DBCC gives no errors.
DBCC gives amount of rows 2 million rows
Select count(*) on the same table gives 3 million rows

Exporting the data, truncating the table re-importing data gives no
result, right now the DTS-status is 203 and the machine is "thinking".

Is there any possibility to check the "integrity" of the table?

This problem is on the production machine, but right now i am working
on a copy so it was propagated with backup / restore-mechanism.

Any hint would be very helpful

Thanks and Greetings

Uli(uli2003wien@.lycos.at) writes:
> We have two tables with around 2 / 3 million rows. These tables have no
> key and no ID. (This major design flaw will be overcome in some later
> version of the application-software working on this DB but right now i
> have to live with this).
> Now for the funny bit
> 1) I open one window in the Query-Analyzer and write some code like
> Begin transaction INSERT INTO TABLE COMMIT
> 2) in another window i write "SELECT COUNT(*) from TABLE"
> If I perform the insert then afterwards select count(*) the row-count
> is incremented by two whereas the Insert-Statement said "1 row(s)
> modified.
> DBCC gives no errors.
> DBCC gives amount of rows 2 million rows
> Select count(*) on the same table gives 3 million rows

Well, I would definitely add a non-unique clustered index on the
table. It does not really matter which column, but if you add the
index, the entire table will be reorganized.

I recognize the symptom; other people have recommended similar observations.
Although they usually had a WHERE clause, and maybe even some indexes
on the table. I vaguely recall that a clustered index was a workaround
out of the problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(uli2003wien@.lycos.at) writes:
> We have two tables with around 2 / 3 million rows. These tables have no
> key and no ID. (This major design flaw will be overcome in some later
> version of the application-software working on this DB but right now i
> have to live with this).
> Now for the funny bit
> 1) I open one window in the Query-Analyzer and write some code like
> Begin transaction INSERT INTO TABLE COMMIT
> 2) in another window i write "SELECT COUNT(*) from TABLE"
> If I perform the insert then afterwards select count(*) the row-count
> is incremented by two whereas the Insert-Statement said "1 row(s)
> modified.
> DBCC gives no errors.
> DBCC gives amount of rows 2 million rows
> Select count(*) on the same table gives 3 million rows

Well, I would definitely add a non-unique clustered index on the
table. It does not really matter which column, but if you add the
index, the entire table will be reorganized.

I recognize the symptom; other people have recommended similar observations.
Although they usually had a WHERE clause, and maybe even some indexes
on the table. I vaguely recall that a clustered index was a workaround
out of the problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog schrieb:
> (uli2003wien@.lycos.at) writes:
> > We have two tables with around 2 / 3 million rows. These tables have no
> > key and no ID. (This major design flaw will be overcome in some later
> > version of the application-software working on this DB but right now i
> > have to live with this).
> > Now for the funny bit
> > 1) I open one window in the Query-Analyzer and write some code like
> > Begin transaction INSERT INTO TABLE COMMIT
> > 2) in another window i write "SELECT COUNT(*) from TABLE"
> > If I perform the insert then afterwards select count(*) the row-count
> > is incremented by two whereas the Insert-Statement said "1 row(s)
> > modified.
> > DBCC gives no errors.
> > DBCC gives amount of rows 2 million rows
> > Select count(*) on the same table gives 3 million rows
> Well, I would definitely add a non-unique clustered index on the
> table. It does not really matter which column, but if you add the
> index, the entire table will be reorganized.
> I recognize the symptom; other people have recommended similar observations.
> Although they usually had a WHERE clause, and maybe even some indexes
> on the table. I vaguely recall that a clustered index was a workaround
> out of the problem.

Thank you Erland,

as always a great help and a hint for the right direction. Actually
this table had already a clustered index but dropping the index and
recreating the index did the job for me (and much faster than
exporting, dropping and importing the table)

Regards

Uli|||
Erland Sommarskog schrieb:
> (uli2003wien@.lycos.at) writes:
> > We have two tables with around 2 / 3 million rows. These tables have no
> > key and no ID. (This major design flaw will be overcome in some later
> > version of the application-software working on this DB but right now i
> > have to live with this).
> > Now for the funny bit
> > 1) I open one window in the Query-Analyzer and write some code like
> > Begin transaction INSERT INTO TABLE COMMIT
> > 2) in another window i write "SELECT COUNT(*) from TABLE"
> > If I perform the insert then afterwards select count(*) the row-count
> > is incremented by two whereas the Insert-Statement said "1 row(s)
> > modified.
> > DBCC gives no errors.
> > DBCC gives amount of rows 2 million rows
> > Select count(*) on the same table gives 3 million rows
> Well, I would definitely add a non-unique clustered index on the
> table. It does not really matter which column, but if you add the
> index, the entire table will be reorganized.
> I recognize the symptom; other people have recommended similar observations.
> Although they usually had a WHERE clause, and maybe even some indexes
> on the table. I vaguely recall that a clustered index was a workaround
> out of the problem.

Thank you Erland,

as always a great help and a hint for the right direction. Actually
this table had already a clustered index but dropping the index and
recreating the index did the job for me (and much faster than
exporting, dropping and importing the table)

Regards

Uli|||(uli2003wien@.lycos.at) writes:
> as always a great help and a hint for the right direction. Actually
> this table had already a clustered index but dropping the index and
> recreating the index did the job for me (and much faster than
> exporting, dropping and importing the table)

That's good to hear. I would keep an eye on the table, in case the
problem would reappear.

By the way, rather than dropping and recreating, DBCC DBREINDEX can
be somewhat quicker. You can also use WITH DROP_EXISTING on CREATE INDEX.

This is particularly important if you have non-clustered indexes on the
table as well, as they will have to be rebuilt if you drop the clustered
index. This is because the NC indexes use the clustered index keys as
their row locator.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

A couple of things with my new report

I have made a report in SRS and am not sure of how to do some of the functions I need.

In SQL I can use UPPER to convert to upper case, SRS doesn't seem to like this. When I add UPPER to the SQL in my SRS report, the report runs but the line that is to be upper case is missing. If I remove the UPPER word from my query it works fine just displaying in lower case. Am I doing something wrong?

In Excel 2007 (I am converting my report from Excel to SRS) there is a function called NETWORKDAYS (Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.) Is there something similar I can use in SRS?

Thanks.

Why not put all of your TSQL into a stored procedure? I have just completed a project where this was done.

>Use NETWORKDAYS to calculate employee benefits that accrue based on thenumber of days worked during a specific term.) Is there somethingsimilar I can use in SRS?
It is possible to write such a function - I wrote one recently - will try and find a reference to it.

|||Table definition and stored procedure to add workdays is

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Holiday]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Holiday] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[YYYYMMDD] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO
ALTER TABLE [dbo].[Holiday] WITH NOCHECK ADD
CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Holiday] ON [dbo].[Holiday]([YYYYMMDD]) ON [PRIMARY]
GO
execsp_addextendedproperty N'MS_Description', N'Date in yyyyMMdd notation',N'user', N'dbo', N'table', N'Holiday', N'column', N'YYYYMMDD'
GO
CREATE FUNCTION dbo.fnAddWorkdays(@.FROM DATETIME, @.DAYS INT)
-- Purpose:
-- Calculate Date @.DAYS in future from @.FROM
-- Copyright (C) 2007 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
RETURNS DATETIME AS
BEGIN
DECLARE @.YYYYMMDD CHAR(8)
WHILE @.DAYS > 0
BEGIN
SET @.FROM = DATEADD(day, 1, @.FROM)
SET @.YYYYMMDD = SUBSTRING(REPLACE(CONVERT(CHAR(20), @.FROM, 126), '-', ''), 1, 8)
IF DATEPART(weekday,@.FROM) NOT IN (1, 7) BEGIN
IF NOT EXISTS(SELECT * FROM Holiday WHERE YYYYMMDD = @.YYYYMMDD) SET @.DAYS = @.DAYS - 1
END
END
RETURN @.FROM
END
GO
SELECT dbo.fnAddWorkdays(GETDATE(), 1)
SELECT dbo.fnAddWorkdays(GETDATE(), 2)
SELECT dbo.fnAddWorkdays(GETDATE(), 3)

-- I will write another function later today.

A couple of things with my new report

I have made a report in SRS and am not sure of how to do some of the functions I need.

In SQL I can use UPPER to convert to upper case, SRS doesn't seem to like this. The report runs but the line that is to be upper case is missing. If I remove the UPPER word from my query it works fine just displaying in lower case. Am I doing something wrong?

In Excel 2007 (I am converting my report from Excel to SRS) there is a function called NETWORKDAYS (Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.) Is there something similar I can use in SRS?

Thanks.

You should be able to use UPPER in your SQL, certainly works for me....

i.e.

Code Snippet

select
acct.accno,
UPPER(nonpers.name) as 'name',
term.amount,
term.eod_balance,

|||

Hello Joe,

There are 2 ways to fix your upper case requirement:

1. Like Will said, you can do it straight from your SQL query.

2. The equivalent function from within SSRS is UCase. Use it like this: =UCase(Fields!Field1.Value)

As for your other question, I think you'll have to write a custom function to handle that.

Hope this helps.

Jarret

sql

A case of "OverDeletion"

I added this line to a sproc yesterday:

--get rid of the product master for inactive raw-mats/packaging
DELETE FROM PRODUCT_MASTER
WHERE PRODUCT IN
(SELECT PRODUCT FROM SAPROD_PLAN.DBO.PARTMSTR
WHERE PART_STATUS IN ('I','D') AND PART_TYPE NOT IN ('F','N','K','D'))

This sql statement should have thrown an error, because there is no column named "product" in the table partmstr. Instead, all of the data in the table
product_master was deleted. Of course, this table cascade deletes to 18 other tables, so I lost a whole slew of data. No big deal there, I can get a tape backup. Just curious how it could have even happened in the first place.I added this line to a sproc yesterday:

--get rid of the product master for inactive raw-mats/packaging
DELETE FROM PRODUCT_MASTER
WHERE PRODUCT IN
(SELECT PRODUCT FROM SAPROD_PLAN.DBO.PARTMSTR
WHERE PART_STATUS IN ('I','D') AND PART_TYPE NOT IN ('F','N','K','D'))


This sql statement should have thrown an error, because there is no column named "product" in the table partmstr. Instead, all of the data in the table
product_master was deleted. Of course, this table cascade deletes to 18 other tables, so I lost a whole slew of data. No big deal there, I can get a tape backup. Just curious how it could have even happened in the first place.

u are kidding.Delete statement will raise error if any column name u mentioned in sql statement which is not in ur table.
Im 100% sure deletion will not happened in such case.|||U funny

You should post the DDL for both tables and provide some sample data.

Look at the sticky's at the top of the page, but...

USE Northwind
GO

SET NOCOUNT ON
SELECT * INTO myOrders99 FROM Orders
GO

sp_help myOrders99
GO

DELETE FROM myOrders99 WHERE Brett IN (SELECT Brett FROM myOrders99)
GO

SET NOCOUNT OFF
DROP TABLE myOrders99
GO|||Do you have a couple of PARTMSTR tables? I see this one is declared with the database name, as well as owner.|||This duplicates the problem...

CREATE PROCEDURE TMPTEST
AS
CREATE TABLE TMPPARTMSTR (
PART_CODE VARCHAR(18),
PART_TYPE CHAR(1),
PART_STATUS CHAR(1),
)

CREATE TABLE TMPPRODUCT_MASTER(
PRODUCT VARCHAR(18)
)

INSERT INTO TMPPARTMSTR (PART_CODE, PART_TYPE, PART_STATUS)
SELECT 'ABC123', 'F', 'A' UNION ALL
SELECT 'DEF123','K','A' UNION ALL
SELECT 'ABC456','R','A' UNION ALL
SELECT 'DEF456','R','I'

INSERT INTO TMPPRODUCT_MASTER(PRODUCT)
SELECT 'ABC123' UNION ALL
SELECT 'DEF123' UNION ALL
SELECT 'ABC456' UNION ALL
SELECT 'DEF456'

DELETE FROM TMPPRODUCT_MASTER
WHERE (PRODUCT IN
(SELECT PRODUCT
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D') AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')))
GO

It ends up deleting all 4 rows from tmpproduct_master, when it should throw an error.|||It looks like it's coorelating the column without a lable because it's unique

SELECT * FROM TMPPRODUCT_MASTER
WHERE PRODUCT IN ( SELECT PRODUCT
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D')
AND PART_TYPE NOT IN ('F', 'N', 'K', 'D'))|||Hi,
Initially i found this query strange and thought that ur statement is not valid,but after testing it manually i found that the statement given is absolutely correct,and it even worked in the same way in oracle also..

The only way one can restrict this is by adding the table name in front of the column name
eg:
delete from deal where dealid in (select tst1.dealid from tst1 where id1=1)
where dealid is a column of deal table and id1 is the col of tst1 table..

After checking the execution plan I can see that
1.DB checks for the availability of clustered index for deal.dealid with the condition deal.dealid=deal.dealid(ordered)
2.Scans the inner query for tst1.id1=1.

one more weird thing which i have observed is
delete from deal where dealid in (select tst1.dealid from tst1 where dealid=1)
also works fine and deletes the rows from the deal table..

May be this is a bug in SQL.

regards,
Pavan.|||This duplicates the problem...

DELETE FROM TMPPRODUCT_MASTER
WHERE (PRODUCT IN
(SELECT PRODUCT
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D') AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')))
GO


It ends up deleting all 4 rows from tmpproduct_master, when it should throw an error.

PRODUCT column is not in table TMPPARTMSTR and it is refered from TMPPRODUCT_MASTER .Obviously condition would be true and deleted all records.

In future always add alias name with table and refer ur column with alias name.|||It's not a bug.

But, why does this work

DELETE FROM TMPPRODUCT_MASTER
WHERE PRODUCT IN ( SELECT PART_CODE
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D')
AND PART_TYPE NOT IN ('F', 'N', 'K', 'D'))

SELECT * FROM TMPPRODUCT_MASTER

But this does not? (Not firing all the neurons today I guess)

DELETE FROM TMPPRODUCT_MASTER o
WHERE EXISTS ( SELECT *
FROM TMPPARTMSTR i
WHERE PART_STATUS IN ('I', 'D')
AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')
AND i.PART_CODE = o.PRODUCT)|||PRODUCT column is not in table TMPPARTMSTR and it is refered from TMPPRODUCT_MASTER .Obviously condition would be true and deleted all records.

So, are you saying: because the subquery can't find a column named "product" in the only table in the subquery, it will go OUTSIDE of the subquery to look in other tables contained in the query? That seems counterintuitive to me, but that is what it appears to be doing.|||That's a fact JACK

Look at my SELECT Statement as a sample...

I thought it only coorelated in the predicate and not in the SELECT|||Well, between what I've read here and in BOL, I almost understand whats happening. In the future, I'll stick with the advice given here, and use tablename.columnname, even though BOL doesn't do that in some of their examples.

Learn something new every time I come here.
Thanks!|||tablename.columnname, even though BOL doesn't do that in some of their examples.

ahhh the blind dude way...

I use aliases instead

SELECT * FROM Orders o JOIN [Order Details] d ON o.OrderId = d.OrderId

Mr. dude fully qualifies everything, because, well, he's blind you see|||The blind dude codes with style!sql

70-444... Case studies, or...?

Took and passed the 70-443 yesterday morning, now just need the 70-444 to complete the MCITP: DBA cert... Was wondering if anybody out there knew if the 70-444 exam used the case study format or ...? Couldn't find that info on http://www.microsoft.com/learning/exams/70-444.mspx.

Thanks.

Justin

Never mind. Took (and passed) the test yesterday morning. I'll consider the lack of replies to my original message a sign that this was something that's not supposed to be discussed, so I suppose I shouldn't mention anything about the format myself.

Good luck to anybody else taking it.

Justin, now with MCITP goodness.

|||

Congrats!

So tell me, what format was the test?

Did it have multiple choice & simulation?

Was it case studies like the 70-443?

Thanks!

4 node cluster

I want to set up a 4 node cluster where 3 nodes are active and 1 is the
passive node that would take over in case any one of those 3 active nodes
fail. How can I set this up ? Any documentation..
Using SQL 2005..
"Hassan" <hassan@.hotmail.com> wrote in message
news:eDbSEq7hHHA.4680@.TK2MSFTNGP06.phx.gbl...
>I want to set up a 4 node cluster where 3 nodes are active and 1 is the
>passive node that would take over in case any one of those 3 active nodes
>fail. How can I set this up ? Any documentation..
> Using SQL 2005..
First, you get the proper training...
Basically, you build the four node cluster then install SQL three times to
create three instances with each instance hosted in its own resource group.
Place a SQL instance on each node that you want to be the active node.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp class is:
April 30, 2007 - Denver
|||You can find a lot of documentation on MSDN about this. Setting up clustering
is a very eloborate process and you need to prepare a lot before actually
setting one up.
All the best.
Suresh
"Hassan" wrote:

> I want to set up a 4 node cluster where 3 nodes are active and 1 is the
> passive node that would take over in case any one of those 3 active nodes
> fail. How can I set this up ? Any documentation..
> Using SQL 2005..
>
>