SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Data Farming

This editorial was originally published on Oct 13, 2008. It is being re-run as Steve is out today, traveling to a SQL in the City event and SQL Saturday in Sacramento.

It's a fictional story, or at least I hope it is, but Bruce Schneier has a great piece on Identity Farming, a long term way to create false identities that would fit great in the spy world. Mr. Schneier doesn't see a practical point in doing it, but it's interesting from a data standpoint because he brings up a point. This could be done without a real person existing to back up all the data that's created.

The part that strikes me from this piece is that all too often we make assumptions about the people or entities that created all the data we use. One bad foreign key, orphaned child row, or incorrectly transformed piece of data could snowball downhill at a tremendous rate and it might be hard to determine what is wrong.

The blog entry talks about our data shadows, which grow larger and larger all the time. Unless you are actively trying to limit yours, every day that are likely new entries in some database about your life. And more and more, various companies and institutions interact with our data shadows instead of us. Credit checks, marketing efforts, when we board an airplane or make a purchase, all of these require checks on the shadow of data in our lives, not necessarily ensuring that the shadow is tightly linked to each of us.

I've had more than my share of confusion because of my name; it's common, in almost every database, and shared by thousands, if not millions of people. On on hand it means that I'm a little lost in the flood of "Steve Joneses" out there. On the other hand it makes it hard to correct mistakes. If there are 6 people with the same name and you have an orphaned record, who do you link it to? Do you guess? Infer it from the other data? I'd like to think you need to somehow research this, contact me, and make a note that the quality of this data could be suspect.

People working with information try to be accurate, but they get busy, and mistakes occur. I'm sure I'll find more and more over time, and I don't have a great solution for what might work better. I'd like to think that we would implement better checks for data quality, fuzzy searches, and somehow assign "risk" values to data. Something to let people know that there might have been some issue.

It's a thorny problem, one that's not going away, and likely to become more problematic in the future.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


Video and Audio versions

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video ( 34.9MB) feed

MP4 iPod Video ( 30.1MB) feed

MP3 Audio ( 5.8MB) feed

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

ADVERTISEMENT
SQL DBA Bundle

Top 5 Hard-earned Lessons of a DBA

‘10 Tips for Efficient Disaster Recovery’ by Steve Jones. Prepare for any future disaster by reading Steve’s tips today.

ANTS Performance Profiler

Free eBook: 25 Secrets for Faster ASP.NET Applications

Want to speed up your web application? Our new eBook has 25 tips for getting maximum performance from ASP.NET - download it free.

sqlbackup

Have you got your 5GB free hosted storage yet?

Back up your SQL databases to the cloud using SQL Backup Pro. You’ll get your first 5GB of storage free. Try it now.

Featured Contents

 

Finding lost or forgotten SQL Servers

Clay Punnett from SQLServerCentral.com

This article will show a way to find all SQL Servers, particularly those that have been forgotten about or lost. More »


 

SQLServerCentral.com Users Survey 2013

Additional Articles from SQLServerCentral.com

We want to make sure we're covering the things that are relevant to you, so we're asking for some feedback on what you use on SSC, where we need to improve, and what you'd like to see. It should only take a few minutes, and three randomly-selected people will win a $100 Amazon gift card for their efforts. More »


 

Disaster Recovery Tip #6 - Monitor Your Systems

Press Release from SQLServerCentral.com

Today's complex systems are too large to be able to rely on any DBA's memory for knowledge of each component, and its configuration options and settings. More »


 

AlwaysOn in SQL Server 2014 CTP1

Additional Articles from MSDN Communities

AlwaysOn encompasses the SQL Server solutions for mission-critical high availability and disaster recovery. Two solutions were included under the AlwaysOn umbrella in SQL Server 2012: Availability Groups and Failover Cluster Instances. Each solution has different characteristics, making them appropriate for different scenarios, and both can be combined in the same deployment. More »


 

From the SQLServerCentral Blogs - Why You Need a Data Warehouse

Introduction You likely have heard about data warehousing, but are unsure exactly what it is and if your company needs one.... More »

Question of the Day

Today's Question (by Steve Jones):

You are searching a document with full text search and use the following where clause:

where contains( data_stream, 'Near((whitewash, brush), 3)')

Which of the following phrases in the data_stream column will cause a result to be returned? (choose 2)

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 2 points in this category: full text search.

Did you miss yesterday's question, Indexing in views: answer it now or check out the answer.

We'd love to give you credit for your own question and answer.
To submit a QOD, simply log in to the Contribution Center.

ADVERTISEMENT

Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!

Featured Script

Monitor database size with dbcc showfilestats

Landry D. Salles Filho from SQLServerCentral.com

Monitor the databases except master, tempdb,model and save the evolution in a table msdb.dbo.tb_ocupacaoBD.

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

Excecution plan slows - Hi, We have problems with a view created from a select sentence with a few joins. This view runs properly some days...

orphaned users in sql server - hi this is reddi Krishna i created a new login at server level and mapped to user level permissions after...

Could not load the DLL SQLsafe_ExtendedStoredProc.dll - Hi All, My SQL Server backup job, which will use the SQLsafe to backup, suddenly failed with the following error. I...

Securty Trigger stroed procedure - Hi , I want to capturethe Audit Server Security Events , like if any one doing Create / Alter / Drop logins on server...

Linked Server Oracle Unable to execute select QUERY - Gentlemen I have browsed lots of threads after which i achieved my initial target, however, this is something i could...

Database Mail - I had DB Mail working properly while using our ISP as the SMTP server. We switched to Google business mail...

Database Mirroring - I came across DB Mirroring where the owner of the database is different in principal & mirror. So i hav e...

SQL Server 2005 : Backups

SQL doesn't see .bak file? - Sorry, this is probably a really dumb question, but when I go to do a restore in SSMS, when I...

SQL Server 2005 : Business Intelligence

OLEDB Error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER - I have a OleDB connection mgr being used in a SQL Server Destination task in a pkg in which I...

Incremental Loading using sqlserver 2008 - I have a scenario where I have to calculate the incremental data ( insert,update and delete). The architecture is as follow: SSIS...

SQL Server 2005 : Development

SQL Question - Hello, I need your help in the following scenario. I have customer information in two tables, ProductA_Customer and ProductB_Customer. I am trying...

SQL Server 2005 : SQL Server 2005 General Discussion

Issues with saving in SSMS - Hello, When I create a new query in SSMS then try to save it, I get an error message with a...

SQL Server 2005 : SQL Server 2005 Security

Restrinct for insert update and delete from a specific table - hello experts, In my existing database one table we are going to create like Sql_Audit_table. I want to restrict each and every...

Can it be possible SQL Login creation with an empty password - Hi I would like to know that, Is that possible to create SQL Login with blank password in SQL Server... Please advise...

SQL Server 2005 : SQL Server 2005 Performance Tuning

max server memory configuration - hello all, please find below configuration windows server 2003 enterprise edition 64 bit sql server 2005 enterprise edition 64 bit Total RAM= 127 GB my...

SQL Server 2005 : SQL Server 2005 Integration Services

Event Handlers in SSIS(OnError & OnPostExecute) - Hi, I am using a SSIS package to write the records to the flat file destination. During this process I...

SQL Server 2005 : T-SQL (SS2K5)

Delete From Scripting Help - Hello – I currently have an SSIS package running and the first part of it deletes all data in (Table A)...

Case Select Sum - I have the following sql: [quote]SELECT Id, Name, CASE ( (SELECT SUM(Revenue) FROM myTable WHERE [Start Date] = '2008-08-01' AND Name = 'myName') ) WHEN > 0 THEN...

SQL Server 7,2000 : T-SQL

Need help with a query please - ID lotCode lotCurrentQty lotNetWeight NbPal spCode 64 0113047522003 0 0 0 NULL 10 0113047522003 16 516 1 SP0113000033 11 0113047522003 14 457 1 SP0113000034 65 0113047522004 0 0 0 NULL 12 0113047522004 19 480 1

SQL Server 2008 : SQL Server 2008 - General

Changing the system date on a server running SQL Server instance - I am recently supporting an application that can be classified a 'rules engine' and the developers and business rely on...

SSRS Newbie -- Question on combining data from different instances in 1 report - Hi All, I'm just learning on SSRS and needed clarification. If I want to create a simple report that outputs...

Change Data Capture. - I am trying to enable Change Data Capture on SQL Server 2008 R2 DB. When I enable CDC on a table...

SQL dependency - Is there any free script or software for SQL dependycy? Red-gate has one but need to purchase.

Edit multiple stored procedures - I need to create 5 test databases by copying the live db's. There are many stored procedures in each db....

How get some informations about MSSQL (options , memory...)? - Hi everybody , I need help : 1) I want to know the value of options "Partitioning" and "Bit-Mapped" (In oracle , informations...

SSIS to Excel export error - An Script task in SSIS fails with error saying excel cant access 'C:\....\Windows\Temporary Internet Files\Content.MSO\'. Any resolution to stop this...

issue using the Konestan File Watcher Task in SSIS 2008 to move files between network folders - Hi, I am using the Konestan File Watcher task for SSIS 2008 within a For Loop Container to watch a specific...

Can someone help me with a script please? - Hi, I got very nice assistance last time i had an issue on this site. Can anyone assist me? I would...

SSIS Package with a Send Mail Task works perfectly within BIDS but does not work in a SQL Agent Job. - Hi, I have a SSIS Package with a Send Mail Task in it. This works perfectly within BIDS...I can get the...

Fuzzy searching - Hello everyone, I'm having trouble finding a way to use fuzzy searching (specifically Levenstein) in my database. For now, I've...

Currious thing about Count - I accidently had an unfinished piece of SQL code in my query window during execution. The code was [code="sql"]select count(*)[/code] Now to...

reinitialize subscription cancel - One of our IT staff has accidently initiated a reinitialize subscription on a 150GB database via a VPN link. Anybody...

space check - how to check the free space on clustered drives which are having mountpoints?

Update table values if corresponding values change in another table - Hi, I am very new to SQL and really dont know how to phrase my question. There are 2 tables linked...

split mirroring - is any technique split mirroring in sql ? if yes can any one explain how to do ?

Lock Wait - Hi, How to find out the total amount of time spent on lock wait event using T-SQL Query

sql server agent is not sending dbmail - Hi everyone, i've got a problem with the agent. I've create a procedure to check the memory and, in case is...

DB Backup Script - i am using a very simple script to take back up of a TEST Database on production server. SCRIPT IS: use master backup...

RAID 5 Vs RAID 10 Performance Questions - I'm guessing this could lead to a lot of theory and complex answers, however, I'm curious on your thoughts between...

Utilizing Fake Partitioning with a View - So you are running MS SQL non enterprise edition, meaning you can not utilize true partition tables. You have a...

Indexes - Hi All, I have a table [BusinessProcesses] with BpId as primary/clustered key/index on it. I have the following index on column CorrespondingContractNumber: [code="sql"]CREATE...

Stored procedure really slow..Sugestions? - [code="xml"]USE [] GO ALTER PROCEDURE [dbo].[NIC_ENTERPRISE_PatientActivityLogSearch] @lUser INT, @szFirst VARCHAR(20) = '', @szLast VARCHAR(40) = '', @szChartNum VARCHAR(10) = NULL, @SDate DATETIME = NULL, @EDate DATETIME = NULL, @szIdentifierValue...

Need procedure to delete .bak - Hi , I had scheduled backups in Express edition 2008 using cmd and taskmanager. But I wantedto have an automated process...

IF EXISTS(SELECT 1 FROM ...) giving unexpected results in a TSQL job step - Weird one SQL 2008 Standard SP2 on Windows 2003 The SQL below checks to see if a given job ('Production DB...

SQLPS issue - I have two node windows cluster on which SQL2008 R2 is running. When I execute SQLPS, it throws error [b]"Drive...

Looping through table - I am simply trying to look through a table select out email(recipient), and combing to columns into one(body and unique)...

SQL 2008 R2 Build 10.50.1790.0 - MS11-049 is this a windows update. I can get sql to 1777 build. Using cu packs. How do I use...

grant access to all databases - hello experts, i have around 600 databases in my server, a user need select access of all the databases. will...

Running SSRS report from SSIS package - I have an SSIS package. It updates a table in SQL Server 2005 database. There is another SSRS report that...

SQL Server 2008 : T-SQL (SS2K8)

Partitioned table JOIN query performance - I have five large (500m+ records) tables with differing informaton and column structures. However, each of these tables have five...

Simple Interview Question - select 5 & 17 What is the Output? Ans is :1 1)how it will return 1 ? 2)what is the use of...

How to make code for below requirement? - Hai Friends, I m currently making one web application in that i wanna display details from data base into asp page...

can I use transactions to rollback only the current batch - I have a package with design as below 1.I have DFT to load a Total_table with 90k records 2.it is connected...

Get values from 2nd Table - Can someone show me how to combine these two tables and get one result set CREATE TABLE_1 ( Field1 VARCHAR(4) NULL,...

Insert into Table by whom ?? - I have one SQL Server table (one of many) that night will be filled. Nobody knows which program, service or...

how to return email id if it contains dell.com otherwise any one email in the group - if an email contains %dell.com% then return it otherwise max(email) / min(email) just one please help me, [b]DDL[/b] create table #onner (acctno int,...

Finding unequal column values with multiple column comparison - Hello All, I need your help in writing a query for below scenario, Lets assume there are two tables, Table A has 4...

SQL Server 2008 : SQL Server Newbies

How make procedure of below requirement in sql server 2000? - Hai Friends, I m currently making one web application in that i wanna display details from data base into asp...

Using a Temporary Table in a View in Order to Combine three Queries - Hi all. I am trying to create a view using the three queries below and I get the error message[b]...

Trying to convert varchar to datetime - I am trying to convert 20114 which is stored as varchar to datetime but I am getting the following error Syntax...

SQL Server 2008 : Security (SS2K8)

Grant permission to access only 2 tables in a database - Hi I am a newbie, I need to give an access to a selected i.e to a particular 2 tables...

SQL Server 2008 : SQL Server 2008 High Availability

Give me free advice !!! (mirroring , Replication) ? - Hi there I wanna give a online backup from my SQL server 2008 and i don't know how to do...

SQL Server 2008 : SQL Server 2008 Administration

delete old SSIS log files using maintenance plan - We have a SSIS log folder that has a lot of daily log files for SSIS packages. I am hoping to...

SSIS job - I have a ssis package given by a developer that imports data from an oracle database to SQL server. I setup...

Job STep with multiple commands - Good day I want to make a job step,but it must do various commands.The steps is as follows. 1.Copy a file over...

Will using a Temp table as working / staging tables reduce the log backup size? - I just want to clarify something. I am trying to reduce the size of the log file backups created by certain...

Need a blocking script with specific details - Hello All, I am looking for a script that will give me these details. 1) spid 2) Total number of spids...

shared & Exculsive latches? - Hi, PAGEIOLATCH_SH --------------- wiat_time_S 175094.26 Pct 1.05 Running_Pct 96.99 PAGEIOLATCH_EX -------------- wiat_time_S 119320.6 Pct 0.71 Running_Pct 98.53 these shared & Exculsive latchs acquired in waits stats in cummulative number since restarted server,...

rebuild index sql server 2008 standard edition - Can any one help me out in rebuilding the indexes in sql server standard edition wit out taking the database...

Career : Certification

How many tests do I need for 2012 after I get my MCITP - OK, I'm desperate to figure out how many exams I have to take after I finish the SQL 2008 exams...

Programming : Powershell

Powershell error - Ok in ISE , fails in Agent job - Morning all. I am trying to run script to get Translog details. The script works fine in ISE and produces report...

SQLServerCentral.com : Anything that is NOT about SQL!

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

SSRS Data Driven Subscriptions - Hi all, I'm relatively new to SSRS (my version is 2008 r2) so I apologize if this is a question that...

Reporting Services and Access Services - Hi, our environment is SharePoint 2010, with SQL Reporting Services in integrated mode. We have been using this environment for...

Data Warehousing : Integration Services

Flat File Source -> DB table overwrite in T-SQL 2008R2 - Hello SQLSC, I am extremely new to SQL. As you can tell is my first post on the forum, I have...

Problem with Data source, Pls help me - Hi All, Greetings! I have requirement like, -- We are maintaining queries in a table -- By using single ssis Package we need to...

Send Mail Task works perfectly within BIDS but does not work in SQL Agent Job. - Hi, I have a SSIS Package with a Send Mail Task in it. This works perfectly within BIDS...I can get the...

Add row value from a data flow to an object variable - Hi, I would like to add a column value in a data flow Task to an object variable using the script...

Flat File Source: prevent records from loading with a valid date but in the wrong format - Hi Guys, Is there a simple solution to this. I have a flat file which is being generated by a 3rd...

SCript component Binary code code not found error always - SSIS2012 - I have spent three days on this not able to find an anserr Added script component transform open the edit script...

Using SIS to move a file to a IP address - I have an SIS package running on a cloud server and I am trying to setup a connection to send...

Data Warehousing : Analysis Services

Please help with the mdx query - Below is the calculated member [Estimated Value]= IIf([Measures].[PRC]=0,null,Sum([Product].[MM#].members-[Product].[MM#].[All],[Measures].[PRC])/[Measures].[MM Count]) SELECT {[Measures].[SO Actual Qty],[Measures].[Estimated Value]} ON COLUMNS, { ([Business Unit].[Business Unit].[Business Unit].ALLMEMBERS )} ON ROWS FROM...

Can i convert select statment of an mdx query into an calculated member - Please find the select query as below SELECT {[Measures].[SO Actual Qty],[Measures].[Estimated Value]} ON COLUMNS,([Business Unit].[Business Unit].[Business Unit].ALLMEMBERS ) ON ROWS FROM [SMS_CURRENT_WW] Please...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com