In this issue

Featured Contents


Featured Script

SQL Source Control Connect your existing source control system to SQL Server
SSMS plug-in connects SVN, TFS, Git, Hg and all others to SQL Server. No source control system needed to evaluate. Learn more.
SQL Monitor Monitor your business, not just your servers
SQL Monitor gives you extra monitoring flexibility with custom metrics - monitor what's most important for your environment. Find out more here.
Deployment Manager NEW! Deployment Manager Early Access Release
Deploy SQL Server changes and .NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try the Early Access Release to get a 20% discount on Version 1. Download the Early Access Release.

In This Issue

Stairway to SQL PowerShell Level 4: Objects in SQL PowerShell

This far, we have learned about installation and setup of the PowerShell environment. You should now have a foundation of SQL Server PowerShell. We now are ready to learn about Objects in SQL PowerShell. More »

Archiving SQL Server Data Using Partitioning

Many companies now have a requirement to keep data for long periods of time. While this data does have to be available if requested, it usually does not need to be accessible by the application for any current transactions. Data that falls into this category are a good candidate for archival. More »

From the SQLServerCentral Blogs - Create Several Shell SSIS Packages In Minutes Using BIML

What do i mean by shell packages? You’re spot on – that’s an excellent question to begin with. For the purpose... More »

Editorial - A Software Warranty

Years ago I worked in a large company on the operations team. We were responsible for all production issues for the 6,000 people and the assorted machines, devices, and applications that come with a large workforce. There was a department that was aligned with our group that focused on engineering and various development groups that built different applications. The engineering group was good at working closely with the production team to ensure smooth deployments, but they weren't on call and would at times respond slowly to develop solutions when problems occurred. They were, however, better than the development groups who often sent code to be deployed, and accepted bug reports back, but provided little support or assistance for problems with their code.

I ran across this link from a DevOps person called You Write It, You Support It in the Brent Ozar, PLF newsletter. The piece makes a case for the problems that occur with some deployments, like a lack of, or surplus, of logging, switches to turn features on/off, error handling, and more. It's a pretty good description of typical problems I've often seen, and it calls for developers to support the features that they write, even in production systems.

I like this idea, though I don't think that it should be a continuous expectation with developers required to support their code forever. I would like to see developers giving their code a "warranty" of sorts, perhaps a couple months of priority support when code is deployed into live environments with developers taking responsibility and responding to calls, even after hours.

There are arguments to be made that developers' time is better spent enhancing applications and applying their creativity to new ideas, but this leads to a human frailty. Too often we view a job finished as a job completed, and that's not always the case. Doing a job well means more than completion. It implies a level of craftsmanship and pride in the finished product, both from the developer and the client. 

Developers should write code that works. If it doesn't, then it's not really finished and should be fixed. With that contract in place, we usually find that the developer spends a bit more time ensuring the product is built in a quality way that reduces the need for much support.

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

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:

, varfield varchar(100)


INSERT INTO IdentTest VALUES ('abc')

SELECT Ident FROM IdentTest

What is the value of Ident?

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.

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today

Yesterday's Question of the Day

Which of the following statements is true of table variables in SQL Server 2008, 2008 R2, and 2011? (select 6)


  • The primary key can be multi-column
  • An unclustered index can be on multiple columns
  • Multi-column check constraints are allowed
  • They can have an identity column
  • They permit multi-column unique constraints
  • They can have only one ROWGUID column

Explanation: Primary key and unique constraints can be included in the declaration of a table variable either as column constraints or as table constraints; declaring them as table constraints permits multi-column versions; since all such constraints involve indexes, there can be multiple independent indexes, and clearly these can't all be the clustered index, so declaring these constraints allows unclustered indexes to be created . Check constraints also are allowed at both table and column level, and an identity column and a ROWGUID can be declared. Of course all constraints have to be incuded in the declaration, they can't be added by separate DDL statements. See DECLARE @local_variable in BOL.

» Discuss this question and answer on the forums

SQL Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate

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

New feature in SQL Server 2012 - Sequence

A new feature in SQL Server 2012 is Sequence. A Sequence object provides functionality similar to Identity. 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 Parse?? - Hey everyone, Trying to find a bit of help with a project I am working on. Basically, we are trying to...

Loading Windows Application Logs to SQL Server 2012 - Hello, Could someone help me the best way to load Windows Application/System logs to SQL Server DB. Given we have application...

SQL Server 2005 : Business Intelligence

ForEach Loop to delete all files in folder. - Hi... I went to the following URL: [url][/url] for some help on how to configure a For each loop to delete all the...

SQL Server 2005 : CLR Integration and Programming.

How does a C# CLR trigger know the table it was called for? - I'm researching to create a C# CLR trigger that implements the auditing for any table in a database. One generic...

SQL Server 2005 : SQL Server 2005 Strategies

Redesign help - Hi, Ive finally been allowed to redesign some tables which are "wide" (Think Field1, Field2, Field3 ... Fieldn). There are multiple fields...

SQL Server 2005 : SQL Server 2005 Integration Services

How to compare current row with previous row - I'm not sure how this can be done in SSIS, but I would like to compare the current row to...

Progress To Sql Server - Hi, I am using SSIS to transfer a Database from progress to Sql server. I am using OpenEdge 10.1B ODBC driver to...

SQL Server 2005 : T-SQL (SS2K5)

NOT operator and Index Use - Hi all, Very basic question. I was reading the MCTS 70-433 training kit book from Microsoft, and fairly early on, they...

Find sub-tree nodes - Hi, I have a tree structure represented by a simple table that references itself: [code] CREATE TABLE [dbo].[AframeGroup] ( [GroupID] [int] IDENTITY (1, 1)...

SQL Server 2005 : SQL Server Newbies

Adding linked records - I need to add records to a DB in SQL Server 2005. Each composite record represents a project in the table...

SQL Server 2008 : SQL Server 2008 - General

How do you set relations in sql for tables - I have several tables and need to set relationships. How do you do this with the sql server manager? Thanks Lon

sql locking problem - Hi, I have a SP that calculates data based on criteria, the result were % and duration. It takes 8-10 seconds before...

rssbus Quickbooks Connection Error - Hello, I have create a SQL Server Agent job that reads data from the QuickBooks database using the file name and...

JOINing two tables with 2 SELECT Statements - I am joining 2 tables of 30 columns with 5 mil and 50 columns with 10 mil rows. Now, I...

SSRS linked server to MySQL parameter problems - In SSRS 2008, I have a linked server setup to SugarCRM using the 64 bit Mysql ODBC connector. Openquery is...

How to Graph SQL Server Data from a PowerShell Script - Has anyone found a way to graph SQL Server data from a PowerShell script? This link describes something close to what...

Querying EAV data - Friends, I am using a third partry tool whose database is modeled using Entity-Attribute-Value model. What according to you would be...

SQL Server 2008 : T-SQL (SS2K8)

many to many relations - I have one table and the columns are bookingreference,complaintreference,category1,category2,desriptionofthecomplaint, one bookingref have one complaintid, one complaintid have many bookingreferences, one complaintid have many...

Identity column and the seed - I have seen a strang thing about the identity column of seed, and cannot find why, please help. My situation is...

Convert Rows to Columns Pivot - [font="Courier New"] [code="plain"] I have data in a table ManagerGoals EventID EEID CustValue 107 12 JLK 108 12 Manager 109 12 50 ...

How do I use one column for node names and the others for elements in that node? - I'm trying to query some data with FOR XML to output the XML. Let's say I have data like this...

SQL Server 2008 : SQL Server Newbies

Adding a composite record to 2 tables - I need to add records to a DB on SQL Server 2005. Each composite record represents a project in the table...

SQL Server 2008 : Security (SS2K8)

Cannot Generate SSPI Context with AD account - I have several SQL 2K8R2 servers in "the cloud"; most work correctly. One is giving me problems. On that one (which...

SQL Server 2008 : SQL Server 2008 High Availability

Multiple sql servers to a single mirror - Hi, It seems the answer is no to this but I would like to confirm this. I have three SQL 2005...

SQL Server 2008 : SQL Server 2008 Administration

SQL Server Maintenance Plans Failing - I am getting the error below when I setup a job using the Maintenance Plan (SQL 2008 SP2). I have...

Database mail saying remote certificate invalid - Hello Everyone, I have a crushing issue that i have been looking all over the web, a week ago we moved...

SQL Largest tables - Hi All I'm using the built in "Disk Usage by Top Tables Report" in SQL Management Studio to check the tables...

Email after DB restore completion - Hi, I have a DB that gets restored from from different server everyday. Once the database restore is completed, I...

SQL Server 2008 - DB failed to attach - SQL Server 2008 - DB failed to attach DB files are of SQL Server 2008. I reinstalled, but still I see the...

SQL Server Agent service failed to start, windows 7 - Hello, Please help me to fix the issue. Log file pasted below. 2012-10-20 20:46:17 - ? [100] Microsoft SQLServerAgent version 10.0.1442.32 (x86 unicode retail...

Index question - I indexed a char datatype column but my query is still doing a table scan. So, indexes can be applied... : Anything that is NOT about SQL!

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

Data Warehousing : Integration Services

Problem to create a CONNECTION MANAGER inside a SSIS - Hi to all. I currently use SSIS of SQL SERVER 2008 R2 to create data flow to import data from several...

How to read Excel file name from stored procedure and file path in a variable in ssis - Hi, I have few excel sheets in a folder i need to load that sheets in a table the sheet...

SSIS vs SSMS ! - Here's a more theoretical question for you, to make change from all that hard code stuff. It all started when someone...

Execution Of EXE - Hi every one , any one help me on below requirement I had two servers server 1 -------SSIS Package is present server 2----- EXe...

XML from RESTful web service into SQL table(s) using SSIS - Hi, I'm a relative noob at this - so please bare (bear?) with me. I am retrieving XML data from a web...

Article Discussions by Author : Discuss content posted by Perry Whittle

TDE and SQL network force encryption - Hello, I am a bit confuse about TDE and SQL network force encryption implementation with respect to which certificate to use....