In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle ‘Six Scary SQL Surprises’
Brent Ozar joins the DBA Team, for Lesson 3 of their ‘Top 5 Hard-earned Lessons’. Gain valuable tips from the pros - Read now
 
SQL Compare Pro Compare and sync databases with SQL Compare
“SQL Compare is fast, extremely easy to use, full-featured and affordable. I wouldn't bother messing around with anything else.” Adam Machanic, SQL Server MVP. Download a 14-day free trial.
 
SQL Monitor Custom metrics from SQL Server MVPs
SQL Monitor is the only tool with a free library of custom metrics from SQL Server MVPs. Find new metrics for your servers

In This Issue

Stairway to PowerPivot and DAX - Level 4: The DAX BLANK() Function

Business Intelligence architect and author Bill Pearson exposes the DAX BLANK() function, and then provides some hands-on exposure to its use in managing empty values underlying our PowerPivot model designs. More »


SQLServerCentral at SQL Intersection

Steve Jones and Grant Fritchey will be speaking at the SQL Intersection conference in April. Come join them in learning about SQL Server in the spring. More »


Database Deployment Challenges

There are a number of challenges that make the deployment task more difficult. Alex reviews the common techniques for deploying new databases and upgrading existing ones, and their flaws, and argues the advantages of an automated, incremental, script-based approach to deployments. More »


From the SQLServerCentral Blogs - SSIS 2012 Deadlock When Running SSIS from SQL Agent Job

I recently had an interesting situation where an SSIS package was scheduled to run at 1 AM but failed with... More »


Editorial - The Backup Process

I was reading Scott Hanselman's post on The Computer Backup Rule of Three, and I completely agree with him. If things are important, three copies are the minimum you should keep. I have had backups before, but I've found that when something breaks, like a tape drive or hard drive, I might not get to replace it right away. Usually this is procrastination, or as it's more commonly known, laziness. During that window of opportunity, I've had Mr. Murphy strike. My inherent paranoia of having that second extra copy has saved me, both personally (at home) and professionally (at work).

All too often I run into DBAs that seem to trust and assume their backups and processes are very reliable. After setting up a backup process, usually to disk, and testing it with a restore on another system, they assume it will work in the future. It should, but there is always the chance something will fail at some point in time. There's the chance that some hardware failure or software reconfiguration will cause an issue with your process. There's also the simple chance that your I/O system might introduce corruption into your backup files.

If you aren't regularly testing your processes, you can't be sure they are working as they have in the past. The ideal environment would perform a restore of every backup file taken, every day, but that isn't always possible. However restoring a random backup once every month or two will help you to ensure your backup, and restore, processes, have the best chance of succeeding when you actual need to perform them because of a disaster. 

This isn't hard to script and automate, but it is something you need to do periodically. However even if you automate restores of all your databases, make sure you still practice your manual skills regularly. Automation might not work in a disaster situation, but those quarterly practice sessions restoring a database to a point in time with a tail log might just relax you when a crowd in your cube is asking if the database will be back up soon.

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


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

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.

You can also follow Steve Jones on Twitter:

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


Question of the Day

Today's Question:

--I have the following table

CREATE TABLE #T(ID INT,[Year] VARCHAR(4),M VARCHAR(2),D VARCHAR(2),H VARCHAR(2),Mi VARCHAR(2),S VARCHAR(2),MS VARCHAR(3))

Various clerical personnel have entered into table #T the following data.

INSERT INTO #T 
SELECT 1,'2013','1','22','11','11','11','995' UNION ALL
SELECT 2,'2013','1','22','11','11','11','992' UNION ALL
SELECT 3,'2013', '1', '31', '15', '34', '21', '003' 

To verify the input data I then created the following T-SQL statements.

DECLARE @Y VARCHAR(4),@M VARCHAR(2),
        @D VARCHAR(2),@H VARCHAR(2),
        @Mi VARCHAR(2),@S VARCHAR(2),@Ms VARCHAR(4),@IC INT 

SET @IC = 1

WHILE @IC <= (SELECT MAX(ID) FROM #T)
 BEGIN
  SET @Y = (SELECT [Year] FROM #T WHERE ID = @IC)
  SET @M = (SELECT M FROM #T WHERE ID = @IC)
  SET @D = (SELECT D FROM #T WHERE ID = @IC)
  SET @H = (SELECT H FROM #T WHERE ID = @IC)
  SET @Mi = (SELECT Mi FROM #T WHERE ID = @IC)
  SET @S = (SELECT S FROM #T WHERE ID = @IC)
  SET @Ms = (SELECT MS FROM #T WHERE ID =@IC)
  SELECT @IC + 3 AS 'Select number', DATETIMEFROMPARTS (@Y,@M,@D,@H,@Mi,@S,@Ms) AS 'Datetime'
  ,CAST(@S AS CHAR(2)) + ':' + CAST(@Ms AS CHAR(3)) AS 'Original seconds input'   SET @IC = @IC + 1 
END

The question is: which select number returned the datetime value with the same number of seconds as was input. The (same number of seconds) is expressed as 59:005 and this is NOT the correct answer.

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

This question is worth 1 point in this category: DateTime. We keep track of your score to give you bragging rights against your peers.

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

 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!


Yesterday's Question of the Day

What will the following expression return?

select isnull ( convert (char(3),1/9) ,'*')

Answer: '0'

Explanation: The expression 1/9 is calculated as an integer because of implicit conversion therefore result is 0.

0 is not null so * will not be printed.

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Featured Script

Script to get max dates on tables that contain a certain column.

This script will allow you to get maximum date associated with tables that contain a certain column.  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

Filegroups - Hi guys I have a problem, i'd created a new file group and i wanted to transfer some tables to the...

SQL Server 7,2000 : T-SQL

INSERT into Excel leaves values as text - I use TSQL to create Excel files and send them as reports to a list of people. I use the...

SQL Server 2008 : SQL Server 2008 - General

XLSX Import Error: result code db_sec_E_auth_failed - I'm trying to import a 190 meg .XLSX file to a SQL 2008 (not R2) server. First, I installed "Office...

send Table name to procedure as a parameter - hi i create a procedure in my database.this SP select all rows from a table : [code][b]CREATE PROCEDURE FillTable AS BEGIN select * from...

Transact replication on two computers - Hello I am trying create transactional replication with updatable with subscriptions on two PC ( between instances database is OK ). On PC1...

SQL Server 2008 R2 - Failed to connect to SQL Server - Hello everyone, I am having an issue and I am not sure why. I have every thing set properly I...

problem in execute the stored procedure - hi i craete a SP in my database . by this command : USE [MyDatabase]; GO CREATE PROCEDURE test AS SET NOCOUNT ON; SELECT id, feed1,...

Data not inserting - Hi i have written a query to check the insertion but its not working neither getting any error : Declare @valuebased...

Database name is visible but not available. - When I reboot my server and log into SSMS my database is listed but it cannot be expanded. The table...

SQL 2008 - Min and Max server memory - For SQL 2008, the min and max server memory settings are limited by total memory. If my server has 4gb...

can we install 2008 R2 integration services and 2012 integration services on the same server - Hi DBA'S I had 2012 integration services installed on a server. i am trying to connect to Oracle but it is...

Upgrading/moving datbases frm 2005 to 2008 - After moving databases from 2005 to 2008, do we need to do run any queries (like update usage which we...

SQL Server 2008 : T-SQL (SS2K8)

Pivot Query with Count - Hi there I am fairly new to SQL Server TSQL and I have a strong MS Access background. I can't get my...

problem in select command - hi please look at this select command : [Code][b]select top 2 * from dbo.NFFeeds order by id desc [/b][/code] for my database the...

Get missing records - Hi, I have a table #TransDetail CREATE TABLE #TransDetail ( ID INT IDENTITY(1,1) PRIMARY KEY, Number INT, TransDate SMALLDATETIME, TransTime INT, Site...

SQL Server 2008 : SQL Server Newbies

I wish to compose Normalised tables for the below requirment Please Help. - There is requirement as below to create the Data Model for estimation in No of tables as of now Openings Resumes Available...

Error "Must declare scalar variable ..." when using cursor - Hi, Hope you can help me. Every week KPI's for our HR-dept. have to be generated. The problem with this is that the...

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'Noida' to data type int. - CREATE TABLE EMPLOYEE ( EMP_ID INT PRIMARY KEY, FIRSTNAME nvarchar(100), LASTNAME nvarchar(100), Salary INT, CITY nvarchar(100) ) insert INTO EMPLOYEE VALUES(2, Monu Rathor, 4789.00, Agra); insert INTO EMPLOYEE VALUES(4, Rahul Saxena, 5567.00, London); insert INTO EMPLOYEE VALUES(5, Prabhat...

Do Users based on logins in master have access to other databases? - When I create a user and select master as the default database does that mean or imply that user can...

Creating my first UD - I really like sql server, but one of the things that drives me crazy is formatting columns. The numbers in...

Microsoft Access 2010 & SQL Server 2008 Express - What's the best way for clients to connect to an sql server? Should all clients have their own seperate Access...

SQL Server 2008 : Security (SS2K8)

smk and dmk in sql 2008 - How to view service master key and database master key in sql 2008 and what is the actual use of...

what is certificate and its usage - I want to have more clearity on what certificate actually holds.when to use it preferably. BACKUP CERTIFICATE sales09 TO FILE = 'c:\storedcerts\sales09cert'...

SQL Server 2008 : SQL Server 2008 Administration

can not find Model after Master restore - I was doing some testing to ensure I had all the version build files required to restore the Master database...

Suggestions on TEMPDB Setup - I have a teradata environment with database aroudn 3TB. This server has around 40 core processors. Microsoft suggested to Use...

Programming : General

A tricky one - Hi I have a table of links to images. I have an identity key for a particular image but i want...

Programming : XML

XML Special characters - DECLARE @t TABLE(a VARCHAR(100)) INSERT @t SELECT 'abc' UNION ALL SELECT 'd<c>f' UNION ALL SELECT 'ghi' SELECT CAST( (SELECT a+',' FROM @t...

Reporting Services : Reporting Services

Best Practice: Custom Front End for SSRS - Hi All, I am from a small software company that sells products exclusively for the mining industry. We have decided as...

Data Warehousing : Integration Services

Only one column not extracting from Excel source - I am trying to extract the data out of an excel file with 3 columns Success, Failure and Waiting. The...

Data Warehousing : Analysis Services

trying to make my first report and it is not going :( - Hi, I am new here, I have a question, unfortunately every time I solve it on my own I keep mixing...