Search

Dec 10, 2012

Query to Find First and Last Day of Previous, Current and Next Month - SQL Server

Hello All,

This is normal requirement to find First and Last day of Month. I got one in which I have to find not only current month or given month but Previous and Next month as well.

Following query will gives start date and end date for previous, current and next month respectively. That is First Day of Previous Month, Last Day of Previous Month, First Day of Current Month, Last Day of Current Month, First Day of Next Month and Last Day of Next Month.

DECLARE @PreviousMonthFirstDay DATETIME
DECLARE @PreviousMonthLastDay DATETIME
DECLARE @CurrentMonthFirstDay DATETIME
DECLARE @CurrentMonthLastDay DATETIME
DECLARE @NextMonthFirstDay DATETIME
DECLARE @NextMonthLastDay DATETIME

DECLARE @CurrentDate AS DATETIME = CONVERT( DATETIME, CONVERT( DATE, GetDate() ) )

SELECT @CurrentMonthFirstDay = DATEADD(dd, -(DAY(@CurrentDAte)) + 1, @CurrentDate)
SELECT @CurrentMonthLastDay = DATEADD(d, -1, DATEADD(mm, 1, @CurrentMonthFirstDay))

SELECT @PreviousMonthFirstDay = DATEADD(m, -1, @CurrentMonthFirstDay)
SELECT @PreviousMonthLastDay = DATEADD(d, -1, @CurrentMonthFirstDay)

SELECT @NextMonthFirstDay = DATEADD(d, 1, @CurrentMonthLastDay)
SELECT @NextMonthLastDay = DATEADD(d, -1, DATEADD(mm, 1, @NextMonthFirstDay))

SELECT
@PreviousMonthFirstDay PreviousMonthFirstDay
,@PreviousMonthLastDay PreviousMonthLastDay
,@CurrentMonthFirstDay CurrentMonthFirstDay
,@CurrentMonthLastDay CurrentMonthLastDay
,@NextMonthFirstDay NextMonthFirstDay
,@NextMonthLastDay NextMonthLastDay

Output as of 12-Dec-2012image
You can make this more generic by replacing GetDate() with perticular date while setting up value of @CurrentDate

Sep 20, 2012

Operation is not valid due to the current state of the object

Hello All,

While working with web application, there may be case we got ‘Operation is not valid due to the current state of the object error’ or ‘The URL-encoded form data is not valid’ while doing postback

There may be other reason but one of the reason is form collection reaches its limit of maximum number of keys that it can hold a time. Means you have huge amount of data and you are trying to postback that data to server which end up with this error

Operation is not valid due to the current state of the object error’ or ‘The URL-encoded form data is not valid

Microsoft has added this as security for Form can have 1000 items at a time, however thanks Microsoft also provides way to change this Smile

To change this add following setting into your configuration file

<appSettings>
<add key="aspnet:MaxHttpCollectionKeys" value="10000" />
</appSettings>

In case you have issue with Jason data then you have to add following settings

<appSettings>
<add key="aspnet:MaxJsonDeserializerMembers" value="10000" /></appSettings>

Hope this helps

Jun 27, 2012

Deleting Parent and its child record without setting ON CASCADE DELETE

Hello All,

Very recent I found requirement for removing parent record which is having tons of relation as they have foreign key constrains; you can’t delete them unless you have ON CASCADE DELETE

I found solution on Stack Overflow and base main article from sqlteam. I have modified procedure to fix some minor issue and here is the procedure which take table name and a query to filter row which we need to delete from table along with its reference.

-- ================================================
-- Expects the name of a table, and a conditional for selecting rows
-- within that table that you want deleted.
-- Produces SQL that, when run, deletes all table rows referencing the ones
-- you initially selected, cascading into any number of tables,
-- without the need for "ON DELETE CASCADE".
-- Does not appear to work with self-referencing tables, but it will
-- delete everything beneath them.
-- To make it easy on the server, put a "GO" statement between each line.
-- ================================================
CREATE PROCEDURE DeleteCascade (
@BaseTableName VARCHAR(200)
,@BaseCriteria VARCHAR(1000)
)
AS BEGIN

DECLARE @ToDelete TABLE
(
Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
Criteria VARCHAR(5000) NOT NULL,
TableName VARCHAR(200) NOT NULL,
Processed BIT NOT NULL,
DeleteSql VARCHAR(5000)
)

SET NOCOUNT ON

INSERT INTO @ToDelete (Criteria,TableName , Processed)
VALUES (@BaseCriteria, @BaseTableName, 0)

DECLARE @Id INT
DECLARE @Criteria VARCHAR(5000)
DECLARE @TableName VARCHAR(5000)

WHILE EXISTS(SELECT 1 FROM @ToDelete WHERE Processed = 0)
BEGIN

SELECT TOP 1
@Id = Id
,@Criteria = Criteria
,@TableName = TableName
FROM @ToDelete
WHERE Processed = 0
ORDER BY Id DESC

INSERT INTO @ToDelete (Criteria, TableName, Processed)
SELECT
ReferencingColumn.name + ' IN (SELECT [' + ReferencedColumn.name + '] FROM ['
+ @TableName +'] WHERE ' + @Criteria + ')',
ReferencingTable.name,0
FROM sys.foreign_key_columns fk
INNER JOIN sys.columns ReferencingColumn
ON fk.parent_object_id = ReferencingColumn.object_id
AND fk.parent_column_id = ReferencingColumn.column_id
INNER JOIN sys.columns ReferencedColumn
ON fk.referenced_object_id = ReferencedColumn.object_id
AND fk.referenced_column_id = ReferencedColumn.column_id
INNER JOIN sys.objects ReferencingTable
ON fk.parent_object_id = ReferencingTable.object_id
INNER JOIN sys.objects ReferencedTable
ON fk.referenced_object_id = ReferencedTable.object_id
INNER JOIN sys.objects constraint_object
ON fk.constraint_object_id = constraint_object.object_id
WHERE ReferencedTable.name = @TableName
AND ReferencingTable.name != ReferencedTable.name

UPDATE @ToDelete
SET Processed = 1
WHERE Id = @Id

END

SELECT
'PRINT ''Deleting from ' + TableName + '...''; DELETE FROM [' + TableName + '] WHERE '
+ Criteria
FROM @ToDelete
ORDER BY Id DESC

END



When you execute this, you will see list of DELETE statement, which you need to run.

May 15, 2012

Enumeration Types as Bit Flags

An enum is best practice to give internal constants to readable string format, example

image

It’s very handy to use string rather to remember int value and it also helps to maintain code if down the road we need to change integer value it wont be difficult. I am sure developer knows very well about how to use enum in their code.

The post is all about answering few question comes while development or requirement, How you pass multiple value by using enum? or How to assign multiple enum values to variable? or How can I store combination of enum values using single enum variable?

All question have single answer, to use enum as bit flags. In order to achieve this, you have to add new attribute Flags to your enum declaration. As these values are bit you can use bitwise operation which are AND, OR, XOR and NOT.

Let’s see useful example of Flag enum using normal coding and then we will see same thing using Flags.

In normal coding if I want to calculate discount for four country and more then one at same time, I rather create for methods and call four individual functions

image

OR I can add bool into signature for calculating particular discount.

image

Drawback of first method is you have to keep adding new functions if you introduce new country and in second you have to pass false to country which you don’t want to do calculation, so signature will get increase in case we have more countries

Now lets use Flag enumeration to avoid mention drawbacks.

This will how your new enum will look like

image

A new attribute called Flags is added and we have assigned constants in power or TWO, that is 1,2,4,8 and so on, so when we use in combination no one get overlap.

Lets go ahead and create CalculateDiscount function and use into our program.

image

So single function CalculateDiscount is responsible to calculate discount for four different country at a time. If I want to calculate discount for German and Canada, you can do that by passing both in single argument using ‘|’ operator, its bitwise OR operator. You can use ‘&’ bitwise AND operator to determine whether a specific flag is set or not.

Apr 4, 2012

Changing Physical Path of multiple Applications in IIS 7

Hello All,

Usually, we need functionality where we have more then one set of application configured in our machine, like QA environment and DEV environment. We don’t have way to keep two directory pointing to same location in single web site.

We can use backup feature for IIS 7 which helps to take backup as well as change directories of multiple applications at same time. First we are going to do backup IIS7

Go to your inetsvr directory for your system’s system32 directory from command prompt. Make sure you are using Administrator account, if not then open command prompt with “run as administrator”

image 

Write following command in command prompt

appcmd add backup NAME_OF_BACKUP

image

I have created backup with name IISBackup, So we created backup of IIS including application pool, application, virtual directory etc. We will now change the physical path of more then one application and then restore backup back to IIS

Open backup folder in explorer.

image

Open that folder and edit applicationHost.config into notepad

image

In sites tag you can see your application with all details. Update physicalPath with your new path and save the file

image

Once you are done with this, go back to command prompt to restore our updated IIS settings, write following command, it will take few secs to set your new configurations.

appcmd restore backup NAME_OF_BACKUP

image

You are done!

So, we see how to backup and restore IIS, and how to change physical path of more then one applications.