November 21, 2010

Get all the dates between two dates in SQL Server

CREATE FUNCTION [dbo].[GetDatesInBetween](@FromDate DATETIME, @ToDate DATETIME, @IncludeWeekends BIT )

RETURNS @DateTable TABLE(DATE DATETIME)
AS
BEGIN
INSERT INTO @DateTable (DATE) VALUES (@FromDate)
WHILE @FromDate < @ToDate
BEGIN
SELECT @FromDate = DATEADD(D, 1, @FromDate)
IF @IncludeWeekends = 1
INSERT INTO @DateTable (DATE) VALUES (@FromDate)
ELSE IF DATENAME(dw, @FromDate) <> 'Thursday' AND DATENAME(dw, @FromDate) <> 'Friday'
INSERT INTO @DateTable (DATE) VALUES (@FromDate)
END
RETURN
END

Strip/Remove HTML from text in SQL Server

I used the below function, because HTML contents are not fully supported in crystal report.

Ref:
http://blog.sqlauthority.com/2007/06/16/sql-server-udf-user-defined-function-to-strip-html-parse-html-no-regular-expression/

http://lazycoders.blogspot.com/2007/06/stripping-html-from-text-in-sql-server.html


CREATE FUNCTION [dbo].[StripHTML]

(
@HTMLText varchar(MAX)
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @Start int
DECLARE @End int
DECLARE @Length int
-- Replace the HTML entity & with the '&' character (this needs to be done first, as
-- '&' might be double encoded as '&')
SET @Start = CHARINDEX('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END
-- Replace the HTML entity < with the '<' character
SET @Start = CHARINDEX('<', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '<')
SET @Start = CHARINDEX('<', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END
-- Replace the HTML entity > with the '>' character
SET @Start = CHARINDEX('>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '>')
SET @Start = CHARINDEX('>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END


-- Replace the HTML entity & with the '&' character
SET @Start = CHARINDEX('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END


-- Replace the HTML entity   with the ' ' character
SET @Start = CHARINDEX(' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ' ')
SET @Start = CHARINDEX(' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END

-- Replace any
tags with a newline

SET @Start = CHARINDEX('
', @HTMLText)

SET @End = @Start + 3
SET @Length = (@End - @Start) + 1


WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, CHAR(13) + CHAR(10))
SET @Start = CHARINDEX('
', @HTMLText)



SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END
-- Replace any
tags with a newline

SET @Start = CHARINDEX('
', @HTMLText)

SET @End = @Start + 4
SET @Length = (@End - @Start) + 1


WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX('
', @HTMLText)

SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END
-- Replace any
tags with a newline

SET @Start = CHARINDEX('
', @HTMLText)

SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX('
', @HTMLText)

SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END
-- Remove anything between tags
SET @Start = CHARINDEX('<', @HTMLText)
SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX('<', @HTMLText)
SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
SET @Length = (@End - @Start) + 1
END
WHILE CHARINDEX(' ', @HTMLText) > 0
SET @HTMLText = REPLACE(@HTMLText, ' ', ' ')


RETURN LTRIM(RTRIM(@HTMLText))


END

November 13, 2010

Chimamanda Ngozi Adichie : Novelist



When we reject the single story, when we realize that there is never a single story about any place, we regain a kind of paradise

A poem by my Father: A Bridge at Sun

From Dream to Reality


The horizon blushed crimson
The backwaters sang lullaby
To the boats that rocked with the ripples
In the lake that bosomed the life around.

The island lay in the lap of the lake
Waiting for the night to fall over
The palm groves that glowed green
Grew darker against a painted sky.

A wistful dream of the islanders
For a bridge that would fill the chasm
To hug the city at random
Did come true at long last.

A bridge spanned across the lake
Like an arch that bade welcome
Fro all the sons and sisters of the island,
To the comforts and warmth of a home.

Life shall never be the same
Speed shall rule the roads
The waters that lulled the pace
Shall flow quiet beneath the bridge.

The darkness spreads across the waters
The city blinks open its umpteen eyes
The port, the boats and the ships
All glittered in the waves like a sea of stars..

An old man stood on the bank
A thousand sparks in his mind
Of the quiet journeys in the ferry
Of the beautiful sunsets he loved.

The bridge may bring prosperity,
Convenience and comforts to all
But, some of us shall miss the boat,
The sunsets, the cool breeze, the twilight,
And the waters rocking us to a dreamy sleep.

November 11, 2010

Martin Luther King "I have a dream"







We refuse to believe that the bank of justice is bankrupt.

Nineteen sixty-three is not an end, but a beginning.

Let us not seek to satisfy our thirst for freedom by drinking from the cup of bitterness and hatred.
We must forever conduct our struggle on the high plane of dignity and discipline.
We must not allow our creative protest to degenerate into physical violence.
Again and again, we must rise to the majestic heights of meeting physical force with soul force.

No, no, we are not satisfied, and we will not be satisfied until "justice rolls down like waters, and righteousness like a mighty stream."¹


And so even though we face the difficulties of today and tomorrow, I still have a dream. It is a dream deeply rooted in the American dream.

I have a dream that one day this nation will rise up and live out the true meaning of its creed: "We hold these truths to be self-evident, that all men are created equal."

I have a dream that one day on the red hills of Georgia, the sons of former slaves and the sons of former slave owners will be able to sit down together at the table of brotherhood.

I have a dream that one day even the state of Mississippi, a state sweltering with the heat of injustice, sweltering with the heat of oppression, will be transformed into an oasis of freedom and justice.

I have a dream that my four little children will one day live in a nation where they will not be judged by the color of their skin but by the content of their character.

I have a dream today!

I have a dream that one day, down in Alabama, with its vicious racists, with its governor having his lips dripping with the words of "interposition" and "nullification" -- one day right there in Alabama little black boys and black girls will be able to join hands with little white boys and white girls as sisters and brothers.

I have a dream today!

I have a dream that one day every valley shall be exalted, and every hill and mountain shall be made low, the rough places will be made plain, and the crooked places will be made straight; "and the glory of the Lord shall be revealed and all flesh shall see it together."2

This is our hope, and this is the faith that I go back to the South with.

With this faith, we will be able to hew out of the mountain of despair a stone of hope. With this faith, we will be able to transform the jangling discords of our nation into a beautiful symphony of brotherhood. With this faith, we will be able to work together, to pray together, to struggle together, to go to jail together, to stand up for freedom together, knowing that we will be free one day.

And this will be the day -- this will be the day when all of God's children will be able to sing with new meaning:

My country 'tis of thee, sweet land of liberty, of thee I sing.
Land where my fathers died, land of the Pilgrim's pride,
From every mountainside, let freedom ring!
And if America is to be a great nation, this must become true.
And so let freedom ring from the prodigious hilltops of New Hampshire.
Let freedom ring from the mighty mountains of New York.
Let freedom ring from the heightening Alleghenies of Pennsylvania.
Let freedom ring from the snow-capped Rockies of Colorado.
Let freedom ring from the curvaceous slopes of California.
But not only that:
Let freedom ring from Stone Mountain of Georgia.
Let freedom ring from Lookout Mountain of Tennessee.
Let freedom ring from every hill and molehill of Mississippi.
From every mountainside, let freedom ring.

And when this happens, when we allow freedom ring, when we let it ring from every village and every hamlet, from every state and every city, we will be able to speed up that day when all of God's children, black men and white men, Jews and Gentiles, Protestants and Catholics, will be able to join hands and sing in the words of the old Negro spiritual:

Free at last! Free at last!

Thank God Almighty, we are free at last!

November 10, 2010

C# code geneartor stored procedure

Ref: http://www.codeproject.com/KB/database/CSCodeBuilder.aspx

CREATE PROCEDURE tools_CS_SPROC_Builder



(


@objName nvarchar(100)


)


AS




SET NOCOUNT ON






DECLARE @parameterCount int


DECLARE @errMsg varchar(100)


DECLARE @parameterAt varchar(1)


DECLARE @connName varchar(100)


//Change the following variable to the name of your connection instance


SET @connName='conn.Connection'


SET @parameterAt=''






SELECT


dbo.sysobjects.name AS ObjName,


dbo.sysobjects.xtype AS ObjType,


dbo.syscolumns.name AS ColName,


dbo.syscolumns.colorder AS ColOrder,


dbo.syscolumns.length AS ColLen,


dbo.syscolumns.colstat AS ColKey,


dbo.systypes.xtype


INTO #t_obj


FROM


dbo.syscolumns INNER JOIN


dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN


dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype


WHERE


(dbo.sysobjects.name = @objName)


AND


(dbo.systypes.status <> 1)


ORDER BY


dbo.sysobjects.name,


dbo.syscolumns.colorder






SET @parameterCount=(SELECT count(*) FROM #t_obj)






IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objName






IF(@errMsg is null)


BEGIN


PRINT 'try'


PRINT ' {'


PRINT ' SqlParameter[] paramsToStore =


new SqlParameter[' + cast(@parameterCount as varchar) + '];'


PRINT ''






DECLARE @source_name nvarchar,@source_type varchar,


@col_name nvarchar(100),@col_order int,@col_type varchar(20),


@col_len int,@col_key int,@col_xtype int,@col_redef varchar(20)






DECLARE cur CURSOR FOR


SELECT * FROM #t_obj


OPEN cur


-- Perform the first fetch.


FETCH NEXT FROM cur


INTO @source_name,@source_type,@col_name,@col_order,


@col_len,@col_key,@col_xtype






if(@source_type=N'U') SET @parameterAt='@'


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.


WHILE @@FETCH_STATUS = 0


BEGIN


SET @col_redef=(SELECT


CASE @col_xtype


WHEN 34 THEN 'Image'


WHEN 35 THEN 'Text'


WHEN 48 THEN 'TinyInt'


WHEN 52 THEN 'SmallInt'


WHEN 56 THEN 'Int'


WHEN 58 THEN 'SmallDateTime'


WHEN 59 THEN 'Real'


WHEN 60 THEN 'Money'


WHEN 61 THEN 'DateTime'


WHEN 62 THEN 'Float'


WHEN 99 THEN 'NText'


WHEN 104 THEN 'Bit'


WHEN 106 THEN 'Decimal'


WHEN 122 THEN 'SmallMoney'


WHEN 127 THEN 'BigInt'


WHEN 165 THEN 'VarBinary'


WHEN 167 THEN 'VarChar'


WHEN 173 THEN 'Binary'


WHEN 175 THEN 'Char'


WHEN 231 THEN 'NVarChar'


WHEN 239 THEN 'NChar'


ELSE '!MISSING'






END AS C)


--Write out the parameter


PRINT ' paramsToStore[' + cast(@col_order-1 as varchar)


+ '] = new SqlParameter("' + @parameterAt + @col_name


+ '", SqlDbType.' + @col_redef


+ ');'






--If the type is a string then output the size declaration


IF(@col_xtype=231)OR(@col_xtype=167)OR(@col_xtype=175)


OR(@col_xtype=99)OR(@col_xtype=35)


BEGIN


PRINT ' paramsToStore[' + cast(@col_order-1 as varchar)


+ '].Size=' + cast(@col_len as varchar) + ';'


END


PRINT ' paramsToStore['+ cast(@col_order-1 as varchar)


+ '].Value = ;'


-- This is executed as long as the previous fetch succeeds.


FETCH NEXT FROM cur


INTO @source_name,@source_type,@col_name,@col_order,


@col_len,@col_key,@col_xtype


END


PRINT ''


PRINT ' SqlHelper.ExecuteNonQuery(' + @connName +


', CommandType.StoredProcedure,"' + @objName + '", paramsToStore);'


PRINT ' }'


PRINT 'catch(Exception excp)'


PRINT ' {'


PRINT ' }'


PRINT 'finally'


PRINT ' {'


PRINT ' ' + @connName + '.Dispose();'


PRINT ' ' + @connName + '.Close();'


PRINT ' }'


CLOSE cur


DEALLOCATE cur


END






if(LEN(@errMsg)>0) PRINT @errMsg


DROP TABLE #t_obj


SET NOCOUNT ON






GO


SET QUOTED_IDENTIFIER OFF


GO


SET ANSI_NULLS ON


GO

November 03, 2010

Visual Studio 2005 Crashing in Windows 7 randomly

faulting module craxddrt.dll_unloaded

The problem was that when opening my solution, by default it opened the last crystal report that i was working on. This seemed to start some crystal report ActiveX thing that didn’t work on Windows 7. It would always crash, even if i closed the report straight away.

To stop this problem was to load up VS, close the report immediately and safely close VS before it got a chance to crash! The next time it opens, there is no crystal report and the ActiveX control never loads.

So the next time you save the solution and close visual studio be sure to close all Crystal Reports window

Also I installed Service Pack for VS 2005, http://www.microsoft.com/downloads/en/details.aspx?FamilyID=90e2942d-3ad1-4873-a2ee-4acc0aace5b6&displaylang=en

October 24, 2010

Export responses of sharepoint survey with rating scales to excell sheet

Last day I was not able to export the responses of a sharepoint survey which has multiple rating scale questions.

I removed '&' from the questions .I created a new page in the site with single dataview using sharepoint designer. But none of them did not work for me.

At last resolved the issue by removing 'tabs' from the questions. It happened when I copied questions from a word document, while I created survey.

October 07, 2010

Join in Update/Delete Statement in SQL Server

Update Statement
UPDATE t1
SET t1.TargetCol1 = t2.SourceCol1,
t2.TargetCol2 = t2.SourceCol2
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.PrimaryCol = t1.PrimaryCol

Delete
DELETE TB1 FROM Table1 TB1 INNER JOIN StagingTable ON Table1.Field2 = StagingTable.Field2

October 03, 2010

Single Responsibility Principle Poster

Just because you can implement all the features in a single device, you shouldn't". Why? Because, it adds lot of manageability problems for you in the long run.

September 21, 2010

Synchronize data between MS Access and SQL Server database using Linked Server

I used linked server to export data from an access database to Sql server database at regular interval. I feel it is much easier rather than creating a windows service/scheduled job.

  1. Add a linked server
    EXEC sp_addlinkedserver
    @server = N'AccessDB',
    @provider = N'Microsoft.Jet.OLEDB.4.0',
    @srvproduct = N'OLE DB Provider for Jet',
    @datasrc = N'C:\SourceData.mdb'
  2. Set up login
    EXEC sp_addlinkedsrvlogin

    @rmtsrvname = N'AccessDB',
    @useself = N'False',
    @rmtuser = N'Admin',
    @rmtpassword = ''
  3. Now you can exceute sql statement with this access database.
    Eg: simple select statement:- SELECT * FROM OPENQUERY(AccessDB,  'SELECT * FROM Order')In my case I wrote an insert statement in side sql job with scheduled time interval.

September 20, 2010

Web survey

WebSurvey is an ASP.NET server control that helps developers quickly and easily generated web-based surveys from an XML file

Creating a Windows Service Application

Just thought of sharing a very nice article, explaining how to create a windows service using visual studio
http://msdn.microsoft.com/en-us/library/zt39148a.aspx

Admin privilege is required to follow all the steps in the article.

August 30, 2010

TIMESTAMP/ROWVERSION and UNIQUEIDENTIFIER in SQL Server

A blog post after very very long gap......

The TIMESTAMP column does not contain any information about date/time. It is not dependent on the system or system date

 
This is just a binary format string, which denote version of row. This value will be incremented by 1 on every insert/update of the record.

 
So it can be used for redundancy check on multiple user updating tables

ROWVERSION and TIMESTAMP in sql server are exactly same, where as there are some difference with UNIQUEIDENTIFIER.


 
  • Size of TIMESTAMP value is 8 bytes whereas size of UNIQUEIDENTIFIER is 16 bytes.
  • TIMESTAMP is not based on system date or time. However, UNIQUEIDENTIFIER value is based on the computer's MAC addresses and system date time.
  • The purpose to TIMESTAMP is to track the operation in tables on the database level. The purpose of UNIQUEIDENTIFIER is to have a unique value assigned to a row (maybe as primary key). It remains unique in any system in the world.