In this issue

Featured Contents

Editorial

Featured Script

If you think your management doesn’t know what it’s doing or that your organisation turns out low-quality software crap that embarrasses you, then leave. -- Edward Yourdon

 
 advertisement
 
SQL Backup "Impressed is the word!
Cut our backup times in half and reduced the space used by 80%!" Tobie Dunn, SQL Backup Pro 7 user. See what savings you can achieve - download a free trial today.
 
SQL Connect Keep your database and application development in sync
SQL Connect is a Visual Studio add-in that brings your databases into your solution. It then makes it easy to keep your database in sync, and commit to your existing source control system. Find out more.
 
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.

In This Issue

How to receive Deadlock information automatically via email

Receive Deadlock info from the SQL Error Log every time a deadlock occurs. More »


SQL in the City - Boston 2012

A free day of training in Boston on Oct 8, 2012. Come join Grant Fritchey, Steve Jones and more to talk about SQL Server and how you can work more efficiently. More »


Grant’s video warning – backup verification

Grant takes a humorous (but completely serious) look at why you should be regularly verifying your backups. Get top tips for backup and recovery, and protect yourself when disaster strikes. Watch the video More »


Power View Infrastructure Configuration and Installation: Step-by-Step and Scripts

This document contains step-by-step instructions for installing and testing the Microsoft Business Intelligence infrastructure based on SQL Server 2012 and SharePoint 2010, focused on SQL Server 2012 Reporting Services with Power View. This document describes how to completely install the following scenarios: a standalone instance of SharePoint and Power View with all required components; a new SharePoint farm with the Power View infrastructure; a server with the Power View infrastructure joined to an existing SharePoint farm; installation on a separate computer of client tools; installation of a tabular instance of Analysis Services on a separate instance; and configuration of single sign-on access for double-hop scenarios with and without Kerberos. Scripts are provided for all/most scenarios. More »


From the SQLServerCentral Blogs - Going to the PASS Summit? Join Us at a Networking Dinner

For the third year Steve Jones and I are coordinating a very informal networking dinner on Monday night the week... More »


Editorial - Teammates

I loved the Terry Tate commercials as a young office worker. They were unveiled at the Super Bowl in 2003, when I was still managing a database team at Peoplesoft, with weekly issues occurring on our production systems. There were times I wished I could hire Terry to try and convince more people they should be working together using the stick instead of the carrot.

I have known a few people that worked with former atheletes in real life. If you didn't recognize them from their past, you might never realize they used to play sports for a career. I was reminded of them by this short slide show that imagined various NFL players as office workers. If you can get past the idea of professional athletes attacking your business problems in the same way they play on the field, the piece shows a number of different personalities that you might find in business. I've dealt with many of these in the past, with mixed experiences. I certainly haven't enjoyed working with the "Lawrence Taylors" of the business world.

This Friday I wanted to make this a fun poll. I thought you might want to describe yourself or your boss with a famous figure:

Which professional athlete (or other star) displays the type of traits you think would best suit your position?

It doesn't have to be an athlete. Pick someone in the arts, music, movies, etc. Just choose someone famous that you think would exemplify the person you want to be perceived as, or would like to grow to be like. Or represents you now if that's the case.

For me, I think Lawrence Lessig has a fantastic way of expressing himself in relatively few words, but in a very logical manner. I also appreciate his passion and knowledge on various topics. Some day I hope I can express myself as well as I think he does.

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

Which command will provide the total CPU time and total amount of disk reads for a process?

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

This question is worth 1 point in this category: Administration. 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 Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate


Yesterday's Question of the Day

Which of following are used wen configuring resource governor work? (Choose 3)

Answer:

  • Resource pools
  • Workload Groups
  • Classification Functions

Explanation: The Resource Governor allows you to limit the CPU and memory allocated to or used by a specific connection or group of users. The Resource Governor is configured with three components: Resource pools, Workload Groups and Classification functions.

Ref: Resource Governor - http://msdn.microsoft.com/en-us/library/bb933866.aspx

» Discuss this question and answer on the forums

SQL Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate


Featured Script

SQL Server Index Defragmentation (Updated)

Defrags all indexes in select databases in 2005, 2008, and 2012. 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

Backups - Transaction Log and Differential - Hi all, I'm a bit confused regarding the different modes of backup. If I understand correctly, a FULL backup takes a backup...

How to speed up backup process. - Hello, Apart from [b]Backing up to multiple locations and compressing the backup file[/b], is there any other way to SPEED UP...

Logshipping restore job failed - Hi Experts, In one of my prod server I have configured logshipping for two databases in a single server (SQL Server...

Logins enabled - Query to find out list of logins that are enabled in SQL SERVER 2005.

Communications to SQL server "freeze", then resume. - I am looking to see if anyone else has a few ideas of where to start in looking for problems...

Question on ACID properties - Experts, One of the interviewer asked me something like below question. It was not clear to me anyway. "When we get an...

SQL Server 2005 : Backups

Maintenance Plan succeeds, but does not perform backup - I have a new SQL Server 2012 AlwaysOn Availability Group on Windows Server 2008 R2 Ent SP1. I'm creating a...

Third Party Backup Technologies - Hello, I am looking at implementing a third party technology to allow me to compress and encrypt backups of databases in...

SQL Server 2005 : Business Intelligence

How to create excel destination dynamically in SSIS 2008? - Hi friends, I’m facing a problem with my SSIS package, I want to export the data into an excel 2003 using...

How to SetItemParameters for a report in SSRS 2008 using web services through SSIS package Script Task - Gettings Everyone !! :-) I'm new to SSRS and really need some help from you experts in this Forum -- I created a...

Problem to install SQL Server 2008 - Hi I am in need of help. I had Sql server 2008 R2 installed before. I project I got demanded that...

SSIS and Raw files - Hi all Does anybody have an idea if you can use raw files to update records in a table using SQL...

SQL Server 2005 : SQL Server 2005 General Discussion

Why it is returning * - hi All, when i am executing the below query it is returning * instead of showing error. select cast(123 as varchar(2)) means the length...

BCP Error On New Server - I have a SQL Job that executes a BCP command to create an file of exported data. The job has...

Delete Constraint rule Set Default problem - I just want to ask if Set Default works with columns that are string related? I've tried setting up my a...

A severe error occurred on the current command.The results, if any, should be discarded. - Hi all, i have some problem with my SQL Server, if i am creating any new Db, and i am...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Any info on Deadlock detection algorithms? - Hi Gurus, I'm trying to find a starting point from which to troubleshoot an issue we are facing where the same...

Server becomes very slow [RESOURCE_SEMAPHORE] waittype. - From last week our server periodically becomes very slow, I'm getting multiple RESOURCE_SEMAPHORE waittypes. The server box specs are: 4 Quad...

SQL Server 2005 : SQL Server 2005 Integration Services

For Loop Expression - Casting Error - I am trying to program a For Loop container and cannot get the For Loop Properties expressions set. I have...

No Progress while Update - Hi Friends, I am about to update some rows so i use OLEDB command component. When i do this, my...

SQL Server 2005 : T-SQL (SS2K5)

SQL not deleting files - Why is the following statement not deleting the backup files? EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master',N'.bak',N'2012-09-12T16:25:36',1 It finishes without error, in...

sql server Data Matrix. - I have Table A , and table B . table A holds the about the class of a product and table B...

Copying Headers from SQL Query Window results in SQL 2005 - I wanted to copy the headers of a query result to a excel spreadsheet. In the grid view mode, by...

Is it safe to use "update syscolumns set colstat" - Hello guys, I have a very large table(100,000,000 records), and I have an identity field ,which is primary key.I want to...

SQL Server 2005 : SQL Server Newbies

Northwind Database for SQL Server 2005 - Hello to all. This is my first post in this forum so I guess I am a "Grasshopper" again. I...

SQL Server 7,2000 : Administration

Could not allocate space for object 'patient' in database 'DMART' because the 'FG2' filegroup is full.. - I'm out of ideas here. When I was first alerted to this situation on a legacy SQL 2000 box I...

SQL Server 7,2000 : Working with Oracle

Learning Oracle - Found some resources, SQL Reference Guide for Oracle 10g. Guess what, Oracle actually has CTE's, they just don't call it...

SQL Server 7,2000 : T-SQL

Csv list in column to separate columns - Hi all, Can I ask please if I have a row and one column has a csv list in it. Is...

SQL Server 2008 : SQL Server 2008 - General

Replication 2008 - log-based indexed-view-to-table - Hi, We are setting up a Transactional Replication and we are having some issues with an indexed view that slows down...

error :Column data type DT_STR is not supported by the PipelineBuffer class. - I'm moving some data from sql server table to shaepoint list. I'm on sql server 2008 r2 The input columns from sql...

Migrating 2008 reports to 2012 - Hi Friends, I am new to 2012 and I have been given the task to convert the Reports generated in 2008...

unable to connect using sql authentication - I have installed sql server 2008 r2 with mixed mode authentication. I am able to use the sa credentials from my...

Data Archival - There is a requirement, details as below: 1) Copy data from transaction table (around million records inserted daily) into a archival...

Update Statement - Differences between SQL Versions? - Hi, I've been having a chat with Google and haven't been able to find an answer to it so here...

List Transactions that Meet Greater Than Criteria - I work for a credit card company. Our database has a customer table and a transaction table. Fields in the...

Create login - domain name with '.' - Hi I'm trying to create a simple script to allow Network Service accout to access my database DECLARE @user NVARCHAR(50) SET @user...

ms standards doc? - Does Microsoft provide a standards document for SS design standards? For example: "Column names should be Pascal-cased - ex - FirstName"

Maint. Question...Alter Index THEN UPDATE STATISTICS FULLSCAN, COLUMNS? - I have probably a silly question but I wanted to throw it out there anyways... In developing a methodology of reindexing...

How to know if connection can be made to SQL 2008 R2 in my case? Thanks. - Hello, I have an IIS server running on Windows 2008, it needs to connect to a SQL 2008 R2 on a...

A Database with 70 Table - hi everybody I Have Database With 70 Tables and this my first time that i have alot table in my DB....

Sum with Multi Category and Group By Acc No ? - SELECT SUM(AMOUNT) as SumAmount,TransType_ID,Transaction_AccNo FROM Tbl_Transaction Group By TransType_ID,Transaction_AccNo below is the sql query result SumAmount TransType_ID Transaction_AccNo 65000.000 0 1 -35157.000 3 1 -1872.235 2 1 [b]Question = i want to show only...

New Line Character - Hi, Am using below query to display veh_name, expiration_date, for new line character, here am using "+CHAR(10)" what is the new line character,...

IN Vs INNER JOIN - I want to know which query is more optimized? SELECT 1 FROM ABC WHERE Id IN (SELECT Id FROM ABC1) =================================================== SELECT 1...

sql server 2012 cluster take way too long to backup/restore - Hi experts, I need some directions on what sort of things should I look into why backup and restore take way...

Replication Issue. - When I try to run sanpshot job is also failing. When I replication monitor, I see below error. Help needed. Error messages: The...

results from a SP into a #table ? - Hi, I am getting empty result set while trying to insert results from a proc into a #table unless I...

Need to zip and move C2 level audit files - We have a secure shop running SQL Server 2008 EE on a couple of Microsoft Failover Clusters (Windows Server 2008...

SQL Server 2008 : T-SQL (SS2K8)

How to transform this result? - Hi SQL Gurus, I have this default master data table below: [code="plain"] ITEM | CNT | DESCRIPTION ---------------------------------------- HMMEDA | | HYH Med Oil HMMEDA | G01 | HYH Med...

Query execution time high cost? - Hi, Is it possible to change alternative way to modified this sql statement. its tooks 2.47 min for fetching 151 records, [code="sql"]SET...

Looking script to get the available disk space and other details. - hi, I am looking for a custom script to get the details from all the servers while running the script in...

Cursor Help - I have a question, Here is my query, SELECT SID FROM chartdDB WHERE ADate = @ADate AND SID NOT IN (SELECT SID FROM...

Get the overall status of a particular product - Hi, need some help to write the query as i am not sure how to achieve this. I am having a...

Billing of Materials query - Hi there, I'd be grateful if someone could help:) I have a BoM issue. The table below shows the components, A's being the...

Weird behavior with a foreign key and "on delete set null" - So I have two tables [code="sql"] CREATE TABLE dbo.Orders ( order_id int NOT NULL IDENTITY(1,1) CONSTRAINT PK_ORDERS PRIMARY KEY CLUSTERED, order_description varchar(250) NOT...

use column in Group By, but Hide column from the results set - Is this possible in sql server 2008? I want to use the column in the select, and group by. But want...

MERGE vs IF EXISTS with INSERT UPDATE - Hi, SQL Server has the MERGE statement and some people advise to use it instead of IF EXISTS with INSERT / UPDATE... Is...

How to get Distinct values across the table? - DECLARE @t TABLE( col1 VARCHAR(1), col2 VARCHAR(1), col3 VARCHAR(1), col4 VARCHAR(1) ) INSERT INTO @t VALUES( 'A', 'B', 'C','D' ); INSERT INTO...

Help With Update trigger - Hi There I require some assistance or direction with a update Trigger I have 2 tables Client and Client 2 I...

How do I use one column for node names and the others for elements in that node? - I'm trying to query some data with FOR XML to output the XML. Let's say I have data like this...

End Of Year - Good Day To All, I'm working on a budgetreport that ask the user to enter the up to date. If the user...

Combining multiple rows of data from the Source table into just one single row of data with additional columns in the Destination table - Below are some of the sample SQL scripts and the given scenarios. USE [db_test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON --Create SourceTable GO CREATE...

T-SQL Help Needed - CREATE TABLE Issue ( id int not null identity(1,1) primary key, name varchar(10) ) CREATE TABLE Jrn ( id int not null identity(1,1) Primary Key, issue_id int, created_dt datetime ) ALTER...

Order by with case - Hi All I want to sort some table data using column index The following query works: [code="sql"] declare @index varchar(10) = 4 select CityInternalID, CityDefaultName, CityCountryID...

UPDATE when the values are the same - Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating...

SQL Server 2008 : Security (SS2K8)

Notify when CmdExec Job Steps Scheduled..? - I have been working with a customer to help them implement a departmental SQL Server that conforms to their Corporation's...

Ownership Chains, Dynamic Queries, and SQL Injection - I was hoping to confirm that while SQL injection is possible with dynamic queries that don't use parameterized queries, ownership...

Another linked server login problem - hi everyone, i need to consult this issue am having with linked server. i setup a linked server from server1 to...

SQL Server 2008 : SQL Server 2008 High Availability

Mirror disconnected - I have sql server 2008 on windows 2008 Mirror configured with Witness(Certificate authentication) today I found that Mirror disconnected state.I got the...

VMware combined with MSCS Clustering - Any thoughts if an MSCS clustering strategy on top of VMware is overkill from an HA perspective? It the current...

SQL Server 2008 : SQL Server 2008 Administration

Required disk space to reindex - 1st, apologies if this has been asked before. I remember I faced this issue few years ago, but do not...

Moving master LDF to log drive - I normally leave sys db's in the default location (not tempdb). However, I have a production 2008 instance, where the...

SQL Performance Statistics - Hi All How accurate are the counters in sys.dm_os_performance counters When I run this query [code="sql"]select * from master.sys.dm_os_performance_counters where counter_name='Batch Requests/sec'[/code] I get a cntr_value...

New Windows 2008 R2 server with SQL 2008 R2 - Hard Drive Setup - Okay I my be a little out of my league here, but I am learning. Please bear with me. Sorry...

x-cmdShell access - Dears all how can i restrics xp_CmdShell accesss to run some command? for example xp-cmdshell can not run format syntax or delete...

Programming : General

Is DBA a limited Profession ? - What after several years of DBA , Sr DBA.. Then what ? Just curious to know.... Isnt it DBA a profession which...

SQLServerCentral.com : Anything that is NOT about SQL!

Attended an Interview - Recently I attended an interview for a role of Senior SQL Developer and I wondered the kind of questions panel...

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

Front End Dev - HI all, I'm very happy in my SQL world with all the tools MS give me. Just sometimes somebody wants a...

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

Quick Interview question... I don't know if i got it right or wrong.... - Hi Experts, I had a manager asked me this question during an interview: There is SSRS Tree prompt in a report, where...

Grouping Values in a Report?? - Hi, I have an SSRS report to build that looks like this. Name Count Total BIKE COMPANIES 50 3000 HELMET COMPANIES 10 200 The problem...

Database Design : Design Ideas and Questions

odd data design (at least to me) - I've encountered a data design that is new to me. Frankly, I want to gouge my eyes out because it...

Data Warehousing : Integration Services

Integration Issue - Goal: Filtrate the different integration issues. My selection is: Description - Customer Name - IBM Encoding - Sex - M = Male and F= Female Units...

Integration Issue - Goal: Filtrate the different integration issues. My selection is: Description - Customer Name - IBM Encoding - Sex - M = Male and F= Female Units - Cable Length Inches Key...

Fast or not, using SSIS 32 bits comparing with 64 bits - I wonder if run a ssis package using 32 bits on a 64 bits server, will it runs slower than...

In SSIS package Need to Create new table Dynamically for each 1000 Records - Dear all, I had a requirement like, I have a flat file with 10,000 records, I need to load this...

SSIS Job needs to run every 15 minutes for 8 hours to check for existance of a record in a Table for the Current Date - I have a C# Solution and it checks for the existence of a file named Complete.txt that is generated from...

SSIS package error in SQL 2008 ActiveX Script - Hello All, I'm receiving an error on a newly upgraded SSIS package from 2005 to 2008 R2. The error happens...

SSIS E-mail download - Can any one tell me how to download a file form email and used in ssis Package?

Data Warehousing : Strategies and Ideas

Configuring SQL Server 2008 R2 Change Data Capture with SSAS - I'm completely new to SSAS so you'll have to excuse my ignorance. I've been running through the MSDN SSAS tutorial...

Number of natural\business keys in dimension tables - My scenario is when the destination dimension table is derived from multiple source table with the source table changes occuring...