In this issue

Featured Contents

Editorial

Featured Script

The goal of Computer Science is to build something that will last at least until we've finished building it. --Anonymous

 
 advertisement
 
SQL Source Control The best way to version control T-SQL
SSMS plug-in SQL Source Control connects SVN, TFS, Git, Hg and all others to SQL Server. Learn more.
 
SQl Monitor Hosted Here’s looking at you, SQL Monitor Hosted
Interview with SQL Monitor Hosted dev Matt Lee on taking SQL Monitor to the cloud. Read here.
 
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.

In This Issue

Webinar: Temporary Tables in Oracle and SQL Server

Once again Jonathan Lewis (Oracle Ace Director, OakTable Network) and Grant Fritchey (Microsoft SQL Server MVP) will host a live discussion on Oracle and SQL Server, this time in relation to temporary tables. Will they agree on some common ground? Or will it be an out and out argument? Either way, be prepared for a lively exchange that will not only entertain, but will teach you key concepts on Oracle and SQL Server. More »


DAX Query Basics

In this document I will attempt to talk you through writing your first very simple DAX queries. For the purpose of this document I will query the rather familiar Adventure Works Tabular Cube.  More »


SQLServerCentral Webinar Series #21 - Forgotten Rings & Other Monitoring Stories

Most common monitoring metrics are important and useful, especially over time, but they can fall short. How do you gather information to determine, for example, if you have buffer cache pressure? Register now for the free webinar. Wednesday, October 17 2012 4:00pm - 5:00pm BST More »


Meredith Ryan: DBA of the Day

Meredith Ryan – DBA at the Bell Group –was elected by judges and the SQL Server community as the Exceptional DBA of 2012. So who is Meredith, and how did she become a DBA? What makes her exceptional at her work? Simple-Talk sent Richard Morris to investigate. More »


Editorial - Trade-offs

I've always told my managers that building software is a trade off. We can do things cheaply, or we can do them quickly, but we can rarely do both. We can certainly fail in both ways, and many people do, but I usually see the need to trade time for money, or vice versa, when building software.

I ran across a piece recently that was similar, though in it Allan Hirt says you can do things right, do them fast, or do them cheap. Allan talks about limited resources in working on projects. At some point time, budget, and staff resources are constrainted and you can't expect stellar performance on extremely large workloads in a 24x7x365 environment. Something has to give, and one of those resources is going to let you down.

I had always looked at people as a "money" cost, since they are paid for work, but there is another constraint to people that I hadn't considered. Each individual in your company isn't just a resource that's exchangeable for others. Each individual has knowledge and skills that aren't easily transferred to, or replaced by, other employees. In addition, each person has time constraints; they will only work so much in a given time period. 

Building powerful, efficient, effective systems, whether in software, infrastructure, or even in culture takes effort, time, and money. However if you do it well, it's something you're not only proud of, but something that pays for itself over and over.

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

There are three queries with DDL statements inside active transactions.

-- Query#1
BEGIN TRAN
CREATE TABLE tblTran
  (
     id INT
  )
ROLLBACK TRAN
GO

-- Query#2
BEGIN TRAN
CREATE DATABASE testDB
ROLLBACK TRAN
GO

-- Query#3
CREATE TABLE tblTruncate
  (
     id INT
  )
BEGIN TRAN
  TRUNCATE TABLE tblTruncate
ROLLBACK TRAN
GO

Which of them will be successful? (answer them in the sequence of execution)

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.

Troubleshooting SQL Server: A Guide for the Accidental DBA

Three SQL Server MVPs provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". Grab your copy today from Amazon.


Yesterday's Question of the Day

How many Extended Event packages were shipped with SQL Server 2008 R2 that can be used within user-defined event sessions?

Answer: 3

Explanation: There are three Extended Event packages that were shipped with SQL 2008 R2 that can be consumed by a user-defined event session. Those packages are: package0, sqlos and SQL Server. The SecAudit package is a private package and is not consumable by a user-defined event session.

Ref: http://msdn.microsoft.com/en-us/library/dd822788(v=sql.100).aspx (right after Fig 1) You can query the sys.dm_xe_packages DMV to see these packages and some of their properties. You can also find which are consumable by a user-defined event session using this code snippet: SELECT p.* FROM sys.dm_xe_packages p WHERE (p.capabilities IS NULL OR p.capabilities_desc <> 'private');

» Discuss this question and answer on the forums

SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.


Featured Script

SQL Start up Script

This is a script that I run when SQL starts to notify me via email that a server has restarted as well as the location of the server. 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

Our DTS PAKAGE can not run - Hi All, Suddenly my dts package on agent failed. And giving below error: Executed as user: MYDOMAIN\b2b.admin. ....3042.00 for 32-bit Copyright (C)...

Table partition - Multiple filegroups vs Primary filegroup? - Hi All, Can someone tell me the advantage of doing the table partition on multiple filegroups or on primary filegroup? Thanks in...

What are the best options to secure backups without using third part tools ? - Experts, What are the best options to secure backups without using third part tools ? I am able to think of the...

SQL Server Memory - HI Experts, I have a small concern again on SQL Server Memory issue as one of my prod server runs on...

Agent service - Hello, I have set a alert for all the job status, i cant afford certain jobs to fail or not to...

Maintenance Jobs Fail After Changing sa Password - On a number of different instances of various SQL versions, Including SQL2005 and 2008, I am having Maint jobs failing with...

simple recovery model Log truncation - Hi 1) At what time intervel does the simple recovery model truncates the trans Log 2) I am having a 30...

select query - i need a select query for below. there is 3 date colums in a table. how to find out the greatest value...

Cannot generate SSPI - Error message shown in the logs is : Approva.ApplicationBlocks.ExceptionManagement.BaseApplicationException: Error occurred in Openconnection in SQLServer. ---> System.Data.SqlClient.SqlException: Cannot generate SSPI context. ...

SQL Server 2005 : Backups

Could not locate file 'SAML' for database 'SAML' in sys.database_files - Good Morning - I know this topic has been beaten to death over the years but I can't shrink a large...

SQL Server 2005 : Business Intelligence

Adding an ESRI Map to an SSRS Report - Hi I'm using Windows XP Pro with Office 2007 & have installed SQL Server Mgmt Studio 2008 R2 with Business Intelligence Development...

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

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 : Data Corruption

BACKUP DATABASE is terminating abnormally - Dear All, Could someone please advice on this. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. Msg 845, Level...

SQL Server 2005 : SQL Server 2005 General Discussion

Degree of parallelism in SQL Server - I have a question on Degree of Parallelism. I basically want to know the best practice or guidelines to set...

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

SQL Server 2005 : T-SQL (SS2K5)

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

SQL Server 7,2000 : Administration

Display values from multiple rows in one row - Hi, Lets say, I have a table with Gender,Name and Age. Gender Name Age -------------------- Male John 52 Female Joe 33 Male Jim 42 I want...

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 : T-SQL

Match firstname lastname - Hi, Could anyone help me with T-SQL to match names. I have to match names from one database to bank account...

SQL Server 2008 : SQL Server 2008 - General

Error - Contains applied to remote table (linked server) - Hi good day! The error is the next: Variable parameters can not be passed to fulltext predicates: contains, freetext and functions:...

SQL Query Timed Out - Hi We are looking to upgrade our current SQL and Server, at the minute a lot of our larger tables...

If MSDB is in Recovery, will SQL Agent Jobs still execute? - This morning I came in, and it seems that my boss had power cycled one of our SQL Servers this...

Inconsistent performance on views - We're running SQL Server 2008 R2 and the views in question are not indexed. Originally the SELECTs against the views ran...

Disk Used by Tables - Hi all Please help me to understand why there is space difference in tables. please find an attachment. In tblUsrActivityLog table has...

Understanding the difference between Join and Where filters - SQL 2008R2 - I'm new to the TSQL world (coming from visual basic) and am working with large tables (125+ million rows) and...

sql service restarted unexpectedly - hello experts, in my production server sql service restarted unexpectedly, the concern teams are asking for root cause analysis doc for...

Unable to compare text datatype fields to extract salient data - Hi all, I am looking at a table which holds a record of things typed by different users into a field...

Trigger to concatenate two columns and update in new column - Hi, i have a table with columns ID F_Name L_name Full_Name my requirement is when user inserts id, F_name, L_name using trigger - concatenate F_Name + Lname and update...

database integration tasks - Hi, What are the database integration tasks available?

Behavior of "NOT IN" - Hi Here is sample script: create table a(id int, nm varchar(200)) insert into a select 1,'a' insert into a select 1,NULL select * from a where nm not...

Display multiple row values in a single row. - Hi, Lets say, I have a table with Gender,Name and Age. Gender Name Age -------------------- Male John 52 Female Joe 33 Male Jim 42 I want...

How to download SQL 2008 - Hi, I would like to download SQL 2008 x64 for test CRM Application. But,in official website has an SQL 2012. How...

Group By - question - I have a question which is probably really basic but can't work it out for the life of me so...

Update Query - USE [tempdb] CREATE TABLE [dbo].[hubCustomer]( [CustVid] [int] IDENTITY(1,1) NOT NULL, [CustSourceGid] [nvarchar](30) NULL ) INSERT INTO HUBCustomer SELECT '103' UNION ALL SELECT '105' UNION ALL ...

Select product depend upon date - Hai, Product Date -------------------------------------------------------- pd0 2012-08-11 18:45:55.780 Pd1 2012-08-11 18:55:17.020 pd2 2012-08-11 19:06:58.623 pd3 2012-08-18 12:00:01.193 pd4 2012-08-25 12:13:04.077 pd5

can i set ISOLATION LEVEL at user level ? - Hi All, is it possible to set a default isolation level for a user? because, when i am giving permission for a...

Renaming all constraint names with user defined names - Hello, How do I query the database for all types of constraints such as Primary Key,Foreign Key, Unique Key, and Default...

Upgrade 2008 r2 express w advanced tools to developer - I would like to upgrade my free 2008 r2 with advanced tools to the 2008 r2 developer edition. I understand...

SQL Server Best Practices - Hi All, Let me start by saying that I know that I'm opening up a can of worms here. My company...

split string into columns based on special character - /*The Displayname column has to be split into 5 columns basing on the '/' . I have noticed that the Field4 and...

Temp DB getting full.. Any remedies - Hello Everyone, I have a proc, that is used as a job previously that pulls lot of data. From the past...

Reporting : Addition of Custom Delimiters - Is it possible to add custom delimiter of ! in Text file in Sql Server Reporting Services. My requirement is to...

System tables - hello to all, i would like to ask if ever there are ways to copy the data from system tables...

Intresting Question need answer ASAP - Write the shortest code that produces results as 1 without using any numbers in the select statement. using sql

Please help connecting to SQL ! - First time I encountered this: I have a SQL2008 express instance on a remote server that I connect to from...

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

DBCC CHECKTABLE Error: 1203, Severity: 20, State: 1. - process attempted to unlock a resource it does not own - Recently we began receiving this error on a production database when a schedules job with DBCC CHECKTABLES runs: [quote]Message Process ID 115...

Reorganize Indexes in sql 2008 - Hi, I have done reorganizing of 76 indexes. But after that some of the indexes have no effect. Can anyone explain why SQL...

Copy results, update a few fields, reinsert - I'm trying to find an easier way of doing the following: We have a batch file that processes records into...

SSMS 2008 - Is there a way to better display tables in various schemas - In sql server 2008 we have 30 users schemas setup under a singel database (say "UserDB") Each user is set up...

Changing Authentication mode of connecting to Integration Services - In Managment studio(SQL SERVER 2008),I am trying to connect to Integration Services.But it is always in windows Authetication and I...

SQL Server 2008 : T-SQL (SS2K8)

Multiple Space into one + 65,33,17,9,5,3,2 - All, I have recently read Jeff Moden's Multiple space into one space article and i saw M's reply on this topic. He...

select rows where values appear consecutively - Ok, I have a question. I need to select data from a table where a specific value appears for a...

Get sql statement which throws exception while executing in try block - Hi, I need to get sql statement, which throws an exception while executing in try block. E.g. Begin Try SELECT 1/0 End Try Begin Catch ...

SELECT FOR UPDATE - Hi, I have a SP to get one record from a worker table and mark the record as processed. But since it's...

SSMS Query Logging - We have a user who has come from an environment (SAS) where all of the queries she ran were logged,...

How to download SQL 2008 Standard - Hi, I would like to dowload SQL 2008 standard x64 for trial for test CRM Application.The official website has only SQL...

How to filter condition by column? - Hi SQL Gurus, I have a below table with column names ACCOUNT, QTR1, QTR2, QTR3, QTR4 ACCOUNT QTR1 QTR2 QTR3 QTR4 ------------------------------- Opex 20...

SQL query eliminating rows problem - Hi all! I am trying to solve an issue and wondered if you could help, I'm relatively new to t-SQL and...

Decimal to DateTime2 conversion SQL SERVER 2008 help - Hi Forum, I have a EndDate column decimal(18,0) Q1 -- works perfectly select convert(datetime2,CONVERT(varchar(30),clx.prdt)) as ADate from EXPM clx where clx.prdt<>0 Error Q2--Msg 241,...

OPTIMIZE FOR UNKNOWN (parameter sniffing problem) - Hi, I recently read this post [url]http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url] and not sure I got the "juice"... If I have a SP with 4 parameters...

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

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

help with SQLCMD - Hi , I am trying to use SQLCMD to run a SQL Script. The script is of type. Begin tran Delete ..... Insert ..... Commit...

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

DB Last Used Date - Hi Forum, As we are on the way to cleaning Production server, we need to find out the last usage date...

SQL Server 2008 : SQL Server Newbies

read multiple fields with common delimited data into either a temp table or table variable without using cursors - if I have a table with fields which contain common delimited data in them, is there a way I can...

How Do You Keep The "Pieces" Of A Complex Statement "Organized" - I just finished a complex SQL statement; and, yes, I know there are even more complex ones. When I am faced...

Creating Trigger To Update Total On 'Parent' Table - Hi, I have two Table, [b]Order[/b] and [b]OrderDetail[/b] [code="sql"] Create Table [Order] ( OrderID BigInt Identity(1,1) Primary Key, OrderTotal Decimal(22,2) Not Null ) Create Table [OrderDetail] ( ...

Bulk insert of large files (more then 450MB) - Hi everyone I have to populate a table with a pretty large text file and I'm happily using the very good...

SQL Server 2008 : SQL Server 2008 High Availability

Changing Locations of DataFile - Hi All, As i see some of the servers are having databases online... Now this Database have data file & log file on...

SQL Server 2008 : SQL Server 2008 Administration

How to know SQL Server Memory Utilization - Hi All, We have SQL 2008R2 Standard Ed.(64 bit) installed on Windows 2008 R2. Physical RAM on Server is 48GB. We have...

SQL %Processor Time - Can any body point me in the right direction. I've got a vendor application that I just setup on a...

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

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

Programming : Powershell

French characters into SQL Server - Hi all, I use Powershell to deploy DB scripts. In one script file, I have French accented characters. When I call Invoke-sqlcmd...

How to copy last line of .txt file into new .txt file? - Windows Server 2008 R2 I'm totally new to PowerShell but was trying to use it to solve an issue with data...

SQLServerCentral.com : Anything that is NOT about SQL!

The SQL Saturday Thread - I figured I'd start a thread (I don't think anyone's done so) for all things [url=http://www.sqlsaturday.com/]SQL Saturday[/url]! I'm heading down to...

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

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

External images not working - Hi All, I am using 2008 R2. I am trying to move from embedded to external images but I can not...

SSRS Indicators Expression - Hi geniuses, I'm stuck here with some indicators. Here is my table: [valueType] | [Value] Allocation | 3000000 Value Planned | 5000000 I have to use a 4...

AutoRefresh fails after midnight on reports with date parameter - Hi We use some reports in our NOC which have date parameters with defaults set to the current day. These reports...

Data Warehousing : Integration Services

dynamic columns in flat file destination connection manager? - Hello, In a large database I wish to extract text files with the data from individual tables as well generate the...

One common Question regarding SSIS Interview - Its been while have not given any interview SSIS.Just preparing my future course of action One common questions that comes...

SSIS - Connection manager Variable file name - Hi Guys, I'm new to SSIS & I need some assitance around the Flat File Manager concept. I've designed my whole package and...

SSIS - Connection Problem when outputting to an Excel Binary Workfile (.xlsb) - Hello All, I'm just about trearing my hair out with a Connection problem when I'm writing to an Excel Binary Workfile....

Data Warehousing : Analysis Services

SSAS CUBE PROCESSING ERROR - I am new bie trying to learn SSAS. I have these errors in trying to process my cube after it...