Selfelected

Simple testing of transactions in sqlserver management studio

Testing that transactions do as they should is probably easiest done right in Sqlserver management studio.  The trick is to fire up two of them.

You can set breakpoints as with your usual Visual studio and use the same shortcuts for stepping through.

Transactions are complex, do not think otherwise.  Similarly to multi threaded programming it requires knowledge beforehand and just not the usual intellisense-and-see-which-methods-pops-up we have learned to use.

Playing around with transactions before implementing them is crucial for understanding and correct behaviour.

Just understanding that the transaction does what it should is probably not enough for any solution that has more than a trickle of traffic.  Resources I have used a lot are Inside Sqlserver and Sqlserver Books online (BOL). The latter is the same as the help.

Scope_Identity doesn’t return an Int in sqlserver but a Decimal

This is an old item.  Scope_Identity in Sqlserver doesn’t return an Int but a Decimal.  So in Dapper the call is

                var id = conn.Query<Decimal>(...

Honour those who should and here.

Show connected users and disconnect them in Sqlserver through Tsql

The title says it all.

For example when one restores a database one has to disconnect all users.  Here is a script for that.

USE master
GO
DECLARE @dbname varchar(30), @spid varchar(10), @start datetime
SELECT @start = current_timestamp, @dbname = 'dbname'
-- Timeout after 5 mts
while(exists(Select * FROM sysprocesses WHERE dbid = db_id(@dbname)) AND
 datediff(mi, @start, current_timestamp) < 5)
    begin
     DECLARE spids CURSOR FOR
      SELECT convert(varchar, spid) FROM sysprocesses
       WHERE dbid = db_id(@dbname)
     OPEN spids
     while(1=1)
     BEGIN
      FETCH spids INTO @spid
      IF @@fetch_status < 0 BREAK
      exec('kill ' + @spid)
     END
     DEALLOCATE spids
END
GO

I recently forgot to close the connection and looped through records so the connection pool was exhausted and people couldn’t log on to the system.  Typically something one finds out in a live system where many use your web at once.  At least that was the case for me.  Below is the code I ran over and over again while looping to make sure my updated code didn’t use too many connections in the pool.

USE MASTER
SELECT * FROM sysprocesses WHERE dbid = DB_ID('dbname')

Honor those who should.

There is also a longer version of the latter here.

Script data in Sqlserver

I don’t know for how long this has been possible but I guess my years of whining about lack of data scripting capabilities in Sqlserver management studio is moot.

I found an article which describes the hidden setting.  I wonder for how long the possibility has been there.

http://www.kodyaz.com/articles/how-to-script-data-in-sql-server-2011.aspx

I remember Sqlbase scripting its own data back in last century.  I remember myself writing sprocs for the same for Sqlserver.  I remember myself hunting scripts for the same on the web.  I now foresee a future with lots of clicketyclick – the GUI of MS’s Sqlserver tools is way too fiddly for my taste.

CREATE ASSEMBLY failed because method ‘…’ on type ‘…’ in safe assembly ‘…’ is storing to a static field. Storing to a static field is not allowed in safe assemblies.

When working with CLR inside the SQLserver database one doesn’t have to deploy the DLL through Visual Studio.  Instead use the Create Assembly command.

There are several reasons for this command to fail and

CREATE ASSEMBLY failed because method 'AAA' on type 'BBB' in safe assembly 'CCC' is storing to a static field.
Storing to a static field is not allowed in safe assemblies.

is one of them.

Unless your assembly is very complex the diagnosis is easy.  The AAA is a static something.  If it is a property

public static Colour{ get; set; }

AAA will be

set_Colour

CCC is the assembly that refuses to install.

Find AAA.  Rewrite to be not static.  Compile.  Run create assembly again.

Do annotate fields in the database

It is, at least in MSSqlserver, possible to annotate fields.  Please do.

There are people coming after you to maintain the code base.  One day it might be you who come after someone.  Start paying it forward.

The field explanation might be in the Documentation but the Documentation is too often faulty, not accessible and almost always not up to date.  The database is closer and probably easer to keep in sync with reality.

Don’t think a field name is self explanatory.  The field CustomerID in table Customer might be.  But the same field in Region is not.  Is it the region master or the primary customer or what?  By the time the field was created the meaning of this field was clear but not a year afterwards.

The reason I write this is that I presently work in a project where the habile predecessor has written down the meaning of the fields and saved me lots of time and lots more of confidence of what the figures mean and tons of debugging time.

Login failed for user ‘IIS APPPOOL\DefaultAppPool’.

If one uses trusted connection to a sqlserver database under Windows 7 and II7 one might get the error message “Login failed for user ‘IIS APPPOOL\DefaultAppPool’.” when trying to run a web application.  There are several articles for this but all points to a different GUI than mine (earlier versions of Windows7/IIS7?).

Update: as I lightly noted, to give the apppool SystemLocal rights is Not the right way to go.

Fire up Internet Information Services Manager.  (window-iis-return)

Find the Application Pools item in the Connections toolbar.  One can reuse the default application pool or create a new one.  Go for the latter since it is slightly more complicated and hence more fun.

I don’t know what the settings above do but they seem to work.

Select your newly created application pool and the advanced settings.  Change the Process model to LocalSystem.
(I had planned to create a “better” account with the proper limited rights but ran out of time.  Someone else…?)

Update: Don’t change to LocalSystem, instead add the user to allowed SQLServer logins as noted in the bottom here.

The recommended Microsoft solution is to create a separate account. However, if your solution is small, you can instead add the ‘IIS APPPOOL\DefaultAppPool’ user as a database user in your SQL instance, then providing the proper ‘User Mappings’ to the databases you need the DefaultAppPool user to access. You may not necessarily be able to search for this user, but you can still enter it in the ‘Login name’ field in the “Login – New” window as “IIS APPPOOL\DefaultAppPool” (without the quotes).

Follow this link as a reference and pay attention to the last post: http://social.msdn.micro…-4a71-a448-3e3eef9ee404/

Application pool identity dialogue

IIS application pools listing

As one can see in the listing above the new application pool runs as different identity than the default ones.

Now change your web application to use this application pool.  This could be made simpler by moving the menu for this to the same place as the other menus.  To make the story short – select your site and look to the right of the IIS manager.

To add a new user to SQLServer go to your database->security and add user connected to IIS APPPOOL\DefaultAppPool.  This way the user can log in to the database.

Then go to the SQLServer->security and add this user to be allowed to login to the server.

That is all there is to it.