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 ..:)

Sunday, December 26, 2010

META tag info in SEO

We often miss to consider META tag in web development. Basically its very useful for Search Engine Optimization (SEO).

Content given in META tag :

Content returned by Search Engine :

Sunday, December 27, 2009

.NET 3.5 Features

I just started on reading .Net 3.5 features and impressed with following great things, and listing for guys who want to start exploring .net 3.5.

1) LINQ - Gives differant ways of writing Data Access code,With new programming style. .Net uses built-in to execute this.

2) JS Intellisense - This was delivered in VS 2008. Same like our Server-Side code (i.e c#), even in JS we have auto-complete of methods and properties available for an JS objects. Explore more on :http://weblogs.asp.net/scottgu/archive/2007/06/21/vs-2008-javascript-intellisense.aspx

3) JS Debugger - Even this delivered in VS 2008. As like server-side debugging we can put breakpoint on any javascript code block and VS will execute that block when page get rendered. Now no need to use 'debugger' keyword on any JS block.This will work ASp.net 2.0 web page edited in VS 2008. Expore more on : http://weblogs.asp.net/scottgu/archive/2007/07/19/vs-2008-javascript-debugging.aspx

4)Debugging .Net Fx Classes - Using VS 2008, we can debug .Net framework class libraries by simple pression F11, With previous version by doing same will take to a meta-data information, but now will take to source code. This is very great feature and give good knowledge of exploring Microsoft development standards. Bit excited with feature .. !!!. Explore more on http://weblogs.asp.net/scottgu/archive/2007/10/03/releasing-the-source-code-for-the-net-framework-libraries.aspx.

Will keep editing these post by reading feature one-by-one.

Thanks,
Guna.

Monday, November 2, 2009

Converting any File to Byte[] and Byte[] to File.

Below C# code takes filepath, where file located as input parameter and gives byte array of that file.

public byte[] ReadFile(string sPath)
{
FileStream fStream = null;
try
{byte[] data = null;
FileInfo fInfo = new FileInfo(sPath);
long numBytes = fInfo.Length;
fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);
using (fStream)
{
BinaryReader br = new BinaryReader(fStream);
data = br.ReadBytes((int)numBytes);
}
return data;
}
catch { throw; }
finally { fStream.Dispose(); }
}


Below code snippets takes Byte array and File extention as input parameter and stores the file in application path under temp folder.

public byte[] ReadFile(string sPath)
{
FileStream fStream = null;
try
{byte[] data = null;
FileInfo fInfo = new FileInfo(sPath);
long numBytes = fInfo.Length;
fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);
using (fStream)
{
BinaryReader br = new BinaryReader(fStream);
data = br.ReadBytes((int)numBytes);
}
return data;
}
catch { throw; }
finally { fStream.Dispose(); }
}