SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Vocation or Career

Career: an individual's journey through learning, work, and other aspects of life.

Vocation: an occupation to which a person is specially drawn or for which he or she is suited, trained, or qualified.

Which one describes your current employment situation? How do you feel about the technical work you do? A career is a good thing, and I hope that you all have long, successful careers, I hope you continue to learn and maintain a balance between work and your life outside of your job.

However a vocation is what I'd hope drives you forward. You find some endeavor that you are drawn towards, and at which you have some skill at performing. As much as I never planned to be a technical writer, I find that I am really drawn to it, and I enjoy the work quite a bit. I'd say the same thing about speaking and presenting to groups of technical workers. My employment is more of a vocation, and certainly much more than "just a job."

As you go to work, accomplish tasks and finish projects, think about what you like and don't like. Think about whether you look forward each day to the challenges you face or you dread each hour. If it's the latter, then I hope you are making plans to change something. Find a new employer or position. Life is short and you need to remember that you'll spend a lot of your time at work. Make what you do a vocation and work becomes an exciting piece of your life that enriches your time here.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 1.8MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

The Voice of the DBA 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.

Everyday Jones

Follow Steve Jones on Twitter to find links and database related items and announcements.

ADVERTISEMENT
Backup Questions You're Too Shy to Ask

14 SQL Server Backup Questions You Were Too Shy to Ask

Read Grant Fritchey’s free PDF and get the answers to some of life’s big questions, like, “How do I retrieve a table from the log?” and, “Are SAN backups enough?” Download the free article PDF.

SQL DBA Bundle

The seven tools in the SQL DBA Bundle support your core SQL Server database administration tasks.

Make backups a breeze! Enjoy trouble-free troubleshooting! Make the most of monitoring! Download a free trial now.

SQL Source Control

24% of devs don’t use database source control – make sure you aren’t one of them

Version control is standard for application code, but databases haven’t caught up. So what steps can you take to put your SQL databases under version control? Why should you start doing it? Read more to find out…

Featured Contents

 

Context Is Everything - Check Your Settings

William Talada from SQLServerCentral.com

Check 96 server and database settings to make sure they match your expectations in your databases with this script. More »


 

SQL Saturday #306: Redmond

Press Release from SQL Saturday

On May 31, 2014 SQL Saturday is coming to Redmond, Washington. Come for a free day of SQL Server training and networking, the schedule currently features tracks on T-SQL, SSIS, Backup & Recovery and more. Register while space is available. More »


 

How column COLLATION can affect SQL Server query performance

Additional Articles from MSSQLTips.com

Most DBAs, myself included, install SQL Server with the default server collation SQL_Latin1_General_CP1_CI_AS and all of our table columns get created using this default setting. This tip will look at the performance impacts of querying data with this setting as it compares to querying columns with the collation set to SQL_Latin1_General_CP1_CS_AS. More »


 

From the SQLServerCentral Blogs - Using a Filtered Index to Enforce Filtered Uniqueness

William Assaf from SQLServerCentral Blogs

One of the advantages and uses of Filtered Indexes (introduced with SQL Server 2008) is to provide filtered uniqueness. Obviously... More »

Question of the Day

Today's Question (by Andy Warren):

Pop quiz hot shot - say you're on a bus going 50 miles per hour when the driver asks you for the ranges of the exact number data types in SQL Server. Your eyes glaze, who can remember that stuff? He says, ok hotshot, how about this - tell me the smallest exact number integer data type that can't support a negative number and I'll stop the bus and buy you breakfast.

Can you answer the question, stop the bus, and have a nice omelette?

Note - don't worry about the spelling/abbreviation used for the data types in the answers.

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Data Types.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Microsoft® SQL Server® 2012 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Mounika Parsi):

Assume you have a table "Transactions" in the database "Test". The table "Transactions" has CDC enabled on it. Now we shall create a DDL Trigger on DROP_TABLE event. The trigger code is as follows:

USE Test
CREATE TRIGGER PreventDrop ON DATABASE
    FOR DROP_TABLE
AS
    BEGIN
        IF EXISTS ( SELECT *
                        FROM sys.tables
                        WHERE name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
                                                       'varchar(100)')
                            AND is_tracked_by_cdc = 1 )
            ROLLBACK
    END
What happens if you drop the table "Transactions" ?

Answer: Trigger will not rollback the transaction and the table is dropped

Explanation:

DDL triggers fire only after the DDL statements that trigger them are run. So the table is already dropped when the trigger is fired but it is not committed. The event which fired the trigger and the trigger come under the same transaction. 

As the table is already dropped by the time the trigger is fired, the entry of that table is deleted from sys.tables.

So, the statement

 if exists( select * from sys.tables where name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)') and is_tracked_by_cdc=1)

will not return any rows and the rollback command will not even run.

A simple rollback statement in the trigger code without the if exists statement would have stopped the drop though.

Ref: DDL Triggers - http://msdn.microsoft.com/en-us/library/ms175941%28v=sql.120%29.aspx


» Discuss this question and answer on the forums

Featured Script

vlfs_Count_for_Each_Database

Patrick Akhamie from SQLServerCentral.com

It's preety easy. Just copy and paste it in to the ssms window and run the script. The result will speak for itself. After copying the results, run the last portion (to delete the temp tables) again and make sure they are deleted.

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 2014 : Administration - SQL Server 2014

Read only on secondary Replica - Assume if i have a connection(Application intent readonly) starts with reading, writing and again reading data for a report. how...

Upgrade from SQL 2008 R2 Dev to 2014 didn't go well. - I have SQL Server 2008 R2 Developer Edition installed on my development box. I purchased SQL Server 2014 Developer Edition...

Am using Windows Server 2008R2,In this C:/ Drive Space getting increase - Dear All, I know this is some what get irritate to you,Very sorry for that but i hope that you can...


SQL Server 2014 : Development - SQL Server 2014

Changed behavior or am I missing something? - Hi all, when porting and testing code from 2008/2012 to 2014 I noticed that some queries where significantly slower. Digging into...


SQL Server 2012 : SQL 2012 - General

Design a table to hold filters for selection criteria - I have an ordering database with several tables that store data of orders belonging to a wide variety of clients....

How to create a SQL 2012 Failover cluster using VMs? - Hi thanks before everything!, I have the following question that I hope you can oriented me: How to create a SQL...

Pull the string inside the stored procedure definition - In the below procedure definition, i need to find a way parse the definition and get the list of places...

The Service Broker endpoint is in disabled or stopped state - I have a SQL 2012 Enterprise edition HADR cluster with 2 nodes and my event log on the second node...

many to many relationship - Hi, why do we need a junction table in a many to many relationship? Why can't everything be in just...

CI and NC index on same column is it sugestable ? - Hi, I have a question regarding indexes. If i have cluster and NC index on same column,does it degrade performance on DML...

Missing Index suggested by missing index script - These index came from the query that reported missing indexes, but I’m just trying to get a better understanding and...

T- SQL Query - Hi I have a Table 'A' where column X is of type XML. Following is Column X value [code="xml"] <qsds:Details Xmlns:qsds="http://www.abc.com/Details"> <Category> <Employee EmployeeID="01"> <Name> ...

Parse XML Data to Table format - Hi friends, declare @xml table (xmldata xml) insert @xml select N'<parseObject name="Motel"> <fields> <field name="vehicleno" fieldType="int" fieldSize=""> <grammar> <data><![CDATA[ div.biz-page-subheader li > span.i-phone-biz_details-wrap...

DB containment and systables collation error - Hi I have setup db containment with a different Collation that the TempDB and it works perfectly when using temptables. However,...

SQL 2012 AlwaysOn Group Configuraiton Issue. - Dear Gurus, Hope all is well. i am confronting one error while configuring the SQL 2012 AlwaysOn Group after the...

How to View OR Retrieve all the tabs in SQL. - SQL query windows saves all the tabs in numerical sequence after you close each window. SO for example if you...

SSIS tasks installation - I'm seeking help from a real SSIS expert, which I'm not to any extent. I recently developed an open source project...

What is wait state FFT_NSO_FILEOBJECT - Google is just not helping me. I've been trying to access the file table shares exposed on a server (I...

Encrypting data from Oracle to MS SQL over WAN - I have been tasked to find a solution for Encrypting data being transferred from an Oracle database in one state...


SQL Server 2012 : SQL Server 2012 - T-SQL

Querying XML from SSMS - Hi, I have an xml document that (for this example) I've simplified to look like this: <xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'> <s:Schema id='RowsetSchema'> <s:ElementType name='row' content='eltOnly' rs:updatable='true'> <s:

How to find this year and past year compare in business - We have customers who are new to this year (2014) and there same customers in last year (2013). also there...

complicated sql syntax - looking for explanation for working query - About six months ago, one of the regular SSC forum experts gave me this query to use to build a...

convert parts of string to int - I have log files that I am loading into a table with duration data in the format "xx hrs xx...

If I understand Views VS Functions correctly...I should use views to enapsulated commonly used where clauses? - Some background to my question. I am working with healthcare data and my datasource for Charges is one big transaction...

Help!!! - I need help in creating a temporary table where I can log the QueryName, StartTime, Endtime, Duration and run the...


SQL Server 2008 : SQL Server 2008 - General

Help Needed in Complex Logic - Hi, My table and data: [code="sql"] Create table Sample(Empid int primary key identity(1,1),EmpName varchar(50),Empsalary money,EmpDesignation varchar(30)); insert into Sample values('Jhon',8000,'Manager'), ('Smith',6000,'Lead'), ('Samuel',4000,'AccountExecutive'), ('Simson',4000,'AccountSpecialist'), ('Eric',22000,'Director'), ('Jonathan',12000,'SeniorManager')[/code] Expected

index - Hello, does somebody know query for result about index where procedure or query use this index ?

TSQL dilemma - I have a table with an identifying field that is duplicated. I want to get a single record for this...

How to Script Creation of Multiple Stored Procedures - I have about 50 Stored Procs and UDFs that need to be created in a test database each time the...

Help Needed in BCP Command - Hi, I am trying to export my table data into Textfile using BCP command. Below is my query to do that. [code="sql"]EXECUTE master.dbo.xp_cmdshell...

Auditing Options - I've been asked to research auditing products for SQL Server, currently a mostly 2008 R2 environment. I'm wondering what people...

Maintenance Backup Plan Failing Sporadically - Failed to acquire connection "Local server connection". - Occasionally I'm seeing the following error when tranlog or full backup maintenance plan is executing. Failed:(-1073573396) Failed to acquire connection "Local...

SELECT query help - I have the following data in a table col1 col2 col3 col4 col5 1 122 AAA null null 2 122 null BBB null 3...

Generate XML From SQL Query - My client requirement is to generate XML From Two Tables and make hierarchy with same table at same level. I...

Help required while creating Index - These index came from the query that reported missing indexes, but I’m just trying to get a better understanding and...

SQL Permissions - Hi, If you do not want to assign db_owner or sysadmin rights to a user. But you want them to be...

Full Text search pefromance for long string - Hi, I have two queries related to full text search.In that first one is giving result in 10 sec and second...

How to check IO affinity of SQL server through a query? - Hi Experts, Just wanted to know how to check 'IO affinity' of SQL server through a query? :-):-)

use Of TSql in Xml and Run In Sql server - Hi I can not speak English well i have a table(f1 int) and file xml and save of xml in sql problem: i want use...

SHRINK SQL FILE - http://tinypic.com/view.php?pic=2ly5ym0&s=8#.U2dDemeKDop hi all, i don't understand why file is not shrink after shrink file is made. i used shrink with release...

Error when view design table - I have noticed in my error log below error when i view design table : what should i do to correct...

Operand data type varchar is invalid for subtract operator - Hi, I am using the below query to sum two columns values and I couldn't sum it. I get this error:[b]Operand data...

shrinklog fle - how to shrink the logfile of the database who is configured in replication?

Large database migration best practices - We are planning to move 3-4TB of databases to new environment. Can someone explain some best practices. What should we...

Time Difference Help - Hey everyone, I'm looking to see how long it took someone to complete a task, but I'm unable to figure...

Alter logins rights to user/Login - Hi i want to provide create and alter login rights to user where he can change password of logins.Apart of...

Null value passed in as job parameter - Hello everyone, We have had an interesting issue with one of our production servers lately and I cant really put...

where can I query the 'Enable Mail Profile' value from the Agent properties - Hi All, I'm looking to query (via SQL) the 'Enable Mail Profile' value from the Agent properties, but I've been unable...

Backup questions for 612GB database! - We have a database that is over 612Gb and growing. Our backup is being done on tape using Backup Exec...

SSIS data conversion error - Iam using flat file source,data conversion and oledb destination for data transfer But Iam getting the error [BEM(ETA) [161]] Error: SSIS Error...

SQL Server Read ismore - Insert into Table Select * from anothertable where somecondition in(select some_condition from thirdtable) While doing this operation i see there are more reads...

Word Doc in SQL Server! - Hi All I wish to store a word document in SQL Server. I should be able to search on the content....

Extracting Data From a MySQL Linked Server - Hi all, I am using MS SQL 2008, and I am trying to extract data from a MySQL database. I am...


SQL Server 2008 : T-SQL (SS2K8)

problem in logic for finding a Recursive Cte - hi, hope everybody doing good, here is my problem this is my data [code] DECLARE @Tbl AS TABLE (Mainid INT, Referenceid INT) INSERT INTO...

Delete Table records with Bakup - Hi Team, using below query DELETE FROM Table_name WHERE Date_column < GETDATE() - 30 am able to delete old records morethan 30 days, but i...

Create batch file to selectively run SQL files - I have about 1200 sql files in one of my folders. Almost all of these files do data inserts and...


SQL Server 2008 : SQL Server Newbies

New guy with a newbie question - Hello all, I've played around with Access and SQL statements for some time, not a master by any means, but the...

Union error due to field types - I am trying to run the query below. Between the %% will be certain text that we are looking for. The problem...

Help a complete noob with a project - Hello everyone, i have a project for a datawarehousing class and could really use some help in this. Heres what i...


SQL Server 2008 : SQL Server 2008 High Availability

Cluster resource 'SQL Server' failed - While troubleshooting why a query failed on our SQL Server 2005 cluster, I note in the SQL Server log that...

Mirroring - Intermittent "network name is no longer available" - Is this a network issue? I have a mirroring setup - synchronous with automatic failover. The databases went into Disconnected mode...


SQL Server 2008 : SQL Server 2008 Administration

BAckup's not completing 100% - I have a monthly full backup job in my prod instance serverinstance1. It runs fine and gives me a successful...

KRA for DBA - Can any one give me Key Responsibility Areas for SQL Server DBA ?

DTA problem - Hi, In my Instance Db engine Tuning Advisor is not opening. It's showing initialize MSDB for tuning error message. i have...


SQL Server 2008 : Data Corruption (SS2K8 / SS2K8 R2)

Corruption in D:\$MFT. How may this progress? I don't think CHECKDB is useful here? - Hello I have a server that is reporting NTFS errors; one of the error messages implicates d:\$MFT as being possibly corrupt....


SQL Server 2008 : SQL Server 2008 Performance Tuning

Queries for per database performance - I'm trying to get a handle per-database performance on my servers (SQL 2005 and 2008 R2) to determine candidates for...

writelog wait_type sys.dm_os_wait_stats reasonable number - I found a simple query on the web to evaluate my waitstats for log writes. SELECT wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type...

find out which queries are causing the deadlock - Hi, I occasionally get the following message on our production sql server box. Transaction (Process ID 787) was deadlocked on lock...

Odd Results from an Indexed JOIN Column - Hello there! Starting off with some DDL... [code] CREATE TABLE #AgentSession( [sessionId] [int] IDENTITY(1,1) NOT NULL, [dirSrvSessionGuid] [uniqueidentifier] NOT NULL, [serverId] [int] NOT NULL, [projectId] [int]...


SQL Server 2005 : Administering

SQL Server Agent : Job Failure - Hello, I wish to put the result of a query in Excel then send it to a number of persons at...

Database is in recovery status. - Hi, Database is in recovery status. I dont know why it is showing recovery status. I tried to do detach,offline and drop then...

Implicit conversion from datatype...... - Hi All I get the following error: Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT...


SQL Server 2005 : Backups

SQLServer 2000 Backup on Multiple Disks - I have one of the database on SQL Server 2000. The database is close to 200 GB. Any single local...


SQL Server 2005 : Business Intelligence

Connection Manager - Assign Server Name during Runtime - Hi All, I have a table where I have a list of sql servers in my network. I want to execute...

Questions that were asked during my interview - Hi Friends, I had an interview last week and I was asked few questions "1.What is the difference between OLEDB and...


SQL Server 2005 : Data Corruption

Unable to attach MDF File to sql server - Dear All, Please help, I'm unable to attach mdf file to the sql server . Error Message : An exception occurred while executing...


SQL Server 2005 : Development

SQL Server does not allow remote connections.". - HI, I HAVE LINKED SERVER .... I M EXECUTING A FROM SERVER1 select * from [SERVER2\dev].COMPANYNAME_prod.sys.sysobjects ITS GIVING ERROR: OLE DB provider "SQLNCLI" for linked server...


SQL Server 2005 : SQL Server 2005 General Discussion

TempDB Write Latency - Hi I have been reviewing a fairly large SQL environment and business owners have asked the usual question - why is a...


SQL Server 2005 : SQL Server 2005 Integration Services

Config files priority - I have a package that checked the enable configurations box to enable config files but I don't have a config...

SSIS 2012 - How to Query Excel File Using "Execute SQL Task" - First of all, I don't see a SSIS 2012 forum here, just this SSIS 2005 forum, so I hope it's...

Problem writing to user variable - I can't seem to get this to write to a user variable whose scope is at the package level. If...

Which would be faster, an SSIS Foreach or SQL Cursor for 6300 rows? - I'm currently re-writing an inherited C#/SQL CSV import process due to speed issues with the previous process (reading each line...

Sharepoint List Source Error -A possible reason might be you are trying to retrieve too many items at a time (Batch size) - [b][/b]I am trying to get data from a Sharepoint List using the following CAML Query in SharePoint List Source <Query><Where><Contains><FieldRef Name='Function...


Reporting Services : Reporting Services 2005 Development

problem with Sql _Variant in the code - HI All , Below is my script: USE [DevSalesSecondDrawer] GO /****** Object: StoredProcedure [Neg].[UsersCreateStaticsGet] Script Date: 03/27/2012 09:47:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure...


Reporting Services : Reporting Services 2008 Development

ssrs 2008 tablix should be invisible - In an SSRS 2008 r2 report, I have an existing report that if the grade level is from 01 to...

How to convert the below statement into SSRS expression - Hello, I have an active report code where this.txtHdFiscalYear.Text=reportDataSet.Tables[1].Rows[0]["FiscalYear"].ToString(); this.lblJulCount.Text= Convert.ToString(Convert.ToInt32(this.txtHdFiscalYear.Text) -3) + " Meal Count"; this.lblSepCount.Text= Convert.ToString(Convert.ToInt32(this.txtHdFiscalYear.Text) -2) + " Meal Count"; I am converting this...

Group headers are not repeating with in rectangle with i tablix in visual studio 2012 - Hello, I have a report where I am using group header with school name; I placed textbox with school name inside...

SSRS 2008R@_Reporting Services: Got an error message "Timeout expired" when doing "Request Fields" for a stored procedure. - Hello BI experts, I have successfully ran the sp on SSMS and got the results(about 30 thousands records) and it's fine....


Data Warehousing : Integration Services

How to Create custom error log - Hi All, I am new to ssis ,i want to send customized email notification in the below scenario i am uploading an...


Data Warehousing : Analysis Services

group by clasue in mdx - Hi Friends i have small doubt in mdx query. table having data like below table name : patieninformation pn prcode dos Expected Payment MS0000003 PT001 1/2/2013 29.72 0 MS0000003 PT001 1/21/2013 57.1 0 MS0000003 PT002 6/7/2013 26.69...


SQLServerCentral.com : Anything that is NOT about SQL!

First SQl job were you nervous? - I currently work in a health clinic as a Clinical Applictions Specialist. I do the training, configuring workflows and as...


SQLServerCentral.com : Articles Requested

Execute SQL Queries Across Multiple Servers - Was wondering if i can blog on this 'Execute SQL Queries Across Multiple Servers'

Utilizing dbWarden and ApexSQL Log to monitor a 2008R2 Standard database - Hello -- I took on the responsibility of finding a way to determine if changes were made to a particular database...

Is an Index Being Used? - I am looking for a short piece that queries the DMVs to show which indexes are being used by your...

Deploy to Test - How do you deploy your code changes to test from development? Give us the process and an example.

Write tsqlt tests for stored procedures. - Looking for a number of articles here. Basically for each I'd like to see some type of code that you...

Spackle - Kill SPIDs - A short piece that looks at finding an issue, say a runaway/stuck process, or a blocker, and then killing them.

Saving Execution Plans - Looking for a quick article that shows a user how to run a query, save the execution plan, and then...


SQL Server 7,2000 : Data Corruption

Attach MDF file with no LOG - Greetings, I'm trying to attach a MDF file with no LOG (log file has been deleted). Normally SQLServer creates a new...


SQL Server 7,2000 : Performance Tuning

Cursor - i have a job which is failing my lead wants me to Remove the cursors from the job and modify...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com