Register .NET COM Interop DLL

One day, running RegAsm I got error:
"Cannot create a stable subkey under a volatile parent key"

It took a couple of hours trying to understand, what is going on. At last solution has been found. Restart computer.
Registry was in the state to prevent add keys, because it expecting restart from some previous operations.

Sql Server cache

To clean cache:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

To view cache list (SQL 2005):

SELECT objtype, p.size_in_bytes, t.[text], usecounts
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) t
WHERE objtype IN ('Prepared', 'Adhoc')
ORDER BY usecounts DESC

winforms: Dock Fill is working wrong

I experienced much time with troubles in the C# windows form, if control is set dock:fill and I adding any other control (menu, status bar,whatever) to the form or panel.
In this case, main control with fill dock style doesn't recognize that need to fit, it overlapping any new control. Sometimes, to fix I creating new form and redesigned it from scratch.

Solution is very easy:
Right click on the control with fill dock style, and choose Bring to Front. Docking will be fixed :)

Source

Performance Counters are damaged.

Found at the customer station that all perfomance counters are damaged and only numbers appears in the performance monitor app.
My application is failing when tried to use perfomance counters "Input string incorrect format".

Solution:
run command:
lodctr.exe /r

Performance monitor now ok, as well as my application too.

IIS7: Application pool, managed pipeline mode

Referenced posts:
Separated application pool
Application pool idenity

Windows Vista comes with new property of application pool..
Now we have property "managed pipeline mode".
It allows two options:
- 0 - Integrated (set by default during creation of the new application pool)
- 1 - Classic

In the .NET 3 new .NET library Microsoft.Web.Administration allows us to manage IIS settings very easy.

Unofortunately, I was required to turn IIS settings on the Windows Vista in old way, because software product still not working with .NET 3, so I use old good way of the ADSI and System.DirectoryServices

To set application pool in classic mode on Windows Vista/IIS7, I make additional call (in bold):
string appPoolName = "myAppPool";
DirectoryEntry poolRoot = new DirectoryEntry("IIS://localhost/W3SVC/AppPools");
DirectoryEntry pool = poolRoot.Children.Add(appPoolName, "IIsApplicationPool");
pool.InvokeSet("ManagedPipelineMode", new Object[] { 1 });
pool.CommitChanges();

I set 1, because I need to set my application pool working in classic mode. 0 should be used for integrated mode.

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 });
pool.CommitChanges();

possible values:

0	Local System
1	Local Service
2	Network Service
3	Specific 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 });

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.

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 indexes.name, * FROM sysindexes indexes
INNER JOIN sysobjects objects ON indexes.id = objects.id
WHERE indexes.indid>0 AND objects.name='mytable'
and indexes.name 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(indexes.id, indexes.name, 'IsStatistics')=0
and INDEXPROPERTY(indexes.id, indexes.name, 'IsHypothetical') = 0
and INDEXPROPERTY(indexes.id, indexes.name, 'IsAutoStatistics') = 0

Foreign keys for the referenced table

Let's say, I have a database table "SingleTable" and I want to get list of all tables which are referencing to this SingleTable and names of foreign keys...

(I need this, for example, if I am going to drop SingleTable but I need drop all dependencies from this table first )

I have option to use undocumented stored procedure sp_MStablerefs, however I am trying to avoid using undocumented feature. Never know when and where Microsoft will put stick in my wheels :)
So, there is alternative way I found by experiments:

USE mydatabase
SELECT name as fkname, OBJECT_NAME(parent_obj) as fkTableName
FROM sysobjects
INNER JOIN sysreferences
ON sysobjects.id=sysreferences.constid
WHERE name like 'FK%' AND xtype='F' -- foreign key only
AND rkeyid=object_id(N'SingleTable')

Result is set of forein key names and table names referencing to my table..
Bingo. Now we can drop them one by one using cursor, or DataSet on the upper code level..

IIS6: separated application pool

When IIS runs multiple versions of .NET web projects, collision happens sometimes.
So, I required to assign my web project in separated application pool under Windows 2003 /IIS6 programmatically.

It is easy through System.DirectoryServices of .NET
There is, I create virtual directory "myWebDir" and assign it to new pool with the name "myAppPool":

DirectoryEntry folderRoot = new DirectoryEntry("IIS://localhost/W3SVC/1/Root"); DirectoryEntry virDir = folderRoot.Children.Add("myWebDir", "IIsWebVirtualDir"); object[] param ={ 0, "myAppPool", true }; virtDir.Invoke("AppCreate3", param); //set virtual directory properties //....... newVirDir.CommitChanges();

Application pool is created automatically if third parameter is a true in the param array.

However, I would like also to manage my application pools without specific virtual directory.
Here are examples to create/remove application pools:

Create Application pool:

string appPoolName = "myAppPool"; DirectoryEntry poolRoot = new DirectoryEntry("IIS://localhost/W3SVC/AppPools"); if (!DirectoryEntry.Exists(IISAppPoolRootPath + "/" + appPoolName); { DirectoryEntry pool = poolRoot.Children.Add(appPoolName, "IIsApplicationPool"); pool.CommitChanges(); }

Remove Application pool:

string appPoolName = "myAppPool"; DirectoryEntry poolRoot = new DirectoryEntry("IIS://localhost/W3SVC/AppPools"); DirectoryEntry pool = poolRoot.Children.Find(appPoolName, "IIsApplicationPool"); if (pool != null) { poolRoot.Children.Remove(pool); poolRoot.CommitChanges(); }

Remove only works, if no one web application assigned to pool.