In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Doc Hate explaining your database in meetings?
SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.
 
SQL Backup Hosted 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 Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.

In This Issue

Email Formatted HTML Table with T-SQL

Create a professional looking HTML table from T-SQL, not SSRS. More »


Avoiding Parameter Sniffing in SQL Server

Parameter sniffing is when SQL Server compiles a stored procedure’s execution plan with the first parameter that has been used and then uses this plan for subsequent executions regardless of the parameters.  More »


From the SQLServerCentral Blogs - Azure First

Microsoft has been pretty clear about their commitment to the entire Azure infrastructure. The updates to Azure come out on... More »


From the SQLServerCentral Blogs - Natural Born Killers (The series) – Day 1 Statistics

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


Editorial - Lost in Space

Today's editorial was originally published on Sept 2, 2008. It is being re-run as Steve is traveling today and out of the office.

Well perhaps not lost in space, but according to this article, a number of US airports report over 600,000 laptops lost a year. Over 10,000 are lost each week at the 36 largest airports. That's a lot of bits floating out there in the world. There's some dispute as to these numbers in another article, so it's hard to know who's correct. I tend to think these numbers might be high.

In any case, what might be even more amazing is that 65% of these laptops are not reclaimed. What's scary is that 53% of people surveyed said confidential company information was on their laptop and 65% said no effort was taken to secure their data. I found this on Bruce Schneier's blog, and he sees it as a huge dollar loss for the country if the numbers are correct.

My wife travels quite a bit, 30-40,000 miles a year, and she's not surprised by these numbers. She guesses that the main problem is that there is no good way to match a lost laptop with a traveler. Unless you lose it at your home airport, with a lack of staff and the time it might take for something to get your lost and found, it's likely you would never be able to search for it.

And how long would you search? After how many days would you just move on and file a claim and replace the laptop? I tend to carry my important data on a USB key (and likely will upload to some service for future travel), so I'd probably spend whatever time I had in the airport, or maybe a day here in Denver, but after that I'd be ordering a replacement and moving on.

Information has a tremendous amount of value, but to many of us, the information also has a shelf life. We might move on quickly and just accept the losses as part of doing business. I understand that and agree with it for the most part.

However I think we should all have some sort of encryption and protection for our data. You never know when you might have some letter to a bank you drafted with your account information, or something else. For most thieves, I'd guess that an encrypted laptop isn't worth dealing with. They'd wipe it and move on.

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

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]

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

This question is worth 1 point in this category: Locking. 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 would the following return?
create table #Employee
(
PositionId int,
Birthday datetime
)

insert into #Employee
select 0, '1995-01-01'
union all
select 1, '1983-08-01'
union all
select 2, '1948-07-31'
union all
select 3, '1932-02-29'
union all
select 4, '1980-06-15'

--Query 1
SELECT BirthMonth = CHOOSE(Month(Birthday), 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')
 FROM #Employee

Answer: January, August, July, February, June

Explanation: The correct answer is: January, August, July, February, June. If PositionId 3 had a date of 1932-02-30 instead of 1932-02-29, then we would have received an out-of-range issue on the insert. NULL, August, July, February, June are not correct because we are going based off of the integer on the month, instead of the PositionId integer.

Ref: http://msdn.microsoft.com/en-us/library/hh213019.aspx

» Discuss this question and answer on the forums

SQL Server Execution Plans

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!


Featured Script

SQL Server Dynamic XML Tag

Extract data from XML file from dynamic tag. in this below e.g we can add / remove Escalations tag from xml file. and depends on that result will display. 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

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

Backups and Integration Packages - Hi , We run a setup which has SSIS 2008 and SQl 2008 R2 . We run integration packages every night at...

Why SQL Jobs history getting removed automatically in SQL Server 2005? - Why SQL jobs history getting removed automatically in SQL Server 2005 even not configured any user process to remove it?...

Database Engine becomes unresponsive - Hi All, Running MSSQL Server 2005 Enterprise Edition. Recently there's been an upgrade to an application and ever since we are seeing...

Step fails, but job reports success. - I have a job that has multiple steps. Each step is set to 'Go to next step' on success or...

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 : Business Intelligence

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

Update record if master table record exist - I want to update the PO table with the BuyerName if there is a Buyer record exist. In below example , it...

SQL Server 2005 : SQL Server Newbies

Newbie needs help - Hi, Newbie here would like some help if possible. I’m trying to write a sql statement to extract “Previous” and “Current...

SQL Server 7,2000 : T-SQL

Select statement to reduce precision? - Hello all, I'm sure there's a simple answer to this but it's escaping me. How can I reduce the precision of...

SQL Server 2008 : SQL Server 2008 - General

How will work with(nolock) option in select queries - Hi, We have few transaction tables having more than 20 million rows and using those table when we select tables are...

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

Need column name in Status column - Hi All, I need to write a code in SQL Server 2008 in which I have 4 columns(First Name (2 columns)...

Need column name if condition fails - Hi All, I need to write a code in SQL Server 2008 in which I have 4 columns(First Name (2 columns)...

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

SSIS Using XML Source and max size limit of nvarchar(4000) - I have xml files that I'm trying to import into SQL Server using SSIS XMLSource and the xml file has...

Tempdb - Hi I need some clarification on this topic. what is tempdb mdf files used for vs ldf files used for? I understand...

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

EMERGENCY MODE - HI ALL, I am experimenting with one of my test databases . I intentionally set the database to emergency mode.But now...

Leave - i use this syntax in sql server when t.timein is null and l.date is not null then u.description and make join...

Restore Database if Job fails - Hi Is there a way of starting a database restore if an agent job fails? Thanks

SQL Server 2008 view Query - Kindly help on sql query to create a view that merges data from two tables with same columns but second...

log shipping version. - Hello All, Please let me know is it possible to create log shipping between two instances with different sql server version. Regards

Memory Pressure Errors - Hi, Our SQL server services got restarted last night due to memory pressure. Looking forward for your help in diagnosing the...

ROLES - To run sp's what type of role we assign to user?

transport encoding - Hi, I need to implement transport encoding. if anybody worked on or working please provide me guidance. Thanks, Sreenivasa Chary.T

user self services - Hi, I need to implement user self services for administrative tasks. if anybody worked on or working please provide me guidance. Thanks, Sreenivasa Chary.T

integration of key management - Hi, i need to work on integration of key management.As per my knowledge we need use EKM and need to manage...

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

pivot query - hi all, I have a table like below: ID LOCATION STATUS MONTH MEASURE 1 PARIS OPEN JAN 25 2 PARIS CLOSED JAN...

Different plan for a query In APP and SQL - Hi I execute a query from web App and I get the select statement from sql profiler. I run exactly the statement...

merge case when - i have this syntax case when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and spendtime is not null then 'Late' else '' end remarks case when (540-...

Table Partition - Hi All, I have partitioned five tables with same date range for all tables(with single partition function and single partition scheme)...

Trying to understand this SQL Query - Hi. I am fairly new to SQL Server and have come across this SQL Server Query at work and I am...

Database Mail - Could not connect (no such host)! - Hi all, Tried to find a fix on here for my issue but everything I've found re the above problem me...

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

How to find dependencies in dynamic sql queries - I think it is far fetched because after searching the internet for days I found nothing. I want to find out...

.ldf deleted and now cant attach database - Hi All, I had deleted my .ldf as it was getting far too large! and then when i went back to...

SQL Server 2008 : T-SQL (SS2K8)

How can I display a block value count of 8 repeating - Hi All Thanks in advance for any advice. I need to print address data in blocks of 8 for the labels we...

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

PIVOT on VIEW for "special data" - Hi Guys, I'm in need of a solution that takes data from a primary table and also values from a separate...

Why is this Query Taking a long time? - [size="4"]This query seems to take forever. Is there a way I can speed it up?[/size] SELECT cat.Category1 , cat.Category2 , cat.Category3 , tblItem.GPItemNumber...

combining multiple rows into one - Hello all, I have a table that stores pictures for my users but now i need to get multiple pictures into...

smart first name matching in TSQL - Hello experts, I looking for script (actually for data) for smart first name conversion. I.e. if user enter William or Billy then...

SQL Server 2008 : SQL Server Newbies

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

What is Windows System Commands in SQL Server? - What are Windows System Commands in SQL Server? i got this jargoan from msdn website http://msdn.microsoft.com/en-us/library/ms174187.aspx. I tried googling it but...

Creating an import from sql server - Hi All, Below is the automation i need to do: There is a table which has entries coming to it every day....

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

how to copy for a file in sub directories basing on the file name in ssis - How to copy a particular file from sub folders basing on the file name. I have multiple sub folders and...

Move a 2008 database to a new machine on 2008R2 - I was supposed to be migrating some 2008 (SP3) databases from old physical machines to new virtual machines. I was...

Trigger to set a value for a column - I have a column that has to be set with a certain value as A,B,C or D when a certain...

ldf file size - Dear All I have a db for which recovory model is Full. And i am taking only full back up. .Ldf...

UNION with multiple CTEs and summing data - I've been trying to get this to work for days. I have two queries with the results I want. I...

SQL Server 2008 : SQL Server 2008 High Availability

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

Removed Mirroring but Witness did not delete it's record - I used SSMS to remove mirroring but the row in the view on witness server didn't get deleted. Now I...

SQL 2008 - Remove cluster node - Apologies if this is a daft question (never a good start!) Had a thought earlier, we have a very simple active...

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

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

Detect missing db FULL backup and backup that db - I have a problem I am trying to solve. I would like to run a query of some sort that...

uninstall service pack - Any one knows how to uninstall a service pack of SQL server 2008 Sp3? Thanks

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

How do you deal with Sql Service Pack Upgrades....? - Years ago we installed a sql server service pack on a non-clustered server and the results were usually fine. Now...

IP Address Doesnt turn from offline to online - :unsure: Hi I got a problem, I think it is a little bit weird kind of problem, I haven't find anything...

Career : Certification

any recommendations for 70-450? - Hi, I just passed the 70-432 test and am going to start to studying for the 70-450 test to complete my...

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!

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

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

Reports - Hi All, We have a requirement for developing a report. Datacenter, Date, Servername, Database Name and DB size..... these all things should...

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

"HTTP 400: Bad Request" - Reporting Services 2005 - Has anyone ran into this error yet while testing Report Manager? "The request failed with HTTP status 400: Bad Request." I can...

Reporting Services : Reporting Services 2005 Administration

SSRS Error: Failure sending mail: The report server has encountered a configuration error. Mail will not be resent - Hi All, I have been trying to send ssrs report to users via email and i followed all the rules like...

Reporting Services : Reporting Services 2005 Development

Viewing Reports in Report Server - In a SQL Sever 2005 environment I have SSRS install & running along with IIS but when trying to view my...

Data Warehousing : Integration Services

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

SQL agent job fails when accessing the csv file - Hi All, I have a csv file in a shared folder in a server. My package is running fine in the...

Data Warehousing : Analysis Services

Slow pivottable queries thru Citrix (SSAS 2008) - we have a citrix server hosting excel 2010 which queries a SQL 2008 SSAS server. when running a pivottable query...

Microsoft Access : Microsoft Access

call a SQL Server User Defined Function from Access query - Hi, can I call a UDF (Scalar-valued function) from Access2007 in a query window? I know it works with Stored Procedures,...

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

Article Discussions by Author : Discuss content posted by Press Release

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