In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor SQL Monitor v3 is even more powerful
Use custom metrics to monitor and alert on data that's most important for your environment, easily imported from our custom metrics site. Find out more.
 
SQL Compare 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 Backup Pro Gold Community Choice Award for SQL Backup Pro
Try award-winning SQL Backup Pro for faster, smaller, fully verified SQL Server backups. Download a free trial now.

In This Issue

Shred XML column using SSIS

This article describes a way using SSIS to shred a XML column from a source table into its respective columns in the destination table. More »


SQL Bits XI

The popular UK conference heads to Nottingham from May 2-4, 2013. Both Grant Fritchey and Steve Jones will be speaking, along with lots of talented SQL Server professionals. Register today. More »


Standardize SQL Server Installations with Configuration Files

If you have a requirement to install multiple SQL Server instances with the same settings, you most likely want to do it without following the numerous manual installation steps. The below tip will guide you through how to install a SQL Server instance with less effort. More »


From the SQLServerCentral Blogs - A Learning Experiment at SQL Saturday #187– Richmond

In a little over a week, SQL Saturday #187 takes place in Richmond, VA. I’m looking forward to going back... More »


From the SQLServerCentral Blogs - Windows 2012 for the DBA Setting Your Server to High Performance

Hello Dear Reader!  I’ve been doing a lot of work with Windows Server 2012 lately.  The interface is wildly different... More »


Editorial - The Microsoft Sideshow

Last year I read a piece in Vanity Fair that talked about Microsoft and how the company has appeared to stagnate somewhat over the last decade. As a stockholder, I've been a little disappointed in Microsoft's performance, especially as I've seen many other technology companies grow rapidly in that time. The performance has been more suited to some of the stodgier industrial companies than a high tech offering.

There was a time when Microsoft courted developers, and created an excitement that resulted in many individuals and companies writing new applications for the Windows platform and caused an explosion of growth, not just for Microsoft, but for everyone in the ecosystem. The last six or seven years it seems Apple has taken over that crown with the excitement generated by iOS. Even as Microsoft made a fantastic update to its core OS with the lean and fast Windows 7, and even as SQL Server has grown to become a platform that can power almost any application in the world. 

Overall, however, I think Microsoft has become more of a business than an innovator. There is an incredible focus on business and sales first, and awesome technology second. I don't think leadership is inspiring or even leading the company well. I feel that there is a lack of focus across the business, with such a disparate set of products and efforts, that the overall view of one of many independent groups vying for resources and lacking a coherent vision of where the company is headed. That results in a lot of products that don't seem to integrate well, at least without lots of patches.

Microsoft isn't doing badly, however, as they just posted record revenues. Even better for us data professionals is the fact that SQL Server revenue was up 16%, which shows the product is popular, solid, and selling well. I expect we'll continue to see lots of advancement in the features we can use in future versions. Now if they'd just get rid of the sideshow of "R2" naming....

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

--Using SQL Server 2008R2 or 2012 
--I execute the following two (2) CREATE TABLE statements.

CREATE TABLE dbo.Output(ID INT,Col1 CHAR(15),Col2 INT, Col3 INT)
CREATE TABLE dbo.Input(ID INT IDENTITY(1,1),Col1 CHAR(15),Col2 INT, Col3 INT)

-- I then execute the following T-SQL statement
INSERT INTO Dbo.Input
VALUES('Surprise',20, 30),('Whoops',10,15),('Again',100,200)

--I then execute the following T-SQL 
--Delete statement
DELETE dbo.Input
OUTPUT DELETED.ID,DELETED.Col1,DELETED.Col2,DELETED.Col3 
INTO Dbo.Output
WHERE Col1 = 'Again' OR Col1 = 'whoops'

--I then execute the following T-SQL statement
SELECT * FROM dbo.Output

The Questions are: can OUTPUT be used with a deletion? If OUTPUT can be used with a delete, how many rows were deleted  by the Delete statement?

Select the three (3) correct answers.

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

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

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.


Yesterday's Question of the Day

What will the outcome be after running all the following statements as 1 batch?

-- Statement 1
Select
       1 As [RowId]
     , 'True or False: The HAVING clause is the 4th phase of logical query processing?' As [Question]
     , 'True: 1-FROM, 2-WHERE, 3-GROUP BY, 4-HAVING, 5-SELECT, 6-ORDER BY.' As [Answer]
     , '15 Jan 2013' As [PublishDate]
  Into
       #TempQoD;

-- Statement 2
Select * From #TempQoD;

-- Statement 3
Drop Table #TempQoD;
GO
Note: This was only tested with SQL Server 2008 SP3

Answer: All statements will succeed and 1 row will be returned.

Explanation: The Select Into statement can be used to create tables on the fly.

From MSDN: "SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it."

Ref: http://msdn.microsoft.com/en-us/library/ms188029(v=sql.110).aspx

» Discuss this question and answer on the forums

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



Featured Script

ERRORFILE and MAXERRORS option with BULK INSERT

ERRORFILE and MAXERRORS option are rarely used important arguments in BULK INSERT so, I will try to highlight the use of these arguments in this script. 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

LDF is IDF instead - The production database has 1 MDF and 1 log file. The log file extension is idf instead of LDF. Would this...

How to delete any backup files based on CURRENT day in a daily schedule. - How to delete any backup files based on CURRENT day in a daily schedule. I create a sql script as follows....

Avoid DEADLOCK for concurrent DELETE - Hi ALL, I have a table called Products which has many columns. It is a temporary table used for reporting purpose. The...

Problem with connecting 2 SQl 2005 Servers - Hi I am running SQL 2005 Standard edition under Windows Small Business Server 2003. Everything, that I need runs just fine from...

Replication and mirroring can be working for New VM server - With this new concept, is there any new approach regarding SQL database replication/mirroring in the virtual server concept? As today, we...

Named Pipes Provider: Could not open a connection to SQL Server [53] - I am running SQL Server 2005 - 9.00.3042.00 On a Windows 2003 server service pack 2. I have a VB application...

What Windows PerfMon Counters Should be Monitored - We are using BMC Patrol. What Windows PerfMon Counters should be Monitor SQL2005 by it to ensure SQL is running...

SQL Server 2005 : Backups

Backup Failed but backup file is created - Hey guys, I have an issue with backing up a 80GB database over the network, Here is the error:A nonrecoverable I/O...

Restore failed for Server. (Microsoft.SqlServer.Smo) - I have been trying to restore a backup to my test server and keep getting the following error. I am...

Table Level Rescovery/Restoration - HI All, Can we restore/recover individual Table in Microsoft SQL Server. As far as i know it is not possible Out...

SQL Server 2005 : Development

Partitioned Tables - i have one table it contains 30 million records, Now I have partitioned the Table, I could not find much...

How to get the inner XML from an xml element using XQuery - DECLARE @XML XML SET @XML = '<root><Books><Book><ID>2</ID><Name>Sql Server Admin</Name> <Author><Name>Joseph</Name><Age>35</Age></Author></Book></Books></root>' I have a XML like this; I would like to get the Result as...

SQL Server 2005 : SS2K5 Replication

Help with Replication between two sql 2005 servers - Hi guys I need a little help, I am new to SQL I have setup replication between two sql 2005 servers internally...

SQL Server 2005 : SQL Server 2005 Integration Services

Tip needed - Change Connection on several containers at the same time - I have a DTSX package that I need to change the connections on for about 30 containers. Is there a...

SQL Task Fails - confirmed Connection and SQL is fine. - I have a DTSX package that is a copy of another one. There are a lot of import steps so...

SQL Server 2005 : T-SQL (SS2K5)

Calculate an expression - Hello, I need help to calculate an expression in sql. For example I have to send (248/5)*30 to a store procedure...

In a Jam - Need Some Help - Hello, I'm urgently working away at this but not able to solve my problem. I'm at the point of needing...

SQL Server 2008 : SQL Server 2008 - General

Database to Database Connection - Unsecure? - Hello, I work for a government agency and I'm trying to implement some automated data pull procedures and I was...

MySQL Forums ? - I've just inherited some MySQL databases with some problems. Does anyone have any favorite forums ?

Moving data from one server to another - I have read permissions on a production server and admin rights on a dev server. I am working on a...

Data Verification after a Migration - Hi all, We are about to migrate one of our core data repositories (a Data mart of Person & Organization contact details)...

Size of temp db - Hi Is there any effect on performance if I empty the temp db by restarting SQL server,and increase the size...

delete database - iam unable to delete the database as we are getting error 1222 and delete backup history how to proceed ?

Testing scenario, physical reads. - Hello in a testing scenario, I want to have access to the physical reads for every individual query. With statistics io...

Trace flag 2371 - Hi All I want to enable trace flag 2371 for update statistics. The application generates a lot of queries. As enabling the...

What is best practice- load data then transform it, or load as needed? - I have a loader application that loads rows into a database with one column in the form: aaa/bbb The end user...

Fragmentation - Performance Slow.. how do i check the fragmentations, i mean which DB/tables to be reindexed :w00t:

SQL server 2008 query help - Hi guys im trying to show all the duplicate data from a table how would one do this. this is...

update columns from another Database - Hi Team, Am having two databases in one instance, Names are SCC SCCL both are in sync, regularly we are using SCC for regular...

Is SQL Server 2 tier architecture or 3 tier architecture - Hi Team, Can anybody please let me know Is SQL Server 2 tier architecture or 3 tier architecture ?

Data Modeling Exercise - To Help Both Performance and Concurrency - Hello All, I am looking for your thoughts/corrections/modifications on the following: I am currently working on re-designing an existing data model. Currently,...

conditional sort in sql - i have this table CREATE TABLE [dbo].[HRCandidateProject]( [ProjectId] [bigint] IDENTITY(1,1) NOT NULL, [orgid] [int] NULL, [uid] [int] NULL, [CandidateId] [bigint] NULL, [FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS...

Datatype varchar(MAX) - I am now implementing an update of a large table by looping all the databases in my server instance. Its...

SQL Query help - Hello I have attached images with the sample data. Basically I want to break notes column based on the ItemName coulmn....

get the first and last day of any Year/Month - Hi, i have a view with two columns, lets say SpecYear and SpecMonth, both are integer. How can I build two...

Need help with dynamic sql query - Hi, I need help with this dynamic query to execute the sql statements but it gives the following error when...

Cannot open database “” requested by the login. The login failed. Login failed for user 'sa' - Hello I am trying to use [b]SQL Authentication[/b] to connect to a database . I am having extreme difficulties and my...

Find first appointment after discharge from hospital - I need to find the first doctor's appointment after a patient is discharged from the hospital then, include the number...

how to put my sql request within an application to disaply result? - Hi i have some request i would like to put them in one application so it's can display all the...

Does the query optimizer make use of transitive equality? - Does the query optimizer make use of transitive equality? By that I mean if A = B and B = C, then...

Sql server 2008 R2 , Database growth setting - Hi Friends, I am observing a around 100 GB growth in the database size due to database growth setting of around...

Installing sql server 2008 in command line - I am using command line to install sql server 2008 automatically in silent mode.But if i select mixed authentication then...

Need help with a select, insert - GOAL: insert into tblcompanyassignments from tbluserassignments. Only attempt the insert for userid that exist in both tbluserassignments and tblexistingusers. For...

Virtualization reviews - i have to Assess server landscape –Server reviews –Virtualization reviews –OS/patch level reviews can any one help me out regarding Virtualization reviews. what does it...

What, if any, is the best type of replication for implementing on hundreds of databases on one server? - Hi all, We have a 2 node (active/passive) SQL 2008 SP1 Enterprise Cluster that hosts about 500 databases. Now while the Cluster...

Multiple SQL servers Consolidation to one Single SQL 2012 Cluster - Hi, Experts. I have a project which includes different 180 databases on around 18 different SQL servers with different versions (2000,2005,2008,2008...

Declared Variable in WHERE clause weird behavior - I'm seeing a huge performance difference between two queries that are almost identical. The only SQL difference is in the...

Need help comparing two execution plans - Hi all, We're testing an upgrade from SQL 2005 to SQL 2008 R2 in our data warehouse environment. I've run into...

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005 - This lovely error happens frequently when I need to add articles to my transactional replication setup. After making the changes...

SQL Server 2008 : T-SQL (SS2K8)

Query to list all jobs - Hello, We are consolidating servers and I was asked to write a query that returns a list of jobs on a...

Right align Alpha field in msdb.dbo.sp_send_dbmail - I'd love to have some pointers on this problem. I see postings on the 'net claiming to correct the problem...

dynamic sql- insert (error) - I am looping through all the databases for a table and trying to insert data into two columns in that...

Log File full / Database Down - I am a newbie and we ran a script that made our logfile drive go to 100%. We tried to...

Updating specific rows - I am having a problem, but think I may be making it harder on myself. I need to update a...

Interesting Question in SQL - Hi Everyone.... I have a table (no Primary Key defined) with some value and I need to create a view on...

How to shrink TempDb - I have read a few posts on how to shrink TempDb but I am not even sure if "Shrink" is...

Hierarchy example - Ken Henderson's book - not working - I’m working on the hierarchy lesson in Ken Henderson’s The Guru’s Guide to Transact-SQL. He wants to show two things....

Show all data from four tables joined or matched on a particular column - Hello all. I am trying to select computer names from a number of tables and display them ordered. I have been...

Require serial access to table, Stored Procedure, TABLOCKX - Hi all. Database newbie. I have a one off requirement to rename all our computers on the domain. (hundreds) Part of the...

UDF Help - Hi Guys, I want to create UDF. Below is my logic. Please guide me where i am wrong. Alter Function [dbo].[udf_GPList](@EID int,@PID...

SQL Server 2008 : SQL Server Newbies

Case Statement - Good Day To All I'm a Newbie so please be patient. I don't know if this is possible or am I...

SQL Locks - Hello Masters, In my new environment of SQL Server 2008, I got an alert from one server "Total_Locks>=4000 ", I checked and...

Query Using Multiple table - I have two table ACINF and ACPMNTA ACINF COMCODE ACTCODE ACTDESC 3305 130000000000 CURRENT ASSETS 3305 130100000000 Loans 3305 130100010000 3305 130100020000 3305 130100090000 Loan to Others 3305 130100090001 Loan to Others 3305 130200000000 Loan to Employee (RHEL) 3305 130200010000 Loan to Emplyee...

Exclude duplicates, keep the value with the latest date - I'm trying to create a query with multiple columns. The CandidateID column includes values that appear more than once. I...

SSIS error: version number in the package is not valid - I installed SSIS 2008 (SSIS, SSRS, SSAS....not the database engine) on a server that already had SQL Server 2005 installed...

SQL Server 2008 : SQL Server 2008 High Availability

Sqlserver 2008 R2 Mirroring failed to failover when the primary server got stacked. - Question: Sqlserver 2008 R2 Mirroring failed to failover when the primary server got stacked. Environment: 3 virtual servers: Primary, secondary and witness No...

conflict resolution in merge replication in sql 2008 - I was working with merge replication and found conflict occurring for the same row update and used conflict resolver component...

Wht components to watch in Replication monitor - I am working with Merge Replication when I use RM not understanding the usuage of it.Can anyone in simple manner...

how to configure DB Mirroring Endpoints - I have one System (box)on which I have two databases which are to be mirrored on another instance on the...

Mirroring Strange Behaviour Question - Hi all Last night one of our mirrored databases failed over and then failed back automatically. This seems strange behaviour to...

SQL Cluster - Multi Instance - I have a SQL 2008 R2 Cluster name of say SQLCluster1 on NodeA with an instance name of Instance1 ie....

SQL Server 2008 : SQL Server 2008 Administration

sqlserver service is not being started - sqlserver service is not being started. 1.checked startup parametres and all the files(master data and log files,errorlog file) existing in the...

data loss for all published tables by adding article to replication? - Hi there, I'm not sure if this is the right forum but I try: yesterday I added an article to our transactional...

Is database backup in multiple strips faster than in single strip? - Hi i heard that Database backup in multiple strips is faster than database backup in single file. Then I tested...

database size plannig - Hi All, Would like to know what is the correct procedure in planning the database size over the period. Do i...

Application does not work with named instances?! - So I just have to ask this because I am a bit baffled and flabbergasted by this situation. In 9...

SQL server SP1 update - We installed SQL server 2012 SP1, and there is a non security update. http://support.microsoft.com/kb/2793634 After install this update , where can I check...

SQL installatin SLIPStrem - I am the first time trying to use the FullslipStream download file to install SQL server 2012. the download is...

UCP and MDW Data Collectors / SQL Jobs - I have set up MDW and UCP - can someone clarify if the 'collection_set_5_noncached_collect_and_upload' SQL job is acually related to a...

export to flat file is failed - Dear Experts I am trying to export data from table to a flat file but this error recured on several fields Error...

find the status of SQL Services on multiple servers - I have a scenario where our DBAs and Server team planned for Maintenance, tonight. We have more than 800 servers that...

Career : Resumes and Job Hunters

Role and Responsibility of SQL Developer - Dear Friends, Can any one pls tell about Role and Responsibility of SQL Developer. And also know about BI deelopment. I am having...

Programming : Connecting

Run SQL Server Job from Command Line - Good afternoon, I need to daisy chain a SQL Server job with an AS400 job (have the AS400 job kick off...

Programming : General

column selection on dynamic basis - Hi, How can i select columns of a table dynamically through a select statement if the column name need to be...

Programming : Powershell

Getting printers on a remote PC in a workgroup - I'm trying to learn PowerShell. So I did a [b]Get-Help Get-Printer -examples[/b] and saw that I can issue the command...

SQLServerCentral.com : 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...

Reporting Services : Reporting Services 2005 Administration

double hop issue for one db server and not for other - i have report server/iis installed in one machine and different db servers (locally on another machines and in branches ). to...

Data Warehousing : Integration Services

Deleting the contents of a text file before import - Hi All, Is there way to in SSIS to delete the contents of a text file before importing data into...

Use a variable to dynamically specify the table name - Hi folks, This is my next challenge: I'm using an Execute SQL Task to run an update. The source and target...

Data Warehousing : Strategies and Ideas

Experiences using Wherescape Red on SQL Server? - I am struggling to find much content out online so I was wondering if anyone wanted to share their experiences...

Data flow mapping - ETL - Hi, I was wondering what do you guys use to map the data flow in ETL. I am looking at the...

Data Warehousing : Analysis Services

How to handle historical data change in SSAS - I am a novice to SSAS and I was wondering if there is a way to process historical data change...