In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
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.
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.
 
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.

In This Issue

How to Change the SQL Server Instance Name after Renaming the Windows Host

This article shows how to change a SQL Server instance name after changing the Windows machine name. More »


Getting Started Testing Databases with tSQLt

There are several frameworks for assisting with the testing of SQL Server databases, but tSQLt is popular because it is written in TSQL and is simple for a database developer to set up and use. It doesn't get in the way. Rob Sheldon shows you how to get started. More »


From the SQLServerCentral Blogs - Dragging a name from the object explorer to a query window

In the same vein as Steve Jones’ series on Customizing SSMS this is a fairly basic trick, but one that... More »


From the SQLServerCentral Blogs - The Tale of One Tabular Model and Excel 2013 with Multiple Power View Reports

SQL Server 2012 Reporting Services introduced Power View, which was initially available only in SharePoint. This limited the use of... More »


Editorial - Honeywords in SQL Server

As  we become more and more security conscious, it becomes more important not only to configure systems for better security, but also to add more monitoring and auditing to detect when problems occur. We know that at some point someone will attempt to hack our systems. Many of us have auditing set up to detect failed logins, but is that good enough?

If a hacker manages to gain access to your password hashes, and it's not a stretch these days to think that they might, wouldn't you like to know if they manage to find the plain text that corresponds to the hash? There's an idea that systems could be written to store multiple passwords for user accounts, but only one of which is valid. A separate system detects attempts to log in with the false passwords and alerts administrators to a hacking attempt.

This is an interesting idea, and while it won't solve all our problems, it will solve some. If a brute force attack occurs on an account, and multiple passwords are being tried, all of which are known to be false (the honeyword passwords), administrators can be notified, and warnings passed on to users. It doesn't help if the hacker chooses the correct password to enter first, but with enough honeywords, you reduce the chances that they will.

I don't know that I'd like to see this for SQL Server, but I certainly would like to see additional security features. Two factor authentication would be nice, perhaps even some sort of  approval process enabled that required multiple approvals for some changes. The latter would help us prevent the cowboy DBA from making changes without anyone else being aware of them.

Detection of breaches, using something like honeywords, provide another layer of security. They don't prevent hacks, but they can help us deal with them.

» 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 happen on executing the CREATE index statement for this table's definition in SQL Server 2012?

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

This question is worth 2 points in this category: ColumnStore Index. 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

I have a table dbo.MyTable with contents like this:

I then run this query:

SELECT CASE
         WHEN ValueCol < -10 THEN 'Very negative'
         WHEN ValueCol < 0   THEN 'Bit negative'
         WHEN ValueCol = 0   THEN 'Zero'
         WHEN ValueCol <= 10 THEN 'Bit positive'
                             ELSE 'Very positive'
       END AS Category
FROM   dbo.MyTable
WHERE  KeyCol = 5;

What will happen?

Answer: I get one row of output, with category "Bit negative"

Explanation: A CASE expression will always evaluate the WHEN clauses from top to bottom. As soon as one evaluates to True, the corresponding ELSE is returned. The remaining WHEN ... THEN clauses and the ELSE (if present) are irrelevant (but see note!).

A CASE expression will always return the THEN for the *first* WHEN (reading left-to-right and top-to-bottom) that evaluates to True.

The query only selects row with KeyCol = 5, which has ValueCol = -2. The first WHEN is false (-2 is not < -10); the second is true (-2 is < 0), so "Bit negative" is returned and the rest of the CASE is irrelevant.

Note: Officially, clauses following the first WHEN that is true should not be evaluated. In practice, this not always happens. Other WHEN, THEN and ELSE clauses may be evaluated when aggregates are involved. You should only rely on clauses evaluating in order when no aggregates are involved.

Reference: CASE - http://msdn.microsoft.com/en-us/library/ms181765.aspx
CASE won't always evaluate in order - https://connect.microsoft.com/SQLServer/feedback/details/690017/case-coalesce-wont-always-evaluate-in-textual-order

» Discuss this question and answer on the forums

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.


Featured Script

Custom Maintenance Plans - the T-SQL way

The non-SSIS/GUI method I use to deploy standardized Database Maintenance Plans. 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

shrink fails with error - File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty - This is an fyi (no response necessary). I got this error and researched it and found no good answer for how...

SQL Server 2005 : Backups

Disaster recovery - Hi, Can anyone please help me...I am new in SQL Server. I have scheduled the database backup--- full backup-- everyday morning differntial backup--every 2...

Differential Sizes Increase After Index Rebuild - Hi Folks I am trying to understand why my differential backups are almost as big as my full backups after rebuilding the...

SQL Server 2005 : Business Intelligence

Cube question - Swapping Measure Depending on Dimension being used? - Hi, I am using SSAS 2012. I have been asked to see if it is possible to change the measure depending...

SSIS to Load selected CSV File columns into SQL Server 2008 database table - Hi, The source file format in CSV, lets assume it has following data id name marks 1 a 100 2 b 90 I have created...

Best Practice for loading DW tables from live source with timestamp/Rowversion column to identify changes - I want to build a DW and I need to identify records that have changed, and luckily each table in...

SSIS-Data in multiple .csv files to columns in same csv file - Hi Everyone I have the below problem,could anyone please suggest me a solution. file 1 has below data 1.csv A B C 2.csv E F G the data in the output...

SQL Server 2005 : Data Corruption

Sharepoint database 'docs' corruption ... what if? - Long story short :D We inherited a sharepoint database on SQL2k with corruption dating back 2 years (so no usable backups)....

SQL Server 2005 : Development

SSIS package run error - Hi All I am trying to run a package which reads data from Oracle using Select Statement. used "Oracle provider for...

I need to create an XML file populated by fields data(eg: SQL Server DB table) - I have the following XMLSchema template: <?xml version="1.0" encoding="UTF-8"?> <DataMarketsFeed xmlns="http://www.test.co.uk/XMLSchema/DataMarketsFeed-v1-2" xmlns:cmn="http://www.test.co.uk/XMLSchema/DataFeedCommon-v1-2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <DataFeedHeader xmlns="http://www.test.co.uk/XMLSchema/DataFeedCommon-v1-2"> <FeedTargetSchemaVe

SQL Server 2005 : SQL Server 2005 General Discussion

Report executing but not finding a stored procedure during Verify Database - Please disregard this question. (Mods, delete?) I misinterpreted my results, it's definitely on the Crystal side. My apologies.

Part time Data Entry Clerk from home ! - :-) Position Type: Part time Home based data entry clerk jobs. Details: Earn money from home simply posting ad on free classifieds...

PROBLEMS WHEN UPDATING LINKED SERVER WITH MSSQL DATABASE - I have a linked server (mysql) and I want to update some colums with the information on MSSQL information now I...

OLE DB provider "SQLNCLI" for linked server " " returned message "No transaction is active." - I have linked a local SQL 2005 Standard Server 32-bit Unclustered to a remote SQL Server 2005 Standard 64-bit Clustered...

SQL Server 2005 : SQL Server 2005 Integration Services

Import Excel 2010 file, and return an wrong row count - Hi,I am trying to load a excel 2010 file to sqlserver2005, I used visual studio 2005 and excel datasource, the...

SSIS Integration Task - I have used SSIS a while ago where my requirement was to create a new table out of the excel...

configuring ssis pakage for environmental variable - I want proper steps to configure package for environmental variable and deploy that package and run it on another machine...

SQL Server 7,2000 : Administration

SQL VDI issue - Hi , Am using SQL server 2005 version in my project.. We have configured the backups using the Data protector tool. For...

SQL Server 7,2000 : T-SQL

Create FInancial week Dynamic based on parameter - Hi all, I need to generate the financial week but in my case the starting month of the financial week...

SQL Server 2008 : SQL Server 2008 - General

how to identify last mod datetime for sp? - Hello - What's the easiest way to identify the last modified datetime of a stored procedure through SSMS?

sql and msbi basic concept for beginners...plz share your ideas about this two topics - hi frndz plz share ur idea regarding year syntax in sql how to add this one

Execution Plan - when i'm looking at stored procedures i add the following to the top of the code dbcc freeproccache set statistics io on set...

Import Excel File to SQL Server - I am having trouble while importing excel file to SQL Server. Excel File: ColumnA -------- b2 2 a2a 2aa When I import...it comes as ColumnA -------- b2 a2a 2aa Just number "2" doesn't comes. I...

Red Gate Sql Monitor Alert is downgraded but still shows as HIGH - Hi I have been reorganising/rebuilding some indexes due to high fragmentation due to an alert generated by Red Gates sqlmonitor. I have...

variables and Escape quotes - I am trying to create a variable like the following in a WHERE clause DECLARE @Test AS VARCHAR(90) SET @Test = '2003 OR...

Access Provisioning Tools - Hi Folks, Can you suggest some of the best tools available in the market which offers the easiest way to provide...

Adding text to Rank - Hi All, Please i need help to complete this query, what it does is that it ranks some records which works...

add a new column with its columnName and aliasName - hi i want to create a new column (through a vb.net app) for a table . the problem is that i...

Management DataWarehouse - Hello All, Is it advisable to use management data warehouse to track the database growth? The steps for configuring seem to be...

SSIS export to Tab Delimited Text file - Hello, I was asked to make an SSIS package that extracts Supplier data from the source db and export the data...

Get who eliminated a default trace. - Hello, Is there any way that i can find who have eliminated a default trace from a machine? The point is...

execute ssis package in sql server 2008 r2 error - Hi, When I executed my SSIS package in BIDS successfully but when I try to schedule to execute my SSIS package...

Alter Stored Procedure to only update changed values - Hi, My Problem is that Data-Updates are done via an .asp-Application which uses a Stored Procedure to update Records in our...

Chinese Language and Decoding. - Dear Gurus, Your suggestion is required. Our team wants to override “windows regional language Settings” for Chinese Language by applying encoding...

SQL Server 2008 Audit - Hi Guys, I wish to audit all the activities performed by users having sysadmin role. Is there any straight foward way to...

Running DTS packages on 2008 R2 - Hope someone can help me on this one We have a 2008 R2 cluster which failed over recently, a number of...

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005 - This lovely error happens frequently when I need to add articles to my transactional replication setup. After making the changes...

Changing a user defined data type definition - I recently had cause to change the definition of a user defined data type and was I suppose intrigued by...

Replication subsystems failed to load - I am running SQL 2008 Enterprize Edition with SP1 on Windows 2008. I am trying to set up replication. I...

SQL Server 2008 : T-SQL (SS2K8)

Implicit Update in TRY..CATCH can it become uncommitable - Hi All, My question is could this statement cause an uncommitable transaction and subsequent leave transaction in an open state where...

Changing a varchar column with money values in, into a decimal data type - Ive been given a data set to work with with a column for money. This column is basically a mess but...

Unknown Syntax for me ;With - Dear all, I got following code in one procedure and did not get why its use and what is it for...

Count Number Of Investors in fund - Hi, Please help me with this : I need to take count of investors invested and having balance in Funds:1,2,3,4,5,6,7,8 by date...

Limit count of user work with my DB (by C#) - Hi I have sql server DB and C# Application. How can I restricted count of user who use from my DB? How can...

convert varchar to date - I would like to convert a varchar column to datetime. The varchar column are supposed are be like a date : 2013-05-12 But...

need help with trigger - I have a strange requirement. This is a code issue but i am looking for a work around from daatabase...

Extracting files from "binary" stored in TEXT datatype - I've been asked to look into extracting files (mostly jpg or pdf) from a database where the files are stored...

SQL Server 2008 : Working with Oracle

Installing Oracle 11g Client in a sql server 2008R2 active/passive Clustered Environment - I'm trying to get a step-by-step approach to installing Oracle 11g Client in a sql server 2008r2 Active/Passive Clustered Environment....

SQL Server 2008 : SQL Server Newbies

What would you do? - Virtualisation again, sorry! I have done a search but this is more about how to handle a particular issue at...

Copy table+values from one server to another - This might be the dumbest question ever, but I really don't know/remember how this is done. Let's say you've got 2...

Understanding Errors - I have this sqlquery that I am writing: [code="vb"]Private Sub Command1_Click () Dim Irepsonse as Integer Iresponse = msgbox("press a button", vbabortretryignore, "Chapter 4") Select...

mdf file growing - Hi, I am new in SQL Server. I have a database whose size is almost 25 GB, there log size 18...

SQL vs LINQ - Is SQL becoming outdated soon ??? AS new LINQ is all around us.... Do we have to learn LINQ for future...

Syntax Check - When I am typing in a new sqlquery how can I turn on the syntax check option or whatever it...

linked tables - I have a SQL server express that I'm using to learn how SQL server works. Up till this point I've...

SQL Server 2008 : Security (SS2K8)

When to use Transparent Data Encryption (TDE)? - My company has been getting a lot of requests from our clients to put into place more stringent security practices....

SQL Server 2008 : SQL Server 2008 High Availability

Database state (Principal, Synchronized - I have four databases (size between 20MB to 1300MB) which has (Principal, Synchronized) next to there name And on the...

SQL Server 2008 : SQL Server 2008 Administration

High Memory utilization alerts in SQL Server 2008 - Hi, We are getting alerts from our monitoring tool Idera SQL Diagnostic manager ( which is use as monitoring and notification tool...

SQL Profiler sp_trace_setfilter parameters - Hello, I am trying to exclude some users from being captured from a trace. If the traceid I have is 2 I...

Refresh tables from production to Dev - Hi, I have a requirement to refresh some tables in dev from prod...and we need to do this every week...so i...

Resource Governor - how to see what workload group current connections are in? - We just enabled resource governor and monitoring it via perfmon and the DMVs. Through the DMVs, I can see active...

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. - I am using MSQL 2008 R2. I am getting the below error "The instance of the SQL Server Database Engine...

reduce impact performance impact on the OLTP SQL database - Hi, Our client occasionally needs to load very large transaction files into their OLTP SQL database during periods of heavy “live”...

optimize the schema definition - 1. How would i optimize the following schema definition? CREATE TABLE dbo.Orders ( RowID INT IDENTITY NOT NULL, OrderID INT PRIMARY KEY NONCLUSTERED, OrderTypeNVARCHAR(20)...

deploying changes to a live database - Hi, What are some major risks for deploying changes to a live database, and how would we mitigate them? a. Stored procedure changes? b. Indexing...

send me som sample type of tickets - generally as a DBA what type of tickets we will get....? send some samples

Need advice of hardware configuration of SQL server in production environment - Dear All, We are having a SQL server which is being used through out day and night from local and outside...

SQL Server has encountered 1 occurrence of I/O Requests taking longer than 15 seconds - I am running SQL Server 2008 R2 SP2 x64 and we just increased the RAM to 32GB (against my wishes)...

MAXDOP settings for OLAP - Hello, Does anyone have any idea what is the better way to set MAXOP settings for OLAP system. Here is my...

TempDB full - if Temp Db full which databases will go Offline?

How to copy .bak files on an external drive which is not part of domain but requires authentication? - We are looking for a solution to copy .bak files on external network drive which does not allow access without...

SQl Agent job when query fails to return result - Hello, I am trying to put in place a job which will email me when a query fails to return results:...

Export table in to txt - dear Gurus, I want to export data in table to txt SELECT [StatBeginTime] ,[MDASum] ,[BureauName] ,[MDA] FROM [kpidb].[dbo].[t_MDA_1] the result : 2013-05-13 00:14:00.000 290 bjm 261651 2013-05-13 00:29:00.000 226 bjm 203767 2013-05-13...

Moving from Standalone to clustered SQL - Hi - We have a standalone SQL box that we need to migrate all the DBs to a new clusted environment...

Does BACKUP use the transaction log? - Hi, I noticed several autogrowth events for the transaction log file of a database shortly after its backup job started. I've...

How can I kill ad-hoc or long time running queries, safely? - Ok, Need the final push on this ... I do have a requirement at work that I need to control or kill...

Can't Kill SPID “Transaction Rollback in Progress” - I'm running into frequent Blocking on a Development Server. I have been correctly the code or making recommendations to prevent this...

Programming : Connecting

Connecting to SQL Server from a Visual Studio 2012 Express Class - Can anyone tell me how to create a connection to SQL Server from a class created in VB in Visual...

SQLServerCentral.com : Anything that is NOT about SQL!

LINQ vs SQL - Is SQL becoming outdated soon ??? AS new LINQ is all around us....

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 2012 - Export to Excel - We just upgraded SSRS 2005 to SSRS 2012. We have reports that contain a page break on grouping. Since the...

Help with setting LinkTarget=_blank to open URLs in new window - I want to change links on one report to open in a new window. I have read enough to know...

I would like to have an SSRS report export data to Excel every two hours - Hello, I would like to have an SSRS report export data to Excel every two hours and add the data to...

Report Manager DSN Problem - Hi I have created a DSN (using sybse db) and develop report in SSRS 2008R2. Reports working fine in BIDS. Now...

Permission by department manager from single report to view. - Hello, We are using SSRS 2005 and I have a report I want to give access to 5 different managers the...

Database Design : Disaster Recovery

AppAssure - Hello, I am wondering if anyone has experience with AppAssure and disaster recovery of SQL Server databases? I have googled (a...

Data Warehousing : Integration Services

SSIS foreach loop timeout error - I have a ssis package with foreach loop. The connection times out after some 50 files for processed when the loop...

Dynamic Connections - Hello All, I have a package that downloads a lot of csv files and transfers their contents into a database. For...

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

MDX 101 Question - Anyone know why the following doesnt work? SELECT { [DimX].[DimId].Members, [DimX].[DimId].currentmember.properties("value" ) } ON ROWS, [Measures].[Mea_1] ON COLUMNS FROM Measures This keeps throwing the following exception: Query (2, 2)...