In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor Get alerts within 15 seconds of SQL Server issues
SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.
 
SQL Data Compare Save time when comparing and synchronizing database contents
"It has also streamlined our daily update process and cut back literally a good solid hour per day." George Pantela, GPAnalysis.com. Download a free trial of SQL Data Compare now.
 
SQL DBA Bundle ‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now.

In This Issue

The Job Posting - Do I really have to be the SQL God?

Looking for a job in the SQL Server industry can be a challenge for many people. Craig Farrell examines part of this process in his look at job postings and how you can interpret them. More »


SQL Server Sample Databases

This is a landing page for the sample SQL Server databases we are aware of. More »


Using source data from an SSRS report in PowerPivot for Excel

In this tip we would look using an existing SSRS report to provide the data for PowerPivot in Excel. More »


From the SQLServerCentral Blogs - Per Member Per Month Per 1000 Calculations in MDX

In a lot of industries there is a popular calculation called “Per Member Per Month Per 1000” calculation or the... More »


Editorial - Provisioning

At a talk recently, the presenter talked about the time lag for an on-premises server to be installed and configured to be on the order of months. Not that many, but it could easily be six months.

That seems crazy, but in many companies I've worked in, that wasn't an unusual time frame. From the time someone decided that a purchase was allowed, it could be days for the accounting department to approve the funds. A day or two to place an order, assuming that IT had already provided the specifications. Vendors respond quickly, but it could still take a week or two, possibly even more, for a server to arrive at our company.

One of the advantages of cloud computing services is that new virtual machines can be bought and enabled in minutes. That might be true, but I wanted to ask many of you if you know how longs it actually takes for you to begin working on a new SQL Server.

How long does it take to provision a new server? 

Imagine that you were to ask for a database server today, and you management agreed to grant the approval, think about how long would it be before you could sign into a SQL Server. I'm sure some of you have virtual infrastructures available, and that should reduce the time it takes, but let us know if that's your setup and how long it takes.

The ultimate service would be a cloud like application inside your organization that allowed you to select a SQL Server template, and send you a server name and login with minutes after an automated build took place. I don't know many companies that have that yet, but I think many of us would appreciate that capability. Especially if we could choose the hardware we required.

» 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. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

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:

You are trying to execute the following code:

DECLARE @MoreColumns bit;
SET @MoreColumns = 1

IF @MoreColumns = 0
CREATE TABLE #Table (
    id int, 
    name varchar(50) 
)
ELSE
CREATE TABLE #Table (
    id int, 
    name varchar(50), 
    Description varchar(8000), 
    USER nvarchar(200) 
)
    
DROP TABLE #Table

What would be the outcome?

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.

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.


Yesterday's Question of the Day

What is returned from this query?

SELECT
   REPLICATE('12',1)
,  REPLICATE('12',0)
,  REPLICATE('12',-1)
,  REPLICATE('12',1.5)

Answer: '12','',NULL,'12'

Explanation: According to BOL (http://msdn.microsoft.com/en-us/library/ms174383.aspx), if integer_expression is negative, NULL is returned. And 0 returns 0 instances, and the second parameter is bigint, so 1.5 is converted to 1.

» 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

Generate DDL script to drop table constraints

Execute SP to generate alter statements to drop PK, FK, Check Constraints and Default Constraints on a Table 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

Does the log file of the database grow when DBCC CheckDB is run ? - Hello, Does the log file of the database grow when DBCC CheckDB is run ? Thanks, Smith.

sysJobActivity showing jobs from years ago that never ended - I've noticed that sysJobActivity shows a number of jobs where the stop_execution_date is NULL, but the run_requested_date is up to...

How to delete any backup files based on CURRENT day in a daily schedule. - How to delete any backup files based on CURRENT day in a daily schedule. I create a sql script as follows....

Does SQLSERVER2008 offer Object level recovery - Friends, I have been using a third party toll that offers excellent object level recovery from the compressed backup file, whereby...

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

INDEX REBULID - HI, ANY ONE GIVE ME SCRIPT FOR INDEX REBULID AND UPDATE STATISTICS IN SQL 2008 R2

SQL Server 2005 : Business Intelligence

Import Excel binary files into SQL Db using SSIS - Hello, I have a several .xlsb (MS Excel binary) files with data that I need to import into a SQL...

SSIS - XML Source to OLE DB Command problem - I have an SSIS package that is trying to pull from an XML Source and into an OLE DB Command....

SQL Server 2005 : Development

Extracting data from xml - I am trying to read data from the following sample XML file. I have tried SSIS XML task to remove...

SQL Server 2005 : SQL Server 2005 General Discussion

Retrive parent child relation - Hi all below is my table and I want query for parent child relation. CREATE TABLE [dbo].[PRDST]( [PARENT] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS...

Deploying ssis package - Hi All, Am trying to deploy a package. which is created in VS2010 into sql server 2012. but am getting below...

SQL Server 2005 : SS2K5 Replication

Help - restored backup is syncing with the production database - Never mind.

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS Package Optimization - Folks, We are taking Oracle Source data using Data Flow Task in which Data Reader as Source [We have Separate DSN...

SQL Server 2005 : T-SQL (SS2K5)

Adding an include to an existing Index - Hi Everyone, What's the T-sql to add an include column to an existing index? Many thanks

Error converting datatypes ('NULL' to int and VARCHAR to int) - Hello - my issue is that I need to get 5 rows of data from my PROD system down to my...

SQL Server 2005 : SQL Server Newbies

Data Scrubbing - Hello Guys, I have a Requirement where i have to scrub the data. I have loaded data from a flat...

SQL Server 7,2000 : Administration

Gather Storage Information for SQL SERVER - How to gather storage information for a particular sql server environment.

SQL Server 7,2000 : General

Triggers - SQL2000 Need help with triggers. I'm trying to create a trigger that checks the table; if the record exists based on...

SQL Server 7,2000 : Performance Tuning

Shrink large database - I have been asked to look a a sql 2000 database that has been neglected for some time. It has an...

SQL Server 2008 : SQL Server 2008 - General

Writing a case statement once including it in several queries - I used to use Focus before migrating to Sql and miss some of the commands in Focus and not in...

Linked Server Query Hung in Killed/Rollback - I have a SQL 2008 R2 production database where a sql agent job kicks off some stored procedures. One of...

Patch compliance report - Hello everyone I want to set up a new process at work, whereby each of our SQL Server instances (2000 up...

Moving from SQL 2012 Enterprise to Standard - possible? - Is there any way to downgrade a SQL Server 2012 Enterprise instance to Standard edition? I guess it's not as...

Several Jobs or One Jobs with several steps? - We do have the need of running several jobs daily... jobs that update custom tables, create reports etc. What is the...

Ambiguous SQL server - Hi In a couple of select statements (out of nine) I had to qualify the field table with the table. Yet this...

recommended configuration - I need to build 3 servers to run SSRS (dev, qa, and prod) the servers will only be used for...

How to send mail from DataBase mail in sql server 2008 - Hi, I am using Sql Server 2008. I had tried using Gmail server and I was able to send the mail. But,I...

SQL Trigger on condition - Hi Team, i need a trigger based on condition. CREATE TRIGGER [dbo].[Trigger_Base_line] on [dbo].[Base_Tab] AFTER INSERT,DELETE,UPDATE here i want a condition logic in trigger, if...

BufferTempStoragePath - Hi I have a semicolon separated list for this property on my Data Flow Tasks. For example 'F:\Folder; G:\Folder' What could...

Database Tuning Adviser - Good Day. On our SQL Server 2008R2 we identified an inefficient query . We ran it through the Database Tuning Adviser...

Agent Job Links to Another SQL Server and Fails due to Service Account Privs - To my friends in the community, here is a stumper that I can't seem to find sufficient information on despite...

Scripting & automating all SQL Server Jobs - Hello, I am looking to script out all the SQL Server agent jobs and automate it, for high availability purposes on...

Finding users with particular roles - Hello I need to find all users that have DB_Owner or DB_SecurityAdmin on every database. I've got this script that will...

SQLCMD Issue - I am facing error with SQLCMD(sql 2005 EE) SQLCMD -E -S<Ipaddress> Sqlcmd: Error: Microsoft SQL Native Client : Unable to complete login process...

Import of huge XML file - Hi All! I have an xml file of 44 Gb (Not Meg, its really GB) Delivered by the Danish custom authorities. My problem...

indexed views, aggregations and poor cardinality estimations - I've got a large sales table (66m rows) that I need to query in an aggregated way to return a...

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

SSIS format Excel worksheet with script task - I'm exporting to an Excel file and then formatting the spreadsheet. Using SQL2008R2. When I run the package from the...

MS-SQL instance failing due tempdb space issues - Hi, I should clarify my instance is Cluster aware and running SQL2012, but posting here because I believe this is more...

Problem patching to 2008 SP3 - I have a 2008 enterprise 32 bit instance running on Windows Server 2003 Enterprise server. I'm an administrator on the...

BULK INSERT failing. - Hello, I have a situation where I am trying to do a bulk insert of a file that's on a network...

SQL Server 2008 : T-SQL (SS2K8)

How to get date as my column not as row - I have data that looks like the following: Sales_2012 Plan fiscal_week_2013 Sales_2013 LY PN 232.2935700 286.8642 2013-01-05 263.1448400 0.132811553931518638 -0.0826852665251378 237.2016700 286.8642 2013-01-12 258.2310900 0.088656289814485707 -0.0998144463016142 232.0180700 286.8642 2013-01-19 264.8221900 0.141386056698083903 -0.0768380765586006 238.2087000 286.8642 2013-01-26 266.3660400 0

previous Friday till the end of the current Friday - Hi Guys, I'm designing a report in SSRS,I need to pull that from DB from previous Friday till the end of...

Pivot Table Help - DECLARE @EmpID nvarchar(max) = ' ' DECLARE @sql nvarchar(max) SELECT @EmpID = @EmpID + '['+Convert(nvarchar(max),EmpID) +']' FROM (SELECT DISTINCT EmpID FROM emp.teamassignments) AS StaffList SET @EmpID = STUFF(@EmpID,1,1,'') PRINT @EmpID SET...

Syntax on assigning result of EXEC to var - Morning folks, I'm a bit stumped here. The issue I've got is syntax related I'm sure. Essentially I want to run the...

join 2 rows to a column - create table dbo.#abc([name] varchar(100),id int) insert into dbo.#abc([name],id) select 'Animal',100 union select 'beer',100 union select 'Tree',200 union select 'Olive',200 I want to have resultset as animal.beer tree.Olive.

creating procedure for Insertion? - Hai Friends , I m creating one web application in that input fileds are departuredate,from_place,To_place,travel mode. My condition for insertion when i...

concatenation breaks when using different datatype - HI, I have a table, SampleNums, containing a couple of int columns that I need to concatenate into one column. When...

Trigger referring twice to a table doesn't work properly - Hi all, I have a problem with a trigger and I hope some expert can help me on this ... This is...

Finding the total count of ACTIVE Customers and their remaining TOTAL POINTS for each distinct groups of CATEGORY. - Hi I’ve a CustomerActivity table that records all the activities of the consumers. I've create a simplyfied version of the table with...

SQL Server 2008 : SQL Server Newbies

Result should display only certain information in select command - I am new to SQL enviornment. I have question regarding using select command. Current SQL command Select caseid, casetype, Casesubtype...

Sort by Calendar month - All; I am having an issue where my rows are calendar months and my columns are different vendors. I can get...

Sending a Null Value in a Store Proc - Newbie question here. But...if I don't ask, I dont learn. Writing and testing a new SP which will insert data to...

mirroring intervals for automatic failover - Hi, in my production server i configured mirroring with automatic fail over in sql server 2008 r2 can anyone tel me...

Check 2 date fields for a particular date? - Hi everyone, I've created the table below to keep a record of office staff's annual leave (or vacation) each line...

windows updates / security patches in a clustered environment? - Hi all I´me new to the DBA role and this weekend we are having our first "maintenance weekend" in a while,...

Database Diagram - Hi Guys. After creating a new database diagram,i don't see any relationships.It's only showing the tables.I tried selecting the show relationships...

Sql server relationship using between 2 values - I have a database that contains 2 tables (sql server 2008): [b]LenderCommission[/b] ID int Commission decimal [b]CommissionTier[/b] ID int MinCommission decimal MaxCommission decimal I want to create a...

Backup and recovery for the accidental DBA - Hello - I am not a DBA, let's start with that...but I have acquired the task of backup up a db...

Random Number Generator - I am trying to create a random number generator that would populate a number between 8-48 to 9 different lanes...

SQL Server 2008 : Security (SS2K8)

REVOKE ability to GRANT - I want to revoke the ability for a particular user (who owns a schema) to grant permissions on objects in...

SQL Server 2008 : SQL Server 2008 High Availability

One Way Transactional Replication: Multiple subscribers for single Publisher - Hi Friends, I have a scenario where there are two servers A and B. A act as a publisher and B...

Instance not selectable when trying to remove node from a cluster - Hi All, I have installed SQL on one of the nodes of a 2 node cluster but it failed, this...

SQL Server 2008 : SQL Server 2008 Administration

Copy SQL .bak file to other server using FTP and restore the backup to a database - Hi, I am looking for solution for below approach using TSQL or powershell or using SQL agent job. 1. Perform backup on...

Service SQLExpress does not start oall of the sudden. - c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn\SQLAGENT.EXE" -i SQLEXPRESS 2013-05-23 11:07:28.95 Server Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53...

[sys].[dm_os_volume_stats] - I'm working on collecting stats on my sql servers to better keep an eye on them, and there's a table...

SQL Agent Stopped - if not exists (select * from [master].dbo.sysprocesses where left(program_name,8)='SQLAgent') Begin EXEC msdb.dbo.sp_send_dbmail @profile_name ='Database Mail', @recipients ='email@id.com', @body ='Please look into the...

Error: 17066, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped. - Dear Sir, We are using sql server 2005 standard edition. Ram is 4GB. Today we got this below error. Error: 17066, Severity: 16, State:...

Edition upgraded after Maintenance plan was failed? - Hi, Recently upgraded done from standard edition to enterprise edition and SP3, after that Backup maintenance plan was corrupted then it...

stored proc taking more time when executed thru a job - I have a stored proc which collects the primary key values into temp table and delletes the rows from the...

SCOM and SQL - Set and forget? - For you that use SCOM in your environment. How do you treat the SQL management packs? Do you let SCOM administrator just...

SQL Server Setup failure - I'm trying to install SQL Server 2008 R2 on Windows Server 2003 R2. As soon as I run the setup.exe...

cluster - Can someone help me understand cluster? we have two nodes for the cluster. each cluster we installed two sql instances, one...

Backup Job Incomplete - Hi, I have problem My backup script runs on every sunday for full backup and should have to take 41 database...

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

Activity Monitor Empty - I need help and I'm ready to pull my hair out. I'm running SQL Server 20008. Microsoft SQL Server Management Studio 10.0.4000.0 Microsoft...

dbmail problems on SQL 2008 /SBS 2008 64bit - Hi, I'm neither a DBA nor a Sys Admin person, More a coder but I've been asked to move a...

Older Versions of SQL (v6.5, v6.0, v4.2) : Older Versions of SQL (v6.5, v6.0, v4.2)

invalid characters in input file - Hi all, I'm being told I have to scrub invalid data in an input file. I have a 30 character...

Programming : Service Broker

Disabling Service Broker. - I have two service broker applications on one instance of database. One sends/recieve messages locally and other one sends/recieve messages to...

Programming : Powershell

Retrieving Data From Hyphenated Server - What better place to solve a nagging Powershell issue than good ol' SQL Server Central :) I'm trying to retrieve disk...

SQLServerCentral.com : SQLServerCentral.com Website Issues

Why are some pages so W-I-D-E ?? - Why are some of the pages so incredibly wide? My screen resolution is set to 1280 x 1024 and some...

Reporting Services : Reporting Services

Running a report multiple times - Hi, So... I have a report that has a dataset that looks something like this: AccountManager, Product, Value The report has a variable...

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

Now You Can Use Visual Studio 2012 To Work with Word, Excel, PDF, PowerPoint and many more Applications - Many of our users use Visual Studio to speed up development and help organize their code. It’s a flexible development...

Unable to get stacked Column chart working - Hello, I am trying to get a stacked column chart to work. I have the following query: SELECT COUNT(CALL_Status) AS Calls,...

Data Warehousing : Integration Services

SSIS Package w/Dynamic Source and Destination: Is this possible, if so how? - Greetings, I have been tasked to investigate how to develop an SSIS solution that would accept a string variable containing...

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

SSIS Package to remove leading 0's - Hey Everyone, :-) I am creating an SSIS package and having some issues removing leading 0's from a field. I cannot...

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

Question on cube layout - Hello All! I posted in the data mining forum about trying to lay out my cube with my measures segmented by...

Article Discussions by Author : Discuss Content Posted by Jeff Moden

innerjoin between 3 table columns - Hai, i want to know innerjoin between 3 tables ata a time. plz helpme.