Some SQL, some .NET, and whatever else


View SQL Server table updates per second

When trying to guage the level of database activity, you can use SQL Profiler to view the type and volume of transactions in motion at any given time and to view the overall level of database IO, but you can't use it to directly tell which database tables are being updated.

However, there's a handy dynamic management view called sys.dm_db_index_usage_stats that tells you the number of rows that have been updated in each database index since the instance was last restarted (or since the table/index was created, if that happened more recently):

FROM sys.dm_db_index_usage_stats

The view also has some additional information on index usage, including the number of scans, seeks, and lookups performed on each index - super helpful information if you're looking for unused indexes or which objects are heaviest-hit. If you look at indexes 0 and 1 (zero is the table heap, 1 is the clustered index), you'll see activity on the underlying table data itself.

I needed to see the row updates per second for every table in the database, so rather than run that select over and over (and compare the results), I wrote a quick script to do the comparison repeatedly for me:


-- Remove the working table if it already exists
-- so it doesn't get in the way
IF OBJECT_ID('tempdb..#TableActivity_After') IS NOT NULL
DROP TABLE #TableActivity_After

-- Collect our working data
SELECT object_name(us.object_id) as TableName,
		user_updates as UpdatedRows,
		last_user_update as LastUpdateTime
INTO #TableActivity_After
from sys.dm_db_index_usage_stats us
join sys.indexes si
	on us.object_id = si.object_id
	and us.index_id = si.index_id
where database_id = db_id()
and user_seeks + user_scans + user_lookups + user_updates > 0
and si.index_id in (0,1)
order by object_name(us.object_id)

-- Figure out if we're running it the first time or again
-- Put the data into the correct tables 
IF OBJECT_ID('tempdb..#TableActivity_Before') IS NULL
	-- First time it's being run - stage the existing data
	PRINT 'Initial table usage collected - execute again for changes'

	-- Running script a subsequent time
	-- Compare this set of data to our last set

	-- See how long it's been since we ran this script last
	-- Or at least since last change in any table in the database
   DECLARE @SecondsSince DECIMAL(10,2)
	SELECT @SecondsSince = CONVERT(FLOAT, DATEDIFF(ms, MAX(LastUpdateTime ), GETDATE()))/1000
	  FROM #TableActivity_BEFORE

	SELECT @SecondsSince as 'Seconds since last execution'

	-- Do actual table comparison and give results
	SELECT a.TableName,
		   a.updatedrows - isnull(b.UpdatedRows,0) as RowsUpdated,
		  CONVERT(INT, (a.updatedrows - isnull(b.UpdatedRows,0)) / @SecondsSince) as RowsPerSecond
	 FROM #TableActivity_After a
	 JOIN #TableActivity_Before b
	   ON b.TableName = a.TableName
    WHERE a.updatedrows - isnull(b.UpdatedRows,0) > 0
	ORDER BY RowsUpdated DESC


-- Swap the tables so the AFTER table becomes the new BEFORE
-- Then clean up AFTER table since we'll get a new one next time
IF OBJECT_ID('tempdb..#TableActivity_Before') IS NOT NULL
DROP TABLE #TableActivity_Before

  INTO #TableActivity_Before
  FROM #TableActivity_After

DROP TABLE #TableActivity_After

Running that script the first time will grab an snapshot of table activity. Running it again will tell you what has changed since you ran it the first time, and running it again will continue to tell you (updating the "before" image each time so you're getting an update on only the most recent database activity).

If you wanted to see activity on all database indexes, you could update the query at the top to show index name and remove the "WHERE si.index_id in (0,1)" and you'd see all the index details.

I hope this is helpful - if you have any feedback or would like to see something added, please feel free to leave a comment below!

Download the full script here


Querying Active Directory from SQL Server

SQL Server provides some pretty flexible integration with Active Directory through the ADSI Linked Server provider, something that's present by default when you install SQL Server. If you've never used it before, it allows you to connect to a domain controller and query AD the same way you'd query any other linked server. For example, it gives you the option to:

  • Identify when logins to SQL Servers or databases that support financial applications exist, but have no matching AD account (either direct integrated logins, or if SQL logins or rows in a "User" table have been set up to match the AD login)
  • Kick off alerts to provision the user in various systems based on their AD group membership
  • Automatically trigger an action when a new account appears in active directory (for example, we auto-provision security badges and send an email alert to our head of security to assign the appropriate rights)

While much of this could also be done from Powershell as well, we use the SQL Server Agent to manage many of our scheduled job (because it's so handy to have the agent remotely accessible), as well as sometimes just needing data from AD in a query. To support a number of processes we have in place, we run a synchronization job every so often throughout the day that pulls about two dozen fields for all users and synchronizes them into a table if anything has changed.

Setting up the linked server itself is pretty straightforward (courtesy of

  1. Create the linked server itself
  2. Set the security context (if you want to query AD as something other than the SQL Server Service account - by default, all domain users can do this and it's only required if the domain is remote or if, for some reason, your SQL Service account's AD rights have been restricted, like if you're running as "LOCAL SERVICE")
  3. Enable OPENQUERY (Ad Hoc Distributed Queries)

You'll notice that setting up the linked server itself doesn't actually specify where Active Directory is located or what domain/forest you'll be querying - that's actually done in the query itself. In each query, you'll need to specify the FQDN (Fully-qualified domain name) of the domain (or OU) of the domain you're querying. For example, we'd get all users from a domain by issuing the following query (in this example, "ADLinkedServerName" is the linked server we just created, and our domain is "corp.mycompany.local"):

SELECT EmployeeNumber, Name AS FullName, givenName as FirstName, sn as LastName,
L AS Location, samAccountName as ADAccount
FROM OPENQUERY(ADLinkedServerName,'SELECT Name, L, givenName, sn,
EmployeeNumber, EmployeeID,samAccountName,createtimestamp
FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
WHERE objectClass =''user''') ad

This query will search that OU ("Users", in this case) and everything below it, so changing the FROM to "LDAP://DC=corp,DC=mycompany,DC=local" would fetch the entire directory (for all the "user" objects), regardless of what folder they appeared it - if your directory puts users in another OU (like "Associates", for example), you should adjust the query accordingly.

For column names, you can pull any AD properties at all that you’re looking for – even custom ones that aren't part of a standard AD configuration. To get an easy list of AD properties to choose from, I like using ADSIEDIT (part of Microsoft’s Remote Server Administration Tools - download RSAT for Windows 7 or RSAT for Windows 8.1) – just drill down all the way down to an object, like a user, right click on them and select “Properties”, and you can see a list of all the properties on that account. If you’ve got Domain Admin rights, this tool can be used to modify these values too, but for querying, you only need to be a domain user or somebody who has rights to browse AD. Make a note of the names of particular properties that you're interested in - also note that AD queries are case-sensitive, so you'll need to note the casing of these properties as well.

One potential gotcha that I've run into is that maximum result size that AD will return in a single query can be set as part of domain policy - by default it's 1000 records at once, and can be configured by setting or adjusting the "PageSize" property on your domain controllers (see Also, there's a "MaxResultSetSize" property as well that's set to 256KB by default, but I've never hit it - unless you're pulling every single property back, you'd likely hit the PageSize row limit before you hit the ResultSize byte limit, but remember that both are there. If you do hit the AD result count limit, it will return the rows up to the limit, but then execution stops with a kind of cryptic error:

Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "YOURDOMAIN".

If your domain is larger than the PageSize limit, you'll need to cut your query into multiple return sets of data so you don't exceed the limit on any single query. Since our domain contains about 2400 users, we were able to do it in two queries, broken up like this:

SELECT samAccountName
  FROM OPENQUERY(ADLinkedServerName,'SELECT samAccountName
                                       FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
                                      WHERE objectClass =''user''
                                        AND givenName<''L''') as c
SELECT samAccountName
  FROM OPENQUERY(ADLinkedServerName,'SELECT samAccountName
                                       FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
                                      WHERE objectClass =''user''
                                        AND givenName>=''L''') as c

By dividing the names on L, this cut the directory roughly in half - if yours was larger, you could divide it by querying each OU separately, or by looping through letters of the alphabet, or whatever makes sense in your setting. You could even do something dynamic like pull as many records as you can, then grab the value from the last record you pulled and use it as the baseline to pull the next set as far as you can, and then repeat until you run out of records. Linked servers don’t allow you to dynamically assemble your query at run-time – it has to be hard-coded in the query – but there are some ways around that (like building your OPENQUERY as a string and then executing it via sp_executesql, for example).

Now that you have your AD records stored in a temp table, you can identify new/changed records and merge them into a SQL table you already have ready using an INSERT/UPDATE/DELETE or MERGE statement, or possibly trigger notifications or some other business process.

I hope this is helpful - if you'd like some more detail, please leave a comment and I'm happy to elaborate where it's necessary!


Removing expired/unused SSRS subscriptions

SQL Reporting Services doesn't do a very good job keeping the SQL Agent clean by removing expired or otherwise unusable subscriptions from the job list. To deal with this, we created a script that pulls some details about these old subscriptions, including the report responsible, the last run time and status, and the user who originally scheduled it. If you notice your SQL Agent job list getting excessively long, you can use this query to identify the culprit reports and owners, and then either notify them or remove the old subscriptions manually yourself (run this on the server with your SSRS databases):

  select c.Name as ReportName,
         s.Description as SubscriptionDescription,
         s.LastStatus as LastSubscriptionStatus,
         s.LastRunTime SubscriptionLastRunTime,
            when recurrencetype = 1 then 'One Time'
            when recurrencetype = 2 then 'Hourly'
            when recurrencetype = 4 then 'Daily'
            when recurrencetype = 5 then 'Monthly' 
            when recurrencetype = 6 then 'Month Week' 
            else 'Other'
         end as RecurranceType,
         u.UserName as SubscriptionSetUpBy,
         s.ModifiedDate as SubscriptionLastModifiedDate
    from [ReportServer].[dbo].[Subscriptions] s
    join [ReportServer].[dbo].[Catalog] c
      on c.ItemID = s.Report_OID
    join [ReportServer].[dbo].[Users] u
      on u.UserID = s.OwnerID
    join [ReportServer].[dbo].[reportschedule] rs
      on c.itemid = rs.reportid 
     and s.subscriptionid = rs.subscriptionid
    join [ReportServer].[dbo].[schedule] sch
      on rs.scheduleid = sch.scheduleid
   where s.EventType <> 'RefreshCache'
     and s.LastRunTime < dateadd(m, -3, getdate())
order by

There are a number of similar scripts out there that pull much of this information together, but there wasn't one that collected all the details we were looking for in one place. From here, you can deal with the subscriptions as you see fit.

Note that you can just remove the old subscriptions by brute force if you'd prefer, and SSRS will clean up the orphaned SQL jobs, but I've preferred to review the list and notify users as we've never had too much volume to deal with. If you want to just delete them straight away, you can do so here:

DELETE ReportServer.dbo.Subscriptions
WHERE InactiveFlags != 0
	OR LastRunTime < dateadd(m, -3, getdate())

Exporting from SQL Server to CSV with column names

SQL Server can easily export to CSV file, but it exports just the data, without the column names included. In order to export the column names, you need to actually perform two exports - one with the column names, and one with the data - and then combine the two files into a single file. It populates

You could do this using any query you want - native SQL, a linked server, a stored procedure, or anything else - and the results will export the same way once they're in the temp table. Since it builds the list of column name dynamically as well, you only need to change out the query being executed and set the export location - no other configuration is necessary.

-- Declare the variables
        @DelCMD VARCHAR(4000),
        @HEADERCMD VARCHAR(4000),
        @Combine VARCHAR(4000),
        @Path VARCHAR(4000),
        @COLUMNS VARCHAR(4000)

-- Set values as appropriate
    SET @COLUMNS = ''
    SET @Path = '\\servername\share\outputpath'

-- Set up the external commands and queries we'll use through xp_cmdshell
-- Note that they won't execute until we populate the temp tables they refer to
    SET @CMD = 'bcp "select * from ##OutputTable" queryout "' + @Path + '\Temp_RawData.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
    SET @HEADERCMD = 'bcp "SELECT * from ##cols" queryout "' + @Path + '\Temp_Headers.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
    SET @Combine = 'copy "' + @Path + '\Temp_Headers.csv" + "' + @Path + '\Temp_RawData.csv" "' + @Path + '\MyCombinedFile.csv"'
    SET @DelCMD = 'del "' + @Path + '\Temp_*.csv"'

-- Create and populate our temp table with the query results
  INTO ##OutputTable
  FROM YourSourceTable

-- Generate a list of columns	
   from tempdb..syscolumns c
   join tempdb..sysobjects t
     on =
  where like '##OutputTable%'
  order by colid
-- Run the two export queries - first for the header, then for the data
exec xp_cmdshell @HEADERCMD
exec xp_cmdshell @CMD

-- Combine the two files into a single file
exec xp_cmdshell @Combine

-- Clean up the two temp files we created
exec xp_cmdshell @DelCMD

-- Clean up our temp tables
drop table ##cols
drop table ##OutputTable

If you have any suggestions or run into any issues, please let me know!


Working with bit masks in T-SQL

Decoding bitwise masks can be a bit confusing, so I wanted to share a couple of T-SQL functions I've created to make them easier to deal with. If you'd like to read more about bitmasks and applying them in T-SQL, you can read about it at SQL Fool: T-SQL Bitwise Operators.

The first will return the value in a given bit position of an integer - it accepts two parameters (the lookup value and the bit position) and returns a bit for the value in that position. Note that it starts with position zero, so make sure you're counting correctly:

CREATE FUNCTION dbo.BitwisePosition(
	@value BIGINT,
	@PositionNumber INT

		SET @Mask = POWER(2,@PositionNumber)

	SET @Result = (CASE @value & @Mask WHEN @Mask then 1 else 0 end)

	RETURN @Result


The second function returns a bit (true/false) based on whether a provided bitmask applies to a reference value:

CREATE FUNCTION dbo.BitwiseMask(
	@value BIGINT,


	SET @Result = (CASE @value & @Mask WHEN @Mask then 1 else 0 end)

	RETURN @Result


Don't forget to grant them permissions:


To use these functions, you'd call them as in these examples:

-- Value:   1110001000
-- Position 9876543210
-- Checkpoing position 7, 4, and 0 should return 1, 0, 0
 select dbo.bitwiseposition(904, 7),
		dbo.bitwiseposition(904, 4),
		dbo.bitwiseposition(904, 0)

-- Value:   1110001000 = 904
-- Bitmask: 0010001000 = 136
-- Will return true since mask "fits inside" value
select dbo.bitwisemask(904, 136)

-- Value:   1110001000 = 904
-- Bitmask: 0010001001 = 137
-- false since mask has a bit "outside" value
select dbo.bitwisemask(904, 137)

I hope you find them helpful!


Write permission error when inserting over linked server

I spent some time troubleshoot permissions over a linked server recently before finding out the the cause of my error wasn't permissions-related at all. I was attempting to perform an insert on a remote table, and was getting the following error:

Msg 7344, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI10" for linked server "RemoteServer" could not INSERT INTO table "[RemoteServer].[RemoteDB].[dbo].[IdentInsertTest]" because of column "ID". The user did not have permission to write to the column.

After some time attempting to isolate the missing permissions, I realized that it was actually a disguised error message. I was trying to insert a value into an identity column, but rather than the standard error message I expected to see in that case, I got a generic "You don't have permission" message, leading to some wasted time troubleshooting.

To recreate the issue, you can follow these steps:

-- Create a test table
CREATE TABLE IdentInsertTest (
	SomeValue VARCHAR(10)

-- This insert will succeed
INSERT INTO IdentInsertTest (SomeValue)
SELECT 'Some Value'

-- Will fail with IDENTITY_INSERT error
INSERT INTO IdentInsertTest (ID, SomeValue)
SELECT 10, 'Some Value'

The second statement will fail with the standard error message:

Cannot insert explicit value for identity column in table 'IdentInsertTest' when IDENTITY_INSERT is set to OFF.

Now, connect to another server and set up a linked server to the other instance, and then try these statements again:

-- This remote insert will succeed
INSERT INTO LinkedServer.RemoteDB.dbo.IdentInsertTest (SomeValue)
SELECT 'Some Value'

-- Will fail with a permissions error
INSERT INTO LinkedServer.RemoteDB.dbo.IdentInsertTest (ID, SomeValue)
SELECT 10, 'Some Value'

If I'd realized what I was doing, it would have saved me some troubleshooting time! The moral here is that if your statement fails over a linked server, ensure your user account is set up correctly and then test it locally - you may get a more accurate error message!


Calculating working hours between two dates

As a follow-up to an earlier post (Return a list of all dates between a start and end date), I need to find the number of working hours between two timestamps - in this case, it was to see how long a support ticket had been open before it was initially assigned, but the user didn't want non-work hours to count against them.

To do this, I used the previous script to generate a list of dates and hours, and then marked the rows as work time or not (based on day of week and hour of day, evaluated together). The result was a table that would effectively let me do a SUM to find the value I was looking for. Once I had that table, I could join to it for rows between the two datetimes in question and SUM up rows that had "WorkTime" marked:

SELECT tt.TicketNumber,
       SUM(  CONVERT(INT, wh.WorkTime)) as WorkHoursBeforeAssigned
       COUNT(CONVERT(INT, wh.WorkTime)) as TotalHoursBeforeAssigned
  FROM TroubleTickets tt
  JOIN #WorkingHours wh
    ON wh.EvaluateTime BETWEEN tt.TicketCreateTime
                           AND tt.TicketAssignTime
GROUP BY tt.TicketNumber,

In this case, tickets that were created and picked up after hours, without passing any worktime, would show as zero hours old (as they should, since they were interested in working time) - however, I've also included COUNT here to show total hours as well as work hours.

Also, this script only counts for raw day-of-week and hour-of-day working time - it ignores holidays and other special circumstances. I have a script that tracks holidays (American ones, at least), and I'll put that up shortly as well - if you want to take holidays into account, you could incorporate that into your evaluation.

Here's the script that builds the working time table (you can also download it here):

-- Set things up before we get started
		@WorkTimeEnd		TINYINT,
		@WorkDayOfWeekStart	TINYINT,
		@WorkDayOfWeekEnd	TINYINT

		@EndDate			DATETIME

CREATE TABLE #WorkingHours (
		EvaluateTime	DATETIME,
		IsWorktime		BIT DEFAULT(0)


	SET @WorkTimeStart = 7  --7AM
	SET @WorkTimeEnd   = 16 --4PM hour (4-5PM count as working)
	SET @WorkDayOfWeekStart = 2 --Monday
	SET @WorkDayOfWeekEnd   = 6 --Friday

	SET @StartDate	= '2000-01-01 00:00:00'
	SET @EndDate	= '2020-12-31 23:59:59'


-- Built the list of timestamps we're working with
;WITH numberlist(number)
   AS (SELECT RANK() over(order by c1.object_id,
		 from sys.columns c1
         join sys.columns c2)
INSERT INTO #WorkingHours (EvaluateTime)
SELECT DATEADD(hh, number-1, @StartDate)
  FROM numberlist
 WHERE DATEADD(hh, number-1, @StartDate) <= @EndDate

-- Set the times to worktime if they match criteria
UPDATE #WorkingHours
   SET IsWorktime = CASE WHEN (DATEPART(dw, EvaluateTime)
								BETWEEN @WorkDayOfWeekStart
								AND @WorkDayOfWeekEnd)
							  (DATEPART(hh, EvaluateTime)
							   BETWEEN @WorkTimeStart
							   AND @WorkTimeEnd) THEN 1
						 ELSE 0

-- Retun the results
 SELECT * FROM #WorkingHours
 ORDER BY EvaluateTime

 DROP TABLE #WorkingHours

Clean up vendor names and other data with unwanted numbers/characters

In creating an accounting report, the vendor names we get back from our credit card processor needed some major clean-up: "52334SOUTHWESTAIR1234", "ABD2343-BLUE DINER 843", and so on. I initially found a great function for this from Pinal Dave:

But I wanted to make a few enhancements to it:

  1. He leaves numbers in the string and I'd like to remove them
  2. I'd like to combine multiple spaces in a row into a single space, but leave spaces intact

The first is pretty easy to do - just remove the [0-9] and add a space to the PATINDEX. The second one uses a trick from another post I did a few years ago.

Here's the modified version:

CREATE FUNCTION dbo.UDF_ParseAlphaChars2
   @string VARCHAR(8000)
   DECLARE @IncorrectCharLoc SMALLINT
   SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)

   WHILE @IncorrectCharLoc > 0
      SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
      SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)

   -- Trim groups of spaces into single space
   SET @string = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@string,' ','<>'),'><',''),'<>',' ')))

   RETURN @string

SELECT dbo.UDF_ParseAlphaChars2('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
SELECT dbo.UDF_ParseAlphaChars2('52334SOUTHWESTAIR1234')
SELECT dbo.UDF_ParseAlphaChars2('ABD2343-BLUE DINER 843')

Roll your own lightweight SQL Server source control

I've wanted to implement some kind of source control on my SQL Servers before, but the only product available at the moment is Red-Gate's SQL Source Control, and I didn't need all the functionality it offered (or want to pay for it). Also, it relies on developers checking-in their changes, and that's prone to forgetfulness anyways, as well as leaving your database prone when somebody just changes something in production, without using their development tool - ouch. Sure,  you're protected against accidental drops, but what if somebody tweaks something in production without checking it back in? You're hosed.

All I wanted was a simple process that would run automatically, taking periodic snapshots of the database objects and recording any changes. I decided to roll my own - it's quick, simple, can be set up to run on a schedule, and automatically includes any new databases created on the server without any intervention.

This Stored Procedure goes through the following steps:

  1. If the Master.dbo.coSourceControl table (used to store the history) doesn't exist, it creates it
  2. For each database on the server (so new databases are added automatically), it:
    1. Grabs the text contents of all the user objects (not flagged as "IsMsShipped")
    2. Compares the contents of each to the last known copy (if there is one)
    3. If the object is new or has changed, add a new copy to the source control table in master
  3. Output the number of objects updated
  4. Optionally, it could email somebody to tell them about the results, but it currently does not

The history is kept in a single table - master.dbo.coSourceControl - which has the database it came from, the object_id, the object name, object contents, and the timestamp. Since it uses the object_id to track things, it will also record a name change in an object, even if the contents didn't change.

To implement it, just grab the script and run it in the master database - it will create the stored procedure coSourceControlRefresh. That's it - now either run it on demand, or you can schedule it. It will create the supporting table (if it's missing) and scan every database every time it's run. To see the history for an object, just do:

  SELECT db_name(databaseid) as [Database],
         object_name(objectid) as [Object Name],
    FROM master.dbo.coSourceControl
   WHERE object_name(objectid) LIKE '%The name of some object%'

Restoring a dropped or changed database object should be as simple as running the query above, grabbing the contents of ObjectText you're interested in, and then pasting it in another window and executing it. Bam - previous version of the object restored (and this stored proc should, the next time it runs, see that you've altered the object and record that there's a "new" version of it).

If you run it and like it - or don't like it - please leave a comment to let me know - nothing expected in return, but it's nice to know when people find it useful. I'm happy to make any enhancements you'd like to see. I hope you enjoy it and it's able to save you from the headache of a dropped database object to which you can't find the source!

Download the Source Control database script