Search results matching tags 'SQL Server', 'Database Testing', and 'Database Unit Testing'

Fun with DBCC CHECKIDENT

Alexander Kuznetsov — Thu, 26 Jun 2008 21:51:00 GMT

Suppose you have an empty table:

SELECT COUNT(*) FROM SampleTable

---

What would be the result of the following query:

DBCC CHECKIDENT('SampleTable', RESEED, 1)

INSERT SampleTable(j) SELECT 1

SELECT SCOPE_IDENTITY()

Surprisingly enough, the answer is “it depends”. If the table is brand new, you’ll get one, if there have been inserts into the table, the answer is 2. See for yourself:

CREATE TABLE SampleTable(i INT IDENTITY, j INT)

SELECT COUNT(*) FROM SampleTable

---

DBCC CHECKIDENT('SampleTable', RESEED, 1)

INSERT SampleTable(j) SELECT 1

SELECT SCOPE_IDENTITY()

---

DELETE FROM SampleTable;

DBCC CHECKIDENT('SampleTable', RESEED, 1);

INSERT SampleTable(j) SELECT 1;

SELECT SCOPE_IDENTITY();

---

Even more surprising is the fact that this inconsistent behavior is by design – it has been documented in MSDN:

http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx

quoting from this MSDN article: “If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1.”

So far I cannot come up with a reason why such inconsistent behavior could be required. Any feedback is welcome.

Anyway, if you are unit testing a stored procedure inserting into a table with identity, this feature of DBCC CHECKIDENT may break your unit tests. The workaround that I am using in my database unit testing is simple – when I set up my test fixture, I run the following script against all the tables with identities:

BEGIN TRY

INSERT INTO MySchema.MyTable DEFAULT VALUES

END TRY

BEGIN CATCH

PRINT '1'

END CATCH

Database Unit Testing: Impersonation

Alexander Kuznetsov — Wed, 18 Jun 2008 16:19:00 GMT

When you run NUnit/C# unit tests against your local instance, you are a dbo. As such, you are not getting any errors caused by missing permissions. However, you can impersonate another user, and run your unit tests in the context of that other user. The following test harness demonstrates the technique:

using System.Data;

using System.Data.SqlClient;

using NUnit.Framework;

namespace MyDB.Tests

{

[TestFixture]

public class ImpersonatorTests

{

private SqlConnection connection = null;

private Impersonator realUser;

#region constants

private const string userName = "me";

private const string domainName = "myDomain";

#region hide me

private const string pwd = "myPassword";

#endregion

[TestFixtureSetUp]

public void TestFixtureSetUp()

{

realUser = new Impersonator(userName, domainName, pwd);

connection = new SqlConnection("server=(local);trusted_connection=true;database=FinancialDW;");

connection.Open();

}

[TestFixtureTearDown]

public void TestFixtureTearDown()

{

if (realUser != null) realUser.Dispose();

}

[Test]

public void ImpersonationSucceededTest()

{

SqlCommand reader = connection.CreateCommand();

reader.CommandText = "SELECT SUSER_NAME()";

reader.CommandType = CommandType.Text;

string suserName = reader.ExecuteScalar().ToString();

Assert.AreEqual(@"myDomain\me", suserName);

//Console.WriteLine(suserName);

}

[Test]

public void CanSelectFromSalesTest()

{

SqlCommand reader = connection.CreateCommand();

reader.CommandText = "SELECT COUNT(*) FROM Data.Sales";

reader.CommandType = CommandType.Text;

int numRows = int.Parse(reader.ExecuteScalar().ToString());

Assert.IsTrue(numRows >= 0);

}

So, if the myDomain\me user does not have the SELECT permission on Data.Sales, I am getting an exception:

System.Data.SqlClient.SqlException: SELECT permission denied on object 'Sales', database 'MyDb', schema 'data'

The code that I used to implement the Impersonator class is mostly copied and pasted from http://support.microsoft.com/?scid=306158

, and here it is:

using System;

using System.Runtime.InteropServices;

using System.Security.Principal;

namespace MyDB.Tests

{

public class Impersonator : IDisposable

{

/* code taken from

http://support.microsoft.com/?scid=306158

public const int LOGON32_LOGON_INTERACTIVE = 2;

public const int LOGON32_PROVIDER_DEFAULT = 0;

WindowsImpersonationContext impersonationContext;

[DllImport("advapi32.dll")]

public static extern int LogonUserA(String lpszUserName,

String lpszDomain,

String lpszPassword,

int dwLogonType,

int dwLogonProvider,

ref IntPtr phToken);

[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]

public static extern int DuplicateToken(IntPtr hToken,

int impersonationLevel,

ref IntPtr hNewToken);

[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]

public static extern bool RevertToSelf();

[DllImport("kernel32.dll", CharSet = CharSet.Auto)]

public static extern bool CloseHandle(IntPtr handle);

public Impersonator(string user, string domain, string password)

{

if(!ImpersonateValidUser(user, domain, password))

{

throw new Exception(string.Format(@"Logon failed for {0}\{1}", domain, user));

}

private bool ImpersonateValidUser(String userName, String domain, String password)

{

WindowsIdentity tempWindowsIdentity;

IntPtr token = IntPtr.Zero;

IntPtr tokenDuplicate = IntPtr.Zero;

if (RevertToSelf())

{

if (LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,

LOGON32_PROVIDER_DEFAULT, ref token) != 0)

{

if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)

{

tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);

impersonationContext = tempWindowsIdentity.Impersonate();

if (impersonationContext != null)

{

CloseHandle(token);

CloseHandle(tokenDuplicate);

return true;

}

if (token != IntPtr.Zero)

CloseHandle(token);

if (tokenDuplicate != IntPtr.Zero)

CloseHandle(tokenDuplicate);

return false;

}

#region IDisposable Members

public void Dispose()

{

if (impersonationContext != null)

impersonationContext.Undo();

}

#endregion

}

Unit Testing Tips: Reproduce a Lock Timeout

Alexander Kuznetsov — Fri, 30 May 2008 22:24:00 GMT

Well, I have just finished a series of articles about unit testing on simple-talk.com, and some techniques were not included, just to keep the articles short. Still problems such as reproducing a lock timeout and unit testing the retry after it (lock timeout) are interesting, at least to me, so I am continuing.

You will need the database StressTests, which can be downloaded at the following URL:

http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes---reproduce-database-errors/

You will also need the following stored procedure which logs the timeout and retries at most five times:

CREATE PROCEDURE [Writers].[UpdateUserWithRetry]

@UserID INT,

@FirstName VARCHAR(8),

@LastName VARCHAR(8),

@Position VARCHAR(8)

SET NOCOUNT ON

DECLARE @ret INT, @succeeded INT, @attemptNumber INT;

SELECT @ret = 0, @succeeded = 0, @attemptNumber = 1;

WHILE(@succeeded = 0 AND @attemptNumber < 5) BEGIN

SET @attemptNumber = @attemptNumber + 1;

BEGIN TRY

UPDATE Data.Users

SET FirstName = @FirstName,

LastName = @LastName,

Position = @Position

WHERE UserID = @UserID;

SET @succeeded = 1;

END TRY

BEGIN CATCH

IF (XACT_STATE()) = -1

BEGIN

ROLLBACK TRANSACTION;

END;

INSERT INTO Data.ErrorLog(ERROR_PROCEDURE, ERROR_LINE, ERROR_NUMBER, ERROR_MESSAGE, ERROR_TIME)

SELECT ERROR_PROCEDURE(),

ERROR_LINE(),

ERROR_NUMBER(),

ERROR_MESSAGE(),

CURRENT_TIMESTAMP;

SET @ret = 1;

END CATCH

END

RETURN @ret;

Also you will need the LockTimouter class which reproduces a lock timeout inside your stored procedure:

· - it opens another connection,

· - begins a transaction,

· - modifies the data from that connection,

· - then it attempts to modify the same data from your stored procedure

· - releases the lock as soon as your error is logged, so that your retry can succeed

public class LockTimeouter : IDisposable

{

private readonly SqlConnection _anotherConnection;

private readonly SqlCommand _command;

private readonly SqlCommand _anotherCommand;

private readonly IAsyncResult _anotherCommandStarted;

public LockTimeouter(SqlConnection connection, string hookCommand)

{

_command = connection.CreateCommand();

_command.CommandText = "SET LOCK_TIMEOUT 1;DELETE FROM Data.ErrorLog";

_command.CommandType = CommandType.Text;

_command.ExecuteNonQuery();

_anotherConnection = new SqlConnection(connection.ConnectionString + ";Asynchronous Processing=true");

_anotherConnection.Open();

_anotherCommand = _anotherConnection.CreateCommand();

_anotherCommand.CommandText = string.Format("BEGIN TRANSACTION; {0};", hookCommand);

_anotherCommand.CommandType = CommandType.Text;

_anotherCommand.ExecuteNonQuery();

_anotherCommand.CommandText = "DECLARE @c INT;SET @c=0;WHILE @c=0 BEGIN SELECT @c=COUNT(*) FROM Data.ErrorLog;END;COMMIT";

_anotherCommand.CommandType = CommandType.Text;

_anotherCommandStarted = _anotherCommand.BeginExecuteNonQuery();

}

public void Dispose()

{

_command.CommandText = "IF @@TRANCOUNT>0 BEGIN ROLLBACK; END";

_command.ExecuteNonQuery();

_anotherCommand.EndExecuteNonQuery(_anotherCommandStarted);

_anotherCommand.CommandText = _command.CommandText;

_anotherCommand.ExecuteNonQuery();

_anotherConnection.Close();

}

The unit test itself is quite simple, because most of the work is wrapped in the LockTimouter class

[Test]

public void LockTimeoutTest()

{

SqlCommand command = _connection.CreateCommand();

command.CommandText = "DELETE FROM Data.Users WHERE UserId = 12345";

command.CommandType = CommandType.Text;

command.ExecuteNonQuery();

command.CommandText = "Writers.UpdateUserWithRetry";

command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@UserId", 12345);

command.Parameters.AddWithValue("@FirstName", "Josh");

command.Parameters.AddWithValue("@LastName", "Olson");

command.Parameters.AddWithValue("@Position", "QA");

using (new LockTimeouter(_connection, "INSERT INTO Data.Users(UserID, FirstName, LastName, Position, LotsOfComments)VALUES(12345,'Jill','Hansen','QA','')"))

{

try

{

command.ExecuteNonQuery();

Console.WriteLine("No exception raised");

}

catch (Exception e)

{

if (!e.ToString().Contains("Transaction count after EXECUTE indicates "))

Console.WriteLine(e);

}

//check if there are updates in Data.Users, and if the error has been recorded

}

When the test is finished, you can verify that one error was indeed recorded and that the modification finally completed:

SELECT * FROM Data.ErrorLog ORDER BY ERROR_TIME

SELECT * FROM Data.Users