Difference between != NULL and IS NOT NULL

When you deal with NULL in SQL Server you basically work with 3-value logic with all the implications.

IF(@myVar != null)  vs  IF(@myVar is not null)

It basically boils down to the question

what is the difference between: @myVar = null vs @myVar is null

@myVar = null will always evaluate to null as what you are asking is: is the value in @myVar equal to UNKNOWN

As you do not know what the UNKNOWN is this question cannot by answered yes, or no so it evaluates to UNKNOWN

e.g.
    "is 1 = UNKNOWN" - I do not know
    "is 'a' = UNKNOWN" - I do not know
    "is UNKNOWN = UNKNOWN" - I do not know

The last one may be a bit tricky but just imagine that you have 2 boxes with candy and you do not know neither how many candy are in box1 one nor in box2 so asking.

so the answer is I do not know

the second one @myVar is null is different as it is like asking is the value in @myVar UNKNOWN

so the difference is that you specifically ask "is it true that the value stored in the variable is UNKNOWN?", so

    "is 1 UNKNOWN" - NO
    "is 'a' UNKNOWN" - NO
    "is UNKNOWN UNKNOWN" - YES

THX,

RS

Notepad in C#

Hi,
I have developed a small notepad in C# windows application. In this sample notepad i have given New, Open, Save, Save As, Cut, Copy, Paste, Undo,  Select All, Find, Word-wrap, Font and Color functionality.


Here I am attaching a zip file of the source code. Download Here


Thx,
RS

Call jquery funtion when it is exist.

Some times we need to call a function after chceking that it is exist there or not. This is very simple in jquery. Use that function name in if and if is exist then it will go in if block.


Example : 
function temp(){
  // Sample Code
}

if(temp){
  temp();
}


Like this temp function execute only when it is awailable.

Update XML Node value in SQL-Server 2008

Sometimes we need to change the value of a node in Sql-Server. Here is the example by which we can update the xml node value


Example : 



DECLARE @StrXml AS XML = '<BooksHome><Books>1</Books><Books>2</Books><Books>3</Books><Books>4</Books><Books>5</Books><Books>6</Books></BooksHome>'


DECLARE @i INT = 1
DECLARE @j INT = 141


SET @StrXml.modify('replace value of (//BooksHome/Books[sql:variable("@i")]/text())[1] with sql:variable("@j")')


we can see the updated result as :


SELECT @StrXml

Count of Nodes in XML through Sql-Server

Hi,


Here is the example by which we can count that how many times a particular node is coming.



DECLARE @strXml XML = (
'<Parent>
<Child>1</Child>
<Child>2</Child>
<Child>3</Child>
<Child>4</Child>
<Child>5</Child>
<Child>6</Child>
<Child>7</Child>
</Parent>'
) 
DECLARE @cnt INT 
SET @cnt = 
(
SELECT
COUNT(*) AS NodeCount
FROM
(SELECT @strXml) AS XmlTable(XmlColumn)
CROSS APPLY 
XmlColumn.nodes('/Parent/Child') XmlTableFunction(XmlColumn2)
)

Chaning extension of a file

This is very usefull code for me. This code I was needed when my solution was migrated from VS2008 to VS2010. In VS2008 we are using the naming convention for our function and SP's like this : dbo.<function_name>.sql and dbo.<SP_name>.sql but in VS2010 we are using <function_name>.function.sql  and <SP_name>.proc.sql. I want to commit som 100+ files from VS2008 t0 VS2010 so I uses below code which changes the file names.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace ConsoleApplication1
{
    class Program
    {
       static void Main()
        {
            //Path where all the files are stored.
            string[] filePaths = Directory.GetFiles(@"E:\test\");
            for(int i = 0;i<filePaths.Length;i++)
            {
                string oldFileName=filePaths[i].ToString();
                //This will remove the prefix and suffix name of the file
                string fileName = oldFileName.Substring(0,oldFileName.LastIndexOf("."));
                //This will add the suffix name of the file
                string NewFileName=@"E:\test\"+(fileName.Substring(fileName.LastIndexOf(".")+1))+".function.sql";
                //It creates the copy of the file
                File.Copy(oldFileName, NewFileName);
                //It deletes the old file
                File.Delete(oldFileName);
            }
        
        }
    }
}

Allow only numeric character in JavaScript

Hi,


Here is the example code of Javascript which allows you to type only numeric value in a textbox.


Here is the features of this code :

• It allows only numeric value with decimal point.
• It allows only one decimal point in a textbox.
• It allows only two character after decimal point.

$("#txtExample").keydown(function (event) {
var a = $("#txtWeightChangePercent").val();
var len = '';
if ((a.indexOf('.') - 1) == 0)
len = a.length - a.indexOf('.') - 1;
else
len = a.length + (a.indexOf('.') - 1);

if (len > 1 && event.keyCode != 8) {
event.preventDefault();
}

if (event.keyCode == 46 || event.keyCode == 8 || event.keyCode == 190 || event.keyCode == 110) {
if ((a.split(".").length - 1) > 0 && event.keyCode != 8) {
event.preventDefault();
}
// Some thing goes here
}
else {
// Ensure that it is a number and stop the keypress
if ((event.keyCode < 48 || event.keyCode > 57) && (event.keyCode < 96 || event.keyCode > 105) && (event.keyCode <= 42 || event.keyCode >= 40) && (event.keyCode <= 33 || event.keyCode >= 45)) {
event.preventDefault();
}
}
});


If you don't want to allow decimal also then remove this "|| event.keyCode == 190 || event.keyCode == 110" 
Thx,
RS

Ranking Function

We have four types of ranking function in sql server


1.) row_number
2.) rank
3.) dense_rank


Row_Number() Function


The ROW_NUMBER() function generates the auto incrementing integer number to the sort order of the OVER() clause


it is used like this :


SELECT
       ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS RowNumber
       ,SalesOrderID
       ,Sales
FROM
       SalesOrder




Rank() and Dense_rank() Function


RANK() and DENSE_RANK() function return values as if the rows were computing according to the sortorder.
For example if two record have the same sortorder value then both have same rank value. but next value in rank function will be +2 and in denseRank function it will be +1.


it is used like this :


SELECT
      RANK() OVER(ORDER BY SalesOrderID) AS RANK
      DENSE_RANK() OVER(ORDER BY SalesOrderID) AS DENSE_RANK
     ,SalesOrderID
     ,Sales
FROM
     SalesOrder

Connecting BIDS or Visual Studio 2008 with TFS 2010

In my project I am working on Visual Studio 2010 and I am using TFS 2010. For SSRS we have to use VS 2008 Or BIDS. But TFS 2010 does not allow us to directly chekin VS 2008 or BIDS Files in our solution. For check in those files we have to perform following step.

1. Install Visual Studio 2008. 
2. Install Visual Studio Team explorer. Download Here
3. Install Visual Studio 2008 SP1. Download Here
4. Install Visual Studio 2008 SP1 Forward compatibility Update For TFS 2010. Download Here

Make Sure all these things should install in the mention order.


After these installation we have to give the server ip address for tfs like this .
http://[ip address]:[portnumber]/[tfs]/[]folddername]

Getting Hard Disk Serial No. in C#

Every hard disk has a unique serial number which we can get by our code here is a example which gives you the you computer's c drive serial number.

using System.Management;

public string GetHDDSerialNumber(string drive)

        {

            //check to see if the user provided a drive letter

            //if not default it to "C"

            if (drive == "" || drive == null)

            {

                drive = "C";

            }

            //create our ManagementObject, passing it the drive letter to the

            //DevideID using WQL

            ManagementObject disk = new ManagementObject("win32_logicaldisk.deviceid=\"" + drive + ":\"");

            //bind our management object

            disk.Get();

            //return the serial number

            return disk["VolumeSerialNumber"].ToString();

        }

Replacing Tab key functionality with Enter Key C#

Sometime we need to replace the Enter key functionality with the tab key here is a sample which can help you :

public static void EnterTab(System.Windows.Forms.KeyPressEventArgs e)

{

   if (e.KeyChar == 13)

   {

     System.Windows.Forms.SendKeys.Send("{tab}");

   }

}

Manipulate Header and Footer values at each page

Generally in SSRS Header and Footer value is same for a report. Sometime we require to show different value in the footer or header on the bases of database of each page of SSRS.
For acheiving this we have to refer textbox name which is in report area or if it is not there we have to create a text box and give that value which we want to show in header or footer then we can hide it. Refer Text Box Name AS : ReportItems!Textbox208.value
Now in Header or footer for text box’s expression we can use = ReportItems!Textbox208.value.


With the help of this we can get different value in header and footer at each page of report.

Creating Connection String Dynamically

For creating the connection string dynamically first you create a notepad file and save it at desktop with the extension .udl

For Example:

Right Click on desk top and New -> Text Document

Go to File-> Save As. Save this file with extension .udl

Now Just Double Click on your udl file. And select provider

Select Microsoft OLEDB Provider for SQLServer and click on Next Button;

Now in Give the server name for your machine you can give '.' or your computer name.

Submit user name & password of your database, Select the Database Name Check the connection with click on test connection and then click on ok. Now open that udl file in notepad you an find connection string in that. 

Retrieve the Image From the Database

If we want to retrive the image from the database then first we pick the image from the database and then convert it into the image :

Here I am store the image in to database in an byte array now I am converting that byte array in to an image :

Image MyImage = ConvertImage(imageData);

public static Image ConvertImage(object value)

{

     byte[] img;

     Image newImage;

     img = (byte[])value;

     //Read image data into a memory stream

     using (MemoryStream ms = new MemoryStream(img, 0, img.Length))

     {

        ms.Write(img, 0, img.Length);

        //Set image variable value using memory stream.

        newImage = Image.FromStream(ms, true);

     }

     return newImage;

}

picturebox1.Image = MyImage;

Save the Image in to database

Here is a code which we can use for storing the image into database for this we can convert the image into a byte array and then store that array in to data base. The method is as follows:

byte[] imageData = CreateImage.convertPicBoxImageToByte(pb1);

public static byte[] convertPicBoxImageToByte(System.Windows.Forms.PictureBox pbImage)

{

    System.IO.MemoryStream ms = new System.IO.MemoryStream();

    pbImage.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

    return ms.ToArray();

}

Now save that imageData variable in database as a normal way.

Sql directives order

When we are optimizing the SQL statements to improve the performance I thought this tip would help the figure out where to tweak their code.

This is how the order in which sql directives (clauses) get executed when you write a select statement.

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

Insert Multiple records into a SQL-Server Database

To insert multiple records into a SQL Server database, we need to call the INSERT INTO  query every time.
Suppose if we want to insert 10 records, we need to call INSERT INTO statement 10 times.
As every time we invoke the INSERT INTO statement the INSERT INTO statements are repeated multiple times.
The work around is to use one Single INSERT INTO statement in SQL Server.
There are two methods Available to Insert Multiple Records in Single INSERT INTO Statement


1. Using UNION ALL Keyword
2. SQL Server 2008 Row Construction Method


Assume the database TEST_DB contains a table called Student with the following fields:


1. StdID
2. First Name
3. Last Name
4. Country
5. State
6. ZipCode


To Insert 5 records we have to call INSERT INTO 5 times as follows:


MULTIPLE INSERT STATEMENTS


USE Test_DB
GO


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1001, 'Student1','User1', 'INDIA','Bhopal','462001')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1002, 'Student2','User2', 'INDIA','Bhopal','462001')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1003, 'Student3','User3', 'INDIA','Banglore','560068')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1004, 'Student4','User4', 'INDIA','Banglore','560068')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1005, 'Student5','User5', 'INDIA','Banglore','560068')


GO


1). USING UNION ALL


The same result can be accomplished using the following Code.


USE Test_DB
GO


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
SELECT 1001, 'Student1','User1', 'INDIA','Bhopal','462001'
UNION ALL
SELECT 1002, 'Student2','User2', 'INDIA','Bhopal','462001'
UNION ALL
SELECT 1003, 'Student3','User3', 'INDIA','Banglore','560068'
UNION ALL
SELECT 1004, 'Student4','User4', 'INDIA','Banglore','560068'
UNION ALL
SELECT 1005, 'Student5','User5', 'INDIA','Banglore','560068'


GO


2). USING SQL Server 2008 Row Construction Method


USE TEST_DB
GO


INSERT INTO Employee  (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
VALUES (1001, 'Student1','User1', 'INDIA','Bhopal','462001'),
(1002, 'Student2','User2', 'INDIA','Bhopal','462001'),
(1003, 'Student3','User3', 'INDIA','Banglore','560068'),
(1004, 'Student4','User4', 'INDIA','Banglore','560068'),
(1005, 'Student5','User5', 'INDIA','Banglore','560068')

Find Duplicate Entry

In this way we can find duplicate entry in the database


-- create table for test data
create table test_data (
id int,
product char(10)
)


-- insert test data


insert into test_data values(1,'item 1')
insert into test_data values(2,'item 2')
insert into test_data values(3,'item 3')
insert into test_data values(3,'item 4')
insert into test_data values(5,'item 5')
insert into test_data values(5,'item 6')
insert into test_data values(5,'item 7')
insert into test_data values(8,'item 8')


-- find duplicate id
select id, count(*) #_of_dups
from test_data
group by id
having count(*) > 1


-- drop table test_data
drop table test_data

Detail members can only contain static inner members

Hi,
'The tablix 'Table1' has a detail member with inner members. Detail members can only contain static inner members. 

This is the one of the generic Error in SSRS 2008. This error will come when we are putting a table inside another table and forget to gives the group property of a parent table. It is always coming when we are having a table inside another table and if parent table does not have any group. So whenever we are using a table in side another table that time it is necessary to give the group property to the parent table.

Age Calculation

Hi,
In most of the project we need to calculate the age. Consider below TSQL to calculate age, it should be used where age needs to be displayed.

SELECT CAST((DATEDIFF(dd, <date_of_birth>, GETDATE())/365.25) AS INT)

Calendar In Sql Server Reporting Services.

In SSRS there is no any option by which we can create the calender in the reports but this thing can be done using SQL-Server here I am giving a example code of creating a calender for a month

For this we have to write the some sql queries in SSRS Data Source

The query is as follows :

--First day of current month

DECLARE @StartDate datetime = '01/01/2011'

DECLARE @EndDate datetime= '01/31/2011'

SET @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))

DECLARE @CurrentMonth VARCHAR(50)

SET @CurrentMonth = CAST(DATENAME(MONTH, @StartDate) AS VARCHAR)+' '+ CAST(DATENAME(YEAR, @StartDate) AS VARCHAR)

--First day to display on calendar

SET @StartDate = DATEADD(DAY,-DATEPART(WEEKDAY,@StartDate)+1,@StartDate)

--Last day of month

SET @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0))

--Last day to display on calendar

SET @EndDate = DATEADD(DAY,6-DATEPART(WEEKDAY,@EndDate),@EndDate)

WITH

Dates([DATE]) AS

(

--Select First day in range

SELECT

CONVERT(DATETIME,@StartDate) AS [DATE]

UNION ALL

--Add a record for every day in the range

SELECT

DATEADD(DAY, 1, [DATE])

FROM

Dates

WHERE

DATE < CONVERT(DATETIME,@EndDate)

), Events AS

(

--Create some events to place on the calendar

SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, -1,GETDATE()),101) + '/30/2009 02:00:00 PM', Note = 'Event 1'

UNION

SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/23/2009 12:00:00 PM', Note = 'Event 2'

UNION

SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2009 02:00:00 PM', Note = 'Event 3'

UNION

SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2009 06:30:00 PM', Note = 'Event 4'

UNION

SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/30/2009 07:00:00 PM', Note = 'Event 5'

UNION

SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, 1,GETDATE()),101) + '/01/2009 01:30:00 PM', Note = 'Event 6'

)

SELECT

-- Number the records based on the date, if multiple records have

-- the same date then they will be numbered the same. Used in

-- calculation to determine row record is to display on.

[ORDER] = DENSE_RANK() OVER (ORDER BY d.[Date]),

-- date used in all caluclation for date

d.[Date],

--generates matrix columns

[WeekDay] = DATEPART(WEEKDAY, d.[Date]),

--used to display day of month on calendar

[DAY] = DATEPART(DAY,d.[Date]),

--used in some calculations for display

[MONTH] = DATEPART(MONTH,d.[Date])

FROM

Dates d

LEFT JOIN

Events e

ON

CAST(CONVERT(VARCHAR(10),e.EventDate,101) AS DATETIME) = d.[Date]

--Set the maximum times the Dates cte can recurse

OPTION (MAXRECURSION 100)

After writing this query we have to follow these steps :

1. Right Click on the report page and select matrix table.
2. Right Click on the row group property of this table and give group on value as “=Ceiling(Fields!RowOrder.Value / 7)”
3. Give the width for first column as “0.0inch” and set the visible property of that column as false.
4. For second column right click on Column group property and givr group on value as = “=Fields!WeekDay.Value”
5. Now For the First row in text box write the following code
   =IIF(Fields!WeekDay.Value = 1,"Sunday",
   IIF(Fields!WeekDay.Value = 2,"Monday",
   IIF(Fields!WeekDay.Value = 3,"Tuesday",
   IIF(Fields!WeekDay.Value = 4,"Wednesday",
   IIF(Fields!WeekDay.Value = 5,"Thrusday",
   IIF(Fields!WeekDay.Value = 6,"Friday","Saturday"))))))
6. In second row write the following Code :
   =Fields!Day.Value
   
   
   Now Run the report.