SQL Server Cluster Installation Error

I got the below shown error during SQL cluster installation. It can be resolved by doing the below command.

SETSPN -A MSSQLSVC/VirtualServerName.XX.XX.EDU:InstanceName <STARTUP ACCOUNT NAME>

Please refer the below URL for more information.

http://blogs.msdn.com/b/karthick_pk/archive/2009/03/27/installation-of-sqlserver2008-cluster-fails-on-windows2008-the-group-or-resource-is-not-in-the-correct-state-to-perform-the-requested-operation-exception-from-hresult-0x8007139f.aspx

Backup DataBase Users and Roles

Use the below script to back up users, roles, schema and secure permission from a DB.
1) Update the @DBNAME with the DB Name which you want to script out.
2) Update @STORAGEDBNAME with the DB Nam where you want to store the 
result.
3) Execute script in the same DB and get the result.

--select * from MSDB.DBO.TEMP_TABLE_FOR_USERS
DECLARE @DBNAME VARCHAR(50),
  @STORAGEDBNAME VARCHAR(50)
SET @DBNAME='<Your DB Name>'
SET @STORAGEDBNAME='MSDB'

SET NOCOUNT ON
DECLARE @CMD VARCHAR(350)
SET @CMD= 'IF  EXISTS (SELECT * FROM '+@STORAGEDBNAME+'.sys.objects WHERE object_id = OBJECT_ID('''+@STORAGEDBNAME+'.[dbo].[TEMP_TABLE_FOR_USERS]'')
   AND type in (''U''))
   DROP TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
   CREATE TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
   ([COMMAND] TEXT)'
   
EXEC (@CMD)
CREATE TABLE #TEMP
 (   NUMBER INT IDENTITY(1,1) NOT NULL, 
  USERNAME VARCHAR(100),
  ROLENAME VARCHAR(100),
  ISNTGROUP INT
 )
CREATE TABLE #TEMP1
 (   NUMBER INT IDENTITY(1,1) NOT NULL, 
  OBJECTNAME VARCHAR(100),
  TYPE_DESC VARCHAR(100),
  PERMISSION_NAME VARCHAR(100),
  STATE_DESC VARCHAR(100),
  USERNAME VARCHAR(100),
  SCHEMANAME VARCHAR(100)
 )
CREATE TABLE #TEMP2
 (   NUMBER INT IDENTITY(1,1) NOT NULL, 
  USERNAME VARCHAR(100),ISNTGROUP INT
  )

DECLARE @CM VARCHAR(100)
SET @CM='INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''USE '+@DBNAME+''''
EXEC (@CM)

DECLARE @CMD1 VARCHAR(500),
@USER INT,
@USERNAME VARCHAR(50)

SET @CMD1= 'SELECT U.NAME , G.NAME ,U.ISNTGROUP
            FROM '+ @DBNAME+'.DBO.SYSUSERS U,' +@DBNAME+'.DBO.SYSUSERS G,' +@DBNAME+'.DBO.SYSMEMBERS M
            WHERE   G.UID = M.GROUPUID
            AND G.ISSQLROLE = 1 AND U.UID = M.MEMBERUID AND U.NAME<>''dbo'''
INSERT INTO #TEMP
EXEC (@CMD1)

INSERT INTO #TEMP2
SELECT DISTINCT USERNAME,ISNTGROUP FROM #TEMP
DECLARE @CMD2 VARCHAR(5000)
SET @CMD2=  'DECLARE @COUNT INT,@USER INT,@ISNT INT
     SET @COUNT=1 SELECT @USER=COUNT(*) FROM #TEMP2 
     WHILE @USER>=@COUNT
     BEGIN
       SELECT @ISNT= ISNTGROUP FROM #TEMP2 WHERE NUMBER=@COUNT
       IF @ISNT=0
       BEGIN
      INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
      SELECT ''IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = ''''''+USERNAME+'''''')
      CREATE USER [''+USERNAME+''] FOR LOGIN ['' +USERNAME +''] WITH DEFAULT_SCHEMA=[DBO]'' FROM #TEMP2
      WHERE NUMBER=@COUNT 
      END
      ELSE
      BEGIN
      INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
      SELECT ''IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = ''''''+USERNAME+'''''')
      CREATE USER [''+USERNAME+''] FOR LOGIN ['' +USERNAME +''] '' FROM #TEMP2
      WHERE NUMBER=@COUNT 
      END
      SET @COUNT=@COUNT+1
     END'
EXEC (@CMD2)
--SELECT @CMD2
-------------------- Schema Starts Here ------------------

CREATE TABLE #TEMP3
 (   NUMBER INT IDENTITY(1,1) NOT NULL, 
  SCHEMANAME VARCHAR(100)
 )
INSERT INTO #TEMP3 SELECT NAME FROM SYS.SCHEMAS WHERE [SCHEMA_ID] BETWEEN 5 AND 16383


DECLARE @CMD5 VARCHAR(5000)
SET @CMD5= 'DECLARE @SCHEMA INT,
     @COUNT3 INT
   SET @COUNT3=1
   SELECT @SCHEMA=COUNT(SCHEMANAME) FROM #TEMP3
   WHILE @SCHEMA>=@COUNT3
    BEGIN
    INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
      SELECT ''IF NOT EXISTS (SELECT * FROM SYS.SCHEMAS WHERE NAME = ''''''+SCHEMANAME+'''''')
      EXEC SYS.SP_EXECUTESQL N''''CREATE SCHEMA [''+SCHEMANAME+''] AUTHORIZATION ['' +SCHEMANAME +'']'''''' FROM #TEMP3
      WHERE NUMBER=@COUNT3 SET @COUNT3=@COUNT3+1
    END'
     
     
EXEC (@CMD5)

-------------------- User Role Starts Here ---------------
DECLARE @CMD3 VARCHAR(5000)
SET @CMD3= 'DECLARE @ROLE INT,
     @COUNT2 INT
   SET @COUNT2=1
   SELECT @ROLE=COUNT(ROLENAME) FROM #TEMP
   WHILE @ROLE>=@COUNT2
    BEGIN
     INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''EXEC sp_addrolemember ''''''+ROLENAME+ '''''' ,''''''+ USERNAME+'''''''' FROM #TEMP 
     WHERE NUMBER=@COUNT2 SET @COUNT2=@COUNT2+1 
    END'
 
EXEC (@CMD3)

--------------------- Secure Permission Starts Here ---------------
INSERT INTO #TEMP1
SELECT O.NAME COLLATE LATIN1_GENERAL_CI_AS_KS_WS AS OBJECTNAME ,TYPE_DESC,
PERMISSION_NAME,STATE_DESC,U.NAME AS USERNAME,S.NAME AS SCHEMANAME
FROM SYS.DATABASE_PERMISSIONS P
INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID=MAJOR_ID
INNER JOIN SYSUSERS U ON U.UID=P.GRANTEE_PRINCIPAL_ID
INNER JOIN SYS.SCHEMAS S ON O.SCHEMA_ID=S.SCHEMA_ID
WHERE TYPE_DESC <>'SYSTEM_TABLE'

DECLARE @CMD4 VARCHAR(5000)
SET @CMD4= 'DECLARE @SECUR INT,
     @COUNT1 INT
   SET @COUNT1=1
   SELECT @SECUR=COUNT(*) FROM #TEMP1
   WHILE @SECUR>=@COUNT1
    BEGIN
     INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''''+STATE_DESC+'' ''+PERMISSION_NAME+'' ON ''+SCHEMANAME+''.''+OBJECTNAME+''  TO [''+USERNAME +'']''FROM #TEMP1 
     WHERE NUMBER=@COUNT1 SET @COUNT1=@COUNT1+1
    END'
EXEC (@CMD4)

-------------------- Final Output ---------------
DROP TABLE #TEMP
DROP TABLE #TEMP1
DROP TABLE #TEMP2
DROP TABLE #TEMP3
SET NOCOUNT OFF

Database Transaction Log Cannot be Shrunk Because it is Marked as “REPLICATION” (and Replication has not been Configured)

Finding the reason for not being able to truncate the transaction log requires using the DMV sys.databases and looking for the information under the log_reuse_wait_desc column. Most of the times the description on this column will be LOG_BACKUP, indicating that a transaction log backup operation is needed in order to truncate the transaction log file. In our case the result from this query was REPLICATION (I am using the AdventureWorks sample database here to illustrate this):
USE master;
GO
SELECT name, log_reuse_wait_desc, * FROM sys.databases
WHERE name = 'AdventureWorks';

Trying to restore the status of the transaction log file using the SQL Server Replication stored proceduresp_repldone will not fix the issue as the stored procedure will complain the database is not configured for replication. The fastest and easiest way to get rid of the REPLICATION mark in the transaction log file is to configure Snapshot replication for the database and then, afterwards, removing this configuration from the server.

Select the affected database as the database for Replication
You do not need to select all the objects for the Replication process, just select any table (one) on the database
Remember to create an initial Snapshot when asked to do so during the wizard
You can safely use the SQL Server Agent service account in the Security settings as this Replication will only be used temporarily
Once the Snapshot Replication Wizard completes the replication scenario successfully, run again the previous SELECT in sys.database; at this point the ‘log_reuse_wait_desc’ column should show either NOTHING or LOG_BACKUP:



If the status is still REPLICATION, execute the following instruction to force all pending transactions to be distributed:

EXEC sp_repldone @xactid = NULL, @xact_sgno = NULL, @numtrans = 0, @time = 0, @reset = 1;

Then we need to remove all replication objects from the database as we do not longer need any Replication for this database. First we drop the Publication (we do not need to drop subscribers firsts as we did not configured any):
USE AdventureWorks;
GO
EXEC sp_droppublication @publication = N'AW_Test_Publication'
USE master
GO
EXEC sp_replicationdboption @dbname = N'AdventureWorks', @optname = N'publish', @value = N'false';
Note that in this example our Publication name is “AW_Test_Publication”. In your case this name will vary depending on what you have specified during the Snapshot Replication Wizard.
Then we need to drop the Distributor:
USE master;
GO
exec sp_dropdistributor @no_checks = 1;
And make sure, finally, no replication objects remain on the database by running the following stored procedure:
USE master;
GO
sp_removedbreplication 'AdventureWorks';

SQL Server 2008 Configuration Manager WMI Error

I tried to open up the configuration manager but the following error showed up instead of the configuration manager mmc.
The error was “Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manager SQL Server 2005  or later servers with SQL Server Configuration Manager. Invalid class [0X80041010]”.

I followed the steps exactly as mentioned in the above blog post and it worked perfectly for SQL Server 2008 too. Here are the steps:

1. Open up command prompt using elevated privileges (Vista, Windows 7, Windows Server 2008) or normally in Windows XP or Windows 2003.
2. Navigate to the folder c:\Program Files (x86)\Microsoft SQL Server\100\Shared
3. Run the following command:  mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"      (or just “mofcomp.exe  sqlmgmproviderxpsp2up.mof” )
4. You should see a similar output in your command prompt:

Parsing MOF file: C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof

MOF file has been successfully parsed

Storing data in the repository…

Done!

Document Objects in DB

Its important that a DBA need to document all the objects in the DB for future reference. Its possible by executing the below query and save the output as html.


GO
Declare @i Int, @maxi Int
Declare @j Int, @maxj Int
Declare @sr int
Declare @Output varchar(4000)
--Declare @tmpOutput varchar(max)
Declare @last varchar(155), @current varchar(255), @typ varchar(255), @description varchar(4000), @createdat datetime, @modifiedat datetime, @objType varchar(255)
create Table #Tables  (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000), created datetime, modified datetime)
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(4000))
create Table #Fk(id int identity(1,1), Name varchar(155), col Varchar(155), refObj varchar(155), refCol varchar(155))
create Table #Constraint(id int identity(1,1), Name varchar(155), col Varchar(155), definition varchar(1000))
create Table #Indexes(id int identity(1,1), Name varchar(155), Type Varchar(25), cols varchar(1000))
create Table #ProcCode (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(max))
create Table #Params (id int identity(1,1), Name varchar(155), Type Varchar(155), Length int, Precision int,Direction char (5))


Print '<head>'
Print '<title>::' + DB_name() + '::</title>'
Print '<style>'   
Print ' body {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' td {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' th {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' background:#d3d3d3;'
Print ' }'
Print ' table'
Print ' {'
Print ' background:#d3d3d3;'
Print ' }'
Print ' tr'
Print ' {'
Print ' background:#ffffff;'
Print ' }'
Print ' </style>'
Print '</head>'
Print '<body>'


set nocount on
begin
insert into #Tables (Object_id, Name, Type, [description], created, modified)
Select o.object_id,  '[' + s.name + '].[' + o.name + ']', 
case when type = 'V' then 'View' when type = 'U' then 'Table' when type = 'P' then 'Proc' end,  
cast(p.value as varchar(4000)), o.create_date, o.modify_date
from sys.objects o 
left outer join sys.schemas s on s.schema_id = o.schema_id 
left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description' 
where type in ('U', 'V','P') and left(o.name,3) != 'sp_' and left(o.name,3) != 'sys'
order by type desc, o.name 
end
Set @maxi = @@rowcount
set @i = 1


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td colspan="3" style="height:50;font-size:14pt;text-align:center;"><a name="index"></a><b>Index</b></td></tr></table>'
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr</th><th>Object</th><th>Type</th></tr>' 
While(@i <= @maxi)
begin
select @Output =  '<tr><td align="center">' + Cast((@i) as varchar) + '</td><td><a href="#' + Type + ':' + name + '">' + name + '</a></td><td>' + Type + '</td></tr>' 
from #Tables where id = @i

print @Output
set @i = @i + 1
end
print '</table><br />'


set @i = 1
While(@i <= @maxi)
begin
--table header
select @Output =  '<tr><th align="left"><a name="' + Type + ':' + name + '"></a><b>' + Type + ':' + name + '</b></th></tr>',  @description = [description], @objType =type, @createdat = created, @modifiedat = modified
from #Tables where id = @i

print '<br /><br /><br /><table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td align="right"><a href="#index">Index</a></td></tr>'
print @Output
print '</table><br />'
if (@objType != 'Proc')
begin
print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Description</b></td></tr><tr><td>' + isnull(@description, '') + '</td></tr></table><br />' 

--table columns
truncate table #Columns 
begin
insert into #Columns  (Name, Type, Nullable, [description])
Select c.name, 
type_name(user_type_id) + (
case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
then '(' + cast(max_length as varchar) + ')' 
when type_name(user_type_id) = 'decimal'  
then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar)   + ')' 
else ''
end
), 
case when is_nullable = 1 then 'Y' else 'N'  end,
cast(p.value as varchar(4000))
from sys.columns c
inner join #Tables t on t.object_id = c.object_id
left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id  = c.column_id and p.name = 'MS_Description' 
where t.id = @i and t.type != 'Proc'
order by c.column_id
end
Set @maxj =   @@rowcount
set @j = 1

print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Table Columns</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Datatype</th><th>Nullable</th><th>Description</th></tr>' 

While(@j <= @maxj)
begin
select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td>' +  upper(isnull(Type,'')) + '</td><td align="center">' + isnull(Nullable,'N') + '</td><td>' + isnull([description],'') + '</td></tr>' 
from #Columns  where id = @j

print @Output
Set @j = @j + 1;
end


print '</table><br />'


--reference key
truncate table #FK
begin
insert into #FK  (Name, col, refObj, refCol)
select f.name, COL_NAME (fc.parent_object_id, fc.parent_column_id) , object_name(fc.referenced_object_id) , COL_NAME (fc.referenced_object_id, fc.referenced_column_id)     
from sys.foreign_keys f
inner  join  sys.foreign_key_columns  fc  on f.object_id = fc.constraint_object_id
inner join #Tables t on t.object_id = f.parent_object_id
where t.id = @i and t.type != 'Proc'
order by f.name
end

Set @maxj =   @@rowcount
set @j = 1
if (@maxj >0)
begin


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Refrence Keys</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Reference To</th></tr>' 


While(@j <= @maxj)
begin


select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td>' +  isnull(col,'') + '</td><td>[' + isnull(refObj,'N') + '].[' +  isnull(refCol,'N') + ']</td></tr>' 
from #FK  where id = @j


print @Output
Set @j = @j + 1;
end


print '</table><br />'
end


--Default Constraints 
truncate table #Constraint
begin
insert into #Constraint  (Name, col, definition)
select c.name,  col_name(parent_object_id, parent_column_id), c.definition 
from sys.default_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i and t.type != 'Proc'
order by c.name
end
Set @maxj =   @@rowcount
set @j = 1
if (@maxj >0)
begin


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Default Constraints</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Value</th></tr>' 


While(@j <= @maxj)
begin


select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td>' +  isnull(col,'') + '</td><td>' +  isnull(definition,'') + '</td></tr>' 
from #Constraint  where id = @j


print @Output
Set @j = @j + 1;
end


print '</table><br />'
end




--Check  Constraints
truncate table #Constraint
begin
insert into #Constraint  (Name, col, definition)
select c.name,  col_name(parent_object_id, parent_column_id), definition 
from sys.check_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i and t.type != 'Proc'
order by c.name
end
Set @maxj =   @@rowcount

set @j = 1
if (@maxj >0)
begin


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Check  Constraints</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Definition</th></tr>' 


While(@j <= @maxj)
begin


select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td>' +  isnull(col,'') + '</td><td>' +  isnull(definition,'') + '</td></tr>' 
from #Constraint  where id = @j
print @Output 
Set @j = @j + 1;
end


print '</table><br />'
end




--Triggers 
truncate table #Constraint
begin
insert into #Constraint  (Name)
SELECT tr.name
FROM sys.triggers tr
inner join #Tables t on t.object_id = tr.parent_id
where t.id = @i and t.type != 'Proc'
order by tr.name
end
Set @maxj =   @@rowcount

set @j = 1
if (@maxj >0)
begin


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Triggers</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Description</th></tr>' 


While(@j <= @maxj)
begin
select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td></td></tr>' 
from #Constraint  where id = @j
print @Output 
Set @j = @j + 1;
end


print '</table><br />'
end


--Indexes 
truncate table #Indexes
begin
insert into #Indexes  (Name, type, cols)
select i.name, case when i.type = 0 then 'Heap' when i.type = 1 then 'Clustered' else 'Nonclustered' end,  col_name(i.object_id, c.column_id)
from sys.indexes i 
inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id 
inner join #Tables t on t.object_id = i.object_id
where t.id = @i and t.type != 'Proc'
order by i.name, c.column_id
end


Set @maxj =   @@rowcount

set @j = 1
set @sr = 1
if (@maxj >0)
begin


print '<table width="1000"  border="0" cellspacing="0" cellpadding="0"><tr><td><b>Indexes</b></td></tr></table>' 
print '<table width="1000"  border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Type</th><th>Columns</th></tr>' 
set @Output = ''
set @last = ''
set @current = ''
While(@j <= @maxj)
begin
select @current = isnull(name,'') from #Indexes  where id = @j
 
if @last <> @current  and @last <> ''
begin
print '<tr><td align="center">' + Cast((@sr) as varchar) + '</td><td>' + @last + '</td><td>' + @typ + '</td><td>' + @Output  + '</td></tr>' 
set @Output  = ''
set @sr = @sr + 1
end


select @Output = @Output + cols + '<br />' , @typ = type
from #Indexes  where id = @j

set @last = @current
Set @j = @j + 1;
end
if @Output <> ''
begin
print '<tr><td align="center">' + Cast((@sr) as varchar) + '</td><td>' + @last + '</td><td>' + @typ + '</td><td>' + @Output  + '</td></tr>' 
end


print '</table><br />'
end
end
if (@objType = 'Proc')
begin


print '<table width="1000" border="0" cellspacing="0" cellpadding="0"><tr><td><b>Created:</b></td><td>' + cast(@createdat as varchar)+ '</td></tr><tr><td><b>Modified:</b></td><td>' + cast(@Modifiedat as varchar)+ '</td></tr></table><br />' 


--Proc Parameters
truncate table #Params 
begin
insert into #Params  (Name, Type, Length , Precision ,Direction)
--FOR 2005
SELECT p.name AS parameter_name
,TYPE_NAME(p.user_type_id) AS parameter_type
,p.max_length
,p.precision
,case when p.is_output = '0' then 'In' when p.is_output = '1' then 'Out' when p.is_output = '2' then 'In/Out' end 
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
INNER JOIN #tables t on p.object_id = t.object_id
WHERE t.id = @i
order by p.parameter_id
end
Set @maxj =   @@rowcount
set @j = 1


print '<table width="1000" border="0" cellspacing="0" cellpadding="0"><tr><td><b>Parameters</b></td></tr></table>' 
print '<table width="1000" border="0" cellspacing="1" cellpadding="0"><tr><th>Sr.</th><th>Name</th><th>Datatype</th><th>Length</th><th>Precision</th><th>Direction</th></tr>' 

While(@j <= @maxj)
begin
select @Output = '<tr><td align="center">' + Cast((@j) as varchar) + '</td><td>' + isnull(name,'')  + '</td><td>' +  upper(isnull(Type,'')) + '</td><td align="right">' + Cast((Length) as varchar)  + '</td><td align="right">' + Cast((Precision) as varchar)  + '</td><td align="center">' + Direction + '</td></tr>' 
from #Params  where id = @j

print @Output
Set @j = @j + 1;
end


print '</table><br />'



--table columns
truncate table #ProcCode 
begin
insert into #ProcCode  ([description])
--FOR 2005
SELECT replace(replace(m.definition,char(13),'</br>'),' ', '&nbsp;')
  FROM sys.sql_modules m, sys.procedures p, #tables t
  WHERE m.object_id = p.object_id 
  and p.object_id = t.object_Id
  and t.id = @i
end
Set @maxj =   @@rowcount
set @j = 1


print '<table width="1000" border="0" cellspacing="0" cellpadding="0"><tr><td><b>Code</b></td></tr></table>' 
print '<table width="1000" border="0" cellspacing="1" cellpadding="0"><tr><th>Description</th></tr>' 

While(@j <= @maxj)
begin
select @Output = '<tr><td>' + isnull([description],'') + '</td></tr>' 
from #ProcCode  where id = @j

print @Output
Set @j = @j + 1;
end


print '</table><br />'




end
    Set @i = @i + 1;
end


Print '</body>'
Print '</html>'


drop table #Tables
drop table #Columns
drop table #FK
drop table #Constraint
drop table #Indexes 
drop table #Params
drop table #ProcCode


set nocount off

Find All Constraints of a Database

Execute the below query to find all the constraints in a DB.



SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
           SCHEMA_NAME(schema_id) AS SchemaName,
           OBJECT_NAME(parent_object_id) AS TableName,
           type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' 

SQL 2005 Cluster Installation Error

See the below error during SQL Server 2005 Cluster Installation. To solve this issue i have done the below steps.

1) Cancelled the installation.

2) Installed only Database Service (Client Components was selected initially)  

    in the cluster.

3) Installed the client components after that.