Procedure

First you’re going to need to get makecert.exe and pvk2pfx.exe. Both of these utilities can be downloaded from Microsoft as part of the Windows Driver Kit. I put these utilities on the server on which I was configuring Reporting Services.

Before beginning, you can use the following command (cmd window) to see if there are any SSL Certificate bindings already present:

netsh http show sslcert

If there aren’t any, you should see output like this:

So, on the the Reporting Services server:

1. Open a command prompt, being sure to run as Administrator
2. Run makecert.exe as admin, specifying the certificate and private key filenames.

makecert -r -pe -n "CN=" -eku 1.3.6.1.5.5.7.3.1 -sr localmachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 -sv ".pvk"  ".cer"

You will need to specify a password for the Private Key:


3. Create a PFX file using pvk2pfx.exe, again in the cmd window running as Administrator:

"pvk2pfx.exe" -pvk "<Private Key filename>.pvk" -spc "<Certificate filename>.cer" -pfx "<.pfx filename>.pfx" -po "<password used in the above step>"

4. Run MMC from the Start Menu -> Run
5. Go to File -> Add/Remove Snap-in
6. Choose “Computer Account” -> Next
7. Make sure “Local Computer: (the computer this console is running on)” is checked
8. Expand Trusted Root Certification Authorities on left
9. Right click Certificates -> All Tasks -> Import
10. Select PFX file created above
11. Enter password and check “mark this key as exportable.”
12. Be sure cert is going into the certificate store “Trusted Root Certification Authorities”
13. Repeat steps 8-12, using the “Personal” folder instead of “Trusted Root Certification Authorities”
14. Open up Reporting Services Configuration Manager and connect to the instance for which the SSL cert will be used
15. Navigate to the Web Service URL portion of the manager
16. Click on Advanced
17. Remove HTTP identities
18. Add the SSL Identity by clicking Add and selecting the certificate imported earlier
19. Choose the correct SSL port and IP Addresses and click Ok
20. Do steps 16-19 in the Report Manager URL

That’s it! Now you should be able to run netsh again and get output similar to this:

Problems, Pitfalls, and Errors

I struggled with setting this up, no thanks to some of the errors I was getting. Here’s one of my favorites, thrown during the CreateSSLCertificateBinding method: “An unknown error has occurred in the WMI Provider. Error Code 80070520 —> System.Runtime.InteropServices.COMException (0x80070520): A specified logon session does not exist. It may already have been terminated. (Exception from HRESULT: 0x80070520)”

You get this error when your certificate isn’t quite set up correctly. This is a real pain, considering makecert will happily create a certificate that SSRS cannot subsequently use. So, even though you get a certificate that can be imported via the Certificates snap-in for MMC and that shows up in SSRS Configuration Manager, SSRS cannot actually bind the SSL certificate!

One Experience Troubleshooting Transactions Over Linked Servers

The Backstory

This client had been plagued by poor table and index design, which by way of regular application use, caused logical fragmentation of the indexes to go from under 5% to over 90% in a relatively short amount of time. So I built a solution using Service Broker to check index fragmentation and REORGANIZE or REBUILD where necessary. The indexes are checked for fragmentation weekly. Not long after this solution was in place, I got word that a few indexes were still getting too fragmented, even with weekly maintenance on the indexes. Before implementing the index solution, it would be common for me to manually rebuild the indexes on a table when performance was degraded. Almost always, this first step resolved the performance issue.

The Event

Two principles come to mind when I recount this specific troubleshooting episode:

– Assume nothing
– Remember what tools are in your toolbox, and use them

Usually when we don’t adhere to these principles during troubleshooting, the effort takes much longer than is necessary. Such is the case for me when I was tasked with troubleshooting application timeouts due to long-running UPDATE and SELECT queries. The table in question is a very small table, with 460 total records. I checked BLOCKED_PROCESS_REPORT events and found hundreds of statements waiting on the table’s Primary Key. I’ve seen this a lot lately due to fragmentation, so I checked out logical fragmentation on the table indexes. I like to use the following query to quickly check fragmentation on a specific table in a database.

SELECT i.name, d.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('<TABLE_NAME>'),NULL, NULL, NULL) d
INNER JOIN sys.indexes i ON d.[object_id] = i.[object_id] AND d.index_id = i.index_id
WHERE d.alloc_unit_type_desc = 'IN_ROW_DATA'

In this specific case, the fragmentation wasn’t nearly high enough to raise concerns. At this point, I had prematurely convinced myself that this was a fragmentation issue, even if logical fragmentation wasn’t over 10%. Next I used the sysinternals tool contig to check physical fragmentation on the database data file. contig showed this file to be in 79 fragments, a nod to the days of 1 MB auto-growth. I got permission from the client to drop the database and restore it from backup. Once I did this, contig showed the .MDB was in 1 fragment. The only problem was that this didn’t solve my performance issue. In fact, it had no noticeable effect. Next I thought that maybe the application code had gone awry, submitting the same or similar query against the same record multiple times from multiple SPIDs. This is also something I’ve seen before at this client. So, I decided to fire up SQL Server Profiler to see if that would help figure out the issue.

Sure enough, once I dialed in the trace properties and started the trace, I could see plain as day what was going on. The table in question has a trigger on it that updates a table over a linked server. I started running some test update statements across the same linked server but I didn’t get back any errors. This is when you need to remember that triggers fire in the same transaction as the statement that caused the trigger to fire. The key word here is “transaction.” My test updates were not firing inside of a transaction. As soon as I ran the updates inside of a transaction, I got back errors. Furthermore, it was taking over a minute of execution time before encountering the error. The application timeout setting is at 30 seconds and this is why the developers were getting a .NET timeout error instead of the SQL Server error messages I was getting.

Okay, so I know what’s wrong. It seems the firewall is blocking MSDTC traffic. I used Wireshark to look at the traffic going between the server and the linked server to be updated, filtering out anything that wasn’t MSDTC traffic or traffic to the linked server IP. This gave me a clear picture of what IP the MSDTC traffic was going out on. After checking the firewall on the linked server, I saw the IP where the traffic was coming from wasn’t in the allow list for the MSDTC rule. I added the IP, updated group policies, and BAM! the trigger succeeded.

I came in the next day to reports of the SAME EXACT ISSUE! So what’s going on? The server initiating the linked server update is actually a clustered SQL Server instance running on a 2 node Windows cluster. In addition to this clustered SQL Server instance, the same Windows cluster hosts 3 additional SQL Server clusters. This specific set up has 14 IPs in total. On this particular morning, the MSDTC traffic had switched to a different IP, and was going out over an IP not even associated with the SQL Server instance initiating the update! Furthermore, this IP wasn’t the one associated with the clustered MSDTC service. So, I ended up having to add all 14 IPs to the firewall rule. Problem solved. No new reports of this issue now. Whew.

Now I’m on a quest to understand why MSDTC traffic goes out any old IP! If I find the answer to this, you can bet there will be another blog post on that topic. Until then, enjoy!

Overview

Indexes are a powerful concept in any RDBMS. They can dramatically increase the performance of queries, but they can also become overly burdensome. Enter redundant, unused indexes. Every index is maintained by SQL Server after every INSERT, UPDATE, or DELETE statement. So, if there are indexes on a table that are redundant, and aren’t used to service any SELECT queries, then it is a waste of resources for SQL Server to store and maintain those indexes. This maintenance unnecessarily slows down INSERT, UPDATE, and DELETE statements. So when I found literally dozens of these indexes in each production database in this specific client’s environment, suffice it to say I was shocked.

Naturally, I took it upon myself to delete these indexes. Doing this manually would be extremely time consuming, as there were well over a thousand of these across all production databases. I cobbled together code from my own mind and a few disparate sources across the internet and created a stored procedure to report back to the caller any redundant and unused indexes in the database in which the procedure was run. I created the procedure in the [master] database and marked it as a system object. This way, I only had to deploy the procedure to each instance of SQL Server, and not each individual database. Without further ado, here is the code for the procedure. Below the full procedure code is a discussion of the parts.

Procedure Code

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_RedundantIndexes]
AS 
BEGIN

	SET NOCOUNT ON;

	DECLARE @ID				INT
	DECLARE @DatabaseName	NVARCHAR(128)
	DECLARE @TableName		NVARCHAR(128)
	DECLARE @IndexName		NVARCHAR(128)
	DECLARE @IndexID		INT
	DECLARE @ObjectID		BIGINT
	DECLARE @LastSvcRestart	DATETIME

	CREATE TABLE #indexdetails (
		  ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
		, ServerName NVARCHAR(128)
		, DatabaseName NVARCHAR(128)
		, TableName NVARCHAR(128)
		, IndexName NVARCHAR(128)
		, IndexID INT
		, IndexType NVARCHAR(128)
		, [Object_ID] BIGINT
		, MostRecentDate DATETIME
		, IsPK BIT NULL
		, StatsSum INT NULL
		, IsSubset BIT DEFAULT(0)
		, IsRedundant BIT DEFAULT(0)
		, IsMaster BIT DEFAULT(0)
		, Processed BIT DEFAULT(0)
	)
	
	--Get time of last SQL Server service restart.  Whenever SQL Server is restarted, tempdb is recreated
	SELECT @LastSvcRestart = create_date FROM sys.databases WHERE name = 'tempdb';
	
	IF ( DATEDIFF(HH, @LastSvcRestart, GETDATE()) <= 96 )
	BEGIN
		SELECT TOP 0 ServerName, DatabaseName, TableName, IndexName 
		FROM #indexdetails;
		
		RETURN;
	END

	INSERT #indexdetails (ServerName, DatabaseName, TableName, IndexName, IndexType, IndexID, [Object_ID], MostRecentDate, IsPK)
	SELECT DISTINCT 
		  CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128)) --@@SERVERNAME
		, DB_NAME()
		, o.name AS TableName
		, i.name AS IndexName
		, i.type_desc AS IndexType
		, i.index_id
		, o.[object_id]
		, CASE WHEN o.modify_date > o.create_date THEN o.modify_date ELSE o.create_date END AS MostRecentDate
		, CASE WHEN kc.[type] = 'PK' THEN 1 ELSE 0 END AS IsPK
	FROM		sys.indexes i 
	INNER JOIN	sys.objects o ON i.[object_id] = o.[object_id]
	INNER JOIN	sys.index_columns ic ON i.[object_id] = ic.[object_id] AND i.[index_id] = ic.[index_id]
	LEFT  JOIN	sys.key_constraints kc ON i.[object_id] = kc.[parent_object_id] AND i.name = kc.name
	WHERE o.type_desc <> 'SYSTEM_TABLE'
	ORDER BY o.name, i.name

	WHILE EXISTS(SELECT 1 FROM #indexdetails WHERE processed = 0)	-- Still records to process
	BEGIN
		SELECT TOP 1 
			  @ID = ID
			, @DatabaseName = DatabaseName
			, @TableName = TableName
			, @IndexName = IndexName
			, @IndexID = IndexID
			, @ObjectID = [Object_ID]
		FROM #indexdetails
		WHERE processed = 0

		UPDATE #indexdetails 
		SET		processed = 1 ,
				IsSubset = (SELECT ISNULL(MAX(IndexStatus), 0) FROM (
								SELECT CASE WHEN NOT EXISTS(
											SELECT index_column_id, column_id, is_descending_key, is_included_column FROM sys.index_columns WHERE index_id = @IndexID AND [object_id] = OBJECT_ID(@TableName)
											EXCEPT
											(
												SELECT index_column_id, column_id, is_descending_key, is_included_column FROM sys.index_columns WHERE index_id = @IndexID AND [object_id] = OBJECT_ID(@TableName)
												INTERSECT
												SELECT index_column_id, column_id, is_descending_key, is_included_column FROM sys.index_columns WHERE index_id = t2.index_id AND [object_id] = OBJECT_ID(@TableName)
											)
										)
										THEN 1 ELSE 0 END AS IndexStatus	--1 implies "IsContainedIn" and 0 implies "IndependentOf"
										, t2.index_id
								FROM sys.indexes t2
								WHERE t2.[object_id] = OBJECT_ID(@TableName) AND t2.[index_id] <> @IndexID
								) z),
				IsMaster = (SELECT ISNULL(MAX(IndexStatus), 0) FROM (
								SELECT CASE WHEN NOT EXISTS(
											SELECT index_column_id, column_id, is_descending_key, is_included_column FROM sys.index_columns WHERE index_id = @IndexID AND [object_id] = OBJECT_ID(@TableName)
											EXCEPT
											SELECT index_column_id, column_id, is_descending_key, is_included_column FROM sys.index_columns WHERE index_id = t2.index_id AND [object_id] = OBJECT_ID(@TableName)
											)
										AND NOT EXISTS(
											SELECT index_column_id, column_id, is_descending_key, is_included_column FROM sys.index_columns WHERE index_id = t2.index_id AND [object_id] = OBJECT_ID(@TableName)
											EXCEPT
											SELECT index_column_id, column_id, is_descending_key, is_included_column FROM sys.index_columns WHERE index_id = @IndexID AND [object_id] = OBJECT_ID(@TableName)
										)
										THEN 1 ELSE 0 END AS IndexStatus	--1 implies "IsDuplicate" and 0 implies "NotExactMatch"
										, t2.index_id
								FROM sys.indexes t2
								INNER JOIN #indexdetails n ON t2.index_id = n.indexID AND t2.[object_id] = n.[object_id] AND n.IsMaster = 0
								WHERE t2.[object_id] = OBJECT_ID(@TableName) AND t2.[index_id] <> @IndexID 
							) z)
		WHERE ID = @ID;
	
		;WITH stats AS (
			SELECT s.database_id, s.[object_id], s.index_id, SUM(s.user_seeks + s.user_scans + s.user_lookups) AS StatsSum
			FROM sys.dm_db_index_usage_stats s
			WHERE s.database_id = DB_ID()
			GROUP BY  s.database_id, s.[object_id], s.index_id
		)

		UPDATE i
		SET StatsSum = ISNULL(s.StatsSum,0)
		FROM		#indexdetails i
		LEFT JOIN	stats s ON s.database_id = DB_ID(i.DatabaseName) AND s.[object_id] = OBJECT_ID(i.TableName) AND s.index_id = i.IndexID

		UPDATE #indexdetails SET isRedundant = 1 WHERE isSubset = 1 AND ISNULL(statsSum, 0) = 0 AND ID = @ID

	END
	
	SELECT ServerName, DatabaseName, TableName, IndexName, IndexType
	FROM #indexdetails 
	WHERE	isRedundant = 1
	AND		IsPK <> 1
	AND		IsMaster <> 1

END
GO

Code Guts

Lines 1 – 40: The code in the first 40 lines is very straightforward. The table #indexdetails will store all of the information needed during execution of the stored procedure. The variables will be used when iterating over each record in #indexdetails.

Lines 41 – 50: This code causes the stored procedure to return 0 results if it is run within 4 days of the last restart of SQL Server. For the client’s specific needs, this 4 day restriction is sufficient to ensure that we don’t return indexes that are used less frequently but nevertheless are important. Most people may find that this time frame is far too short. If your organization has reporting queries that only hit an index once a month, quarter, or year, this restriction will need to be adjusted accordingly. When you combine the fact that index use stats are reset at the time of a SQL Server restart and the fact that a query may not use an index but once in a great while, it may not be possible to identify unused indexes. This procedure is not used on databases where this is the case.

Lines 52 – 68: This INSERT statement is straight-forward and populates #indexdetails with basic information on every index in the database (excluding those on system tables).

Lines 70 – 132: These lines are for the WHILE loop that does the majority of work in the procedure and the most interesting pieces are discussed in further detail below. This section iterates over every index listed in #indexdetails one record at a time.

Lines 84 – 98: This subquery uses an additional correlated subquery to check if the current index is a subset of another index on the same table. First index_column_id, column_id, is_descending_key, and is_included_column values are obtained from sys.index_columns for the current index. Next these values are Intersected with the same values for every other index on the same table. This is what makes the inner subquery correlated. The first result is then EXCEPTed from the second result set. If nothing is returned from this EXCEPT statement, then the index in question is a subset of another index on the same table. The idea here is to find any index on the same table that has the same set of values for index_column_id, column_id, is_descending_key, and is_included_column as the current index being analyzed. When we encounter one, we know that the current index can be seen as a subset of another index.

Lines 99 – 115: Like the subquery just discussed, this one has a correlated subquery as well. This subquery checks to see if the current index should be considered a “master” index. Here, master index must be defined in the event that N indexes are identical. This will prevent the procedure from returning all of those indexes as redundant, when only N-1 indexes are redundant. That is, if 3 indexes have identical definitions, then only 2 are redundant, not all 3. This subquery uses EXCEPT but not INTERSECT. EXCEPT is used to retrieve index_column_id, column_id, is_descending_key, and is_included_column values that are present for the current index that are not in any of the other indexes on the same table, checking one index at a time. At the same time, the code looks at index_column_id, column_id, is_descending_key, and is_included_column values that are present in the correlated set (minus any indexes already marked IsMaster = 1) that are not present in the result set for the current index. If both EXECPT queries return empty sets, the current index is marked as the master.

Lines 118 – 128: This query uses a CTE and the DMV sys.dm_db_index_usage_stats to SUM the values for user_seeks, user_scans, and user_lookups. This value is stored as StatsSum in #indexdetails.

Line 130: This UPDATE sets the IsRedundant flag for indexes that have been marked IsSubset = 1 and have no combined user_seeks, user_scans, or user_lookups.

Lines 134 – 138: This simple SELECT returns records to the caller of sp_RedundantIndexes. The records that are returned are ones that are marked as redundant but aren’t Primary Keys and aren’t marked as a master index.

Limitations

There are some limitations to this code. The first and foremost limitation concerns the use stats for the index. If an index is used only rarely, but is critical when it does get used, then this code may incorrectly mark it as unused. That said, only redundant AND unused indexes are returned. This reduces the possibility that the procedure returns a needed index. The other limitation here is that this doesn’t look at primary keys. The reason for this is that the client I built this for often times uses a single column as a primary key, and that column is then duplicated in other indexes that index additional columns, with the primary key column being the leading column. These primary keys may be considered redundant indexes, but because they are primary keys, they should not be dropped, as other tables may have foreign keys pointing back to them.

What’s Next

In Redundant Indexes Part 2, I show a simple Powershell script that I use to take action on the indexes returned by sp_RedundantIndexes.

Alter every stored procedure in a database that fits a given criteria

Recently I had to search for, and update, hard-coding server references for every stored procedure in a database. This amounted to altering over 60 stored procedures. Instead of going through each stored procedure, one-by-one, I used the following code to accomplish the task:

BEGIN TRANSACTION

CREATE TABLE #ProcsToChange (
	ProcName NVARCHAR(MAX) NULL
)

CREATE TABLE #tblHolding (
	ID INT IDENTITY(1,1) NOT NULL,
	proctext NVARCHAR(MAX) NULL
)

INSERT #ProcsToChange (ProcName)
SELECT DISTINCT OBJECT_NAME(sc.id)
FROM syscomments sc
INNER JOIN sysobjects so 
ON sc.id = so.id
WHERE sc.text LIKE '%[OldServer]%'
ORDER BY OBJECT_NAME(sc.id); 

DECLARE @newProcText NVARCHAR(MAX)
DECLARE @proc NVARCHAR(MAX)

DECLARE c CURSOR FOR
SELECT ProcName FROM #ProcsToChange;

OPEN c;
FETCH NEXT FROM c INTO @proc 

WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT #tblHolding (proctext)
	EXEC sp_helptext @proc
	
	PRINT @proc
	SELECT @newProcText = NULL;
	SELECT @newProcText = COALESCE(@newProcText,'') + proctext FROM #tblHolding ORDER BY ID ASC
	SELECT @newProcText = REPLACE(@newProcText, 'CREATE PROCEDURE', 'ALTER PROCEDURE');
	SELECT @newProcText = REPLACE(@newProcText, '[OldServer]', '[NewServer]');

	--PRINT @newProcText;
	EXEC sp_executesql @newProcText;

	TRUNCATE TABLE #tblHolding
	
	FETCH NEXT FROM c INTO @proc
	
END
CLOSE c;
DEALLOCATE c;

DROP TABLE #ProcsToChange
DROP TABLE #tblHolding

--ROLLBACK
COMMIT

In order to grab only the stored procedures fitting your criteria, simply manipulate the WHERE clause that is used to filter data being inserted into #ProcsToChange.

This code should be thoroughly tested in a test environment before using in production. This code is provided as-is, with no warranty whatsoever.

So you want to load a CLR assembly into SQL Server.  What’s that you say?  You DONT want to set your database to TRUSTWORTHY?  Well look no further, there is a much more secure alternative to the old TRUSTWORTHY fallback: sign your assemblies with a secure certificate.

While this sounds like a walk in the park, it’s not very straight-forward.  In this post I will walk you through the steps necessary to get up and running with a signed assembly.  First you’ll need some additional tools.  You’ll need the makecert.exe tool, which is part of the Microsoft Windows SDK.  Also needed are the signtool and pvk2pfx tool, both of which are also part of the SDK.  You can download the Windows SDK here: http://go.microsoft.com/fwlink/?linkid=84091.

Once you’ve downloaded and installed the SDK, you’re ready to begin.  First, create the root authority certificate:

makecert -sv SignRoot.pvk -cy authority -r signroot.cer -n "CN=Cert Authority"

Once you’ve created this certificate, use it to create an additional certificate and private key pair. This is what will be loaded into SQL server. It is also what you will use to sign compiled DLL files.

makecert -m 360 -n "CN=Signing Cert" -iv signroot.pvk -ic signroot.cer -cy end -pe -sv signcert.pvk signcert.cer

Now convert this certificate into a PFX file for signing assemblies:

pvk2pfx -pvk signcert.pvk -spc signcert.cer -pfx signcert.pfx

Once you have the PFX file, you can use it to sign assemblies:

signtool sign /f signcert.pfx "YOUR_DLL_HERE"

Load the certificate file created above into SQL Server. Be sure to use the .cer file:

USE [master];
GO

CREATE CERTIFICATE CLRCertificate
FROM FILE = 'signcert.cer'
WITH PRIVATE KEY (
	  FILE = 'signcert.pvk'
	, DECRYPTION BY PASSWORD = 'password'
	, ENCRYPTION BY PASSWORD = 'password'
)

Now that the certificate has been loaded into SQL Server, you need to create a login from that certificate. This login will need to have the necessarey assembly permissions (unsafe, external_access).

CREATE LOGIN AssemblyLoader FROM CERTIFICATE CLRCertificate
REVOKE connect sql FROM AssemblyLoader 
GRANT external access assembly TO AssemblyLoader 
GRANT unsafe assembly TO AssemblyLoader 

There you have it, the assembly is successfully loaded into SQL Server without compromising security by using the TRUSTWORTHY flag. Enjoy.

Today I had the pleasure of creating a linked server on our main production SQL Server 2005 instance. The catch is that the server I needed to link was a SQL Server 7 instance. Here are the steps I took to get the linked server up and running in under 5 minutes flat:

1. Create a new system DSN using the SQL Server (not native client) driver for the server you’re going to link to.  I used version 6.01.7600.16385.  Walk through the configuration options and modify those that are applicable to your situation.
2. Right click “Linked Server” under Server Objects in Management Studio.
3. Type a name for the linked server and select “Other data source.”
4. For the Provider, select “Microsoft OLE DB Provider for ODBC Drivers”
5. For Product name, type the name of the DSN you created in step 1.
6. Data source is the name of the DSN again.
7. The provider string I used is Data Source=<DSN>;Initial Catalog=tempdb;Integrated Security=SSPI;
8. Location and Catalog are both blank.
9. Under the security page, be sure the radio button is selected for “Be made using the login’s current security context”
10. Set the appropriate options on the Server Options page.
11. Click OK

That’s it!  You should now have a working linked server setup between SQL Server 2005 and SQL Server 7.

Overview

In this post I would like to demonstrate how to create a simple yet non-trivial CLR table-valued function (TVF). There are several examples of CLR TVFs on the net but the majority of them show only a trivial example. It is very difficult, in some cases impossible, to apply the principles and code in those examples to your own CLR TVF. The TVF outlined in this post is used to return the IP address for a given host name. At my shop we needed a way to log the IP a user connects from when they perform certain actions in the database. .NET can readily perform this work so why not create a TVF to return the IP addresses for a user? Sometimes this function will get called on a machine with two or more IPs (wireless and physical LAN) so a scalar function will not work here. What makes this TVF non-trivial in my opinion is that it does not just output an already formatted table; you have to build the output.

Without further ado, let’s dive in. The following code is full C# source code for the function. I will first divulge the entire code and then explain the pieces.

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;                       // Bring in classes necessary for working with IPs
using System.Collections;               // Bring in the IEnumberable defintion
using System.Collections.Generic;       // Bring in the List<T> class

public class Class_mfn_dnslookup
{
    [SqlFunction(Name = "mfn_dnslookup", FillRowMethodName = "FillRow", TableDefinition = "IP NVARCHAR(20)")]
    public static IEnumerable mfn_dnslookup(SqlString hostname)
    {
        string c_hostname = hostname.ToString();
        IPHostEntry host;
        host = Dns.GetHostEntry(c_hostname);

        List<object[]> resultItems = new List<object[]>();

        foreach (IPAddress ip in host.AddressList)
        {
            if (!IPAddress.IsLoopback(ip) && ip.ToString().IndexOf(".") != -1)
            {
                object[] item = new object[1];
                item.SetValue(ip.ToString(), 0);

                resultItems.Add(item);
            }
        }
        return resultItems;
    }

    public static void FillRow(Object obj, out SqlString IP)
    {
        object[] item = (object[])obj;
        IP = (SqlString)item[0].ToString();
    }
};

Code Guts

In line 10 we define the FillRowMethodName which will dictate how the return table is built. We also define the TableDefinition for the output table. Note that Line 11 uses the output type IEnumerable. This type interacts closely with the FillRow method specified earlier. This also allows for the table to be returned as rows are filled, instead of waiting for all rows to be populated and then returned. This is particularly handy when returning large data sets that you want to begin viewing right away. To build a result set, I assign the IP address to an index within an object array of static length (lines 23 and 24). This array only has the one slot, which is fine since each adapter has only one IP address. I then add this array to the resultItems list (line 26). Line 29 returns the resultItems list. But to where? Or what? This is where the IEnumerable class comes back into play. For CLR TVFs, the data being returned (resultItems list in this case) is operated on by the FillRow method, row by row.

So, the resultItems list, when returned, is passed through the FillRow method. Lines 34 and 35 show how values are set for one output row. The IP output in these lines corresponds to the [IP] column in the table definition. This happens for each output row in the resultItems list that is passed into the FillRow method. The results are sent back to the calling client as they are returned.

Conclusion

While there are several postings about CLR TVFs, I felt none of them explained their code well enough, namely the FillRow method. Jonathan Kehayias has a decent post at SQLCLR.net. I took the concepts from his post and applied them to my situation. Hopefully my explanation will help you better understand how to create a meaningful CLR TVF.

Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

Recently I ran into this error on an instance of SQL Server 2005. There are several articles and blog posts concerning this error on SQL Server 2000 systems, but next to none that discuss the error in SQL Server 2005. For a list of all of the procedures in MSDB that can throw this error, execute the following query:

SELECT name 
FROM sys.procedures
WHERE
OBJECT_DEFINITION(OBJECT_ID(name)) LIKE '%RAISERROR(14274%'

In my case, the error came from msdb.dbo.sp_add_job. To figure out what was causing the error, take a look at the procedure code:

USE [MSDB]
GO

EXEC sp_helptext sp_add_job;

Do a search for ‘RAISERROR(14274′ and you’ll see the logic that is causing the error. In the case of sp_add_job, the logic is:

IF (@job_id IS NULL)
  BEGIN
    -- Assign the GUID
    SELECT @job_id = NEWID()
  END
  ELSE
  BEGIN
    -- A job ID has been provided, so check that the caller is SQLServerAgent (inserting an MSX job)
    IF (PROGRAM_NAME() NOT LIKE N'SQLAgent%')
    BEGIN
      RAISERROR(14274, -1, -1)
      RETURN(1) -- Failure
    END
  END

Here we see that if a non-NULL value for @job_id is passed into sp_add_job from somewhere other than a SQL Agent Job step (e.g. SSMS), you’ll get the 14274 error.

I hope this helps someone out there!

Overview

When scripting stored procedure code for deployment to several servers, there are a few things to keep in mind.  One of those things is the permissions associated with the procedure you’re deploying.  When deploying a brand new stored procedure, the process of assigning permissions is fairly straightforward.  If you need to deploy an update to an existing stored procedure, things get a bit more complicated.  If you simply issue a DROP PROCEDURE call followed by the CREATE PROCEDURE code, you will lose the permissions that are associated with the stored procedure.  Issuing an ALTER PROCEDURE command will preserve the permissions but will cause you trouble if there are places where the procedure doesn’t yet exist.  To solve this issue, you should script out the permissions on a procedure to a temp table before dropping the procedure.  If there is a chance that the batch will fail, use a persisted table or a global temp table instead. Once the proc is recreated, run through the permissions and apply them to the proc again.  This way, you’ve preserved the permissions on the proc.

Now, there are some pitfalls to this approach.  If the proc didn’t already exist when you run the deploy script, there will obviously be no permissions to script out to a temp table.  So, you might run into the problem where the same proc on two different databases has two sets of permissions.  This might be an issue, but considering the proc didn’t exist in the first place, it’s doubtful that it would be an issue.  Nevertheless it is something to keep in mind.

The way to get around this pitfall is to include desired the permissions in the SQL script used to create the procedure. This way, the newly created proc will have the permissions it had before, in addition to any included in the deployment script.

The Code

Here is some sample SQL for scripting the permissions into a temp table:

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'SampleProc')
BEGIN
	IF OBJECT_ID('tempdb..#tmp_PermissionsWork') IS NOT NULL DROP TABLE #tmp_PermissionsWork
	CREATE TABLE #tmp_PermissionsWork (
		  ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL 
		, Permission NVARCHAR(MAX) NULL
	)
	
	INSERT #tmp_PermissionsWork (Permission)
	SELECT state_desc + ' ' + permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + QUOTENAME(USER_NAME(grantee_principal_id)) + ';' FROM sys.database_permissions 
	WHERE major_id = OBJECT_ID('SampleProc')

	DROP PROCEDURE SampleProc
END
GO

CREATE PROCEDURE SampleProc
AS
...
GO

DECLARE @cmd NVARCHAR(MAX)

WHILE EXISTS(SELECT 1 FROM #tmp_PermissionsWork)
BEGIN
	SELECT TOP 1 @cmd = Permission
	FROM #tmp_PermissionsWork
	
	EXEC sp_executesql @cmd;
	
	DELETE FROM #tmp_PermissionsWork 
	WHERE Permission = @cmd
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'SampleProc')
BEGIN
	IF OBJECT_ID('tempdb..#tmp_PermissionsWork') IS NOT NULL DROP TABLE #tmp_PermissionsWork
	CREATE TABLE #tmp_PermissionsWork (
		  ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL 
		, Permission NVARCHAR(MAX) NULL
	)
	
	INSERT #tmp_PermissionsWork (Permission)
	SELECT state_desc + ' ' + permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + QUOTENAME(USER_NAME(grantee_principal_id)) + ';' FROM sys.database_permissions 
	WHERE major_id = OBJECT_ID('SampleProc')

	DROP PROCEDURE SampleProc
END
GO

CREATE PROCEDURE SampleProc
AS
...
GO

DECLARE @cmd NVARCHAR(MAX)

WHILE EXISTS(SELECT 1 FROM #tmp_PermissionsWork)
BEGIN
	SELECT TOP 1 @cmd = Permission
	FROM #tmp_PermissionsWork
	
	EXEC sp_executesql @cmd;
	
	DELETE FROM #tmp_PermissionsWork 
	WHERE Permission = @cmd
END
GO

To use this code, swap out ‘SampleProc’ with your stored procedure and put your procedure code in place of lines 17 – 20.

*This code is provided “AS-IS” with no warranty of any kind.

Using Profiler to run a trace can be an invaluable tool for troubleshooting performance problems with T-SQL code. Occasionally something happens to the connection between Profiler and the SQL Server where the trace is running and you can no longer manage the trace from the profiler window. Because an errant trace can impact performance, this situation needs to be rectified ASAP. To get information on the traces being run on a server, you can issue the following query.

SELECT * FROM ::fn_trace_getinfo(NULL)

The output is a table and looks like the following:

The [traceid] column of the result set is the ID of the trace. You’ll need to find the errant trace ID from this column. Check around with other folks that may be running traces in the event that the above query returns more than 2 traceid values. The default trace will always have traceid = 1. For more information on fn_trace_getinfo, see the TechNet article here.

Once you know the traceid of the errant trace, you can stop it and remove it from the server. To do that, you’ll need to use the sp_trace_setstatus stored procedure. The syntax is as follows:

EXEC sp_trace_setstatus @traceid = 2 , @status = 0
EXEC sp_trace_setstatus @traceid = 2 , @status = 2

The @status parameter is what is used by the stored procedure to start, stop, or remove the trace. A status of 0 indicates the ‘stop’ action is to be taken while a status of 2 indicates that the trace is to be removed. Using @status=1 starts the specified trace. Additional details regarding the sp_trace_setstatus procedure can be found here.

I hope this helps you in the event you need to quickly kill and remove an errant trace.