Service Unavailable error while connecting to team foundation server.

         You may receive "Service Unavailable" error while connecting to Team Foundation Server(TFS). I was getting this error from yesterday whenever I was trying to connect to TFS. Finally today I found the reason and solution. I had changed the password of Administrator user which I had used to configure TFS Application Pool. This has stopped the application pool as the account which is running the TFS Application Pool has old password. So one has to change the password in TFS Application pool. To change password in application pool, click Start - Run and type inetmgr. This will open IIS. Now select the TFS application pool.

           Right click on TFS Apppool and select properties. Select Identity tab. Here you have to enter a new password for this account and click ok. Restart the IIS and try to connect to TFS. This time you will not get the "Service Unavailable" error.

       You will get this same error if you have deleted the user which is used to configure TFS Application pool. Here is the good article from Microsoft. 

Happy Programming !!

The media set has 2 media families but only 1 are provided.

      You get this error while restoring the database backup file in SQL Server 2005. This is because when you took the backup your backup file is divided in more than one part and at the time of restoring the database you have not added all the parts.

       Lets do this practically so that you can have better idea. Take the backup of any database you have. To take the backup right click on database and select the task and click on backup. 

         Once you click on Backup you have to select the location for .back file on database. If there are two paths in path selection box as shown in image below, the backup file is generated in two parts and saved at two different locations.

        So in this case, we have two backup files generated for selected database. Now at the time of restoring if you are assigning only one file and try to restore the database, you will get "The media set has 2 media families but only 1 are provided." error.

        To solve this issue you have to add all the files (in our case two files) in file selection box. Once you select all the backup files and click on restore, the database will restore successfully.

For more detail check here.

Happy Programming !!

Query to find all the tables and columns in selected database.

         As a developer, it is really important for us to understand database design and underlying tables used in application. Sometime we do not have direct access to database server so that we can not open the server console and look in to the database.

         In this case we can take help of SysObjects, SysColumns, SysTypes tables of SQL Server 2005. These tables stores the information about each tables and columns and their data types. Using this tables you can write the query to find out all the tables and columns in selected database. Below is the query that gives you all the table and columns for those tables with data types and length.

 

   1: SELECT 

   2:     SysObjects.[Name] as TableName,

   3:     SysColumns.[Name] as ColumnName,

   4:     SysTypes.[Name] As DataType,

   5:     SysColumns.[Length] As Length

   6: FROM

   7:     SysObjects INNER JOIN SysColumns

   8:     ON SysObjects.[Id] = SysColumns.[Id]

   9:     INNER JOIN SysTypes 

  10:     ON SysTypes.[xtype] = SysColumns.[xtype]

  11: WHERE

  12:     SysObjects.[type] = 'U'

  13: ORDER BY 

  14:     SysObjects.[Name]

Fig - (1) Query to find all the tables and columns for those tables.

 

           "Type" columns of SysObjects table represent the different objects available in database (like Table,Trigger,Stored Procedures etc.). Below list explains the different values of "Type" columns.

   1: C = CHECK constraint 

   2: D = Default or DEFAULT constraint

   3: F = FOREIGN KEY constraint 

   4: FN = Scalar function

   5: IF = Inlined table-function

   6: K = PRIMARY KEY or UNIQUE constraint 

   7: L = Log

   8: P = Stored procedure

   9: R = Rule

  10: RF = Replication filter stored procedure

  11: S = System table 

  12: TF = Table function

  13: TR = Trigger

  14: U = User table

  15: V = View

  16: X = Extended stored procedure

Fig - (2) Explanation of different values of "Type" columns in SysObjects table.

 

          For more detail refer SysObjects and SysColumns.  and So in fig - (1) query uses [type] = 'U'. This means query displays al the user tables. You can change the condition in WHERE clause to get different objects. The query shown below displays all the triggers in selected database.

 

   1: SELECT 

   2:     b.[Name] as [Table Name],

   3:     a.[Name] as [Trigger Name],

   4:     a.[crdate] as [Created Date] 

   5: FROM 

   6:     SysObjects a 

   7:     INNER JOIN Sysobjects b

   8:     ON a.[parent_obj] = b.[id]

   9: WHERE 

  10:     a.[type] = 'TR'

  11: ORDER BY 

  12:     b.[Name]

Fig - (3) Query to display all the Triggers for selected database.

 

Happy Programming !!