Site Sections
Posts
33
Comments
186
Trackbacks
0
June 2011 Entries
Date/Time formats in SQL Server

In SQL server a column with datatype of DateTime stores the time to the millisecond, but many times you just like to show the date and time only upto the second.

Although there are many ways to achieve this, I'm just going to show the couple of possible ways -

1.)

SELECT CONVERT(VARCHAR,GETDATE(),120)

This would yield the current date time in YYYY-MM-DD HH:MM:SS (ofcourse depends on your regional setting)

2.) You can get the millisecond part from the getdate() function and subtract it with the getdate() to leave you with time only upto second.

SELECT DATEADD(ms, -DATEPART(ms,GetDate()),GetDate())

As I mentioned earlier, these are just some of the possible ways to acheive the desired result.

The post www.sql-server-helper.com/tips/date-formats.aspx has some very good collection to acheive various formats in date/time in SQL.

Hope this helps!

 

Posted On Thursday, June 9, 2011 12:21 PM | Comments (2)
Detecting browser 'Refresh' from Code behind in C#

Browser 'Refresh' is always a cause of concern for the developers. It becomes even more worse when the page interacts with the database. As each refresh, if not handled, would lead to the database action repeated.

This could lead to inconsistency in database or even break the application.

One way could be to detect 'refresh' using javascript and disable F5 or right click..but there are so many other ways end-user can initiate 'Refresh' action for e.g. by using CTRL+R on the keyboard...

The best way to stop 'Refresh' calling your program again is to detect it at the server side and handle it..

The following code snippet detects 'Refresh' in page_load function

bool IsPageRefresh = false;

//this section of code checks if the page postback is due to genuine submit by user or by pressing "refresh"

if (!IsPostBack)

    
{

ViewState["ViewStateId"] = System.Guid.NewGuid().ToString();

Session["SessionId"] = ViewState["ViewStateId"].ToString();

}

else
 
{

if (ViewState["ViewStateId"].ToString() != Session["SessionId"].ToString())

{

IsPageRefresh = true;

}

Session["SessionId"] = System.Guid.NewGuid().ToString();

ViewState["ViewStateId"] = Session["SessionId"].ToString();

}     
 


You can then use the 'IsPageRefresh' boolean flag in the code-behind to determine if it's a postback due to genuine User submit action or by browser 'Refresh'.

Hope this helps!

Posted On Wednesday, June 8, 2011 4:34 PM | Comments (13)
Catching Schema Errors in TRY..CATCH block in SQL Server

Recently I was writing a stored procedure in SQL Server 2005. The stored procedure was pretty straight forward with some couple of SQL DML statements inside TRY block and exception being handled and raised in CATCH block.

However, while testing the exception handling of the stored procedure, it didn't seem to go to CATCH block.

All I was trying was - Insert some data into some 'non-existent' table and catch the error in the CATCH block. I was expecting stored procedure to report 'Invalid object name' but it didn't...

BEGIN TRY

SELECT * FROM NonExistentTable

END TRY 

BEGIN CATCH

INSERT INTO EXCEPTION (Number, Message)

VALUES (ERROR_NUMBER(), ERROR_MESSAGE())

END CATCH

Little bit of digging led me to the folowing link

http://www.codeproject.com/KB/database/try_catch.aspx

It is a very good article on Try..Catch statement, according to this the Try/Catch block will not be able to detect any COMPILE errors which includes schema errors.

To be able to CATCH such errors, we should wrap the statements in dynamic SQL in TRY block .. this solved my problem..

BEGIN TRY

DECLARE @sql nvarchar(100)

SET @sql = 'SELECT * FROM NonExistentTable'

EXEC sp_executesql @sql

END TRY

BEGIN CATCH

INSERT INTO EXCEPTION (Number, Message)

VALUES (ERROR_NUMBER(), ERROR_MESSAGE())

END CATCH

Hope this helps someone too!

 

Posted On Wednesday, June 8, 2011 3:15 PM | Comments (1)
Dynamic table creation using SELECT INTO Clause in SQL

While working with SQL, you come across a scenario when you need to dump the resultset of a SELECT statement into either a temporary table or a placeholder table. I can imagine how tiresome it is to create a schema for the table beforehand and keep that updated with any additions/deletions to the SELECT statement.

To cut the long story short, SQL makes ones life easy with the help of a syntax "SELECT... INTO..."

E.g.

SELECT column1, column2,  column3

INTO NewDynamicTable

FROM TableName

This would create a new table 'NewDynamicTable'  with 3 coumns and populate it with the resultset of the SELECT statement.

Tip - If you want to create just schema for the table and does not want to populate it with the results of the select statement - use a where clause in the SELECT statement which won't yield any results.

SELECT column1, column2

INTO NewTable

FROM TableName

WHERE 1=2

Hope this helps!

Posted On Tuesday, June 7, 2011 12:07 PM | Comments (3)
Unsign the assembly for Warning VSP2013

Recently while performing a .Net unit testing on some .Net dll, I kept getting the below error -

'XXX is a strongly named assembly.  It will need to be re-signed before it can be executed.

Warning VSP2013 : Instrumenting this image requires it to run as a 32-bit process"......

A little of  google revealed that since its a strongly named assembly and while performing unit testing it needed to be re-signed or I need to remove the 'strong name' all together...

As I didn't had the orginal private key file with which the assembly was initially signed, I choose the latter option.

To de-sign the assembly -

Go to visual studio command prompt  (Start -> Progrmas -> Microsoft Visual Studio 2005/2008 -> Visual Studio tools -> Command prompt)

Type the following  -

SN -Vr Path_Of_Assembly

Make sure to sign the assembly again at the end of the task otherwise it may lead to some security issues.

SN -Vu Path_Of_Assembly

 Hope this helps!!

 

Posted On Friday, June 3, 2011 2:30 PM | Comments (0)
Archives
Post Categories