Friday, November 30, 2007

Final Release of VS 2008 and .NET 3.5 Now Available

Final Release of VS 2008 and .NET 3.5 Now Available

Final Release of VS 2008 and .NET 3.5 Now Available

Experience the latest release of the most productive and powerful development tool and user interface platform on the planet. Learn about the new features in Visual Studio 2008 and the .NET Framework 3.5, from built-in ASP.NET AJAX support, to the new Visual Studio Web page designer, to the enhanced JavaScript support; then watch the ASP.NET 3.5 video series and the LINQ video series; and then download a free copy of Visual Web Developer 2008 to try it out yourself.

Go to the link: http://asp.net/downloads/vs2008/

View Sabah u Din Irfan's profile on LinkedIn

What's new in Visual Studio 2008 and .NET Fx 3.5 for the Web Developer

Kindly visit the following link, it is very useful:

http://msdn2.microsoft.com/en-gb/bb905504.aspx?id=108

SQL Server 2005: Using NULLIF

It takes two parametes like,
NULLIF (param1,param2)
and returns NULL if both param1 and param2 are equal.

NULLIF returns the first parameter if the two parameters are not equivalent

NOTE:
param1 and param2 can be a constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators.

NULLIF is somehow similar to CASE function.


http://www.bloglines.com/blog/sabah-irfan?id=41

SQL Server 2005: Using NULLIF

It takes two parametes like,
NULLIF (param1,param2)
and returns NULL if both param1 and param2 are equal.

NULLIF returns the first parameter if the two parameters are not equivalent

NOTE:
param1 and param2 can be a constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators.

NULLIF is somehow similar to CASE function.


http://www.bloglines.com/blog/sabah-irfan?id=41

Monday, October 29, 2007

JavaScript: Some Useful Functions

toUpperFirstChar:
Accepts an object of type "Text" and converts the first character of the string to upper case.
Parameters: Object
Returns: n/a

Here is the defination:

function toUpperFirstChar(obj)
{
LTrimObj(obj);
obj.value = obj.value.substr(0,1).toUpperCase()+obj.value.substr(1,obj.value.length-1);
}


checkDateValidity:-
This method checks if the fDt is less than tDt or not.
Parameters: FromDate, ToDate
Returns: Boolean

Here is the defination..


function checkDateValidity(fDT,tDT)
{
var fyear=fDT.charAt(7)+fDT.charAt(8)+fDT.charAt(9)+fDT.charAt(10);
var tyear=tDT.charAt(7)+tDT.charAt(8)+tDT.charAt(9)+tDT.charAt(10);

var fm=fDT.charAt(3)+fDT.charAt(4)+fDT.charAt(5);
var tm=tDT.charAt(3)+tDT.charAt(4)+tDT.charAt(5);
var fmonth=MonthReplace(fm);
var tmonth=MonthReplace(tm);

var fday=fDT.charAt(0)+fDT.charAt(1);
var tday=tDT.charAt(0)+tDT.charAt(1);

fDate = new Date(fyear,fmonth-1,fday);
tDate = new Date(tyear,tmonth-1,tday);

if(fDate > tDate)
{
alert("From date must not be greater than to date");
return false;
}
else
{
return true;
}
}


varifyPassword:-
varifies if the password is more than 6 char long and it contains atleast one digit and 4 distinct characters.

Here is the defination of the function:

function varifyPassword(pass)
{
pass=Trim(pass);
var validFlag = 1;
if(pass.length < 6)
{
validFlag = 0;
}
else
{
alphaList=new Array();
numList=new Array();
var flag=0;
for(var i=0; i< pass.length; i++)
{
var oneChar=pass.charAt(i)
if(isAlphabet(oneChar))
{
var charFlag=1;
for(var p=0; p {
if(oneChar==alphaList[p])
{
charFlag=0;
break;
}
}
if(charFlag)
{
alphaList[alphaList.length]=oneChar;
}
}
else if(oneChar>=0 && oneChar <=9)
{
var numFlag=1;
for(var p=0; p {
if(oneChar==numList[p])
{
numFlag=0;
break;
}
}
if(numFlag)
{
numList[numList.length]=oneChar;
}
}
else
{
validFlag = 0;
break;
}


if ((alphaList.length >= 1) && (numList.length>= 1) && ((alphaList.length + numList.length) >= 4))
{
flag = 1;
break;
}
}

if(flag)
{
return true;
}
else
{
validFlag = 0;
}
}

if (! validFlag)
{
alert("The password is not valid, or needs to be updated. Consult the help page for instructions about choosing a valid password.")
return false;
}
}

JSP: sendRedirect() vs. getRequestDispatcher()

Today my other team member was facing a problem and the request objects were destroed when he was using request.sendRedirect(). He was struggling with it and came to me. Actually there is other method to use when you want to pass the origional request objects, that is getRequestDispatcher();


When you want to preserve the current request/response objects and transfer them to another resource WITHIN the context, you must use getRequestDispatcher or getNamedDispatcher.

If you want to dispatch to resources OUTSIDE the context, then you must use sendRedirect. In this case you won't be sending the original request/response objects, but you will be sending a header asking to the browser to issue a request to the new URL.


NOTE: If you don't need to preserve the request/response objects, you can use either.

Friday, September 28, 2007

JavaScript@ Validation Functions

1- Empty Field Validation:- (To check whether a text field is empty or not)

function IsEmpty(aTextField) {
if ((aTextField.value.length==0)
(aTextField.value==null)) {
return true;
}
else { return false; }
}
////////////////////////////////////////////////

2- Numeric Validation:- (To check whether a text field contains Numeric Values)

function IsNumeric(sText)
{
var ValidChars = "0123456789.";
var IsNumber=true;
var Char;


for (i = 0; i < sText.length && IsNumber == true; i++)
{
Char = sText.charAt(i);
if (ValidChars.indexOf(Char) == -1)
{
IsNumber = false;
}
}
return IsNumber;

}
////////////////////////////////////////////////

3- Aplphabet Validation:- (To check whether a text field contains Alphabetic Values)


function IsAlphabet(sText)
{
var ValidChars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
var IsAlpha=true;
var Char;


for (i = 0; i < sText.length && IsAlpha == true; i++)
{
Char = sText.charAt(i);
if (ValidChars.indexOf(Char) == -1)
{
IsAlpha= false;
}
}
return IsAlpha;

}
/////////////////////////////////////////////////////////////////////////

3- Email Address Validation:- (To check whether a text field contains valid email value)


function isValidEmail(str) {
return (str.indexOf(".") > 2) && (str.indexOf("@") > 0);

}

Monday, September 24, 2007

SQL Server @ To List The Database Names along with their Size

The following system stored procedure is helpful to list the all database names along with their size in KiloBytes:

EXEC sp_databases

Results:
--------------------------------
DataBase_Name DataBase_Size REMARKS
HighFall_Rockin 3712 NULL
master 4608 NULL
model 1728 NULL
msdb 7360 NULL
tempdb 19456 NULL
TLSPFX 4689152 NULL
TLSPFX_BK 987008 NULL

SQL Server @ Query To List Available Triggers On a DataBase

The following query is helpful to enlist all the available triggers on a specific database. sys.triggers is a view that contains a row for each object that is a trigger.

Here is the query:

USE MSDB -- Database Name Here
SELECT name,type,type_desc,create_date,modify_date
FROM sys.triggers


Results:
---------------------------------------------------------
name type type_desc create_date modify_date

trig_sysmail_profile_delete TR SQL_TRIGGER 2005-10-14 01:55:32.520 2005-10-14 02:02:31.787
trig_sysmail_servertype TR SQL_TRIGGER 2005-10-14 01:55:32.740 2005-10-14 02:02:31.850
trig_sysmail_server TR SQL_TRIGGER 2005-10-14 01:55:33.067 2005-10-14 02:02:31.910
trig_sysmail_configuration TR SQL_TRIGGER 2005-10-14 01:55:33.287 2005-10-14 02:02:31.943
trig_sysmail_mailitems TR SQL_TRIGGER 2005-10-14 01:55:33.507 2005-10-14 02:02:31.990
trig_backupset_delete TR SQL_TRIGGER 2005-10-14 01:55:16.113 2005-10-14 02:02:32.007
trig_sysmail_attachments TR SQL_TRIGGER 2005-10-14 01:55:33.723 2005-10-14 02:02:32.050
trig_sysmail_log TR SQL_TRIGGER 2005-10-14 01:55:33.943 2005-10-14 02:02:32.100
trig_sysoriginatingservers_delete TR SQL_TRIGGER 2005-10-14 01:54:09.833 2005-10-14 02:02:32.397
trig_sysjobs_insert_update TR SQL_TRIGGER 2005-10-14 01:54:10.490 2005-10-14 02:02:32.520
trig_sysschedules_insert_update TR SQL_TRIGGER 2005-10-14 01:54:12.677 2005-10-14 02:02:33.210
trig_targetserver_insert TR SQL_TRIGGER 2005-10-14 01:55:08.570 2005-10-14 02:02:34.740
trig_notification_ins_or_upd TR SQL_TRIGGER 2005-10-14 01:55:14.583 2005-10-14 02:02:35.333
trig_notification_delete TR SQL_TRIGGER 2005-10-14 01:55:14.910 2005-10-14 02:02:35.363
trig_sysmail_profile TR SQL_TRIGGER 2005-10-14 01:55:31.647 2005-10-14 02:02:35.787
trig_principalprofile TR SQL_TRIGGER 2005-10-14 01:55:31.867 2005-10-14 02:02:35.833
trig_sysmail_account TR SQL_TRIGGER 2005-10-14 01:55:32.083 2005-10-14 02:02:35.863
trig_sysmail_profileaccount TR SQL_TRIGGER 2005-10-14 01:55:32.303 2005-10-14 02:02:35.910

SQL Server @ Query To List The available SQL JOBS

The following query is helpful to enlist the name, created date, modified date and description of the Sql Jobs available. You can add the mre column in the result set if needed.

USE MSDB

SELECT name,enabled,date_created,date_modified,description
FROM sysjobs

Results:
-------------------------------------------------------------
Name Enabled Date Created Date Modified
GEServiceEntries 1 2007-04-11 11:25:49.897 2007-04-11 11:54:05.503
CDW_CORFXSQL01_CORFXSQL01_0 1 2006-02-28 19:51:08.967 2006-02-28 19:51:09.217
DataEntryHHReports 1 2006-11-03 13:25:36.230 2007-09-20 11:43:29.297

SQL Server@ Query To See the Execution History of SQL JOBS

I have made a couple of SQL jobs that are scheduled accordingly to perform some specific tasks. I wanted to know whether the jobs are executed accordingly or not? And what are the results of the execution? Means success or failure. And finally, I created a query for this purpose after looking into the MSDB. Here is it:

USE MSDB

SELECT j.name,h.run_status,h.run_date,h.run_time, h.run_duration,h.server, h.message
FROM sysjobhistory h,sysjobs j
WHERE h.job_id=j.job_id
ORDER BY run_date desc ,run_time desc

Here are the results: (NOTE: I have omitted the Message column here)
-----------------------------------------------------------------------------
DataEntryHHReports 1 20070921 121000 0 CORFXSQL01
DataEntryHHReports 1 20070921 121000 1 CORFXSQL01
DataEntryHHReports 0 20070914 121301 0 CORFXSQL01
DataEntryHHReports 2 20070914 121001 0 CORFXSQL01
DataEntryHHReports 0 20070914 121000 301 CORFXSQL01
GEServiceEntries 1 20070911 0 0 CORFXSQL01
GEServiceEntries 1 20070911 0 1 CORFXSQL01
DataEntryHHReports 1 20070907 121000 1 CORFXSQL01
DataEntryHHReports 1 20070907 121000 1 CORFXSQL01
DataEntryHHReports 1 20070831 121001 0 CORFXSQL01
DataEntryHHReports 1 20070831 121000 1 CORFXSQL01
DataEntryHHReports 1 20070824 121001 0 CORFXSQL01
DataEntryHHReports 1 20070824 121000 1 CORFXSQL01
DataEntryHHReports 1 20070817 121000 0 CORFXSQL01
DataEntryHHReports 1 20070817 121000 0 CORFXSQL01
GEServiceEntries 1 20070811 0 0 CORFXSQL01
GEServiceEntries 1 20070811 0 0 CORFXSQL01


Monday, September 3, 2007

SQL Server@ List Stored Procedure Names using specific table

Sometimes you need to drop some table or needs to modify the name of the table, and before doing that you want to make sure which are the stored procedures that are using this table?

Secondly, you just want to update the table by deleting it's some column, and you want to make sure which are the stored procedures that are using this column?

The following query gives you the list of Stored procedures that are using some specific table name "CDRMaster".

SELECT Name, create_date,modify_date
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%CDRMaster%'

The following query will give you the list of the stored procedures using some specific column name(CDRID in this case)of the table CDRMaster:

SELECT Name, create_date,modify_date
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%CDRMaster%'
AND OBJECT_DEFINITION(object_id) LIKE '%CDRID%'


SQL Server@ List Stored Procedure Names Created in Specific Date Range

This simple query returns the list of stored procedure names that are created with in the specific data range given in where clause.

SELECT Name,create_date,modify_date
FROM sys.procedures

WHERE create_date between '07/14/2007' and '08/20/2007'


Wednesday, August 29, 2007

Java 1.4: ASSERTIONS

Assertions provide a convenient mechanism for verifying that a class's methods are called correctly. This mechanism can be enabled or disabled at runtime. The intention is that assertions typically be enabled during development and disabled in the field.

Assertions has the following syntax:

assert Expression1;
assert Expression1 : Expression2;

  • Expression1 must be boolean type.
  • Expression2 may have any type.
  • If assertions are disables at runtime (default state) then assert statement does nothing.
  • If assertions are enabled at runtime( using command line argument to JVM) then Expression1 is evaluated. If it is true, no further action is taken. If it is false then AssertionError is thrown. If Expression2 is present, it is passed to constructor of the AssertionError where it is converted to String and used as a error message.

Thursday, August 16, 2007

Inserting rows with default values in a Table

The following simple query will insert a row into the table name specified with the default values for each column:

INSERT INTO TableName DEFAULT VALUES

Tuesday, August 7, 2007

JAVA 5.0 Autoboxing/Unboxing

What is Boxing?
Converting a primitive type to it's wrapper class is known as Boxing. For example converting from int to Integer, float to Float, double to Double etc.

What is unboxing?
The reciprocal of Boxing is called unboxing, means converting from some wrapper class to it's respective primitive type is known as unboxing. For example converting from Integer to int, Float to float, Double to double etc.

In the earlier versions from JDK 1.5 , boxing and unboxing was manual. You have to explicitly convert from one into another. But in JDK 1.5 boxing and unboxing is automatic and the compiler will do this itself on your behalf.

Example of Manual Boxing/unboxing:

int a = 2;
int b;
Integer aW = Integer(a); // This is example of boxing

b =aW.intValue(); // This is an example of unboxing.

If you want to store int values of some range say 0-100 in an ArrayList, then what you need to do ? Certainly you will make the wrapper objects of Integer class and will add them into an array list like here

ArrayList list = new ArrayList();
for(int i = 0; i <100;i++)
list.add(
new Integer(i));// You can pass only Objects into add method // not primitives
}

This is basically boxing. Similarly if you want to add the elements of the ArrayList into some int variable then you need to do the unboxing like shown here:

sum += ((Integer)list.get(i)).intValue();

But if you are using JAVA 5.0, all of these will be done automatically by the compiler on your behalf like shown here:

ArrayList list = new ArrayList();
for(int i = 0; i <100;i++)
{
list.add(i); // AutoBoxing is done here
}

int sum = 0;
for
( Integer j : list)
{
sum += j; // Auto-unboxing is done here..
}

For further reference Read the SUN DOCUMENTATION from the following link:

http://java.sun.com/j2se/1.5.0/docs/guide/language/autoboxing.html



Monday, August 6, 2007

JAVA 5.0 Enhanced For- Loop

Enhanced FOR-LOOP is a new addition in JAVA 5.0. If you have worked in Visual Basic then you have used FOR-EACH loop in VB. Enhanced For-loop in JAVA5.0 is very similar to For-Each Loop of Visual Basic.

Enhanced for-loop is used to iterate arrays and collections.

Iteration over Arrays :
 The  syntax for iteration over arrays is as following :

for (type variable : array)
{
body-of-loop
}
First we make a function that uses simple/conventional For Loop to add an array of int and returns its sum:

int sumArray( int [] arr )
{
int sumArray=0;
for ( int i=0;i
{
sumArray+=arr[i];
}
return sumArray;
}

If we want to utilize the new Enhanced for-loop( sometimes called For-Each loop or For-In loop) the above function will be like.

int sumArray( int [] arr )
{
int sumArray=0;
for ( int i : arr )
{
sumArray+=i; // here i gets successively each value in array a
}

return sumArray;
}

Iteration over Collections :
 The  syntax for iteration over collections is as following :

for (type variable : collection )
{
body-of-loop
}

For example we have a collection of books in a List and we retrieve it using Iterators using the
conventional for- loop like:

List books = ......
for (Iterator i = books.iterator(); i.hasNext(); )
{
Book book= (Book) i.next();
//..... body of loop ....
}

Its reciprocal syntax using enhanced-for loop will look like,
// Assume there is a List of books available like
List books = ......

for (Book book : books)
{
// /now book has the value for each iteration of list
}

Restrictions of Enhanced For-Loop:
  • It is not possible to traverse more than one structures at the same time. For example two arrays.
  • You can only access only single element at each iteration. You can not have access to more than one elements. Like in case of comparing successive elements in an array.
  • You can not iterate an array backwards. It is forward-only and single step increment.
  • It is not compatible for the earlier versions of the JAVA 5.0.
For more details visit the following link of SUN DOCOMENTATION

Friday, August 3, 2007

JAVA: A Method to Get Current Day of Week

Hello everybody, here is the method that returns the current day of week.

public static String getCurrentDayofWeek()
{
String DAY="";
Calendar cal =Calendar.getInstance();
int dayOfWeek = cal.get(Calendar.DAY_OF_WEEK);
switch (dayOfWeek)
{
case 1:
DAY="SUNDAY";
break;
case 2:
DAY="MONDAY";
break;
case 3:
DAY="TUESDAY";
break;
case 4:
DAY="WEDNESDAY";
break;
case 5:
DAY="THRUSDAY";
break;
case 6:
DAY="FRIDAY";
break;
case 7:
DAY="SATURDAY";
break;
}
return DAY;
}

Thursday, August 2, 2007

New Features in JAVA 5.0

The following is the list of new features added on in version 5.0 of JAVA.
  1. Generics
  2. Enhanced "for" loop ( Click Here for Detail )
  3. Autoboxing/Unboxing ( Click Here for Detail )
  4. Typesafe Enums
  5. Varargs
  6. Static Import ( Click Here for Detail )
  7. Metadata( Annotations)

SCJP Hand outs Part-1

  1. The floating-point numbers did not throw divide by zero ArithmeticException. They will give a result that is Not A Number NAN.
  2. The case argument must be either an int literal , or an int-compatible variable which is a constant (i.e static or final)
  3. Assume byte a=3; byte b=2; byte c= a+b; à Compile time error because the addition operator will wide the byte to int before addition and result should be stored in int variable.
  4. An abstract class can have a constructor.
  5. It is legal to access a static method using an instance of the class. But it is preferred way to use the class name to access.
  6. Methods can be overridden Attributes/member variables can not.
  7. If a try/catch block calls System.exit() then finally block will not execute.
  8. A member variable can not be native.
  9. Constructors are not inherited.
  10. You can not automatically/implicitly convert a char to short. There is same bit depth but since char are unsigned they might have higher positive value than a short can accept.
  11. Arithmetic Exception is not a checked exception.
  12. An interface can extend multiple interfaces however a class can extend only one class.
  13. A member variable can not be declared synchronized.
  14. Math.round(-3.22) = -3
  15. You can not make a final abstract class.
  16. A constructor of a class can not be declared static.
  17. Inner classes can not be defined in a class outside of its methods like where declaring member variables.
  18. A class can not be declared transient.
  19. A method can not be declared transient.
  20. static methods of a class can not be accessed using this.MethodName().
  21. Only variables can be declared as volatile.
  22. A floating point literal (like 3.45) in java will be treated as double unless you specify an f at the end like (3.45f).
  23. A top level class can not be marked as private.
  24. An overloaded method can change the return type, but return type alone is not enough—it also must change the argument list.
  25. If there is a return statement in try/catch block then finally block will also be executed.
  26. int iArray[5] = new int[] {1,2,3,4,5}; will not compile but int iArray[] = new int[] {1,2,3,4,5}; will compile.
  27. An anonymous inner class can not have a constructor.
  28. Result of System.out.println(Math.sqrt(-4)); is equal to NAN.
  29. A top level class can not be marked as protected.
  30. cast is not a keyword in JAVA.

Tuesday, July 31, 2007

STATIC Import, A new feature in JAVA 5.0

Static Import statement is the new addition in JDK 5.0. This statement is used to import the static variables/fields and static members of some class.

Let's take an example, like if we need to use the "PI" static field of Math class in java.lang then we need to write something like :

double a=10;
double res = Math.sin(Math.PI * a);

But if you use the static import in your program then it will be like:

import static java.lang.Math.PI;

double a=10;
double res = Math.sin(PI * a);
you noticed that we have no need to write the fully qualified name of the PI but still we need to write the fully qualified name of the static method "sin" like Math.sin(..).

There is a second form of the static import that imports all of the static fields and members of a class .i.e by using the * notation.


import static java.lang.Math.*;

double a=10;
double res = sin(PI * a);

Friday, July 27, 2007

Visual Studio .NET 2008 Beta Available to Download Now

Visual Studio .NET 2008 Beta Available to Download Now on the Microsoft website:
Here are the Links:

You can download the Visual Studio 2008 product here. You can alternatively download the smaller VS 2008 Express Editions here.


Enjoy!!!!!!

Visual Studio .NET 2008 First Look Video

I was surfing on the net today and came across a link for the first look video of Visual Studio .NET 2008. I would like to share it with you people.

Here is the link:

http://www.xmlforasp.net/codebank/download/blog/video/LinqDataSource.wmv

Enjoy!!!!!!

UDF to Get Day of Week in SQL Server 2005

The following UDF can be used to get the Day of Week by passing it a specific date:

CREATE FUNCTION dbo.udf_Day_Of_Week(@p_Date DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @return_DayofWeek VARCHAR(10)
SELECT @return_DayofWeek = CASE DATEPART(dw,@p_Date)
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
RETURN (@return_DayofWeek)
END


Wednesday, July 25, 2007

How To Take Database Schema Backup in SQL Server 2005

How To Take Database Schema Backup in SQL Server 2005

  • Go to OBJECT EXPLORER of the SQL Server 2005 and expand the Databases tree node, you can see the available databases in your server.





  • Then Right Click on the specific Database you wish to make the schema backup. And go to TASKSà GENERATE SCRIPTS like shown in the figure below:





  • After selecting the GENERATE SCRIPTS you will see a welcome screen like shown below.




  • Click on NEXT button of the Wizard and you will be asked to select the database you wish to generate schema backup like below in figure(TestDB is the database in this case):





  • If you want to take the full schema back up of the database then check the checkbox “Script all objects in the selected database” like shown here in the figure and click Next button of the wizard.




  • In the next screen you can play with the different properties. Just need to change one of the property if you need to use this schema on SQL Server 2000 version, that is “Script for Server Version” and select the appropriate version you needed.





  • After selecting the appropriate SQL Server version for which you are preparing the schema backup click Next Button and you will reach at the OUTPUT OPTION and then select the appropriate output option. In my case I am saving the script of schema in a File of Unicode Text like:





  • Then Click Next and you will reach on the Script wizard Summary screen, Simply Click Finish to execute your requested job.





  • After clicking the Finish button you will reach at the Generate Script progress Wizard. It will take a little time depending upon the number of objects in your selected database. After completing its job it will show you the success status.




  • If you want to see the Report of the whole process, just select from the options of Report button just up from the Close and select the output option of the report. Otherwise click Close. That’s it and you are Done.

Tuesday, July 24, 2007

A UDF To Get Previous Working Day in SQL Server 2005

It has pretty similar logic as we done with the UDF of Next Working Day. You can read the earlier post on "How to get the Week Day" for better understanding. Here is the self explanatory code.

CREATE FUNCTION dbo.udf_GetPreviousWorkingDay (@p_Date DATETIME )
RETURNS DATETIME
AS
BEGIN
DECLARE @m_WeekDay INT
DECLARE @rt_Prev_Working_Day DATETIME
SET @m_WeekDay = DATEPART(weekday,@p_Date)

IF @m_WeekDay = 1 -- IF SUNDAY then subtract 2 fromsunday to get friday that is working day
SET @rt_Prev_Working_Day = DATEADD(d,-2,@p_Date)
ELSE IF @m_WeekDay = 2 -- IF MONDAY then subtract 3 fromsunday to get friday that is working day
SET @rt_Prev_Working_Day = DATEADD(d,-3,@p_Date)
ELSE -- ELSE subtract one to get the prev date
SET @rt_Prev_Working_Day = DATEADD(d,-1,@p_Date)

RETURN @rt_Prev_Working_Day
END

Here are some of the test cases having the same result:

SELECT dbo.udf_GetPreviousWorkingDay ( '07/21/2007')
SELECT dbo.udf_GetPreviousWorkingDay ( '07/22/2007')
SELECT dbo.udf_GetPreviousWorkingDay ( '07/23/2007')

Result:

2007-07-20 00:00:00.000

A UDF To Get The Next Working Day @ SQL Server 2005

The following scalar valued function will return the next working day of the week. Here is the code:

CREATE FUNCTION dbo.udf_GetNextWorkingDay (@p_Date DATETIME )
RETURNS DATETIME
AS
BEGIN
DECLARE @m_WeekDay INT
DECLARE @rt_Next_Working_Day DATETIME
SET @m_WeekDay = DATEPART(weekday,@p_Date)

IF @m_WeekDay = 6 -- Friday
SET @rt_Next_Working_Day = DATEADD(d,3,@p_Date)
ELSE IF @m_WeekDay = 7 -- Saturday
SET @rt_Next_Working_Day = DATEADD(d,2,@p_Date)
ELSE
SET @rt_Next_Working_Day = DATEADD(d,1,@p_Date)

RETURN @rt_Next_Working_Day
END

To test this UDF you can use the following example:

SELECT dbo.udf_GetNextWorkingDay ( '07/20/2007')
SELECT dbo.udf_GetNextWorkingDay ( '07/21/2007')
SELECT dbo.udf_GetNextWorkingDay ( '07/22/2007')

The result of all of the above test cases will be as following:
2007-07-23 00:00:00.000

How to Get Week-Day and Quarter of year in SQL Server 2005

  • You can get the week-day by using the DATEPART scalar valued function of the SQL Server 2005. For example the following statement returns 1 (one) as it is default value for Sunday.
SELECT DATEPART(weekday,'07/22/2007' )
or
SELECT DATEPART(dw,'07/22/2007' )

Result = 1
-------------------------------------------------------
The following are the default values for the week days:

SUNDAY = 1
MONDAY= 2
TUESDAY = 3
WEDNESDAY=4
THURSDAY= 5
FRIDAY = 6
SATURDAY = 7

  • Similarly you can check the quarter of the year by using one of the following query. SELECT DATEPART(quarter,'07/22/2007' ) or
    SELECT DATEPART(qq,'07/22/2007' ) or
    SELECT DATEPART(q,'07/22/2007' )
Result: 3 ( as this is 3rd quarter of the year)

You can get the details from MSDN online, Here is the reference of the MSDN:
MSDN ONLINE

Friday, July 20, 2007

Hibernate Plugin for Eclipse (HibernateSynchronizer)

Hibernate is used for database object/relational persistence and it is a very cool. It is available for both JAVA and .NET but is most popular for JAVA.

I was working with the Hibernate using eclipse. I was looking for some plugin of Eclipse and I found one with the name of "HibernateSynchronizer"

It is an Eclipse plugin code generation tool which, by default, binds to *.hbm files and automatically generates business objects when your hibernate schema configuration file is modified.

Here is the link to download it:
http://hibernatesynch.sourceforge.net/
http://sourceforge.net/project/showfiles.php?group_id=99370

String Functions @ SQL Server 2005

There are number of scalar function to manipulate the strings in SQL server. I will just discuss the most common used scalar functions available for string manipulation:

1- REVERSE
This scalar function is used to reverse the character string provided. The input parameter may be constant string, variable or column name of character or binary type.
EXAMPLE:- SELECT REVERSE('SABAH') , REVERSE('IRFAN')
RESULT:- HABAS NAFRI

2- LEN
It returns the number of characters in the input parameter provided. Input parameter may be constant char string, variable or column name. Please note it will not count the trailing spaces.
EXAMPLE:- SELECT LEN('SABAH U DIN') , LEN('IRFAN ')
RESULT:- 11 5

3-
SPACE
It returns the number of spaces according to the integer parameter provided as input. It takes integer_expression as input. if the integer expression is negative then it returns null string.
EXAMPLE:- SELECT 'Sabah' +SPACE(10)+'Irfan'
RESULT:- Sabah Irfan

4-
REPLACE
This function has three parameters all of character type. It finds the 2nd parameter_string from 1st_parameter string and replaces all occurrence with the 3rd parameter_string.
EXAMPLE:- SELECT REPLACE('abcdefghicde','cde','xxx')
RESULT:- abxxxfghixxx

5- ASCII
It returns the integer ASCII code of the left most character of the input string.
EXAMPLE:- SELECT ASCII('A'),ASCII('ABCDEF')
RESULT:- 65 65

6- CHAR
It converts the integer ASCII code into char.
EXAMPLE:- SELECT CHAR(65),CHAR(78)
RESULT:- A N

7-
LOWER
It returns the lower case character string after converting the upper case characters present into the input string.

EXAMPLE:- SELECT LOWER('SAbah U Din')
RESULT:- sabah u din

8- UPPER
It returns the upper case character string after converting the lower case characters present into the input string.

EXAMPLE:- SELECT UPPER('SAbah U Din')
RESULT:- SABAH U DIN

9- LEFT
It returns the left part of the string with the number of characters provided.
EXAMPLE:- SELECT LEFT('Sabah-u-Din',7)
RESULT:- Sabah-u

10- RIGHT
It returns the right part of the character string with the number/count of characters provided in its 2nd parameter.
EXAMPLE:- SELECT RIGHT('Sabah-u-Din',7)
RESULT:- h-u-Din

11- RTRIM
It
returns a character string after truncating all trailing blanks(blanks at the end of string).
EXAMPLE:- SELECT RTRIM('Sabah ')
RESULT:- Sabah

12- LTRIM
It
returns a character string after truncating all leading blanks(blanks at the start of string).
EXAMPLE:- SELECT LTRIM(' Sabah')
RESULT:- Sabah


13- CHARINDEX
It returns the starting position of the specified expression in a character string. Its syntax is
CHARINDEX ( expression1 ,expression2 [ , start_location ] )

EXAMPLE:- SELECT CHARINDEX('r','rSaba-u-din-irfan',3)
RESULT:- 14

NOTE: There are a couple of more scalar functions available in SQL Server but the above
are the mostly used ones.

Wednesday, July 11, 2007

Listing Specific Objects From the DB @ SQL Server 2005

There are always situations when you need to check/find the specific object like table/stored procedure from the database and usually for enterprize level applications there are hundreds or even thousands of user tables and stored procedures.

Following are some of the queries that will help to list the complete details of some specific
Object whose type you are providing in the WHERE clause. You can filter out the results as you needed just by adding couple of more conditions.

select * from sys.objects where type = 'D' -- DEFAULT_CONSTRAINT
select * from sys.objects where type = 'F' -- FOREIGN_KEY_CONSTRAINT
select * from sys.objects where type = 'FN'-- SQL_SCALAR_FUNCTION
select * from sys.objects where type = 'IT'-- INTERNAL_TABLE
select * from sys.objects where type = 'P' -- SQL_STORED_PROCEDURE
select * from sys.objects where type = 'PK'-- PRIMARY_KEY_CONSTRAINT
select * from sys.objects where type = 'S' -- SYSTEM_TABLE
select * from sys.objects where type = 'SQ'-- SERVICE_QUEUE
select * from sys.objects where type = 'U' -- USER_TABLE
select * from sys.objects where type = 'UQ'-- UNIQUE_CONSTRAINT

The following query will result you with the available types of Objects of the database:

select distinct(type) from sys.objects order by type asc

Monday, July 9, 2007

UDF to parse the numeric values from a String

This UDF will parse a string and will return the numeric values that are present in the string in the same sequence that they have in string.

CREATE FUNCTION dbo.UDF_ParseNumeric
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
END
SET @string = @string
RETURN @string
END

TEST:
SELECT dbo.UDF_ParseNumeric('san1as2,.?"< \{4}[]6>:')
Result:
1246

PATINDEX and STUFF @ SQL Server 2005

1- PATINDEX:-

It returns the starting index/position of the first occurrence of the pattern provided. If no match is found then it will return zero.
Syntax:
PATINDEX( '%pattern%', expression )

where
pattern:
is the pattern to find.
expression: is the string or usually the column name that will be
searched to find the specified pattern.
--------------------------------------------------------

2- STUFF:-

Deletes a specified length of characters and inserts another set of characters
at a specified starting point.

Syntax:

STUFF ( character_expression , start , length ,
character_expression )

where:
character_expression: can be a constant, variable, or column
of either character or binary data.
start: is the start location for the deletion and insertion.
It is an integer value. If its value is negative or
longer than character_expression then null string is
returned.
length: is the length of the character to be deleted.

Example:

SELECT STUFF('abcdefgh', 2, 3, 'SABAH')

Result: aSABAHefgh





Thursday, July 5, 2007

New Features in C# .NET 2.0

There are couple of new features/concepts that are introduced in the version 2.0 of C#.NET. These are listed below:
  1. Generics
  2. Partial Classes
  3. Iterators
  4. NULLABLE Value Types
  5. Anonymous Methods
  6. Coalesce operator: (??)
  7. Anonymous delegates providing closure functionality
  8. A yield return similar to yield in Python
Will explain one by one in different blogs for simplicity.

SQL Server @ WHERE vs HAVING Clause

WHERE vs HAVING Clause

Both are used to filter out the records but have coupe of differences:
  • HAVING can only be used with SELECT statement, while WHERE can be used for SELECT, UPDATE and DELETE.
  • HAVING specifies a search condition on group or aggregate function, while WHERE specifies the search condition on the individual rows.
  • HAVING is typically used with GROUP BY clause. If GROUP BY is not used then HAVING behaves like WHERE clause.

Tuesday, July 3, 2007

sp_rename @ SQL Server 2005

It Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

Syntax:

sp_rename @ObjectName, @ObjectNewName, @ObjectType

WHERE:

@ObjectName: is the name of the user object etc.
@ObjectNewName:
is the new name of the user object. It has no defaults.
@ObjectType:
defines the type of the user object to be renamed. The following types are supported:
  • A column
  • A database
  • An Index
  • UserDataType ( Alias type, objects created using CREATE statement and CLR user defined types)

Monday, July 2, 2007

CREATE STORED PROCEDURE And UDF WITH ENCRYPTION

You can create encrypted stored procedures, so that no one can see the source code of the stored procedure. Even if you will try to see its text using sp_helptext procedure; it will show the following message

The text for object '[sptst_Test]' is encrypted.

Syntax:
Just add WITH ENCRYPTION Clause after the parameter's list of the stored procedure or UDF.

Example:

CREATE PROCEDURE [dbo].[sptst_Test]
-- Add the parameters for the stored procedure here
WITH ENCRYPTION
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT * FROM sys.tables

END

NOTE:
  • You can not decrypt the Encrypted stored procedure or UDF. So it is good practice not to use this security. Rather use the user permissions if you need to restrict some one.
  • CLR UDF and Stored Procedures can not be Encrypted.


Replicate function in SQL Server 2005

The replicate function in SQL Server 2005 is used to replicate a specific value for a provided number of times. For example the following query,

select replicate('Sabah ',10) as [Replicate Test]

Results:

Sabah Sabah Sabah Sabah Sabah Sabah Sabah Sabah Sabah Sabah

Friday, June 29, 2007

A UDF to convert the seconds into MM:SS

A UDF to convert the seconds into MM:SS Format:

Here is the Code:


CREATE FUNCTION [dbo].[fn_SEC2MIN]( @Sec as int )
RETURNS VARCHAR (15)
AS
BEGIN

DECLARE @return AS VARCHAR (15)
DECLARE @i_MM as INT
DECLARE @i_SS as INT

IF ( @SEC >= 60 )
BEGIN
SET @i_MM = FLOOR(@Sec / 60 )
SET @i_SS = @Sec % 60
END
ELSE
BEGIN
SET @i_SS = @Sec
END
SET @return =right('00' + cast( @i_MM AS Varchar), 2 )+':'+ right('00' + cast( @i_SS AS Varchar), 2 )

RETURN @return
END

Here is the test query for the UDF:

print dbo.fn_SEC2MIN ( 120)

Phone Number Formatting UDF

The following UDF will give you the Formatted Phone Number:

CREATE FUNCTION [dbo].[fn_PhoneNoFormat]( @Phone as char(10) )
RETURNS VARCHAR (15)
AS
BEGIN

Return '('+substring(@phone,1,3)+') '+substring(@phone,4,3)+'-'+substring(@phone,7,4)

END

Here is the Test Query:

print dbo.fn_PhoneNoFormat( '1234567890')

UDF to Get the DD Part of the last Date of Month

-- =============================================
A UDF to get the DD part of the last date of the month according to the date passed into its parameter
-- =============================================
CREATE FUNCTION [dbo].[fn_DD_OF_Last_Date_OF_Month]( @myDate as datetime)
RETURNS INT
AS
BEGIN

DECLARE @return AS INT
SET @return =0
Declare @myDate2 datetime


SET @mydate2=dateadd(m,1,@myDate)-day(dateadd(m,1,@myDate))

SET @return= cast (datepart(dd,@myDate2) as int)

RETURN @return
END

The following query is to test the UDF:

select [dbo].[fn_DD_OF_Last_Date_OF_Month]( '03/13/2005')

Thursday, June 21, 2007

DELETE, TRUNCATE and DROP @ SQL Server

TRUNCATE TABLE:

Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is functionally the same as the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

       Advantages of TRUNCATE TABLE :

  1.              Less transaction log space is used.The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
  2.              Fewer locks are typically used. When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
  3.             Without exception, zero pages are left in the table.  After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

           Restrictions of TRUNCATE TABLE:

  1. TRUNCATE statements do not fire triggers.
  2. Records removed by the TRUNCATE TABLE statement cannot be restored.
  3. You can not specify a WHERE clause to narrow down the rows.
  4. you can not  retain the identity counter.
  5. You cannot specify a WHERE clause in a TRUNCATE TABLE statement. It is all or nothing.
  6. You can not usee TRUNCATE TABLE when:

  •                        Are referenced by a FOREIGN KEY constraint.
  •                       Participate in an indexed view.
  •                       Are published by using transactional replication or merge replication.

DELETE TABLE: 

  • DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as            maintaining log sequence number (LSN) information.  
  • You can also specify a WHERE clause to narrow down the rows to be deleted.
  • When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the            empty pages requiring manual release using DBCC SHRINKDATABASE (db_name). 
  •             If you need to retain the IDENTITY counter then se the DELETE statement.
  • When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following         statements can be used to achieve the same result as TRUNCATE TABLE:
DELETE from "table_name" 
DBCC CHECKIDENT("table_name", RESEED, "reseed_value")

DROP TABLE:
 To remove the table definition in addition to its data, use the DROP TABLE statement.
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain.