Hello World!
So here I was looking at a DB script for one of our products that
checks MSSQL Metadata for existence of an object before attempting to
do anything with it and my enquisitive self pestered me to checkout if
the (very) old way of checking for a DB objects existence as follows:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[<schema>].[<table-name>]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
<do something>
END
ELSE
BEGIN
<do something else>
END
GO
has any easier, more readable way of doing in MSSQL 2005 or higher. I
then remembered seeing some thing like INFORMATION_SCHEMA and SYS which
can be queried for details of metadata. My search (GOOGLE: sys.tables
vs information_schema.tables) landed me on this
excellent article on the facts of the matter.
To Summarize, MSSQL 2000 onwards provides what are called as "SYSTEM
VIEWS" to look up metadata. For example, to check if a table exists,
one could write a query like:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'<TABLE-NAME>')
BEGIN
<do something>
END
ELSE
BEGIN
<do something else>
END
GO
Phew! Much better and self explanatory! As it turns out, you could also
query "CATALOG VIEWS" for metadata and would get much more detailed
information, albeit at the cost of a slightly more detailed syntax. A similar query using CATALOG VIEW would then be
IF EXISTS (SELECT * FROM SYS.TABLES WHERE name =
'<TABLE-NAME>' AND type = 'U')
BEGIN
<do something>
END
ELSE
BEGIN
<do something else>
END
GO
Happy Coding!
SM
Tuesday, November 23, 2010
AB's SQL Server Day: An enriching experience
Hello World!
I had the opportunity to attend a session by Amit Bansal in Microsoft
(Embassy Golf Links, Bangalore) premises on Saturday, 20th
November 2010. The discussion agenda was as follows:
I must mention here that the keynote and
welcome by Vinod worked as an appetizer that should've generated
curiosity and set expectations on what was to follow. Must say that the
demonstartion on how the TRUNCATE command behaves vis-a-vis DELETE in
terms of transaction logging, TRANSACTIONS themselves was really cool.
I never knew that a TRUNCATE could be issued within a transaction and
rolled back!!
I have had the oppurtunity to attend a session on MS SQL 2005 Reporting
and Analysis services by Amit Bansal in 2006 (The same Microsoft campus
as mentioned in the beginning). The man has only got better with time
in terms of an engaging style of delivery and the way he connects with
the audience. As most of us would agree, it is hard to concentrate on a
deep-dive technical session for more than 15 minutes but thanks to AB's
efforts, in the entire 1 hour of session, I hardly winked! Add to that
the fact that I only have theoretical knowledge about the subject
matter of Data Warehousing and Data Mining and you'd know what I'm
talking about! (PS: I am a .NET developer with only a working knowledge
of databases :)
I would appeal to all my friends to join the SQLServerGeeks.com community
as also the wetogether.in site. Amit and Peopleware are doing a
fantastic service to the community by organising such events. I have
honestly got motivated to be on the podium in one such event in the
very near future!
Happy Coding
SM
I had the opportunity to attend a session by Amit Bansal in Microsoft
(Embassy Golf Links, Bangalore) premises on Saturday, 20th
November 2010. The discussion agenda was as follows:
- Keynote and Welcome by
Vinod Kumar (Microsoft) - Microsoft BI Platform
developed around MS SQL 2008 R2 - Delivered by Sudhir Rawat (Microsoft) - An introduction to
SQLServerGeeks.com, a community portal in development - By Amit Bansal
I must mention here that the keynote and
welcome by Vinod worked as an appetizer that should've generated
curiosity and set expectations on what was to follow. Must say that the
demonstartion on how the TRUNCATE command behaves vis-a-vis DELETE in
terms of transaction logging, TRANSACTIONS themselves was really cool.
I never knew that a TRUNCATE could be issued within a transaction and
rolled back!!
I have had the oppurtunity to attend a session on MS SQL 2005 Reporting
and Analysis services by Amit Bansal in 2006 (The same Microsoft campus
as mentioned in the beginning). The man has only got better with time
in terms of an engaging style of delivery and the way he connects with
the audience. As most of us would agree, it is hard to concentrate on a
deep-dive technical session for more than 15 minutes but thanks to AB's
efforts, in the entire 1 hour of session, I hardly winked! Add to that
the fact that I only have theoretical knowledge about the subject
matter of Data Warehousing and Data Mining and you'd know what I'm
talking about! (PS: I am a .NET developer with only a working knowledge
of databases :)
I would appeal to all my friends to join the SQLServerGeeks.com community
as also the wetogether.in site. Amit and Peopleware are doing a
fantastic service to the community by organising such events. I have
honestly got motivated to be on the podium in one such event in the
very near future!
Happy Coding
SM
Tuesday, September 21, 2010
MSSQL Server on a non-default port - Complete configuration
Hello World,
I've been waking up on the right side of my bed from past couple of days (touch wood). Today is one such day - nothing can beat me today!!
On a serious note, a client of ours approached me to ask about configuring their SQL server instance to run on a non-default port. They had been advised to the like by a "security-expert". Now I won't lie but I did not have any experience of configuring a SQL server to accept connections on a non-default
port until today. Before I delve into the solution, a little background:
SQL Server version: MSSQL 2005 Enterprise Edition
Default Port: 1433
Default connection mechanism:
Shared Memory (Local Clients/SQL Server Management Studio)
Named Pipes AND/OR TCP/IP for remote connections
I trusted Google to find my way around. It rightly came up with the following Microsoft documentation for connecting to MSSQL on a non-default port: MSDN Link
But you can't always have your cake and eat it too (at least not till you earn it anyway). As it turns out, post configuring the server, you would need to tell your client apps that they should not attempt connections on default port 1433 (isn't that obvious?!). In essence, you would need to modify your connection string to indicate that the client app should connect using TCP/IP and specify the port it should connect to. I am going to summarise the steps required for clarity:
I. Configure SQL server to accept connections only via TCP/IP on a port specified
1. Open the SQL server configuration manager: Start > All Programs > Microsoft SQL Server 2005 > Configuration tools > SQL Server configuration manager
2. Expand the node SQL Server Network Configuration on the left pane and click Protocols for where is you SQL server instance name (MSSQLSERVER by default)
3. Ensure "Status" column for each of the protocols except TCP/IP listed on the right pane is "Disabled"
4. Double click on the protocol name TCP/IP. This will open the properties window. Click on the "IP Addresses" tab
5. For each IP address listed, set the value of "TCP Dynamic Ports" to blank. If there is a 0 as value, delete it. Next, set the port number you want SQL server to listen on against the "TCP Port" property.
II. Configure client application to connect to the port setup in the above series of steps
1. The Data Source key in your connection string should be in the format,. E.g. If you have configured your SQL server to run on port 1500, you would specify "Data Source=192.168.123.456,1500"
2. To force your application to attempt only a TCP/IP connection, add NETWORK LIBRARY=DBMSSCON; in your connection string. For details see here
3. Your connection string would look something like this now:
Data Source=192.168.123.456,1500;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
Test your application with these settings. If you are not having a bad day, you should be good ;)
[NOTE: The "Network Library=DBMSSCON" is not mandatory as per my testing]
Happy Coding
I've been waking up on the right side of my bed from past couple of days (touch wood). Today is one such day - nothing can beat me today!!
On a serious note, a client of ours approached me to ask about configuring their SQL server instance to run on a non-default port. They had been advised to the like by a "security-expert". Now I won't lie but I did not have any experience of configuring a SQL server to accept connections on a non-default
port until today. Before I delve into the solution, a little background:
SQL Server version: MSSQL 2005 Enterprise Edition
Default Port: 1433
Default connection mechanism:
Shared Memory (Local Clients/SQL Server Management Studio)
Named Pipes AND/OR TCP/IP for remote connections
I trusted Google to find my way around. It rightly came up with the following Microsoft documentation for connecting to MSSQL on a non-default port: MSDN Link
But you can't always have your cake and eat it too (at least not till you earn it anyway). As it turns out, post configuring the server, you would need to tell your client apps that they should not attempt connections on default port 1433 (isn't that obvious?!). In essence, you would need to modify your connection string to indicate that the client app should connect using TCP/IP and specify the port it should connect to. I am going to summarise the steps required for clarity:
I. Configure SQL server to accept connections only via TCP/IP on a port specified
1. Open the SQL server configuration manager: Start > All Programs > Microsoft SQL Server 2005 > Configuration tools > SQL Server configuration manager
2. Expand the node SQL Server Network Configuration on the left pane and click Protocols for
3. Ensure "Status" column for each of the protocols except TCP/IP listed on the right pane is "Disabled"
4. Double click on the protocol name TCP/IP. This will open the properties window. Click on the "IP Addresses" tab
5. For each IP address listed, set the value of "TCP Dynamic Ports" to blank. If there is a 0 as value, delete it. Next, set the port number you want SQL server to listen on against the "TCP Port" property.
II. Configure client application to connect to the port setup in the above series of steps
1. The Data Source key in your connection string should be in the format
2. To force your application to attempt only a TCP/IP connection, add NETWORK LIBRARY=DBMSSCON; in your connection string. For details see here
3. Your connection string would look something like this now:
Data Source=192.168.123.456,1500;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
Test your application with these settings. If you are not having a bad day, you should be good ;)
[NOTE: The "Network Library=DBMSSCON" is not mandatory as per my testing]
Happy Coding
Tuesday, September 7, 2010
A UI to edit/view config files for .NET Apps
Hello World,
I was wondering if I could provide my deployment team with a nice UI that would present app.config or web.config AppSettings section for viewing or editing. After some deliberation, I fired up my Visual Studio and started playing around with the methods of the Configuration class (found under the System.Configuration namespace). I noticed a method called OpenExeConfiguration that looked promising; it had all the methods that looked like having the ability to traverse the config file and read/write key value pairs.
Each discovery though comes with issues! I tried several ways of accessing the config file from another app but no matter what I tried, the HasFile method of Configuration class would return "false" indicating that there was no config file in the location I specified!! Then I began googling (what else would you do?!) and found this useful link: Blog Post
It appears that you need to tell the OpenExeConfiguration method where the EXE is and it will try to locate the config file by concatenating the exe name with ".config". E.g., if your exe is called MySuperApp.exe and could be found in the directory C:\, you would write something like this:
Configuration conf = ConfigurationManager.OpenExeConfiguration(@"C:\MySuperApp.exe");
Once I am through writing this UI app, I will try posting the code in this blog.
Until next time,
System.AllIsWell = true;
:)
I was wondering if I could provide my deployment team with a nice UI that would present app.config or web.config AppSettings section for viewing or editing. After some deliberation, I fired up my Visual Studio and started playing around with the methods of the Configuration class (found under the System.Configuration namespace). I noticed a method called OpenExeConfiguration that looked promising; it had all the methods that looked like having the ability to traverse the config file and read/write key value pairs.
Each discovery though comes with issues! I tried several ways of accessing the config file from another app but no matter what I tried, the HasFile method of Configuration class would return "false" indicating that there was no config file in the location I specified!! Then I began googling (what else would you do?!) and found this useful link: Blog Post
It appears that you need to tell the OpenExeConfiguration method where the EXE is and it will try to locate the config file by concatenating the exe name with ".config". E.g., if your exe is called MySuperApp.exe and could be found in the directory C:\, you would write something like this:
Configuration conf = ConfigurationManager.OpenExeConfiguration(@"C:\MySuperApp.exe");
Once I am through writing this UI app, I will try posting the code in this blog.
Until next time,
System.AllIsWell = true;
:)
Tuesday, July 6, 2010
MS SQL 2005 TRY ... CATCH In Stored Procedures
Hello World!
I was trying to figure out how to throw a custom error message through a stored procedure in MS SQL 2005 that can be "caught" using the structured exception handling provided in the database. All this while, my major interaction had been with SQL 2000 style stored procedures that use the @@ERROR system flag to check for an error after each statement that may cause an error to be thrown. One needs to define GOTO labels to handle the errors once detected.
So here is what I was trying to do:
1. Initiate a transaction
2. Start executing a batch of SQL statements that may run into potential errors OR may need to throw an error if some conditions are met/not met
3. Catch the error using a CATCH block, rollback the transaction is required
4. Return the error encountered using the inbuilt functions ERROR_NUMBER(), ERROR_PROCEDURE(), ERROR_LINE() and ERROR_MESSAGE()
I managed to create a stored procedure as per my requirements stated above, but the issue was, the error I was trying to “throw” was not being “caught” in the CATCH block. The reason: I did not indicate that my error was severe enough!! A very nice article by Pinal Dave explains what the severity levels indicate. To sum up, a severity less than 16 is treated as informational by SQL server and hence the CATCH block does not get hit.
Here is the basic stored procedure body with the logic that tries to do 1 through 4 I listed earlier:
CREATE PROCEDURE MyStoredProc
@Param1 NVARCHAR(20),
@Param2 NVARCHAR(20)
AS
BEGIN
-- We don’t need extra results to affect our rowcount
SET NOCOUNT ON
-- The following block wraps our SQL statements which may throw error
BEGIN TRY
BEGIN TRANSACTION
DECLARE @MyUniqID INT
-- Check if there is a record for @Param1 and @Param2
SELECT @MyUniqID = MyTableKey WHERE Col1 = @Param1 AND
Col2 = @Param2
-- Did we find something?
IF @MyUniqID IS NOT NULL
BEGIN
-- Go on do what ya’ like!
END
ELSE
-- Nope! We did not find a row!
BEGIN
RAISERROR (N'Invalid Record. There is not record with the specified %s and %s',
16, -- Severity, -- Less than 16 would not be enough!
1, -- State,
N'Param1’s column name', -- First argument.
N'Param2’s column name'); -- Second argument.
END
-- If we reach here, everything went to plan
COMMIT TRANSACTION
RETURN 0
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_NUMBER() AS ERR_NUMBER, ERROR_PROCEDURE() AS ERROR_PROCEDURE ,ERROR_LINE() AS ERROR_LINE, ERROR_MESSAGE()AS ERR_MESSAGE
END CATCH
SET NOCOUNT OFF
END
If you are executing this SP though .NET code using ExecuteDataset(), you may examine the columns of the returned data table to check and take appropriate action in the code as required.
Bon Voyage!!
I was trying to figure out how to throw a custom error message through a stored procedure in MS SQL 2005 that can be "caught" using the structured exception handling provided in the database. All this while, my major interaction had been with SQL 2000 style stored procedures that use the @@ERROR system flag to check for an error after each statement that may cause an error to be thrown. One needs to define GOTO labels to handle the errors once detected.
So here is what I was trying to do:
1. Initiate a transaction
2. Start executing a batch of SQL statements that may run into potential errors OR may need to throw an error if some conditions are met/not met
3. Catch the error using a CATCH block, rollback the transaction is required
4. Return the error encountered using the inbuilt functions ERROR_NUMBER(), ERROR_PROCEDURE(), ERROR_LINE() and ERROR_MESSAGE()
I managed to create a stored procedure as per my requirements stated above, but the issue was, the error I was trying to “throw” was not being “caught” in the CATCH block. The reason: I did not indicate that my error was severe enough!! A very nice article by Pinal Dave explains what the severity levels indicate. To sum up, a severity less than 16 is treated as informational by SQL server and hence the CATCH block does not get hit.
Here is the basic stored procedure body with the logic that tries to do 1 through 4 I listed earlier:
CREATE PROCEDURE MyStoredProc
@Param1 NVARCHAR(20),
@Param2 NVARCHAR(20)
AS
BEGIN
-- We don’t need extra results to affect our rowcount
SET NOCOUNT ON
-- The following block wraps our SQL statements which may throw error
BEGIN TRY
BEGIN TRANSACTION
DECLARE @MyUniqID INT
-- Check if there is a record for @Param1 and @Param2
SELECT @MyUniqID = MyTableKey WHERE Col1 = @Param1 AND
Col2 = @Param2
-- Did we find something?
IF @MyUniqID IS NOT NULL
BEGIN
-- Go on do what ya’ like!
END
ELSE
-- Nope! We did not find a row!
BEGIN
RAISERROR (N'Invalid Record. There is not record with the specified %s and %s',
16, -- Severity, -- Less than 16 would not be enough!
1, -- State,
N'Param1’s column name', -- First argument.
N'Param2’s column name'); -- Second argument.
END
-- If we reach here, everything went to plan
COMMIT TRANSACTION
RETURN 0
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_NUMBER() AS ERR_NUMBER, ERROR_PROCEDURE() AS ERROR_PROCEDURE ,ERROR_LINE() AS ERROR_LINE, ERROR_MESSAGE()AS ERR_MESSAGE
END CATCH
SET NOCOUNT OFF
END
If you are executing this SP though .NET code using ExecuteDataset(), you may examine the columns of the returned data table to check and take appropriate action in the code as required.
Bon Voyage!!
Thursday, July 1, 2010
Configure SASL with svn (subversion)
Hello World,
So here I was trying to grope my way through setting up an svn (SUBVERSION) instance for my projects. Following is what I had set out to achieve:
1. Install an SVN Server
2. Install an svn client to access the server
3. Enable access through a secured protocol like SASL
4. Create a repository structure
This post details how I went about achieving the above.
1. Install an SVN server
This one was the simplest of all. After googling a bit, I gathered that CollabNet offers a free, open source server that comes with an MSI installer – just what I was looking for. I downloaded the same from the CollabNet website. The site has documentation to aid the install and configuration that pretty much works for everyone with the ability to understand English :)
2. Install an SVN client
Although there are a number of choices here, the most widely used and simple client interface is offered by Tortoise SVN. Again, this comes with an installer (MSI) that is self explanatory.
3. Enable SASL to access the SVN server
This turned out to be the difficult part because the documentation that accompanied Tortoise SVN did not have enough detail (or I could not find it!). What did I do next? No prizes for guessing: Google for how to set up SASL with SVN. I will list out what worked for me (and hopefully would work for anyone)
To enable SASL:
1. Edit svnserve.conf located in C:\SVN_REPOSITORY\conf directory (assuming your repository is located there.)
1.1 Uncomment the line use-sasl=true
1.2 ensure there are no spaces before "use-sasl"
2. Uncomment the line realm=My first repository
2.1 Give a unique name here e.g. realm=SVNREPOROOT
2.2 Ensure there are no spaces before "realm"
3. Create file subversion.conf in the directory pointed to by the registry path
HKEY_LOCAL_MACHINE\SOFTWARE\Carnegie Mellon\Project Cyrus\SASL Library and key called SearchPath under that
4. Enter the following in the subversion.conf file created in 3 above:
pwcheck_method: auxprop
auxprop_plugin: sasldb
mech_list: DIGEST-MD5
for an explanation of what the keys mean, refer to SASL documentation online
5. Now to create users, run the following from command line:
saslpasswd2 -c -f C:\sasldb -u SVNREPOROOT username
where-
C:\sasldb is the location where you'd want to create the sasl database file
SVNREPOROOT is the value specified for realm parameter in your svnserve.conf file
username is the login that you would like to use
5.1 You would be asked for password and then to confirm the password. Once done, the prompt would return to a new line (no message will be shown after you confirm password). This indicates that you have successfully created a user and set a password!!
IMPORTANT NOTE: All parameters in the svnserve.conf file (and any other conf files) should begin on the first column on the line; that is, there should not be any space before the parameter name. If this is not the case, you would get some strange failure messages that may cause your head to spin like a top. You have been warned!
So here I was trying to grope my way through setting up an svn (SUBVERSION) instance for my projects. Following is what I had set out to achieve:
1. Install an SVN Server
2. Install an svn client to access the server
3. Enable access through a secured protocol like SASL
4. Create a repository structure
This post details how I went about achieving the above.
1. Install an SVN server
This one was the simplest of all. After googling a bit, I gathered that CollabNet offers a free, open source server that comes with an MSI installer – just what I was looking for. I downloaded the same from the CollabNet website. The site has documentation to aid the install and configuration that pretty much works for everyone with the ability to understand English :)
2. Install an SVN client
Although there are a number of choices here, the most widely used and simple client interface is offered by Tortoise SVN. Again, this comes with an installer (MSI) that is self explanatory.
3. Enable SASL to access the SVN server
This turned out to be the difficult part because the documentation that accompanied Tortoise SVN did not have enough detail (or I could not find it!). What did I do next? No prizes for guessing: Google for how to set up SASL with SVN. I will list out what worked for me (and hopefully would work for anyone)
To enable SASL:
1. Edit svnserve.conf located in C:\SVN_REPOSITORY\conf directory (assuming your repository is located there.)
1.1 Uncomment the line use-sasl=true
1.2 ensure there are no spaces before "use-sasl"
2. Uncomment the line realm=My first repository
2.1 Give a unique name here e.g. realm=SVNREPOROOT
2.2 Ensure there are no spaces before "realm"
3. Create file subversion.conf in the directory pointed to by the registry path
HKEY_LOCAL_MACHINE\SOFTWARE\Carnegie Mellon\Project Cyrus\SASL Library and key called SearchPath under that
4. Enter the following in the subversion.conf file created in 3 above:
pwcheck_method: auxprop
auxprop_plugin: sasldb
mech_list: DIGEST-MD5
for an explanation of what the keys mean, refer to SASL documentation online
5. Now to create users, run the following from command line:
saslpasswd2 -c -f C:\sasldb -u SVNREPOROOT username
where-
C:\sasldb is the location where you'd want to create the sasl database file
SVNREPOROOT is the value specified for realm parameter in your svnserve.conf file
username is the login that you would like to use
5.1 You would be asked for password and then to confirm the password. Once done, the prompt would return to a new line (no message will be shown after you confirm password). This indicates that you have successfully created a user and set a password!!
IMPORTANT NOTE: All parameters in the svnserve.conf file (and any other conf files) should begin on the first column on the line; that is, there should not be any space before the parameter name. If this is not the case, you would get some strange failure messages that may cause your head to spin like a top. You have been warned!
Tuesday, April 27, 2010
Case Sensitive or Insensitive SQL Query
Hello World!
So today was one of those days - you know when you see a ton of mails saying everything that was working yesterday is not working today! But somehow you believe that you'd be able to make it all work again - almost magically!
Well the problem today was as follows: I work with a .NET application that has the following components:
1) A windows NT service
2) A set of UI tools (desktop apps) to administer the behaviour of the service
Now what happened is that one of our QA was trying to set up an environment from scratch using the App - installer (an MSI) and the database scripts provided. The database had to be created first followed by running all scripts in order to create all objects and insert default data. The rest of the configuration had to be done by using the UI. The application uses strongly typed DataSet to access the DB layer. After setup, the app started throwing an unhandled exception while trying to start. The exception stack trace referred to a "column not found" whilst the table had the column when I checked the database. I just happened to check the query being executed by the application normally by using the SQL server management studio - and lo - it indeed complained of the missing column! I then realized that if I spelled the column name in the same case as being shown in Object Explorer, the query did succeed.
On further investigation, when I checked the server properties ( Right Click the server node in object explorer -> Properties; Click "General" under Select a Page pane; Look for the property named "Server Collation"), the collation was listed as "Latin1_General_CS_AI". When I googled for this, I got to know that the "CS" stands for case sensitive. To change it to case-insensitive, one must use "CI"
You can change the collation at the database level as follows:
1. Right click on the database name in object explorer and click properties
2. Under "select a page" (right pane) click on "Options"
3. Change the collation drop down to the desired collation
4. click Ok.
You can also accomplish the same by running the script from the master database:
ALTER DATABASE COLLATE
NOTE: The database needs to be exclusively locked to perform this operation. This, in plain English, means that there should not be any connections to the database while doing this.
The following link demonstrates how to do this at a query level:
Steven Smith : Case Sensitive or Insensitive SQL Query
If you use a collation with each query, you needn't worry about DB collation!
All the best!
So today was one of those days - you know when you see a ton of mails saying everything that was working yesterday is not working today! But somehow you believe that you'd be able to make it all work again - almost magically!
Well the problem today was as follows: I work with a .NET application that has the following components:
1) A windows NT service
2) A set of UI tools (desktop apps) to administer the behaviour of the service
Now what happened is that one of our QA was trying to set up an environment from scratch using the App - installer (an MSI) and the database scripts provided. The database had to be created first followed by running all scripts in order to create all objects and insert default data. The rest of the configuration had to be done by using the UI. The application uses strongly typed DataSet to access the DB layer. After setup, the app started throwing an unhandled exception while trying to start. The exception stack trace referred to a "column not found" whilst the table had the column when I checked the database. I just happened to check the query being executed by the application normally by using the SQL server management studio - and lo - it indeed complained of the missing column! I then realized that if I spelled the column name in the same case as being shown in Object Explorer, the query did succeed.
On further investigation, when I checked the server properties ( Right Click the server node in object explorer -> Properties; Click "General" under Select a Page pane; Look for the property named "Server Collation"), the collation was listed as "Latin1_General_CS_AI". When I googled for this, I got to know that the "CS" stands for case sensitive. To change it to case-insensitive, one must use "CI"
You can change the collation at the database level as follows:
1. Right click on the database name in object explorer and click properties
2. Under "select a page" (right pane) click on "Options"
3. Change the collation drop down to the desired collation
4. click Ok.
You can also accomplish the same by running the script from the master database:
ALTER DATABASE
NOTE: The database needs to be exclusively locked to perform this operation. This, in plain English, means that there should not be any connections to the database while doing this.
The following link demonstrates how to do this at a query level:
Steven Smith : Case Sensitive or Insensitive SQL Query
If you use a collation with each query, you needn't worry about DB collation!
All the best!
Thursday, April 15, 2010
How to find out if a user exists on a domain or system?
So here I was, trying to grope through the System.DirectoryServices namesapce trying to find a way to check if a given username exists in the domain. What I wanted to do is this: If you have ever opened your computer management console, you would have noticed that when you attempt to add a user to a group, the dialogue would check if the username exists. I was trying to achieve the same functionality programmatically.
My application was required to do the following:
1. Present the users with an input box to type in a username to add and a drop down to select the group in which to add them
2. Check if the username is valid
2.1 If the username is valid, check if it already exists as a member of the group selected
2.1.1 If the username is valid AND it does not exist in the group selected, add to the group
I was able to (with help from support.microsoft.com KB article) figure out how to check if the user is member of a group but was having trouble trying to figure out how to check if the user actually exists in the domain(Active Directory) or system (WinNT)
I landed on the following article from Stackoverflow.com:
Faster way to find out if a user exists on a system? - Stack Overflow
I'm quoting the code snippet for quick reference:
If you are running this code from a machine which is not in the domain (or in a domain which does not have a trust relationship with user's domain) you would have to launch this thread as a security principal (that is a user) that has access to the target domain. The following MSDN link should get you started in doing that.
My application was required to do the following:
1. Present the users with an input box to type in a username to add and a drop down to select the group in which to add them
2. Check if the username is valid
2.1 If the username is valid, check if it already exists as a member of the group selected
2.1.1 If the username is valid AND it does not exist in the group selected, add to the group
I was able to (with help from support.microsoft.com KB article) figure out how to check if the user is member of a group but was having trouble trying to figure out how to check if the user actually exists in the domain(Active Directory) or system (WinNT)
I landed on the following article from Stackoverflow.com:
Faster way to find out if a user exists on a system? - Stack Overflow
I'm quoting the code snippet for quick reference:
NOTE: ContextType enum contains three values: Machine (to check on local machine), Domain (To verify user on a domain) and ApplicationDirectory that represents AD LDS store.using System.DirectoryServices.AccountManagement; //Add System.DirectoryServices.AccountManagement in Project Reference
public bool IsUserFound(String UserName)
{bool UserExists;using (PrincipalContext pc = new PrincipalContext(ContextType.Domain))
{
UserPrincipal up = UserPrincipal.FindByIdentity(pc, IdentityType.SamAccountName, UserName);
UserExists = (up != null);
}
return UserExists;
}
If you are running this code from a machine which is not in the domain (or in a domain which does not have a trust relationship with user's domain) you would have to launch this thread as a security principal (that is a user) that has access to the target domain. The following MSDN link should get you started in doing that.
Wednesday, April 7, 2010
Uninstalling a .NET service
Hello World,
Well, this should be pretty simple: InstallUtil.exe is provided with the .NET framework for manual install/uninstall as described here: MSDN Link
Basically what I like to do when running a command like this is to add it to the "Path" variable under my environment variables.
For example, if you are working with .NET framework 3.5, on a Windows server 2003 (32-bit Standard edition), InstallUtil.exe can be found at the following path:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
So, to be able to launch the utility without having to type the entire path, add the path (in italics above, based on your system configuration) to your "Path" environment variable.
For instructions on how to edit your environment variables, refer THIS link. Once done, to install a .NET service, open up a command prompt (Start -> Run -> cmd) and
browse to the path of your service executable. e.g. C:\Projects\MyWinService\MyWinService.exe and issue the command InstallUtil MyWinService.exe
What I managed to do (don't ask me why!) is delete the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MyWinService. After that all my attempts to uninstall the service were unsuccessful. Needless to mention, the service could not be installed again as Windows complained that "The service is already installed"
Then, I thought, let me try this: from a backup, I copied over the service executable to the folder where it should have been. Then, I issued the command
InstallUtil /u MyWinService.exe
[/u uninstalls the service]
and, as expected, the utility successfully un-installed the service!
[By the way, for .NET services, the old style -service command does not install them to the service controller. Neither does /uninstall remove them.]
Best Of luck!
Tuesday, April 6, 2010
Uninstalling Visual Studio 2008 (all versions)
Uninstalling Visual Studio 2008 (All Versions)
I was struggling to repair an installation of VS 2008 Team System (Development edition) and found this on the blog by Victor Chen
The following link should take you the download page for the uninstall tool:
Best of Luck!
adaptive path » ajax: a new approach to web applications
The guy who coined the term Ajax (As in Asynchronous JavaScript and XML for the un-initiated) : now a buzzword or shall I say indispensable for any web developer worth his salt (or sugar if you will!)
C# Using Reflection to list object properties programatically
Hello World!
Today a fellow colleague asked me if it is possible to programmatically access the fields of a custom type and check if they are null or empty and take some action based on the test.
I knew this had to be solved by using reflection by determining the runtime "Type" and then enlist members. On research, found this useful link:
There are several (working!!) examples here that demonstrate the technique.
All the best!
Visual Studio Setup - projects and custom actions
Hello World!
The following article from Simple Talk gives detailed procedures of adding custom actions to a Visual Studio setup project (.vdproj)
All The Best!
Subscribe to:
Posts (Atom)