In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle logo ‘Beating Backup Corruption’
The DBA Team are back, revealing more of their ‘Top 5 Hard-earned Lessons’. Lesson two is out now! Learn to beat backup corruption.
 
SQL Data Compare logo Save time when comparing and synchronizing database contents
"It has also streamlined our daily update process and cut back literally a good solid hour per day." George Pantela, GPAnalysis.com. Download a free trial of SQL Data Compare now.
 
SQL Monitor custom metrics 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.

In This Issue

Stairway to Server-side Tracing - Level 1 - Overview of Tracing

The first part of our stairway series on SQL Trace examines the overview of this subsystem in SQL Server, it's architecture and the kind of information it returns. More »


SQL Saturday #177 - Silicon Valley

Don’t miss the Bay Area’s largest SQL Server event of the year! You can choose from sessions on SQL Server 2012, DBA, Development, Business Intelligence, and Big Data.  More »


From the SQLServerCentral Blogs - Overconfidence – How it Almost Cost Me and What You Can Do to Avoid It

It was early one morning and I was working on a production deployment to add a new Subscriber to an... More »


From the SQLServerCentral Blogs - I’ve got mad SQLSkills

Digging the new logo Last week I was fortunate enough to attend SQLSkills IE1 class in Tampa. Fortunate to have an... More »


Editorial - Impressive Accomplishments

Most of us have had an interview or two in our lives. We've had to assemble a resume or CV, create cover letters, and practice answering questions in preparation for an interview. In all of these things, we are looking for ways to market our past work and present it in the best light as evidence that we will make a good employee in our next position.

We each have our own experiences, successes, and failures, but we aren't bound by what's happened in our career so far. We have the ability to shape our brand for the future by making changes in the way we approach our career from this day forward. I have a talk and blog on this subject, but I certainly don't have all the answers. I'm always looking for more ideas and suggestions to help people and wanted to ask for your ideas this week.

What would impress you most about a job candidate?

Is there something that you've seen or heard about in the past that impressed you? If a candidate had written a book, or created some software, would that make you look upon them favorably? I know that a candidate needs to present themselves well in an interview and be able to write SQL, or manage an instance, but when you are examining dozens of resumes, is there something that stands out?

Take a moment and tell us what has impressed you in the past, and whether it was an indicator of a good employee or not.

» 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. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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


Question of the Day

Today's Question:

If I am executing the below code, what is the name of the database returned from the three queries (in the same order of execution).

USE master;

-- Query#1
SELECT DB_NAME() DatabaseName1;

EXECUTE ('USE AdventureWorks2008');

-- Query#2
SELECT DB_NAME() DatabaseName2;

EXECUTE ('USE AdventureWorks2008; USE master');

-- Query#3
SELECT DB_NAME() DatabaseName3;

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 Wait Stats Joes 2 Pros

You learn a lot when thrown into a crisis situation, where everyone is looking to you for answers. The system is down, the CEO is pleading to you for answers as the each minute cuts the company’s bottom line. When you are in a hurry, you need some new ideas right away. This book is your helpful tool. When used correctly, you can determine solutions and even related solutions to simple (yet lesser known) areas of SQL Server. Learn what causes SQL tasks to wait or site in queues. Even if your system is not in trouble right now, you can use what you will learn in this book to view the history of wait statistics.

Pick up your copy of this great book from Amazon today.


Yesterday's Question of the Day

Given the following code:

use tempdb;
go
create table A (
id int,
val int
);
go
insert into A values
(1,1),
(1,2),
(2,3),
(3,4);
go

create table B (
id int primary key,
val int
);

with CTE as (
 select
  id,
  val,
  row_number() over (partition by id order by val desc) as RowNo
 from A
)

merge into B dest
using CTE src on
src.id = dest.id and
src.RowNo = 1
when not matched by target then
insert (id, val)
values (id, val);

select count(*) as [Count] from B;

drop table A;
drop table B;

What is the result of the select statement?

Answer: 0

Explanation: Statements in the on clause of the merge statement are only evaluated if they include a column in the destination table, thus the RowNo is not evaluated, causing attempt to insert duplicate value (1) into the primary key column, so no rows are inserted.

Ref: http://technet.microsoft.com/en-us/library/bb510625.aspx

» Discuss this question and answer on the forums

SQL Server 2012 Data Integration Recipes

SQL Server 2012 Data Integration Recipes provides focused and practical solutions to real world problems of data integration. Need to import data into SQL Server from an outside source? Need to export data and send it to another system? SQL Server 2012 Data Integration Recipes has your back. You'll find solutions for importing from Microsoft Office data stores such as Excel and Access, from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and even from other SQL Server databases. You'll learn techniques for managing metadata, transforming data to meet the needs of the target system, handling exceptions and errors, and much more.

Get your copy from Amazon today.


Featured Script

Get Detach or Attach all user databases script

Script to generate script to detach or attach user databases  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

Cannot perform a differential backup for database "ReportServer" - Good day, Please assist Im getting the error below: "Cannot perform a differential backup for database "ReportServer" because a current database backup...

Different between Application DBA and Production Support DBA - anyone knows the different between Application DBA and Production support DBA? In my understanding, production support is the person whom...

SQL 2005 ALERTS Not Working for non-logged events - SQL 2005 Enterprise X64 SP4 ALERTS: SQL SERVER EVENT ALERT ---->Severity Has anyone noticed that the Alerts only seem to trigger when...

Run SSIS package. Works in BIDS, not on server. - Hello. I have an issue that has consumed a lot of my time to date, so I've turned to the SSC...

Migrate with minimal downtime - Hi All, Assuming my database server is 24*7 database, what would be the best strategy to migrate my database server to...

Getting an error during creation of Link Server on SQL SERVER 2005 through RUMBA Driver - Hi, I am getting an error during creation of Link Server on SQL SERVER 2005. I have already created DSN (AS400JDE ) [b]like Provider=MSDASQL.1;Password=*****;Persist...

I want to have SQL Server Mgmt Studio to have the ability to run as a different Windows user. Some has it while others has run as Administrator. - How do I do that? What causes the differences?

SQL Job getting stuck at one step - Hi There, I have a SQL job with 16 steps but it is getting stuck at step 9. All that step...

Database in Recovery mode from last 7 days. - Hello, Please any one can help me, how to recover databases or change status from in recovery to normal. i had...

Login failed for user 'NT AUTHORITY\SYSTEM', Very straing - I get hundreds of these messages in my SQL Server logs every day (Exactly every 15 minutes). The messages have a...

SQL Server 2005 : Backups

Database recovery after truncate command - Hello Friends, I got a query from one of my team member’s friend. Here is the scenario : SQL server Version : SQL...

SQL Server 2005 : Business Intelligence

expression output - Hi, can any body tell the output for expression by taking any 2 dates as example. =cdec(DateDiff(DateInterval.Day,Fields!Date.Value,Fields!Date2.Value))*(-1) Thanks, Niha

SQL Server 2005 : SQL Server 2005 General Discussion

Bulk Insert to Linked Server Error - When trying to use BULK INSERT to a linked server I discovered there is apparently a limitation that you can't...

A severe error occurred on the current command.The results, if any, should be discarded. - Hi all, i have some problem with my SQL Server, if i am creating any new Db, and i am...

How to force drop database? - Hi people, i'm new in SQL 2005 programing, and at this moment i'm making one website in ASP.NET/VB.NET... In that Website...

SQL Server 2005 : T-SQL (SS2K5)

Need urgent help in a tricky t-SQL - Hi, Trying this for a long time but no results :-( need some help from experts here. I have the following dataset....

Odd performance caused by Where Clause - So I have this odd issue that I can't figure out how to solve. I have a poorly written view...

Convert Varchar to Decimal - Hi, Select LHP.LHP_PARAM_RESULT_VALUE, Cast(Replace(LHP.LHP_PARAM_RESULT_VALUE,',','') as decimal(18,2)) from LS_HMT_PARAM_RESULTS LHP Where ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND LHP_PARAM_RESULT_VALUE <> '' This Query Run some results and with [b]Arithmetic overflow error...

SQL Server 7,2000 : Administration

Reindexing Tables used in Log Shipping - I have several databases just set up for log shipping to another server. Previously, I would set the DB to...

SQL Server 2008 : SQL Server 2008 - General

job script automate - Hi, We need to take all jobs scripts in sql server 2008. If possible reply script. Regards Sivakumar.T

Installing new instance on already installed sql server PC - In my PC i have already installed sql server 2008 express with a default instance name.(SQLEXPRESS). Now i want to...

Named Instance and specified port not connecting - Hi all I have opened up a port on a remote SQL instance and can see that the port is LISTENING...

Cursor Names - List - Hi Team, one challenging requirement for me, In my Database, i have 100+ tables, triggers, stored proc's, functions, in that am using...

sql server x64 access x86 COM object - I want to transfer a database from SQL Server 2000 x86 to SQL Server 2008 R2 x64 but it uses...

failed to list sql server services in sql server configuration manager - failed to list sql server services in sql server configuration manager due to Remote procedure call failed.???? error : 80706be ???

How to do formaula calculation inside a string in SQL Server - HI, I need to evaluate a formula in sql server (eg) ((a+b)*c)/d, [b]where this formula will be stored inside a string value[/b]...

enable login failed - enable login failed .for login 'sa' sql server error 15151

removing duplicate columns from data conversion output - Hi all, Is it possible to remove columns from output of the data conversion transformation.because whenever we doing data conversion...

select in recursive table - Hi, in my database i have these two table [quote] CREATE TABLE [dbo].[CATEGORIES]( [ID_CATEGORIE] [int] IDENTITY(1,1) NOT NULL primary key, [CAT_ID_PARENT] [int] NULL, [CAT_DESIGNATION] [varchar](150) NOT...

SSIS -> Excel Formatting Issue - Hi everyone What is the best way to keep the formatting in Excel? I have created a template with the font,...

SQL Developer to SQL DBA - Any tips on making this career change? Has anyone done this? Many thanks,

JOBS ON SQL DBA? - Hi, guys, I would like to work on dba in usa? any one please help on this?

Please let me know the salary Pakege? - Hi, Please let me know any one? Waht is the salary packeges in india, for 3 years of exp?

Top 10 products by Month - Hello Can anyone help me with this below. Jan 2010 2011 2012 course name course name course name course name course name course name course...

create database failed - I try to create database in sql server(logon as windows authentication). GOT ERROR : "User does not have permission to create database". Then...

Upgrade to 2008 - Some Advice - Hi, I was just wondering what the best option is. I have a 2005 SQL server with 4 instances and about...

Why doesn't my transaction log grow with delete's? Recovery mode is set to FULL - Hi, I have a sproc that does millions of deletes every hour. It grew everytime i manually set the date...

Trigger issue - I have trigger on a table and it failed when i insert on a table with below errro message and...

Splitting a Column in a table keeping reference column - I have a table that has ID Name 123 12345-abcd, 6789-efgh 456 R11223344-abc, 223366-rreett, A45566-ppooit I need to take the "name" column and...

Please help my carrer Path? - Hi Guys, Any one Help my career Path? Corently am Working with capgemini india Pvt Ltd. on Mainframe technology, I have 4...

SFTP with sql server 2008 - I want to know the syntax of SFTP command that will execute by sql server job and what steps will...

XQUERY In sql server - Hi, I'm very new to XQuery's, here i need a situation to Join two different xml's and apply some filter conditions. From...

how to convert a MySQL date (linked server) to MS SQL? - Hi, I'm stuck on converting a MySQL date field (on a linked server) to MS SQL datetime format. I've googled...

Puzzle / CTE Help - NO CURORS :) - I have a use-case and I know a CTE is probably where I'm gonna go with this but I just...

Split VLDB into Multiple data files - We have an 600gb database as a single mdf and ldf. Due to various reasons, we are planning to split...

Naming Convention for UDF's, Views and SP's - I recently has to take a written SQL test for a client and one of the questions got me stumped...

Merge statement with openrowset - HI All Is it possible to run the MERGE statement using Openrowset or openquery? I tried with below statement: SELECT * FROM OPENROWSET('SQLNCLI', ...

Intermittent Stored Proc long running issue. - Hello I have an interesting (at least to me) case of a stored procedure that is intermittently running long. These are...

sp_send_dbmail fails in SQL Server Agent Job - Hi, I have read I think every post on the web about this problem but still no luck with a solution....

Mirror Database - Cannot create snapshot - Hi, I have a mirrored database configuration, with two SQL Server 2008 Enterprise boxes, and SQL Server 2008 Express as a...

SQL Server 2008 : T-SQL (SS2K8)

Email Alert for VLF_count > 50? - Hi, I have modified this script for getting email alert just VLF count information, also working fine and received email.. I want...

Weird Behavior of Trigger - I've got a trigger (SQL 2008 R2) that does a few simple operations but the results are not logical. A...

Why does this get me multiple records? - Trying to return just a single record for the max audit date for an agent. I thought this would do...

Does a CASE statement execute all cases? - Hi, I've been having this discussion. See this example: [code="sql"] DECLARE @i INTEGER = 1 SELECT CASE @i WHEN 0 THEN (SELECT COUNT(1) FROM table1) ELSE (SELECT COUNT(1)...

Calculating a Moving Average - Hi, I have a need to calculate a moving average. Ultimately, I need to convert the Select statement into an Update...

SQL Server 2008 : SQL Server Newbies

Worktables & Hash Tables - Hi All After testing and researching - I want to make sure that my understanding is correct regarding Worktables & Hash Tables >Worktables & Hash...

SQL Server 2008 : Security (SS2K8)

Orphaned users versus loginless users - Hi forum, a short question: What is the difference between orphaned users and loginless users and how can I seperate them...

Does this error reveal too much? - I'm new to SQL, so I apologize for any incorrect terminology, etc. If someone were trying to access this database, does...

SQL Server 2008 : SQL Server 2008 High Availability

Log Shipping Failover Test - Hi I'm currently running some DR testing and have failed over a couple of databases to our DR site. Generally the...

Replicate a new table without taking a snapshot - I have a 350 GB table that is replicated, and I need to rename. This will require replication to be...

SQL Server 2008 : SQL Server 2008 Administration

SQL profiler and performance counter - Dear Experts I have made a trace file from sql profiler and open it. Also I have created a windows performance...

Database Refresh - Hi, What is the database refresh. how it will work. How to do the database refresh in sql server real time environment. Thanks...

Urgent help in Script to restore the database by creating empty log file - Hi, I have a situation where in I need to restore a .BAK file and i do not have enough...

Performance - I got the saved sql profiler trace file from DBA team for performance and would like to know the SQL Trace...

Deallocation of Space from Image Column - I have a table which has an image column that contains an average of 67,000 bytes/row with 32,000 rows giving...

How to set limited access to certain database tables? - Hello - Developers at the company I work for want to access one of our databases to create an iPad app...

cmd job - Dear Experts I am just trying to test this agent cmd job msg "%username%" "HI" but an error said that this user...

Indexed View - Hi Experts, We have an indexed view and the development team want to keep the data in indexed view intact even...

List sql servers, versions, SP installed in all the servers of environment. - Help required, How to gather / list a report of all the sql servers and versions in my environment Prod / Dev / Test...

Can we move the CT Tables to different filegroup or Different Disk from Base tables. - Hi All, I understand that when we enable CDC on a database, all the respective CT Tables are created on...

SSIS 2008 Connection Error - Client: Windows 7 Server: Windows Server R2 SQL: SQL Server 2008 Trying to connect to Integration Services via SSMS2008. I'm getting the following...

SQL Agent Recurring Schedule End Event - What actually causes a SQL Agent job to run for the last time within the daily frequency settings? I ask...

PF Usage of RAM reaches near to the maximum memory of the Server frequently - Hi All, i have a server which has 8 GB of RAM , 3 GHz, which has SQL Server 2008 Standard Edition...

SQL server performance condition Alert - HI experts Do any one knows a good article explains in detail how to use sql server performance condions ALERTS Thanks lot

Transaction Log Backup - Our company has never backed up the transaction log file, and is now about 143 gig, with about 35 gig...

Server configuration - Hi, I've a customer that has a weird infrastructure implemented to store his customers databases. He has 4 SQL Servers, with 1...

Programming : Powershell

How to pass a response to a .exe called by PoSh - I'm calling an command line .exe from within a PoSh script. This is generating a hit Y for Yes / N...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Getting Dynamic columns into my SSRS report - Hi, I have one SP it returns 10 columns based on selection some more columns i am getting(dynamic columns) how to get...

Problem with a report. - Hello, I have a Dataset with: Company, Depot, Account, Year Period, Ethical, Glaxo, Drug, Surgical, General, PI, Misc, Phone, PostCode, CustomerName,...

decimal separator - I have to create French report. In french the decimal separator is the comma. How to set the decimal separator...

Unselect 'All' when choosing a value in the drop down lista. How? - Hi, I have a problem with a SSRS report where I am using a drop downlist. I have searched on the...

Reporting Services : Reporting Services 2005 Development

Creating Variables in SSRS 2005 - Is there a way to create variables in the 2005 version? There is a tab with Datasets on the Layout...

subscription issue - I have a report developed in ssrs 2005 and it runs on 2008 report server. It has 5 parameters (out...

Select all records in current month - I have to change a prior report which included all records in which DateFinished is in the last 2 weeks...

Data Warehousing : Integration Services

SSIS Extract and Load - HI, I am trying to write a ssis package which moves clients file from certain directory to another. Source Path : D:\Temp\Source\Client...

Data Warehousing : Strategies and Ideas

How often do you back-up your data warehouse? - Hi All, I'm trying to find a happy medium to a back-up plan for an up-and-coming data warehouse. The DB won't...

Data Warehousing : Analysis Services

Measure shows incorrect value - I have a measure that calculates revenue. There are several partitions to store the sales data. Each partition holding about 10...

a simple? MDX query returning top 10 and rest result - Hi, I need a simple (if possible) MDX query on a cube. My starscheme looks like this (simplified for this question ) : - a...