Renjucool.com - "Life Runs on Code"

Compare Sql Server Table Constraints

2011-07-24T04:59:00.001-07:00

Hello friends, after a long period of time I’m back into my blog. As due to continuous projects didn’t get a long stretch to sit with my blog.

In a BI project I faced a lot challenges for migrating some huge databases which ended up with lot of dependencies on sql server schema and object comparison tools(Redgate Sql Data compare,Sql compare,Xsql) to make my SSIS to execute smoothly. Now I’m in a workshop for synchronizing two databases. In the middle of that I’m sharing some useful sql queries which will be useful for happy coding.


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_GET_ALL_CONSTRAINTS]') 
and objectproperty(id, N'isprocedure') = 1)
DROP PROCEDURE [dbo].[SP_GET_ALL_CONSTRAINTS]
go
CREATE PROC SP_GET_ALL_CONSTRAINTS
(
	@sourceServer nvarchar(1000),
	@targetServer nvarchar(1000)
)
--@targetServer db to be migrated(old db) eg 12.3
--@sourceServer is the new database having all new schemas scripts   eg:12.4
as
SELECT	
	'NEW'				AS CHANGE,
	DatabaseName		AS DATABASENAME,
	TABLE_NAME			AS TABLENAME,
	COLUMN_NAME			AS COLUMNNAME,
	CONSTRAINT_NAME		AS CONSTRAINTNAME,
	'N-A'				AS NEW_CONSTRAINTNAME,
	CONSTRAINT_TYPE		AS CONSTRAINTTYPE, 
	ORDINAL_POSITION	AS ORDINALPOSITION,
	REF_PK_KEY			AS REFERENCEPKKEY,
	ParentTable			AS ParentTable,
	ParentField			AS ParentField,
	TYPE_CLUSTER		AS TYPECLUSTER,
	CASE CONSTRAINT_TYPE WHEN 'PRIMARY KEY'
			THEN 'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT' + ' ' + CONSTRAINT_NAME + ' PRIMARY KEY (' + COLUMN_NAME + ')' 
			WHEN 'FOREIGN KEY' THEN 
						'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT '+ CONSTRAINT_NAME + ' ' + 
						CONSTRAINT_TYPE +  '(' + REF_PK_KEY + ') REFERENCES ' + ParentTable + '(' + ParentField + ')' 
			ELSE 'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT ' + CONSTRAINT_NAME + ' UNIQUE (' + COLUMN_NAME  + ')'
			END AS Script
FROM
	tbl_ALL_CONSTRAINTS
WHERE
	DatabASeName = @targetServer
AND
	CONSTRAINT_NAME NOT IN
	(
		SELECT 	CONSTRAINT_NAME
		FROM 	tbl_ALL_CONSTRAINTS
		WHERE	DatabaseName = @sourceServer
		)
UNION ALL
-- select the changed columns
SELECT	
	'MODIFIED'			AS CHANGE,
	v1.DatabaseName		AS DATABASENAME,
	v1.TABLE_NAME		AS TABLENAME,
	v1.COLUMN_NAME		AS COLUMNNAME,
	v1.CONSTRAINT_NAME	AS CONSTRAINTNAME,
	v2.CONSTRAINT_NAME	AS NEW_CONSTRAINTNAME,
	v1.CONSTRAINT_TYPE	AS CONSTRAINTTYPE, 
	v1.ORDINAL_POSITION AS ORDINALPOSITION,
	v1.REF_PK_KEY		AS REFERENCEPKKEY,
	v1.ParentTable		AS ParentTable,
	v1.ParentField		AS ParentField,
	v1.TYPE_CLUSTER		AS TYPECLUSTER,
	CASE v1.CONSTRAINT_TYPE WHEN 'PRIMARY KEY'
			THEN 'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT  ' 
					+ v1.CONSTRAINT_NAME  + ' ALTER TABLE dbo.' + v1.TABLE_NAME 
					+ ' ADD CONSTRAINT' + ' ' + v2.CONSTRAINT_NAME 
					+ ' PRIMARY KEY (' + v1.COLUMN_NAME + ')' 
			WHEN 'FOREIGN KEY' THEN 
						'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT ' + v1.CONSTRAINT_NAME  +  ' ALTER TABLE dbo.' + v1.TABLE_NAME 
						+ ' ADD CONSTRAINT '+ v2.CONSTRAINT_NAME + ' ' + v1.CONSTRAINT_TYPE  + ' (' + v1.COLUMN_NAME + ') REFERENCES ' 
						+ v1.ParentTable + '(' + v1.ParentField + ')'
			ELSE 'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT  ' 
					+ v1.CONSTRAINT_NAME + ' ALTER TABLE dbo.' + v1.TABLE_NAME + ' ADD CONSTRAINT ' + v1.CONSTRAINT_NAME + ' UNIQUE (' + v1.COLUMN_NAME  + ')'
			END AS Script
FROM 	
	tbl_ALL_CONSTRAINTS v1
JOIN
	tbl_ALL_CONSTRAINTS v2
ON
	v1.TABLE_NAME  = v2.TABLE_NAME
AND 
	v1.COLUMN_NAME=v2.COLUMN_NAME
AND
	v1.CONSTRAINT_TYPE=v2.CONSTRAINT_TYPE
AND	
	v1.DatabaseName = @sourceServer
AND	
	v2.DatabaseName = @targetServer
AND 
	v1.CONSTRAINT_NAME<>v2.CONSTRAINT_NAME
GO
SET ANSI_NULLS ON 
GO

The below script will populate all the sql server metadata information


----QUERY to Find out All the Constraints in Detailed
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CreateAllConstraintsInTable]') and objectproperty(id, N'isprocedure') = 1)
drop procedure [dbo].[sp_CreateAllConstraintsInTable]
go
CREATE PROCEDURE sp_CreateAllConstraintsInTable 
@sourceServer  nvarchar(30), --Database to be compared (New Database)  
@targetServer  nvarchar(30)  --Database to be compared with(Old Database)  
as 
declare @sql varchar(max)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_ALL_CONSTRAINTS]') and objectproperty(id, N'istable') = 1)
drop table [dbo].[tbl_ALL_CONSTRAINTS]

select @sql=

'	SELECT 
	u.TABLESCHEMA,
	u.DatabaseName,
	u.TABLE_NAME,
	u.COLUMN_NAME,
	u.CONSTRAINT_NAME,
	u.CONSTRAINT_TYPE,
	u.ORDINAL_POSITION,
	u.REF_PK_KEY,
	u.ParentTable,
	u.ParentField,
	u.TYPE_CLUSTER into tbl_ALL_CONSTRAINTS
	FROM 
	(
		(SELECT 
			TAB_CON.CONSTRAINT_SCHEMA AS TABLESCHEMA,
			TAB_CON.TABLE_CATALOG as DatabaseName,
			TAB_CON.TABLE_NAME AS TABLE_NAME,
			KEY_COL.COLUMN_NAME AS COLUMN_NAME, 
			TAB_CON.CONSTRAINT_NAME AS CONSTRAINT_NAME,
			TAB_CON.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
			KEY_COL.ORDINAL_POSITION AS ORDINAL_POSITION,
			RC.UNIQUE_CONSTRAINT_NAME  AS REF_PK_KEY,
			ccu.table_name AS ParentTable, 
			ccu.column_name as ParentField, 
			ISNULL(CLUST_TYPE.TYPE_DESC,'+ '''NOT APPLICABLE'''+') AS TYPE_CLUSTER 
		FROM '+
			@sourceServer+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TAB_CON 
		INNER JOIN 
			 '+@sourceServer+'.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KEY_COL  
		ON 
			TAB_CON.TABLE_SCHEMA =  KEY_COL.TABLE_SCHEMA  
		AND 
			TAB_CON.TABLE_NAME = KEY_COL.TABLE_NAME   
		AND 
			TAB_CON.CONSTRAINT_NAME = KEY_COL.CONSTRAINT_NAME  
		LEFT JOIN 
			 '+@sourceServer+'.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC  
		ON 
			RC.CONSTRAINT_NAME =  TAB_CON.CONSTRAINT_NAME
		AND 
			TAB_CON.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA  
		LEFT JOIN  '+@sourceServer+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		ON 
			RC.unique_constraint_schema = ccu.constraint_schema 
		AND 
			RC.unique_constraint_catalog = ccu.constraint_catalog 
		AND 
			RC.unique_constraint_name = ccu.constraint_name  
		LEFT JOIN  
			(SELECT IDX.OBJECT_ID,IDX.NAME,IDX.TYPE_DESC,SCHEMA_ID FROM SYS.INDEXES IDX JOIN SYS.OBJECTS OBJ ON IDX.[OBJECT_ID] = OBJ.[OBJECT_ID] ) 
		AS 
			CLUST_TYPE ON OBJECT_NAME(CLUST_TYPE.OBJECT_ID) = TAB_CON.TABLE_NAME 
		AND 
			CLUST_TYPE.NAME = TAB_CON.CONSTRAINT_NAME  
		AND 
			SCHEMA_NAME( CLUST_TYPE.[SCHEMA_ID]) = TAB_CON.CONSTRAINT_SCHEMA  
			)
			union all
		(SELECT 
			TAB_CON.CONSTRAINT_SCHEMA AS TABLESCHEMA,
			TAB_CON.TABLE_CATALOG as DatabaseName,
			TAB_CON.TABLE_NAME AS TABLE_NAME,
			KEY_COL.COLUMN_NAME AS COLUMN_NAME, 
			TAB_CON.CONSTRAINT_NAME AS CONSTRAINT_NAME,
			TAB_CON.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
			KEY_COL.ORDINAL_POSITION AS ORDINAL_POSITION,
			RC.UNIQUE_CONSTRAINT_NAME  AS REF_PK_KEY,
			ccu.table_name AS ParentTable, 
			ccu.column_name as ParentField, 
			ISNULL(CLUST_TYPE.TYPE_DESC,'+ '''NOT APPLICABLE'''+') AS TYPE_CLUSTER 
		FROM  
			 '+@targetServer+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TAB_CON 
		INNER JOIN 
			'+@targetServer+'.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KEY_COL  
		ON 
			TAB_CON.TABLE_SCHEMA =  KEY_COL.TABLE_SCHEMA  
		AND 
			TAB_CON.TABLE_NAME = KEY_COL.TABLE_NAME   
		AND 
			TAB_CON.CONSTRAINT_NAME = KEY_COL.CONSTRAINT_NAME  
		LEFT JOIN 
			'+@targetServer+'.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC  
		ON 
			RC.CONSTRAINT_NAME =  TAB_CON.CONSTRAINT_NAME
		AND 
			TAB_CON.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA  
		LEFT JOIN '+@targetServer+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		ON 
			RC.unique_constraint_schema = ccu.constraint_schema 
		AND 
			RC.unique_constraint_catalog = ccu.constraint_catalog 
		AND 
			RC.unique_constraint_name = ccu.constraint_name  
		LEFT JOIN  
			(SELECT IDX.OBJECT_ID,IDX.NAME,IDX.TYPE_DESC,SCHEMA_ID FROM SYS.INDEXES IDX JOIN SYS.OBJECTS OBJ ON IDX.[OBJECT_ID] = OBJ.[OBJECT_ID] ) 
		AS 
			CLUST_TYPE ON OBJECT_NAME(CLUST_TYPE.OBJECT_ID) = TAB_CON.TABLE_NAME 
		AND 
			CLUST_TYPE.NAME = TAB_CON.CONSTRAINT_NAME  
		AND 
			SCHEMA_NAME( CLUST_TYPE.[SCHEMA_ID]) = TAB_CON.CONSTRAINT_SCHEMA  
		)
	) u
	ORDER BY 
		u.TABLESCHEMA, u.CONSTRAINT_TYPE DESC, 
		u.TABLE_NAME,u.ORDINAL_POSITION'
exec (@sql)
go

Get this function also from here

Bom Sabado Worm Fix for Orkut

2010-09-26T01:02:00.001-07:00

Most of the new orkut users are affected with a worm called “BOM SABADO”. Please refrain from login into the new orkut, or you switch to the old one. This worm will send scraps to all of your orkut friends. For safe side you can change your google account password.

You can get out of this virus by using firefox addon Adblock plus

After installing Click go to Preferences-> Add filter and enter *tptoos.org/* .

Also Revoke the access to worm in your google account settings.

Go to google account settings
In Security ,”Change authorized websites”
Revoke the access from “stapi.snacktools.com”
Switch to old orkut, until google had a fix over it.

Happy Social Networking!!!!!!!!!!!!!!

HTTP Error 500.19 with IIS 7.0

2010-07-04T03:55:00.001-07:00

Last week i was deploying an application in IIS 7.0 freshly configured and got a HTTP 500.19 error as elaborated below

Description: The requested page cannot be accessed because the related configuration data for the page is invalid.

Error Code: 0x80070021

Notification: BeginRequest

Module: IIS Web Core

Requested URL: http://localhost:4341/webapplication1

Physical Path: c:\inetpub\wwwroot\webapplication1

Logon User: Not yet determined

Logon Method: Not yet determined

Handler: Not yet determined

Config Error: This configuration section cannot be used at this path. This happens when the section is locked at a parent level. Locking is either by default (overrideModeDefault="Deny"), or set explicitly by a location tag with overrideMode="Deny" or the legacy allowOverride="false".

Config File: \\?\c:\inetpub\wwwroot\webapplication1 \web.config

This due to the web.config section is get locked. By default whenever we install ASP.NET this section will be get unlocked. But sometime if we install IIS after ASP.NET installation it will not get unlocked. Here is the appcmd to unlock the section

%windir%\system32\inetsrv\appcmd unlock config -section:system.webServer/handlers

You need to run this command in Administrator mode, also go to control panel-> Turn windows feature on or off and under IIS install ASP.net.

Windows Phone 7: No Native Code Support

2010-06-08T00:04:00.000-07:00

As in the previous generations of Windows Mobile phones the newly arrived Windows Phone 7 does not have any Native Code(Native code is computer programming (code) that is compiled to run with a particular processor (such as an Intel x86-class processor) and its set of instructions) support.It will support application running in the RIA platform Silverlight runtime and XNA Game studio only. This will make most of the SmartPhone Application developers to refrain from the Windows Phone 7. The user interface overlay the .Windows Embedded CE 6.0 Release 3 kernel , runs only interpreted or managed code through the two runtime environments provided by Silverlight and XNA.

Eventually all the Silverlight developers will become windows mobile developers. Any way that's the new step to the mobile-RIA world, good gaming
platform for the windows mobile with minimum hardware requirements

VS 2010 –Some amazing features

2010-05-08T02:29:00.001-07:00

For the past some months I'm looking into what’s there will be with Visual Studio 2010.At its beta releases VS 2010 faced a series of problems(Installer crash,Add reference empty in Silverlight project, Smart device project creation problem). Now they fixed most of the issues in the latest releases.I’m describing some of the cool new features with 2010 IDE.

View Call Hierarchy

By this we can see the hierarchical list of function usage.We can see the parameter details and function location.

Data & Schema Comparer

Previously most of us struggle a lot in comparing data's and schemas in and between databases. Also there was some third party tools Sql tool belt from Redgate, its so expensive.In Visual Studio 2010(I'm using vs 2010 ultimate) there is a new schema and data comparison tool.You can select two different databases and run the comparison task.

Architectural and UML Model Explorer

This is quite useful for functional & architect.In the tool bat Select->Architecture->Windows->Architecture Explorer.Dig into it and do more!!!

Debugging With IntelliTrace

Visual Studio 2010 had a vast changes in the debugging sections.One of the cool feature is “IntelliTrace” which will more helpful while debugging the application as it provide a detailed picture of the debug info , you can record debug information and we can later look up into the previous debug info.

With IntelliTrace, you can actually see events that occurred in the past and the context in which they occurred. This reduces the number of restarts that are required to debug your application and the possibility that an error will fail to reproduce when you rerun the application.

Also we can pin-in values to variables while debugging!!!

Converting “Var” to Dataset

2010-05-08T02:26:00.001-07:00

After a long time back i’m into my blog…Didn’t get enough time to sit with the things..

In some cases we need to convert the anonymous datatype var to a dataset or datatable.we cant do it directly as so.Here is a

Simple method that will return the var or List item to dataset


   public static DataSet ToDataSet(List list)
    {

        Type type = typeof(T);
        DataSet ds = new DataSet("Company");
        DataTable dt = new DataTable("Contacts");
        ds.Tables.Add(dt);
        //Dynamically adding columns to data table
        foreach (var propInfo in type.GetProperties())
        {
            dt.Columns.Add(propInfo.Name, propInfo.PropertyType);

            //You can add columns manually also here
            if (propInfo.Name == "ID")
            {
                dt.Columns.Add("Photo");
            }
        }
        foreach (T item in list)
        {
            DataRow dr = dt.NewRow();
            foreach (var propInfo in type.GetProperties())
            {
                dr[propInfo.Name] = propInfo.GetValue(item, null);
            } 
            dt.Rows.Add(dr);
        }
        return ds;
    }

Xml Manipulation Using Linq-Some basic lessons

2010-02-17T09:40:00.000-08:00

The “functional construction” feature of Linq to Xml provides great usability in creating and modifying xml documents. XDocument object is used for the xml declarations. The XElement class constructor are used in resolving the xml entities.


string path = Server.MapPath(@"/Linq2XML/DataStore.xml");
        XDocument xd = XDocument.Load(path);
        var result = from c in xd.Elements("DataStore").Elements("Table") select new { oid = (string)c.Element("oid"), BuildName = (string)c.Element("BuildName"), Appserver = (string)c.Element("Appserver"), DBServerName = (string)c.Element("DBServerName"), dbname = (string)c.Element("DBName"), comments = (string)c.Element("comments") };

This will load the Xml file to your XDocument object and perform a linq operation to get the xml elements. We can use Descendants to return the filtered collection of matching XName elements.

For modifying the elements we can use the SetElementValue Method of XElement, that will set, add and remove child elements.


public void ModifyEnvironments(string oid,string buildName,string appServer,string dbServer,string dbName,string comments )
    {
        XDocument objdoc = XDocument.Load(HttpContext.Current.Server.MapPath(@"\Envdetails\DataStore.xml"));
        var items = from item in objdoc.Descendants("Table")
                    where item.Element("oid").Value == oid
                    select item;
        foreach (XElement itemElement in items)
        {
            itemElement.SetElementValue("BuildName", buildName);
            itemElement.SetElementValue("Appserver", appServer);
            itemElement.SetElementValue("DBServerName", dbServer);
            itemElement.SetElementValue("DBName", dbName);
            itemElement.SetElementValue("comments", comments);          
        }
        objdoc.Save(HttpContext.Current.Server.MapPath(@"\Linq2XML\DataStore.xml"));
    }

We can use Remove() to delete an element.Also can use RemoveContent method that will make an empty element tag()


 public void DeleteEnvironment(string oid)

    {
        XDocument objdoc = XDocument.Load(HttpContext.Current.Server.MapPath(@"\Linq2XML\DataStore.xml"));
        var items = (from item in objdoc.Descendants(@"Table")
                     where item.Element("oid").Value == oid
                     select item).FirstOrDefault();
        items.Remove();     
        objdoc.Save(HttpContext.Current.Server.MapPath(@"\Envdetails\DataStore.xml"));
    }

For adding new XElement we can use the Add() method


    public void AddEnvironments(string buildName, string appServer, string dbServer, string dbName, string comments)
    {
        XDocument objdoc = XDocument.Load(HttpContext.Current.Server.MapPath(@"\Envdetails\DataStore.xml"));
        XElement xe = objdoc.Descendants("DataStore").Last();
        xe.Add(new XElement("Table", new XElement("oid", GetMaxOid()), new XElement("BuildName", buildName)
            ,new XElement("Appserver",appServer),new XElement("DBServerName",dbServer),new XElement("DBName",dbName),
            new XElement("comments",comments)));
        objdoc.Save(HttpContext.Current.Server.MapPath(@"\Linq2XML\DataStore.xml")); 
    }

Please find the sample XML file (“DataStore.xml”)here

Location and Sensor Platform in Windows 7

2010-02-02T09:49:00.000-08:00

Cool , there are some nice sets of Location and Sensor API associated with windows 7.As now computers are portable like cellular phone the necessity of GPS enabled application are very useful. If your laptop doesn’t have any sensors installed it will take the default location provided by the user. This Location and sensor API open wide variety of applications.

References
http://msdn.microsoft.com/en-us/library/dd318936(VS.85).aspx
http://msdn.microsoft.com/en-us/library/dd464636(VS.85).aspx

Learn how to read the GPS co-ordinates, See this article.
http://blogs.msdn.com/coding4fun/archive/2006/10/31/912287.aspx
Even More..

Func – Flexible delegate to create reusable functions

2010-01-14T08:24:00.001-08:00

Today while googling i found an interesting feature of delegate Func<T,TResults> ,it allows us to represent a method that can be passed as a parameter without declaring a custom delegate explicitly and the method must have one parameter that is passed to it by value and must return a value.

In the following example we need to explicitly define a new delegate and assign a named method to it.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace BlogSamples
{
    delegate int ConvertMethod(int _x);
    class Program
    {
        static void Main(string[] args)
        {
            ConvertMethod objConv = SquareMe;
            int val = 5;
            //delegate is called
            Console.WriteLine(objConv(val));
            Console.ReadKey();
        }
        private static int SquareMe(int myInt)
        {
            return myInt * myInt;
        }
    }
}

Without explicitly defining a new delegate it can be simplified as below


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace BlogSamples
{
    class Program
    {
        static void Main(string[] args)
        {
            Func convertMethod = SquareMe;
            int val = 6;
            Console.WriteLine(convertMethod(val));
            Console.ReadKey();
        }
        private static int SquareMe(int myInt)
        {
            return myInt * myInt;
        }
    }
}

Please see the msdn link for more references.

Restoring backup of Partitioned Database in sql server 2008

2009-12-19T22:34:00.000-08:00

If we need to restore a database having some 'n' number of partition its very tedious to give all the partitioned filegroup to restore into a particular location in the database server.The below example we demonstrate to perform the task.Run the script in the master database.


IF Exists (SELECT '1' from sysobjects where name = 'RESTOREBKP_2008' and type ='p')
BEGIN
DROP PROCEDURE dbo.RESTOREBKP_2008
END
GO
CREATE PROCEDURE RESTOREBKP_2008
(
@DBName varchar(max), -- DATABASENAME
@BackupPath varchar(max), -- BACKUPFILEPATH
@Path varchar(max) -- PHYSICALPATH
)
AS
BEGIN

DECLARE
--Variables declaration
@ResFilesOnly Nvarchar(max),
@ResQuery Nvarchar(max),
@STR AS nVARCHAR(MAX),
@Logicalname as nvarchar(max),
@physicalname as nvarchar(max)

--- LOOP Variable
DECLARE @I AS INT, @J AS INT


set @ResFilesOnly='RESTORE FILELISTONLY
FROM DISK = '''@BackupPath''''

set @I = 1

DECLARE @RESTORE AS TABLE(
LogicalName nvarchar(128), PhysicalName nvarchar(500), Type char(1), FileGroupName nvarchar(128),
Size numeric(20,0), MaxSize numeric(38,0), FileID bigint, CreateLSN numeric(38,0),
DropLSN numeric(25,0) NULL, UniqueID uniqueidentifier, ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL, BackupSizeInBytes bigint, SourceBlockSize int,
FileGroupID int, LogGroupGUID uniqueidentifier NULL, DifferentialBaseLSN numeric(38,0) NULL,
DifferentialBaseGUID uniqueidentifier, IsReadOnly bit, IsPresent bit,TDEThumbprint varchar(500)
)
INSERT INTO @RESTORE
EXEC sp_executesql @ResFilesOnly

SELECT @J = MAX(FILEID) FROM @RESTORE
SET @STR = ''
WHILE @I <= @J
BEGIN
SELECT
@Logicalname = LTRIM(RTRIM(Logicalname)), @physicalname = LTRIM(RTRIM(REVERSE(LEFT(REVERSE(physicalname),CHARINDEX('\',REVERSE(physicalname))-1))))
FROM
@RESTORE
WHERE
FILEID = @I
SET @STR = @STR + 'MOVE N''' + @Logicalname + ''' TO N''' + @Path + @physicalname + ''', '

SET @I = @I + 1
END

--Restore Backup Query
set @ResQuery = 'RESTORE DATABASE ' + @dbname + ' FROM DISK = '''+ @BackupPath + ''' WITH '+ @STR +
N' NOUNLOAD, STATS = 10'

EXECUTE SP_EXECUTESQL @ResQuery
END

Execute this Stored Procedure in your master database as given below

EXEC RESTOREBKP_2008 '', '', ''

Note:-In case of any clarification please mail me to mail@renjucool.com

See this article in MSDN by me http://code.msdn.microsoft.com/Sql2008PartionedDb

Sql Server Reporting Service –Silverlight enabled

2009-12-16T10:56:00.001-08:00

For sometime before i’m thinking of the reports(SSRS/CR) to be enabled by silverlight technologies, so after googling i found out a amazing tool from perpetuumsoft to display SSRS reports in RIA enabled UI can do a deep zoom!!!!.Seems rocking!!!!

Debugging for Silverlight

2009-12-16T10:41:00.001-08:00

Find out the RIA platform silverlight debugging tool from MSFT,whdc. also see this blog regarding the memory leak problem in silverlight.

http://blogs.msdn.com/delay/archive/2009/03/11/where-s-your-leak-at-using-windbg-sos-and-gcroot-to-diagnose-a-net-memory-leak.aspx

Table Comparison in Sql Server

2009-12-04T05:09:00.000-08:00

SQL Server is having a command line tool (TableDiff) to compare the data in two tables.It will perform the following task.

A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
Perform a fast comparison by only comparing row counts and schema.
Perform column-level comparisons.
Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
Log results to an output file or into a table in the destination database.

eg:

"C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe" -sourceserver "SERVERNAME" -sourcedatabase "DBNAME" -sourceschema "dbo" -sourcetable "SOURCETABLE1" -sourceuser "sa" -sourcepassword "PASSWORD" -destinationserver "SERVERNAME" -destinationdatabase "DBNAME" -destinationschema "dbo" -destinationtable "SOURCETABLE2" -destinationuser "sa" -destinationpassword "PASSWORD" -dt -o "C:\Documents and Settings\renjuraj\My Documents\diff.xls"

Run the above command in command prompt.Please refer your sql server installation path(C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe).

Windows 7 Startup Animation Design

2009-12-03T10:23:00.001-08:00

View Full Album

Rolf Ebeling, a senior user experience lead for the User Experience Design and Research Team for Windows, Windows Live, and Internet Explorer, is the man who designed the 105-frame Windows 7 boot animation that millions see or will see every day for years to come. Along with developing the famous boot animation, he also helped with the appearance and functionality of the calculator. He's already confirmed that he'll be working on the next version of Windows. Ebeling was only with the company for four months before he was asked to start designing what would become four swirling balls of light that come together to form a pulsing Windows 7 flag. Although the sketches of the early boot animation concept pictured above don't show it, Ebeling said he looked everywhere for inspiration, including street lights in the rain, light reflecting off water, and fireflies. A self-taught designer with a degree in English literature, Ebeling was a creative director for Newsweek.com in New York before joining Microsoft in April 2008, his first software job.

Source :Microsoft

Longest Running Procedures in Sql Server

2009-11-22T23:14:00.000-08:00

Its useful to determine which SProc/Code have the greatest impact on the Server. Sometimes, that is determined by examining the I/O cost,sometimes by the Exectution Duration. In this eg, Total Impact is determined by examining the length of execution and the frequency of execution.


SELECT TOP 10 
 temp.text as ProcedureName, 
 s.execution_count as ExecutionCount, 
 isnull( s.total_elapsed_time / s.execution_count, 0 ) as AvgExecutionTime, 
 s.total_worker_time / s.execution_count as AvgWorkerTime, 
 s.total_worker_time as TotalWorkerTime, 
 s.max_logical_reads as MaxLogicalReads, 
 s.max_logical_writes as MaxLogicalWrites, 
 s.creation_time as CreationDateTime, 
 s.total_physical_reads as PhysicalReads,
 isnull( s.execution_count / datediff( second, s.creation_time,getdate()), 0 )as CallsPerSecond
FROM sys.dm_exec_query_stats s 
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) temp 
ORDER BY 
-- s.total_elapsed_time DESC 
s.execution_count desc

Transaction Log growing in Sql Server

2009-11-19T22:40:00.000-08:00

Transaction log in SQL Server is one of the most important parts of a SQL Server , as well as one of the most common generators of problems I see online.The following are the causes for transaction log growth,

1.Due to Uncommitted transactions. We can find if there are any open transactions using DBCC OPENTRAN.
2.Running DBCC REINDEX, CREATE INDEX operations with the database in Full Recovery model.
3.Running extremely large transactions like Bulk Insert, Select Into commands.
http://support.microsoft.com/kb/317375/

The following are the proactive measures in order to minimize the unexpected log file growth,

1.If you do not want point in time recovery of your databases then you can change the recovery model to Simple.
2.Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
3.Configure the automatic expansion of transaction log in terms of MB instead of %.
4.Backup the transaction log regularly to delete the inactive transactions in your transaction log if you are using full or bulk logged recovery model.
5.You can switch the recovery model to Bulk logged from full recovery model if you perform some bulk inserts, select into, bcp, alter index, create index commands because these operations will be minimally logged in bulk logged recovery model and after those operations are completed you can switch over to full recovery model.

Solutions

Take full backup of your database
Take t-log backup of your database frequently say every 30 or 15 minutes so that log file will not grow drastically
Shrink if you do not have any free space. You can perform this operation manually if required.
Generally avoid shrinking the database and keep it as the last option.

If you are in full recovery, then no it won't be truncated.run the following command:

SELECT DATABASEPROPERTYEX('Lorenzo347', 'RECOVERY')

If it returns FULL or BULK_LOGGED, then you will have to backup the log, either to disk or specifying the truncate only to get it truncate the space off. If you do that, then you might as well change your recovery model to SIMPLE and be done with this problem. If it says you are already in SIMPLE, then it should shrink without problem.

backup log <your database name> with truncate_only

Shrink Log files

DECLARE @LogFileName varchar(100)

SELECT @LogFileName = rtrim(name)

FROM dbo.sysfiles

WHERE Name like '%_log%'

dbcc SHRINKFILE(@LogFileName, 2)

Shrinks the log file to 2 MB

http://msdn.microsoft.com/en-us/library/aa258824(SQL.80).aspx

Note:Detaching and deleting the log is definitely not advisable
At best it forces you to take your database offline. Worst case is that you invalidate your entire database and have to restore from backup.

Index to be recreated in Sql Server

2009-11-15T22:49:00.001-08:00

When your database grows, the index fragmentation becomes too high,that will scale down the performance of the sql server. To overcome that we need to re-build the index, that should be done at the down time of the server.Here there is a query that will list all the index to be recreated/rebuild.It uses the following tables(sys.indexes,sys.tables,sys.schemas,sys.dm_db_index_physical_stats,sys.partitions). For detailed checkup change the last NULL in the dm_db_index_physical_stats call to 'SAMPLED' or even 'DETAILED'


SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name +'] ' +
CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD'
ELSE 'REORGANIZE' END +
CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' +
cast(ps.partition_number as nvarchar(max)) ELSE '' END
FROM sys.indexes AS ix INNER JOIN sys.tables t
ON t.object_id = ix.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
INNER JOIN (SELECT object_id, index_id,avg_fragmentation_in_percent, partition_number
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, NULL)) ps
ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT
partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id, index_id) pc
ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent > 10 AND
ix.name IS NOT NULL

VS 2010 Tranining kit

2009-11-09T11:07:00.000-08:00

MSFT released the training kit(october preview) of VS 2010 framework.Can download it from Microsoft website.The Beta 2 release of the Training Kit contains 15 presentations, 19 hands-on labs, and 13 demos. Many technologies are covered in this release, including: C# 4, VB 10, F#, Parallel Extensions, Windows Communication Foundation, Windows Workflow, Windows Presentation Foundation, ASP.NET 4, Entity Framework, ADO.NET Data Services, Managed Extensibility Framework, and Visual Studio Ultim

Click here to dowload from Microsoft

Getting Country List from CultureInfo

2009-10-28T06:16:00.001-07:00

We can use CultureInfo to get the list of countries/languages.Today i got a method to list all the countries, even it has some duplicate values that are elimated by a distinct linq query.


public void CountryList()
{
ArrayList cList=new ArrayList();
foreach (CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures & ~CultureTypes.NeutralCultures))
{
RegionInfo ri = new RegionInfo(ci.LCID);
cList.Add(ri.EnglishName);
}
var countries = cList.ToArray();
var i = (from temp in countries select temp).Distinct().OrderBy(s=>s);
foreach (var item in i)
{
listBox1.Items.Add(item.ToString()); 
}
}

Also an interesting thing with Textinfo to make the first letter of a string/sentence to capital.(Little bit tricky :-))

string temp = "MULTIDIMENSIONAL NEWTON RAPHSON";
string firstSmall = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(temp.ToLower()); //I made a trick to make the string to lower case
MessageBox.Show(firstSmall.ToString());

Output :Multidimensional Newton Raphson

Workstation & Server Garbage Collector

2009-10-27T04:52:00.000-07:00

There are two types of garbage collector,the workstation GC and Server GC.Workstation is the default on client versions of windows,but server is much faster on multicore machines.The Server GC utilize more memory

  
<configuration>  <runtime>    
 <gcServer enabled="true"/> 
 </runtime>
</configuration>

Note:All sample code is provided is for illustrative purposes only.These examples have not been thoroughly tested under all conditions.Myself, therefore, cannot guarantee or imply reliability,serviceability, or function of these programs.

Bugs in Sql Server

2009-10-25T11:24:00.000-07:00

Today i got noticed a bug in sql server 2005(also in SSMS 08) while executing a simple select statement

select 3 --(*)

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '--(*'.

If we put a space or any other characters before the "(" there is no error. Also
select 3 --)* not returning error.Any way this is a bug in sql server as it does not parse the comment statements

Also i got an error message while Parsing an sql script as

."Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded."

But the script is executing successfully and giving the accurate results!!!!

Last modified Db User Objects & Tables without Trigger in Sql server

2009-10-14T05:30:00.001-07:00

If you need to check the details of all DB User Objects by Last Modified Date try the following query

select name, modify_date, case when type_desc = 'USER_TABLE'
then 'Table'when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION')
then 'Function'end as type_desc from sys.objects where type in ('U', 'P', 'FN', 'IF', 'TF')and is_ms_shipped = 0
order by 2 desc
Find the below query that will list all tables without triggers in sql server.This is helpful when someone modify the table
make sure that there aren't any tables that have triggers on them so that stuff doesn't start to break after you make changes.

SELECT ob1.name FROM sysobjects ob1 LEFT JOIN sysobjects ob2 ON
ob1.id =ob2.parent_obj AND ob2.xtype = 'TR'
WHERE ob2.name IS NULL AND ob1.xtype = 'U'
ORDER BY ob1.name

ELMAH (Error Logging Modules and Handlers)

2009-10-14T04:19:00.000-07:00

HTTP modules and handlers can be used in ASP.NET to provide a high degree of componentization for code that is orthogonal to a web application, enabling entire sets of functionalities to be developed,packaged and deployed as a single unit and independent of an application. ELMAH (Error Logging Modules and Handlers) illustrates this approach by demonstration of an application-wide error logging that is completely pluggable. It can be dynamically added to a running ASP.NET web application, or even all ASP.NET web applications on a machine,without any need for re-compilation or re-deployment.The end-user(if allows) can view the exception/inner exception logged in the server,Its description and even the screen-shot of the
error page

It can be downloaded from
http://code.google.com/p/elmah/
http://msdn2.microsoft.com/en-us/library/aa479332.aspx
http://download.microsoft.com/download/E/9/7/E97DB6A9-3418-46DF-99CA-840
357FE4D72/MSDNElmah.msi

Using spt_values to generate junk datas

2009-10-12T21:38:00.001-07:00

master..spt_values can be used if you need to populate a table with 100 numbers from 1,or if you need to generate some Junk/Test data.Refer the query below that will generate data of various datatypes

select
abs(checksum(newid()))%10000 as n_id,
abs(checksum(newid()))*rand()/10000 as f_float,
substring(replace(cast(newid() as varchar(36)),'-',''),1,abs(checksum(newid()))%15) as s_string,
dateadd(day,0,abs(checksum(newid()))%100000) as d_date,
abs(checksum(newid()))%2 as b_byte
from master..spt_values
where type='p' and number between 1 and 200

Getting all reference of an Assembly

2009-10-12T04:25:00.001-07:00

To get the assembly reference in a ASP.NET web application i found a recursive method that will read from the csproject file.
Import these namespace also
using System.Xml.Linq;

public void LoadAssembly(string path)
{

int result=0;
XNamespace msbuild = "http://schemas.microsoft.com/developer/msbuild/2003";
XDocument projDefinition = XDocument.Load(path);
IEnumerable<string> references = projDefinition.Element(msbuild + "Project")
.Elements(msbuild + "ItemGroup")
.Elements(msbuild + "Reference")
.Elements(msbuild + "Name")
.Select(refElem => refElem.Value);
foreach (string reference in references)
{

richTextBox1.AppendText(reference);

richTextBox1.AppendText(Environment.NewLine);

}
}

If you need to loop through the IEnumerable iterator to find out other xml nodes u can use the following

using (IEnumerator<string> enumerator = references.GetEnumerator())
{

while (enumerator.MoveNext())
result++;

}
if (result == 0)
{

//Do ur xml operations

}

Trigger to monitor the entire database server

2009-10-11T23:52:00.001-07:00

Please find this trigger script to monitor the entire database activities and to log its records into audit tables.
It will work on Sql Server 2008.

USE [master]
-------Create table -------------------------
-- DROP TABLE AuditTrigger
CREATE TABLE [dbo].[AuditTrigger](
[OID] [bigint] IDENTITY(1,1) NOT NULL,
[Details] [varchar](max) NULL,
[Host] [varchar](20) NULL
)
GO
-----Create proc to insert into AuditTrigger table
IF EXISTS(SELECT 1 FROM sysobjects where name = 'PInsertAuditTrigger')
BEGIN
DROP PROCEDURE PInsertAuditTrigger
END
GO
CREATE PROC PInsertAuditTrigger(@Details varchar(max),@hostname VARCHAR(MAX))
AS
BEGIN
INSERT INTO master.dbo.AuditTrigger(DETAILS,HOST) SELECT @DETAILS,@HOSTNAME
END
-------- Trigger to monitor tables
GO
CREATE TRIGGER [Table_Trigger]
ON ALL SERVER
FOR create_table,alter_table,drop_table
AS
BEGIN
print 'Create/Alter table is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Create/Alter table'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
-------- Trigger to monitor procedures
GO
CREATE TRIGGER [Proc_Trigger]
ON ALL SERVER
FOR create_procedure,alter_procedure,drop_procedure
AS
BEGIN
print 'Create/Alter procedure is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Create/Alter procedure'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
-------- Trigger to monitor login,roles and database
GO
CREATE TRIGGER [Misc_Trigger]
ON ALL SERVER
FOR Create_role,drop_role,alter_role,create_database,drop_database,alter_database,create_view,alter_view,drop_view,create_login,drop_login
AS
BEGIN
print 'Illegal operation is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Illegal operation'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
GO

Removing all active connections from the db

2009-10-09T02:04:00.001-07:00

Use Master
Go
Declare @dbname sysname
Set @dbname = 'DBNAME'
Declare @spid int Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End

Sql Server 2008-Spacial Data

2009-10-08T09:41:00.000-07:00

Microsoft SQL Server 2008 delivers comprehensive spatial support that enables organizations to seamlessly consume, use, and extend location-based data through spatial-enabled applications which ultimately helps end users make better decisions.storing Lat/Long data in a geography datatype and being able to calculate/query using the functions that go along with it.It supports both Planar and Geodetic data.

•Use the new geography data type to store geodetic spatial data and perform operations on it

•Use the new geometry data type to store planar spatial data and perform operations on it

•Take advantage of new spatial indexes for high performance queries

•Use the new spatial results tab to quickly and easily view spatial query results directly from within Management Studio

•Extend spatial data capabilities by building or integrating location-enabled applications through support for spatial standards and specifications

DECLARE @g GEOMETRY
SET @g = 'LINESTRING ( 69 26, 69 23, 69 21, 67 20, 65 20,
63 18, 58 17, 52 17, 51 17, 49 17, 45 18, 44 20,
44 21, 42 26, 42 29, 42 32, 42 35, 43 35, 47 38,
50 41, 55 42, 58 42, 65 44, 66 44, 67 44, 68 45,
69 47, 70 48, 70 50, 71 51, 70 56, 68 60, 68 63,
66 65, 65 66, 63 68, 60 71, 59 71, 57 71, 55 71,
51 69, 45 65, 44 63, 42 62, 41 59, 41 57, 41 56,
41 54, 42 53 ,67 77 ,44 88)'
insert into location values(2,'renju',null,@g);

declare @p geometry
set @p= 'POLYGON ((30 30, 40 30, 40 40, 30 40, 30 30))'
insert into location values(3,'renju2',null,@p);

http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx

Sql Server Hidden Features - Part 2

2009-10-07T23:24:00.000-07:00

A lot of SQL Server developers still don't seem to know about the OUTPUT clause (SQL Server 2005 and newer) on the DELETE, INSERT and UPDATE statement.
It can be extremely useful to know which rows have been INSERTed, UPDATEd, or DELETEd, and the OUTPUT clause allows to do this very easily -

it allows access to the "virtual" tables called inserted and deleted (like in triggers):

DELETE FROM (table)OUTPUT deleted.ID, deleted.DescriptionWHERE (condition)

If you're inserting values into a table which has an INT IDENTITY primary key field, with the OUTPUT clause, you can get the inserted new ID right away:

INSERT INTO MyTable(Field1, Field2)OUTPUT inserted.IDVALUES (Value1, Value2)

And if you're updating, it can be extremely useful to know what changed - in this case, inserted represents the new values (after the UPDATE), while deleted refers to the old values before the UPDATE:

UPDATE (table)SET field1 = value1, field2 = value2OUTPUT inserted.ID, deleted.field1, inserted.field1WHERE (condition)

If a lot of info will be returned, the output of OUTPUT can also be redirected to a temporary table or a table variable (OUTPUT INTO @myInfoTable).

More tips

Use ctrl-0 to insert a null value in a cell
WITH (FORCESEEK) which forces the query optimizer to use only an index seek operation as the access path to the data in the table.
http://msdn.microsoft.com/en-us/library/bb510478.aspx
PIVOT and UNPIVOT
Save transactions
Select xact_state() --Returns whether transaction pending/committed
xp_fixeddrives --Returns the free disk space in the server
SELECT ASCII('A'),CHAR(65),CHAR(ASCII('A')),UNICODE(N'?'),NCHAR(923),NCHAR(UNICODE(N'?')) --Some Ascii/unicode features
select * from tablename tablesample(2 percent) --Returns the 2% of the records in the db.Alt select top 10 percent * from table
select cast(GETDATE()as varchar) as datetime --Returns the date time as formatted.
WITH TIES
The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group
select top 5 with ties * from scores order by name desc
SELECT Column FROM Table ORDER BY CHECKSUM(NEWID()) -- Return rows in a random order
master..xp_cmdshell to run commands from a command prompt on the server
master..xp_cmdshell 'dir d:\' --List the Directory
master..xp_cmdshell 'sc query "ccm6.5_ess"' --Query a service
master..xp_cmdshell 'sc start "ccm6.5_ess"' --Start a service

Two free security tools from Microsoft SDL team

2009-10-03T09:41:00.001-07:00

The SDL team here at Microsoft released a couple of new tools recently to help development teams verify the security of their software before they ship. BinScope Binary Analyzer and MiniFuzz File Fuzzer are both being offered as free downloads. The team took the time to make sure that both tools work as stand-alone tools as well as integrated into Visual Studio and Team System.

BinScope is a verification tool that has been used inside Microsoft for several years to help developers and testers confirm they are building their code to use compiler/linker protections required by the SDL. BinScope allows you to scan your code to verify you are setting important security protections such as /GS, /SafeSEH, /NXCOMPAT, and /DYNAMICBASE. In addition it checks to see that you are using .NET strong-named assemblies, good ATL headers, an up-to-date compiler, and not using dangerous constructs such as global function pointers.

Both of these tools are equipped to easily integrate with Visual Studio 2008 Pro as well as Team Foundation Server 2008 and Team System 2008. By installing BinScope as integrated, it can be launched and output results within the Visual Studio IDE. MiniFuzz can be installed as an external tool add-in. Both tools have easy-to-set integration with Team Foundation Server 2008 and compliment the SDL Process Template for VSTS.

Writing secure code is becoming very important to most development teams. I am glad to see the SDL team making these types of tools available to the Visual Studio development community and making it easier to ship more secure code.

Sql Server Hidden Features

2009-10-03T01:22:00.000-07:00

Stored Procedures

* sp_msforeachtable: Runs a command with '?' replaced with each table name (v6.5 and up)
* sp_msforeachdb: Runs a command with '?' replaced with each database name (v7 and up)
* sp_who2: just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up)
* sp_helptext: If you want the code of a stored procedure
* sp_tables: return a list of all tables
* sp_stored_procedures: return a list of all stored procedures
* xp_sscanf: Reads data from the string into the argument locations specified by each format argument.
* xp_fixeddrives:: Find the fixed drive with largest free space
* sp_help: If you want to know the table structure, indexes and constraints of a table
Functions

* HashBytes()
* EncryptByKey
* PIVOT command

TableDiff.exe

* Table Difference tool allows you to discover and reconcile differences between a source and destination table or a view. Tablediff Utility can report differences on schema and data. The most popular feature of tablediff is the fact that it can generate a script that you can run on the destination that will reconcile differences between the tables.
See More

Return rows in a random order
SELECT
SomeColumn
FROM
SomeTable
ORDER BY
CHECKSUM(NEWID())

In Management Studio, you can put a number after a GO end-of-batch marker to cause the batch to be repeated that number of times:

PRINT 'X'
GO 10

Will print 'X' 10 times. This can save you from tedious copy/pasting when doing repetitive stuff

Identity Coloumn Insert

Last week i need a scenario to insert values into a table having an identity coloumn only.

eg: create table test(id int identity)

Is it possible to insert values into the without setting identity insert off/on.I find out a method as follows.

insert into test() default values
go 20

This will insert 20 records to the table haaa...
Will update later..

Google Wave: the sky is falling!!!

2009-10-02T20:01:00.000-07:00

Google Wave is a product that helps users communicate and collaborate on the web. A "wave" is equal parts conversation and document, where users can almost instantly communicate and work together with richly formatted text, photos, videos, maps, and more. Google Wave is also a platform with a rich set of open APIs that allow developers to embed waves in other web services and to build extensions that work inside waves.

A wave is equal parts conversation and document. People can communicate and work together with richly formatted text, photos, videos, maps, and more.

A wave is shared. Any participant can reply anywhere in the message, edit the content and add participants at any point in the process. Then playback lets anyone rewind the wave to see who said what and when.

A wave is live. With live transmission as you type, participants on a wave can have faster conversations, see edits and interact with extensions in real-time.

So is Wave going to threaten RIA platforms? I don’t know. Is it even an RIA platform? I just think that all the messages about how Wave is pushing out things like Flash, Silverlight or JavaFX are unfounded at this point. They all serve purposes.I still requested for an invitation to join waves,but not yet received from google.One who got invites can invite 8 more people,if then so please invite me to mail@renjucool.com
http://wave.google.com

Sony announces PS3 Slim for $299

2009-08-20T11:09:00.000-07:00

Sony finally announced the much rumored PlayStation 3 Slim gaming console at the on-going GamesCom 2009 Expo in Cologne, Germany. Back in May, images of Sony PS3 Slim chasis and retail package were leaked. Sony Computer Entertainment CEO Kaz Hirai unveiled new PlayStation 3 Slim model at the GamesCon 2009 Expo.
Sony will offer PS3 Slim in North America and Europe based stores from Sept. 1 for $299 (Rs. 14,400 approx.). The Sony PlayStation 3 Slim CECH-2000A features 120GB HDD and will come with PS3 firmware 3.0 version. Sony said it currently has no plans to bring PS3 Slim in India. Interestingly, PS3News folks dug up some information about another PS3 Slim CECH-2000B model with 250GB HDD from FCC site.

Fat and huge looking Sony PS3 will shed weight and size in new PlayStation 3 Slim version that will pack 120GB storage. Sony has redesigned the internal design architecture of PS3 Slim - from main semiconductors to power supply unit. Hirai, at the launch, said that PS3 Slim is 33 percent smaller and 36 percent lighter than the previous PS3 models.

PS3 Slim has new 45nm process manufactured Cell processor that runs at the same clock speed as the old 60nm processor. Obviously this smaller processor would be more power efficient and power consumption can be brought down by two-thirds which will reduce fan noise. Sony has designed PS3 Slim to be used horizontally (as PS2 is used) unlike the earlier PS3 models. However, Sony offers a $24 (Rs. 1,200 approx.) stand to hold the PS3 Slim vertically.

Along with that, Sony has also slashed prices of the existing PS3 models by $100 (Rs. 4,800 approx.). So now, the 80GB HDD bearing PS3 will cost $299 (Rs. 14,400 approx.) and the 160GB HDD bearing PS3 will cost $399 (Rs. 19,200 approx.).

The PlayStation 3 80 GB prices for India have been slashed by 20 percent and now the console costs Rs. 19,990. Along with that, Sony India will also offer two Free Games: Uncharted: Drake's Fortune and GT5 Prologue.

Windows Azure Services Platform

2009-08-15T05:11:00.000-07:00

Windows® Azure is a cloud services operating system that serves as the development, service hosting and service management environment for the Windows Azure Platform. Windows Azure provides developers with on-demand compute and storage to host, scale, and manage Web applications on the Internet through Microsoft® data centers.

Windows Azure is currently in Community Technology Preview. Commercial availability for Windows Azure will likely be at the end of calendar year 2009.

Windows Azure is an open platform that will support both Microsoft and non-Microsoft languages and environments. To build applications and services on Windows Azure, developers can use their existing Microsoft® Visual Studio® 2008 expertise. In addition, Windows Azure supports popular standards and protocols including SOAP, REST, XML, and PHP.

We can use azure service for
* Add Web service capabilities to existing packaged applications
* Build, modify, and distribute applications to the Web with minimal on-premises resources
* Perform services (large-volume storage, batch processing, intense or large- volume computations, etc.) off premises
* Create, test, debug, and distribute Web services quickly and inexpensively
* Reduce costs of building and extending on-premises resources
* Reduce the effort and costs of IT management

On August 11,2009 we are releasing a new set of features for Windows Azure Blob. Windows Azure Blob enables applications to store and manipulate large objects and files in the cloud. The blobs (files) can be up to 50GB in size for the CTP.

All changes for this release are versioned changes, using “x-ms-version: 2009-07-17”. All prior versions of commands executed against the storage system will continue to work, as we extend the capabilities of the existing commands and introduce new commands.
http://www.microsoft.com/azure/netservices.mspx

Silverlight and Deepzoom

2009-08-15T04:25:00.000-07:00

Silverlight Deep Zoom provides the ability to view high resolution images rapidly without affecting the performance of your applications.
This page from silverlight.net has some quick start tutorials for Silverlight Deep Zoom.
http://memorabilia.hardrock.com/ has the best example for Silverlight Deep Zoom implementation.

Above page has hundreds of high resolution images of Rock Bands. Below are screenshots of Silverlight Deep Zoom in action.

I dont need to tell you who the guys are ;-)

A little bit of zoom out!!!

Some more…

more….

Some more…

Can you find the guys in the above picture? Let us zoom out some more..

more…

some more???

hmmm!! some more??

This is the beauty of Silverlight Deep Zoom. So next time when you send your large collection og your last picnic photos you dont have to send large attachments. Create a simple Silverlight site with Deep Zoom and send it with Style.

Data Recovery/Live Software-ERD Commander

2009-07-19T11:16:00.000-07:00

RD Commander is a very useful tool that Microsoft acquired with it’s purchase of Winternals. It’s especially useful for computers that aren’t able to boot into Windows, or even safe mode. ERD (Emergency Repair Disk) allows access to Windows restore points, file recovery, crash analysis, hotfix uninstall, and other low level operating system tasks, all in a very familiar Windows interface. It also provides network and internet access, as well as a web browser.

The full version is intended for IT professionals, and is available only as part of the Microsoft Desktop Optimization Pack for Software Assurance customers. However, you can download a free-trial as part of the Microsoft Diagnostics and Recovery Toolset. After downloading this toolset, install it to find an ISO file containing ERD Commander:

C:\Program Files\Microsoft Diagnostics and Recovery Toolset\erd50.iso

An ISO is a special file that when burned to a CD can create a bootable CD. Windows doesn’t support ISO burning without third-party software. ISO Recorder is a very simple, and free program for creating CDs from ISOs. After you’ve burned the CD from the ISO, simply boot the system from the CD you just created to start ERD Commander. If your system doesn’t boot from the CD, you might have to change the BIOS boot settings (boot order).

A couple of caveats. The trial version of ERD Commander is ERD Commander 2005. The latest version (currently ERD 6.0) is only shipping with the Desktop Optimization Pack. While version 6.0 is Vista compatible, 2005 is not (compatible with Windows NT, 2000, XP, and Server 2003). The trial period is 30 days. Next time you’re faced with an unbootable Windows XP system, give ERD Commander a try.

Converting Text to RSS Feeds

2009-05-02T05:03:00.000-07:00

Today i used created a good method to parse a raw text document to rss feeds using Generic Handlers.Please refer the code below.
<%@ WebHandler Language="C#" Class="Cricket" %>

using System;
using System.Web;
using System.Net;
using System.Text;
using System.Linq;
public class Cricket : IHttpHandler {

public void ProcessRequest(HttpContext context)
{
StringBuilder sb=new StringBuilder();
context.Response.ContentType = "text/xml";
sb.Append(@"

Score
http://renjucool.co.nr
Get latest score
(c) 2008 renjucool.co.nr

");
sb.Append("");
sb.Append("Score of "+DateTime.Now.ToShortDateString()+"");
sb.Append("Score of " + GetScore() + "");
sb.Append("Score of " + GetScore() + "");
sb.Append("Score of " + DateTime.Now.ToString("ddd, MMM yyyy hh:mm:ss tt") + "");
sb.Append("");
sb.Append(@"

");
context.Response.Write(sb.ToString());
}
public string GetScore()
{
string ret = "";
WebClient myClient = new WebClient();
String data=Encoding.ASCII.GetString(myClient.DownloadData
("http://renju.sparkonnet.com/files/raw.tx"));

foreach (string s in (from s in data.Split((char)10) select GetValue(s)))
{
ret += " "+s;
}
return ret;
}
public string GetValue(string inp)
{
if (inp.IndexOf("=") == -1) return inp;
return inp.Substring(inp.IndexOf("=") + 1);
}

public bool IsReusable {
get {
return false;
}
}

}
Please refer the screen shot as the code contains html tags

v$session for oracle and sql server

2009-04-23T09:41:00.000-07:00

select * from sys.sysprocesses

can be used for sql server instead of v$session in oracle

If you work with SQL Server or Oracle you at some point probably will need to determine your unique connection identifier. For SQL Server, the connection identifier is called a Server Process ID or SPID. It can be obtained from the global @@SPID as:
SELECT @@SPID
Once the SPID is determined, it can be used to query various system tables (e.g., sysprocesses, sysobjects, etc.) to obtain process information.
For Oracle, the connection identifier is called a Session ID or SID. To get the Session ID, the V$SESSION view must be queried as:

SELECT sid from v$session where audsid = userenv('sessionid');
When the SID is determined, it can be used to find out session information using other Oracle provided system views such as V$SQL.

Disk I/O:The Performance Bottle neck

2009-04-23T04:42:00.000-07:00

Many people think of "performance tuning" as optimizing loops, algorithms, and memory use. In truth, however, you don't get the huge performance gains from optimizing CPU and memory use (which is good), but from eliminating I/O calls.

Disk I/O is responsible for almost all slow websites and desktop applications. It's true. Watch your CPU use next time you open a program, or your server is under load. CPUs aren't the bottleneck anymore - your hard drive is. At the hardware level, the hard drive is the slowest component by an incredibly large factor. Today's memory ranges between 3200 and 10400 MB/s. In contrast, today's desktop hard drive speeds average about 50 MB/s (Seagate 500GB), with high-end drives getting 85MB/s (WD 640, Seagate 1TB). If you're looking at bandwidth, hard drives are 200-300 times slower. Bandwidth, though, isn't the killer - it's the latency. Few modern hard drives have latencies under 13 milliseconds - while memory latency is usually about 5 nanoseconds - 2,000 times faster.

You're probably looking at these numbers and thinking, "13ms is quite fast enough for me, and my app is only dealing with small files". However, I have a question: what other applications are using that drive? If you're on a shared server, the odds are high that between 25 and 2500 ASP.NET apps are being run on the same drive.

CPU, bandwidth, and memory throttling is becoming more and more common on shared servers and virtualization systems, but practical disk throttling isn't even on the horizon from what I can tell. Improper I/O usage from any app affects everybody.

Since hard drives are slow, pending operations go into a queue. So even if your app only needs a single byte of data from the hard drive, it still has to wait its turn. It's quite common for disk operations to take several seconds on a shared server under heavy load. If any application on the server is paging to disk from exessive memory use, it can take several minutes, causing a timeout.

Realistic I/O performance is really hard to simulate in a development environment. On a dedicated development machine, disk queues are short, and response times are usually near the optimal 13ms, which tends to give software developers gravely incrorrect ideas about the performance characteristics of their application.

This is one of my favourite article i read,refer Click to read more

String Searching in a database

2009-04-05T11:31:00.000-07:00

This is the query which searches for string in the entire DB.
Provide 2 inputs as DB name and Search string.
This query takes more time, but will sure get you the results.
You can customize the search criteria…

----------------------------------------------------------
Use [Database_Name]
go
declare @SearchChar varchar(8000)
Set @SearchChar = 'Search_Text' -- Like 'renju%', '11/11/2006'
declare @CMDMain varchar(8000), @CMDMainCount varchar(8000),@CMDJoin varchar(8000)
declare @ColumnName varchar(100),@TableName varchar(100)
declare dbTable cursor for
SELECT
Distinct b.Name as TableName
FROM sysobjects b
WHERE
b.type='u' and b.Name <> 'dtproperties' order by b.name

open dbTable
fetch next from dbTable into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
declare db cursor for
SELECT c.Name as ColumnName
FROM sysobjects b,syscolumns c
WHERE C.id = b.id and
b.type='u' and b.Name = @TableName
order by b.name

open db
fetch next from db into @ColumnName
set @CMDMain = 'SELECT ' + char(39) + @TableName + char(39) + ' as TableName,'+
' ['+ @TableName + '].* FROM [' + @TableName + ']'+
' WHERE '
set @CMDMainCount = 'SELECT Count(*) FROM [' + @TableName + '] Where '
Set @CMDJoin = ''

WHILE @@FETCH_STATUS = 0
BEGIN
set @CMDJoin = @CMDJoin + 'Convert(varchar(5000),[' +@ColumnName + ']) like ' + char(39) + @SearchChar + char(39) + ' OR '
fetch next from db into @ColumnName
end
close db
deallocate db
Set @CMDMainCount = 'If ('+ @CMDMainCount + Left(@CMDJoin, len(@CMDJoin) - 3)+ ') > 0 Begin '
Set @CMDMain = @CMDMainCount + @CMDMain + Left(@CMDJoin, len(@CMDJoin) - 3)
Set @CMDMain = @CMDMain + ' End '
Print @CMDMain
exec (@CMDMain)
fetch next from dbTable into @TableName
end
close dbTable
deallocate dbTable

Renju

Top Ten IIS Performance Optimization

2009-03-07T06:35:00.001-08:00

1) Enable HTTP Keep-Alive
This setting is enabled by default. Set a side that this could improve client connection experience, this must be enabled in order for integrated authentication or all connection based authentication to work.

2) Adjust Connection Timeouts
Right on the spot, as highlighted by the author. You may want to adjust this according to your needs. To me, I felt 120 seconds is way to long for a connection timeout. Typically, I set for 30 to 45 seconds, if "I', the IIS server take longer than that to response to client or waiting for data, I would just drop it. No point holding the resource for more than 30 seconds :) Obviously, you need to evaluate your environment to derive the correct timeout value.

3) Enable HTTP Compression
Yes, some prefer third party tool like httpzip or xcompress. While if you like me. I will stick with the built-in compression feature. Refer this kb to add in more document types for compression:
Click How to specify additional document types for HTTP compression

4) Grow a Web Garden
This can be tricky, although this will boots up your application response time, do take note that if you are using any session variables make sure you have out-of-process session management in place, otherwise - since all worker processes inside the web garden do not share the memory space, having an in-process session management will cause session detail lost if the request was previously handle by other worker process. The following KB explains about the session management for ASP.NET.
Click
INFO: ASP.NET State Management Overview

5) Adjust the IIS Object Cache TTL
Great suggestion, the default value is too fast. Typically, I would go for 60 seconds at least. Unless, your site is pure dynamic and content must not be cached. In related to this Object TTL, You would also have the UserToken TTL, by default this is 15mins, which I think suitable for most of the setup. If you have a pretty dynamic user accounts management. E.g. temp account created on the fly and only valid for a short period, you might want to shorter the value. With IIS 6, setting it to '0' will disable the UserToken cache. More info, refer
Click
Changing the Default Interval for User Tokens in IIS

6) Recycle
Specifically, apply to IIS 6 only. While most users will stick the default, I on the other hand will disable all recycling event coz I think if an application is well-written and tested. It should not behavior weird and causing issue :) Well, this is just my wish, in real life - especially those IIS that you manage? For sure haunting you from time to time. I remembered in the past where developer always complaint about “something wrong with your IIS configuration’, while you, the system admin on the other hand keep fighting back saying that you are innocent, it is the code that causing this. Until, you find way to prove that it has nothing to do with IIS and the problem is with the application, your boss would typically go with developer :) Anyway - back to the topic. If you are seeing problem with your application, you should use any of the recycle events to keep up the application availability until you figure out what's wrong. Also, always checked 'shutdown worker process after being idle for' for X minutes. Recycling is good, not only it refreshes your application (sometime I don't agreed with this view), but it also returns the unused resource by to the system pool. Oh ya! What do you do with IIS 5? Well, you can try the IIS5 process recycling tool, refer
Click
How to recycle IIS 5.0 with the IIS 5.0 Process Recycling tool

7) Limit Queue Length
I'm happy with the default limit, there's typo in the article. Default is 4000 not 1000 :) And of coz at any time - you should not see lot of queue requests, if you do see that, meaning you are either experiencing hardware bottleneck or something really wrong with your application.

8) Shift Priority to the Working Set
I am lazy :) I never really change this setting unless is for SQL server. When you running on a low power box with less cpu power and memory - you should look into this.

9) Add Memory
Mm.... to my standard - I have lot of budget - my web server are typically equipped with 2GB memory and this is good enough for most of the web application out there. Of coz, depending on the number of users and application nature - you may need more ram or even setup network load balancing. In the past, I have been seeing to boots up web server you need more CPU processing power, while database server needs tons of memory. That's just what I have experienced, anyway - for you to make the right call - always do a performance monitoring to determine if memory is a bottleneck.

10) Use Disk Striping
This is like a bonus. In my opinion, since you have no control for the IIS binaries reside on the system partition, you should have maximum read output for your website pages, and maximum write performance for the log files. Hence, you should have a mirrorset for web pages, and disk striping without parity for the log files. Also, it always the best practice to have the above (IIS binaries, Web pages, IIS log files) in three different partitions/drive and secure it properly with NTFS permissions.

Enabling HTTP Compression in IIS 6.0

2009-02-27T23:40:00.000-08:00

HTTP Compression is when a Web server receives a request for a file and instead of just serving the file to the client, it checks to see if the client browser (or application) is "Compression Enabled". If so, the Web server does a check on what type of file is being requested (this is determined by the file’s extension). If the file is marked as a static file, such as an HTML file, IIS will check the Compression Cache Temporary Directory.

Tip: To find the Compression Cache Temporary Directory, open up IIS and right-click on the Web Sites node and go to the Service tab. There is a text box that has a label next to it marked Temporary Directory, although it may not yet be enabled.

If a compressed version isn’t found, IIS will send an uncompressed version of the file to the client and a compressed version is placed in the temporary directory (IIS will only serve to the client from the temp directory). If the compressed version is found, IIS will send the file directly to the requesting client. If the requested file is a dynamic file, such as an ASP.NET Web form, then the response is dynamically compressed and sent to the requesting client (no temp directory access is ever done).

Enabling HTTP Compression on your Windows 2003 Server

There are quite a few steps to enabling HTTP Compression on your server. If you follow the steps in this article, you shouldn’t have any issues.

First, open up IIS and right-click on the Web Sites node and go to Properties. Click on the Service tab. As shown in FIGURE 1, you’ll see two options: Isolation mode and HTTP compression. If the Run WWW service in IIS 5.0 isolation mode check box is checked, IIS will run almost exactly like IIS 5.0. This means you won’t be able to take advantage of things such as Application Pools, which in my opinion are worth the upgrade to IIS 6.0 by themselves.

FIGURE 1: The Web Sites Properties dialog box

We’ll utilize the options within HTTP compression in this article:

Compress application files. Check this to compress application files. If you do select this, you must also have Compress static files checked, although you won't be warned of this need.
Compress static files. Check this to compress static files. After you do so, the Temporary directory text box is active.
Temporary directory. You can leave this at the default, which is %windir%\IIS Temporary Compressed Files, or set it to a custom folder. This is where temporary compressed static files will be stored.
Maximum temporary directory size. This option enables you to set the maximum size of the temporary directory. After the size is met, items are removed based on duration; the older files are removed and the new files are put in.
Next, go to the Web Service Extensions node. Right-click in the right pane, and click Add a new Web service extension. The New Web Service Extension dialog box appears, as shown in FIGURE 2. You can enter any name for the extension, but what others, including myself, recommend is HTTP Compression.

FIGURE 2: The Web Service Extension dialog box

Click on Add. Choose C:\WINDOWS\system32\inetsrv\gzip.dll (your path may be different, but that is doubtful), and click OK. Check the Set extension status to Allowed check box, and click OK.

IIS 6.0 Metabase Configuration - MetaBase.xml
Open up Windows Explorer and go to C:\Windows\System32\inetsrv. Find MetaBase.xml and make a copy (you can just highlight it and do a Ctrl-C, then a Ctrl-P to make a copy of MetaBase.xml). Now open up MetaBase.xml in a text editor. Find the section. Be careful, there are two sections here: one for deflate and one for gzip. We want gzip so the Location attribute of the element will have the following value:

Location ="/LM/W3SVC/Filters/Compression/gzip"

Look for the HcScriptFileExtensions section. Your default should have: asp, dll, and exe. This is where you add any extensions you want to compress for dynamic files. In my case, I added aspx.

You’ll notice many other attributes in FIGURE 3. These are the ones I find most important:

HcDoDynamicCompression. Specifies whether dynamic content should be compressed. This is important because dynamic content is by definition always changing, and IIS does not cache compressed versions of dynamic output. Thus, if dynamic compression is enabled, each request for dynamic content causes the content to be compressed. Dynamic compression consumes considerable CPU time and memory resources, and should only be used on servers that have slow network connections, but CPU time to spare.
HcDoStaticCompression. Specifies whether static content should be compressed.
HcDoOnDemandCompression: Specifies whether static files, such as .htm and .txt files, are compressed if a compressed version of the file does not exist. If set to True and a file doesn't exist, the user will be sent an uncompressed file while a background thread creates a compressed version for the next request.
HcDynamicCompressionLevel. VAL(1-10) specifies the compression level for the compression scheme, when the scheme is compressing dynamic content. Low compression levels produce slightly larger compressed files, but with lower overall impact on CPU and memory resources. Higher compression levels generally result in smaller compressed files, but with higher CPU and memory usage.
HcFileExtensions. Indicates which file name extensions are supported by the compression scheme. Only static files with the specified file extensions are compressed by IIS. If this setting is empty, no static files are compressed.
HcScriptFileExtensions. Indicates which file name extensions are supported by the compression scheme. The output from dynamic files with the file extensions specified in this property are compressed by IIS.

FIGURE 3: Essential attributes

For this example, just add aspx to the HcScriptFileExtensions section as I did, but don’t try to save. It won’t work because the file is locked by default if IIS is running.

Tip: To change this default behavior, open IIS and right-click on the top node, Internet Information Services, and check Enable Direct Metabase Edit.

Warning: The help documents state that you should use a space delimited list for the file extensions, and I have found this to be incorrect. Instead, use new lines and tabs like the following:

HcCompressionDll="%windir%\system32\inetsrv\gzip.dll"
HcCreateFlags="1"
HcDoDynamicCompression="TRUE"
HcDoOnDemandCompression="TRUE"
HcDoStaticCompression="TRUE"
HcDynamicCompressionLevel="10"
HcFileExtensions="htm
html
txt"
HcOnDemandCompLevel="10"
HcPriority="1"
HcScriptFileExtensions="asp
dll
exe
aspx">

The final step is to do an IIS shutdown and restart by right-clicking in Internet Information Services node and then click All Tasks, Restart IIS.

Upload T-SQL and execute at your hosting provider using an ASP.NET page

2009-01-25T09:16:00.000-08:00

With this approach, you can use the Database Publishing Wizard to generate a T-SQL file from your local database. Then, you can upload the script to your hosting provider, and use the sample ASP.NET page provided to execute the code below.

This approach is useful in the following circumstances:

* Your hosting provider has not deployed the Database Publishing Services, enabling simple publishing of your SQL Server database
* Your hosting provider does not have a T-SQL script execution window or the T-SQL script generated by the Database Publishing Wizard is too large to paste into the T-SQL script execution window

Here is the code, just copy it then paste to your RunSQL.aspx


<%@ Page Language="C#" AutoEventWireup="true"  %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System.IO" %>

<%@ Import Namespace="System.Net" %>

<%

    // **************************************************************************

    // Update these variables here

    // ************************************************************************** 

    // Url of the T-SQL file you want to run



    string fileUrl = @"http://<>/<>.sql";    

    

    // Connection string to the server you want to execute against

    string connectionString = @"<>";

    

    // Timeout of batches (in seconds)

    int timeout = 600;

 %>











    Executing T-SQL





    

        

    

    

    <%

        SqlConnection conn = null;                   

        try

        {

            this.Response.Write(String.Format("Opening url {0}
", fileUrl));

            

            // read file

            WebRequest request = WebRequest.Create(fileUrl);

            using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))

            {

                this.Response.Write("Connecting to SQL Server database...
");

                

                // Create new connection to database

                conn = new SqlConnection(connectionString);               

                

                conn.Open();



                while (!sr.EndOfStream)

                {

                    StringBuilder sb = new StringBuilder();

                    SqlCommand cmd = conn.CreateCommand();

                    

                    while (!sr.EndOfStream)

                    {

                        string s = sr.ReadLine();

                        if (s != null && s.ToUpper().Trim().Equals("GO"))

                        {

                            break;

                        }

                        

                        sb.AppendLine(s);

                    }



                    // Execute T-SQL against the target database

                    cmd.CommandText = sb.ToString();

                    cmd.CommandTimeout = timeout;



                    cmd.ExecuteNonQuery();

                }



            }

            this.Response.Write("T-SQL file executed successfully");

        }

        catch (Exception ex)

        {

            this.Response.Write(String.Format("An error occured: {0}", ex.ToString()));

        }

        finally

        {

            // Close out the connection

            //

            if (conn != null)

            {

                try

                {

                    conn.Close();

                    conn.Dispose();

                }

                catch (Exception e)

                {

                    this.Response.Write(String.Format(@"Could not close the connection.  Error was {0}", e.ToString()));

                }

            }

        }                                 

        %>

Below are the instructions to use this approach:

1. Run the Database Publishing Wizard to generate a T-SQL script file for your local database
2. Using FTP (or another approach if applicable), upload this T-SQL file to your hosting account
3. Download the sample ASP.NET page by clicking on this link: RunSQL.aspx
4. Edit the ASPX page and change the values of the variables fileUrl and connectionString as follows:
1. fileUrl should be the url of the T-SQL file you uploaded. For example if your domain name is www.mydomain.Com, then the url would be http://www.mydomain.com/File.Sql
2. connectionString should be the connection string of your hosted SQL Server database
5. Upload the ASPX page to your hosting account
6. Point your web browser to the ASPX page you uploaded. When this page has completed loading, your database should now be populated in the remote SQL Server database
7. Important: Delete the T-SQL file and ASPX page in your hosting account. This will prevent others from reading your data or tampering with your database.

Stored Procedure using the most CPU

2008-12-28T00:40:00.000-08:00

The following query gives you a high-level view of which currently cached batches or procedures are using the most CPU. The query aggregates the CPU consumed by all statements with the same plan__handle (meaning that they are part of the same batch or procedure). If a given plan_handle has more than one statement, you may have to drill in further to find the specific query that is the largest contributor to the overall CPU usage.
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

Creating Antibot Image With c#

2008-12-13T06:56:00.000-08:00

Antibots images are needed for avoiding Trojans to make fake registration with out human interaction.In the method given below generate random strings and generate the bitmap of that.This method Should be added as web handler(ashx) in the asp.net project.

public void ProcessRequest(HttpContext context)
{
if(context.Session["antibotimage"] == null)
{
context.Session["antibotimage"] = generateRandomString(4).ToUpper();
}

GenerateImage(context.Session["antibotimage"].ToString(), 100, 20, "Arial").Save(context.Response.OutputStream, ImageFormat.Jpeg);
}

private Bitmap GenerateImage(string text, int width, int height, string fontFamily)
{
Random random = new Random();

// Create a new 32-bit bitmap image.
Bitmap bitmap = new Bitmap(width, height, PixelFormat.Format32bppArgb);

// Create a graphics object for drawing.
Graphics g = Graphics.FromImage(bitmap);
g.SmoothingMode = SmoothingMode.AntiAlias;
Rectangle rect = new Rectangle(0, 0, width, height);

// Fill in the background.
HatchBrush hatchBrush = new HatchBrush(HatchStyle.Wave, Color.LightGray, Color.White);
g.FillRectangle(hatchBrush, rect);

// Set up the text font.
SizeF size;
float fontSize = rect.Height + 1;
Font font;
StringFormat format = new StringFormat();
format.Alignment = StringAlignment.Center;
format.LineAlignment = StringAlignment.Center;

// Adjust the font size until the text fits within the image.
do
{
fontSize--;
font = new Font(fontFamily, fontSize, FontStyle.Bold);
size = g.MeasureString(text, font, new SizeF(width, height), format);
} while (size.Width > rect.Width);

// Create a path using the text and warp it randomly.
GraphicsPath path = new GraphicsPath();
path.AddString(text, font.FontFamily, (int)font.Style, font.Size, rect, format);
float v = 4F;
PointF[] points =
{
new PointF(random.Next(rect.Width) / v, random.Next(rect.Height) / v),
new PointF(rect.Width - random.Next(rect.Width) / v, random.Next(rect.Height) / v),
new PointF(random.Next(rect.Width) / v, rect.Height - random.Next(rect.Height) / v),
new PointF(rect.Width - random.Next(rect.Width) / v, rect.Height - random.Next(rect.Height) / v)
};
Matrix matrix = new Matrix();
matrix.Translate(0F, 0F);
path.Warp(points, rect, matrix, WarpMode.Perspective, 0F);

// Draw the text.
hatchBrush = new HatchBrush(HatchStyle.DashedUpwardDiagonal, Color.DarkGray, Color.Black);
g.FillPath(hatchBrush, path);

// Add some random noise.
int m = Math.Max(rect.Width, rect.Height);
for (int i = 0; i < (int)(rect.Width * rect.Height / 30F); i++)
{
int x = random.Next(rect.Width);
int y = random.Next(rect.Height);
int w = random.Next(m / 50);
int h = random.Next(m / 50);
g.FillEllipse(hatchBrush, x, y, w, h);
}

// Clean up.
font.Dispose();
hatchBrush.Dispose();
g.Dispose();

return bitmap;
}

private string generateRandomString(int size)
{
StringBuilder builder = new StringBuilder();
Random random = new Random();
char ch;
for (int i = 0; i < size; i++)
{
ch = Convert.ToChar(Convert.ToInt32(Math.Floor(26 * random.NextDouble() + 65)));
builder.Append(ch);
}
return builder.ToString();
}

Programmed by
Renju.R,
Software Engineer,
ISOFT, Chennai,India

Parsing/Looping through Treeview using Interface

2008-10-11T00:02:00.000-07:00

private static void parseNode(TreeNode tn)
{

IEnumerator ie = tn.ChildNodes.GetEnumerator();

string parentnode = "";

parentnode = tn.Text;

while (ie.MoveNext())
{
TreeNode ctn = (TreeNode) ie.Current;

if (ctn.ChildNodes.Count == 0)
{
sr.Write(ctn.Text);
}
else
{
sr.Write("<" + ctn.Text + ">");
}
if (ctn.ChildNodes.Count > 0)
{
parseNode(ctn);
}
}

sr.Write("");
sr.WriteLine("");

}

Saving the treeview to XML

public static void exportToXml(TreeView tv, string filename)
{
sr = new StreamWriter(filename, false, System.Text.Encoding.UTF8);
sr.WriteLine("");
if (tv.Nodes.Count > 0)
{
IEnumerator ie = tv.Nodes.GetEnumerator();
ie.Reset();
if (ie.MoveNext())
{
TreeNode tn = (TreeNode)ie.Current;
sr.WriteLine("<" + tn.Text + ">");
parseNode(tn);
}
}

sr.Close();
}

SCOPE_IDENTITY

2008-09-14T22:43:00.000-07:00

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

SCOPE_IDENTITY()

Returns numeric value.eg:- RETURN SCOPE_IDENTITY()

Parsing Emails using Regular Expressions

2008-09-10T03:56:00.000-07:00

Using Regular Expressions

Using regular expression we can simply parse valid emails from the given contents.this is mainly used to extract actual email addressess from the to,from,cc and bcc fields.

Implementation(c#)

First import using System.Text.RegularExpressions;

the code is as below

private string ParseEmails(string text)
{
const string emailPattern = @"\w+@\w+\.\w+((\.\w+)*)?";

MatchCollection emails = Regex.Matches(text, emailPattern, RegexOptions.IgnoreCase);
StringBuilder emailString = new StringBuilder();
foreach (Match email in emails)
{
if (emailString.Length ==0)
{
emailString.Append(email.Value);
}
else
{
emailString.Append("," + email.Value);
}
}

return emailString.ToString();
}

Word Break for Gridview/DataGrid

2008-09-07T22:06:00.000-07:00

Sometime we are facing problems with gridview in inserting word breaks to format the grid.ie if we are inserting a coloumn that is having more length so the grid layout is changed.

"wbr" Tag is used for that.That should be placed in angle bracket("<>").I'm representing it as wbr in the code given below as it is an html tag.


public static string WordBreak(String inString, int length)
    {
        StringBuilder stb = new StringBuilder();
        String ret = "";
        int pos;
        if (length == 0)
            length = 10;
        while ((true))
        {
            if (inString.Length <= length)
            {
                ret += inString;
                break;
            }
            pos = inString.IndexOf(" ");
            if (pos != -1)
            {
                if (pos > length - 1)
                {
                    pos = length;
                    ret += inString.Substring(0, pos + 1) + "wbr";
                }
                else
                {
                    ret += inString.Substring(0, pos + 1);
                }
                inString = inString.Substring(pos + 1);
            }
            else
            {
                ret += inString.Substring(0, length) + "wbr";
                inString = inString.Substring(length);
            }
        }
        if (ret.Length > 50)
        {
            string temp = ret.Substring(0, 50);
            stb.Append(temp);
            stb.Append("....");
            ret = stb.ToString();
        }
        return ret;
    }

Regards
www.renjucool.com

Don’t run production ASP.NET Applications with debug=”true” enabled

2008-09-06T00:17:00.001-07:00

One of the things you want to avoid when deploying an ASP.NET application into production is to accidentally (or deliberately) leave the switch on within the application’s web.config file.
Doing so causes a number of non-optimal things to happen including:

1) The compilation of ASP.NET pages takes longer (since some batch optimizations are disabled)

2) Code can execute slower (since some additional debug paths are enabled)

3) Much more memory is used within the application at runtime

4) Scripts and images downloaded from the WebResources.axd handler are not cached

This last point is particularly important, since it means that all client-javascript libraries and static images that are deployed via WebResources.axd will be continually downloaded by clients on each page view request and not cached locally within the browser. This can slow down the user experience quite a bit for things like Atlas, controls like TreeView/Menu/Validators, and any other third-party control or custom code that deploys client resources. Note that the reason why these resources are not cached when debug is set to true is so that developers don’t have to continually flush their browser cache and restart it every-time they make a change to a resource handler (our assumption is that when you have debug=true set you are in active development on your site).

When is set, the WebResource.axd handler will automatically set a long cache policy on resources retrieved via it – so that the resource is only downloaded once to the client and cached there forever (it will also be cached on any intermediate proxy servers). If you have Atlas installed for your application, it will also automatically compress the content from the WebResources.axd handler for you when is set – reducing the size of any client-script javascript library or static resource for you (and not requiring you to write any custom code or configure anything within IIS to get it).

Model View Controller Pattern

2008-09-05T23:51:00.000-07:00

Luckily, the ASP.NET team has been listening to developers like me and has started development of a new Web application framework that sits side-by-side with the Web Forms you know and love but has a distinctly different set of design goals:

* Embrace HTTP and HTML—don't hide it.
* Testability is built-in from the ground up.
* Extensible at almost every point.
* Total control over your output.

This new framework is based around the Model View Controller (MVC) pattern, thus the name, ASP.NET MVC. The MVC pattern was originally invented back in the '70s as part of Smalltalk. As I'll show in this article, it actually fits into the nature of the Web quite well. MVC divides your UI into three distinct objects: the controller, which receives and handles input; the model, which contains your domain logic; and the view, which generates your output. In the context of the Web, the input is an HTTP request, and the request flow looks like Figure
above

This is actually quite different from the process in Web Forms. In the Web Forms model, the input goes into the page (the View), and the view is responsible for both handling the input and generating the output. When it comes to MVC, on the other hand, the responsibilities are separated.

Building Web Apps without Web Forms

Click Here

Insert Values into an Identity Column

2008-09-05T04:44:00.000-07:00

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted.

use tempdb
GO
IF OBJECT_ID('IdentityTable') IS NOT NULL
DROP TABLE IdentityTable
GO
CREATE TABLE IdentityTable (
TheIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
TheValue NVARCHAR(20) NOT NULL )
GO

Simply trying to INSERT a value into the identity column generates an error:

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (1, 'First Row')
GO

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'IdentityTable' when IDENTITY_INSERT is set to OFF.

The trick is to enable IDENTITY_INSERT for the table. That looks like this:

SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')

SET IDENTITY_INSERT IdentityTable OFF

Here are some key points about IDENTITY_INSERT

* It can only be enabled on one table at a time. If you try to enable it on a second table while it is still enabled on a first table SQL Server will generate an error.
* When it is enabled on a table you must specify a value for the identity column.
* The user issuing the statement must own the object, be a system administrator (sysadmin role), be the database owner (dbo) or be a member of the db_ddladmin role in order to run the command.

If you insert a value greater than the current identity seed SQL Server uses the value to reset the identity seed. For example:

SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (10, 'Row Ten')

SET IDENTITY_INSERT IdentityTable OFF

INSERT IdentityTable(TheValue)
VALUES ('Should be 11')

SELECT * FROM IdentityTable
GO

(1 row(s) affected)

(1 row(s) affected)
TheIdentity TheValue
----------- --------------------
10 Row Ten
11 Should be 11

(2 row(s) affected)

Row Locking

2008-09-05T04:11:00.000-07:00

BEGIN TRAN

SELECT *
FROM authors
WITH (HOLDLOCK, ROWLOCK)
WHERE au_id = '274-80-9391'

/* Do all your stuff here while the record is locked */

COMMIT TRAN

The HOLDLOCK hint will instruct SQL Server to hold the lock until you commit the transaction. The ROWLOCK hint will lock only this record and not issue a page or table lock.

The lock will also be released if you close your connection or it times out. I'd be VERY careful doing this since it will stop any SELECT statements that hit this row dead in their tracks. SQL Server has numerous locking hints that you can use. You can see them in Books Online when you search on either HOLDLOCK or ROWLOCK.

Converting Time Units With Math

2008-09-05T03:37:00.000-07:00

* To convert seconds to hours, simply divide by 3600 (since each hour has 60 seconds * 60 minutes). The remainder is the remaining seconds.
* To convert seconds to minutes, simply divide by 60. The remainder is the remaining seconds.

Nothing too shocking there, right? So, let's do some math. If we have a TotalSeconds, we can get:

* Hours = (TotalSeconds / 3600)
* Remaining Minutes = (TotalSeconds % 3600) / 60
* Remaining Seconds = (TotalSeconds % 60)

(The % is the modulo operator in T-SQL, which returns the remainder when dividing two integers.)

Thus, we can write our SQL like this to return 3 integer columns (Hours, Minutes, Seconds) for each event:

select
EventID,
TotalSeconds / 3600 as Hours,
(TotalSeconds % 3600) / 60 as Minutes,
TotalSeconds % 60 as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x

Results

EventID Hours Minutes Seconds
----------- ----------- ----------- -----------
1 6 11 22
2 7 42 29
3 0 21 6
4 3 51 21
5 1 15 53
6 5 24 0
7 16 50 2

Calculating Duration Totals

select
sum(TotalSeconds / 3600) as Hours,
sum((TotalSeconds % 3600) / 60) as Minutes,
sum(TotalSeconds % 60) as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x

Hours Minutes Seconds
----------- ----------- -----------
38 214 133

select
sum(TotalSeconds) / 86400 as Days,
(sum(TotalSeconds) % 86400) / 3600 as Hours,
(sum(TotalSeconds) % 3600) / 60 as Minutes,
sum(TotalSeconds) % 60 as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x

Days Hours Minutes Seconds
----------- ----------- ----------- -----------
1 17 36 13

Ajax Auto complete Extender webservice

2008-09-05T01:34:00.000-07:00

Sample Code in the Webservice

public String[] FillList(string prefixText, int count)
{
SqlCommand cmd = new SqlCommand("select distinct BugTitle from bugmaster where bugtitle like '" + prefixText.Replace("'", "''") + "%'", new clsGroConnection().getOpenCon());
SqlDataAdapter da = new SqlDataAdapter(cmd);
int nn_itmCount;
DataTable dt=new DataTable("renju");
da.Fill(dt);
ArrayList arr = new ArrayList();
for (nn_itmCount = 0; nn_itmCount < ((dt.Rows.Count >= 100) ? 10 : dt.Rows.Count); nn_itmCount++)
{
arr.Add(dt.Rows[nn_itmCount]["BugTitle"]);
}
return (String[])arr.ToArray(typeof(string));
}

STUFF vs REPLACE

2008-09-04T23:17:00.000-07:00

(see PIVOT Table Post for its usage)

STUFF - Deletes a specified length of characters and inserts another set of characters at a specified starting point.
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO
Here is the result set:
---------
aijklmnef
REPLACE - Replaces all occurrences of the second given string expression in the first string expression with a third expression.
SELECT REPLACE('abcdefghicde','cde','xxx')
GO
Here is the result set:
------------
abxxxfghixxx

Delete the rows which are duplicate (don’t delete both duplicate records)

2008-09-04T23:10:00.000-07:00

SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0

Table's row count-you can use alternative way instead of SELECT COUNT(*)

2008-09-04T22:54:00.000-07:00

Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*):

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

So, you can improve the speed of such queries in several times.

Bubble Sort Algorithm in C#

2008-09-04T22:34:00.001-07:00

static int[] bubbleSort(int[] numbers, int array_size)
{
int i, j, temp;

for (i = (array_size - 1); i >= 0; i--)
{
for (j = 1; j <= i; j++)
{
if (numbers[j - 1] > numbers[j])
{
temp = numbers[j - 1];
numbers[j - 1] = numbers[j];
numbers[j] = temp;
}
}
}
return numbers;
}

Returning multiple values from a Function

2008-09-04T22:32:00.000-07:00

public static int add(int a, int b, ref int c)
{
c = a + 2;
return a + b;
}

We can use out and ref,can be called as shown below

int e = add(2, 3, ref d);

How to format a Date or DateTime in SQL Server

2008-09-04T22:26:00.000-07:00

Question: How do you create a DateTime in a specific format in SQL Server?

Answer: You don't. You can't. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type. This means that it is no longer a DateTime. It is a VARCHAR.

This might not be the answer you are looking for, but please ... don't stop reading!

. . .

Why do people have trouble with the concept of raw data versus the presentation of that data? Am I missing something? If you use CONVERT() in an attempt format your DateTime data, surely people must understand that CONVERT() also converts that data from a DateTime to another data type? It's right there in the expression:

SELECT CONVERT(varchar(10), someDate)

That clearly is CONVERTing someDate to a varchar(10), isn't it? What gets returned when you convert something to a VARCHAR, a date or a string? The answer is a string of meaningless characters that no longer have any value as an actual date.

"You clearly are a hack, Jeff," you tell me, "since it is very easy for a SQL-Master such as myself to format dates without converting them to strings. For example, check this out this sweet 'mm/dd/yyyy' format:

select right('0' + rtrim(month(@d)),2) + '/' + right('0' + rtrim(day(@d)),2) + '/' + rtrim(year(@d)).

I can write stuff like this all week! It's easy. You've got much to learn!"

Well, I have bad news for you. That still is implicitly converting everything to a string -- the rtrim() function is handling that part. This is even worse than doing it explicitly with a CONVERT() function, and the end result is not easy to read or work with or write, it is not efficient, and it is still not returning a DateTime value.

. . .

Always remember: If a value is not a DateTime datatype, it is not a date. No matter what it looks like, or how neatly formatted you made that string, or how careful you were to use an ISO compliant format, it is not a Date. Period.

It is crucial to understand this, and to thus to understand the implications of trying to "format" data at the database layer. It cannot be done! All you can do is convert things to generic "string" datatypes. That's it.

. . .

No matter what they may look like, strings don't sort like dates. They don't compare like dates. You can't get the month from a string consistently, or calculate the amount of minutes between two strings, or add x days to a string. You can't ensure that different databases or stored procedures or functions or applications will always interpret your chosen date formatted string the same. Client applications -- who should be doing the formatting -- cannot apply date formatting to a string, they need an actual date stored in the correct data type. Thus, they would need to convert this string back to a DateTime type and only then can they format it for display purposes or use standard date calculations on the value. Does it really make sense to start with a date value, convert it to a string in SQL, and then have your client convert it back to a date value?

Simply return raw data from your database using the proper data types, and then simply use the tools designed to handle raw data in the correct types at your clients to format and present that data.

* In crystal reports or other reporting tools, you can just drop your nice, clean, raw unformatted datetime value on your report, right-click it, and easily format it any way you want. You can use regional settings, specify mm/dd/yyyy format strings, and all kinds of options. It's simple and easy, but you must return datetime values back from SQL, not VARCHARS!
* In Excel, again, you can simply right-click and choose any format you want, or create your own.
* In .NET applications, you can usually format dates in data bound controls using the GUI interface, and you can also format things using the ToString() method of a true datetime value and specify all kinds of simple yet flexible formatting strings.
* In ASP.NET web pages, just about all data bound web controls let you specify a FormatString property for your bound columns, giving you clear, simple control over exactly how your dates look.
* In VB and VBA, there is a Format() function that again works with named formats or custom format strings.
* In MS Access, the report and form designer lets you format any text box containing a datetime value any way you want, again with simple format names or format strings, and you have all of the VBA functions available to format dates in your code. You can even specify the specific date format for columns in a query in the query designer -- but, again, you must be working with data in the correct datetime data type.

Isn't it much easier to simply right-click on something and then enter a simple "mmm dd, yyyy" format string instead of building and parsing this manually using CONVERT and SUBSTRING parsing in T-SQL? Isn't it more flexible to do all formatting at your presentation layer so that you can just return data from your database and not worry about how it looks? Then 5 different clients can query the same stored procedure and each output those dates any way they want -- without changing any database code.

Sql Server TimeSpan

2008-09-04T22:24:00.000-07:00

With those functions in place, we can add two more that will give us further flexibility when working with dates and times: The concept of a "TimeSpan", very similar to what is available in the .NET framework.

create function TimeSpan(@Days int, @Hours int, @Minutes int, @Seconds int)
-- returns a datetime the specified # of days/hours/minutes/seconds from the "base" date of 1/1/1900 (a "TimeSpan")
returns datetime
as
begin
return dbo.Time(@Hours,@Minutes,@Seconds) + @Days
end

create function TimeSpanUnits(@Unit char(1), @TimeSpan datetime)
-- returns the # of units specified in the TimeSpan.
-- The Unit parameter can be: "d" = days, "h" = hours, "m" = minutes, "s" = seconds
returns int
as
begin
return case @Unit
when 'd' then datediff(day, 0, @TimeSpan)
when 'h' then datediff(hour, 0, @TimeSpan)
when 'm' then datediff(minute, 0, @TimeSpan)
when 's' then datediff(second, 0, @TimeSpan)
else Null end
end

Here, a TimeSpan is just a datetime offset from the "base" date of 1/1/1900. Creating one is the same as creating a Time using the Time() function, but we have added a parameter for Days to give more flexibility.

The TimeSpanUnits() function works similar to standard T-SQL DatePart() function, but it returns the total # of units in the given time span. So, if you create a time span of 1 day and 2 hours, then TimeSpanUnits("d") will return 1 and TimeSpanUnits("h") will return 26. Negative values can be returned as well. You also may wish to implement the TimeSpanUnits() function as multiple functions, one per unit (e.g., TimeSpanHours(), TimeSpanDays(), etc) depending on your preference.

Of course, a simple way to create a TimeSpan is to simply subtract two standard T-SQL DateTimes. Also please note that we can add and subtract Dates, Times, and TimeSpans all together using standard + and - operators and everything will work as expected. We can also add integers to our Dates and Times which will add entire days to the values.

Here's a TimeSpan usage example:

declare @Deadline datetime -- remember, we still use standard datetimes for everything, include TimeSpans
set @Deadline = dbo.TimeSpan(2,0,0,0) -- the deadline is two days

declare @CreateDate datetime
declare @ResponseDate datetime

set @CreateDate = dbo.DateTime(2006,1,3,8,30,0) -- Jan 3, 2006, 8:30 AM
set @ResponseDate = getdate() -- today

-- See if the response date is past the deadline:
select case when @ResponseDate > @CreateDate + @Deadline then 'overdue.' else 'on time.' end as Result

-- Find out how many total hours it took to respond:
declare @TimeToRepond datetime
set @TimeToRespond = @ResponseDate - @CreateDate

select dbo.TimeSpanUnits('h', @TimeToRespond) as ResponseTotalHours

-- Return the response time as # of days, # of hours, # of minutes:
select dbo.TimeSpanUnits('d',@TimeToRespond) as Days, DatePart(hour, @TimeToRespond) as Hours, DatePart(minute, @TimeToRespond) as Minutes

-- Return two days and two hours from now:
select getdate() + dbo.TimeSpan(2,2,0,0)

SQL Server Date, Time and DateTime Functions

2008-09-04T22:22:00.000-07:00

create function DateOnly(@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
begin
return dateadd(dd,0, datediff(dd,0,@DateTime))
end
go

create function Date(@Year int, @Month int, @Day int)
-- returns a datetime value for the specified year, month and day
-- Thank you to Michael Valentine Jones for this formula (see comments).
returns datetime
as
begin
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
end
go

create function Time(@Hour int, @Minute int, @Second int)
-- Returns a datetime value for the specified time at the "base" date (1/1/1900)
-- Many thanks to MVJ for providing this formula (see comments).
returns datetime
as
begin
return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
end
go

create function TimeOnly(@DateTime DateTime)
-- returns only the time portion of a DateTime, at the "base" date (1/1/1900)
returns datetime
as
begin
return @DateTime - dbo.DateOnly(@DateTime)
end
go

create function DateTime(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
begin
return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
end
go

Remember that you must prefix UDFs with the owner (usually dbo) when calling them.

Usage Examples:

* where TransactionDate >= dbo.Date(2005,1,2) -- no formatting or implicit string conversions needed for date literals

* select dbo.Date(year(getdate()), 1,1) -- returns the first day of the year for the current year.

* select dbo.DateOnly(getdate()) -- returns only the date portion of the current day.

* if dbo.TimeOnly(SomeDate) = dbo.Time(5,30,0) -- check to see if the time for a given date is at 5:30 AM

* select dbo.Date(year(getdate()), month(getdate()),1) -- returns the first day of the current month.

* select dbo.Date(year(getdate()), month(getdate())+1,0) -- returns the last day of the current month.

* where SomeDate >= dbo.DateOnly(getdate()) and SomeDate < dbo.DateOnly(getDate())+1 -- a simple way to get all transactions that occurred on the current date

* select dbo.DateOnly(getdate()) + 1 + dbo.Time(14,30,0) -- returns tomorrow at 2:30 PM.

Joins,Groupby,Datetime Stored Procedures(REFERENCE)

2008-09-04T21:05:00.000-07:00

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Renju.R
-- Create date: August 1,2008
-- Description:
-- =============================================
ALTER procedure [dbo].[GetEmpPerformance]
@fromDate datetime,
@toDate datetime
--@projectId int,
--@moduleId int
as
begin
select ws.currentStatus,am.activityName,
w.workID,w.work,w.startDate,w.finishDate,w.formId,w.activityId,
ts.activityId,sum(ts.hoursInActivity),sum(ts.minInActivity),
(select UserName from dbo.UserRegMaster where UserID=w.toUser) as ToUser,
--dbo.GetWorkHours(@fromDate, @toDate) TotalWorkingHours,
sum(DATEDIFF(hour,w.startDate,w.finishDate)) as WorkHours,
sum(DATEDIFF(minute,w.startDate,w.finishDate) - (DATEDIFF(hour,w.startDate,w.finishDate) * 60)) as WorkMinutes
from work w
left outer join dbo.workStatus ws on ws.workID=w.workID
and statusDate=(select max(statusDate) from workStatus where workId=w.workID)
left outer join dbo.TimeSheet ts
on ts.activityId=w.activityId and
ts.workID=w.workID
left outer join dbo.ActivityMaster am on ts.activityId=am.activityId and ts.workID=w.workID
where --projID=@projectId and modID=@projectId and
startDate between
convert(datetime,@fromDate,101) and convert(datetime,@toDate,101) group by
ws.currentStatus,am.activityName,
w.workID,w.work,w.startDate,w.finishDate,w.formId,w.activityId,
ts.activityId,ToUser
end

Looping and inserting values to Datatable

2008-09-04T05:27:00.000-07:00

public DataTable ModifyData(DataSet dsMod)
{

int rCount = 0;
string temp2 = "";
DataTable dtNew = new DataTable();
DataTable dt = dsMod.Tables[0];
DataRow dr;
for (int k = 0; k <= dt.Columns.Count - 1; k++)
{
string cname = dt.Columns[k].ColumnName;
dtNew.Columns.Add(cname);
}

foreach (DataRow dataRow in dt.Rows)
{
int cnt = 0;
dr = dtNew.NewRow();
dtNew.Rows.Add(dr);
foreach (DataColumn dataColumn in dt.Columns)
{
string t1 = dataRow[dataColumn].ToString();
if (Convert.ToString(dataColumn) != "Name")
{
if (t1 == "")
{
temp2 = "";
}
else
{
temp2 = clsCommon.MinutesToHours(Convert.ToInt32(t1));
}
}
else
{
temp2 = t1;
}
dtNew.Rows[rCount][cnt] = temp2.ToString();
cnt++;

}
rCount++;
}
return dtNew;
}

Exporting DataTable to Excel

2008-09-04T05:24:00.002-07:00

private void ExportToExcel(DataTable dtIn)
{

DataTable dt = dtIn;
if (dt != null)
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();
}

}

Dynamic DataGrid Templates

2008-09-04T05:24:00.001-07:00

public void DynamicGrid()
{
DataGrid dg = new DataGrid();
DataSet ds = objws.getempinactivity(1, 8, DateTime.Now.AddDays(-50), DateTime.Now);
DataTable dt = ds.Tables[0];

for (int i = 0; i <= dt.Columns.Count - 1; i++)
{
TemplateColumn templateColumn = new TemplateColumn();
string columnName = dt.Columns[i].ColumnName;
templateColumn.HeaderTemplate = new DataGridTemplate(ListItemType.Header, columnName);
for (int j = 0; j <= dt.Rows.Count - 1; j++)
{
string value = dt.Rows[j][i].ToString();
templateColumn.ItemTemplate = new DataGridTemplate(ListItemType.Item, value);

}
DataGrid1.Columns.Add(templateColumn);
}
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
}

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

///
/// RENJU.R
/// Summary description for DataGridTemplate
///
public class DataGridTemplate : ITemplate
{
ListItemType templateType;
string columnName;
public DataGridTemplate(ListItemType type, string colname)
{
//
// TODO: Add constructor logic here
//
templateType = type;
columnName = colname;

}
public void InstantiateIn(System.Web.UI.Control container)
{
Literal lc = new Literal();
switch (templateType)
{
case ListItemType.Header:
lc.Text = "" + columnName + "";
container.Controls.Add(lc);
break;
case ListItemType.Item:
lc.Text = columnName;
container.Controls.Add(lc);
break;
case ListItemType.EditItem:
TextBox tb = new TextBox();
tb.Text = "";
container.Controls.Add(tb);
break;
case ListItemType.Footer:
lc.Text = "" + columnName + "";
container.Controls.Add(lc);
break;
}
}
//public void InstantiateIn(Control container)
//{
// LiteralControl l = new LiteralControl();
// l.DataBinding += new EventHandler(this.OnDataBinding);
// container.Controls.Add(l);
//}

//public void OnDataBinding(object sender, EventArgs e)
//{
// LiteralControl l = (LiteralControl)sender;
// DataGridItem container = (DataGridItem)l.NamingContainer;
// l.Text = ((DataRowView)container.DataItem)[colname].ToString();
//}
}
Poste

Sql Server Pivot Table

2008-09-04T05:23:00.001-07:00

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Renju.R
-- Create date: August 5,2008
-- Description: Cross Tabbing Tables
-- =============================================
ALTER procedure [dbo].[crosstab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
as
begin
declare @sql varchar(8000), @delim varchar(1)
set nocount on
set ansi_warnings off
exec ('select ' + @pivot + ' as pivot into ##pivot from ' + @table + ' where 1=2')
exec ('insert into ##pivot select distinct ' + @pivot + ' from ' + @table + ' where '
+ @pivot + ' is not null')
select @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' end)' )
select @delim=case sign( charindex('char', data_type)+charindex('date', data_type) )
when 0 then '' else '''' end
from tempdb.information_schema.columns
where table_name='##pivot' and column_name='pivot'
select @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' case ' + @pivot + ' when '
+ @delim + convert(varchar(100), pivot) + @delim + ' then ' ) + ', ' from ##pivot
drop table ##pivot
select @sql=left(@sql, len(@sql)-1)
select @select=stuff(@select, charindex(' from ', @select)+1, 0, ', ' + @sql + ' ')
exec (@select)
end
set ansi_warnings on

For Executing the procedure some Examples are as below

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[EvaluationFormWise]
as
begin
execute crosstab
'select pm.ProjectName,mm.ModuleName,fm.formName,fm.formType,
sm.SeverityName from bugmaster bm
inner join formmaster fm on fm.formId=bm.formid
inner join dbo.SeverityMaster sm on sm.SeverityID=bm.SeverityID
inner join dbo.ProjectMaster pm on pm.projectId=bm.projectId
inner join dbo.ModuleMaster mm on mm.moduleId=bm.moduleid
left outer join UserRegMaster um on um.userid=bm.programmerId
left outer join UserRegMaster urm on urm.userid=bm.submitterid
group by pm.ProjectName,mm.ModuleName,fm.formName,fm.formType,sm.SeverityName',
'count(bm.SeverityID)',
'SeverityName',
'SeverityMaster'
end

Asp.Net Message Box

2008-09-04T05:22:00.000-07:00

Sql Server getting date difference in hours,min,sec

2008-09-04T05:21:00.000-07:00

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Renju.R
-- Create date: July 31
-- Description: getting date difference in hours,min,sec
-- =============================================
ALTER FUNCTION [dbo].[GetDateDiff]
(
@startDate datetime,
@finishDate datetime
)
RETURNS TABLE
AS
RETURN
(
SELECT DATEDIFF(hour,@startDate,@finishDate) as TotalHours,
DATEDIFF(minute,@startDate,@finishDate) - (DATEDIFF(hour,@startDate,@finishDate) * 60) as TotalMinutes,
DATEDIFF(second,@startDate,@finishDate) - (DATEDIFF(minute,@startDate,@finishDate) * 60) as TotalSeconds
)

ASP.NET Site Search

2008-09-04T05:20:00.000-07:00

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;
//Programmed by Renju.R
//Refer Microsoft Indexing Service
public partial class _Default : System.Web.UI.Page
{
public static DataView tempds = new DataView();
public static int resultCount = 0;
public DataRow drIndexed;
public DataTable dtPub = new DataTable();
public DataSet dsPub = new DataSet();
public DataRow dr2;
public DataTable dt2 = new DataTable();
public DataSet ds2 = new DataSet();
public DataSet ds = new DataSet();
public DataRow dr;
public DataTable dt = new DataTable();
public static StringBuilder stb = new StringBuilder();
// public static OleDbConnection oldbCon = new OleDbConnection("Provider=MSIDXS.1;Integrated Security .='';Data Source=Reader");
public static OleDbConnection oldbCon = new OleDbConnection("Provider=MSIDXS.1;");
public int count;
protected void Page_Load(object sender, EventArgs e)
{
}
public void FillResult()
{
//ds.Merge(dtTemp, false, MissingSchemaAction.Add);
//ds.Merge(ds2.Tables[0]);
//ds.Tables[0].Merge(ds2.Tables[0]);
//tempds = ds.Tables[0].Copy().DefaultView;
MergeSearch();
Searchfill();
}
protected void Button1_Click(object sender, EventArgs e)
{
string searchText = TextBox1.Text.ToString();
//OleDbCommand cmd = new OleDbCommand("select doctitle, filename, Path ,Attrib, rank, characterization from scope() where FREETEXT('" + searchText + "') and filename <> 'Default.aspx' order by rank desc", oldbCon);
OleDbCommand cmd = new OleDbCommand("select doctitle, filename, Path ,Attrib, rank, characterization from saravanan.Reader..scope() where FREETEXT('" + searchText + "') and filename <> 'Default.aspx' order by rank desc", oldbCon);
cmd.CommandType = CommandType.Text;
if (oldbCon.State == ConnectionState.Closed)
{
oldbCon.Open();
}
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
resultCount = ds.Tables[0].Rows.Count;
ds2.Tables.Add(dt2);
dt2.Columns.Add("new");
/////
//ds.Tables.Add(dt);
//dt.Columns.Add("search");
////
dsPub.Tables.Add(dtPub);
dtPub.Columns.Add("text");
foreach (DataRow drTemp in ds.Tables[0].Rows)
{
ReadDocuments((string)drTemp["path"]);
}
FillResult();
}
void Searchfill()
{
DataList1.DataSource = ds.Tables[0].DefaultView;
DataList1.DataBind();
}
public DataSet MergeSearch()
{
ds.Tables[0].Columns.Add("search");
int cnt = 0;
// ds.AcceptChanges();
//int mCount = ds2.Tables[0].Rows.Count;
foreach (DataRow drLoop in ds2.Tables[0].Rows)
{
DataRow drTest= ds.Tables[0].Rows[cnt];
drTest["search"] = drLoop["new"];
//dt.Rows.Add(drTest);
// ds.Tables[0].Rows.Add(drTest);
cnt++;
}
return ds;
}
public DataSet MergedValues(string str)
{
DataRow dr5 = ds2.Tables[0].NewRow();
dr5["new"] = (string)str;
dt2.Rows.Add(dr5);
return ds2;
}
public DataSet SearchResult(string text)
{
DataRow drResult = dsPub.Tables[0].NewRow();
drResult["text"] = (string)text;
CreateExcerpt((string)text, TextBox1.Text.ToString(),ds);
dtPub.Rows.Add(drResult);
return dsPub;
}
private string parseHtml(string html)
{
string temp = Regex.Replace(html, "<[^>]*>", "");
return temp.Replace(" ", " ");
}
public string CreateExcerpt(string source, string keyword,DataSet ds)
{
count = 0;
string excerpt = string.Empty;
int charsBeforeAndAfter = 100;
int index = source.IndexOf(keyword, StringComparison.CurrentCultureIgnoreCase);
if (index >= 0)
{
int excerptStartIndex = 0;
int excerptEndIndex = source.Length - 1;
if (index > (charsBeforeAndAfter - 1))
excerptStartIndex = index - charsBeforeAndAfter;
if ((index + keyword.Length + charsBeforeAndAfter) < (source.Length - 1))
excerptEndIndex = index + keyword.Length + charsBeforeAndAfter;
excerpt = source.Substring(excerptStartIndex, excerptEndIndex - excerptStartIndex + 1);
index = excerpt.IndexOf(keyword, StringComparison.CurrentCultureIgnoreCase);
excerpt = excerpt.Insert(index + keyword.Length, "");
excerpt = excerpt.Insert(index, "");
excerpt = string.Format("...{0}...", excerpt);
foreach (DataRow dr in ds.Tables[0].Rows)
{
if(count ==0)
MergedValues(excerpt);
count++;
}
}

//stb.Append(excerpt.ToString());
//stb.Append("
");
//stb.Append("
");
return excerpt;
}
public void ReadDocuments(string docPath)
{
FileStream fs = File.OpenRead(docPath);
string dumptext;
using(StreamReader sr = new StreamReader(docPath, System.Text.Encoding.Default))
{
dumptext = parseHtml(sr.ReadToEnd());
}
SearchResult((string)dumptext);

}
}