In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro 5GB free hosted storage with SQL Backup Pro
Download SQL Backup Pro and securely copy your backups to secure hosted storage. You’ll get your first 5GB of storage free. Try it now
 
SQL Data Compare 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 Skills Deep technical training by world-renowned experts in 2013.
You can't get better ROI for your training budget. Read more.

In This Issue

A Hazard of Using the SQL Merge Statement

The SQL MERGE statement can make your DML querying more efficient but you need to take care or you may get burned More »


Expand Your Skills at the PASS Business Analytics Conference

Join top business intelligence and analytics experts in Chicago April 10-12 for 60+ sessions on the best practices, prescriptive guidance, and strategic vision you need to get the most from your data. Use code BACSSC to save $250 today! More »


Join Reordering and Bushy Plans

Normally, the query Optimiser won't consider 'bushy' plans, where both operands to a join operator could be intermediate results from other joins. This means that it won't produce good query plans for some types of query. Hints, by themselves won't do it. More powerful magic is required. More »


From the SQLServerCentral Blogs - How do I move a SQL login from one server to another without the password?

This is an uncommon task but one that does turn up every once in awhile. A SQL login has to... More »


Editorial - Data Darwinism

A few years ago my son asked me to buy him The Unincorporated Man. After he finished it, he gave it to me and we read all four books in the series, which we both enjoyed. The premise of this future civilization is that each person is their own corporation, selling stock in themselves to anyone in the world. As with a company, the better your performance at life, the higher the price. However there is also accountability, with your actions, jobs, etc., potentially limited by your "board of directors", who are the shareholders in your corporation. It sounds a little drastic, but it's not as bad as you might think. It's actually a neat idea.

It's also somewhat of the way the world works now, although without all the disclosure. In today's world it's actually much easier to hide your flaws and poor performance because the information isn't always readily available to potential employers. Some of us see this in the poor performance of colleagues, who were hired with good recommendations or interviews. We may find out later that these were exaggerated, though we often can't (or won't) do anything about this, suffering through poor performance from the individual or company.

That may be changing. I ran across an interesting article where vehicle drivers were let go from their jobs because clients had rated them poorly or complained about them. There is some controversy here, but it does bring up the issue of more companies that look to "go where the data goes" in operating their businesses. It's all too easy to begin using metrics, measurements, feedback, and more to make business decisions. This is one of the driving forces being building business intelligence systems in our industry. However if the models, assumptions, or data are flawed, bad decisions are not only possible, but probable. It's easy to trust the computer's report more than is prudent, especially when we have no good way of measuring the quality, or even appropriate interpretation of the data.

There are lots of BI systems that work well, and provide companies with many benefits, but there are probably also plenty of them that don't work well and we don't hear about them. There are systems that use flawed, incomplete, or otherwise compromised models to help business leaders make decisions. Ultimately a BI system needs lots of human intelligence added to it, including judgement and refinement, constant tweaking, and a bit of common sense. I would hope that using data to cut off some service, fire an employee (or decline to interview someone), or make any far reaching decision has a lot of experience and audit built into the system to prevent its abuse.

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

What will be the result if we have following function and executing Query2?

CREATE Function fn_tst
(@str VARCHAR (MAX))
Returns @tbl Table 
(
str1 VARCHAR(100),
str2 VARCHAR(100),
str3 VARCHAR(100),
str4 VARCHAR(100),
str5 VARCHAR(100),
str6 VARCHAR(100)
)
AS
BEGIN

Declare @Mstr VARCHAR(3000),
@cmpstr VARCHAR(2000),
@str1 VARCHAR(100),
@str2 VARCHAR(100),
@str3 VARCHAR(100),
@str4 VARCHAR(100),
@str5 VARCHAR(100),
@str6 VARCHAR(100) 

DECLARE @i INT

SELECT @Mstr=@str ,@i=1

WHILE @i<= 6
 BEGIN
  SET @cmpstr=SUBSTRING( @Mstr ,1,ISNULL(NULLIF( CHARINDEX(',',@Mstr),0)-1,LEN(@mstr)))
  SELECT @str1=CASE WHEN @cmpstr Not like 'A%' THEN NULL ELSE @cmpstr END,
        @str2=CASE WHEN @cmpstr Not like 'B%' THEN NULL ELSE @cmpstr END,
        @str3=CASE WHEN @cmpstr Not like 'C%' THEN NULL ELSE @cmpstr END,
        @str4=CASE WHEN @cmpstr Not like 'D%' THEN NULL ELSE @cmpstr END,
        @str5=CASE WHEN @cmpstr Not like 'E%' THEN NULL ELSE @cmpstr END,
        @str6=CASE WHEN @cmpstr Not like 'F%' THEN NULL ELSE @cmpstr END

  SET @Mstr= SUBSTRING(@Mstr, CHARINDEX(',',@Mstr)+1 , LEN(@Mstr) )

  SET @i=@i+1
 END 

INSERT INTO @tbl 
 SELECT @str1 , @str2 ,@str3 ,@str4 ,@str5 ,@str6 

RETURN

END

--##Query 2
Select *
  from fn_tst('AA,BB,CC,DD,EE,FF')

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

This question is worth 1 point in this category: CASE statement. 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 Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Yesterday's Question of the Day

USE master;
GO

BEGIN TRY;
BEGIN TRAN;

SELECT 1 AS a INTO #temp1;
RAISERROR ('Catch Me If You Can', 10, 0);

END TRY;
BEGIN CATCH;

PRINT 'I Caught you!!';
IF @@TRANCOUNT > 0
ROLLBACK TRAN;
THROW;

END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRAN; 

What happens when you run this code in SQL Server 2005 and higher?

Answer: The Code fails with a syntax error

Explanation: A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.

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

» Discuss this question and answer on the forums

SQL Server Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Featured Script

Copy Table

Copy tables from one schema to another schema. 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

Windows logins cannot connect to sql instance - hello, i have a sql 2005 instance in a two node cluster. the instance has mixed authentication, suddenly i started...

why is this column NULL?sys.DM_exec_query_plan - I am using below SQL to get actual execution plan: [code="sql"] select * from( SELECT cp.objtype AS PlanType, Object_name(st.objectid, st.dbid) AS ObjectName, cp.refcounts AS...

Database IO is frozen for snapshot / IO is thawed - I notice in my error log there are messages which says the Databasename IO is frozen for snapshot then follow...

sql server agent jobs - Can anyone tell me how can i copy the sql server agent jobs easily from production to test server without...

Login failure - I had a user state they could not log into a specific database with an NT login and the SS...

Error while running xp_readerrorlog - I can run xp_readerrorlog when server is not in single user mode. I get ''Failed to open loopback connection'' error...

pass variables between SQL Server tasks in a JOB - I'm using Sql Server 2005. I'm trying to pass a variable between the tasks in a job. NOT SSIS. We...

HOw to send SMS Using t-sql - Hi, How to send using T-SQL ( ofcourse using GSM port integration ,but how to intigrate ) Any suggestions.. Thanks in advance,....

SMS from SQL Server - Hi , I need to send SMS from my application, How to send SMS from sql server... Can you please suggest me..? Thanks in...

unattended installation of sql sever 2008 - Hi all, I have to install MS SQL Server 2008 on about 150 remote servers. Since doing this is a tedious...

SQL Server 2005 : Business Intelligence

SSIS to IMPORT and then EXPORT to/from EXCEL - I have an SSIS package that creates the temp table, then uses a data flow to import a CSV file...

I cannot import SSIS package into MSDB folder. - Hi all, I am working on SSIS and created integrated services project and saved them under a new folder. I want to...

SQL Server 2005 : Data Corruption

2k5 MDF Emptyfile -> NDF - Hi, I wonder if someone can help? I was attempting to move contents of mdf to a secondary data file ndf but...

SQL Server 2005 : SQL Server 2005 General Discussion

prerequisite to apply service pack 4 - hello experts, In one of my production server i want to apply service pack 4 current configuration : sql server 2005 standard...

HashBytes Function - Change varbinary output - DECLARE @HashThis varchar(255); SELECT @HashThis ='Hello world!'

SQL Server 2005 : SS2K5 Replication

best replication method for large database - What is the best replication method for large database? :unsure: thanks

SQL Server 2005 : SQL Server 2005 Integration Services

Creating instances of excel file - I'm using SQL Server 2008 BI. I have an oledb source outputting data into an excel destination. I want the...

SQL Server 2005 : T-SQL (SS2K5)

Assing point to race times - I bulk insert a csv file (generated from our timing laptops at race events) into storage table in our database. the...

how to verify blank spaces using substr function in SQLSERVER 2008 - I am new to SQL SERVER 2008, trying to explore T-SQL I am trying to split a sentence into words using...

SQL Server 2005 : SQL Server Newbies

create table to group th e people of same department - NAME AGE SALARY DEPARTMENT SHARATH 24 21000 MARKETING RATHAN 35 21000 MARKETING RATS 28 21000 IT DARSHAN 23 20000 IT LINI 25 25000 IT SHAN 23 20000 MARKETING SUDARSHAN 22 20000 IT to above given table i wan to write a qury that display IT MARKETING RATS RATHAN DARSHAN SHARATH LINI SHAN

SQL Server 2008 : SQL Server 2008 - General

Server Review - What are the things we need to consider for reviewing a windows server keeping SQL server as our point of...

encryptbypassphrase:decrypt or read values without passphrase - Just interested to know some details about encryption. If i have admin access to the sql server then i can...

Non Clustered Index - Hi, One of my query (simple update query) with clustered index utilizing 98%. And then i added non-clustered index on the...

What is a Staging table ? - What is a Staging table ? How to use it ? And When is it advised to use Staging tables Regards, Arjun

Query in SSRS - there is duplication of field source column name when i saw in the dataset properties of my report for a...

Converting to Float in ssis - I'm exporting Flat File to DB Table. In Flat File I have numeric column with some values has empty space. In Derived...

Splitting the rows -- distributing on logic - Hi Experts , here is sample table and data create table #sample ( Invtid varchar(255) ,[Final SiteId] varchar(255) ,Whseloc varchar(255) ,Days int ,Qty int ,[Aging Stock] int ,Priority int ) insert into #sample select '11003291001-----NOB30000','0V003','A00A00',269,285,305,1 union all select...

Query takes long time - I have a stored proc which takes quite long time. I have seen the execution plan and I found that...

oracle 9i to Sql server 2008 R2 migration - Hi, We are having oracle 9i as database and migrating to sql server 2008. Our oracle 9i database connects to another oracle...

update stats - How update stats works in sql ?

Data Compression in SQL Server: Shubham Saxena - Dear Friends, Please help!! I need to build up a function or stored procedure to compress/decompress a large object. like image or blob...

How to write custom query which shows last two records added only. - For Insurance where logic is something like after renewing a policy that renewed policy is current policy and previous policy...

Cannot convert between Unicode & Non Unicode Strings Error - Hi, I have a SSIS package that imports an ADO.Net source file & inserts into an OLEDB Destination. There are about 25 fields...

Can I move the older ERRORLOG files? - Hi all, Long story short, we had a process that was filling up our log file at an impressive rate (impressive...

Cardinality Issue - I have a 100,000,000 row table with a GUID primary key. (This is an IBM FileNet/ECM database, and utility query.) The...

Requirement of .net framework for sql server 2008 r2 in Windows 8 - What is the actual version requirement of .net framework for installing sql server 2008 r2 in a windows 8 PC? According...

Unplanned outages causing bankruptcy - * I wasn't sure where to ask this throughout the forum, so I decided here was best. * For my thesis in...

How to preserve global temporary table data - Hi guys, I would like to know if anyway to preserve global temporary table data till some one explicitly drop it....

Check Constraint Question - Situations Where a Child Record Should Be Limited to One Occurrance - One of my databases has a table for Project Dates. Some of the milestone dates can occur multiple times such...

SQL Server 2008 Transaction log backup issue... - Hello SQL Masters, Greetings! I would just like to ask regarding Transaction log backup. While backing up the T-log, is there still...

Deadlock on update command - I have the following dead lock happening quite frequently at 3-4 times a day. The deadlock seems to happen on...

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

Suspended process in msdb database - Hello gents I have right now a suspended spid 53 at msdb which is in suspended status for over 2 hours....

Subscriptions - Failure sending mail. 'Include Link' works, 'Include Report' does not. - Hi Everyone I'm hoping you can help me, I've followed most links on the net and decided to post. SQL 2008 R2 Local...

Differences between sql server 2008 and 2008 R2 - I did some research to find the differences between between sql server 2008 and 2008 R2 but couldnt find much....

Informix Linked Server problems - I have been asked to set up a few procs that will take data from our core data source in...

SQL Server 2008 : T-SQL (SS2K8)

Selection using date held as a string - In our database we have a parameters table that holds a parameter type (e.g. D for date, N for number,...

Get summary of column values from a result set - good morning everyone, I have a procedure taking some input parameters that are used for a select statement. After returning the...

Stored Procedure using huge IO? - Hi, one of the SQL SP written like this as below. it is getting blocking every time due to waiting IO...

Transaction Isolation Level - Hello Everyone I have a medium sized SSIS Package, with multiple steps, that call a single sproc each. I am working...

Help with Query - Hi I have a query which returns the following Key segment Value A001 001 3 A001 002 4 A001 003 4 A002 001 2 A002 002...

TRIGGERS... - Hey guys, Our team ran into a problem over the weekend and Im confused over it... We are in the process...

Add Auto Incrementing column to my SELECT statement - Along with the data that I am pulling back from query, I also need to provide an ImportID column with...

Need to unpivot a table - [b]Hello, I have a table like this[/b] CREATE TABLE [temp]( [Country] [varchar](200) NULL, [Country_Group] [varchar](200) NULL, [Year] [varchar](200) NULL, [R1_TV] [decimal](38, 2) NULL, [R2_TV] [decimal](38, 2) NULL, [R3_TV]...

i need the max length value of every column in every table in a database - I'm creating some dynamic sql to loop through each table and column and basically generate some dynamis sql that will...

How to select data in a particular format - Greetings all. I have data in a view that when selected from looks like this: CertYear CertCode Quantity -------- --------- -------- 2008 HI-Master 2 2008 HI-Instr...

please help with cursor syntax - I have a simple cursor and I need help fixing syntax.(I know that in this scenario I am using cursor...

Generate all possible number combinations for a provided list of numbers - Hi Experts, I want to get "all possible number combinations" for provided list of numbers. As an example, if I have 4...

How do i line up the weeks in T-SQL ? - How do i line up the weeks ('Weeks-2013' and 'Weeks-2012') in T-SQL ? Below is sample data to test with: SELECT DISTINCT...

LTRIM,RTRIM - hi i have this code in where clause RTRIM(LTRIM(ODER_ID))= ' ',WHAT THis do

SQL Server 2008 : Working with Oracle

Need better solution to Import from Oracle - I maintain BI that needs to import the data from Oracle every day. I use the import wizard to create...

SQL Server 2008 : SQL Server Newbies

importing an excel file - hi professionals. I am trying to import and excel spreadhseet into the database by right clicking the database, tasks, import. I...

query to find timgaps between rows - Basically I want missing timegaps between the @startdate and @enddate with tbl_book and also in between tbl_book (do not care about...

on primary - why [b]on primary [/b] is used while creating a table is it necessary ex: create table temp (name varchar(50),age smallint,salary int)[b]on primary[/b]

Cursor in Stored Procedure - Hi, I am a SQL newbie and need to update code in a Stored Procedure that has a cursor. Is it...

Error authenticating proxy - Hi, I've got a SQL Server 2008 job which ran ok yesterday but has fail today with an error message ' Error...

SQL Logical errors - Hi All, I need some one help me identify why am getting logical errors in the output of my query below....

Had a job failure but it doesn't appear in the job history - Hello, I am documenting job failures in the last two months and I cannot find the one that we had in...

Stupid Question: Return Object Explorer to It's original position - Stupid question but I accidentally moved object explorer from it's original default position on SSMS. By default, object explorer is...

Available disk space policy - Hi everyone. I designated a SQL Server 2008 instance to act as a central management server. It is running a...

SQL Server 2008 : Security (SS2K8)

Customize DB Role - Hi, I want create one database customize role, that role should able to do only fetching the data from SQL view...

Disable & Rename 'SA' - Hi All, Our SQL risk assessment determined that 'sa' should be renamed and disabled. As a fallback readiness, i gathered all...

SQL Server 2008 : SQL Server 2008 High Availability

multiple instance sql cluster with different windows configuration - We have a two node cluster. In order to utilize the max resources we are planning to add more instances...

SQL Server 2008 : SQL Server 2008 Administration

Scripts To Monitor SQL SERVER - Hi Everybody , it's been two or three weeks I'm trying to find scripts to monitor different SQL SERVER indicators, but...

Restore Master Database - I'm testing what to do in scenario where the Master database is corrupted beyond the ability for SQL Service to...

Installing SQL server 2008 Express from windows application - Hi I am trying to install SQL Server 2008 Express from Window Application I am using this command [code="other"] "/qs /ACTION=Install /SkipRules=VSShellInstalledRule RebootRequiredCheck /HIDECONSOLE...

Career : Certification

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

Career : Resumes and Job Hunters

Have a job offer from US - I am looking forward to get a job in the USA. And I don't have residence permit. May be anyone have...

Programming : Powershell

INSERT statements containing regex as a string being read as regex - I am attempting to run a sql script with 4 simple INSERT statements. Each of the 4 INSERTS is inserting...

SQLServerCentral.com : Anything that is NOT about SQL!

So How Do You do your mass Mailings? - Once a year I help out a local non profit promote their annual event. This is the third year for...

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

Line chart not showing multiple series data - I have a line graph chart which should display either a single or multiple series. It has three variables which...

code to show top five entries in a pie chart - Hello I have a pie chart which displays many legends on a pie chart but i need to write sql code...

Help to get the Expression for Calling a column from different dataset - hi All, I'm preparing one graph for 22 different datasets. # of records in each dataset are different. Thats why i cannot...

Data Warehousing : Integration Services

Moving data from SybaseIQ tables to sqlserver using SSIS. - Hi, Can any one help me to load the data form SYBASEIQ to sqlserver using ssis. 1. where do I get the...

Transfer file into Archive folder - Hi All, I am having text file as a source, I want to load data from this comma separated text file...

64-bit driver for Excel - My understanding is that the file structure for the 64bit and 32bit versions of Excel are the same in Office...

The column "Column 2" cannot be processed because more than one code page (65001 and 1252) are specified for it. - I get this error if i try to import a tab delimited flat file to a table in sql server. [i]The...

Data Warehousing : Strategies and Ideas

SQL Server 2012 VLDB Data Warehouse Design Question - Appreciate feedback to this question. I'm working on a design for VLDB that will be in the several TB size...