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

The Human Touch

How often does human error cause issues? Recently we had a rocket crash in Russia,there have been numerous incidents of drone crashes as more and more unmanned aircraft take to the skies, and a few years ago we had an Air France disaster that might have been cause by humans making poor decisions or engaging the wrong controls. Those are incidents where the wrong button press has large consequences, either in physical damage or the loss of life.

Many of us make mistakes constantly as we work in the various tools and environments we need throughout our day. We click the wrong button in SSMS, we connect to the wrong server and run a script, or we fail to test a change. All of these are mistakes made by humans, and often are mistakes that can be prevented if we did a better job or sticking to routines and processes. That can be hard, but perhaps checklists can help here, along with some double checks by coworkers.

That's why I think using scripts in T-SQL, and using the Script button in SSMS to generate the code that you can run (and save) is the best way to work with your servers. As much as I think Powershell (PoSh) can be a pain to write and debug, there's a good argument to be made for using it when performing complex administrative tasks, especially across servers. Using code rather than forms and buttons is just a better way to accurately and consistently make changes in a controller manner.

This is an area where everyone could work more efficiently. Developers are usually used to working with version control systems and tracking all their changes. However they often will make configuration changes to their machines, SQL Server, IIS, or some other software, and forget to track the changes. Making these changes in code, through T-SQL or PoSh, and tracking these items in VCS, would help with smoothing software deployments. DBAs and other operations staff should learn to use version control systems  as well, helping to track down root causes to issues.

Ultimately humans are often the weaknesses in most systems. We should understand that, accept it, and compensate as best we can.

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 ( 15.5MB) feed

MP4 iPod Video ( 19.7MB) feed

MP3 Audio ( 3.9MB) feed

Feeds are available at iTunes and Mevio

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

ADVERTISEMENT
SQL Backup Hosted

Easy offsite SQL backups

Protect your backups offsite quickly and easily using SQL Backup Pro. Plus get your first 5GB of storage free. Find out more.

SQL Source Control

Get your SQL Server database under version control now!

Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…

SQL Monitor

Get alerts within 15 seconds of SQL Server issues

SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.

Featured Contents

 

Schema-Owned Tables and SQL Server's Generated DROP Script (SQL Spackl

Brandie Tarvin from SQLServerCentral.com

Not all SQL-generated scripts are created equal (or correctly) for alternative schema-owned objects. More »


 

Disaster Recovery Tip #8:Maintain up-to-date contact information

Press Release from SQLServerCentral.com

A corollary to Murphy's Law states that disaster is most likely to strike when your senior people are out of the office. More »


 

Intervals and Counts - Part 1

Additional Articles from SQL Server Magazine

When using SQL Server, you frequently need to work with data that represents intervals of time. For example, consider intervals representing sessions, contracts, projects, and so on. Tasks related to interval manipulation are typically quite intriguing, especially because coming up with efficient solutions isn't easy. Itzik Ben-Gan explains More »

Question of the Day

Today's Question (by Naseer Ahmad):

Which option would we use to encrypt the definition of the view and prevent users of the database from seeing it?

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 1 point in this category: Views.

Did you miss yesterday's question, DBCC CHECKDB - 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

Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012 is a deep dive into perhaps the single-most important facet of good performance: indexes, and how to best use them. The book begins in the shallow waters with explanations of the types of indexes and how they are stored in databases. Moving deeper into the topic, and further into the book, you will look at the statistics that are accumulated both by indexes and on indexes. All of this will help you progress towards properly achieving your database performance goals.

Get your copy from Amazon today.

Featured Script

Automate Data Purity Investigation

Richard Fryar from SQLServerCentral.com

A couple of weeks ago I wrote an article at www.sqlcopilot.com/dbcc-checkdb-with-data_purity.html about the DATA_PURITY option of DBCC CHECKDB, and how to identify the affected columns.

One of the ways to find affected columns is to run a SELECT with a WHERE clause to return out-of-range data. But this doesn't always work. It is possible for data to be within a valid range but still fail the data purity check. It is also possible for the column to be totally corrupt, resulting in an arithmetic overflow error when you attempt to SELECT it.

The method I showed in my article for when a SELECT doesn't help was to use DBCC PAGE. But if you have more than a few columns with invalid data, it can be quite time consuming to do. This was the case for me recently when I migrated a database from SQL Server 2000 to 2008 R2 and the CHECKDB found 540,000 data purity errors!

A SELECT for values outside the range for the datatype (decimal(23, 8) in this case) didn't return any data and so DBCC PAGE was the only option - but obviously there was no way I could run it manually 540,000 times!

The script attached to this article was my solution to the problem.

1. It runs DBCC CHECKDB(dbname) WITH DATA_PURITY, NO_INFOMSGS, TABLERESULTS and captures the results in a temporary table

2. It extracts the page, slot, object id, column name and data type for each row returned

3. It loops through the results perfoming a DBCC PAGE for each one to get the primary key values of the rows containing the invalid data.

Within this loop, there is also code to derive the condition for the primary key (allowing for multi-column keys). The key value is then used to query the table to get the current value of the affected column. The results are stored in a table, tmp_final_results. This can be dropped once all investigation is complete.

There is further code, commented out, that I will explain shortly.

How to use the script

This is a 3 stage process.

1. First run the script within the context of the database to check.

It may take several hours to run, especially if you have a few hundred thousand data purity errors.

When it has finished, the table tmp_final_results will contain a row for each out-of-range column.

2. Use the first commented out section of the script to view the results.

Note: the conversion of [Value] to varchar is necessary, as attempting to retrieve some out-of-range data may result in arithmetic overflow errors. By converting them to varchar, these are displayed as -1.#IND instead.

3. Fix the data

Now you have to decide what to set each one to.

If you are lucky you may find that all the values are within a valid range for their datatypes, so a straightforward UPDATE to their existing value will fix the problem. The third section of my script (also commented out) generates an UPDATE statement for each column.

However, you may find out-of-range values, and some may be displayed as -1.#IND. For these you have to decide what they should be set to, and this means speaking with someone who knows the application well and getting them to look at your data.

4. Tidy up

The final commented out section needs to be run to drop the tables generated by the script.

Please remember that this script is a tool to aid in the identification of columns that have failed a data purity check. You should not blindly update the columns - ensure you are 100% confident of the correct values. However, the code in section 3 of the script is available if you decide the suggested values can be used. And of course, backup the database before making any data changes.

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

Full text index causing recompilation issues - a weird one perhaps? - Hi, This morning I pushed a number of new full text catalogs, indexes and procedures to a SQL 2005 (SP2) server,...

Big Data for Sql ? - hello Guys, As the big data is picking up in the market I would like to know which are best suited...

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

SQL Server 2005 : Business Intelligence

SSIS error - Hi All, I have installed the SQL server 2008 R2 version, when I tried to add Data flow task in SSIS...

Do I have to use a stored procedure in exe SQL task to use a parameter? - I want to set a conditional in an exec sql task based on the value of one of my ssis...

Using a specific date table in SSAS 2012 Tabular - Hi, I have created a few fairly basic OLAP cubes using SSAS 2005 in the past. Basically I created a single...

Execute Child packages parallel in loop - hi , I have a master package which calls the child packages from a folder dynamically, when i tried this with...

How to change the connection string of child packages in Execute Package task? - Hi Friends, I am tryting to execute multiple packages so i am using Execute Package Task to run the packages...

SQL Server 2005 : Development

Timetable query, using CASE - Hello I know this is a total newb question, but i have the following SQL: [code] SELECT FagNavn, adgangskrav, Ugedag, holdId, fagKode, CONVERT(CHAR(10),...

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. - Hello All I m not able to find this error Subquery returned more than 1 value. This is not permitted...

SQL Server 2005 : SQL Server 2005 General Discussion

create table valued function - i need to create a function passing those two varaibales. How do i do that? Table valued function select DISTINCT bb.level...

SQL Server 2005 : SQL Server 2005 Performance Tuning

"DBSCHEMA_TABLES_INFO" error while querying table in linked server - Dear All, When I try to query one of the table in my linked server am getting the following error....

SQL Server 2005 : SQL Server 2005 Integration Services

database manager is not able to accept new requests - Working on this issue ... SSIS package trying to load data from DB2 to SQL .... IBM OLE DB Provider for DB2"...

delete files using ssis - Hi...I want to build an SSIS package that deletes the log files whose names are like Package20081002.txt. The names of...

SSIS package to post a text file in ftp site - Hi all SSIS experts, I have a task to read certain columns from the db, convert it into a tab delimted...

SQL Server 2005 : T-SQL (SS2K5)

Problem with CTE and filter in Query - Hi, I have this query that if I filter by 'AND [ORG LEVEL 2] like '%ZSW' it gives me the monthly...

help with incrementing - I am not sure if "incrementing" is the right word to describe what I need but here it goes. I...

SQL Server 7,2000 : Administration

Install MS SQL Server 2000 Report Services - Hi Guys I need urgent help. I'm trying to install MS SQL Server 2000 Report Services on our DR ISS server,...

SQL Server 7,2000 : SQL Server Newbies

Case When Resulting in Null - Hi All, I just need clarification of why a field for some records would result in a null value for a...

Database Design For Multiple Product Types - I have a database containing different product types. Each type contains field that differs greatly with each other. The first...

attempting to recreate complex "multi-layer" Access queries in SSMS - Please excuse me if Im not usung the right terminology. I am a SQL Server newbie and have a basic...

SQL Server 2008 : SQL Server 2008 - General

Create User - Hi Team, how to create a new user with Password User Name : STLOK Password : STLOK and it should be read - only user.

Change query from sql 2000 to 2008 - Hi, I want to modify the below query which exists in 2000 to sql 2008. Can u please help me? select * FROM...

Data import - Greetings friends, I have a .dta file that's formatted in a strange way and I would like to discuss ways of...

Find executed scripts - Hi Team, am having 10 select statements and 10 Insert statements, and executed all the 20 statements individually. Now i want to...

Export Calendar items from Exchange 2010 Public folder to MS SQL 2008 - Overall situation: We are now migrating from a Windows 2003 Server domain to a Windows 2008 Server R2 domain. At...

Query from SSRS to SSMS - Hi Team, We have a Report (SSRS) with select query, when we generate the report, Database is hanging, how to find the...

How to set publication compatibility level for transactionnal replication ? - Hello, I'm trying to set up transactional replication from an SQL Server 2008 R2 instance to another. The publication created using the...

Disk space used last year - Hi, Can anyone please provide me the query to find the how much disk space used in last year? I need to...

Connect to sever by name rather than IP address - Hi When i first open SSMS the 'Connect to Server' logon box appears. In it the server name drop down box...

Blocking Query - Hi Team, I used to run 'sp_who2' to check the locks, i found that some sp_id are showing in BlkBy column,...

Getting Back Previous Database - Dear, I have restored a database with an existing database. But there was some important information in my previous database. I...

SSIS Lookup Transform Bulk insert error - stumped! - Ok - I'm stumped. I have an SSIS package that reads our CDR data and i need to do an incremental...

Identifying SQL Agent Job Name based on the job id. - I'm trying to figure out which Job is running, based on this the following name. Where can I find this...

Need a help in MCSA SQL Server exam 2012 70-461, 462, 463 - Hello friends, after getting good experience at current workplace, I am planing to get certified in MS SQL SERVER 2008/2012. I went...

How to add an analysis server into SQL server 2008 R2? - I am very new to data warehouse. I just installed Sql server 2008 R2, but when I accessed it, there...

Convert Varchar to Date?? - Hi I have a date field stored in a varchar as "Jul 24 2013 8:05AM" I would like to convert to a...

Incorrect length being returned for FLOAT datatype field - Hello, is somebody able to point out where I am going wrong? We have a table that is of float datatype...

Find and update the Schema changes of a table? - Hi Friends, I have around 25 tables where i am frequently using to put the data from live server to...

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...

Snapshot Agent will not start - I'm working on a 2008 R2 Enterprise SQL Server. There are other working transactional replication publications working on this server. The...

Deploy multiple SSIS Packages in one batch file to SQL Server - Hi, I need to write a Batch file which can Deploy multiple SSIS packages kept at a location. The Batch file...

SQL Server 2008 : T-SQL (SS2K8)

multiple records into single record - I have a task, here in my table I have mutiple records for single icn that means each record is...

SSIS ( cannot able to change datatype column using datatype conversion...... - Hi, Source: multiple text file Requirement: 1. Define age columns as small integer. 2. Convert gross column from $ 667 00 to 667.00...

how to get members of date dimension from current month - Hi , my query is: select distinct calendarmonthkey, calendarmonth from dim.date order by calendarmonthkey [b]Result:[/b] 205801 jan 2058 205802 feb 2058 .... .... 198001 jan 1980 [b]expected result is...

T-SQL subquery - Hello guys, I have performance question or what is better for server I have select [quote]SELECT TOP 5 CONVERT(DATETIME,WOT.ORDER_DATE) ORDER_DATE, WOT.COMMIT_NO, WOT.BODY_NO,...

Salesman Running Totals by Date problem... - Hi All, SQL Server 2008. I have the following problem I need to try and solve... I have a list of salesmen and...

Select variable based upon sub query - I have the following SQL statement (It's actually MYSQL but the principal is the same ;-) ) that selects all the values...

When NULL IS NOT NULL - To all my SQL friends out there. While trying to construct a 1M row test harness I ran across an...

Sum Negative Numbers - Hi All, I have a column GNLBalance with negative and positive balances. I want to add these numbers together when another...

SQL Server 2008 : SQL Server Newbies

tempdb and permaent tables - Hi all I have created some permenent tables(mistake) tables in temp db. But can not view them in managment studio. How...

Need assistance doing a PIVOT-like transformation on VARCHAR data. - I am trying to write a query using the table below - this table comes from a vendor supplied system, so...

SQL Server 2005 and SQL Server 2012 - About start Project with upgrade to IPv6. Need advice on how SQL Server is affected to the 2005 and 2005...

SQL Server 2005 system databases - SQL Server 2005; Was wondering if I can point the system databases to another set of system database files. Such as...

PRIMARY KEY VS UNIQUE KEY* - Hi everyone, Does anybody know any cases/example that we need to use UNIQUE KEY instead of PRIMARY KEY? I understand that...

Not your usual documentation question - I am a server/SQL admin in a large organization. We have thousands of SQL databases on many clusters and individual...

Query hangs on table variable - I have a query that consistently runs fast and returns the expected output, but when I try to insert the...

SQL Server 2008 : Security (SS2K8)

Check_expiration option - Hello friends, I want to create a SQL db user to be dedicatedly used by application only. I am providing...

SQL Server 2008 : SQL Server 2008 High Availability

Very slow for running Query in Standby database? - Hi. Log shipping setup on of the table total Records count 1228976 in standby database, user have read access and using...

SQL Server 2008 : SQL Server 2008 Administration

Connect to SQL Server - Hi Experts, We lost SQL Server sa password and our windows authentication is not allowing us to login . How can i...

Monitoring performance after index deployment. - I am trying to figure out how to monitor performance of the database before and after deploying an index. Are...

How to give users access only to certain columns of certain tables in a database? - Hello, I am new to SQL Server 2008 administration and I have been asked to give a user access to only...

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...

SQl2k8 R2 Ent stops installation after installing setup files - Hi all. I'm having trouble installing SQL 2k8 R2 Ent on a Windows Server 2008 R2 box. This box had 2k8...

Older Versions of SQL (v6.5, v6.0, v4.2) : Older Versions of SQL (v6.5, v6.0, v4.2)

cannot find Maintenance plans - I i have a system using 8.00.2050 version i.e, SQL 2000; I could not see the Maintenance plans Folder under the...

SQLServerCentral.com : Anything that is NOT about SQL!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

SQLServerCentral.com : SQLServerCentral.com Website Issues

What happened to "Yesterdays Question" and answer - I love "Todays Question" and have picked up quite a few tidbits that have been very useful. Often even more...

Reporting Services : Reporting Services

SQL Server 2012 Report Server - I've install SQL Server 2012 and SSRS on the same server. When I use: http://servername/Reports or http://localhost/Reports I can see...

COUNT total qty location by item - I'm quite new with sql and hope someone can help me with the following issue; I have the following result...

nested IIF for font color - Hi I must have something missing unless I can't use IIF for font color? [b] iif(ReportItems!Textbox26.Value >0, "Red",iif(ReportItems!Textbox26.Value < 0, "Green","Black") ))[/b] Thanks In Advance Joe

Help! MDX > 8.000 characters - Hi all, I have a complex MDX that was build within the front-end-tool our users use. Now I want to include...

SSRS Excel export with miscellanious error. - I have a simple tablix report built with SSRS 2008 R2. When I export the results in the Excel format...

Reporting Services slow - My Reporting Services is getting slow to jump from report to report. Is there any way to improve your performance?...

Data Warehousing : Integration Services

Automatically Mapping Flat File Sources Data types to Destination - Is there anyway to automatically map a flat file sources data types to the ole DB destinations data types? I'd...

ADO NET Source works perfectly within BIDS but getting error in a SQL Agent Job - Hi, I have a SSIS Package with a ADO NET SOURCE for capturing data from MS Access DB which is in...

Need MS BI Developer SSIS SSAS SSRS - Please send me your resume amitkadam.mnnit@gmail.com Exp: 3-5 yrs Location: Chennai ( Hexaware) 1.depth knowledge of Microsoft Business intelligence product stack (SQL Server 2008/2012,...

DTS package conversion to SSIS - I'm fairly new at my position and one of the first assigned tasks was to convert an older DTS package...

Data Warehousing : Analysis Services

How to Define a schema for the fact table, and the dimensional tables in SQL from a relational schema? - I am very new to Data warehouse and OLAP, and I dont know how to create OLAP database from a...

DAX - I trying to create a type of inverse filter using DAX. For Ex: [b][u]Filter Table[/u][/b] [u]Column1[/u] A B C If a user filters by A, then...

User defined heirchy levels and Excel Pivot Table - SSAS 2008 R2 Excel 2010 In SSQL Management Studio connected to an SSAS instance i am able to browse a cube. I...

Microsoft Access : Microsoft Access

C# Export Excel - I think this is a great library for exporting to Excel. It is cheap too. [url=https://www.kellermansoftware.com/p-52-excel-reports.aspx] https://www.kellermansoftware.com/p-52-excel-reports.aspx[/url]

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