In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control The best way to version control T-SQL
SSMS plug-in SQL Source Control connects SVN, TFS, Git, Hg and all others to SQL Server. Learn more.
 
SQL Monitor Monitor the data you care about the most
SQL Monitor's customisable monitoring and alerting keeps you up to date with SQL Server performance, wherever you are. Free trial.
 
Red Gate Deployment Manager NEW! Never waste another weekend deploying
Deploy SQL Server changes and ASP .NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.

In This Issue

The SSIS Data Pump - Level 2 of the Stairway to Integration Services

Learn the basics of data flow in SSIS with the data pump in this second installment of our series designed to teach you about Integration Services. More »


Row Sorting in SQL

It should be easy to model a game of poker in SQL. The problem is, however, that you need to model a permutation from a set of elements. Joe Celko argues that using a group of columns to do this isn't necessarily a violation of 1NF, since a permutation is atomic. Then comes the second problem: how would you sort such a column-base permutation in order? Sorting columns in SQL? More »


From the SQLServerCentral Blogs - T-SQL Tuesday #38 – Standing Firm

Introduction Welcome back for the 38th installment in the wildly popular blog party for the SQL Server community.  This is the... More »


From the SQLServerCentral Blogs - Day 1 of 31 Days of Disaster Recovery: Does DBCC Automatically Use Existing Snapshot?

Day 1 of 31 Days of Disaster Recovery: Does DBCC Automatically Use Existing Snapshot? 31 Days of Disaster Recovery Welcome to my... More »


Editorial - DR Prep

A few weeks ago we published a number of editorials and articles on disasters and the challenges this brings to data professionals. As we start the new year, I'd like to get you thinking about how you might change your DR planning or processes for the new year. With that in mind, a simple question this week:

How will you approach disaster recovery or business continuity planning differently in 2013?

I'm wondering if you will change some of your processes, or schedule additional testing. You should have at least one test planned each year, but will you make it more comprehensive? Will you schedule more frequent, but less intensive tests?

Another thing to consider is the skill and experience of your staff. It might pay to. Incorporate some of the recovery skills into your weekly or monthly work to ensure that you know how to perform backups, restore in different scenarios, or even relocate your databases to another Windows host.

Let us know this week how you would like to improve your preparations in 2013 and then spend the rest of the year adding th retails to your plan.

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


The Voice of the DBA Podcasts

No podcasts today due to the busy holiday schedule, but they will return soon.

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


Question of the Day

Today's Question:

List all the differences between a Primary Key and a Unique Key? (Choose 4)

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

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

If you run the following code:


declare	@NewYearsEve datetime;
declare	@NewYearsDay datetime;

set @NewYearsEve = {ts '2012-12-31 23:59:59.997'};
set @NewYearsDay = {d '2013-01-01'};

select @NewYearsDay as NewYearsDay, 
       @NewYearsEve as NewYearsEve,
       dateadd(year, datediff(year,@NewYearsEve, @NewYearsDay),
        dateadd(month,datediff(month,@NewYearsEve, @NewYearsDay),
         dateadd(day,datediff(day,@NewYearsEve, @NewYearsDay),
          dateadd(hour,datediff(hour,@NewYearsEve, @NewYearsDay),
           dateadd(minute,datediff(minute,@NewYearsEve, @NewYearsDay),
             dateadd(second,datediff(second,@NewYearsEve, @NewYearsDay), 
	     @NewYearsEve
	    )
	   )
	  )
	 )
	)
       )as ToTheNewYear;

What is returned as ToTheNewYear?

Answer: 2014-02-02 01:01:00.997

Explanation: The key in getting this right lies in understanding that DATEDIFF and DATEADD are not opposites. DATEDIFF does not tell how much time has passed between two dates; it tells how many interval boundaries exist between the two dates. So although only 0 years, 0 months, 0 days, 0 hours, 0 seconds, and 3 milliseconds have passed between the two dates, DATEDIFF counts 1 year boundary (2012 to 2013), 1 month boundary (Dec to Jan), 1 day boundary, 1 hour boundary, 1 minute boundary, and 1 second boundary. DATEADD does not advance to the next boundary, but instead adds a full interval. So this code adds 1 year, 1 month, 1 day, 1 hour, 1 minute, and 1 second to 2012-12-31 23:59:59:997 to get 2014-02-02 01:01:00.9997 PS. In case you haven't seen them before, the literals in the set statements are ODBC escape clauses. References DATEADD: http://msdn.microsoft.com/en-us/library/ms186819.aspx DATEDIFF: http://msdn.microsoft.com/en-US/library/ms189794.aspx

» 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

Search All Stored Procedures in a Database

Search for a text in all the stored Procedures in a Database 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

SQL Server SP4 - I have updated my SQL 2005 server with SP4 .Is there any cumulative update or hotfixes need to be applied...

SQL 2005 Cluster Breakout - Hi, can anyone please let me know the best procedure to breakout of a SQL 2005 Cluster Setup to an...

Rename Database and Other Querys are Running on the same DB - Hi All, I have List of Jobs scheduled on my server, I have scenario like this [b]Job1:[/b] 1. Copy bkp file 2....

Stange memory and page file behaviour - Hi, I wonder if anyone can shed some light on a problem I experienced the other day. My company operates a...

Linked server for Oracle - Hi, I am having this weird problem with Oracle linked server on SQL 2005 x64. I have installed Oracle client and...

SQL Server 2005 : Backups

Backup on network fails - Hi, I am trying to configure SQL server 2005 backup to a another server drive \\server2\folder. The folder on second server...

Transaction Log - I have a database in SQL server 2005 (64 bit) server. The recovery mode is set to simple recovery, and...

SQL Server 2005 : Business Intelligence

Foreach Loop Container, Traverse through subfolders - I am stuck here and in need of some help. I have a package which archives .mdb files(add to zip, using...

HOW run ssis package on table update - Hi i have created ssis package to move data from one table to other in different instances when i update...

SSRS - Calculate $ cost on monthly basis - I am new to SSRS and stuck at a point and can not move further and so need your help I...

SSIS - E-mailing from causes error - All, A week ago some outside consultants upended my world. They switch the e-mail server from "tom" to "katherine". We...

Extend Timeout on SSRS/BIDS Tooltip - Hi, Fairly new to this so bear with me please. I have a report built in SSRS / BIDS. I have the...

SQL Server 2005 : Development

Excel Connection Manager error in SSIS 2012 - Please assist with error below: Could not retrieve the table information for the connection manager 'Excel Connection Manager'. Failed to connect to...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL 2005 3 node cluster lift and shift with IP change - Hi SQL Experts, We have a activity coming up in our project where sql server 2005 three node cluster will be...

Dynamic Where Clause for Multiple paramaters with AND Operator - Hi I am Trying to Build Dynamic Where Clause for Multiple Parameters and they could be Null Also .Please find...

Job Activity Monitor view - but where are the In Progress jobs? - Hello all, I wrote a view that joins the sysjob, sysjobactivity, sysjobhistory and syscategories tables together to give me a resultset...

SQL Server 2005 : SQL Server 2005 Performance Tuning

SQL Server stopped after changing startup parameters for mirroring. - Dear All, I was trying to implement mirroring in one of my TEST server. After the configuration I got an error...

SQL Server 2005 : SQL Server 2005 Integration Services

Problem with sql statement in vb script using variables. - Hi all, I have been trying to resolve this for hours but failed, hence have to post here. I have this...

Returning Values from a Stored Procedure (SQL TASK, SSIS) - Hi all, Here the problem: I have the following stored procedure: [font="Courier New"][size="2"] ALTER procedure [admin].[up_SetLogicalDate] @QueryDate datetime ,@LogicalDate datetime OUTPUT ,@LogicalDate_Start datetime OUTPUT ,@LogicalDate_end datetime OUTPUT --with execute as caller as begin DECLARE ...

The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. - Hi, I am trying to transfer the data from flat file to sql server.When I am running the package on local...

SQL Server 2005 : SQL Server Newbies

Stripping out all non-numerical characters - SELECT accountid,New_Column = REPLACE(telephone1,SUBSTRING(telephone1,PATINDEX('[^0-9]',telephone1),1),'') INTO #YourNewResults FROM #TelephoneTable I have the script above which I thought would remove all non-numerical characters from the field...

SQL Server 7,2000 : Data Corruption

Logical consistency error - Hi, Often am getting logical consistency error in one my production server. Recently i did DBCC with allow dataloss option, to...

SQL Server 7,2000 : General

CPU Usage goes down to 0 % suddenly - Hi I am running a web application having 500-600 users at a particular time on sql 2000 and ASP.NET 2005. generally...

Import data into multiples tables or one? - I'm building an import package for a CSV file that has about 200 columns. If every column was maxed out...

SQL Server 2008 : SQL Server 2008 - General

cannot connect to sql server named instance - Hi Experts, I created a named instance, I am a DBA , i can connect to named instance using SERVERNAME\INSTANCE name, but...

Suppress automatic Hyperlinks in outlook in HTML format - Happy New Year! Here is my problem that I am trying to resolve. I am sending emails using msdb.dbo.sp_send_dbmail. I have...

Question in regarding to UpdateAdapter with BatchSize - My VB.net code first retrieves data from database, use the fill method to fill to a datatable, makes changes to...

Adding a Sequence Number to the records from LKUP No match output - Hi Friends, I am migrating data from one table to another and I am doing a lookup to find if the...

Cluster design comment wanted. - Hello Forummembers, For all of you a Happy Newyear. I have lost a bit my nightrest over the following: First we do not...

SSIS Package fails in Production - I have created a SSIS data flow package in our development environment that works via debug or sql agent. but...

SSIS count of files processed - I have a package that archives old access database files and then send a mail when it completes. I would...

Getting a synchronized snapshot of several tables - Hi Guys I'm using SQL Server 2008 R2 (Standard Edition) and trying to get a nightly snapshot of several tables for...

Concerns with Transaction replication parameters - Hi SQL Experts, We have an OLTP that was configured in P2P replication. Both the peer nodes were running with...

Simple RDBMS scripts for Login Mockup - I am looking for a quick model to do a mock up from for a login process. I went to...

I need help with my query. I found part of this, but doesn't work. Please help. - I need to get the emails from the n.Note field, which is a nVarchar(max) column. [b] I have the following, but I...

How to copy and paste from a RDP session to your Local Desktop - Hi All, Is there any way to copy and paste from vpn session to our host session? Please advise. Regards, SueTons.

Which free 3rd party tools do you guys use? - Hopefully RG doesn't mind this post, but I'm just curious what freely available add-ons to SSMS or BIDS do you...

Memory management in sqlserver. - Team, I am looking for the TSQL to get the below details. 1)Total memory allocated in the windows machine where a...

Query Help - Hello Everyone, I need one query help [code="sql"] create table #temp1 ( StudentLocalID varchar(9), EnrollSchoolCode Varchar(3) ) create table #temp2 ( StudentLocalID varchar(9), ScheduleSchoolCode varchar(3) ) insert into #temp1 values ('003682153','305') insert into #temp1...

convert -ve int to +ve bigint - Hi, How do you convert the -ve int value to +ve bigint value. I have used cast and convert which didn't help. Example. declare...

Export wizard failed? - Hi, pl. suggestion me, what could be on issues during export to another new database by export wizard? how to resolve...

Record locking - Question1: How can a user, say User1, be able to lock a row, say Row1, and any dependent rows, say...

Upgrading to 2012? - Is it worth upgrading from SQL Server 2008 R2 Developer to 2012 Developer? I believe most companies are still using SQL...

Database restore issue - Hi, I am facing an error while restoring adventure works database to my sql server 2008. TITLE: Microsoft SQL Server Management...

Keeping all three environment(Dev,Stag,Prod) in sync - In our office we run jobs to keep these environment in sync. We get data from outside source and run...

Len did not display right size - I use len function to find out varchar column data size but did not display right size. For example, in some...

Running a sql server 2008 job to back up to network folder - I'm trying to schedule a back up database task to backup some specific databases to a network folder but kept...

SQL Server 2008 : T-SQL (SS2K8)

Dynamic SQL - which would you use? - When I need to execute code depending upon the values of parameters I often implement one of 2 methods: In this...

Null vs '' - Working through an ETL project at the moment and started to question some of my previous design thoughts. I have...

Phone numbers in a Help Desk application - Hello all, I have an access database using sql server as the backend. It has been very simple in design...

Quert regarding getting the results based on months. - use adventureworks go select YEAR(OrderDate) as orderyear, SUM(case MONTH(orderdate) when 1 then TotalDue end) as January, SUM(case MONTH(orderdate) when 2 then TotalDue end) as...

Using xp_readmail to read mail from outer environment - HI Is there any way to read the (Inbox) mails from exchange servers(outside the sql server)? my first requirement is...

Turning raw data into a grid layout - Hi all, not sure how best to explain what I'm trying to do so will try to break it down as...

TSQL and percentile (not percentilerank :) - Hi all, I would like to ask for some help and maybe the best practices on calculating the 90th percentile using...

SELECT * INTO Table without propagating IDENTITY attribute? - Using the simplest SELECT INTO syntax, the IDENTITY property of a column is part of the definition of the destination...

SQL Server 2008 : Working with Oracle

MSSQL Linked server to Netsuite cloud - Hi all, I'm having an issue to create a linked server connection to netsuite on the cloud. They supplied me with an...

SQL Server 2008 : SQL Server Newbies

orphaned Users - hi can you please any one tell me what is exactly orphaned users and when will use this one and...

Date Range or last 6 months of data - Is it possible to run a query that uses a date range, but if the records do not have any...

Count Records in Table and.... - Hi All I am just after a bit of clarification. I am writing a procedure which inserts data from one table...

Transaction Log file Deletion or movement? - Hello All, I have a Query Please help me out about this: Scenerio is, I have a database server which have a...

Dev Licensing questions!? - hi all, trying to get my head around licensing. specifically in a dev environment. Could i get some feedback on...

SQL Server 2012 Developer - I have a book that uses SQL Server 2008 and requires at least a developer edition to run SSIS. My...

SQL Server 2008 : SQL Server 2008 High Availability

How to Create Maintenance Plans on SQL Server Failover Cluster - We have configured a 2 node SQL Server Failover cluster on SQL Server 2008 R2 recently. I am curious to...

SQL Server 2008 : SQL Server 2008 Administration

Difference between Data Fragmentation & Index Fragmentation - Hi all, I am a newbie in SQL Server. I was exploring fragmentation topic. i came across two term which were...

SQL Server Startup Parameter - What is the dif between -m and -f in sql server startup parameter ?

SQL SERVER AGENT->JOBS ->JOB ACTIVITY MONITOR - Dear Expert, i can't find some jobs in SQL SERVER AGENT->JOBS ->JOB ACTIVITY MONITOR. but job exists in the system. and...

Slow Transaction Log Deletes? - I have a sever that has around 230 databases ranging from 1 gig up to 300+ gigs. Running SQL 2008...

Script to know who is logged o to the server - Hi All, Our servers are open to quite a few people. Unfortunately we cannot restrict the number of users on...

Are these SQLIO results ok? Need to ensure that new SAN IO latency is topnotch - Hi, Just run SQLIO on my new Cluster and got very interesting results that I would like to share and/or get...

Faster way to release the Unused Space back to Disk - Hi, I have few databases that have 8 data files allocated with 300 GB each and used only 110 - 120...

SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

PolyServe replacement options - While it seems HP still has not made the official announcement, some of us have had conversations with HP representatives...

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

Report Builder record limitations if any? - Hi, I am new to reporting services but familiar with the basics. I have a stored procedure that queries tables and...

Workaround - LookupFunction in 2008 - Hi geniuses! I have to edit some reports in SSRS 2008 and figure that I'm going to need to use data...

Database Design : Hardware

Storage Subsystems and RAMDisks - We're setting up a new database server for our OLTP and smaller website databases, and disk performance is a big...

Data Warehousing : Integration Services

Log type of the SSIS object that fire an eventhandler - Hello, i want to log some things with SSIS 208R2 Eventhandlers. I log: Begin, End,Username,PackageVersion,RowsOk,RowsError,... Now i think about using the OnInformation Eventhandler...

SSIS Environment Reference Id is null - Hello I have created an SSIS package in visual studio 2010 and deployed it at SQL server 2012 by using Integration...

Include Excel workbook password in OLEDB ACE 12.0 connection string - Is it possible to denote in an OLEDB ACE 12.0 connection string that an Excel workbook is password-protected and also...

about cdc ? - Hi any one can explain me how cdc in sql server work ? thanks pradeep

How to get the data in destination database immediately when source table updates? - We have 2 databases Source Database and Destination Database, We want to transfer data from source table records to destination...

How to get the data in destination database immediately when source table updates - We have 2 databases Source Database and Destination Database, We want to transfer data from source table records to destination...

Need to export excel sheet with SPECIAL formats - Hi, I need to export an Excel sheet with special formats (mandatory) where SQL is the source. I have achieved it...

Run 2012 SSIS package on SQL Server 2005 - Hello all, We work alot with mysql and will only be doing more, although sql server will remain prevalent. We...

Data Warehousing : Analysis Services

DimTime table help - I have a time table that got created through SSAS but it has only data upto 2012. I need to...

How to add Server Admin from different Location - Hi All, My PC is PEOECOM098. I wanted to add a fellow-developer (PEOECOM571\Owner) in the list of Server Administrators via the Analysis...

Notification Services : Administration

Database Mail Notification - Hello , Can you suggest me how to Receive a mail notification, not as soon as job completes but at a...

Products and Books : Discussions about Books

Audio Books - Does anyone know of any good Audio books for SQL Server Admins? I love being able to listen to material during...