In this issue

Featured Contents


Featured Script

SQL Storage Compress Compress live data by 73% 
Red Gate's SQL Storage Compress reduces the size of live SQL Server databases, saving you disk space and storage costs. Learn more.
SQL Source Control No source control system needed to evaluate SQL Source Control
The evaluation repository makes it easy to try SQL Source Control. Get started with the 28-day free trial.
SQL Backup Pro Free eBook! Your complete guide to SQL Server Backup and Restore
Be prepared and minimize data loss when disaster strikes. Then try SQL Backup Pro for faster, smaller backups. Download free resources now.

In This Issue

Pushing SQL Alerts to Remote Server using PowerShell 2 and T-SQL

Managing log files in a large enterprise environment can be an overwhelming task. In this article I will show you how to automate some of the work using SQL Alerts and Powershell 2.0 More »

SQL Saturday #154 | St.Louis, MO

Sept 15th, 2012 at the CAIT Campus of Washington University in St.Louis - 5 Jackson Avenue, Clayton, MO 63105 SQL Saturday #154 is the very first SQL Saturday event in the St.Louis area. It is a free one day event for SQL Server professionals and those wanting to learn about SQL Server. We are planning on a 4 track event with 200 attendees. We currently have several sessions aligned towards Database Administration, Application Development, Business Intelligence and professional Development Tracks. We also have several sessions focused on SQL Server 2012! Please visit our event website for more details and free registration. More »

How to Audit Login Changes on a SQL Server

I've been charged with coming up with a quick solution to audit login changes on my SQL Servers. However, there's no budget so I'm going to have to come up with basic scripts and the like to do the work. Is this tip we cover a solution for you to audit login changes. More »

From the SQLServerCentral Blogs - SQL Audit 201 – Creating an Audit Solution

Welcome back, folks! Ok so, now that we’ve covered the basics of creating a SQL Audit and viewing the output, let’s... More »

Editorial - Code Reviews Must Be Brutally Honest

Today we have a guest editorial from Hakim Ali. 

This post was prompted by a code review session I attended recently. The code in question involved T-SQL stored procedures. There were five database developers in the room, looking over different components of an application we had divided up and coded amongst ourselves.

The raison d’être of a code review is to improve your code, to find things that the original coder may have missed and suggest ways of improving them. You do not go into a code review looking for an ego boost via validation that everything you did was right. You do not go into a code review fearing that others may find something wrong with your code. You definitely do not go into code review with a defensive and protective mindset, not open to critiques. And yet that is exactly what I encountered from one participant.

When this participant's code was being looked at, and one or two of us pointed out some things that he could have done in a more efficient manner, he started stonewalling and defending a position that we all (himself included, very likely) knew was wrong. Now I am not suggesting at all that you should not defend your position if you truly believe you are right. But when you know you are wrong and you do not accept it, what do you gain by the exercise? Why waste everybody's time, if you are not willing to be reviewed? Our friend made it clear that he was not going to be receptive to constructive criticism, and the atmosphere in the session almost immediately turned from one of camaraderie to sullen silences. I knew the rest of the session would be wasted time.

I look forward to being shown that I did something wrong, it helps me improve as a developer. A code review is a fantastic opportunity to look into other developers' minds, and to have peers suggest ways for you to improve your work. Why would anybody be resistant to that? I would be vastly disappointed if my code were being reviewed and nobody showed me ways of improving it.

The attitude above is largely from the perspective of the "reviewee". From the reviewer's point of view too, brutal honesty is the way to go. If you hold back as a reviewer because you do not want to hurt somebody's feelings, you have probably just done everybody in the room a disservice. You are a participant in the meeting for your technical abilities, to look over other developers' work and find any errors or inefficiencies. If you fail to do that, you have failed in your task to help develop the best product or service you can put out. I am not advocating that you should look to be mean and give other coders a hard time. Just be honest. Brutally honest.

Egos, feelings, false pride - these really should not come into the code review session. Check them at the door, and focus on one thing only: building the best software that can be built with the resources at hand. If you are lucky, somebody may just show you a way of doing something better, and help you find some coding Zen.

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

» To submit an article, rant or editorial, log in to the Contribution Center

Question of the Day

Today's Question:

In SQL Server 2012 RTM, which of the following are valid index types?

Note that some index types may require additional attributes to be specified; this does not invalidate them for the context of this question. Only index types that include illegal attribute combinations or non existing attributes should be discarded. Also note that this question is not about the exact spelling of the various options.

This is a question with multiple correct answers. Please select 3.

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

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

Yesterday's Question of the Day

CREATE TABLE #tmpTable(Fld1 CHAR(1))
INSERT INTO #tmptable VALUES ('1')

DECLARE @str1 varchar(50)
DECLARE @str2 varchar(50)
DECLARE @str3 varchar(50)
DECLARE @str4 varchar(50)

SET @str1 = 'select count(*) from #tmpTable -- where Fld1 =0'
SET @str2 = 'select count(*) from #tmpTable --' + CHAR(13) + 'where Fld1 =0'
SET @str3 = 'select count(*) from #tmpTable /*' + CHAR(13) + 'where Fld1 =0 */'
SET @str4 = 'select count(*) from #tmpTable --/*' + CHAR(13) + 'where Fld1 =0 --*/'


SELECT 'str1', @str1
SELECT 'str2', @str2
SELECT 'str3', @str3
SELECT 'str4', @str4
DROP TABLE #tmpTable 
What are the results?

Answer: 1,0,1,0

Explanation: The string char(13) will cause a carriage. This creates some interesting effects. The first query executes fine, showing a 1 since the WHERE clause is commented out. The second query has a carriage return, which cancels the comment and the WHERE clause is executed. The third query is executes fine. The fourth query comments out the comment, but since the comment is run first, and it runs returning a 0.

Ref: -- Comment -
/* */ Comment -

» Discuss this question and answer on the forums

SQL Wait Stats Joes 2 Pros

You learn a lot when thrown into a crisis situation, where everyone is looking to you for answers. The system is down, the CEO is pleading to you for answers as the each minute cuts the company’s bottom line. When you are in a hurry, you need some new ideas right away. This book is your helpful tool. When used correctly, you can determine solutions and even related solutions to simple (yet lesser known) areas of SQL Server. Learn what causes SQL tasks to wait or site in queues. Even if your system is not in trouble right now, you can use what you will learn in this book to view the history of wait statistics.

Pick up your copy of this great book from Amazon today.

Featured Script

spwho2 cleaned up script

spwho2 results minus the Background and Sleeping rows.  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

SQL Server job to delete old backups - Hi, I need to create a job that cleans backups and log backups that are older than a month. I do...

SQL Server 2005 : SQL Server 2005 Integration Services

How to create custom schedule for sql server agent job - Hi everybody, this is my first post and I searched for a soluten but unfortunately couldn't find one. I'm in the process...

SQL Server 2005 : T-SQL (SS2K5)

hierachy with miltiple parent - Childid Childname Parentid pid 100 Bingo 200 1 101 Pingo 201 1 102 Zingo 201 1 100 Bingo 201 2 101 Pingo 200 2 102 Zingo 201 2 100 Bingo 201 3 101 Pingo 201 3 102 Zingo 200 3 100 ...

SQL Server 7,2000 : Data Corruption

The header for file"dbname_log.ldf" is not a valid database file header - Please help me with this problem. SQL 7.0 I copied both ldf and mdf file after shutting down the SQLServer because of...

SQL Server 7,2000 : General

SQL entrance exam - Has anyone taken an SQL server entrance exam for Scott Trade? I'm not sure what to study or how to prepare....

SQL Server 2008 : SQL Server 2008 - General

Error: Must declare the scalar variable "@UPDATE". - Hi, 1)How to resolve this problem " Must declare the scalar variable "@UPDATE". myConnection = db.getConnection(); DataSet ds = new DataSet(); SqlDataAdapter myAdaptor = new...

how to add a grand total column to pivottable - I would like to add a grand total column (total number of students) to a pivot table the pivot table...

SQL-The log file for database is full - Hi...guys.... I am getting this error: event id: 17052 Error: 9002, Severity: 17, State: 6 The log file for database is full Is...

Unable to connect to one or more Databases - Hi SQL Masters, I have a situation here regarding my supported two Databases, one of them is ABC Database and the...

code for calling a stored procedure called spDateRange - Code a call to the stored procedure That returns Invoices with an InvoiceDate Between April 10 and april 20 2008...

Summing 2 Dates but only Workdays - Thanks for the help yesterday. Today I am looking to sum 2 dates but only workdays. So if I have...

Does the optimizer handle joining views to views? - I have read at least one post by Grant Fritchey stating that the optimizer can have issues when joining views...

Suggestion on how to implement a custom rollback - Hello Experts, I am trying to come up with a better solution for our intranet web application (running on SQL Server)....

Openrowset issue - Hello, I'm trying to fetch table data from Server B to a temp table in Server A. I didn't get entire...

Blocking application using policy management? - We are starting to dabble with policy management where I work, and I was wondering if anyone would know if...

Summing Columns of Dates - I have 2 columns of dates. What I need to do is to sum or count days in between. I...

how to add # special character in a string @mysql that will be used by exec @mysql - how to add # special character in a string @mysql that will be used by exec @mysql Hello all I have a temporary...

List of all numbers between low and high - What is the best way to get a list of all numbers between low and high numbers? Thanks. Actually >= low,...

Help with SSIS data insertion mystery - Scenario Used SSIS bulk insert to loop through a series of flat files and load them into a database. The...

SQL/BIDS 2008 R2 Error Processing Mining Structure - I receive the following errors when attempting to process a data mining model against Adventure Works DW ... when following the...

SQL Server 2008 : T-SQL (SS2K8)

How to count the total sales prior's 4 weekend if user give the weekend date. - Hi All, I need to count the total sales prior's 4 weekend when user give the weekend date. I have sample data...

Partition Function - What is partition Function and what is the used(need) of partition function?

Update table where there is FOREIGN KEY reference - Want to update t4 table but update fails as there is relationship between tables. Right there is one to many...

Set command to concatteneate strings - I am trying to use a variabe to camcattenate a string I use following: DECLARE @MYVAR Char(1000) = 'INITIAL' if Conditon 1 SET @MYVAR...

How do I to convert varchar datatype to datetime - Does anyone know how to convert varchar datatype to datetime in TSQL?

How do I remove nulls from case statements with date fields? - How do I remove nulls from case statements with date fields . For example in the else clause when blank, it...

0 is equal to zero length string. Can someone explain how this can be? - --I know this has to do with an implicit conversion of the varchar to int, --but why does the zero...

Addition Of Digits - Hi, This was the question ask to one of my friend during an interview. He was ask to perform the addition...

SQL Server 2008 : SQL Server Newbies

Loop Deleted trigger table - Hi, I use SQL Server 2008 R2 I read in this forum, that it's not best practice looping Inserted/Deleted trigger tables,...

SQL Server 2008 : SQL Server 2008 Administration

Question about SQL Server, Memomy, Disk, Configuration. - Hi all, i cant think about an apropieted subject for the topic. Here is the thing. We have a Server with this...

How to schedule a job to run a command once a month? - Our vertex is installed on sql server 2008R2. Our security policy is that the password will expire every 30 days... : Anything that is NOT about SQL!

SQL Jokes & Riddles!!! - What did the one SQL table say to the other SQL table at the bar this past Friday night? :w00t:

Reporting Services : Reporting Services

Can we Add comments on ssrs report by using report serives browers - Hi All, Is it possible to add comments in ssrs report ? My clear requirement is : Users can able to see the...

Data Warehousing : Integration Services

Get Rows Base on package variable then removes rows from source - I have a package where I am retrieving rows from a source table based on the min(YearMonth) field e.g. 201005,...

Data Warehousing : Analysis Services

Question on Cube Design - Hi, I am kind of newbie on OLAP and I have some questions. I have about 6 tables in my OLTP database...