Top SQL Server Google Searches For October 2006

These are the top SQL Searches on this site for the month of October. I have left out searches that have nothing to do with SQL Server or programming (Like Jessica Alba Playboy Pics)

Here is the list:

Line 1: Incorrect syntax near 'TRY'.
pivot
execute specific dts steps
"The maximum allowed length of the install path is 36"
ole The provider ran out of memory.
execute dts step active x
El proveedor OLE DB 'SQLOLEDB' indica que el objeto no tiene columnas.
locks
The provider ran out of memory
SQL ADD 1 DAY TO CURRENT DATE
dts activex
xp_sendmail attachments

I always find it interesting to see what people are searching for and it also gives me ideas for things to write about
So I will cover PIVOT and UNPIVOT and SQL ADD 1 DAY TO CURRENT DATE by the end of this week

Later today I will post the top 5 post of October and I will also update the top 10 posts of all time

Download A FREE Trial Of Spotlight On SQL Server And Win A Xbox 360

I just received an email from Quest Software. Quest is giving away 15 Xbox 360™ game systems in 15 days! Download a free trial of Spotlight® on SQL Server Enterprise and you'll be automatically entered into the Quest "Xbox-a-Day Giveaway" drawing.

Now you have a chance to win the Xbox 360™ and detect, diagnose and resolve database performance issues with ease—just by trying Spotlight on SQL Server Enterprise.

What is Spotlight on SQL Server Enterprise?
Spotlight on SQL Server Enterprise is an award-winning database diagnostics tool that can help you ensure data availability and prevent problems before they occur.

Discover, diagnose and resolve SQL Server performance issues

Spotlight on SQL Server Enterprise provides an agent-less easy-to-use database issue discovery solution that enables you to identify and resolve SQL Server performance problems within your SQL Server environment. This powerful tool pinpoints the underlying SQL server contention issues and processes for fast and efficient database administration.

With Spotlight, DBAs can drill down to locate in-depth information about the source of thousands of SQL Server performance problems such as: a specific user, a resource-intensive SQL transaction, an I/O bottleneck, a lock or wait. Spotlight for SQL Server Enterprise sets a baseline for normal activity for each instance, and can set thresholds, notify users and display alerts when it detects performance bottlenecks of any kind.

Spotlight not only monitors the SQL Server Environment, but the underlying operating system on which it resides. From an overview screen, DBAs can view the most active SQL Server sessions, SQL statements, Replication information , blocks, deadlocks, waits, and disk activityto pinpoint and alleviate problems before they occur before and seriously impact end users.


Through one single interface, Spotlight on SQL Server Enterprise answers many questions related to performance:

How is each SQL Server instance performing?
How is the SQL Server Environment as a whole performing?
Where and when are the performance bottlenecks occurring?
How do I catch the problems before they arise?
Who or what is causing the problem and how do I resolve it?

Go HERE for more details

Working Four Days A week Until Next Year

I will be working four days a week only until next year. I did not take a vacation this year since my wife was pregnant with twins. We couldn't go anywhere because the due date was the first week of August; twins usually arrive a couple of weeks earlier. Since we didn't know the exact due date and had to go for monitoring every week we decided not to go anywhere. the end result is that I have 8 (out of 10) vacation days left. So I will be taking wednesdays off until 2007.

I used to live in the Netherlands where everyone has five weeks vacation. So when I came to the United States I was surprised that there was not a law like that here. Besides the five weeks of vacation in Holland you also get 8% of your salary as vacation money. So when you go on vacation you can use that as spending money.

I saved the 'best' for last: if you are unemployed in Holland then you have the same rights. that's right five weeks vacation, 8% vacation money. In Holland you can be unemplyed for years and years, in the US you get 6 months unemployment and the cap is $405 a week. After 6 months you are on your own. I think they should do exactly the same in Holland (no wonder the top tax bracket was 72% in the late nineties; it is I believe 52% now). I knew a lot of people who would be unemployed and work off the books...........

Halloween SQL Teaser

Try to guess without running the code which of the following 3 queries will return a value for maxvalue that is not null
Is it A, B or C?

--A
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT @maxvalue = MAX(crdate)
FROM sysobjects WHERE ID = -99
SELECT 'A' ,@maxvalue maxValue
GO

--B
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT TOP 1 @maxValue = crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'B' ,@maxvalue maxValue
GO

--C
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SET @maxvalue = (SELECT TOP 1 crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'C' ,@maxvalue maxValue
GO

Let me know if you were correct. I must admit I saw this code on Simon Sabin's blog
To understand why this happens read this Tony Rogerson article: T-SQL Value assignment SET vs SELECT

So what does this have to do with Halloween? Nothing really.

One Of The Reasons I Participate In Forums And Newsgroups

This is one of the reasons I participate in SQL Server newsgroups and forums

A person had the following update statement

Update Object_data
set External_Claim_Number = BWCClaimNum
From yson_Claims_eDocs_10_27_2006
where LastName = INJ_lname
and FirstName = INJ_fname
and SSN = inj_ssn
and convert(varchar,injurydate,101) = convert(varchar,claim_injury_date,101)
and convert(varchar,pkclaim) = internal_claim_Number
and BWCClaimNum is not null
and BWCClaimNum <> External_Claim_Number

On the table that got updated (Object_data) the following trigger was created

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER   TRIGGER [tr_Object_Data_IU] ON [dbo].[OBJECT_DATA]
FOR UPDATE,Insert
AS

declare @rows int
declare @object_Data_ID int
set @rows = @@ROWCOUNT
if @rows = 0
    return
if @rows = 1
    begin
    select @object_Data_Id = object_data_id
    from inserted
    goto singlerecord
    end
declare curObject insensitive cursor for
    select object_data_id
    from inserted
open     curObject
fetch next from curObject into @object_data_id
while (@@fetch_status <> -1)
    begin
    singlerecord:
    insert object_datahist (OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,
        INTERNAL_CLAIM_NUMBER,CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,
        DOCUMENT_SUBTYPE,DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,
        YEAR_NUM,DateChanged,ChangedBy,Change
        ,PROV_FAX_NUM)   
        select OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,INTERNAL_CLAIM_NUMBER,
            CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,DOCUMENT_SUBTYPE,
            DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,YEAR_NUM,
            getdate(),user_name(),'U'
            ,PROV_FAX_NUM   
        from    inserted where object_data_id = @object_data_id
    if @rows = 1
        return
   
    fetch next from curObject into @object_data_id
    end
close curObject
deallocate curObject

I suggested that the person did not need the cursor since he is inserting everything anyway into the object_datahist table
I decided to modify the trigger as follows

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER   TRIGGER [tr_Object_Data_IU] ON [dbo].[OBJECT_DATA]
FOR UPDATE,Insert
AS
IF @@ROWCOUNT =0
RETURN

    insert object_datahist (OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,
        INTERNAL_CLAIM_NUMBER,CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,
        DOCUMENT_SUBTYPE,DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,
        YEAR_NUM,DateChanged,ChangedBy,Change
        ,PROV_FAX_NUM)   
        select OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,INTERNAL_CLAIM_NUMBER,
            CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,DOCUMENT_SUBTYPE,
            DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,YEAR_NUM,
            getdate(),user_name(),'U'
            ,PROV_FAX_NUM   
        from    inserted

I also suggested modifying the following line from the update query
and convert(varchar,injurydate,101) = convert(varchar,claim_injury_date,101)
to
and injurydate = claim_injury_date
since the dates are the same anyway


This is the response I got

"I tested removing the cursor driven trigger from the Object_Data table in development and replaced it with the set based up date you provided. Here is what happened:

20,784 records in the test.

1. Using the current trigger I canceled the update after an hour and eleven minutes of running. It had updated 10,218 rows.
2. Using the set based trigger it ran in eleven seconds and generated the correct amount of history.

That's really something to me! Thanks again!"


You see, this is one of the reasons I participate in forums/newsgroups because it feels good to help other people. Another reason is that you can look at the responses from the SQL MVP's and see how they would handle certain situations. I remember running into problems once and I also remembered that I saw this same problem in a newsgroup. after that it's a quick Google and you find your answer

What Are You Watching On Halloween?

I have difficulty picking a movie to watch on Halloween.
Did any of you (my readers) see any of these movies below? Which movie would you pick?


Omen (2006 Edition)
The Gingerdead Man
Saw I
Saw II
Exorcist (Begining)
Seed of Chucky
Ringu
Texas Chainsaw
Silent hill
Hills have eyes
Underworld (evolutions)

Leave me a comment or send me an email by using the Contact Me link.

SQL Server experienced revenue growth of over 30%

Microsoft Corp. today announced first quarter revenue of $10.81 billion for the period ended September 30, 2006, an 11% increase over the same quarter of the prior year. Operating income for the quarter was $4.47 billion, an 11% increase compared with $4.05 billion in the prior year period. Net income and diluted earnings per share for the first quarter were $3.48 billion and $0.35 per share. For the same quarter of the previous year, net income and diluted earnings per share were $3.14 billion and $0.29, including a $0.02 per share charge for certain legal charges.

Server & Tools revenue increased 17% over the comparable quarter in the prior year, reflecting healthy performance for offerings such as SQL Server™ 2005, Windows Server®, Visual Studio® 2005 and BizTalk® Server. SQL Server experienced revenue growth of over 30%, as customers are increasingly deploying SQL Server for their mission critical, transaction-oriented databases.

Good news if you are a SQL Server developer

Enable xp_cmdshell In SQL Server 2005 With The Surface Configuration Tool Or With A Script

This question popped up in a newsgroup today. How do I enable xp_cmdshell in SQL Server 2005? Well you can do it two ways; one is with a script and the other with the Surface Configuration Tool
Let's start with the Surface Configuration Tool

Surface Configuration Tool
You have to navigate to the tool from the start button, the path is below
Programs-->Microsoft SQL server 2005-->Configuration Tools-->Surface Configuration Tool

Then select Surface Area Configuration for Features (bottom one)
Expand Database Engine go all the way down to xp_cmdshell and click enable xp_cmdshell and hit apply


SQL Script
To do it with a script use the one below

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

http://sqlservercode.blogspot.com In Top 100000 On Technorati

This blog is currently ranked 90,981 on Technorati
Rank: 90,981 (65 links from 30 blogs)

Not bad I guess, I wonder how long it will take to reach 50000?
The rank was 150000 just a couple of months ago so it's going towards the top (but will never reach it ;-( )

You can check the ranking here http://www.technorati.com/search/sqlservercode.blogspot.com and see who is linking here

Microsoft Changing Live Search Back To MSN Search

Just saw The Island the other day and I must say I liked it a lot. I don't know why the movie didn't do as well as expected. The stars of the movie are Scarlett Johansson (Lost in Translation, Girl with a Pearl Earring) and Ewan McGregor ( Star Wars: Episode III - Revenge of the Sith , Trainspotting , Moulin Rouge!) other prominent roles are for Sean Bean, Steve Buscemi and Michael Clarke Duncan.. The movie is directed by Michael Bay who did Pearl Harbor and Armageddon. Michael Bay is currently working on The Hitcher and Transformers The links are all to IMDB in case you want more details about any of the movies.

The best part of the movie: the car chase.
The car chase is probably the best ever; it even beats the one from the second Matrix movie.

The movie takes place in the year 2050 and people have clones made as insurance; if their liver gives up then they just take the liver from the clone. These clones live isolated underground and have no idea what's going on; they think that they survived some contamination. Every now and then a lottery takes place and the winner goes to the Island. In reality the winner goes to the operating table. I won't give away the whole story you watch that yourself

There is some major product placement in this movie. The Xbox is featured in the movie alongside MSN Search; that's right not Windows Live Search. I guess they can always release a director's cut and 'fix' that. Cadillac has some top of the line car that costs half a million dollars; How much is half a million in the year 2050? To me it seems that a Porsche Carrera would cost about $500000 in about 50 years. Anyway the 'real' Ewan in the movie, who is very rich complains when his 'very' expensive car gets trashed. Would he really get that upset over the car; his clone is more expensive and he tries to kill the clone.

If you can catch the movie on HBO/On Demand then watch this movie. If you don’t want to watch the whole movie then at least watch the car scene; you won’t regret it

Do You Know How Between Works With Dates?

Do you use between to return data that has dates? Do you know that between will get everything since midnight from the first criteria and up to midnight exactly from the second criteria. If you do BETWEEN 2006-10-01 AND 2006-10-02 then all the values that are greater or equal than 2006-10-01 and less or equal to 2006-10-02 will be returned. So no values after 2006-10-02 midnight will be returned.

Let's test this out, first let's create this table

CREATE TABLE SomeDates (DateColumn DATETIME)

Insert 2 values
INSERT INTO SomeDates VALUES('2006-10-02 00:00:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:00:00.000')

Return everything between '2006-10-01' and '2006-10-02'
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061002'
ORDER BY DateColumn
This works without a problem

Let's add some more dates including the time portion
INSERT INTO SomeDates VALUES('2006-10-02 00:01:00.000')
INSERT INTO SomeDates VALUES('2006-10-02 00:00:59.000')
INSERT INTO SomeDates VALUES('2006-10-02 00:00:01.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:01:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:12:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 23:00:00.000')


Return everything between '2006-10-01' and '2006-10-02'
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061002'
ORDER BY DateColumn
Here is where it goes wrong; for 2006-10-02 only the midnight value is returned the other ones are ignored


Now if we change 2006-10-02 to 2006-10-03 we get what we want
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061003'
ORDER BY DateColumn

Now insert a value for 2006-10-03 (midnight)

INSERT INTO SomeDates VALUES('2006-10-03 00:00:00.000')

Run the query again
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061003'
ORDER BY DateColumn

We get back 2006-10-03 00:00:00.000; between will return the date if it is exactly midnight

If you use >= and < then you get exactly what you need
SELECT *
FROM SomeDates
WHERE DateColumn >= '20061001' AND DateColumn < '20061003'
ORDER BY DateColumn

--Clean up
DROP TABLE SomeDates


So be careful when using between because you might get back rows that you did not expect to get back and it might mess up your reporting if you do counts or sums

SQL Server 2005 Practical Troubleshooting: The Database Engine Sample Chapter Available

Ken Henderson has posted a sample chapter from the forthcoming book SQL Server 2005 Practical Troubleshooting: The Database Engine on his blog. The sample chapter is: Chapter 4 Procedure Cache Issues. This is a must read for anyone who is troubleshooting SQL Server queries and procedures. Some of the things covered are: Cached Object Leaks, Cursor Leaks, Parameter Sniffing, Excessive Compilation, Poor Plan Reuse and How Cache Lookups Work. I think that this is the first time I have seen parameter sniffing described in a book. Check it out and let me know what you think.

SQLQueryStress Beta 1 Available For Download

Adam Machanic has made available beta 1 of the SQLQueryStress tool. It provides some support for query parameterization and options for collection of I/O and CPU metrics. It is not intended to replace tools such as Visual Studio Team System's load tests, but rather to be a simple and easy-to-use tool in the DBA or database developer's kit.

To download the tool and read the documentation go here: http://www.datamanipulation.net/SQLQueryStress/

Visual Studio Team Edition for Database Professionals CTP 6 Is Available For Download

Gert Drapers has announced that CTP6 of Visual Studio Team Edition for Database Professionals is available for download

This is an overview of the highlights in CTP6

• Full support for SQL Server 2000 & 2005 objects, the parser work has been completed
• Extended Properties support, we know import and deploy all your extended properties
• Inline constraint support, if you do not want to separate them out, we allow them inline as well
• Pre- and post deployment scripts population during Import Script
• Full support for command line build & deploy and Team Build
• A new Import Schema Wizard which is also integrated with the New Project Wizard to make project creation and import a single stop shop
• Synchronize your database project from Schema Compare, compare your project with a database and pull the differences in to the database project
• Schema refactoring is now allowed even if you have files in a warning state
• Resolve 3 and 4 part name usage when the referenced database is locally present, same for linked servers
• The product no longer installs SQL Express; you can pick your own SQL Server 2005 Developer Edition or SQL Server 2005 Enterprise Edition instance on the local box. When you first start the product for the first time we will ask you to choose an local instance to use
• Display detailed Schema Object properties in the VS Property Window for selected objects in the Schema View
• Separation of user target database settings through user project files, this allows users to work against a different target instance without changing the main project file.
• We made great progress on the overall stability and performance of the product across the board, project creation, importing your schema, reloading project and making changes to your schema
• And last but not least we fixed many reported customer problems!

Visit The Data Dude blog for more info

SQL Server Database Publishing Wizard CTP 1 released

SQL Server Hosting Toolkit Released
The goal of the SQL Server Hosting Toolkit is to enable a great experience around SQL Server in shared hosting environments.
The toolkit will eventually consist of a suite of tools and services that hosters can deploy for use by their customers. It will also serve as an incubation vehicle for tools that hosting customers can download and use directly, regardless of whether their hoster has deployed the toolkit

Database Publishing Wizard Community Technology Preview 1
The Database Publishing Wizard enables the deployment of SQL Server 2005 databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a SQL script file which can be used to recreate the database in shared hosting environments where the only connectivity to a server is through a web-based control panel with a scripting window.

Scrum

I am having Scrum training tomorrow. So what is Scrum anyway?

Scrum is an agile method for project management. Scrum was named as a project management style in auto and consumer product manufacturing companies by Takeuchi and Nonaka in "The New New Product Development Game" (Harvard Business Review, Jan-Feb 1986). They noted that projects using small, cross-functional teams historically produce the best results, and likened these high-performing teams to the scrum formation in Rugby. Jeff Sutherland, John Scumniotales, and Jeff McKenna documented, conceived and implemented Scrum as it is described below at Easel Corporation in 1993, incorporating team management styles noted by Takeuchi and Nonaka. In 1995, Ken Schwaber formalized the definition of Scrum and helped deploy it worldwide in software development.

Its intended use is for management of software development projects, and it has been successfully used to "wrap" Extreme Programming and other development methodologies. However, it can theoretically be applied to any context where a group of people need to work together to achieve a common goal - such as setting up a small school, scientific research projects or planning a wedding.

Although Scrum was intended to be for management of software development projects, it can be used in running maintenance teams, or as a program management approach: Scrum of Scrums.

Characteristics of Scrum
A living backlog of prioritized work to be done;
Completion of a largely fixed set of backlog items in a series of short iterations or sprints;
A brief daily meeting or scrum, at which progress is explained, upcoming work is described and impediments are raised.
A brief planning session in which the backlog items for the sprint will be defined.
A brief heartbeat retrospective, at which all team members reflect about the past sprint.
Scrum is facilitated by a ScrumMaster, whose primary job is to remove impediments to the ability of the team to deliver the sprint goal. The ScrumMaster is not the leader of the team (as they are self-organising) but acts as a productivity buffer between the team and any destabilising influences.

Scrum enables the creation of self-organising teams by encouraging verbal communication across all team members and across all disciplines that are involved in the project.

A key principle of Scrum is its recognition that fundamentally empirical challenges cannot be addressed successfully in a traditional "process control" manner. As such, Scrum adopts an empirical approach - accepting that the problem cannot be fully understood or defined, focusing instead on maximizing the team's ability to respond in an agile manner to emerging challenges.

Notably missing from Scrum is the "cookbook" approach to project management exemplified in the Project Management Body of Knowledge or Prince2 - both of which have as their goal quality through application of a series of prescribed processes.

This is what the training will cover tomorrow

Creating An Agile Environment (An introduction to Scrum)

Presentation Outline:
I. Definition of Agile
II. Agile Principles and Values
III. Historical Background in Software Development
IV. Overview of Scrum
V. Roles and Responsibilities
VI. Benefits and Challenges in using Scrum
VII. Agile adoption patterns for large organizations
VIII. Questions and Recommended Reading


What is Scrum? Scrum is an iterative, incremental process for developing any product or managing any work. It produces a potentially shippable set of functionality at the end of every iteration. It's attributes are:

• Scrum is an agile process to manage and control development work.
  
• Scrum is a wrapper for existing engineering practices.
  
• Scrum is a team-based approach to iteratively, incrementally develop systems and products when requirements are rapidly changing
  
• Scrum is a process that controls the chaos of conflicting interests and needs.
  
• Scrum is a way to improve communications and maximize co-operation.
  
• Scrum is a way to detect and cause the removal of anything that gets in the way of developing and delivering products.
  
• Scrum is scalable from single projects to entire organizations. Scrum has controlled and organized development and implementation for multiple interrelated products and projects with over a thousand developers and implementers.
  
• Scrum is a way for everyone to feel good about their job, their contributions, and that they have done the very best they possibly could.
  

So my question to you is, do you use Scrum? And if you do then did it improve the process of development? We do use it actually but the developers did not get training yet.

Hello OUTPUT See You Later Trigger Or Perhaps Not?

SQL Server 2005 has added an optional OUTPUT clause to UPDATE, INSERT and DELETE commands, this enables you to accomplish almost the same task as if you would have used a after trigger in SQL Server 2000
Let's take a closer look, let's start with the syntax


The syntax for an insert is like this
INSERT INTO TableNAme
OUTPUT Inserted
VALUES (....)


The syntax for an update is like this
UPDATE TableNAme
SET ......
OUTPUT Deleted,Inserted
WHERE ....

The syntax for a delete is like this
DELETE TableName
OUTPUT Deleted
WHERE ....

So the OUTPUT comes right before the WHERE or the VALUES part of the statement
Also as you can see Insert has Inserted as part of the OUTPUT Clause, Update has Inserted and Deleted and Delete has Deleted as part of the OUTPUT Clause, this is very simmilar to the inserted and deleted pseudo-tables in triggers

Let's test it out, first we have to create a table
CREATE TABLE TestOutput
(
ID INT NOT NULL,
Description VARCHAR(50) NOT NULL,
)

INSERT INTO TestOutput (ID, Description) VALUES (1, 'Desc1')
INSERT INTO TestOutput (ID, Description) VALUES (2, 'Desc2')
INSERT INTO TestOutput (ID, Description) VALUES (3, 'Desc3')


Let's show what we just inserted
INSERT INTO TestOutput (ID, Description)
OUTPUT Inserted.ID AS ID,Inserted.Description AS Description
VALUES (4, 'Desc4')


The * wildcard works also in this case
INSERT INTO TestOutput (ID, Description)
OUTPUT Inserted.*
VALUES (5, 'Desc5')


Let's try it with a delete statement
DELETE TestOutput
OUTPUT Deleted.*
WHERE ID = 4

Let's try to use Inserted here
DELETE TestOutput
OUTPUT Inserted.*
WHERE ID = 4

The message that is returned is this
Server: Msg 107, Level 15, State 1, Line 1
The column prefix 'Inserted' does not match with a table name or alias name used in the query.

So as you can see Inserted can not be used with a DELETE command

Let's try using deleted with an insert command
INSERT INTO TestOutput (ID, Description)
OUTPUT Deleted.*
VALUES (5, 'Desc5')

Same story, Deleted can not be used with an INSERT command
Server: Msg 107, Level 15, State 1, Line 1
The column prefix 'Deleted' does not match with a table name or alias name used in the query.


Let's take a look at the update statement
UPDATE TestOutput
SET ID = 4
OUTPUT DELETED.ID AS OldId, DELETED.Description AS oldDescription ,
Inserted.ID AS New_ID , Inserted.Description AS Newdescription
WHERE ID = 5

Create a table where we will insert our output results into
CREATE TABLE #hist (OldID INT,OldDesc VARCHAR(50),
New_ID INT,NewDesc VARCHAR(50),UpdatedTime DATETIME)

Let's insert the old and new values into a temp table (audit trail..kind of)
UPDATE TestOutput
SET ID = 666
OUTPUT DELETED.ID AS OldId, DELETED.Description AS oldDescription ,
Inserted.ID AS New_ID , Inserted.Description AS Newdescription,GETDATE() INTO #hist
WHERE ID = 3

let's see what we inserted
SELECT * FROM #hist


Remember this message?
Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

You would get this in a trigger when a trigger would fire after a multi row statement and you tried to assign a value to a variable. Since you can't assign values to variable with the OUTPUT clause you won't have this problem


How does identity work with the output clause?
CREATE TABLE TestOutput2
(
ID INT NOT NULL IDENTITY,
Description VARCHAR(50) NOT NULL,
)

INSERT INTO TestOutput2 (Description)
OUTPUT Inserted.*
SELECT 'Desc1' UNION ALL
SELECT 'Desc2'

We can not use SCOPE_IDENTITY() or @@IDENTITY, the value for those will both be 2, run the following example to see this
INSERT INTO TestOutput2 (Description)
OUTPUT SCOPE_IDENTITY(),@@IDENTITY,Inserted.*
SELECT 'Desc3' UNION ALL
SELECT 'Desc4'


The bottom line.
The OUTPUT clause is very useful but I don't think it's a replacement for triggers. A trigger can protect your table for any user (unless you execute a alter table..disable trigger statement)
If you use OUTPUT exclusively, then if someone uses a tool like Enterprise Manager to insert a row into your table there is nothing to fire if you want to create an audit trail for example
So it has it's uses but won't replace the trigger

Saving A DTS Package On Another Server

How do I save a DTS package on another server? I have answered this question so many times in the past year or so that I decided to write a little post about it.

First way: Just save it on the other server
From the location dropdown select SQL server, enter the IP or sever name and supply the credentials and you are done. click on the image below this text to see a larger image




Second way: Save the package as a Structured Storage File
Most of the times you won't be able to save the file directly on the destination server. The server might be in a DMZ and behind a couple of firewalls. In that case you can save the package as a Structured Storage File. From the dropdown select Structured Storage File (see picture below) Give the file a name and save it in a folder. You probably won't be able to save the file on the destination server, save the file on your own machine or on a machine which the destination server can access.


Then to open the package on the other server do this:
Right click on the Data Transformation Services folder, select open packages and navigate to the location where the Structured Storage File has been saved (see picture below)



And that is it.

Datamining Speech In SQL Server 2005

I decided to capture everything that my 2 and a half year old son says, store it in a SQL server 2005 database and run some statistics

This is the outcome in order of most words used
NO
Watch
Movie
Barney
Blues Clues
Dada
Mama
Hungry
Cookies
Chocolate
Icecream

As you can see dada is higher than mama (by a count of one)
So this is interesting, as a child approaches the terrible two’s the most used word is no. The second interesting thing is that watch was used 6300 times and Barney, Movie and Blues Clues 2100 times each. It looks like my son likes his entertainment evenly split. And of course junk food is also high on the list, broccoli is not even in the top 100

So how did I do this? Well I used this tool called Komodo Unnatural Speaking. This is a chip embedded in a sticker. The chip captures all speech and because it’s a RFID tag whenever my son passes a certain spot in the house the data is downloaded to my computer and the chip is cleared. A SSIS package uses Fuzzy Logic to process the data (a toddler’s speech is sometimes gibberish). We get rid of noise words and the results are stored in a table.

Pretty interesting don't you think? ;-)

SQL Teaser/Puzzle Inspired By Hugo Kornelis

Hugo Kornelis has posted an interesting article about using STUFF
The link to that article is here

He has a challenge in that article, the challenge is this:
Create this table

CREATE TABLE BadData (FullName varchar(20) NOT NULL);
INSERT INTO BadData (FullName)
SELECT 'Clinton, Bill' UNION ALL
SELECT 'Johnson, Lyndon, B.' UNION ALL
SELECT 'Bush, George, H.W.';

Split the names into 3 columns (not fields ;-) ), the shortest query wins

Your output should be this:
LastName FirstName MiddleInitial
Clinton Bill
Johnson Lyndon B.
Bush George H.W.


Hugo Kornelis posted a solution by using STUFF

SELECT FullName,LEFT(FullName, CHARINDEX(', ', FullName) - 1) AS LastName,
STUFF(LEFT(FullName, CHARINDEX(', ', FullName + ', ',CHARINDEX(', ', FullName) + 2) - 1), 1, CHARINDEX(', ', FullName) + 1, '') AS FirstName,
STUFF(FullName, 1,CHARINDEX(', ', FullName + ', ',
CHARINDEX(', ', FullName) + 2), '') AS MiddleInitial
FROM BadData

which according to editplus is 340 characters


I replied with this

SELECT FullName,PARSENAME(FullName2,NameLen+1) AS LastName,
PARSENAME(FullName2,NameLen) AS FirstName,
COALESCE(REPLACE(PARSENAME(FullName2,NameLen-1),'~','.'),'') AS MiddleInitial
FROM(
SELECT LEN(FullName) -LEN(REPLACE(FullName,',','')) AS NameLen,
REPLACE(REPLACE(FullName,'.','~'),', ','.') AS FullName2,FullName
FROM BadData) x

my solution is 338 characters, so 2 characters less ;-)

Of course I could have 'cheated' a little by doing this

SELECT FullName,PARSENAME(F,NameLen+1) AS LastName,
PARSENAME(F,NameLen) AS FirstName,
COALESCE(REPLACE(PARSENAME(F,NameLen-1),'~','.'),'') AS MiddleInitial
FROM(
SELECT LEN(FullName) -LEN(REPLACE(FullName,',','')) AS NameLen,
REPLACE(REPLACE(FullName,'.','~'),', ','.') AS F,FullName
FROM BadData) x

And this is 306 characters

So let's see, who can do it in less characters