Transactional Replication : How to find erroring command and remove it

Had an interesting situation today where a replicated stored procedure call was in error. The issue was that the procedure call had some single quotes in a string parameter. This was giving the following error in Replication Monitor:

Error messages:
Invalid distribution command, state 2: transaction 0x0x000007c000005e36005000000000 command 3{offset : 258 token offset: 44 state: 50) }. (Source: MSSQL_REPL, Error number: MSSQL_REPL21001)
Get help: http://help/MSSQL_REPL21001

There is actually everything you need in that error, the transaction sequence number is the key piece of information.

But out of interested, I listed up the replication errors at the Publisher using sp_helpsubscriptionerrors as follows:

exec sp_helpsubscriptionerrors  @publisher = 'myPublisher', @publisher_db = 'dbName', @publication='publicationName', @subscriber='mySubscriber', @subscriber_db='dbName'

(I've obviously changed the parameters to hide the sensitive bits!)

This command lists up all of the failed transactions for the subscription. In a normal implementation, this may be just one. However if you have multi-threaded your subscription then there may be more than one.

Once the offending command has been identified (and it will be the same as the one reported in Replication Monitor) you can decide what to do. In this case I wanted to delete the command and apply it by hand (minus the single quotes!). There is a nice command which allows this, the important piece of information is the xact_seqno (otherwise known as the transaction sequence number). sp_setsubscriptionxactseqno will remove the command at the subscriber. Yep, not obvious from the name of the command. The crucial bit to note here is you have to run it in the subscriber database, it will not work from the distributor.

exec sp_setsubscriptionxactseqno @publisher = 'myPublisher',@publisher_db ='publicationName', @publication='mySubscriber', @xact_seqno = 0x000007C000005E36005000000000

SQL Server Transactional Replication Subscription Streams not being used

I was recently looking at increasing the performance of Transactional Replication across a very high latency line (plenty of bandwidth). I had exhausted all of the agent parameter tweaks so decided to look at subscription streams on the subscriber. There's a bunch of good articles out there describing this, not least this article from SQL Server Advisory team which describes the relative benefits (and dangers) of using subscription streams.

So I gave it a go. If you set the -VerboseHistoryLevel to 2 you will get a useful statistics report periodically. Upon checking this I was pleased to see 16 separate threads, so my set up was right. However, when running tests only the first thread was ever used. Looking at the BOL it says :

Note

Subscriptionstreams do not work for articles configured to deliver Transact-SQL. To use subscriptionstreams, configure articles to deliver stored procedure calls instead. Fine, I'm replicating Stored Procedures calls (rather than table articles) so this should work. Well no, my bad, what that note means is the Standard procedures i.e. SCALL, XCALL, MCALL routines. Not replicated Stored Procedures articles. Simple misunderstanding that made all the difference. Changed replication to be Table Article based, and behold 16 threads of goodness and over 10 times increase of performance.

Transactional replication, identity and NOT FOR REPLICATION

Ouch.

Here's the scenario. Have a database server (call it DBS-A, DB1) transactionally replicating to another server (DBS-B, DB1). The tables have identities on them as the unique index (to save space, and as DBS-B is readonly have no problems with duplicate data). All works fine.

So now have another server (DBS-C, DB2) replicating data into DBS-A (into another database DB2) using transactional replication. However, the replication is non standard, in that the sp_MSins_xxxx routine has been amended. This change calls a stored procedure which copies data in the DBS-A, DB1 database. This is a SELECT..INSERT type of affair which works perfectly when called from SSMS or via an application.

However, SQL Server thinks that this data is being created by replication, so obeys the NOT FOR REPLICATION flag on the IDENTITY field, and subsequently returns a not null error, expecting a value etc.

What is annoying is that when you add a table into a replication article, it automatically adds NOT FOR REPLICATION onto any IDENTITY fields and I can not see how to remove this via the UI.

After some investigation I found the following system stored procedure which will do this for you:

DECLARE @tableID INT


SELECT @tableID = object_id('myTable')
EXEC sys.sp_identitycolumnforreplication @tableID, 0

Nice !


Problem 2...
Now this seems to work perfectly on empty tables, however to my horror I found that when I tried it on a populated table (over 1million rows) that the log reader blew up with :

The process could not execute 'sp_replcmds' on 'mydatabase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Get help: http://help/MSSQL_REPL20011

The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {000017d6:000016d7:00b9}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
Get help: http://help/18805

The process could not execute 'sp_replcmds' on 'mydatabase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

Arrrrgggh !
This was traced to be definitely the table that I changed, as the error disappeared once the article was dropped.

After struggling through dropping and recreating the replication several times, the (unconfirmed as I don't want to try it again) solution appeared to be an order thing:

• Create an empty table with the same schema as the one you want to add to the replication
• Add empty table to replication
• Remove NOT FOR PUBLICATION using above stored procedure call
• Populate the data from full table to empty table
• Move any foreign keys over from referencing tables

That seems to work.

Solid State Drives for SQL Server

Interesting article on solid state drives and SQL Server.

SQL Server monitoring

Good article on SQL Server monitoring toolkit.

SSIS projects for monitoring your servers.

SQL Server Stats and Query Optimiser tips

Great article here with 13 Things You Should Know About Statistics and Query Optimiser. Covers a lot of ground and is worth a read.

SQL Plan Parallelism issue

Had a strange scenario whereby a stored procedure would randomly execute very slowly. Normally took half a second, then occasionally took 30 seconds or more. Thought initially as this was on busy tables (i.e. results varied considerably even though the SQL was static with no parameters) that the plan created was incorrectly optimised. However, straight SQL provided the same issue. WITH RECOMPILE seemed to work around the problem, however that added a couple of seconds to the execution time.

So I decided to dig a little deeper into the plan, and noticed that there was a Parallelism item for just about every SQL. Even stranger, I found that if I removed a particular column from the select list (that happened to be a DECIMAL 20,9) that the parallelism went and the SQL flew.

Now I'm not sure if there is any more work required when selecting a DECIMAL field, however it was definitely related. Remove any other field and the parallelism (and the performance) didn't change.

Finally settled on stopping SQL from pursueing parallelism by adding the following after the end of the SELECT statement :

OPTION ( MAXDOP 1 )

This forces the SQL to be run on one processor, and lo the performance is now back to less that half a second.

Not maybe an ideal solution as I'm telling SQL that it is wrong. However, looking at the plan, all the estimated costs are well off so I can only assume that this is some weird set of circumstances.

Detecting blocking SQL 2005

I've got a very occasional problem whereby a process is blocking the incremental rebuild of some fulltext indexes which in turn blocks loads of other statements. Trapping this in a reasonable amount of time so that it doesn't affect users too much has been a problem. I then found this Tony Rogerson article which went a great way to implementing a solution.

Repeating Tony's article (look there for the full details, this is just the code)->

First create the trace threshold. Show advance options needs to be enabled:

sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure
go

-- Set up report capture threshold. Set to 10 seconds here - change figure
-- to suit needs.
sp_configure 'blocked process threshold', 10
go
reconfigure
go

Next create a database to receive the messages:

--CREATE DATABASE RBT.DBAEventManagement
--go

USE [RBT.DBAEventManagement]
go

ALTER DATABASE [RBT.DBAEventManagement] SET ENABLE_BROKER
go

CREATE QUEUE syseventqueue
go

CREATE SERVICE syseventservice
ON QUEUE syseventqueue ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] )
go

CREATE EVENT NOTIFICATION notify_locks
ON SERVER
WITH fan_in
FOR blocked_process_report
TO SERVICE 'syseventservice', 'current database';

GO

This now enough to capture the messages and queue them up. The next part is to receive the messages and to email out/store history. Only 1 email is sent per block - don't want an email every 10 seconds ! The history table is as follows

USE [RBT.DBAEventManagement]
GO

IF OBJECT_ID('dbo.tbl_messages', 'U') IS NOT NULL
DROP TABLE dbo.tbl_messages
GO

CREATE TABLE dbo.tbl_messages
(
Message_Body XML NOT NULL,
Message_Sequence_Number INT NOT NULL,
xactid VARCHAR(20) NOT NULL,
Emailed VARCHAR(1) NOT NULL CONSTRAINT [DF_tbl_messages_Emailed] DEFAULT 'N',
TMStamp SMALLDATETIME NOT NULL CONSTRAINT [DF_tbl_messages_TMStamp] DEFAULT (GETDATE()),
CONSTRAINT PK_messages PRIMARY KEY NONCLUSTERED(TMStamp ASC, Message_Sequence_Number ASC)
)

GO

The stored procedure to receive the messages is as follows:

CREATE PROCEDURE [dbo].[proc_capture_queue]
AS


/* ========================================================================
proc_capture_queue

Capture service broker queue and insert into history table.
Email new message

===========================================================================
Version Date Author Comment
------- ---- ------ -------
v0.00 14/01/2010 Brian Jones Initial Version
=========================================================================== */
SET NOCOUNT ON

DECLARE @msg_body XML,
@DatabaseID INT,
@Process XML,
@xactid VARCHAR(20),
@emailBody NVARCHAR(MAX),
@13LineFeed10 NVARCHAR(2)

DECLARE @msgs TABLE ( message_body XML NOT NULL,
message_sequence_number INT NOT NULL,
xactid VARCHAR(20))

SET @13LineFeed10 = CHAR(13) + CHAR(10)

IF NOT EXISTS (SELECT * FROM sys.server_event_notifications)
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MailProfile',
@recipients = N'myemail@myaddress',
@subject = 'WARNING: SQL Blocking Service Queue',
@body = 'The SQL message queue for tracking blocking sessions is missing, please investigate.'
RETURN
END;

RECEIVE message_body, message_sequence_number,
CAST( message_body AS XML ).value( '/EVENT_INSTANCE[1]/TextData[1]/blocked-process-report[1]/blocked-process[1]/process[1]/@xactid', 'varchar(20)')
FROM syseventqueue
INTO @msgs;

INSERT INTO dbo.tbl_messages(Message_Body, Message_Sequence_Number, xactid)
SELECT Message_Body, Message_Sequence_Number, xactid
FROM @msgs
ORDER BY message_sequence_number

DECLARE email_cur CURSOR FOR
SELECT Message_Body, xactid
FROM @msgs
ORDER BY message_sequence_number

OPEN email_cur
FETCH NEXT FROM email_cur INTO @msg_body, @xactid

WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT * FROM tbl_messages WHERE xactid = @xactid AND Emailed = 'Y')
BEGIN
SELECT @DatabaseId = CAST( @msg_body AS XML ).value( '(/EVENT_INSTANCE/DatabaseID)[1]', 'int' )

SELECT @emailBody = N'The following process is being blocked.' + @13LineFeed10 + @13LineFeed10 +
'Blocked Process ' + @13LineFeed10 +
'=============== ' + @13LineFeed10 +
'Spid : ' + CAST( @msg_body AS XML ).value( '/EVENT_INSTANCE[1]/TextData[1]/blocked-process-report[1]/blocked-process[1]/process[1]/@spid', 'varchar(10)')
+ @13LineFeed10 +
'HostPid : ' + CAST( @msg_body AS XML ).value( '/EVENT_INSTANCE[1]/TextData[1]/blocked-process-report[1]/blocked-process[1]/process[1]/@hostpid', 'varchar(10)')
+ @13LineFeed10 +
'HostName : ' + CAST( @msg_body AS XML ).value( '/EVENT_INSTANCE[1]/TextData[1]/blocked-process-report[1]/blocked-process[1]/process[1]/@hostname', 'varchar(100)')
+ @13LineFeed10 +
'LoginName : ' + CAST( @msg_body AS XML ).value( '/EVENT_INSTANCE[1]/TextData[1]/blocked-process-report[1]/blocked-process[1]/process[1]/@loginname', 'varchar(100)')
+ @13LineFeed10 +
'DatabaseName : ' + DB_Name(@DatabaseID)
+ @13LineFeed10 +
'Batch Started : ' + CAST( @msg_body AS XML ).value( '/EVENT_INSTANCE[1]/TextData[1]/blocked-process-report[1]/blocked-process[1]/process[1]/@lastbatchstarted', 'varchar(25)')
+ @13LineFeed10 +
+ @13LineFeed10 +
'Blocking Process ' + @13LineFeed10 +
'================ ' + @13LineFeed10 +
'Spid : ' + CAST( @msg_body AS XML ).value( '/EVENT_INSTANCE[1]/TextData[1]/blocked-process-report[1]/blocking-process[1]/process[1]/@spid', 'varchar(10)')
+ @13LineFeed10 +
'HostPid : ' + CAST( @msg_body AS XML ).value( '/EVENT_INSTANCE[1]/TextData[1]/blocked-process-report[1]/blocking-process[1]/process[1]/@hostpid', 'varchar(10)')
+ @13LineFeed10 +
'HostName : ' + CAST( @msg_body AS XML ).value( '/EVENT_INSTANCE[1]/TextData[1]/blocked-process-report[1]/blocking-process[1]/process[1]/@hostname', 'varchar(100)')
+ @13LineFeed10 +
'LoginName : ' + CAST( @msg_body AS XML ).value( '/EVENT_INSTANCE[1]/TextData[1]/blocked-process-report[1]/blocking-process[1]/process[1]/@loginname', 'varchar(100)')
+ @13LineFeed10 +
'DatabaseName : ' + DB_Name(@DatabaseID)
+ @13LineFeed10 +
'Batch Started : ' + CAST( @msg_body AS XML ).value( '/EVENT_INSTANCE[1]/TextData[1]/blocked-process-report[1]/blocking-process[1]/process[1]/@lastbatchstarted', 'varchar(25)')
+ @13LineFeed10 +
+ @13LineFeed10 +
'Message in full : ' + @13LineFeed10 +
CAST(@msg_body AS VARCHAR(MAX))

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MailProfile',
@recipients = N'myemail@myaddress',
@subject = 'SQL Blocking Report',
@body = @emailBody

UPDATE tbl_messages
SET Emailed = 'Y'
WHERE xactid = @xactid
END

FETCH NEXT FROM email_cur INTO @msg_body, @xactid
END

CLOSE email_cur
DEALLOCATE email_cur


------------------------ END OF PROCEDURE --------------------------------

OK, I've used a SQL cursor but it's on a small memory table so it isn't an issue.

This stored procedure is in a job which executes every couple of minutes (uses WAITFOR DELAY 00:01 to loop continuously). There is another job which clears the history table down every week.

This is now happily whirring away waiting for the blocking process. This may need (unfortunately) expanding to run a SQL Trace also, so that the blocking process can be investigated further. But that's another blog post !

SSMS Tool Pack

A brilliant addon for SSMS 2005/2008, and it's free ! Features include:

•SQL Snippets
•Window Connection Coloring
•Query Execution History and Current Window History
•Format SQL
•Search Table, View or Database Data
•Run one script on multiple databases
•Copy execution plan bitmaps to clipboard or file
•Search Results in Grid Mode or Execution Plans
•Generate Insert statements from resultsets, tables or database
•Regions and Debug sections
•Running custom scripts from Object Explorer
•CRUD stored procedure generation
•New query template
•General options

Install now, if only for the colour coded connection windows !!

SQL Server Memory Settings 64bit

Rather interesting blog posting here talking about SQL Server 2005/2008 64bit and memory settings. The recommendation is to always set these as the following table outlines:

Physical RAM	MaxServerMem Setting
2GB	1500
4GB	3200
6GB	4800
8GB	6400
12GB	10000
16GB	13500
24GB	21500
32GB	29000
48GB	44000
64GB	60000
72GB	68000
96GB	92000
128GB	124000

What makes this interesting is the comments posted, where one poster mentions that they have seen instability when the memory is not set.

Run SQL Trace in background

Rather useful this if you need to monitor a trace for a period of time and can not leave the session logged in. Found how to do it here.

First use Profiler to define the events, columns, and filters needed. Some Events are : SQL:BatchCompleted and RPC:Completed, SP:StmtCompleted. Important columns are : Duration, CPU, Reads and Writes. Some advanced events are SP:Recompile and Scan:Started to check for table and index scans

Click the Run button. Immediately stop the trace

Click the File menu, expand the Export option, and then expand the Script Trace Definition option. Choose For SQL Server 2005 (or SQL 2000 if creating script for older SQL Server) and select a filename to save the script.

Once the script has been saved, open it for editing in SQL Server Management Studio.

The following line of the script must be edited, and a valid path must be specified, including a filename:
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere',
@maxfilesize, NULL. The @maxfilesize is 5MB by default

Run the script. The generated script will also select back a @traceID

Once you are done use the @traceID to stop and close the trace:

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

The fn_trace_gettable function can be used to read the data from the trace file :

SELECT * FROM ::fn_trace_gettable('C:\Traces\myTrace.trc', 999) where 999 is the number of rollover trace files to read

Easy !

Benchmarking SQL Server

A very useful blog entry on Benchmarking SQL Server.

The article looks at the PAL tool found here

The process is straight forward. Create a performance log for a period of time on the SQL Server you need to benchmark. Typical metrics to record are :

Physical Disk
Logical Disk
Process (I will sometimes tweak this down to just the SQL Server process)
Memory
Network Interface (sometimes I will tweak down to just the counters needed and just the NICs involved)
Paging File
Processor
System
SQL Server: Access Methods
SQL Server: SQL Statistics
SQL Server: Buffer Manager
SQL Server: General Statistics
SQL Server: Latches (Often I just look at the two required counters rather than the object)
SQL Server: Locks
SQL Server: Memory Manager

Save this for a reasonable slice of time and then run it through PAL. This then generates a nice report, highlighting any issues. See the above blog link for a more indepth description.

Find sql usage in a database

The following searches all objects in a database for a particular piece of text:

DECLARE @searchString VARCHAR(128)

SET @searchString = 'xp_smtp'

SELECT DISTINCT so.name, so.type, so2.name as source
FROM sysobjects so
INNER JOIN syscomments sc ON so.id = sc.id
LEFT OUTER JOIN sysobjects so2 ON so2.ID = so.parent_obj
WHERE text LIKE '%' + @searchString + '%'
ORDER BY so.name

Collecting Query Stats

Interesting article here which provides a nice SQL to investigate stats of all statements executed on a server.

Get PID for a suspended or sleeping SQL Session

Quick Tip:

Profiler only works for sessions that are active, sp_who/wp_who2 do not give enough details, so to get any more information a quick check in sys.dm_exec_sessions will provide all the information needed.

Index defrag script

Nice article on setting up index defragmentation jobs

Troubleshoot SQL 2005 Performance Issues

Technet article on useful DMVs to investigate SQL performance issues

Index Defragmentation Script

Borrowed from MS help online pages :

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO


And for a quick looksee on the state of the indexes in a database :

SELECT object_id AS ObjectID,
index_id AS IndexID,
avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags,
avg_fragment_size_in_pages AS PagesPerFrag,
page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID('Adventureworks'),
NULL, NULL, NULL , 'DETAILED')
WHERE avg_fragmentation_in_percent > 0
ORDER BY ObjectID, IndexID

dba Checklist Links

Links to dba checklists

12 Essential Steps After Installing SQL Server by Sadequl Hussain

Using SQL Server Application Roles through C#

I've not had to do this with C# before (did it with Delphi no problem), and found that there is an issue using connection pooling.

The problem is that the connection seems to be in error once a role has been set. The test I performed was as follows :

1. Set approle
2. call a stored procedure to populate a datatable from c#
3. call the stored procedure again.

On the second call to the stored procedure, I got an error whatever I did. If you do not set the approle, you will get a permission denied error. If you set the approle then you get a nasty looking error telling you the connection needs to be reset. Either way doesn't work.

As I am reluctant to use a dedicated connection (for a start this would involve rewriting alot of code, not to mention any performance issues), I needed a proper solution. And here it is :

1. Open connection.
2. exec sp_setapprole
3. run sql
4. exec sp_unsetapprole
5. close connection.

The only tricky bit is that you have to get the @cookie from sp_setapprole and pass it into sp_unsetapprole to free it up. This is a varbinary, so needs to be handled in a byte[] type.

So, pseudo-ish code is

public DataSet getSomeData()
{
connection = getConnection();
connection.Open();
byte[] cookie = SetApprole(connection, approle, approlepassword);
....
call some sql using connection
....

UnsetApprole(connection, cookie);
}

public byte[] SetApprole(SqlConnection connection, string approle, string approlePassword)
{
StringBuilder sqlText = new StringBuilder();

sqlText.Append("DECLARE @cookie varbinary(8000);");
sqlText.Append("exec sp_setapprole @rolename = '" + approle + "', @password = '" + approlePassword + "'");
sqlText.Append(",@fCreateCookie = true, @cookie = @cookie OUTPUT;");
sqlText.Append(" SELECT @cookie");

if (connection.State.Equals(ConnectionState.Closed))
connection.Open();

using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlText.ToString();
cmd.CommandTimeout = commandTimeout;
return (byte[])cmd.ExecuteScalar();
}
}

And the final trick - calling sp_unsetapprole. This gave me issues as it errored with a 15422 (Application roles can only be activated at the ad hoc level) on a CommandType.Text execution. It has to be a CommandType.StoredProcedure to work :

public void UnsetApprole(SqlConnection connection, byte[] approleCookie)
{
string sqlText = "exec sp_unsetapprole @cookie=@approleCookie";

if (connection.State.Equals(ConnectionState.Closed))
connection.Open();

using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_unsetapprole";
cmd.Parameters.AddWithValue("@cookie", approleCookie);

cmd.CommandTimeout = commandTimeout;
cmd.ExecuteNonQuery();
}
}

Remember to add all your favourite try...catch blocks etc.