In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor Get your priorities straight with SQL Monitor
“SQL Monitor gives me the ability to quickly see what my priorities should be, and delivers the information I need to make the right decisions,” Aaron Kolysko, DBA. Start monitoring with a free trial.
 
Deployment Manager A simpler way to deploy
Deploy your .NET code, services and SQL Server databases in a single, repeatable process with Red Gate’s Deployment Manager. Get started now.
 
SQL DBA Bundle Top 5 hard-earned Lessons of a DBA
New! Part 4, ‘Disturbing Development’ by Grant Fritchey, features the return of Joe Deebeeay and a server-threatening encounter with ORMs - read it here.

In This Issue

Understanding T-SQL Expression Short-Circuiting

Understanding the basics of how T-SQL logic works in branching is important to ensure you code works as expected. This article will help you learn how this impacts control of flow language.  More »


SQL Saturday #226 - South Florida

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held Jun 29 2013 in Davie, FL. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleages know about the event. More »


Maximizing Throughput with TVPs

TVPs offer several performance optimization possibilities that other bulk operations do not allow, and these operations may allow for TVP performance to exceed other bulk operations by an order of magnitude, especially for a pattern where subsets of the data are frequently updated. More »


From the SQLServerCentral Blogs - Natural Born Killers (The series) – Day 2 Table Design

To those that don’t know me “Natural Born Killers (The Series)” sounds like a very strange choice for a SQL... More »


Editorial - A Release from Data

MoreshibaThis editorial was originally published on Aug 14, 2008. It is being republished as Steve is at SQL in the City 2013 - London today.

Have you ever had that frustrating day where you can't get your queries to work, or can't find a problem and spend all day working on an issue you can't solve? Have you had customers or clients that make unreasonable demands, and you want to just punch the wall? Any co-workers that upset you with requests or complaints that make you walk away before engaging in some other career-ending action?

Someone actually suggested to me that this might make a good topic as they noticed quite a few people that were interested in databases were also interested in martial arts. At first glance it might seem strange, after all, the traditional geek is more of a passive individual, someone that spends their spare time in virtual worlds they visit on the computer, on a TV screen, or in their minds while reading a book.

I studied martial arts as a teenager for about 6 or 7 years, trying different styles and teachers as I moved around early in my career. It was a great exercise and a nice release after working hard during the day. However I got married, had kids, and my career took off, and I turned to running, swimming, and other activities I could do during lunch, freeing up evenings for my family.

Over the last year, my middle son become interested in karate, and it got me back into a dojo after 15 years. We go together 3-4 times a week and even have one class together where parents and kids and study together. I'm even planning on going to the PASS Summit early and taking him to an aikido demonstration the weekend before ( Puget Sound Aikikai).

Martial arts are not for everyone, but I will say that being able to focus on hitting a pad, and actually hitting it, is a great stress reliever for me. If you've never tried it, I'd recommend taking a class or two from a school that doesn't engage in a lot of contact between people to get a feel for the sport. You might really enjoy it.

If it's not for you, I do think it's important for everyone to take care of their body a bit, especially by putting it under some physical stress. Walking, running, basketball, even chopping wood with Grant, anything that gets you moving will only help the rest of your life. I'd encourage everyone to find some release for those days that you just need to unload some stress after work.

» Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure :). 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.

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:

Do these two queries execute successfully and give the proper output of adding one day to the date? Choose if they execute or error.

--Query 1
DECLARE @dt DATE
SET @dt = '2013-05-30'
SELECT @dt+1
go
--Query 2
DECLARE @dt1 DATETIME
SET @dt1 = '2013-05-30'
SELECT @dt1+1

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.

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

What is the number of locks held by any T-SQL statement on a single reference of a table that triggers Lock Escalation (i.e. Row lock to Page level lock)?

[Note: This is for SQL Server 2008 R2 Onwards]

Answer: 5000

Explanation: A lock escalation will occur if and only if a Transact-SQL statement has acquired at least 5000 locks on a single reference of a table. Lock escalation is triggered when a Transact-SQL statement acquires at least 5,000 locks on a single reference of a table. For example, lock escalation is not triggered if a statement acquires 3,000 locks in one index and 3,000 locks in another index of the same table. Similarly, lock escalation is not triggered if a statement has a self join on a table, and each reference to the table only acquires 3,000 locks in the table.

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

» Discuss this question and answer on the forums

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.


Featured Script

Reindexing for fragmented indexes

Script will give you the list of fragemented indexes. (For SQL 2005-2008) 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 many instances we can maintain in active/passive - Dear Sir, I am in the learning stage of cluster.I am having some doubts which i mentioned below. How many instances we...

Backward Compatibility Package - Hi all, Is there a way I can check if backward compatibility package is installed on sql server 2005? Currently upgrading...

64-bit OS, 32-bit SQL Server 2000 - Hi all, I have read that 64-bit SQL Server 2000 can only run on Itanium. But I experienced errors while tryiing...

SQL Server 2005 : Backups

Problem backuping up to Network Share - I have a two node Active\Active SQL Server 2005 Cluster. I am running three instances on each node. When I...

SQL Server 2005 : Business Intelligence

Pull Data from diffrent servers - SSIS - Hi All, Greetings, I have multiple ID's (Which are data type Uniqueidentifier), I need an approach for creating SSIS package to pull...

Connection error - Process cube through sql job - I want to process my cube daily night so I create a job for that by follow these steps http://www.symantec.com/business/support/index?page=content&id=TECH193699 But when...

SQL Server 2005 : SQL Server 2005 General Discussion

query required - Dear all, I have following tables TABLE : A ENTERPRISE_MAIN_CODE ENTERPRISE_SUB_CODE ENTERPRISE_DESCRIPTION 1 0 A 2 0 B 3 0 C 4 0 D 5 0 E 6 0 F 7...

SQL Server 2005 : SQL Server 2005 Integration Services

How to move file in archive folder when package failed in ssis. - Hi All, I have one scenario I create one package in which file is coming at daily basis and data is...

SQL Server 2005 : T-SQL (SS2K5)

Error converting data type varchar to numeric in CASE statement - Sorry for the narrative but I can't seem to generate sample data to reproduce my problem... I have a WHERE clause...

VARCHAR(x) VS VARCHAR(MAX) - As far as I know, VARCHAR(MAX) is essentially the replacement for the old TEXT data type. My question is - does VARCHAR(MAX)...

SQL Server 7,2000 : T-SQL

Could not complete cursor operation 16958 - Has anybody seen the following error message: "Could not complete cursor operation because the set options have changed since the cursor...

SQL Server 2008 : SQL Server 2008 - General

Need Help:Error log - Hi all, when i checked my error log it has taken 100 gb of space and my disk is out of...

SSIS - Insert Global Variable Into Table - I have a loop task in my ssis package, where I am reading a new file. The file name is...

Is converting case cause TableScan in SQL Server 2008? - I was told by the DBA that I have to eliminate the LOWER() function in my queries which is causing...

LOCAL SYSTEM account permissions reading Snapshot folder share - Have a question -- how to set up permissions to a snapshot folder share for SQL Servers that have LOCAL SYSTEM...

How to remove an unknown special character? - Hi, I am extracting data from a table to a .csv file in SSIS. The data in one of the fields...

Problem Inserting records per Portfolio into a temp table - Hi guys, I have been trying to update a temporary table with the top 10 holdings per portfolio. I have been...

Unable to Replay Default Profiler Trace - Hello Everyone, I am facing an issue wherein the default trace file(.trc) when opened with profiler on the same server doesn't...

SQL QUERY REQUIRED - Dear all, I have following tables TABLE : A ENTERPRISE_MAIN_CODE ENTERPRISE_SUB_CODE ENTERPRISE_DESCRIPTION 1 0 A 2 0 B 3 0 C 4

How to get user position in table i.e 1st 2nd, 3rd - hello, Im having a bit of trouble trying to return the current users position, iv been looking at this for the...

How to find when my SSRS service was last restarted ? - Expertrs, How to find when my SSRS service was last restarted ? Not the DB Engine.. Only Reporting services.. Thanks in advance.. Smith.

How to print a file using a static ip and a printer name - How to print a file using a static ip and a printer name. Plz help me

Security Material. - Hi all, Can any one refer some web pages or books related to security part of SQL. I am struggling...

How you take the breakup by each component in Multi_Pages ( Otherwise MTL/NonBpool ) - Question : How you take the breakup by each component in Multi_Pages ( Otherwise MTL/NonBpool ) like: SQLMail Ole Components Linked Server Third Party DLL’s Extended SP’s CLR

SQL server agent SSIS error - I get the following error when I execute my package as a SQL server agent job. It is an SSIS 2008...

Running Totals - I am having problems figuring out how to add a running total. I initially tried to get my TempTable to...

Rows to Columns (pivot or anyother way) - Hi am trying to change row values to column based on ControlNO & Seq column. (Please see @currenttable) InspInterval --> int0,int1,int2,int3 ChkProcedureKey -->...

Run inserts from file with many records - Hi! I need to run inserts from file with many records/rows: about 500.000. My Management Studio is not supporting many records, there...

Restoring from the backup after encryption of datbase - Hi friends, I used the following query to restore the encrypted database. USE master; GO CREATE DATABASE Encry2 ON ( NAME = Encry2_dat, FILENAME = 'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.mdf', SIZE = 3, ...

SSIS Package Fails - Unhelpful Error message - We have a job that gets data off a website and imports into SQL. It runs for a couple hours,...

Transaction log and Full backups are not happening on secondary & DR - Hi every one, My title shall give you all information. I am not able to see any kind of logs getting recorded...

Hai all - TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore failed for Server 'RAJESH-PC\NAIDU'. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1125+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Serve

Find the correct answer in the following query - Hi, I have two tables named customer and salesorder. In the customer table i have 1000 customers,Of which 900 customers have orders...

PrimaryKey and Unique Clustered Index - Hi, Have a small doubt - what's the difference between the below 2 SQL statements. 1. Create Table T1 (Col1 Int PrimaryKey,...

Permission to run only certain SQL jobs in 2008 - I have an AD group Group1 that needs access to the SQL jobs ( only certain ones) so that any users...

Partitioning in SQL Server 2008 - Hi All, I have got few queries on partitioning in SQL Server. - Why should we go for partition in SQL...

Get Notification when a new column added to flat file - I am working on sql server 2005. We have some SSIS packages which loads data from multiple flat files. Flat...

Using the same partition function and scheme for multiple tables - I have a database where most of the tables have an integer field say "SetId" denoting a batch of data. If...

SQL Server 2008 : T-SQL (SS2K8)

convert int int to date urgent please - /****** Script for SelectTopNRows command from SSMS ******/ SELECT TOP 1000 [ServerName] ,[IsExisting] ,[IsEnabled] ,[IsScheduleEnabled] ,[NAME] ,CONVERT(date, CONVERT(VARchar(18), LASTRUNDATE)) ,[Date] FROM [Monitor].[dbo].[Job_Monitor] ...

Interesting Question related to Splitting table row conditionally - Hi SQL Addicts, Please help! Its emergency The Table setup is as follows CREATE TABLE dbo.Receipt ( ReceiptID int NOT NULL IDENTITY(1,1) , PolicyID int NOT...

pivot? can't make it work. - Hi there, I'm hoping for a pointer in the right direction as I can't suss this out & feel that it...

Strange issue with sp_OAMethod - My procedure creates a COM object with sp_OACreate. Then it uses the sp_OAMethod to call a "load" method of the...

Non cluster index help (SSMS 2008 R2) - Hi Friends, I have table (Table_A (A_id int(PK), col1 nvarchar, col2 nvarchar, col3 nvarchar, col4 nvarchar, col5 nvarcar Col6 nvarchar)) A_id int(PK),--- already...

compare all tables and find number of common columns between all tables - is there any way to compare every table with each other in a database list the number of common column...

UPDATE, MERGE or table-valued function UDF? - Hi, I'm confused how to solve this: Using UPDATE, MERGE, table-valued UDF or what?:w00t: I know using a cursor is possible but there...

Append characters in a sentence after certain length - Hi, Need your help in accomplishing this one. We run on SQL Server 2008. I've a string that can be upto...

How to group by date in a date range - Hello all, I wonder if somebody could point me in the right direction... I have following table: [code="sql"]SET DATEFORMAT DMY; SET NOCOUNT ON; DECLARE @t...

How To return Year & Month in this format: 2013-06 - Hi All, I need to return the minimum date from my table and manipulate it so it starts from the...

SQL Server 2008 : SQL Server Newbies

splitting up a start time and end time into different dates - what i need to do is find the hours both in the AM and PM for each day. so lets...

Backup Automation - Hello Masters! We have more than 100+ instances across different SQL servers, and currently manually checking whether the backup job failed\success....

Need some help with retrieving correct Device IP Address - All, we got Inventory system that runs in our environment collecting hardware/software information. I like to setup system, that checks...

Truncate statement - Is it possible to rollback a Truncate statement?

Dynamic Excel sheet Read - Hi guys, I am new to SSIS. Thanks to SQLCentral it helped me in solving my daily assignments. I have...

t-sql joins - I have three tables. [u]TableA[/u] id_number date name [u]TableB[/u] id_number notes status [u]TableC[/u] id_number location item Sometimes data may be entered in TableA and TableB. Sometimes data may be entered in TableA and TableC. Sometimes...

backup - Dear all If db recovery model is simple and I take full back up every day. And if my recovery mdel...

having trouble granting role to user - I've submitted these SQL commands with no problem: CREATE ROLE testing; -- create Role "testing" GRANT CREATE TABLE TO testing; -- grant CREATE TABLE...

Extracting Excel files from Multiple subfolders and load them into sql server table basing on city name - Hi, I have a folder which contain's Sub folders(City Names) and in those sub folders I have excel files. I...

SQL Server 2008 : SQL Server 2008 High Availability

Can mirroring be set up with a copy only backup? - I have to initialize mirroring on a large database. Due to red tape/policies etc, if i take an offschedule full backup,...

Cause of Database Mirroring automatic failover - Hi, Today morning our prod database has been failed over from principal to mirror....Please let me know what might be the...

Mirroring HIGH SAFETY failover using FORCE SERVICE - Can someone please explain if there would be data loss when using FORCE SERVICE for database mirroring failover, if the...

SQL Clustering and VMware - My production servers are going back on lease soon. They consist of two physical SQL Server 2008 R2 servers, clustered,...

SQL Server 2008 : SQL Server 2008 Administration

Difference between ALTER TABLE REBUILD and rebuilding clustered index on that table - Hi all, After reading [url=http://rusanu.com/2011/10/20/sql-server-table-columns-under-the-hood/]this article[/url], I was wondering if there's a difference between using ALTER TABLE with the REBUILD option...

Upgradation of sql server - is there any issues to upgrade sql server from 2000 to 2008 ? and what is the better way to upgrade...

MSSQLSERVER Services not start? - Hi, win - 2008 enterprise edtion SQL - SQL Server 2008 SQL SP -service pack 1 windows application log found as below error messages,...

Named Pipes vs TCP - Connection Errors - Hello, A bit of a strange one....and i swear this is a networking issue somewhere....but according to the networks team they...

Automating the SQL Profiler - Hi All, I'm facing an issue while trying to automate the SQL Profiler run via scripts: Requirement: Need to audit few events on...

Dbcc checkdb command - Dear Sir, Here i am having a doubt When we run DBCC CHECKDB command if we found any error we can...

Why SQLCMD? - Days by day i am learning something new in SQL Server. Before learning anything i want to understand why do...

Need to free up Physical memory (RAM) - Hi, There were lots of PAGEIOLATCH_SH waits while running queries, when drilled down I found the statistics were out of date,...

service pack on cluster - We have a sharepoint farm that uses a cluster for database server. The cluster has two nodes, one active the other...

SQL Server Agent jobs running under SA - I have an issue with some of my jobs. They are owned by the same domain account that the SQL...

Programming : Powershell

Write PowerShell Output to SQL Server Table - Heh... First time I've had to admit being a "newbie" in a long time so be gentle with me. I...

SQLServerCentral.com : Anything that is NOT about SQL!

I'm infecting my children :-) - So I have this t-shirt with Spiderman using a lap-top and the caption is Webmaster. The other day my 3 year...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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

SSRS report manager not rendering charts - We have built out a new report server using SQL 2012. After deploying our new and old reports to this...

SSRS List & Blank Parameters - Hi geniuses! Is there a way to have a report with 3 multivalue parameters: AREA, LOCATION and PROJECT, exposing only 1...

Report Showing Missing Images - I have a report that shows badge photo images for staff that is working using an external image source for...

How Indicators works - Hello everyone - I was wondering if anyone can provide some insight on how indicators work. I read about the percentage...

Database Design : Design Ideas and Questions

SQL metadata - Can i use ssrs to get sql metadata? Thanks

Data Warehousing : Integration Services

SSIS package droppping row on import - I have a SSIS package which is meant to import 11331 rows into a database table. In my execution results, its...

SSIS Package Configuration Use - I need advice on how best to use package configurations. I understand the mechanics of creating a package configuration. I...

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 : Analysis Services

process cube thorugh job service account issue - Hi All I am having trouble getting my SQL Agent job to process a cube on SQL Svr 2008. I can run...

SSAS 2012 - LinRegPoint help - I am trying to do some linear regression analysis in my cube so that i can the use this to...

Microsoft Access : Microsoft Access

Access Query ported to SQL - Here is the "inner" part of a query in Access. UPDATE [Vehicle Detail] SET [Vehicle Detail].ReturnDate = #4/29/2013#, [Vehicle Detail].ReturnSource = 'ReturnProcess' WHERE ((([Vehicle...