Wednesday, August 29, 2007

IIS6: Application pool Identity

Referenced post: Separated app pool

In referenced post I wrote how to create application pool and assign virtual directory for it..
Now I faced with requirement to change identity from default (Network service) to Local system account) to allow web application more permissions..

Property AppPoolIdentityType should help.
Following code will create pool and set Local System account identity for the pool:

string appPoolName = "myAppPool";
DirectoryEntry poolRoot = new DirectoryEntry("IIS://localhost/W3SVC/AppPools");
DirectoryEntry pool = poolRoot.Children.Add(appPoolName, "IIsApplicationPool");

pool.InvokeSet("AppPoolIdentityType", new Object[] { 0 });

possible values:
0Local System
1Local Service
2Network Service
3Specific user

In the case of specific user need to use following additional operations:

pool.InvokeSet("WAMUserName", new Object[] { computerName + @"\" + user });
pool.InvokeSet("WAMUserPass", new Object[] { password });

Tuesday, August 21, 2007

Sql Server 913 error: Could not find database ID

Strange behaviour of sql server I found.
When databaseid is changed in the sysdatabases, but name of database is same I get 913 error on some queries:
Could not find database ID %d. Database may not be activated yet or may be in transition.
It is totally thrue, my database have another id after series of drop/create, but somehow .NET SqlConnection remember it. There is no matter, if I am closing and reopen connection. I even did SqlConnection.ClearAllPools()
Even creating new connection with same connection string doesn't help. And it is not bug referencing by MSDN if I am using user-defined function. Nope, I don't have UDR in query.. But error appears in some specific queries, like Create mydb.dbo.mytable, or update, or insert..
One note.. I always use connection string with default database master
After drop/create database, I changed connection string and set default database name to the just created.

Bingo!!! It works!
But why?? Why connection of master, even with clearing pools, remember obsolete information from sysdatabases, if I even recreate new connection on it?
Didn't found any reasonable answer.

Thursday, August 09, 2007

list of table indexes

I was need to drop all indexes for specific table excluding primary key indexes.
Following example demonstrates how to get list of indexes.

USE mydb;

SELECT, * FROM sysindexes indexes
INNER JOIN sysobjects objects ON =
WHERE indexes.indid>0 AND'mytable'
and not in
(select constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = 'mytable' and constraint_type = 'primary key')

if table doesn't have clustered index, then result set of sysindexes will contain heap with the field indid=0.. Because it is not real index, and I cannot drop it, I am filtering it out by the indid>0

Lately I found that table may contain statistic indexes, which cannot be dropped as well directly with DROP INDEX, but through "drop statistics" statement

To filter them out I added following part to WHERE:
and INDEXPROPERTY(,, 'IsStatistics')=0
and INDEXPROPERTY(,, 'IsHypothetical') = 0
and INDEXPROPERTY(,, 'IsAutoStatistics') = 0