In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro Have you tried SQL Backup Pro 7 yet?
Get faster, smaller, fully verified backups today. Try SQL Backup Pro 7's automated DBCC CHECKDB and be prepared when you need to restore. Download free trial.
 
SQL Training Learn Agile Database Development Best Practices
Agile database development experts Sebastian Meine and Dennis Lloyd are running day-long classes designed to complement Red Gate’s SQL in the City US tour. Classes will be held in San Francisco, Chicago, Boston and Seattle. Register Now.
 
SQL Monitor The easiest installation of a third party tool, ever!
- Aaron Kolysko, Monsoon Commerce. Get started with SQL Monitor today to gain effortless insights into the health of your own servers - download a free trial

In This Issue

Combining AlwaysOn Groups With Failover Cluster Instances

This article discusses failover cluster instances and AlwaysOn groups More »


SQL in the City - New York 2012

Come join Grant Fritchey, Steve Jones and others for a free day of training in New York City on Sept 28, 2012. More »


SQL Saturday #170 - Munich, Germany

Free SQL Server training comes to Germany. Join fellow SQL Server pros in Munich on Sept 15, 2012. More »


SQL Server Data Type Precedence

I am executing a simple query/stored procedure from my application against a large table and it's taking a long time to execute. The column I'm using in my WHERE clause is indexed and it's very selective. The search column is not wrapped in a function so that's not the issue. What could be going wrong? More »


From the SQLServerCentral Blogs - The Field Museum – SQL in the City 2012 – Chicago

Chicago is a neat down. My first PASS Summit was in Chicago, back in 1999. My wife joined me with... More »


Editorial - Ridiculous

An excellent post from Bruce Schneier, based on a post by Valerie Aurora. Valerie's post just pisses me off. She should never have had to write it. Bruce's is a good reminder that we should recognize the value of a diverse group in our industry. I've written about this before, and unfortunately, I'm not surprised I need to write about it again. This is a male dominated industry, for now, but that doesn't excuse behavior like that talked about at DevCon.

I haven't seen that level of disrespect at any of the SQL conferences and I hope I never do. I have seen women placed in uncomfortable situations, which isn't any better. We are all human, and some of us are attracted to others, which is natural. However in a professional environment, even at a social gathering in the evening, I think that as a man I should be very respectful of the fact that many of the women attending are doing so as colleagues and not anything else.

It's a challenge for women in this industry, and probably for many minority groups. That's one reason I support the Women in Technology events and discussions. Education, understanding, and knowledge of problems is the best way to combat the issues, and ensure you don't make the same mistakes twice. We all know this helps us when working with SQL Server. We should know this will help us in our interpersonal communications as well.

Steve Jones


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:

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


DECLARE @Table AS Table (SlNo INT,Name VARCHAR(500),Salary MONEY NOT NULL)

BEGIN TRY
    BEGIN TRANSACTION        
    INSERT INTO @Table Values(1,'SQL 2005',2005)
    INSERT INTO @Table Values(2,'SQL 2008',NULL)
    INSERT INTO @Table Values(3,'SQL 2012',2012)
    COMMIT TRANSACTION
    SELECT * FROM @Table
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    SELECT * FROM @Table
END CATCH

How many rows will be returned in SQL Server 2008?

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

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

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Yesterday's Question of the Day

It's Labor Day, and an easy question today: what is the name of the Red Gate training events that will be in six cities in the US this fall?

Answer: SQL in the City

Explanation: While those all might be good names, Red Gate and SQLServerCentral are running SQL in the City this fall. These are free, one day training events in the following cities:

Grant Fritchey, Steve Jones and a number of well known speakers will be at various events, including Brent Ozar, Adam Machanic, Jes Borland, Tim Radney, Aaron Nelson and more.

They're free, so register for one today if you'll be one one of those cities on those dates.

» Discuss this question and answer on the forums

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Featured Script

Drop FK For A Specific Table

Drop FK For A Sprecific 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

Security Configuration on SQL2008 Mirror - Hi, Is there a way to see the security configuration of existing mirrored databases? I need to re-mirror these databases and...

A question on rebuilding indexes via table or by index? - I just wondering if there is any difference in rebuilding all the indexes in a database by Option 1 Cycling through all...

Trigger distribution job on completion of snapshot job - Since 30-Aug, the snapshot agent is running for more than 15mins, the interval between the snapshot & subscriber job is 15mins,...

Windows Cluster service account privilege on SQL 2005 cluster - Can anyone provide the list of privileges (Local Security policy) which should be granted to Windows Cluster service account? Below...

How to log in to sql server ??[If Sa disbaled ] - Hi Team, i am unable to log in to sql server, i forget the sa password and my administrator privileges log...

SQL Replication Question - So I currently have transactional replication running from multiple servers A, B, C, -Y (publishers) to one Server Z (Subscriber)....

Log file is growing too much under simple recovery model - In our production database log file growing too much in simple recovery, what cause , in simple recovery every time check...

Temp db log file is growing . - tempdblog file is growing to 5 gb. I dont have more space on the drive. want a immediate solution . without restarting...

Need your advice on an automatica job - Hello, I have a project, it's an asp.net application using SQL 2005 as back end (will soon be upgraded to 2008...

DBA Interview Questions needed - Serious only please. - Hi dudes and dudettes... I've convinced the boss that he needs a dba since the 3rd party firm providing dba services...

xp_delete_file not deleting files on share drive - Hello, xp_delete_file not deleting files on share drive. is there any permission to be given?

Career growth - Production Support SQL Server DBA - I have just started as a Production Support SQL Server DBA . Earlier as I Was working as a development dba. Personally...

SQL Server 2005 : Backups

backup file size - Can anybody help in this matter Original database size [b]100 MB[/b] Backup files created, in scheduled time but size was [b]357...

SQL Server 2005 : Data Corruption

DBCC - Could not repair this error. - Hi guys, one of my customer's hard drive failed. They were able to safe most of the data and create bit...

SQL Server 2005 : SQL Server 2005 General Discussion

Using Parameters in Reporting services 2005 - Hi , i am trying to generate a report based on resource name. I have already added the @Res in my...

Failed to Notify Operator via Email - 1.Database mail is enabled 2.SQL Server Agent's alert system is configured 3.Database mail is enabled, and "send test email" succeeds (to the...

SQL Server 2005 : SQL Server Express

The database is full! - Any one can help me, how to manage this situation if the database is full.::D

SQL Server 2005 : SQL Server 2005 Performance Tuning

Is Database Engine Tuning Advisor's Recommedations are safe to apply on DB - Hi All, I am new to use Database Engine Tuning Advisor for tunning of my company's DataBase Named (ABCData) and I...

Is it possible to move table to different DB without changing connection strings? - Sorry, meant to post in SQL 2008... Would it be possible to move a table to a new database, but still...

What is Update ENVSET_1 Set CTLIDX = '33C49884-A1BF-4899-B2E3-29D45370D3EB' in TextData of SqlProfiler Event - Dear All, I have created trace template to find slow running queries of SQL Server-2005. So i created trace with following events:- ...

SQL Server 2005 : SQL Server 2005 Integration Services

Extracting From Oracle View Returns NULL for VARCHAR2 (4000) Columns - Hi All Wasn't too sure if I should post this in the Oracle forum or SSIS..:ermm: Was wondering if anyone has come...

SQL Server 2005 : SQL Server Newbies

Automate alerts for database blockings. - Hi Everyone, Just want to ask if anyone have "script" or jobs that will automatically check database blocking. I want to...

SQL Server 7,2000 : Data Corruption

REPAIR_ALLOW_DATA_LOSS not resolving the allocation error - hello, this is on sql server 2005. it is for a new client. this may be the first time dbcc checkdb...

How to repair the table - when i running this... DBCC CHECKDB('PMSdb') WITH NO_INFOMSGS Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page...

SQL Server 7,2000 : SQL Server Newbies

Get Month Name - Hi all Is there any best way to get month name in SQL SERVER 2005. I know that we can do...

SQL Server 2008 : SQL Server 2008 - General

Insert from excel to sql using Microsoft.ACE.OLEDB.12.0 - I am trying to create a procedur that inserts data from an excel-file into a table in sql server 2008...

SSIS runs well independently but fails in a Job, probably because of a Script Task - Hello, I'm helping here with a SSIS package. The package loads data, exports it into Excel file, protects the Excel files...

logshipping - how to point application users to new database in logshipping after logshipping failover...

SQL 2008 BI vs SQL 2012 BI - Hi all, Just a little question ... I have ( over the past 18 months ) managed to get my DBA and DBD qualifications...

Is it possible to move table to different DB without changing connection strings? - Would it be possible to move a table to a new database, but still reference it on the old name? We...

Merge replication changing the distributor - I have a client who has a single SQL server that is currently their OLTP database server and is handling...

SQL query to get all predecessors and successors for given node. - Hi All, I have table which holds ID and predecessors ID. One ID can have multiple predecessors. In such scenario I...

Enterprise -> Standard - Hi guys, I have deployed SQL 2008 Enterprise Edition and its now being used successfully by a handful of databases....

Tempdb Drive got corrupt - Hello experts, i am having an instance for which Tempdb database files are located on a separate Drive called (F:) and...

Basic Design Question - I'm trying to model the concept of "teams" of "players". Each team has exactly two players. Intuitively, it would seem...

SQl 2008 change collation of all columns - HI, How can I change collation of all columns in sql 2008. I am getting error for keys. Is there any script to...

SSRS - Cannot Export to Excel - Hey everyone, I'm trying to export a report in report manager and I'm getting an error saying "An error occurred...

Error converting data type nvarchar to numeric - Hi, I have this stored proc that I run nightly. I keep on having issues with it failing because it's...

how to make a search proc for this tables and their conditions ? - here iam having 5 tables for example i have declare 5 tables the @adforum table is used for add a bussiness...

temp table vs data flow task on physical table - Here is the scenario I have one staging table for csv file which is My source I am loading it into...

regarding lsn - does taking tail log back up changes the lsn

SQL Agent - step type = T-SQL or Operating System for stored procedures in different databases - Hi Everyone, My manager has asked me to consolidate several SQL Agent jobs - they do the exact same thing in different...

I can't figure out what's wrong with this syntax - Hello, Error message that I am getting is 'Incorrect syntax near '+'. Select @Years = (2001,2002) INSERT INTO #freddata (ROUTE_NBR , MILELOG , VMT , END_MEASURE , RCLINK...

Can't Kill SPID "Transaction Rollback in Progress" - I have an uncommitted statement in perptual rollback mode in my database. When I try to kill the SPID associated...

db_creator permission not working on SQL Server 2008 r2 - Hi Guys I have created an sql server login called devtest and have given it dbcreator permssions. When I login with the...

Unable to zip the backup data - Hi All, I am unable to zip the backup data. In my server i installed the 7z (zip software). My...

MDF File of MS SQL Server 2008 is Corrupted - Last Night I have a problem due to Power failure actually i have used Microsoft SQL Server 2008 everything is...

Searching stored procedure for table name but ignoring comments - Hi All, Thanks to others who have posted helpful tips I have a way of searching through stored procedures to list...

SQL Server 2008 : T-SQL (SS2K8)

can this code be in a stored proc? - Hello, I have written a stored proc that creates temp tables and uses a table cursor and then drops the temp...

No Data Being Returned? - I'm having a weird issue that I hope someone might be able to shed some light on. I have a...

UNION TWO TABLES WITH ONLY ONE COLUMN IN COMMON - Hi all, I have two tables. One has info about folders and another one about files. Following are the table [code="sql"] DECLARE @Folders TABLE(...

Displays Rows into column type - Helo to all Members of SqlServerCentral.. I cam here first time with a My T-sql problem.. I have a Sql server...

updating the last two spaces of a column - Basically what I am trying to achieve is to update the last two character spaces of a column which is...

fetch latest status of customer - Hi friends, I m having CUST_STATUS table which shows statuscode and effectivedate table.status 1 means customer is active.Customer can deactive,close or...

help on cursor working process - I am using a cursor inside my store procedure. This store procedure is performing some opertaions on thousands of records.. I...

Comparision of a datetime variable against a column of the type nvarchar - Hello All, (Sorry, if I have posted this question already in another topic) I have a table like this: [quote]MinionRpt ( Id [int],...

Generate parent_id using identity column compare name with parent name: Need to help - Actual Table: Id Name Parent_name Parent_id 1 A.B A 2 A.B(1).C B 3 A.B(2).D B 4 A.B.C.D.E D 5 A.B.C.D(1).E D 6 A.B.C.D(2).F D Above table Name column having rows like(A.B,A.B(1).C,A.B.C.D.E,A.B.C.D(1).E,A.B.C.D(2).F) Parent_Name column having rows like(A,B,B,D,D,D) Requirement:...

Generate parent_id using identity column compare name with parent name: Need to help - Actual Table: [u]Id[/u] [u]Name[/u] Parent_name Parent_id 1 A.B A 2 A.B(1).C B 3 A.B(2).D B 4 A.B.C.D.E D 5 A.B.C.D(1).E D 6 A.B.C.D(2).F D Above table Name column having rows like(A.B,A.B(1).C,A.B.C.D.E,A.B.C.

What would be right approach? - I have a table which contains data for 12 months. Now i want to delete the data for the month...

Fiscal Dates - My company wants to create a Financial Calendar Table which contains only the Fiscal Dates. The requirements is ti populate...

using IN statment & a string variable. - Hi, I need some assistance and i've read multiple answers which are very similar but just not sure how to tie...

Unpivot question, struggling with data please help - Have a table with the following structure .. recordid (int), column1a (int), column2a (bit), columns3a (bit), column4a (bit), column1b (int), column2b...

Date Query Format Problem - Hi all, my database has a column called StartDate_Time and the format in this column is like so Wed Sep...

Help with Stored Procedure - I have created a stored procedure that uses a temp table to do some updates. the problem is that the update...

SQL Server 2008 : SQL Server Newbies

Adding process to SQL query - I use the code below to extract a piece of data that is so many along in the delimited string...

Comparision of a datetime variable against a column of the type nvarchar - Hello All, I have a table like this: [quote]MinionRpt ( Id [int], FullName [nvarchar](100), ..., [b]EstDate[/b] [b][nvarchar](100)[/b] )[/quote] If you noticed, the column [EstDate] (Estimated...

Using Access to put data into SQL Server - Hello everyone. So I have a database in SQL Server 2008 that contains 5 tables. I have connected Microsoft Access...

Updating multiple rows for multiple values - Hi, I wasn't sure how to phrase my query so wasn't sure what to search for. I'm attempting the simplest of updates...

Need Help With SQL Query - Have a table with a SSN column that is a float data type. My objective is to obtain the correct...

SQL Server 2008 : SQL Server 2008 High Availability

Mirror did not swap - Dear all, Could you please help me in understanding this issue that happened to me last week. I have the following configuration...

specify service account for Mirroring setup? - Principal & Mirror server - currently SQL service account running local windows users. For the configure mirroring setps error with specify below steps For...

LSAlert job fails in job activity monitor in both DC & DR (Primary & secondary) servers in logshipping - HI, 1. LSAlert job fails in job activity monitor in both DC & DR (Primary & secondary) servers in logshipping 2. While manualy executing...

safely remove rowguid column? - Hi, Recently database was migrated to SQL 2K8R2 from SQL 2K. Already configured replication in SQL2K, replication was removed in SQL 2K...

Removing .NDF files from a SQL Mirror setup - As a test I've migrated data from one file group to another on my principle and been able to successfully...

SQL Server 2008 : SQL Server 2008 Administration

Apply Tlog backup on stand by mode? - Hi, how to Apply Tlog backup on stand by mode? Now database is restoring mode, once change to stand by mode from...

Multiple cached plans for the same stored proc - Hi, When querying sys.dm_exec_procedure_stats, I noticed that some procedures appear multiple times in the result set because they have different cached...

Database blocking - Hi there, I need some advice....I have a select statement that gets blocked by an update statement. When analyzing the stats...

Database Mirroring question? - Hi, Secondary server (mirror server) database under restoring condition and sequence of Log backup restored blongs with Full backup restored Primary server...

Backup taking MUCH longer on prod HELP:) - I have two environments: DEV: VM 4 virtual cores 16 GB Mydatabase 137242.19 MB Attached SAN storage SQL 2008 R2 standard PROD: Physical 32 Core 2 Processor 64 GB RAM Mydatabase...

Career : Certification

Order of exams for MCSE - Just blasted through my 2008 BI exams and very much want to get straight into the 2012 exams. I am looking...

Programming : Powershell

Get-WMIObject Win32_Volume fails on proxy account from Agent Job - It may just be a case of me missing something, but I get unexpected behaviour when running a job as...

SQLServerCentral.com : Anything that is NOT about SQL!

The Beer Cooler Thread - On popular request - at least one! - a dedicated topic about beer. I shall start with my favorite beer: Duvel. It is...

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

SSRS - Rdl Deploy Issue - Custom Code - Hi, I have faced an issue while deploying the rdl to report server for my application. I have a report with...

Line Chart with Conditional Line - Hello, I am having trouble with a line chart I am trying to add to a report. The dataset contains a...

One RDL with multiple data sources - Is it possible to create a single report that will be ran by multiple clients, each with their own data...

Barcode in RDLC Programmatically - How to generate RDLC Barcodes Programmatically........

BarCode Creation - How to Create Postnet code in Rdlc Report (Visual Studio 2010)..

Data Warehousing : Integration Services

Integration Services contain Analysis Services Processing Task. Fails but works in Analysis Services - The dimensions have processed Successfully prior to the updating of the SEN Cubes The attribute key cannot be found when processing:...

Data Flow Task - OLE DB Source missing columns - In my SSIS package I have several data flow tasks that are identical except in the OLE DB Source they...

Data Warehousing : Analysis Services

SSAS Snapshot by Storage - Hi, Did anybody done SSAS Snapshot by Storage? We have HDS storage and we want to create Snapshot evry day after night...

linked server for ssas - hi, any one please tell me about how to create linked server for ssas in sql server management studio2008 in step by...

Microsoft Access : Microsoft Access

Access 2007 front end to SS2K8 problem - My app includes 1) SS2K8 database on the back end 2) Access 2007 front end NOT LINKED, data flows through ADO...