In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor Check SQL Server performance at a glance
We consulted 1000 SQL Server professionals to make SQL Monitor’s UI as clear as possible. Start monitoring with a free trial.
 
Deployment Manager Deliver .NET and SQL Server updates on time every time
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial.
 
SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
New! Read Lesson 3, ‘Six Scary SQL Surprises’, and learn from Brent Ozar and the other experts in the DBA Team. Read now.

In This Issue

Find Currently Running Scheduled Job

Ever need to automatically stop a long running job? Ever need to find which named Schedule is the currently running version of your job? There's simple code that'll let you find out. More »


Solving Complex T-SQL Problems, Step-By-Step

What should you do if your first, most intuitive solution to a problem ends up scanning the data more than is necessary, resulting in poor performance? Have you missed a new SQL Server feature that can remove inefficiency from your technique? Alternatively, do you need a little help, and some lateral thinking, to open the path to a different approach? Sometimes, the answer is "both". More »


From the SQLServerCentral Blogs - Force installation of 64-bit ACE OLE DB provider

Anyone working with SSIS and Excel probably had the following issue: you are creating an SSIS package using an Excel... More »


From the SQLServerCentral Blogs - Troubleshooting SQL Server Error 15517

Last week I had the following error message repeating over and over again in the SQL Server log of one of... More »


Editorial - Flight Data

I'm getting ready to fly tomorrow, heading to SQL Saturday #175 in Fargo, ND. I have a morning flight, to give me a little time in the afternoon to go see the woodchipper. That's if I don't have any delays. I am planning on working in the airport and on the plane, so I won't be less productive if there are, but I might not get the chance to see the famous machine from the movie.

I'll have a quick turnaround after this trip as well, coming home for a little over 24 hours before I turn around and head to the UK for meetings at Red Gate next week and SQL Bits XI next weekend. I'm hoping that none of the airlines have data issues since those can severely impact my travel times. Last week American Airlines had computer issues, and while United hasn't had any in awhile, my fingers are crossed that this won't be the weekend their systems go down.

We have gotten so computerized with many of our transportation systems that problems can interrupt service, usually just causing annoyances, but there is a potential for lives to be lost. Since we don't often know why the systems fail, it's hard to know to what extend we have poor coding issues, improper secure development, incorrect configuration, employees susceptible to social engineering or something else. We even had a computer failure in space, though I'm sure the astronauts weren't annoyed by the alert.

These days it becomes increasingly hard to roll back to manual processes, not because of the complexity of the systems, but also because the knowledge on how the processes should work is being lost as employees leave the company. This means more delays, but hopefully not important safety rules being ignored or forgotten. I know the airlines have invested heavily in IT, and flights record a lot of data, but I hope they've done so in a distributed fashion. Lots of caching, fault tolerance, and most importantly, communication to allow their operations to proceed when the real world interferes.

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

When running the code below on SQL Server 2008 and above, what will the final query (with the comment "Predict the output of this query") return?

CREATE TABLE dbo.Test
   (PrimKey int NOT NULL,
    ValueCol int NOT NULL,
    PRIMARY KEY (PrimKey)
   );
go
CREATE TRIGGER TestTrig
ON dbo.Test
FOR INSERT
AS
IF (SELECT COUNT(*)
    FROM   inserted
    WHERE  ValueCol < 0) = 1
BEGIN;
  RAISERROR ('Negative values are not allowed!', 16, 1);
  ROLLBACK TRAN;
END;
go
-- Insert attempt #1
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (1, 1);
go
-- Insert attempt #2
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (2, -2);
go
-- Insert attempt #3
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (3, -3), (4, -4);
go
-- Predict the output of this query:
SELECT COUNT(*) FROM dbo.Test;
go
DROP TRIGGER TestTrig;
go
DROP TABLE dbo.Test;
go

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

This question is worth 2 points in this category: Triggers. 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.


Yesterday's Question of the Day

After running the following set of statements, how many rows will be inserted in table dbo.Test (and returned with SELECT)?

CREATE TABLE dbo.Test
(
     Col_1 INT IDENTITY(1,1) PRIMARY KEY
    ,Col_2 INT REFERENCES dbo.Test(Col_1) NOT NULL
);

SET IDENTITY_INSERT dbo.Test OFF;

INSERT INTO dbo.Test (Col_2) VALUES (2);
INSERT INTO dbo.Test (Col_2) VALUES (1);
INSERT INTO dbo.Test (Col_2) VALUES (3);

SELECT * FROM dbo.Test;

Answer: One

Explanation: Table will be created but inserting rows without IDENTITY_INSERT will only be possible if provided value for Col_2 already exists in a table or is matched with next identity value. For newly created table only possible value that can be inserted is 1, so first statement fails. However, even if INSERT fails identity value is consumed, so next INSERT also fails, because of expected identity value is now 2. Third row is inserted successfully.

Ref: http://msdn.microsoft.com/en-us/library/ms186775.aspx

» Discuss this question and answer on the forums


Featured Script

String Character Count

A while ago I once attended an interview where I was asked to write a short script that counts each letter of a given string.  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

SSPI Handshake Error 0x8009030c - Hi to all. I am running XenApp on Windows 2008 R2. The database is hosted on SQL 2005 (9.0.5000). Security...

Read transaction log file - Hi, We have an issue where someone had fired a delete query on a table which did't have any audit...

Memory Issues? Error 18210 and 17189 - We are getting errors on our Production 2005 standalone Server that may indicate issues with Memory? @@version = Microsoft SQL Server...

Looking for a SQL Script to script out security - Good afternoon... I am trying to see if anyone has a script that I could run before restoring a database...

Database Mirroring in SQL 2008 R2 instanct - I believe this error is related to database mirrroring. I have more than 90+ databases configured DB mirroring on a...

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

CPU Usage using T-SQL - Hi, Please help me, how can I get CPU usage in % using T-SQL? Regards - MSR

SQL Server 2005 : Backups

Log backups while Full/Differential backups are running - A few weeks ago I was at an event where someone came up and asked me if it's bad to...

SQL Server 2005 : Business Intelligence

SSIS Package to remove Tab spaces in destination flat file. - Hi, I am creating an ssis package to remove tab's from the source flat file and send it to destination flat...

SSAS converts NULL values into string 'NULL' - SSAS converts empty fields into string text 'NULL'. I found out because my report shows NULL as string value. Running...

SQL Server 2005 : SQL Server 2005 General Discussion

Partitioned views and query compile time. - So I've inherited a SQL Server 2005 DB with a whole bunch of tables in the following format: rank_xxxx, where xxxx...

Create database using Backup file - Hi, I have backup file of database in my local disk. I want to create a that database in another SQL...

SQL Server 2005 : SQL Server 2005 Security

PCI Compliance - Hello, We have a database which needs to be secure in order for PCI compliance. What I am trying to establish...

SQL Server 2005 : SS2K5 Replication

To Many Jobs In Distribution Server - Hi Experts I have a question when I am looking at distribution database I am seeing a lot of job...

Transaction Log Growing due to Pending Transaction (Replication) - SQL Server 2005 SP4 with Transactional replication. I have this Database Log, which keeps growing due to a Pending transaction in...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Locks on tempdb objects - Hi all. I'm calling for your help please. I've a problem in production: there's a transaction which is opened from the business...

SQL Server 2005 : SQL Server 2005 Integration Services

we don´t see packages on MSDB chapter SSIS - hello all: we have a problem with SSIS of sql server 2005 on server with SO windows 2003 SP2, I try...

Conditional SSIS Tasks based on a result set? - I have a source table with data that could go to multiple sources. For example, Apples, Oranges, Kumquats. I could...

Copy a CSV file to another network drive - I am using SSIS 2008. The task is following. 1: Execute a storted proc and using the output create a CSV...

update config table - Hello there, I want to have StartDate and EndDate in config table. I need this so that just in case if...

File System Task > Move Files > Error - Process cannot access the file because its being used by another process - Hi, I have 2 foreach loop containers within my control flow: The first foreach loop container loops through a directory and loads...

SQL Server 2005 : T-SQL (SS2K5)

DDL Trigger to prevent dropping of a specific table? - Hi, I know DDL Triggers can monitor changes on both the Server level and Database level, which could track/prevent changes for...

SQL Server 2005 : SQL Server Newbies

How to display 2D barcodes in SSRS reports - The [url=http://www.avapose.com/dotnet_barcode_reporting_service/2d_barcodes.shtml]SQL Reporting Services 2D Barcode Generator Control[/url] by Avapose.com is able to add 2D barcode generation function into Reporting...

SQL Server 7,2000 : SQL Server Newbies

Help Re Indexing - Hi, I have a 28 GB SQL 2000 Database that is in need of a re index. I am currently...

SQL Server 7,2000 : Replication

Replicating My SQL Server - Dear All, I have to prepare a backup server for my SQL Server, which should be automatically updated as the changes...

SQL Server 7,2000 : T-SQL

Connect By Prior - Hi everyone, I was wondering if anyone knows of a function that can do something similar to Oracle 'Connect By Prior'....

SQL Server 2008 : SQL Server 2008 - General

MODEL database filesizes not being used - Has anyone else noticed that the MODEL database filesizes and autogrowth settings aren't applied to a user database if you...

backup in Logshipping - can we take full backup when the db is configurred logshipping ?

Elimination of Table spools - Whats the best way to eliminateLazy table spools as I have two in a particular query that I'm trying to...

queries on Logins - Hi If I execute the stored procedure multiple times on same day, the first time category should not repeat for the...

Returning datasets in groups of 3 in SQL - I have a reporting services report that needs to generate multiple forms for the same report. Each report generated needs...

Building and shipping applications with SQL Server2008 R2 Express - If I am including sql server 2008 r2 express in my application then i have to signup for free distribution...

Help with "There is insufficient system memory in resource pool 'internal' to run this query" - Hi Experts I am getting this error when Running SSID job please help Hi, We are trying to run an SSIS job...

Backup taking too long after migration to SQL 2008 R2 - Hello, We recently migrated databases from SQL 2005 to SQL 2008 R2. It's a nice shiny hardware, much better than the...

Need Computed Column / Trigger / Function to insert data from another table - Hi, I've got the following Problem, which I'd like to solve in SQL Server 2008: I got a Table 'Products', each product...

Aggressive Indexes - Hi everyone. I used Brent Ozar's excellent sp_blitzindex script to discover the below issue. There is a link in the...

Urgent - Reporting services migration - Subscription jobs did not get created - We are moving to a new data center. We have RS install on appweb server that points to different sql...

Optimizer and stale statistics - The question is, what happens when statistics on NORECOMPUTE set tables go stale? i.e. pass the rowmodctr > (unfiltered rows...

Need to change NDF initial size to reclaim disk space - I've inherited a server with several databases, one which has 7 NDF files. The initial size for most of the...

storing output of a Stored Procedure in a vairable - Hi, I am inserting value in a table Gv_storeAllocation for column STNNo i have created a stored procedure. Can anyone tell...

Language British - I have one of dozen users who is getting the date in US format, this despite running a script to...

Similar strings. - In the past week, I saw somewhere a string similarity function. (I think I saw it in my 'spare' time when...

Script - Script to find the all the db roles of all the users in all the databases in sql 2005\2008

Script to find specific schema_owner for all databases - Hello, Do you know a script to find schema_owner for all databases ? I have this script but I can use it...

Name Parser - I am working with a number of large data extracts (10 - 50 million records) and need to parse full name...

mixed data columns - I currently have a column that have both numeric and characters: Example: 3 PF 11 PF PF 3 3 I am trying...

Help with Triggers - I have to create 3 triggers for a table - INSERT, DELETE, and UPDATE. The triggers will be used to update...

SQL Client Access Licence requirements - Hi SQL 2008R2 Std Ed. We are having an internal debate on what does (does not) require a SQL CAL for the...

Function To Add [n] Days to A Date and Adjust to the Nearest Business Date - Anyone have a ready-made function that will add [x] number of days to a date and then adjust it to...

SQL Server 2008 : T-SQL (SS2K8)

How To Sum... - I Having Following Table Structure.... [code="sql"]Create Table Adding (ID int identity(1,1),Result int,ActualResult int) insert into Adding (Result) values (10),(10),(10),(10),(-10),(-10),(-10),(-10) select * from Adding But My...

Two SQL Staatements different results - Got a feeling this is something to do with an implied cast that I dont understand declare @DataReady INT SELECT @DataReady = 1 if...

Pratical Advantage of Heap - Hi all experts, Is there any practical advantage of using Heap as a storage instead of clustered index for table.

incrementing - I have this table with these info: ID int Firstorder int Secondorder int Thirdorder int sequenceNumber int How would I write a script so that the result would be: Before the script: ID firstorder secondorder thirdorder sequencenumber 1 null null null 1 2 null null null 2 100000...

Set-Based Solution to this Problem? - Hello there! I've got an interesting situation I'm handling at present; I've developed a means of completing the task at...

filter duplicate students via T-SQL - /* I need to filter for duplicate students: --------------------------------------- For same student, if one or both instance/s of IDNo is null then do...

Merge Statement over a linked server - Does anyone know a way of running a MERGE statement across 2 tables over a linked server? Getting the message "The...

SQL Server 2008 : SQL Server Newbies

How to Set value from another sp in SQL - Hi All, I am trying to write an SP but stucked. in a situation I am trying to set sub_serial_no and...

Create an SSIS package without tab's in the destination flat file.. - Hi Everyone, Am new to this form am sorry if am rude. I have to create an SSIS package by taking...

backup permissions?? - We have SQLExpress on one of our production servers. A lot of the information is sensistive so our server team...

Unable to run SSIS package built on 2005 under 2008 - I hope Im on the right forum for this, if not please point this out to me. I have migrated a...

SQL Server rename concern with IIS - We have a windows server which will be renamed shortly. It has SQL Server 2008 R2 and IIS v7.5 installed....

Not all Databases getting backed up - I have a maintenance plan that makes a full backup of 45 databases every night, at least it supposed to....

SQL Server 2008 : SQL Server 2008 High Availability

SQL Service account change - Hi, Log shipping (backup job) failed after we change SQL service account in our production database and DR database server. Earlier...

Restoring System Databases - Hi , Im using an Active/Passive SQL Cluster 2008 R2 Standard Edition. In my task to relocate the System Databases, I messed...

Mirroring DR Test Question - I am in the process of setting up Async db mirroring between principal and mirror (no witness server) We're looking @ testing...

Ownership of cluster disk 'Cluster Disk xxx has been unexpectedly lost by this node. - My Cluster went down again. I don't have to say... I am having a not so good morning already ... :-( Here's...

Slow Disc Access on Single Cluster Node - Hi there, We currently have an issue with one of our SQL 2008 R2 Clusters. The cluster contains 2 virtual nodes...

SQL Server 2008 : SQL Server 2008 Administration

Long running CDC Capture - Hi All, I have a major issue now in my SQL Instance where in I my CDC capture is 48 behind...

What do DBAs need to know about networking technologies? - Hi, Could anyone point me to a reference or two that describes the kinds of networking knowledge (technical not social or...

Excessive sp_executesql usage - My devs seem to use sp_execute even for the execution of SPs. Is there any downside to this dynamic model?

Issue with Linking Servers - This is my current situation. I have job scheduled to run every morning that collects data from various servers to...

Why powershell? - Hi all experts, I have being reading about Powershell a lot this days. What i came to know about powershell is...

Catching culprits of high tempdb growth - Hello, We are facing issues with tempdb on our SQL server 2008 clustered instance. The SQL version is Microsoft SQL Server...

Career : Certification

OPtimization Skills - Hello All.... I am a fresher in an MNC and have been assigned the task of optimization. I am working...

Programming : Connecting

Problems connecting to SQL Server 2012 using Microsoft.SqlServer.ConnectionInfo 9.0.242.0 - We are currently performing a large migration to SQL Server 2012. It's been going well, but we have hit a...

Programming : General

Creating database,stored procedures from batch file - Hello, I am using below code to execute a sql scripts through batch file.....[passing parameters ]. [code="plain"] rem This script executes the scripts...

Programming : SMO/RMO/DMO

Problems connecting to SQL Server 2012 using 2005 SMO - Should 2005 SMO be able to access SQL 2012 please? We are currently performing a large migration to SQL Server 2012....

SQLServerCentral.com : Anything that is NOT about SQL!

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

For security reasons DTD is prohibited in this XML document - Hello All,    I have a very big Dataset which pulls up millions of rows. When I pass parameters from Windows...

Database Design : Disaster Recovery

SQL 2008 R2 Failover Cluster - I'm currently looking into the possibility of configuring a 2008 R2 failover cluster for my organisation. I'm curious as to...

Database Design : Design Ideas and Questions

Schemas vs Several Databases - Hi Folks, I would like to discuss the following design issue: I'm working in a Project in which several systems are involved,...

Data Warehousing : Integration Services

How to avoid technically the null values while concatenating columns - HI, In SSIS by using dervied column i manipulate fields and end up in my desired results. But for an record if...

Problems with SSIS package layout - Using SSIS 2012 I'm having problems with the layout of the packages. Frequently when I click on a task it...

WMI and moving files - Okay, maybe not a data warehousing question, but one for the SSIS geniuses out there. I have a situation where I...

send me dowload link for SSIS - Hi, I need to download and install SSIS in my system.But i am unable to do so.I dint find a right...

Deployment and Execution of packages - Hey all. I am hoping to pull upon the collective genius that frequent these forums, because I am drawing a...

Teradata provider is not listed in the SSIS connection manasger - Hi, We need to retrieve the records from teradata by using SSIS. We installed the teradata provider from the Teradata download site. After...

SSIS package wont write to excel destination - i am adding a part to an existing SSIS package which is to write to an excel destination what i...

Dynamically load CSV files To Sql Server Tables - Hi, I am writing a SSIS package of copying CSV files to SQL tables. Csv files are five in numbers...