In this issue

Featured Contents


Featured Script

SQL Backup Pro Can you can restore your backups under pressure?
Use SQL Backup Pro's fully integrated DBCC CHECKDB to verify your backups, so you can restore them when it matters most. Download a free trial now.
SQL Toolbelt Want to work faster with SQL Server?
If you want to work faster try out the SQL Toolbelt. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download the SQL Toolbelt here.
SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
New! Read Lesson 3, ‘Six Scary SQL Surprises’, and learn from Brent Ozar and the other experts in the DBA Team. Read now.

In This Issue

How to recover a SQL Server login password.

I will describe a simple method anyone can use to obtain lost password information for a SQL Server login. More »

SQL Saturday #187 - Richmond

A free day of training in Richmond, VA. Come along and see Steve Jones, Grant Fritchey and more. More »

How to move a SQL Server Log Shipped Secondary Database to Different SQL Server

This tip I will explains a step-by-step method to perform the SQL Server Log Shipping migration. More »

How to move a SQL Server Log Shipped Secondary Database to Different SQL Server

This tip I will explains a step-by-step method to perform the SQL Server Log Shipping migration. More »

From the SQLServerCentral Blogs - A Month of PowerShell – Day 22 (Restores)

Welcome to Day 22 of my “A Month of PowerShell” series. This series will use the series landing page on... More »

Editorial - Does Connect Work?

I had a brief conversation on Twitter about the Microsoft Connect bug reporting system, and received the tweet pictured above. It notes: "I like how you used 'Connect works' in that sentence. Great work of fiction." That made me laugh because it does sometimes seem that Connect doesn't work all that well for those of us that use it.

Connect was supposed to be a place where users could report bugs and make suggestions. Some people file a lot of bugs and suggestions, though more than a few seem slightly silly. Others file what appear to be support requests, and I've had a number of documentation notes. There are even Connect items filed on Connect itself, though I can't understand why this particular one is "postponed". There are some items that have been open for years, even with hundreds of votes.

The Connect system is supposed to feed directly to product groups and there are more than a few times I've received feedback through the system from developers I know personally that provide comments. I know developers are seeing the items, but there appear to be a few times of year when the SQL Server group mass closes lots of Connect items, often without any feedback. The twitter conversation I had above included one of these items.

I remember being excited when Connect was introduced, thinking this would be a good way to get feedback to the product groups, and hopefully influence the managers to prioritize some of the common requests. However that excitement was tempered with the idea that so many items would be submitted, and it would be hard to triage and rank them. Indeed, I rarely find something in the searches I make before submitting, even I'll have submissions closed as duplicate.  The number of items submitted is so high, it's hard to even comprehend how any group works with the submissions. 

With the state of Connect, the lack of feedback, and the mass closures, I'm not sure how well Connect is working these days. However it's not all bad; I did submit one item that received enough votes to actually make a change in MS policy. Outside of that, I think @SirSQL's statement might be closer to the truth than I would hope.

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

I execute the following T-SQL




SET @I = 987.65

SET @S = 'abcdefghij' 



The question is: what values are returned by SELECT STATEMENT. SELECT TWO (2)

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

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

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.

Yesterday's Question of the Day

Will this code execute and return a value?

-- First Step
-- First Create a Table 
create table temp1
( name nvarchar(50)
-- Second Step
--First Stored Procedure
create proc sp1

select * from temp1
exec sp2

-- Third Step
-- Second Stored procedure
create proc sp2

select * from temp1
exec sp1

--Fourth step
exec sp1

Answer: No, this will create an infinite loop

Explanation: When user execute any one SP then it cal to second sp again second sp call to first sp, so that it will continue in an infinite loop. While you can compile both procedures, you cannot execute them successfully.

» Discuss this question and answer on the forums

Performance Tuning with SQL Server Dynamic Management Views

This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements. Grab your copy today from Amazon!

Featured Script

Copy Triggers from One DB to Another

A simple script that transfers triggers from one database to another. 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

about DTA - I am using Database engine tunning advisor to tune my workload file which has adventureworks database related script.I want to...

Failed to Configure Mirroring - Dear, I had configured High safety without automatic failover (synchronous) mode mirroring successfully in SQL Server 2008 R2 . It was running...

SQL Server 2005 : Backups

send data in small size by backup is possible - I have 2 branches database A,B same structure and objects different only in data i must take backup every day.but...

SQL Server 2005 : SQL Server 2005 Integration Services

Automating Excel via SSIS and SQL Agent - Hi I have encountered an issue on a project that I'm rather struggling with and I'm hoping someone has encountered something...

SQL Server 2008 : SQL Server 2008 - General

Just accepted a Sr. DBA job. Time for a gut check? - Hi SQLSC! Buckle up, I apologize in advance for the long post. In many ways, typing it out is as much...

Use parameter value in .csv output filename - Table_1 [code="sql"] ESTAB_ID ESTAB_CODE FORENAME SURNAME 1 9009001 John Jones 1 9009001 Mike Smith 1 9009001 Mary Yates 2 9009005 Ann Ennis 2 9009005 Kelly Homes 3 9009014 Harry Brand 3 9009014 James Casey 3 9009014 Chris Balls [/code] Table_1 contains a list of people belonging to different establishments. The ESTAB_ID is a unique sequential ide

Use parameter value in .csv output filename - Hit the send button in error. see my later post with the same title

Not seeing savings in sparse columns - Ive ran a test comparing a table with a few sparse columns to a table with no sparse columns, and...

partition_id VS. hobt_id - Hello! There is 2 columns in [url=]sys.partitions[/url] view - [b]partition_id[/b] and [b]hobt_id[/b]. From my point of view/experience for any given row from...

SSRS. Parameters mdx script doesn't work with multi-value - Hi ALL, I need your your help!! I want to create a report with multi-value parameters with SSRS. So, I wrote this mdx...

unexplained deadlock - Hi, I'm using SQL Server 2008. i have 2 different statements that lock each other, can you please explain why they are...

How to insert in batches WITHOUT using @@ROWCOUNT - I have the specific insert query below, and it works fine if I have less than 20 records or so,...

even and odd records - The sql code to find odd number of records is: [code]select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);[/code] But...

Query in SSRS - hi All ,i have one requirement where the SSRS report is displaying only certain data of a column based on...

how to get Mac Address in SSRS - Hi, I am having a requirement of taking the MAC Address using SSRS is it is possible to take? My Requirement: To...

fetch tables most often queried - Is there a way to determine which tables of a database are being queried most often, sorted in descending order?

User tables in the system DBs. Are they affected by SPs and CUs - Hello all, I have a client with several instances of SQL Server from 2000, 2005, & 2008, at various builds. They...

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

error in bulk copy - Hi I want to test some changes in the structure of a table and see the plan,I exported the data in...

SQL Server 2008 : T-SQL (SS2K8)

cursor - Hello, Following is my requirement. [quote][code="other"] Table rows: SLN Id StartDate EndDate Duration TimeElapsed 1 1 1/1/2012 10.12 1/1/2012 10.13 0day 00:01:00 0-days 00:01:00 1 1...

FOR XML and trying to avoid nested cursors - Alright, history first. I've got a bit of a mess I've inherited. Groups inheriting groups inheriting... you get the drift....

Deletes taking long time - I have a situation where I created I ran a query that generated about 32,000 individual deletes that I am...

expression in derived column issue - I want to cretaed new column in derived column with default string valuve like Column name Expression TableName FactFDDCellQHourlyPegEricsson1 I have tried...

Select full month number (with 0) - Hi, I use [code="sql"]DATEPART(month, myDate)[/code] or [code="sql"]MONTH(myDate)[/code] the resut is: 2 (if myDate is 2013.02.03). I would like return: 02 Do you have any solution for...

Guidance on table valued types. - We have 1 table valued parameter (tvp) coming to GetXXX stored procedures. It contains only 1 column of type uniqueidentifier....

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

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

Grouping sets - Hi All, how to use the groupingsets to a query having more than 32 columns plz help out

SQL Server 2008 : SQL Server Newbies

can this update statement be made easier - I am running an update statement as follows UPDATE dbo.BigTable SET BigTable.Software_Version_Raw = ( CASE WHEN dbo.BigTable.Software_Version_Raw LIKE '1.%' THEN '1.x' WHEN dbo.BigTable.Software_Version_Raw LIKE...

Group by and Where clause - Hi, Please see below, Table name : Log_table Columns: Logid int, databasename varchar(30), tablename varchar(30), logdate datetime Query 1: Select max(logdate) from log_table where...

SQL Server 2008 : SQL Server 2008 High Availability

Replication synchronizing errors - What all reason could be there for errors while synchronizing merge replication or transactional replication.

dbmirrroring - can two mirroring sessions on single production server between diferrent sql instances use same endpoint and same port numbers eg: DBprincipal1....DBMirroring1 endpoint...

Confusion regarding which full-backup file to apply on secondary in Log shipping - Hi we have a log shipping configured at one of client side and is out of shrink for log time...

SQL Server 2008 : SQL Server 2008 Administration

SQL Server doesn't accept remote connections. - Hello, I am trying to use my SQL Server in my Perl application. I am hosting my Perl code on an...

Attach Database Error after moving MDF - I needed to move the MDF to another location so I detached the database, and dragged the MDF to a...

Problem with Database mail - We have SQL SERVER 2008 R2,which Database mail does not work properly.When I try to send test mail,it says it...

Database in Recovery mode while restoring it - HI All, I need your to understand and resolve this issue. I have initiated the backup script for a database which...

CPU considerations for multiple instances - I've been researching this and haven't found much info so I'm not sure what most people do. Consider a server...

Can't Kill SPID “Transaction Rollback in Progress” - I'm running into frequent Blocking on a Development Server. I have been correctly the code or making recommendations to prevent this...

Programming : Powershell

Risks/Best practices on execution policy - While it would be easy to set the execution policy to unrestricted, or possibly set it to unrestricted in an... : 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

SSRS. Parametres mdx script doesn't work with multi-value - Hi ALL, I need your your help!! I want to create a report with multi-value parameters with SSRS. So, I wrote this mdx...