Archive for October, 2011

http://www.codeproject.com/KB/ajax/IntroAjaxASPNET.aspx

ASP.NET callbacks for AJAX using ICallbackEventHandler interface, AJAX Pro (http://ajaxpro.codeplex.com/) some interesting techniques which are good to know even though we have some latest and simple AJAX techniques like JQuery AJAX.

Advertisements

http://javarevisited.blogspot.com/2011/09/how-to-write-production-quality-code.html

A friend shared this link. Most of the experienced people will surely agree this but its worth to read and refresh ourself once again.

I have used 3 Procedures to make Truncate Table easy, along with truncating all the related tables for a given table.

— TruncateTable is the main proc to be used to see all tables that are getting truncated by not giving 2nd parameter and once you satisfy with the list of tables that are going to be affected then you can run the same proc by giving 2nd parameter as 0 (Zero).

CREATE PROCEDURE [dbo].[TruncateTable]
 @TABLE       VARCHAR(100),
 @NoTruncateOnlyShowRelations BIT = 1
AS
BEGIN

 DECLARE  @LEVEL INT

 SET @LEVEL = 1
 CREATE TABLE #FKKeysTable
 (PKTableName VARCHAR(100), PKColumnName varchar(100),
 FKTableName varchar(100), FKColumnName varchar(100), ForeignKey varchar(100), DROPCon BIT, CREATECon BIT, NESTLEVEL INT)
     
 IF @NoTruncateOnlyShowRelations = 0
 BEGIN
  EXEC TruncateTableRec @TABLE, @LEVEL
 END
 ELSE
 BEGIN
  EXEC ShowTruncateTableRec @TABLE, @LEVEL
 END

 SELECT
  *
 FROM
  #FKKeysTable
 ORDER BY
  NESTLEVEL
 
 
 DROP TABLE #FKKeysTable
END
GO
————————————–

CREATE PROCEDURE [dbo].[TruncateTableRec]
 @TABLE   VARCHAR(100),
 @LEVEL   INT
AS
BEGIN

 DECLARE @PKTableName varchar(255),
   @PKColumnName varchar(255),
   @FKTableName varchar(255),
   @FKColumnName varchar(255),
   @ForeignKey varchar(255),
   @SQL varchar(3000)
 
 INSERT INTO #FKKeysTable
  SELECT
   OBJECT_NAME (f.referenced_object_id) AS PKTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS PKColumnName, 
   OBJECT_NAME(f.parent_object_id) AS FKTableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS FKColumnName,
   f.name AS ForeignKey,
   0,
   0,
   @LEVEL
  FROM
   sys.foreign_keys AS f
   INNER JOIN sys.foreign_key_columns AS fc ON fc.constraint_object_id = f.OBJECT_ID   
   LEFT OUTER JOIN #FKKeysTable AS fkt ON fkt.PKTableName = @TABLE
  where
   OBJECT_NAME (f.referenced_object_id) = ” + @TABLE + ”     
   AND (fkt.PKTableName IS NULL AND fkt.PKColumnName IS NULL AND fkt.FKTableName IS NULL
   AND fkt.FKColumnName IS NULL AND fkt.ForeignKey IS NULL)
  order by
   PKTableName
   

 SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
  @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
 FROM #FKKeysTable
 where DROPCon = 0 AND NESTLEVEL = @LEVEL
 
 WHILE (ISNULL(@ForeignKey, ”) <> ” )
 BEGIN
  PRINT @TABLE + ‘1’   
  
  SET @SQL = ‘IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N”[dbo].[‘+@ForeignKey+’]”) AND parent_object_id = OBJECT_ID(N”[dbo].[‘+@FKTableName+’]”)) ALTER TABLE [dbo].[‘+@FKTableName+’] DROP CONSTRAINT [‘+@ForeignKey+’]’
  
  EXEC (@SQL)
     
  UPDATE #FKKeysTable  SET DROPCon = 1 WHERE ForeignKey = @ForeignKey
  
  SET @ForeignKey = ”
  
  SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
   @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
  FROM #FKKeysTable
  where DROPCon = 0  AND NESTLEVEL = @LEVEL
 END
 
 PRINT @TABLE + ‘2’  
 SET @SQL = ‘TRUNCATE TABLE ‘ + @TABLE
 EXEC (@SQL)

 PRINT @TABLE + ‘3’  
 SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
  @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
 FROM #FKKeysTable
 where CREATECon = 0 AND NESTLEVEL = @LEVEL
 
 WHILE (ISNULL(@ForeignKey, ”) <> ” )
 BEGIN
  PRINT @TABLE + ‘4’
  
  SET @SQL = ‘DECLARE @LEVEL1 INT SET @LEVEL1 =’ + CAST(@LEVEL AS VARCHAR(10))+ ‘ SET @LEVEL1 = @LEVEL1 + 1 IF((SELECT COUNT(*) FROM ‘ + @FKTableName + ‘) > 0) EXEC dbo.TruncateTableRec ‘ + @FKTableName + ‘,@LEVEL1’
  EXEC (@SQL)    
  
  PRINT @TABLE + ‘5’
  SET @SQL = ‘ALTER TABLE [dbo].[‘+@FKTableName+’] ADD CONSTRAINT [‘+@ForeignKey+’] FOREIGN KEY([‘+@FKColumnName+’]) REFERENCES [dbo].[‘+@PKTableName+’] ([‘+@PKColumnName+’])’
  
  EXEC (@SQL)  
  
  UPDATE #FKKeysTable  SET CREATECon = 1 WHERE ForeignKey = @ForeignKey
  
  SET @ForeignKey = ”
  
  SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
   @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
  FROM #FKKeysTable
  where CREATECon = 0  AND NESTLEVEL = @LEVEL
 END
END
GO

———————————

CREATE PROCEDURE [dbo].[ShowTruncateTableRec]
 @TABLE   VARCHAR(100),
 @LEVEL   INT
AS
BEGIN

 DECLARE @PKTableName varchar(255),
   @PKColumnName varchar(255),
   @FKTableName varchar(255),
   @FKColumnName varchar(255),
   @ForeignKey varchar(255),
   @SQL varchar(3000)
 
 INSERT INTO #FKKeysTable
  SELECT
   OBJECT_NAME (f.referenced_object_id) AS PKTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS PKColumnName, 
   OBJECT_NAME(f.parent_object_id) AS FKTableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS FKColumnName,
   f.name AS ForeignKey,
   0,
   0,
   @LEVEL
  FROM
   sys.foreign_keys AS f
   INNER JOIN sys.foreign_key_columns AS fc ON fc.constraint_object_id = f.OBJECT_ID   
   LEFT OUTER JOIN #FKKeysTable AS fkt ON fkt.PKTableName = @TABLE
  where
   OBJECT_NAME (f.referenced_object_id) = ” + @TABLE + ”     
   AND (fkt.PKTableName IS NULL AND fkt.PKColumnName IS NULL AND fkt.FKTableName IS NULL
   AND fkt.FKColumnName IS NULL AND fkt.ForeignKey IS NULL)
  order by
   PKTableName 
 
 SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
  @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
 FROM #FKKeysTable
 where CREATECon = 0 AND NESTLEVEL = @LEVEL
 
 WHILE (ISNULL(@ForeignKey, ”) <> ” )
 BEGIN
  –if(@LEVEL + 1 < 31)
  –BEGIN
   SET @SQL = ‘DECLARE @LEVEL1 INT SET @LEVEL1 =’ + CAST(@LEVEL AS VARCHAR(10))+ ‘ SET @LEVEL1 = @LEVEL1 + 1 IF((SELECT COUNT(*) FROM ‘ + @FKTableName + ‘) > 0) EXEC dbo.ShowTruncateTableRec ‘ + @FKTableName + ‘,@LEVEL1’
   EXEC (@SQL)      
  –END
  
  UPDATE #FKKeysTable  SET CREATECon = 1 WHERE ForeignKey = @ForeignKey
  
  SET @ForeignKey = ”
  
  SELECT TOP 1  @PKTableName = PKTableName, @PKColumnName = PKColumnName,
   @FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
  FROM #FKKeysTable
  where CREATECon = 0  AND NESTLEVEL = @LEVEL
 END
 
END
GO

——————————-

— Testing
/*
CREATE TABLE [dbo].[PKTable](
 [Col1] [int] IDENTITY(1,1) NOT NULL,
 [Col2] [varchar](50) NULL,
 CONSTRAINT [PK_PKTable] PRIMARY KEY CLUSTERED
(
 [Col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[FKTable](
 [FKCol] [int] IDENTITY(1,1) NOT NULL,
 [Col1] [int] NOT NULL,
 [Col3] [varchar](50) NULL,
 CONSTRAINT [PK_FKTable] PRIMARY KEY CLUSTERED
(
 [FKCol] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[FKTableLeve2](
 [FKCol] [int] IDENTITY(1,1) NOT NULL,
 [Col1] [int] NOT NULL,
 [Col2] [int] NOT NULL,
 [Col3] [varchar](50) NULL,
 CONSTRAINT [PK_FKTableLevel2] PRIMARY KEY CLUSTERED
(
 [FKCol] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

— Add FOREIGN KEY Constraints
ALTER TABLE [dbo].[FKTable] ADD CONSTRAINT [FK_Col1] FOREIGN KEY([Col1])
REFERENCES [dbo].[PKTable] ([Col1])

ALTER TABLE [dbo].[FKTableLeve2] ADD CONSTRAINT [FKLevel2_Col1] FOREIGN KEY([Col1])
REFERENCES [dbo].[FKTable] ([FKCol])

ALTER TABLE [dbo].[FKTableLeve2] ADD CONSTRAINT [FKLevel3_Col1] FOREIGN KEY([Col2])
REFERENCES [dbo].[PKTable] ([Col1])

— INSERT Data
INSERT INTO PKTable VALUES(‘a’)
INSERT INTO PKTable VALUES(‘b’)

INSERT INTO FKTable VALUES(1,’a’)
INSERT INTO FKTable VALUES(2,’b’)

INSERT INTO FKTableLeve2 VALUES(1, 1, ‘a’)
INSERT INTO FKTableLeve2 VALUES(2, 1, ‘b’)

— Execute Procedure to see all the tables (including the recursive sub tables) that PKTable one is related with
EXEC TruncateTable ‘PKTable’
— Execute Procedure with flag 0 to truncate the data with out effecting the Foreign Key relations
EXEC TruncateTable ‘PKTable’, 0
*/

http://sqlservergeeks.com/blogs/RakeshMishra/sql-server-bi/76/delete-vs-truncate

Truncate table does not work in case if there is any foriegn key relation on the table. We have to drop the FK constraint before truncating the data and then we have to recreate the constraint.

Many places I have seen the following suggestion and it will not work.

 ALTER TABLE <TABLE1> NOCHECK CONSTRAINT ALL
ALTER TABLE <TABLE1> DISABLE TRIGGER ALL
TRUNCATE TABLE <TABLE1>
ALTER TABLE <TABLE1> CHECK CONSTRAINT ALL
ALTER TABLE <TABLE1> ENABLE TRIGGER ALL

More abt disabling indexes/constraints …

http://technet.microsoft.com/en-us/library/ms177456.aspx

If you have a situation to run some sql command across all user tables in the DB or across all DBs in any SQL Server then read the below link …

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

http://msdn.microsoft.com/en-us/library/cc671619.aspx

PacMan – The SQL Server Integration Services Package Manager is a utility designed to permit batch operations on arbitrary sets of SSIS packages. Users can select a single package, a Visual Studio project or solution or a file system folder tree and then validate or update all selected packages in one operation.

http://pacman.codeplex.com/