An aggregate may not appear in the set list of an UPDATE statement

Posted on 02/20/2011 by ericfickes

Ever seen the error “An aggregate may not appear in the set list of an UPDATE statement” when working with SQL Server? I ran into this one recently after trying to put a COUNT in an UPDATE statement. I was rewriting some legacy code to use a stored procedure, and it turned out to be the perfect case for a Temporary Table.

Instead of boring you with a work scenario, let’s take a simpler one that uses the AdventureWorks database. This example will create a list of sales people, total order count for each person, and store this list a single table variable to be used as the final data table.

Should be three simple steps right?

1. Create @Table variable

[sql]
DECLARE @SalesPeople TABLE
(
EmployeeID int NOT NULL,
SalesPersonID int NOT NULL,
FullName varchar(200) NOT NULL,
Title varchar(200) NOT NULL,
sales_count int NULL default 0
)
[/sql]

2. INSERT sales people into @Table

[sql]
— HACKISH : Match SalesPersonID to EmployeeID, and fill @SalesPeople
INSERT INTO @SalesPeople
( EmployeeID, SalesPersonID, FullName, Title )
SELECT e.EmployeeID, sp.SalesPersonID,
c.FirstName + ‘ ‘ + c.LastName as FullName,
e.Title
FROM Sales.SalesPerson sp,
HumanResources.Employee e,
Person.Contact c
WHERE sp.SalesPersonID = e.EmployeeID
AND e.ContactID = c.ContactID
[/sql]

3. UPDATE @Table with COUNT

[sql]
UPDATE @SalesPeople
SET
sales_count = COUNT( soh.SalesOrderID )
FROM @SalesPeople sp, Sales.SalesOrderHeader soh
WHERE EXISTS (
SELECT DISTINCT SalesPersonID FROM @SalesPeople WHERE SalesPersonID = soh.SalesPersonID
)
AND sp.SalesPersonID = soh.SalesPersonID
[/sql]

Not COUNT allowed in an UPDATE SET statement

The third step is where the original error comes in, so let’s update this to four steps and see how a Table Variable gets through this.

1 & 2 – Repeat from above

3. Create Table Variable of order counts

[sql]
SELECT soh.SalesPersonID, COUNT( soh.SalesOrderID ) AS sales_count
INTO #SalesOrderCounts
FROM Sales.SalesOrderHeader soh
WHERE EXISTS (
SELECT DISTINCT SalesPersonID FROM @SalesPeople WHERE SalesPersonID = soh.SalesPersonID
)
GROUP BY soh.SalesPersonID
[/sql]

4. Update @Table with order counts

[sql]
UPDATE @SalesPeople
SET sales_count = tmp.sales_count
FROM @SalesPeople sp, #SalesOrderCounts tmp
WHERE sp.SalesPersonID = tmp.SalesPersonID
[/sql]

And here’s the full script from start to finish with the table variable in use.

[sql]
— Master table of sales people
DECLARE @SalesPeople TABLE
(
EmployeeID int NOT NULL,
SalesPersonID int NOT NULL,
FullName varchar(200) NOT NULL,
Title varchar(200) NOT NULL,
sales_count int NULL default 0
)

— Match SalesPersonID to EmployeeID, and fill @SalesPeople
INSERT INTO @SalesPeople
( EmployeeID, SalesPersonID, FullName, Title )
SELECT e.EmployeeID, sp.SalesPersonID,
c.FirstName + ‘ ‘ + c.LastName as FullName,
e.Title
FROM Sales.SalesPerson sp, HumanResources.Employee e, Person.Contact c
WHERE sp.SalesPersonID = e.EmployeeID
AND e.ContactID = c.ContactID

— put sales counts into the other kind of #tableVariable
SELECT soh.SalesPersonID, COUNT( soh.SalesOrderID ) AS sales_count
INTO #SalesOrderCounts
FROM Sales.SalesOrderHeader soh
WHERE EXISTS (
SELECT DISTINCT SalesPersonID FROM @SalesPeople WHERE SalesPersonID = soh.SalesPersonID
)
GROUP BY soh.SalesPersonID

— Update our master @table with data from #tableVariable
UPDATE @SalesPeople
SET sales_count = tmp.sales_count
FROM @SalesPeople sp, #SalesOrderCounts tmp
WHERE sp.SalesPersonID = tmp.SalesPersonID

— dump the results
SELECT FullName, Title, sales_count
FROM @SalesPeople

— cleanup
drop table #SalesOrderCounts
[/sql]

Use sys.dm_exec_sessions to disconnect SQL user connections

Posted on 01/03/2011 by ericfickes

<!–[CDATA[

-- init vars
DECLARE @sessID int,
		@dbName varchar(50),
		@userName varchar(50)
SET @dbName	= 'DA413'	-- your database name
SET @userName	= 'DA413'	-- sql user account to look for
-- use a cursor to store all session_ids
DECLARE session_cursor CURSOR
FOR
	SELECT	session_id
	FROM	sys.dm_exec_sessions
	WHERE	original_login_name = @userName
	-- open cursor and grab first row
	OPEN session_cursor
	FETCH NEXT FROM session_cursor INTO @sessID
	-- loop through session_ids
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- kill it
		-- using EXEC because the sproc kill does not like @variables
		EXEC('kill ' + @sessID)
		-- get the next session_id
		FETCH NEXT FROM session_cursor INTO @sessID
	END
-- cursor cleanup
CLOSE session_cursor
DEALLOCATE session_cursor

Permalink

| Leave a comment

2 of 10,388 days remaining #TRON

Posted on 12/15/2010 by ericfickes

<!–[CDATA[

— Using tsql to figure out how long until TRON:LEGACY

DECLARE @tron datetime,

@tron_legacy datetime

SET @tron = ‘7/9/1982 12:00:00’

SET @tron_legacy = ’12/17/2010 12:00:00′

SELECT CAST( DATEDIFF( DD, GETDATE(), @tron_legacy ) as varchar(2) ) + ‘ of’ + CAST ( DATEDIFF( DD, @tron, @tron_legacy ) AS VARCHAR(1000) ) + ‘ days remaining’ as ‘How long until TRON:LEGACY?’

— returns

— 2 of10388 days remaining

Permalink

| Leave a comment

TSQL : How to find all database tables ending with an UPPERCASE X

Posted on 12/08/2010 by ericfickes

<![CDATA[

-- Give me all tables ending with an UPPERCASE X

SELECT	table_name, SUBSTRING( table_name, LEN(table_name), 1 ) as 'end'

FROM	INFORMATION_SCHEMA.TABLES

-- SELECT ascii('X') = 88
-- SELECT ascii('x') = 120
WHERE	ASCII( SUBSTRING( table_name, LEN(table_name), 1 ) ) = 88

Permalink

Selecting random ids using TOP and a CTE

Posted on 09/29/2010 by ericfickes

While testing visualizations in a Flex application, I needed to do some underlying data cleanup in SQL Server. One of my tasks was to manually update an entity table and set the status column to one of three possibilities. Status group A and B both needed to be roughly 20% of my tables total record count, and status group C would be the remaining rows that weren’t touched by status A or status B. Oh and there’s one more thing, the ids in each status group can not be in sequential order, they have to be random.

At first I thought no sweat. My dataset is still small ( only 2000 rows ), so if we want uber control I could do the math and generate my id lists by hand. Yes, hand crafting is possible and under a deadline that kind of logic almost makes sense. However, I already know the table I’m working with will grow in the future, and I’ll probably have to do this data update again, so why not do this right? While playing around with different select statements I had a “EUREKA!” moment. SQL Server’s TOP operator supports PERCENT, not just number. I couldn’t believe it. I use TOP at least once a week and I always forget about TOP PERCENT. Since I already know how to select random rows via CTE, it was time to put it all together.

Before giving you the final SQL, here are the important parts to be familiar with. Also, for the sake of example I’m using the AdventureWorks database so you can play along at home.

TOP PERCENT

If you just need 50% of the rows in a table, but you’re not concerned about the sequence returned, you can fire this query. This will give you a sequential listing of ProductIDs

[sql]
SELECT TOP 50 PERCENT ProductID
FROM Production.Product
ORDER BY ProductID
[/sql]

Which will look something like this.

COMMON TABLE EXPRESSION

Now let’s say you want to randomly pull all rows from a table. This can be achieved using this CTE.

[sql]
WITH data( ProductID ) AS (
SELECT ProductID
FROM Production.Product
)
SELECT ProductID
FROM data
ORDER BY NEWID()
[/sql]

Which will look like this

If you’re looking to randomly select values from a pre-determined list, see my CTE sample here.

So now that you’ve seen TOP PERCENT and CTE in action, it’s time to put these together and solve my initial task of creating randomly selected groups of ids, of a percent size.

RANDOMLY SELECT TOP PERCENT

Putting it all together, here is the query I used to create my first status group.

[sql]
WITH data( ProductID ) AS (
SELECT ProductID
FROM Production.Product
)
SELECT TOP 20 PERCENT ProductID
FROM data
ORDER BY NEWID()
[/sql]

Which gives me a dataset that is 20% of all rows in Production.Product, and the ids are in random order.

And there you have it. Randomly selecting a percent sized data set from a table in SQL Server. The SQL here is really pretty simple, but for some reason I always forget TOP PERCENT. I’m hoping this post will help me remember TOP PERCENT, and maybe even help somebody else with some TSQL.

What if you want to PIVOT against a text column?

Posted on 04/30/2010 by ericfickes

If you’ve ever worked with or researched SQL Server’s PIVOT function, you probably noticed most of the samples pivot against an id column. Typically an int column like EmployeeID, or StoreID. That’s fine and dandy, but what happens when you want to PIVOT against a varchar column? If you’ve been in this need you know this is a bit of a task.

I had this need on an app recently and built a little dynamic sql action that does just this. The example below however, uses the the DatabaseLog table in the AdventureWorks sample database to return a count of Events logged for each Schema. Before jumping into the PIVOT, here’s a simple query that gives you the same information, all Schemas, Events, and Event counts.

SELECT      [Schema], [Event], COUNT( [Event] ) AS 'event_count'
FROM        DatabaseLog
GROUP BY    [Schema], [Event]
ORDER BY    [Schema]

Running this query should give you a long result looking something like this.

While this query returns the same information to you, I don’t like this format as much as using PIVOT. This query result is long and requires a bit of manipulation to get into a readable format.

Now let’s have a look at retrieving the same information using the PIVOT function.

/*
Example of a dynamic PIVOT against a varchar column from the Adventureworks database

References :
PIVOT & UNPIVOT function
http://msdn.microsoft.com/en-us/library/ms177410.aspx

AdventureWorks sample Databases
http://msdn.microsoft.com/en-us/library/ms124501(v=SQL.100).aspx

AdventreWorks.DatabaseLog
http://msdn.microsoft.com/en-us/library/ms124872.aspx
*/

USE AdventureWorks

-- populate temp Event table
SELECT DISTINCT [Event] as 'Event'
INTO	#events
FROM	DatabaseLog

-- this var will hold a comma delimited list of [Event]
DECLARE	@eventList nvarchar(max)

-- create a flattened [Event], list for the PIVOT statement
SELECT	@eventList = COALESCE( @eventList + ', ', '') + CAST( QUOTENAME( [Event] ) AS VARCHAR(1000) )
FROM	#events
ORDER BY [Event]

-- drop table var since our data now lives in @eventList
DROP TABLE #events

-- this var will hold the dynamic PIVOT sql
DECLARE @pvt_sql nvarchar(max)

-- NOTE : we're using dynamic sql here because PIVOT
-- does not support sub SELECT in the 'FOR Event IN ( )'
-- part of the query.
-- If we don't use dynamic SQL here, the PIVOT function
-- requires you to hard code each 'Event'
-- Using SELECT * here so the [Event] columns are auto included
SET @pvt_sql = 'SELECT	*
                FROM
                (
                    SELECT	[Event], [Schema]
                    FROM	DatabaseLog
                ) AS data
                PIVOT
                (
                    COUNT( Event )
                    FOR Event IN
                    ( ' + @eventList + ' )
                ) AS pvt'

-- run the query
EXEC sp_executesql @pvt_sql

Assuming you have the AdventureWorks database installed on your server, running this sql should give you a result looking something like this.

Show all Schemas and count of each Event type

This query result was truncated to fit in this post, but just know the query above creates a column for every Event in the Databaselog table.

A quick explanation of what’s happening in this sql

First you fill a table variable ( #events ) with all Events from DatabaseLog
Next create a comma delimited list of the Events inside of the table variable
Drop the table variable now that we’ve got our delimited list of Events
Build the PIVOT statement as a string so you can inject the Events list
Fire the dynamic SQL via EXEC

Dynamic SQL is something that comes in handy from time to time, but I do my best to only use it if I absolutely have to. In this case we’re using it because the PIVOT function does not allow sub SELECT statements. This is also why we create a specially formatted delimited list of Events prior to building the dynamic sql.

So there you have it, one example of using PIVOT against a varchar column instead of an integer column. Also, this is a pretty good example of a dynamic PIVOT since it’s pretty simple. I hope this makes sense, and if you have any suggestions of better techniques, I’d love to hear it.

Incorrect syntax near the keyword ‘table’ in TSQL

Posted on 04/07/2010 by ericfickes

Ran into something little that I know I’m going to forget if I don’t write down. It appears that when using a TABLE variable in tsql ( SQL Server 2005 ), you must DECLARE that variable on it’s own line, as opposed to inline with your other @variables.

Typically in my sprocs or sql scripts I do my best to have a main DECLARE block and seperate my @variables with a comma like this.

If you're using a TABLE variable, put it on it's own DECLARE line

After some mucking around, it turns out moving the TABLE @variable to it’s own DECLARE line fixes this issue.

DECLARE TABLE @variables on their own line

I haven’t found this info in SQL BOL, so I hope this helps somebody else.

How to TWEET from a SQL CLR Stored Procedure

Posted on 03/03/2010 by ericfickes

Here’s another SQL Server 2005 geek out moment, a CLR SPROC that tweets to Twitter. Big shoutout to Danny Battison for sharing the C# code to post to Twitter. This is what got me started on the C# side of things. Also, you can skip all my ramblings here and just download code here and fire it up. The zip file contains all the source code, the compiled assembly file, and install.sql that shows you how to hook this up.

Being the SQL junky that I am, I was interested in trying out SQL Server’s new CLR Stored Procedures. A CLR sproc is a stored procedure that is able to use .net code that you’ve compiled into an assembly file. For you classic ASP heads out there, think of the ASP page being the sproc, and the .net assembly being your COM object ( cringe, let’s talk about classic ASP ). While there are plenty of great articles on writing CLR stored procedures, I’m going to breeze through the code that makes up this project.

First make a .net class library that will be compiled into an assembly file.

using System;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Net;
using System.IO;
using System.Text.RegularExpressions;

/// <summary>
/// This assembly will be used by a SQL2005 SPROC to communicate
/// with twitter.com
/// </summary>
public sealed class tweetsproc
{
    /*
     * TWITTER CODE BORROWED FROM :
     *  http://www.dreamincode.net/code/snippet2556.htm
     *
     * A function to post an update to Twitter programmatically
     * Author: Danny Battison
     * Contact: gabehabe@hotmail.com
     */

    /// <summary>
    /// Post an update to a Twitter acount
    /// </summary>
    /// <param name="username">The username of the account</param>
    /// <param name="password">The password of the account</param>
    /// <param name="tweet">The status to post</param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "PostTweet")]
    //public static void PostTweet( string username, string password, string tweet)
    public static void PostTweet(   SqlString username,
                                    SqlString password,
                                    SqlString tweet)
    {
        try
        {
            // encode the username/password
            string user = Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(username.ToString() + ":" + password.ToString()));
            // determine what we want to upload as a status
            byte[] bytes = System.Text.Encoding.ASCII.GetBytes("status=" + tweet.ToString());

            // Create a WebPermission.
            WebPermission myWebPermission1 = new WebPermission();

            // Allow Connect access to the specified URLs.
            myWebPermission1.AddPermission(NetworkAccess.Connect,new Regex("http://www\.twitter\.com/.*",
              RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.Singleline));

            myWebPermission1.Demand();

            // connect with the update page
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://twitter.com/statuses/update.xml");

            // set the method to POST
            request.Method = "POST";
            request.ServicePoint.Expect100Continue = false; // thanks to argodev for this recent change!
            // set the authorisation levels
            request.Headers.Add("Authorization", "Basic " + user);
            request.ContentType = "application/x-www-form-urlencoded";
            // set the length of the content
            request.ContentLength = bytes.Length;

            // set up the stream
            Stream reqStream = request.GetRequestStream();
            // write to the stream
            reqStream.Write(bytes, 0, bytes.Length);
            // close the stream
            reqStream.Close();

            // Let's get the Response from Twitter
            var webresp = request.GetResponse();
            // Let's read the Response
            var sread = new StreamReader( webresp.GetResponseStream() );

            // Use SqlContext to return data to the QueryAnalyzer results window
            SqlContext.Pipe.Send( sread.ReadToEnd() );

        }
        catch (Exception exc)
        {
            // send error back
            SqlContext.Pipe.Send(exc.Message);
        }
    }
}

Here’s the app.config for this assembly.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.web>
    <trust level="Full" processRequestInApplicationTrust="true" originUrl="" />
  </system.web>
</configuration>

Once you build this project, you should have your assembly ( tweetsproc.dll ) which will be used by your CLR Sproc. Now it’s time to do some SQL server work.

Enable CLR access for SQL server

EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO

Create the SQL Assembly

CREATE ASSEMBLY tweetsproc_clr_assembly from 'C:UsersericDesktopblogtweetsproc.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

Create your SPROC

CREATE PROC tweetsproc_tweet(	@username as nvarchar(50),
								@password as nvarchar(50),
								@tweet as nvarchar(140)
							)
AS
	-- [Assembly Name].[Class Name].[CLR function Name]
	EXTERNAL NAME tweetsproc_clr_assembly.tweetsproc.PostTweet
GO

Tweet from a sproc

EXEC tweetsproc_tweet 'TwitterUsername', 'TwitterPassword', 'Hey @ericfickes, I''m tweeting from my database too!'

Running this sproc returns the XML response from Twitter.

Tweetsproc returns the full Twitter response

That’s one sample CLR SPROC in the bank! Feel free to download this code and try it out yourself. I’d love to get some feedback on anybody looking to use this for real. While tweeting from a stored procedure probably isn’t a hot topic for anybody, this is a nice teaser for what you can do with CLR sprocs now.

Download code here.

Inside this zip you’ll find this.

install.sql is everything you need to install this on your database
tweetsproc.dll is the twitter assembly used by the sproc
tweetsproc folder is the .net class library project

Everything you need to get TWEETING from a sproc

Does SQL Server Management Studio truncate your results?

Posted on 02/16/2010 by ericfickes

Ever work with query results that are so long SQL Server Management Studio truncates the results?

sproc results are truncated

I ran into this issue recently while running some utility sprocs that generate C# code for me. I was thinking I’d have to refactor my sprocs, but then I found this helpful setting under Query Options.

Update Max.num characters per column in Query Options > Results > Text

This solved my problem and will hopefully solve yours as well.

Right click the query editor
Left click “Query Options…”
Expand Results in the tree on the left ( in popup dialog )
Click on Text under Results
Set “Maximum number of characters displayed in each column” to a number large enough to see all your results.

Quick and easy, hope I remember this setting.

What happens in EXEC, stays in EXEC. Lifespan of a MSSQL table variable

Posted on 02/15/2010 by ericfickes

One of my all time favorite features of MSSQL 2005+ is being able to create table variables on the fly from SELECT statements. This isn’t a lesson in what table variables are, but here is an easy sample in case this is a new concept.

Running this query

SELECT * INTO #myTableVar FROM YourTable

Gives you a new table variable named myTableVar. Table variables are scoped to the active connection, so running this will work.

// make table var
SELECT * INTO #myTableVar FROM YourTable
// show me the data
SELECT * FROM #myTableVar
// you can drop it if you wish
DROP TABLE #myTableVar

However, let’s say you have an aspx page or a sproc that runs this query.

SELECT * INTO #myTableVar FROM YourTable

You can not access myTableVar in a separate connection to the database because as soon as the first query’s connection closes, myTableVar gets dropped. Here are a few other scenarios that also demonstrate the scoping of a table variable.

-- FAILS
EXEC ('SELECT * INTO #tmp FROM MyTable;');
-- #tmp does not exist
SELECT * FROM #tmp

Table variable #tmp lives inside of EXEC

Here we see that the table variable #tmp only lives for the life of the statement inside of EXEC. The second SELECT * calls is outside of the EXEC statement.

-- #tmp2 works inside of EXEC statement
EXEC ('SELECT * INTO #tmp2 FROM MyTable; SELECT * FROM #tmp2');

What happens in EXEC, stays in EXEC

Here #tmp2 works because it’s being used inside of the EXEC statement. This is worth knowing if you work with dynamic sql statements and exec.

-- works!
SELECT * INTO #tmp FROM MyTable;
-- #tmp exists
SELECT * FROM #tmp

typical sample of using mssql table variable

This is a typical example that you may use inside a sproc, trigger, script, etc. Both sql calls live in the same space, so #tmp exists.

Eric Fickes's bloggy blog

Technical Creative, Digital Artist, Skateboarder, Musician

Category Archives: tsql

An aggregate may not appear in the set list of an UPDATE statement

1. Create @Table variable

2. INSERT sales people into @Table

3. UPDATE @Table with COUNT

1 & 2 – Repeat from above

3. Create Table Variable of order counts

4. Update @Table with order counts

Use sys.dm_exec_sessions to disconnect SQL user connections

2 of 10,388 days remaining #TRON

TSQL : How to find all database tables ending with an UPPERCASE X

Selecting random ids using TOP and a CTE

TOP PERCENT

COMMON TABLE EXPRESSION

What if you want to PIVOT against a text column?

Incorrect syntax near the keyword ‘table’ in TSQL

How to TWEET from a SQL CLR Stored Procedure

First make a .net class library that will be compiled into an assembly file.

Here’s the app.config for this assembly.

Enable CLR access for SQL server

Create the SQL Assembly

Create your SPROC

Tweet from a sproc

Does SQL Server Management Studio truncate your results?

What happens in EXEC, stays in EXEC. Lifespan of a MSSQL table variable

1. Create @Table variable

2. INSERT sales people into @Table

3. UPDATE @Table with COUNT

1 & 2 – Repeat from above

3. Create Table Variable of order counts

4. Update @Table with order counts

pass it on >

pass it on >

pass it on >

pass it on >

pass it on >

pass it on >

pass it on >

First make a .net class library that will be compiled into an assembly file.

Here’s the app.config for this assembly.

Enable CLR access for SQL server

Create the SQL Assembly

Create your SPROC

Tweet from a sproc

pass it on >

pass it on >

pass it on >