In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Compare logo 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 DBA Bundle logo Top 5 Hard-earned Lessons of a DBA
Lesson two has now been released! Read ‘Beating Backup Corruption’ and learn from the very best. Read now.
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

In This Issue

MDX Guide for SQL Folks: Part III - Advanced Analysis

Learn everything about MDX drawing only on your T-SQL knowledge in this series. Frank Banin continues talking about Calculated Members, Named Sets, and more in part III. More »


If you are using indexed views and MERGE, please read this!

Here is some information about an important MERGE “wrong results” bug, involving indexed views, that could be affecting the accuracy of your queries right now, and what options you have for working around the problem. More »


From the SQLServerCentral Blogs - What is DBCC SHOW_STATISTICS Telling Me About My Data?

Per Books Online, DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view.  Basically it shows you... More »


Editorial - Bring Solutions

What's your job? Most of you will answer "DBA", or "developer", or "manager", or something that corresponds to your title. That's partially true, but for the most part if you are in Information Technology, I think your job is to solve problems. You should ensure that your systems, or your code, or whatever you work on, is running smoothly for the people that use them.

That isn't always possible. There will be times that things break, that bugs crop up or systems fail. When that happens, people don't want to hear about the inherent problems, or even what went wrong. They might ask, and they might be curious, but really what they want to know is when will the system be fixed and what will it cost.

I've never had a manager that was really interested in the problems that caused a failure when systems were down. I've rarely had managers interested in potential problems I've noticed in our organization. Managers might be curious about the issues, but they don't want you to bring them problems. They want you to bring them solutions when you see a problem, multiple choices to fix things, and a recommendation for the option you feel is best. They want a solution that works well, doesn't cost a lot, and can be implemented quickly. 

Competition for jobs gets more intense all the time and the employees that look for problems and provide solutions without being asked will be the most valuable. They will stand out from their peers and are more likely to not only keep their jobs during down times, but will get asked to solve the interesting and challenging problems.

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

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?

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

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


Yesterday's Question of the Day

Your computer shows the following system date and time:

Date: 1/17/2013
Time 5:10 PM

Which of the following functions will return the date and time as shown below?

2013-01-17 11:40:42.8078994

Answer: SYSUTCDATETIME ( )

Explanation: SYSUTCDATETIME returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.

Ref: http://msdn.microsoft.com/en-us/library/bb630387.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

Reorganize indexes for all tables in all user databases

Script to reorganize all indexes on all tables in 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

How come my log file is still now able to grow? - Initial log file size is 5gb, 10 % file growth, Auto Growth enabled, unrestricted growth checked and drive has plenty of...

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 SERVER AGENT ERROR - Hi...Any one can give quick Resolution for this below error: This error is coming from after we did migration: "SQL Server...

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

Migrate database from sql 2005 to Oracle - We have one of the server 2005 database 450 GB, business needs to migrate from sql server to Oracle.. Please any...

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

Table is not accessible in database - A table in sql server database is seems to be locked and is not accessible. I am executing Select statement, it...

Missing green arrow on the sql server icon in object explore - Hello everyone I have tried to Google this issue for quite awhile and can't find any definitive solution. Now in my...

SQL Server 2005 : Backups

Sql server 2000 software links - Hi All, Can you please any one tell me where can i get sql server 2000 software. if possible plz send...

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

How to Unzip the zipped file - Hi, I have a WinZip file with the .dat.gz extension and i need to unzip that zip file and extract the...

SQL Server 2005 : SQL Server 2005 General Discussion

Intallation error no clue what it is - Hello all, Trying to install SQL Server 2005 after uninstalling sql native client only and it fails here is the...

how to attach multi queries result to different sheets? in same excel - @subject ='Status Log for Today' ,@profile_name ='Status' ,@query ='my query' ,@attach_query_result_as_file = 1 ,@query_attachment_filename ='Status.csv' output has Column name called COUNTRY contain different countries AU,MY,SG I want...

Extracting xml data from text column - I'm very new sql server (oracle dba). I was assigned to work with sql server 2005. The user wants a...

SQL Server 2005 : SQL Server 2005 Performance Tuning

HOw to get Automatic Email notification for High CPU Utilization >80 % with the query which is causing the high CPU usage - hi all, Do any one have any script by which i can get a automatic email notification when a HIGH CPU...

SQL Server 2005 : SQL Server 2005 Integration Services

Exporting 30 query output to Excel spreadsheet - Hi All, I am new to SSIS and am currently working on a requirement as follows: We have 30 sql scripts which...

SQL Server 2005 : T-SQL (SS2K5)

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

Need Help in Converting Rows to Columns - Hi All, Can anyone help me in below problem. I have below result set dtDate sDocIdPrefix CNT 2/11/2013 653 3683 2/11/2013 649 1334 2/12/2013 647 1422 2/12/2013 649 6602 2/12/2013 653 4781 And i want it to be...

compare varchar dates - I have a varchar column containing dates (not my design) with this format 2013-02-12 I need to extract records between two...

Wrong Index Being Used - I was wondering if anyone has seen this before and has any idea why it is happening. This is occuring...

bcp Errors - I am trying to run the following statement, but get errors. Any help would be appreciated. DECLARE @SQL VARCHAR(4000) SET @SQL= 'bcp...

SQL Server 2005 : SQL Server Newbies

(Newbie) Time and Attendance 3 Row Query for Clock In, Clock Out and Hours Worked (Retrieving Wrong Clock In Record) - Hello and Thanks for looking at this, I am not a developer, so my apologies if my information is provided incorrectly....

Update multiple rows with a specific sequence - Can anyone help me to build a sql query for updating multiple rows with different id. Sample Data, ACTCODE datatype nchar(6) ACTCODE...

SQL Server 7,2000 : Administration

Connecting to different instance of SQL Server - Hi all, I have installed 2 instance of SQL Server on a same computer . One is a defualt instance and...

SQL Server 7,2000 : Replication

SQL Server 2005 Replication between Disconnected Sites - We have a database application that we want to run on two (and possibly more) different servers that will not...

SQL Server 7,2000 : T-SQL

How to calculate total working hours of all the employees in specified dates. - Hi, I'm a learner, please help me by solving the following issue. I have data of all employees whose login/logout times are...

SQL Server 2008 : SQL Server 2008 - General

Script to validate flat file - Hi all, i created one ssis package which has for each loop container to loop through all text files in...

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

percentage restore complete on sql 2000 - We have dmv,s in sql 2005 and 2008 to find percentage restore complete,n is any query to find percentage restore...

How t Split last two char from varchar dataset..? - [size="3"][font="Courier New"]Hi All, This is the query i have written but unable to extract last two char.. max(CASE CHARINDEX(' ', GT.FG_DESCRIPTION, 1) WHEN...

Error while installing sql server 2005 express in Windows 7 - When i install SQL server 2005 express in a Windows 7 PC i got following error Microsoft SQL Server 2005...

Is there any way to find out if an instance/DB is in cluster or not ? - Experts, Is there any way to find out if an instance/DB is in cluster or not ? I have no idea about...

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

REG:JOB - Hi, I want to run a script for every 9 months. Which deletes data in all tables in a database....

Class not registered - Hello, When i want to save Maintenance Plans on MSSQL server it give me below error,how i can resolve issue...

SSIS performance - Hi all, which one is good in performance In ssis oledb source(sql server 2008 R2) 1. selecting table and uncheck the columns...

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

Merge Replication: Event ID 14151 - Hey All, I'm in the process of setting up Merge Replication on two test databases before I roll it out...

Query help - Consider this scenario, Item ID HierarchyId 1 103 2 104 Hierarchy HierarchyID Name ParentHierarchyId 100 H1 null 101 H2 100 102 H3...

How can we Group at particular set of records which come in a Sequence , If the sequence changes it would add the records to a new group. - Lets say ,There are 4 columns with Primary Key Constraints L1 L2 L3 Date Forecast 1 a x 01-jan 1.2 1...

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

Help With Foreign Keys - I need to create a simple employee SQl database. One of the requirements is to create primary and foreign keys...

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

System pahe file and sql files - we all know that sql data files are differently read than log files (random Access , sequential Access) so we put...

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

How much data have you replicated with Transactional Replication? - Hi, I have a 4.7 TB Database and I need to replicate a good deal of that to another server....

Can I insert today's date in an email? - I have to set up a sql job that sends an email suppose "This is to test that today is 02/11/2013...

Help with sorting this recursion - [code="sql"] DECLARE @DateLevel int = 0, @DateLevelUp int, @FTID int = 7 CREATE TABLE #FieldRels ( FTIDBase int, FTIDCalc int ) INSERT INTO #FieldRels(FTIDBase, FTIDCalc) SELECT 6,...

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

ExecuteNonQuery returns -1 even if rows are successfully inserted - I have am using ExecuteNonQuery to get number of rows inserted But i found that it returns -1 even though...

"Lost update" Concurrency Problem. Explain it to me please on my example - There is an example I thought up: [IMG]http://imageshack.us/a/img171/8677/cimg3342h.jpg[/IMG] Transaction 1 started first It changes the value from 0 to 1, Transaction 2...

Last Sunday of a month in sql - Hi all, I would like to know how to find last sunday of a month in sql... I wanted basically to...

Sql server Isolation levels - HI, Which is the most preferred isolation level in T-SQL? Does it change from case to case? Say does it differ...

logical consistency-based I/O error - Hello, when i want to see properties of one data base ,or run maitenancetools or backup sqlserver give me this...

Dynamic SQL and sys.sql_dependencies - Hi, When writing a procedure using dynamic SQL (cause of parameters that may not be used, ...) SQL Server doesn't "fill" the...

SQL Server 2008 : T-SQL (SS2K8)

sql server connection to database - if the following is in the connection string in an app.config file, the following will use the sql server login...

Grouping a String by everything other than the last numbers considering they exist - Hey Guys, Our database has Unique SKU's for each product, however when it comes to Jewelry every size needs its...

Sorting Issue - I have some transactional data that i am having hard time to sort properly. CREATE TABLE [MyTransactions]( [Id] [uniqueidentifier] NOT NULL...

Trying to select XML data for one tag from an text field and isn't returning the data I need - Hello DBA's/Developers, I'm trying to select XML data from one tag that is stored in an TEXT data type and can't...

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

Need a backup script to get backup history status for the latest backup from all linked sql servers - Hi Guys, I am actually in need of a t-sql or a powershell script that will help me get the latest...

SQL Server 2008 : SQL Server Newbies

buld insert question - Hi all, I have a text file that has one number in it. I need to load that number into the...

Case Statement Issue - Everything was working fine with the script below until I tried to add a Case Statement with a SELECT Clause...

To get the db name as parameters - Hi all, I have a scenario like i have to create the same database for our different clients. The clients name...

count in more than one table - Hi folks, I do two tables that have the same structure. Table 1 (SalesNew) contains the sales for the actual year,...

execution plan window - I am trying to observe the graphic execution plan, but my query is big. Is there any way that I...

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 : SQL Server 2008 High Availability

Mirroring failover - Hi All, I have to failover couple of the prod databases.....which resides in cluster. What are some of the things I need...

SQL Server 2008 : SQL Server 2008 Administration

SQL profiler - Dear Experts I have created a trace file, and a windows performance monitore counter to combine both in sql profiler, but...

Clustered Index Space - Hi All I understand that when you create a clustered index, SQL Server doesn't create a physical structure seperate from the...

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

install sqlserver using with localsystem and domain account - Can some one clarify. why we can and cannot install with localsystem and switch to a domain account later.

whats wrong with this memory status? - I am running a SQL server 2008 R2 Ent X64 with Lock pages enabled....AFAIK sys.dm_os_memory_clerks is supposed to return details...

Brand new Windows Server / SQL Server 2008 R2 installation - OS: Windows Server 2008 R2 SQL Server: SQL Server 2008 R2 Std. I recently upgraded one of our servers to become a...

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

Low disk space alert - I would like to setup a low disk space alert when space more than 70 percent without using powershell scripts...

Rebuild Index Maintenance Job Running Long - I created a rebuild index job to run weekly on Sundays at 3am. I just used the wizard to create...

Career : Certification

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

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

format the number in indian currency format - hi.... to all.. i'm using sql server 2008 express with advance edition with BIDS... i've developed one report in that employee salary.....

Total indian currency format - hi... to all i've created an report in that report i've one salary field now i want to display the total amount...

Reporting Services : Reporting Services 2005 Development

SSRS integration with another application. - Hello, i want ask about possibility to integrate SSRS with another application. In my case there is a Business Sevice...

Data Warehousing : Integration Services

Unzip error through Excute process task - "Error Message : your ID does not have sufficient privileges to install software. Also,winzip was not properly installed by user with...

I need to download SSIS in my system - Sorry if i am posting this in a wrong forum. I need to download SSIS .Where i will be able to...

Help with VB Script for Sending Mail in SSIS - I have managed to send email from my SSIS package using the script task with VB code. I am not good...

Cozyroc Parallel Loop Task and the Foreach Loop File Enumerator - Hi friends, I'm trying to improve the the performance of a foreach loop task, that reads around 20.000 pdf files each...

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