In this issue

Featured Contents


Featured Script

SQL Data Generator How do you generate test data for your database?
SQL Data Generator quickly populates databases with intelligent and meaningful test data. "SQL Data Generator is simple and effective." Michael Gaertner, Quintech. 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.
Deployment Manager How to automate your .NET and SQL Server deployments
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial.

In This Issue

Updating the TCP Port on a SQL Server Cluster Node

When installing 2 SQL Server instances on a Windows Cluster, the installation program requires a Named Instance during the installation of the second node and a dynamic TCP port is created. How do you update the TCP port after the installation in a clustered environment for the named instance? More »

What is SSIS? Level 1 of the Stairway to Integration Services

Learn about the very basics of what Integration Services is and how it's used in SQL Server. More »

SSRS In a Flash - Level 1 in the Stairway to Reporting Services

Learn the basics of Reporting Services, what it is, and what it can do from you. From MVP Jessica Moss, we have a new series that can help you get started with this part of SQL Server. More »

From the SQLServerCentral Blogs - Starting with Azure

I’ve been meaning to do this, and I finally had the chance recently to start working with Azure. Red Gate... More »

Editorial - Memorial Day 2013

Today is the Memorial Day holiday in the US. I'm sure many of you have the day off, and I hope that you enjoy it, while also remembering those that have given their lives in military service. 

For those of you outside of the US, I have some humorous (I hope) bloopers you can enjoy.

Steve Jones

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

You come into work one morning and find an SSMS query window on your computer open, with the following text in the Messages pane:

Msg 2001, Level 1, State 1, Procedure OpenThePodBayDoors, Line 1

I'm sorry, Dave. I'm afraid I can't do that.

What is the likely cause of ths message? 

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

This question is worth 1 point in this category: Error Messages. 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

You are trying to execute the following code:

DECLARE @MoreColumns bit;
SET @MoreColumns = 1

IF @MoreColumns = 0
    id int, 
    name varchar(50) 
    id int, 
    name varchar(50), 
    Description varchar(8000), 
    USER nvarchar(200) 

What would be the outcome?

Answer: Code fails because object with name #Table already exists in database

Explanation: SQL Server evaluates object references during parse time. Flow-control logic is not applied at this stage. The parser adds a reference for the temporary object #Table when parsing the "TRUE" part of the IF statement. It generates an error when it finds another attempt to create an object with the same name in "ELSE" part of IF.

This error stops parsing immediately. The parser does not have an indication of which duplicate object declaration should be used in further evaluation, so the parser never reaches the keyword USER and does not report the syntax error.

» Discuss this question and answer on the forums

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.

Featured Script

Function to convert UserAccountControl number to details text

Function that converts AD UserAccountControl number to details text 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

Maint job fails with "error cannot drop statistics _W_Sys_xxx.." ......... - Hello, One of our weekly maintenance job which was running fine failed last 2-3 times. In the log we see the...

Truncate log for Mirrored Database - Hello, I am running Mirroring on my Database. Log file for my database growing day by day on Principal server. Recovery...

SQL Server 2005 : Development

SELECT FROM OpenXML not returning any data - I have the following challenge. I would like to parse XML data into a relational table. <NSTRoot xmlns:xsi="" xmlns:xsd="" xmlns=""> <Header> <General> ...

SQL Server 2005 : SQL Server 2005 General Discussion

query xml - i have a large xml file one of my queries looks like: SELECT p.value('(PodactID)[1]', 'nvarchar(max)'), q.value('(CustomerID)[1]', 'nvarchar(max)'), r.value('(IsPaid)[1]', 'int'), r.value('(Payment)[1]', 'real'), r.value('(PaymentDate)[1]', 'nvarchar(max)') FROM XmlTable CROSS APPLY...

SQL Server 2005 : SQL Server 2005 Integration Services

Stored Proc with temp table issue. - Hi everyone. I understand that this topic was covered several times on this forum, but I can't seem to find ones...

Derived Column to handled mulitple conditions in CASE statement? - HI, I've got a case statement that works, but because I have to do it in SSIS I am at...

SSIS by using SP with multiple result sets - Hello, We have an SP which is developed to give us two result sets based on selection. I want to...

SQL Server 2005 : T-SQL (SS2K5)

OPENROWSET() Intermittently Fails to Read Excel File - I've had this problem a number of times: every so often, I receive errors like this when trying to query...

SQL Server 7,2000 : Backups

Can't access data from database restored from backup if not sysadmin - Hi everybody! I need some help please: - I'm creating a .bak file from a remote sql server 2005 express database. -...

SQL Server 2008 : SQL Server 2008 - General

Curious Case of .. Syntax Error. Or may be not? - Not sure- to laugh or cry ?:-) I created small SP to rebuild/reorganize indexes on sql server (2008 R2) DBs (only...

Fragmentation accuracy? 98% fragmented?!! - Hi guys, Today/2am this morning I received my first call out (yay!) for a terrible server I hadn't heard of before....

Is there any book in market on major/common SQL Server 2005/2008 errors and its possible solutions ? - Hello, Is there any book in market on major/common SQL Server 2005/2008 errors and it's possible solutions ? Just curious to know. Thanks, San.

Decimal(18,0) or int? - Hi I have a column of data type decimal(18,0) can i change it to Int for the above case whether...

Need to get list of sprocs for a list of tables - Hello, I need to create a report with all jobs that run to populate the tables that failed and then...

SQL Server can't connect to database - I downloaded file SQLEXPRADV_x86_ENU.exe (version 10.00.1600.22), and used it to install SQL Server 2008 Express with Advanced Services, which I...

T-LOG ISSUE - Dear, Currently my t-log size is 3 GB and 87% is available free space. I take regular log backup and full...

snapshot agent is not running - Hi, Friends I am working on sql server 2008 r2 and I want to replicate data by using snapshot replication, when...

SQL Server 2008 : T-SQL (SS2K8)

Why would you ever use EXECUTE AS CALLER? - Since it is the default, I don't understand what the point of this option would be.

Which is faster, Sub Query or Join? and Why ? - Hi Friends, I'm new to SQL. And I wand to know Which is faster, Sub Query or Join? and Why ? Thanks in...

Automatically inserts into the database on a specific day - Hello everyone, I have database with about 20 tables,and some of them must be populate automatically.. So , on specific day that...

Confused selecting Info From Two DB - i have two database AgencyDB(primary DB) and AgencyBackupDB(Deleted Info from AgencyDB by user) and i have this three table in...

Trigger for truncate - Hi, Can we simulate a truncate trigger? what i need is from which host, who,when truncated a table even if he/she...

SQL Server 2008 : SQL Server Newbies

Query Sum - Hi all, hope in your help. This is my table: [code] +----+--------+--------+ | id | field1 | field2 | +----+--------+--------+ | 1 | A1 | 7 | | 2 | B1 | 9 | | 3 | C1...

SQL Server 2008 : SQL Server 2008 Administration

Where do views and indexed views reside? In memory or tempdb? - Do materialized views and indexed views exist in tempdb or the buffer, correct? They could be in the buffer if...

Having backup problems, and unable to change service accounts... - Two problems, possibly related, both giving me heartburn as I'm going away for 2 weeks and don't want to leave...

Mirroring Vs Log shipping - I had Implemented Mirroring and Log shipping but server team Exact utilization of network bandwidth. Log shipping can scheduled on time...

tempdb getting full - My tempdb is getting full within 2 hours , there are some executing SP that are using temporary tables, cursors and...

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... : Anything that is NOT about SQL!

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

Data Warehousing : Integration Services

SSISDB Project Deployment Model - I have an SSIS project solution which I have deployed from our development server to a SSISB Catalog on our...

Data Warehousing : Strategies and Ideas

Customer Journey - Hello, Has anybody had any experience with representing the customer journey (funnel/loyalty levels) as part of a dimensional model? Would...

Data Warehousing : Analysis Services

MDX ParallelPeriod Function - HELP - Hi to all. I have a calculated measure in one of my cubes. This is named DELTA SALES AMOUNT It calculates [b]Parallelperiod[/b] about...

Recommend a good mdx primer - I've been trying to find a good primer for mdx that I can sit down and learn the basics but...