In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Deployment Manager How to automate your .NET and SQL Server deployments
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial.
 
SQL DBA Bundle ‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest instalment of in the Top 5 hard-earned lessons of a DBA – read it now.
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.

In This Issue

Stairway to PowerPivot and DAX - Level 5: The DAX ISBLANK() Function

Business Intelligence architect, Analysis Services Maestro, and author Bill Pearson exposes the DAX ISBLANK() function, and then provides some hands-on exposure to its use, particularly in combination with other DAX functions, in managing empty values underlying our PowerPivot model designs. More »


Community-driven free Azure tips eBook

We’re putting together a free eBook of 50 tips for Azure Storage, if you’ve got tips that you’d like to share we’d love to hear from you. More »


Monitoring Transactional Replication in SQL Server

If you are using replication in SQL Server, you can monitor it in SSMS, but it makes sense to monitor distribution jobs automatically, especially if you can set up alerts or even set up first-line remedial action when a problem is detected. Francis shows how to do it in T-SQL as an agent job. More »


From the SQLServerCentral Blogs - Hunt Down Tables Without Clustered Indexes

This one's a gimme, but an underrated utility script when I enter a new environment and look for low-hanging fruit... More »


Editorial - A Broken Data Model

On one of my recent trips, there was a delay in leaving the gate. There were two passengers, both of whom had the same seat assignment on their boarding pass. You'd think there would be some sort of primary key that prevented this situation from occurring, but with the constant reassignment and movement of passengers at the gate, I can understand this happening. We print passes at a point in time, but since data can be revised, and new passes printed, there potentially would be a conflict.

However what came to light as we waited was both disconcerting and puzzling to me. The real problem was that one of the passengers was supposed to be on a later flight to the same city.

Apart from the security implications, it seems to me that the ability of someone to get through the scanner near the jetway with a boarding pass for the wrong flight implies a broken data model. I can only guess that the scanner is reading a passenger's name, destination, and seat number. Or that the flight data model is broken and there are two flights on the same day that share a flight number. The journey was short, so I suppose this airplane could have actually been making two round trips in a day.

I can't image the complexity and problems that programming an airline's flight system must bring to the data models and applications. With the constant shuffling of people, flights, and seats, I'm regularly surprised that it works smoothly most of the time. As my airline has modernized their systems, I'm even amazed at how much data they disclose on monitors to passengers waiting for upgrades or standby flights, and how quickly my mobile app updates with new data when there's a change.

I'd never experienced someone getting on the wrong flight before, and was surprised to find their software allowed it. However given that so much of the airline industry relies on systems that were developed decades ago, perhaps I shouldn't be surprised.

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

Due to some technical issues, there is no podcast today, but we will return with a new episode tomorrow.

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:

create table address_staging  
 (clientid int primary key,addressdetails varchar(250));

insert into address_staging
 select 100,'hyderbad,india'
 union all
 select 101,'banglore,india'
 union all
 select 102,'banglore,india'
;
create table address_oltp 
 (client_id int primary key,address_details varchar(250));

insert into address_oltp
 select 104,'newyork,usa'
 union all
 select 105,'chicago,usa'
 union all
 select 106,'washington,usa'
;
select *
 from address_oltp 
 where client_id in (select client_id from address_staging)

drop table address_oltp;
drop table address_staging;

How many rows are returned from the last SELECT?

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

This question is worth 1 point in this category: Subquery. 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.

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Yesterday's Question of the Day

In SQL 2008, SQL 2008 R2, and SQL 2012, assuming that the database Playpen exists and you have full DDL and DML access to it and that nested triggers have not been disabled, what happens when the following code is executed?

use playpen
go
create table dbo.t1 (id int identity(1,1) primary key, x int not null);
insert t1(x) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
go
create view dbo.t1v with schemabinding as
  select t.id as id, t.x as x, t.x+1 as y, t.x-1 as z
    from dbo.t1 as t where x/2 = ceiling(x/2.0);
go
create trigger t1vdel on dbo.t1v instead of delete as
  delete t from dbo.t1v as t where exists (select * from deleted d where d.id  < t.z);
go
declare @before int = 0, @after int = 0;
select @before = COUNT(*) from t1v;
delete t1v where id > 1;
select @after = COUNT(*) from t1v;
drop view t1v;
drop table t1;
select @before/@after;

Answer: no errors and the final select returns 2

Explanation: The view definition is such that any deletion of update can be applied automatically to the original table without needing a trigger (ref: http://msdn.microsoft.com/en-us/library/ms187956.aspx). If this were not the case, the trigger definition used here would be invalid, because it defines an instead of trigger which executes an update which appears to invoke itself, but this is OK as the view is updateable and instead of the trigger being invoked again the nested update is applied directly to the underlying table (ref http://msdn.microsoft.com/en-us/library/ms189799.aspx). Thus there are no errors in the DML blocks. Both counts in the final block produce non-sero values, so there is no error there either. What the two counts are is straightforward SQL arithmetic.

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Featured Script

Script to send an email based on contents of a job step's output file

Use to check for a string value in a job steps output file. If that string exists, send an email with the output file attached. 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

Login Failed error (Linked server) - I am trying to access data through linked server by following query select * from [Servername].dbname.schema.view_name and my schema is not dbo. When I...

Maintenance Plans - Modify does not work !? - Hi all, I have created several Maintenance Plans in the past - I now want to modify one of them ..... So a...

Stuck with this update statement - I have a update statement which is killing my PLE( Page Life expectancy), it is dropping from 4000 to 120....

Log shipping 2k5 file retention issue - the files are not being deleted. - The issue I have is with the file retention of the .trn logs in my log shipping drive. 5 days...

Memory Optimization - Hi All Environment: Windows 2003 Enterprise Edition - 32bit SQL 2005 Standard Edition - 32Bit PAE has been enabled Server has 16GB of ram installed and 8GB...

Safenet encryption: queries return null - Something about our production sql cluster is preventing the initial setup for Safenet encryption. The device sends sql to our...

Memory Poke - I have a supplier who insists on having two jobs that every couple of hours change the Max Memory setting...

Backup - Hi All, I am trying to back up 6 Databases with the total of 108GB combined, all the databases are in...

SQL Server 2005 : Backups

Redgate Differential Backup and Restore - I've recently taken over database duties and am trying to learn as I go. Our company uses Redgate 6.5 for...

Back up scripts - Hi guyz! I want to create scripts that can do a backup on a database on regular basis. How can...

SQL Server 2005 : Business Intelligence

SSAS Calendar Week commencing with split at month - Hi all, Wonder if someone can help. Our CFO wants to see a cube calendar that shows week commencing but stops at...

Career on MS BI - Hi Everyone I'm planning to take up MS BI training. I possess the knowledge of SQL DBA and working as a...

SQL Server 2005 : Development

Android Development - Hi, i am newbie to Andorid development .. suggest me good material to this journey thanks

Creating XML file using Excel Data - I need to create an XML file populated by data from a CSV file. I have the following XMLSchema template: [code="xml"]

SQL Server 2005 : SQL Server 2005 General Discussion

A way to auto disconnect all connections to a database in SQLsrv2005? And self intro - Hi. I am new to the forums. Thank you for having me. I have provided some bio info in my profile for those...

PROFILER - Trace Templates that include Groups - Does anyone know if it is possible to save to template a Trace file that includes 1 or more GROUP...

SQL Server 2005 : SQL Server 2005 Security

Alter schema statement - Incorrect syntax near '\'. - I am trying to use the alter schema statement on a table with this name: ABC\genn.smith.itemresults with this statement ALTER SCHEMA dbo TRANSFER...

How do I limit user access to certain time of day - We have a group of queries that we want to limit to run off peak hours to stop from bogging...

SQL Server 2005 : SQL Server 2005 Performance Tuning

CPUs and SQL Server - Hello, I have a server running an instance of SQL Server 2005 and SQL Server 2012 (both standard editions). The server has...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS and MIMER - Hi, I have to copy some data on a nightly bassis from a MIMER database hosted on a UNIX platform....

How to read data in a pdf file in SSIS - I was wondering if any body had a situation where data needs to be extracted from pdf files and exported...

Exporting Multiple SSIS packages from MSDB - If we need to export a package from MSDB into the file system we can do it using the SQL...

SQL Server 2005 : T-SQL (SS2K5)

Can a CURSOR be populated by firing a stored procedure? - The subject says it all. Can I do this? DECLARE Test AS CURSOR LOCAL FOR [b]EXECUTE uspMyStoredProcedure[/b] OPEN Test FETCH Next FROM Test...

Data transfer while dynamic increment of duplicate values. - Hi, I have to insert data from one table to another in SQL 2005 (destination having lesser but the same name...

compare varchar dates - I have a varchar column containing dates (not my design) with this format 2013-02-12 I need to extract records between two...

XQuery Help - Hi fellow developers. I am new to XQuery and have an issue. Based on the following XML I can't get...

SQL Server 2005 : SQL Server Newbies

Relationship between two tables (what is correct?) - Hi, can someone help me with this basic doubt? "tnx in advance" I have two tables (groups, rol), I need to...

SQL Server 7,2000 : Administration

CmdExec - Jobs - Hi Everyone, I'm using CmdExec to call VB Application to extract records. The application is running fine. But when I put...

SQL Server 7,2000 : T-SQL

SQL statement for new vs duplicate data - I'm looking for a SQL statement that will output 'new vs duplicate' data on a monthly bases, but I can't...

SQL Server 2008 : SQL Server 2008 - General

SSIS Data flow failing - I have a data flow that has been working for months. Starting this week it will transfer some of the...

Need some help generating an XMF file using SSIS - Hi Guys, I have a requirement to produce daily price files for transmition to our website hosting team who require a...

Adding license key though command prompt - We have an eval version of Sql Server 2008 installed on one of our servers, and we want to add...

DB400 to SQL Server data extraction - Hi all, We have a DB400 (DB2 based AS400 platform) database and I'd like to pull data from it using SQL...

Maintenance Plan - Unable to Modify in SQL 2005 - However after upgrade it works !? - Hi all, I am using a Test and production envoironment - both were 2005 - I have created several Maintenance Plans in the...

Cannot get to install SQL 2008 R2 on Windows 2003 x64 - Hi, Error is: Could not load file or assembly 'Microsoft.SqlServer.Configuration.WizardFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The module was...

Which Stored Sproc is causing Performance Issues - I don't have a problem at the the mo. But was wandering after making a fairly large deployment of multiple...

Run-time error "-2147467259 (80004005): - heloo dear .. i am facing this error from last 4 days ...... please give me solution . Run-time error "-2147467259 (80004005): [Microsoft][ODBC Driver...

INDEXING - Hello, i have maybe stupid question why sometimes the SELECT use Noncluster index instead cluster index I thought that still use PK...

if does not exist then insert row - Hi Professionals. I have two table which are identical apart from the data in them lookuptable with columns raw_sw_manufacturer, amended_sw_manufacturer, raw_product_name, amended_product_name, raw_product_version, amended_product_version lookuptable1 raw_sw_manufacturer,...

BEGIN TRY - Catch not working - 1) Run job say TestJob 2) Now execute below Begin try - catch for already running job and you will see below...

DENY ALTER DATABASE (or something else) to db_owner? - We have a number of databases that are now encrypted. Someone deployed a release to one of them and the...

How do I retrieve a value in a text column that has a XML - Folks I have a table with a column "message" whose type is "TEXT". The contents inside this column is XML. Here is a...

SQL 2008 - File Group change - Changing Primary filegroup to a new filegroup 1. Any negative impact i should cover before making the above move. It...

Excel 1 : Databases 0 - SME addicted to Excel! - Hi folks, I feel like I'm banging my head against a brick wall so figured I'd ask for some sage advice!...

find pairs of NULL records without resorting to CURSORS - [code="sql"] create table #tmp ( ref int, Date datetime, time int, seqno int ) insert into #tmp (ref,date,time,seqno) values(34227664,'20130227',0,399850) insert into #tmp (ref,date,time,seqno) values(34227664,'20130304',0,399860) insert into #tmp (ref,date,time,seqno) values(34227664,'20130313',0,399870) insert into...

SQL Server 2008 R2 poor performance on VMWare - We recently migrated our production server from a physical cluster environment to a VM environment, and ever since then the...

Error message during deletion - Hi All, I am trying to delete a record from a table using a stored procedure.While executing the SP i am...

Help with SQL Server backups slow ( backup performance slower than before after data purge) - Hi everyone, (searched SQL Central... I didn't find anything specific) Wanted to get some ideas on solutions. I have a DB...

Need help rebuilding a SQL server with replicated databases - We have a production SQL server in a virtualized environment and we need to move it to a physical server...

Insertion in table - HI, This is the table structure and script below: CREATE TABLE [dbo].[GV_Booklet]( [BookletId] [int] IDENTITY(1,1) NOT NULL, [LeafCount] [nchar](10) NULL, [Denomination] [int] NULL, [VoucherTypeId] [int] NOT...

Has anyone used Data Domain storage for SQL 2008 backups? - We are creating a Microsoft Failover Cluster with Windows Server 2008 and SQL Server 2008. Our storage administrator would like...

Row Compression vs. Page compression - Ok - trying to get a better understanding of when it's best to use Row compression vs. Page compression?

SQL Server 2008 : T-SQL (SS2K8)

Query seems to be constrained by single core CPU in multiple core server - any suggestions? - I receive data from an external data provider which comes in as a single large table. The data represents balance...

filtere index benefit - i need to create a query on a table where the condition is such that we select the rows where...

Why XML Datatype present in SQL Server. - Hi all experts, Every now and then i am learning something new in SQL Server. Latest in that series is XML...

Syntax to call a function correct in one databse but not another? (both SQL 2008 R2) - Hi All I have a function that works in one database but not another. Both databases are SQL2008 R2 64bit. The...

LEFT and RIGHT of Delimiter - I have a value that is '0111~Group Name' in a column called GROUP_NUM in a table called TEMP_TABLE I need to...

Partition Results - There is a heap like the one shown below: [code="sql"]USE tempdb; CREATE TABLE Heap ( [Column 0] NVARCHAR(100) ); INSERT Heap ([Column 0]) VALUES ( '01...

GROUP BY question - Morning all, Could someone help me generate the following grouping please? Sample data plus expected output below: Input Table: [b]SiteMatches[/b] Expected Output: [b]SiteMatches_Output[/b] [img]http://i.imgur.com/7FGfQPu.jpg[/img] [code="sql"]CREATE...

Isolation Level - Hi, I have a scenario in which I am performing insertion on a table using a stored procedure. For every...

conversion of the varchar value overflowed an int column - I have a stored procedure : In this prcedure I am returning @pSTN value and used in another sp as output....

Possible to use tored proc to kill all queries running longer than 2 hours AND are blocking other queries - Is it possible to use a stored procedure to kill all queries running longer than 2 hours AND are blocking?...

is it possible to union all two CTEs? - I have two CTEs sharing same column names and I need to combine their results into one result set. Given...

SQL Server 2008 : Working with Oracle

Oracle Linked server randomly failing from sql agent job - I have a number of jobs that fail because of a linked server message. [code="other"]Executed as user: Domain\User. Cannot initialize the...

SQL Server 2008 : SQL Server Newbies

Queries for Multiple Databases - I am trying to write a query that will search for databases created in the last week. Once I get...

SQL server as a back end for Access - Looking for good and/or bad reasons to use SQL server as a back end component to access 2007 and use...

SQL Server 2008 : SQL Server 2008 High Availability

check if database exist before restore - hi, i'm in the process of automating the database restore using powershell.... but before getting the default paths of data and log...

Ownership of cluster disk 'Cluster Disk xxx has been unexpectedly lost by this node. - My Cluster went down again. I don't have to say... I am having a not so good morning already ... :-( Here's...

SQL Server 2008 : SQL Server 2008 Administration

Restore of ReportServer and ReportServertempdb - I have to convert a named instance to default instance before that i have taken backups of all databases including...

How to configure sql alert to mobile SMS - We need to configure sql alert (SMS- short messaging service) (Log file full, and database down etc) to our on-call...

When DB restored to different server will database mapping and permissions stay? - On Server A I have a database the is overwritten daily by a database on another server [Server B] After the...

Programming : General

SQL Agent Jobs in SSDT - I've got 2 SQL Agent jobs to deploy with a database, the whole lot being in a Sql Server Development...

Programming : XML

XML.Value when it has attributes? - Based on a forum post here, i'm having trouble reading XML when one of the elements has attributes, and was...

Programming : Powershell

replace string in path using powershell - Hi, i'm trying to get the default sql server data and log file path using the below script... [quote]param ( $ClientName, $DESTINATIONDB ) $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlCmd = New-Object...

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

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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

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 Report breaking with an error "System.UriFormatException: Invalid URI: The Uri string is too long." - Need quick help on this issue. Environment Details: Reporting server migrated from 2005 to 2012. But we haven't migrated rdls and these...

Export to TIFF - Good afternoon, I have a report that if I exported to PDF would be in excess of 10 pages. Due...

Monthname: Is there a bug in this function - Can someone tell me if there is a bug/issue with the SSRS built in function MonthName The following expression in the...

Reporting Services : Reporting Services 2005 Development

Report does not display correctly in Safari - I have a series of reports, actually we call them dashboards because they have summary data, not lists. Anyway, the dashboards...

Data Warehousing : Integration Services

Hw to execute stored procedure using select statement? - I have execute my stored procedure using a select statement like Exec SP where select * from view,wt is the finall...

excel migration issue - Hi. We run a STD2008 64 bit relational engine SP1 in production. We recently migrated a pkg that reads an...

Hw to import the system varibles into an xml/csv file - Hw to import or export system varibles into an xml or csv file ,if any one tell me if it...

Hw to view package execution stups without using visual studio - hw to view the packege execution stups , if we are not using the visual studio,can some one tell me r8...

DataType Change - I'm working on some real time project. There is an already existing DataBase. In that DB, i need to change...

Whats the easiest way to read the first 10 characters of a flat file - Basically I am recieveing multiple flat files of two different file layouts, the file names do not give a clue...

Data Warehousing : Strategies and Ideas

Strategies for dealing with Excel - Like a lot of organisations, we struggle with the fact that a lot of users manage important business data in...

Data Warehousing : Analysis Services

What SQL Server Settings for SSAS Tabular - Goal: *The goal is to view if SQL server 2012 tabular and its in-memory ability with can be used in...

Mdx Weird Error Message - Hi, I have a cube with 3 partitions, and several dimensions. When I run the following query: WITH DYNAMIC SET Top5Ip...

Microsoft Access : Microsoft Access

Slow performance after Migration from Access to SQL back-end - Hi. i was able "after a long time of trial and error" to migrate access database to sql 2005 and use...