Data integrity

Question

Post reply

Data integrity

majida2z3

SSC Enthusiast

Points: 139
More actions
July 27, 2010 at 9:50 am

#74610

Hi,
someone can help me on this,
I need to create query table that hold integrity check data by date
then create jobs that populate above tables given the daily integrity queries
Create queries needed to detect data exceptions that could cause replication failures, from the
populated table.
Create report to notify of data exceptions along with the SQL needed to correct(ie. insert missing foreign key, remove duplicates of primary keys)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can do something like this to populate the table, though it will only insert rows if CheckDB picks up an error.

INSERT INTO <TableName> (<ColumnList>)

EXEC ('DBCC CheckDB (''<Database Name'') With No_Infomsgs, Tableresults')

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

majida2z3 SSC Enthusiast Points: 139 More actions · Answer 2

Gail,

thanks for replying, Actually i need to create a table which can populate the output of any duplicate value. and then i have to generate a report

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I think you're going to need to detail the problem a lot more for anyone to offer meaningful help.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

majida2z3 SSC Enthusiast Points: 139 More actions · Answer 4

Gail,

as I wrote above about my project here is the detail

I need to create query table that hold integrity check data by date

then create jobs that populate above tables given the daily integrity queries

Create queries needed to detect data exceptions that could cause replication failures, from the

populated table.

Create report to notify of data exceptions along with the SQL needed to correct(ie. insert missing foreign key, remove duplicates of primary keys)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I saw that, and , as I said, I think you're going to have to detail the problem a lot more to get meaningful help.

I thought, when you said 'integrity check' you meant a database integrity check - CheckDB. Hence my first reply. If you meant something else (like duplicate values in some or other column of some or other table) you need to say that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

majida2z3 (7/27/2010)
i need to create a table which can populate the output of any duplicate value. and then i have to generate a report

This statement seems broad to me. Could you explain in greater detail? Are you considering duplicate values for any column that may be any data type that may be in any table that may be in any database?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

kanejhons SSC Rookie Points: 28 More actions · Answer 7

Hello Friends.........

Data integrity can be compromised in a number of ways:

# Human errors when data is entered

# Errors that occur when data is transmitted from one computer to another

Thanks

adi backoffice