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.

depending on loading Sql Server 2000

My C# Service depends on MS SQL 2000 service and my service is starting to use database immediatly during loading. Everything ok, service is starting and stopping untill... restart.
On some computers, my service is not run automatically after computer restart because database not ready yet.
(I got error: Could not find database ID 2. Database may not be activated yet or may be in transition)

After investigation of the sql server I understand that server starting listening clients before, than starts up my database... How it can be??? Ok, there are milliseconds difference, but my service already tried to access db, and exited because it cannot. On other computers, where service is starting, I see in the log that my database was started before than server starts listening clients.

My database is not marked "Auto Close". Sql Server configuration is exactly same on computers, SP4 installed. So I don't have idea, why some sql servers change their loading priority...

Well, I found some things that may fix this.
1.
I unchecked autostart MSDTC service. Anyway this service is loading when DTC transaction initiated by somebody. And it helps


Interesting, service started, but it is not solution, I don't know, what settings user will have.. I And I suspect, it is just loading time issue.. Because log still shows starting database only after starting listening, not before.

2. I found at microsoft if I will use trace flag 3614, the startup sequence of SQL Server 2000 will be slightly different. And they rules the world with such phrases like "slightly different"?
I checked checkbox "Autostart MSDTC" back and added parameter -T3614. Ooops, service started... Let's see sql server log.Yes! My database is loaded before than service started to listen clients.



Nice. Solution may be used for workaround in the case of problem. But for my serenity, I added to OnStart method of my service check for accessibility of my database, and in the negative case sleep service for some time before continue..
Now it works.

However, I don't have real solution or understanding, why it is happens when Sql Server put user databases to load after starting to accept requests.. Question is opened for while.
It looks like this article have similar situation, but it is not enough to understand.

Drop default constraint

Somebody created table like this:

CREATE TABLE [dbo].[MyTable] (
...
[MyField] [bit] NOT NULL DEFAULT (1),
...
) ON [PRIMARY]

I need to do some changes, but first I should drop constraints and after changes, create again. Oops, I don't know constrain name, because somebody used bad style in creation tables.
I need to execute SQL query like following
ALTER TABLE [dbo].[TreeNodeRelation] DROP CONSTRAINT [DF__MyTable__MyField__2B3F6F97] - but we cannot be sure that name will be same on all installations because it is autogenerated during "create table".

Solution. Following example receives name of default constraint for the specific field, and if it is exists, drop it.

declare @cnstname sysname declare @sql nvarchar(4000) select @cnstname = [name] from sysobjects as so where xtype = 'D' and parent_obj = object_id('dbo.MyTable') and col_name(parent_obj, info) = 'MyField' if @cnstname is not null begin set @sql = N'alter table dbo.MyTable drop constraint ' + @cnstname execute sp_executesql @sql end

Access common windows folders from C#

I interested to get desktop and programs folder from C# application to create some shortcuts there. .NET allows it in following way:

Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
Environment.GetFolderPath(Environment.SpecialFolder.Programs);

But results are directories for the current user, like

c:\document And Settings\LastDon\Destkop.

However I want to place shortcuts for all users, for profile ALLUSERS.
So, I found registry key

HKEY_LOCAL_MACHINE\SOFTWARE\microsoft\windows\currentversion\explorer\shell folders

Not too happy - Windows 2003 is not using this key, and anyway, it is not recommended by microsoft.
So, I come to solution by using API Shell32.dll:

[DllImport("Shell32.DLL")] public static extern int SHGetSpecialFolderLocation( IntPtr hwndOwner, int nFolder, out IntPtr ppidl); [DllImport("Shell32.DLL")] public static extern int SHGetPathFromIDList( IntPtr pidl, StringBuilder Path); const int CSIDL_COMMON_PROGRAMS = 23; const int CSIDL_COMMON_STARTUP = 24; const int CSIDL_COMMON_DESKTOPDIRECTORY = 25; private string GetSpecialFolder(int folderType) { IntPtr pResultList; IntPtr handlePtr = new IntPtr(0); int result =SHGetSpecialFolderLocation(handlePtr, folderType, out pResultList); if (result < 0) { return null; } StringBuilder sb = new StringBuilder (500); result = SHGetPathFromIDList(pResultList, sb); if (result == 0) // boolean result { return null; } return sb.ToString(); }

Get destkop path:
GetSpecialFolder(CSIDL_COMMON_DESKTOPDIRECTORY);
Get programs path:
GetSpecialFolder(CSIDL_COMMON_PROGRAMS);

Following microsoft article helped me to solve it out
http://support.microsoft.com/kb/306285

Uninstall by installshield IDriver

Again problem with installshield.
Uninstall installshield product (InstallScript & MSI) command line is:
IDriver.exe /M{GUID}
I found this command in the registry in uninstall key under InstallShield_{GUID}.

But I want it uninstall silent so i run
IDriver.exe /M{GUID} /uninst

Nothing. Product is not uninstalling. Macrovision just kidding.
Ok, solution is following.
1. Run setup.exe /r from product media and do uninstall the product
2. Copy setup.iss file from windows folder to somewhere.
3. Now command line to uninstall silent will be:
Idriver.exe /M{GUID} /f1"c:\somewhere\setup.iss"

Thanks God, it works, so I can return back to my C# work.

CSS

Nice CSS examples.
Navigation, tabs menu, shadows, rounds, etc...

53 CSS techniques you couldnt live without

Installshield hangs

I would prefer stay away from Installshield. I will never write my experience in this product in the my resume. But it happens, I must work with it "time after time"
Setup hang after finished whole work, and exited from any of my install script events.
Why?
Because I forgot to call SdShowMsg("", FALSE) in one of cases.
After investigation of the community I found, this problem happens on abort or exit calls as well in different versions. I have latest Installshield 12. Should Macrovision fix their bugs? No answer.

VS 2005 DLL Hell and simple C++ Win32

So many people have problems with C++ on VS 2005 and just released Service pack 1. So many blames, hysterics, and threaths to change development environment. What Microsoft doing for C++ programmer? As they explaining, they trying doing best to avoid DLL Hell.
Come on, enough! Let's move to Linux. Just kidding. :)

Fortunately, since .NET appeared, I am not writing C++ too often. However, yesterday I required to write simple Win32 C++ application and got troubles with running it on computer w/o .NET installed.
Damn, where is old good VS 6.0?
I created empty Win32 application, compiled it and copied to computer without .NET.
Bah!
"Application has failed to start because MSVCR80.dll was not found"

Ok, I copied this runtime DLL to application folder. Bah!

"An application has made an attempt to load C runtime library incorrectly"

Ok, result of the investigation is clear - together with exe, VS 2005 built manifest file. I copied manifest file to application directory. Now... error is same.
Just to check, I installed .NET 2.0 and application started to work. Wow. But wait, I am writing win32 C++ application, why I need this framework?

I get through project properties, disabled any manifest settings, and saw, that project settings were set to not require .NET CLR. No progress. Application doesn't work w/o .NET. As experienced C++ programmer, I think, I am too old for this. :) Where I put CD "Visual Studio 6" ?

At last, I changed to static linking for the runtime library. I choosed using /MT rather than /MD. Yes, Microsoft doesn't recommend it. But... Bingo! Application started to work!
Some explanation of the strange behaviour of VS 2005 is found there.
http://www.itwriting.com/blog/?postid=261
There are also another solutions, but they doesn't seem workable for system w/o .NET
I decided to stay with static linking for now as single way to get win32 exe and run it everywhere. Is there another way? God knows.

BTW, there is another source describing the situation. http://www.grimes.demon.co.uk/workshops/fusWSThirteen.htm
I agree with article that implementing strange ideas in unmanaged programming doesn't look too nice and handy.
"The whole process of manifests and embedding them is topsey-turvey and is a complete mess. This is yet another example of Microsoft losing the plot"

C# free test

Actually I don't think these tests make sense. They check knowledge more then programmer's possibilities. Even erudite person may not give good result on job, because knowledge is not all. Yes, good knowledge may help to code effectively in the clean new project. But in the big existing projects, it doesn't make sense without other things.

We always know ourself very good, but in any moment that we can to get free exam, we going to get it. :) Even if the test is so stupid, like this one.
Free exams