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 Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
SQL Backup Pro "Cut the backup time by hours and the file size by 80%" Hazel Cawood, Systems Analyst
Find out how much time and space you can save with SQL Backup Pro. Get compressed, encrypted and fully verified backups. Download a free trial now.

In This Issue

Using FULL JOINs to Compare Datasets

This article describes a technique of using FULL JOINs to compare two datasets within a numerical tolerance. More »


SQL Server Trace Flags

A comprehensive list of trace flags for SQL Server that you can use to configure your server instance. More »


SQL Server Central Webinar Series #23: Safeguard your data offsite with SQL Backup Pro

You may already have a great backup and recovery plan. However, if something were to happen to your databases and you needed to restore from your backups, you’d want to be in control, with the ability to access a copy of those backups quickly, restoring them with minimal downtime and minimal fuss. In this session Grant Fritchey, SQL Server MVP, will discuss the key reasons why you need to have offsite backups, and the advantages of hosted storage. He’ll address some of the fears surrounding cloud backups, and show how offsite backup is made quick and easy with the new ‘backup to hosted storage’ features in SQL Backup Pro 7.3.  More »


From the SQLServerCentral Blogs - Exploring Excel 2013 for BI Tip #6: GeoFlow–The Latest Excel Visualization

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly... More »


Editorial - Accelerating Your Career

I ran across an article on ways to accelerate your career and for the most part I think these are good ideas and suggestions. Networking, finding a mentor, and more will help you no matter what business or field you are working. Some of the advice is geared towards those people looking to climb the corporate ladder and move into management, which is not necessarily what many of us want. If that's the case, ignore those items. There was one item, however, that I thought was particularly interesting for data professionals.

The seventh item on the list notes that you should spend 10-15% of your time working on a project that's outside of the scope of your job or team. That might sound crazy, and even dangerous, but it's a good idea if your boss isn't opposed to it. Having knowledge about the way your business works, the way they use data, or solve a problem, could be valuable in your existing job. You might notice a pattern or way in which you could improve either your job or someone else's. Over time, helping in a variety of departments builds friendships, increases your networking, and might show your boss you deserve a raise.

Many of us end up working with data as a widget. The job of a developer or DBA is writing code or managing data, and sometimes don't often think much about the actual industry in which we are working. Gaining deeper knowledge of the way your particular business works means you can better understand why you are asked to solve a particular problem. That knowledge can lead to a better solution. It might also make your job just a little more interesting.

For the typical US worker, that's 4-6 hours a week. For the IT person, that might be a little more time, but I think it's worth the investment. I wouldn't be overly driven in this area, and if I were working on extra projects at work, I'd eliminate (or cut down) on the time I was spending learning new technologies. Life requires balance, and if you tackle something new in one area, make sure you know what you can give up.

» 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:

The code for this question should work in SQL Server 2008, 2008R2, and 2012 but has only been tested in SQL 2008 R2. The question is: when this code is run, how many rows do each of the three selects return?

-- ****** first switch off nested triggers 
EXEC sp_configure 'show advanced options', 1;
go
RECONFIGURE ;
go
EXEC sp_configure 'nested triggers', 0 ;
go
RECONFIGURE;
go

-- ***** next delete any left over tables/views 
if OBJECT_ID('dbo.vtmp1') is not null drop view vtmp1;
go
if OBJECT_ID('dbo.vtmp') is not null drop view dbo.vtmp;
go
if OBJECT_ID('dbo.tmp1') is not null drop table dbo.tmp1;
go

-- ***** now create the base table
create table dbo.tmp1 (x int identity(1,1) primary key
  , y varchar(6) not null
  , a int not null
  , b varchar(6) not null
  );
go

-- ***** create views and triggers 
create view dbo.vtmp with schemabinding as
  select t1.x, t2.a, t1.y, t2.b
    from dbo.tmp1 as t1 inner join dbo.tmp1 as t2
      on t2.b like t1.y+'%' or t1.y like t2.b+'%';
go
create trigger dbo.vtmpdel on dbo.vtmp instead of delete as
   delete dbo.tmp1 where x in (select x from deleted where LEN(y) != LEN(b));
   delete dbo.tmp1 where a in (select a from deleted where LEN(b) != LEN(y));
go


create view dbo.vtmp1 with schemabinding as
  select y,x,b,a from dbo.vtmp
go
create trigger dbo.vtmp1del on dbo.vtmp1 instead of delete as
begin
  delete dbo.vtmp where x in (select x from deleted where LEN(b)!=LEN(y));
  delete dbo.vtmp where x in (select a from deleted where LEN(y)!=LEN(b));
  delete dbo.vtmp where a in (select x from deleted where LEN(b)=LEN(y));
end
go

set nocount on
go
-- ***** populate the base table 
with tly(i) as (select 0 union all select 1 union all select 2 union all select 3 union all select 4)
insert dbo.tmp1(y,a,b) select REPLICATE(char( 65+i), i+1),5-i,REPLICATE(char(65+i),5-i) from tly;
go

/* ***** get the the count of rows in the top view three times:
before deleting anything, after the first delete, and after the second delete  ***** */
select 'select 1', x, a, y, b from dbo.vtmp1  -- select 1
delete dbo.vtmp1 where x = 4
go
select 'select 2', x, a, y, b from dbo.vtmp1  -- select 2
delete dbo.vtmp1 where x =3
go
select 'select 3', x, a, y, b from dbo.vtmp1  -- select 3

-- ***** tidy up part 1: drop the views and table created above *****
delete dbo.tmp1
go
delete dbo.vtmp
go
delete dbo.tmp1

-- ***** tidy up part 2: enable nested triggers (ie restore the default) *****
EXEC sp_configure 'show advanced options', 1;
go
RECONFIGURE ;
go
EXEC sp_configure 'nested triggers', 1;
go
RECONFIGURE;
go
EXEC sp_configure 'show advanced options', 0;
go
RECONFIGURE;
go

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

This question is worth 2 points in this category: Triggers. 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.

SQL Server Execution Plans

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!


Yesterday's Question of the Day

Given this table and data:

CREATE TABLE #Test
( ID INT
, IsAvailable INT
);

-- Insert into table
INSERT INTO #Test (ID, IsAvailable)
SELECT 1, NULL
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3, 1
UNION ALL
SELECT 4, NULL;
GO
If I want this output:
ID IsAvailable
1  Apr  9 2013 11:38AM
2  1
3  1
4  Apr  9 2013 11:38AM
Which query will give the correct output?

Answer: SELECT ID, CASE WHEN IsAvailable IS NULL THEN CONVERT(varchar,GETDATE()) ELSE CONVERT(varchar,IsAvailable) END IsAvailable FROM #Test

Explanation: When we use ISNULL or COALESCE then the value IsAvailable which is of INT type now gets converted to Datetime and display value of year 1900. So to overcome those incorrect result we need to use CONVERT.

Ref: http://msdn.microsoft.com/en-us/library/ms184325.aspx

» Discuss this question and answer on the forums

SQL Server Execution Plans

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!


Featured Script

Check Candidate Columns for FOREIGN KEY Constraints

Reports table columns that are not foreign keys but that perhaps should be. 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

Cannot drop mapped windows login from SQL Server due to ENDPOINT permission - Trying to do some housekeeping of our SQL logins and I noticed that my active directory account has a mapped...

Frequent corruption of xpstar90.dll - [font="Verdana"][font="Tahoma"]Hi, I am using SQL SERVER 2005 SP 3 & have seen frequent corruption of xpstar90.dll and most of the queries...

Maintenance plan save - Catastrophic failure - Hello, I have an existing maintenance plan that reindexes and updates the stats of some tables. I have selected some of...

Operating system error 5(Access is denied.) to master.mdf'' - having trouble restarting sql server service. here is what I see in the error log... any ideas? 2007-04-04 16:34:16.31 Server      Microsoft...

SQL Server 2005 : Backups

SQL server logs backup / reduction - Intro: I have sql server installed on c drive but the logs (LDF) is stacking up and I am getting out...

Log backups while Full/Differential backups are running - A few weeks ago I was at an event where someone came up and asked me if it's bad to...

Deattach or take offline the DB to copy - Hi, Do I have to Deattach or take offline the DB to copy the .mdf and .ldf files to other machine. I...

SQL Server 2005 : Business Intelligence

DB permissions on basis of application accessing db - Post removed, wrong forum topic.

Error while importing multiple sheets of same excel into one table using ssis. - Hi, I am importing multiple sheets of same excel into one table using ssis. I am following below flow. Data flow task->Excel Source->Look...

SQL Server 2005 : Development

Automatic Update from Excel to SQL - I need to load the data from excel to a table in SQL Server 2008 and automate the updates.ie., If...

SQL Server 2005 : SQL Server 2005 General Discussion

A domain error occurred in Sql server 2005 while updating the data - I have created a function like the one below Create FUNCTION fn_Calc (@Lat1 Float, @Lng1 Float, @Lat2 Float, @Lng2 Float) RETURNS Float AS BEGIN Declare @x...

SQL Server 2005 : SQL Server Express

SP4 on Express - Hello Friends, I want to update sp4 on SQLExpress 2005 edition. Do I need to reinstall the SQL server Express...

SQL Server 2005 : SQL Server 2005 Integration Services

load multiple xml file into a single flat file destination - hi guys i need help, i've created a ForeachLoop Container which loop through all the files .xml in my source directory...

SQL Server 2005 : T-SQL (SS2K5)

nested left and right query? - Is it possible to nest (or perhaps stack via CTE) left and right? For example, if i had a list...

Email validation question - Hi, A stored procedure calls on this UDF. I have to update it to exclude email addresses with '.@' or '@.' in them. [code="sql"] GO /******...

Help please - This is probably a simple join issue but here goes... We have a system (HAL) that does not contain the Dr...

SQL Server 7,2000 : T-SQL

Quote placement Sp_msforeachdb - I need some help with the quotes for the creation of this column with SP_msforeachdb. Here is the original version that...

sql query count - I have a table as below . Id Product1 Product2 Product3 1 1 null null 2 1 2 null 3 3 1 null 4 2...

SQL Server 2008 : SQL Server 2008 - General

jobs execution monitoring - Hi I wonder if there is an article about monitoring jobs, something similar to backups recent article script, we have...

A backup is made on SQL Server 2005, can it be restored to SQL Server 2008 R2 engine? - Quick question experts! A backup is made on SQL Server 2005, can it be restored to SQL Server 2008 R2 engine?

Partitioning a table with 1.5 Billion Rows - Hi, I have a table in our data warehouse with 1.5 Billion rows that we need to partition it based on...

Having real trouble...although it's probably easy! - I asked for some ideas about this last week, but nothing quite suited my situation, which is kinda tricky...maybe if...

Slow performing query! - The following SQL query isn't scaling very well! Works great on record sets of 10-20 million. However, now that I'm...

Communication Link Failures - Hello, I am receiving Communication link failures in SQL which are causing my backups to fail. This has been occurring off...

Check String for some chacters - I need to check to see if string contains (,-, / or . and pull out only the numbers from the string.... 212/2222255 should be...

MS-SQL instance failing due tempdb space issues - Hi, I should clarify my instance is Cluster aware and running SQL2012, but posting here because I believe this is more...

Make a select from three columns (with case expression) - HI, I have a table (named empresa) which has several fields. Three of the fields are: perc_cs_publico perc_cs_privado perc_cs_estrangeiro I want to make a select that...

Urgent help! - Hi I ran a xp_cmdshell 'copy con.....' command on my live server to create a file. It didn't work for some...

T-SQL HELP NEEDED - [code="sql"] Create table Mytemp (Id int , value nvarchar(100), name varchar(50)) insert into [rbamouser\parAPatg].MYTEMP VALUES(1,'B,C,D','XYZ') [/code] mY O/P SHOULD BE LILKE 1 B XYZ 1 C XYZ 1 D XYZ KKINDLY...

disk usage by top tables - script to find disk usage by the top tables on a database in sql 2005\2008

Arabic text is displayed ? reading xml - Hi all, Please help me as it is very urgent !! When you paste the below query, it should return arabic text...

Different in count of records. - Hi All, when i am trying to find the count of records, i am getting two different counts. example: select * from table --1500 select count(*)...

mixed data columns - I currently have a column that have both numeric and characters: Example: 3 PF 11 PF PF 3 3 I am trying...

Pivot - Hi , [code="other"] Create table temp1 (Num varchar(50), id uniqueidentifier) create table temp2 ( num1 varchar(50), value varchar(max) ) ---- INSERT INTO [temp1] ([Num] ,[id]) VALUES ( '22', '50C6CC7C-140E-4697-9287-748AB307C497') INSERT...

DATA consitency errors - I dont have the backup when we ran the dbcc check db got the data consistency errors , how can we...

Schemas and Roles - Hi, I am confusing about the usage of roles and schemas. I have done some research on Internet, and I found...

i need .bak files - Hi, i need .bak files for testing purpose. where can i get that files........ Regards, shiva

Help with Triggers - I have to create 3 triggers for a table - INSERT, DELETE, and UPDATE. The triggers will be used to update...

large object inBuffer cache alert - Hi Can anbody advise on how to handle an alert that tells me i have a large object in the buffer...

Normalization - Is It possible To Identify A Table By Seeing it Whether It is Normalized Or Denormalized?

Advice on problems with "Client Processing Time" - Hello all, I've encountered a strange problem on one of my test SQL Servers. It is a VM box on a SAN,...

Is it possible to create tables Dynamically using Cursors from another table with csv values - Have a Table with the CSV Values in the columns as below ID Name text 1 SID,DOB 123,12/01/1990 2 City,State,Zip NewYork,NewYork,01234...

Help interpret SQLIO result - Im trying to either prove or disprove that the slow front-end (web ui) performance is, in some part, attributed to...

Can full text search on a column in Multi Languages like En&Fr? - I have a table column contains English and French. My FTS always returns something unexpected. I am not sure because...

CMDExec Proxy account not working with Domain Service Account... - I'm working on migrating an existing Server 2003 / SQL 2005 system to Server 2008 R2 / SQL 2008 R2, and am...

Fixing Divide by Zero Error - Hello, I have in select statement select t1.A/t1.B but get divide by zero error when I used with isnull isnull(t1.A/t1.B,'-') I get error...

Is there a better way to insert data than Access? - Is there a better tool to insert data into SQLserver than Access ? For managers. Small number of people. Very limited...

option (maxdop 1) - I've noticed some blocking sessions caused by the following SQL but it's nowhere to be found in my SP's and...

SQL Server 2008 : T-SQL (SS2K8)

Interview Questions - Hi I am facing following interview questions. table i am having one column .That column records like following SATHEESH KUMAR ARUN [b]rahul[/b] [b]s[/b]URYA S[b]elvi[/b] Now i...

Generate SSRS Report and then Email Report from a single Stored Procedure - Can someone please offer assistance? I need to create a Stored Procedure that will generate a SSRS reports a number...

convert tSQL mySQL to MSSQL - Hi Guys need some help with converting this part of a query from MySQL, I need to run this on...

Find Serial number in sql string - So i need to query Active directory and get all the computer based on certian OS type, no problem doing...

filter duplicate students via T-SQL - /* I need to filter for duplicate students: --------------------------------------- For same student, if one or both instance/s of IDNo is null then do...

tsql query - Count the number of spaces in a string - How do I write a query that tells me how many spaces (...or any character for that matter) are in...

SQL Server 2008 : SQL Server Newbies

How to find values in a comma delimeted string with len>3 - Hi everyone, can you please help me with the following? I have a nvarchar column with data like this: ', 55,85,1,4,9888,6587,' How...

Slows system performance - Dear All I am running a process (updation/deletion/insert) of 2 million records. After some time this process makes my system very...

SQL Server 2008 : Security (SS2K8)

DB permissions on basis of application - Hi A few users who has access to a database via a windows group which has modify rights, now also need...

Stored Procedures Execute Permissions - My global office programmed all our stored procedures and it contains a lot of create/delete temp tables, truncate tables, update,...

SQL Server 2008 : SQL Server 2008 High Availability

SQL SERVER 2008 r2 cluster - Hi everyone , i have a query regarding the SQL SERVER 2008 r2 clustering , we have now one cluster on the...

Oracle Virtual Machine and SQL Server 2012 Clustering - Hello All, My current project is running on Oracle Sun Fire x2-8 server and they have brought me in to...

Log Shipping Failover Test - Hi I'm currently running some DR testing and have failed over a couple of databases to our DR site. Generally the...

SQL Server 2008 : SQL Server 2008 Administration

SQL Performance Monitoring software - Hey guys, I'm looking for a good performance monitoring software for our sql servers. So far I was able to find...

How to Give User Access to another user - [font="Verdana"]Hi All, In Server I have 2 users one is MasterAccess and other one is ap. there are many tables but...

Database syncronization script for DR - Hi All, My client is planning to do a daily sycn using backup / restore script. weekly full backup follwed by daily...

TempDB on Amazon Ephemeral drives - Has anybody used the EC2 Ephemeral drives as a location for their TEMPDB databases? Normally I use a standard volume but...

ssas understanding - I am currently working on SSAS 2008.Gone through ebooks for understanding but never got proper indepth understanding of dimensions and...

Break the replication - I have configured the Transaction replication Here i want to know how to break the transactional replication and i want re...

Transactin Log Issue - Hi, I am having issue with out Transaction logs as it's keep getting full even after shrinking the file. We have Sql...

Index Maintenance Strategy - Hi SQL-folks, I've asked myself, what would be the best strategy to keep my indexes always optimal and up-to-date. What are...

Transactional replication issues - Hello There, I am new to this forum, Could anyone help me with this questions please. I am working in retail industry and...

SQL Server 2008 R2 SP2 and KB2551254 - Hello, I'm in process of installing several servers with SQL Server 2008 R2 SP2. So far I've completed 5 of them...

Career : Certification

70-461 - Done, but more difficult than expected - Just passed the 70-461 SQL Developer Exam Today. This exam is much harder than I expected. Its quite syntax heavy, and asks...

Programming : XML

Query XML which has multiple child and subchild - Hi , We have a requirement in which we need to Query an XML which has child and sub child(structure mentioned...

SQLServerCentral.com : Anything that is NOT about SQL!

crystal reports version 8 user guide - I am looking for a Crystal Reports version 8 user guide. I have done an exhaustive google search with no...

Ok, so it sort of is about SQL.... - ...but this made me laugh today... Seen in some production code: [code="sql"] LEFT OUTER JOIN [highlight="#ffff11"]Northwind[/highlight].dbo.orders_actioned ON scheme.opheadm.order_no = [highlight="#ffff11"]Northwind[/highlight].dbo.orders_actioned.order_no [/code] ...just WOW!

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

SQLServerCentral.com : SQLServerCentral.com Website Issues

Print View - For the "Print" link on this site, can you remove the code that automatically brings up the print dialog? You...

Reporting Services : Reporting Services

Reporting Service Home Page - Instead of getting the Reporting Service Home Page with the Content & Properties tab when clicking on a given URL, I...

Is it possible to bind a ServerReport to a ADO.Net Datasource? - I'm kind of new to Reporting Services, so bear with me. But as we use it now we write reports...

PowerPivot intergration to Sharepoint - Hi, I am new to reporting but I have a report in Power Pivot which is using a stored procedure to...

One Dataset populating multiple Reports - Good Morning I have a stack of data and I want to be able to create a single report, this report...

SSRS Report Model Forms Authentication for Web Access and model filtering - We have clients wishing to build custom reports on their data. Is there a way where we can give them a...

Textbox fill color based on textbox value - Hello, I have a report with a series of textboxes. The value of the textboxes is based on the month and...

Drill down Report...Remove the Expand (+) and Collapse (-) button for some groups - Hi.... I have implemented a Drill down upto 3 levels. First level is Continents... when a Continent is expanded.... Countries in...

Data Warehousing : Integration Services

ssis data flow task taking an extremely long time . - Hello everyone . I have a log table that I am planning to import from my Transaction Database to my reporting...

How to avoid technically the null values while concatenating columns - HI, In SSIS by using dervied column i manipulate fields and end up in my desired results. But for an record if...

SSIS package failure on renameing files Error code: 1073548540 - Hi, I am getting following error when package is executed from BIDS: Error: 0xC002F304 at File System Task, File System Task: An...

Ho to Use multiple conditions in Xpath ? - Hi guys, New to Xpath. Was trying to use XML task to load some values. Using Microsoft' XML inventory mentioned below. How...

Excel Source DT_NTEXT Problem - i am trying to load the data from excel files and facing problem in Text data. Excel source in SSIS tries...

SSIS Data Flow Task Crashing - I have a simple data flow task that moves logging events from one database to another. It pulls log events...

Data Warehousing : Analysis Services

How can I sum these values to only show total YTD during selected period until today´s date? - with member Measures.Today as vba!dateadd("yyyy", -1, vba!Now()) member Measures.FirstDayOfCurrentMonth as cdate(cstr(vba!Month(vba!dateadd("yyyy", -1,vba!Now()))) +"-01-"+Cstr(vba!Year(vba!dateadd("yyyy", -1, vba!Now())))) member [Measures].[YTD] as YTD([Dim Time].[Date].&[2010-04-15T00:00:00]) select [Measures].[Belopp] on columns, non empty...

Retrieving the ALL member only - Hi, I have a problem with retrieving the ALL member. With MDX executed from SSMS, it returns the 'All' value into a...

Third Party Products : Embarcadero

DB Artisan - Anyone else using this product??? I am using version 8.1.2 and I have to say it is extremely comprehensive and...