Thursday, February 23, 2012

CLR version.

These are some ways to find CLR version installed in any Windows PC.

1) CLRVer.exe
2) Existance of mscoree.dll file in %SystemTools%\system32

Happy programming :)

Undocumented sys function - fn_DBLog()

I come across interesting function sys.fn_dblog() and bit suprised, it clearly gives in depth info of Transaction log.

Here is the TSQL-query.

Select * from sys.fn_dblog(null,null)

Happy Programming.... :)

Tuesday, February 21, 2012

SQL Server Agent status via TSQL

Today, I just came across a requirement to find Status of SQL Server Agent via TSQL.
Here is it...

EXEC xp_servicecontrol querystate , N'SQLServerAGENT'

More info on http://www.mssqltips.com/sqlservertip/2036/monitor-start-and-stop-sql-server-services-using-xpservicecontrol/.

Happy programming.. :)

Creating table from other Table.- MS SQL Server

There is always an easy was to create table by having other table using below statement in MS SQL Server.

Select * into T1 from T2 where 1=2 

But big problem over here is if any column has BLOB data type(ntext, XML, varbinary) by default it stored in PRIMARY filegroup. If any situation we need to create table in different File group will end up with problem.
and I just solved using this Procedure created by myself.

IF OBJECT_ID('dbo.P_Arc_CreateTable') IS NOT NULL

DROP PROC dbo.P_Arc_CreateTable
GO
Create Proc dbo.P_Arc_CreateTable
(@SourceTableName nvarchar(100)
,@TargetTableName nvarchar(100)
,@FileGroup nvarchar(20)=NULL
,@PartitionSchemeFG nvarchar(200)=NULL
)
as
BEGIN
Declare @TableName as nvarchar(100)
DEclare @SQL as nvarchar(max)
Declare @ShemaName as nvarchar(10)
Declare @ErrorMessage as nvarchar(1000)

IF EXISTS (Select 1 from sys.columns where object_id = object_ID(@SourceTableName) and is_ansi_padded = 1)
SET @SQL = ' SET ANSI_PADDING ON ' +char(10)
ELSE
SET @SQL = ' SET ANSI_PADDING OFF ' +char(10)
SET @ShemaName = (Select SCHEMA_NAME(t.SCHEMA_ID) from sys.tables t where name = @SourceTableName)
SET @SQL =  @SQL + ' CREATE TABLE ['+@ShemaName+'].['+@TargetTableName+']('+ char(10)
Select 
@SQL = @sql 
+ '['+C.COLUMN_NAME  + '] [' + C.DATA_TYPE +']'
+ CASE WHEN C.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN ' ('+ rtrim(cast(C.CHARACTER_MAXIMUM_LENGTH as CHAR))+')' ELSE '' END
+ CASE WHEN Cl.user_type_id in (106,108) THEN ' ('+ rtrim(cast(C.NUMERIC_PRECISION as CHAR))+','+RTRIM(CAST( c.NUMERIC_SCALE AS CHAR))+ ')' ELSE '' END
+ case when cl.is_identity = 1 THEN ' IDENTITY(1,1) ' ELSE '' END
+ CASE WHEN len(C.COLUMN_DEFAULT )> 0 THEN ' DEFAULT ' + rtrim(CAST(C.COLUMN_DEFAULT AS CHAR))  ELSE '' END--THEN ' DEFAULT ' + c.COLUMN_DEFAULT ELSE '' END
+ ' ' + CASE WHEN C.COLLATION_NAME IS NOT NULL THEN ' COLLATE '+ C.COLLATION_NAME ELSE '' END 
+ ' '+ CASE WHEN C.IS_NULLABLE = 'YES' THEN 'NULL' ELSE 'NOT NULL' END
+',' + CHAR(10)
from INFORMATION_SCHEMA.COLUMNS C 
inner join sys.columns cl
on cl.object_id = object_id(c.Table_Name) 
and cl.name = c.COLUMN_NAME 
where Table_Name = @SourceTableName
order by c.ORDINAL_POSITION 


SET @SQL = Substring(@SQL,0,Len(@SQL)-1)
IF (@PartitionSchemeFG IS NOT NULL AND @PartitionSchemeFG <> '')
SET @SQL = @SQL + char(10)+ ') ON '+@PartitionSchemeFG
ELSE
SET @SQL = @SQL + char(10)+ ') ON['+@FileGroup+ ']'
BEGIN TRY
IF OBJECT_ID(@TargetTableName) IS NOT NULL
BEGIN
PRINT 'Table - ' + @TargetTableName + ' already exists.'
RETURN
END
ELSE
BEGIN
EXEC (@SQL)
--print @SQL
PRINT 'Created Table - ' + @TargetTableName + ' successfully.'
END
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE(); 
PRINT 'Error: In creating table - '+ @TargetTableName+'.' 
RAISERROR(@ErrorMessage,16,1)
END CATCH
--Print @SQL
END
--dbo.P_Arc_CreateTable @SourceTableName = 'PromotionInstance',@TargetTableName = 'PromotionInstance_SWD' ,@FileGroup='FG1'
GO

It supports creating an table in Primary File Group, user defined filegroup  or in Partition Schema.

Happy Programming ..:)