Undocumented SQL Server Commands

Some Useful Undocumented SQL Server 7.0 and 2000 DBCC Commands
SQL Server 2000 useful undocumented stored procedures
SQL Server Extended Stored Procedures

Constraints In SQL Server

Constraints: Rules defined on the table, which cannot be violated by the users. It provides a powerful yet easy way to enforce the data integrity in your database.

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.
Data integrity comes in the following forms:

1. Entity Integrity
2. Domain Integrity
3. Referential integrity
4. User-Defined Integrity

Entity Integrity ensures that there are no duplicate rows in a table.(i.e. Ensures that a table has a primary key).
Entity Integrity can be defined by defining

1. Primary Key Constraint
2. Unique Key Constraint
3. or by building an Unique Indexes

Domain Integrity ensures that data values meet certain criteria; i.e. by choosing an appropriate data type.

1. Other approaches include defining CHECK constraints or FOREIGN KEY constraints, or writing a trigger.
2. Default Constraint, Null Constraint, NOT null Constraint also an aspect of domain integrity.

Referential integrity Enforces relationships between two tables, a referenced table, and a referencing table. It ensures that rows cannot be deleted or updated in the referenced, when used in referencing table.

1. Foreign Key

User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories. Each of these categories of the data integrity can be enforced by the appropriate constraints.

Microsoft SQL Server supports the following constraints:

1. PRIMARY KEY
   
   • Composite Key or Compound key or Concatenated Key or Aggregate Key
   • Single-Column Key
   • Natural Key
   • Surrogate Key
   • Super Key
   • Candidate Key
   • Alternate Key or Secondary Key
2. UNIQUE
3. FOREIGN KEY
4. CHECK
5. NOT NULL

A PRIMARY KEY is a column or a combination of columns that uniquely identify a record. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity. NULL values are not allowed. A table can have only one primary key. A primary key adds a clustered index to the table The key can created for single column or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Not Null Constraint is automatically enforced. SQLPrimaryKeys

A Single-Column Key defined with an ever-increasing identity column.

Surrogate Key
It’s also Primary Key. If there no suitable natural key exists then we have to create surrogate key. It is a single column primary key that created automatically (example: Identity key column in SQL Server) or system generated values (like generated via a table in the schema).

Candidate Key
A column or a set of columns can be called as candidate key if they identify each row of a table uniquely. A table can have multiple candidate keys. One of them is specified as Primary key and rest of them can be called as alternate key.

Composite Key
A key formed by combining at least two or more columns is called Composite Key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.
Rules

1. At all times it holds in the relation assigned to that variable that there are no
   two distinct tuples (rows) with the same values for these attributes and
2. There is not a proper subset of this set of attributes for which (1) holds.

Natural Key
It’s also Primary Key. If it is reference by foreign key or by defining an unique index on it.

Super Key
A Super Key is a combination of attributes that can be uniquely used to identify a database record. A table might have many Super Keys. A primary key is therefore a minimum Super Key.

Alternate Key
A candidate key that is not the primary key is called an alternate key.

A UNIQUE constraint enforces the uniqueness of the values in a single column or a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints. Not Null Constraint is not automatically enforced. So it allows only one null value.

A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints. We can create constraints when the table is created, as part of the table definition by using the CREATE TABLE statement.

Default constraint is used to fill column with default value defined during creation of table if nothing is supplied while inserting data.

Super Key - Any combination of keys that make it unique.
Candidate Key - Minimum SK is called candidate key.
Primary Key - Any CK, which does not take null value.
Foreign Key - Reference to PK of another table with no nulls
Unique Key - Same feature is PK but can take null values. Also, in a table there can be many UK.

Any attribute that is uniquely identifying a row in a table is candidate key for the table. We select one of the candidate keys as Primary key.
All candidate keys, which are not chosen as primary key, are Alternate keys.

The key which uniquely identify the rows of the table and which is made up of more than one attribute is called Composite key. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key one of them will become the primary key and the rest are called alternate keys.

Reference
Natural vs. Surrogate Keys in SQL Server
Different Types of SQL Keys
Surrogate Keys, Natural Keys, Candidate Keys, Composite Keys and Super Keys?

Query to get PrimaryKey, ForeignKey, IdentityKey, UniqueKey column names

DECLARE  @TableName NVARCHAR(50)
SET @TableName = 'PkFkIkTestIdentAsPk'
 
 SELECT
  C.COLUMN_NAME,
  C.DATA_TYPE,
  C.ORDINAL_POSITION 
 FROM
  INFORMATION_SCHEMA.COLUMNS C 
  LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS COL
  ON col.TABLE_NAME = C.TABLE_NAME 
  AND col.COLUMN_NAME = C.COLUMN_NAME
  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblCons 
 ON tblCons.CONSTRAINT_NAME = col.CONSTRAINT_NAME 
 WHERE tblCons.TABLE_NAME = @TableName 
  AND 
  (
   tblCons.CONSTRAINT_TYPE = 'PRIMARY KEY' 
  OR tblCons.CONSTRAINT_TYPE = 'FOREIGN KEY' 
  OR tblCons.CONSTRAINT_TYPE = 'UNIQUE' 
  )
 UNION 
  SELECT C.COLUMN_NAME,
  C.DATA_TYPE,
  C.ORDINAL_POSITION
  FROM
  INFORMATION_SCHEMA.COLUMNS C
  WHERE C.TABLE_NAME = @TableName
  AND COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME,'IsIdentity') = 1

SQL SERVER DataTypes

To get the DataType information
SP_HELP DataTypeName

SELECT 'User_type' = name, 
       'Storage_type' = Type_name(xtype), 
       'Length(Size)' = length, 
       'Prec' = Typeproperty(name, 'precision'), 
       'Scale' = Typeproperty(name, 'scale'), 
       'Nullable' = CASE 
                      WHEN Typeproperty(name, 'AllowsNull') = 1 THEN 'Yes' 
                      ELSE 'No' 
                    END, 
       'Default_name' = Isnull(Object_name(tdefault), 'None'), 
       'Rule_name' = Isnull(Object_name(domain), 'None'), 
       'Collation' = collation 
FROM   systypes 
ORDER  BY name  

2008	2005	2000
Exact Numerics	Exact Numerics	Exact Numerics
bigint	bigint	bigint
numeric	numeric	numeric
bit	bit	bit
smallint	smallint	smallint
decimal	decimal	decimal
smallmoney	smallmoney	smallmoney
int	int	int
tinyint	tinyint	tinyint
money	money	money
Approximate Numerics	Approximate Numerics	Approximate Numerics
float	float	float
real	real	real
Date and Time	Date and Time	Date and Time
date
datetimeoffset
datetime2
smalldatetime	smalldatetime	smalldatetime
datetime	datetime	datetime
time
Character Strings	Character Strings	Character Strings
char	char	char
varchar	varchar	varchar
text	text	text
Unicode Character Strings	Unicode Character Strings	Unicode Character Strings
nchar	nchar	nchar
nvarchar	nvarchar	nvarchar
ntext	ntext	ntext
Binary Strings	Binary Strings	Binary Strings
binary	binary	binary
varbinary	varbinary	varbinary
image	image	image
Other Data Types	Other Data Types	Other Data Types
cursor	cursor	cursor
timestamp	timestamp	timestamp
hierarchyid
uniqueidentifier	uniqueidentifier	uniqueidentifier
sql_variant	sql_variant	sql_variant
xml	xml
table	table	table
2008	2005	2000

OutOfMemory Exception

The below code is used to merge multipage tiff with single page tiffs.

 private void CreatThumbnail(Image thumbNailImg, int iPagesCount)
        {
            try
            {
                //TRK
                //PictureBox[] picBoxArray = new PictureBox[iPagesCount];
                picBoxArray = new PictureBox[iPagesCount];
                PictureBox picBox = null;

                pnlThumbnail.Controls.Clear();

                
                int iWidth = 300;
                int iHeight = 300;

               
                if (!rbShowPageByPage.Checked && !rbShowBoth.Checked)
                {
                    //TRK - 01 
                    iWidth = 308;
                    iHeight = 550;
                    pnlThumbnail.Size = new Size(966, 560);

                    //iWidth = 500;
                    //iHeight = 550;
                    //pnlThumbnail.Size = new Size(966, 560);
                }
                else
                {
                    pnlThumbnail.Location = new Point(16, 110);
                    pnlThumbnail.Size = new Size(966, 131);
                }

                int iThumbCurrPage = 0;
                
                for (; iThumbCurrPage < iPagesCount; iThumbCurrPage++)
                {
                    //TRK
                    picBox = new PictureBox();
                    thumbNailImg.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, iThumbCurrPage);

                    using (Image myBmp = new Bitmap(thumbNailImg, iWidth, iHeight))
                    {
                        MemoryStream memoryStream = new MemoryStream();
                        myBmp.Save(memoryStream, System.Drawing.Imaging.ImageFormat.Tiff);
                        picBox.Image = Image.FromStream(memoryStream); // showing the page in the pictureBox1

                        myBmp.Dispose();
                        if (memoryStream != null)
                        {
                            memoryStream.Close();
                            memoryStream.Dispose();
                        }
                        GC.Collect();
                    }

                    picBox.Size = picBox.Image.Size;
                    picBox.Location = new Point(1 + (iThumbCurrPage * (iWidth + 10)), 1);
                    picBoxArray[iThumbCurrPage] = picBox;
                    picBoxArray[iThumbCurrPage].Name = Convert.ToString(iThumbCurrPage);
                    toolTipForControls.SetToolTip(picBoxArray[iThumbCurrPage], MessagesAndToolTips.ThumNailClick);

                    picBoxArray[iThumbCurrPage].Click += new System.EventHandler(this.ThumbNailPictureBox_Click);
                    pnlThumbnail.Controls.Add(picBoxArray[iThumbCurrPage]);

                    if (iThumbCurrPage == 0)
                        objCommonDeclarations.ShowMessage("Loading Page " + Convert.ToString(iThumbCurrPage + 1) + " Of " + Convert.ToString(iPagesCount), "INFO", lblShowMessage);
                    else
                        objCommonDeclarations.ShowMessage("Loading Next Page " + Convert.ToString(iThumbCurrPage + 1) + " Of " + Convert.ToString(iPagesCount), "INFO", lblShowMessage);
                    Application.DoEvents();

                    GC.Collect();
                }

                objCommonDeclarations.ShowMessage("Loading Last " + Convert.ToString(iThumbCurrPage) + " Of " + Convert.ToString(iPagesCount), "INFO", lblShowMessage);
                Application.DoEvents();

                objCommonDeclarations.ShowMessage(string.Empty, "INFO", lblShowMessage);
                Application.DoEvents();

            }
            catch (Exception ex)
            {
                objCommonDeclarations.ShowMessage(ex.Message.ToString(), "ERROR", lblShowMessage);
                objCommonDeclarations.WriteLog(ex.Message.ToString(), false, false);
            }
        }

 private void SwapImages()
        {
            try
            {
                int swapPage = Convert.ToInt32(txtPageNumber.Text.Trim()) - 1;

                //Open file in read only mode 
                using (FileStream fs = new FileStream(@lblFile.Text.Trim(), FileMode.Open, FileAccess.Read))
                {

                    string _sDestinatinPath = Path.Combine(CommonDeclarations.sDestinationPath, Path.GetFileName(@lblFile.Text.Trim()));
                    if (swapPage == 0)
                    {
                        using (Image bmp1 = Image.FromStream(fs))//TRK
                        {
                            bmp1.Save(_sDestinatinPath, ImageFormat.Tiff);

                            fs.Dispose();
                            bmp1.Dispose();
                            GC.Collect();
                        }
                        return;

                        //background
                        //return string.Empty;
                    }

                    ImageCodecInfo imageCodecInfo = GetEncoderInfo("image/tiff");

                    //Image bmp = Image.FromStream(fs);
                    using (Image bmp = Image.FromStream(fs))//TRK
                    {
                        //Bitmap bmp = new Bitmap(fs);

                        int frameCount = bmp.GetFrameCount(FrameDimension.Page);
                        bmp.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, swapPage);

                        //Image newTiff = new Bitmap(bmp, bmp.Width, bmp.Height);
                        //Image newTiff = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height));
                        using (Image newTiff = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height)))
                        {

                            EncoderParameters SaveEncoderParameters = new EncoderParameters(2);
                            System.Drawing.Imaging.Encoder SaveEncoder = System.Drawing.Imaging.Encoder.SaveFlag;
                            EncoderParameter CompressEncodeParam = new EncoderParameter(SaveEncoder, (long)(EncoderValue.MultiFrame));
                            SaveEncoderParameters.Param[0] = CompressEncodeParam;
                            SaveEncoder = System.Drawing.Imaging.Encoder.Compression;
                            CompressEncodeParam = new EncoderParameter(SaveEncoder, (long)(EncoderValue.CompressionCCITT4));
                            SaveEncoderParameters.Param[1] = CompressEncodeParam;


                            System.Drawing.Imaging.Encoder AddEncoder = System.Drawing.Imaging.Encoder.SaveFlag;
                            EncoderParameter AddEncodeParam = new EncoderParameter(AddEncoder, (long)EncoderValue.FrameDimensionPage);
                            System.Drawing.Imaging.Encoder AddCompressionEncoder = System.Drawing.Imaging.Encoder.Compression;
                            EncoderParameter AddCompressionEncodeParam = new EncoderParameter(AddCompressionEncoder, (long)EncoderValue.CompressionCCITT4);
                            EncoderParameters AddEncoderParams = new EncoderParameters(2);

                            AddEncoderParams.Param[0] = AddEncodeParam;
                            AddEncoderParams.Param[1] = AddCompressionEncodeParam;

                            ArrayList swap = new ArrayList();
                            for (int i = 0; i < frameCount; i++)
                            {
                                swap.Add(i);
                            }

                            swap.Insert(0, swap[swapPage]);
                            swap.RemoveAt(swapPage + 1);

                            int pageCount = 0;
                            for (; pageCount < frameCount; pageCount++)
                            {
                                switch (pageCount)
                                {
                                    case 0:

                                        newTiff.Save(_sDestinatinPath, imageCodecInfo, SaveEncoderParameters);
                                        break;
                                    default:

                                        bmp.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, Convert.ToInt32(swap[pageCount]));

                                        try
                                        {
                                            // Convert image to bitonal for saving to file
                                            using (Bitmap newPage = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height)))//TRK
                                            {
                                                newTiff.SaveAdd(newPage, AddEncoderParams);
                                                newPage.Dispose();
                                                GC.Collect();
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            throw ex;
                                        }
                                        finally
                                        {

                                            //GC.WaitForPendingFinalizers();
                                        }
                                        break;
                                }

                                ////Background
                                //objCommonDeclarations.ShowMessage("Reordering Images " + Convert.ToString(pageCount + 1) + " of " + Convert.ToString(frameCount), "INFO", lblShowMessage);
                                //Application.DoEvents();

                                GC.Collect();
                                //GC.WaitForPendingFinalizers();
                            }

                            //////Background
                            //objCommonDeclarations.ShowMessage("Reordering Images " + Convert.ToString(pageCount + 1) + " of " + Convert.ToString(frameCount), "INFO", lblShowMessage);
                            //Application.DoEvents();

                            //////Background
                            //objCommonDeclarations.ShowMessage(string.Empty, "INFO", lblShowMessage);
                            //Application.DoEvents();

                            AddEncoderParams.Param[0] = new EncoderParameter(AddEncoder, (long)EncoderValue.Flush);
                            newTiff.SaveAdd(AddEncoderParams);

                            newTiff.Dispose();
                            GC.Collect();
                        }

                        bmp.Dispose();
                        fs.Dispose();
                        GC.Collect();

                    }
                }
            }
            catch (Exception ex)
            {
                objCommonDeclarations.ShowMessage(ex.Message.ToString(), "ERROR", lblShowMessage);
                //Background
                //return ex.Message.ToString();
                objCommonDeclarations.WriteLog(ex.Message.ToString(), false, false);
            }

            //Background
            //return string.Empty;
        }


/// 

/// To Merge the Single Page Tifs in folder to a Multipage Tiff Image
/// 
/// This denotes Multipage Image Full Path/// This denotes Single Page Tif Image List/// After Merge where need to store the New Merged Tif Filesprivate bool MergeTifImages(String sMultiPageTif, List lstSingleTifs, String sDestinationPath)
        {
            String SinglePageTif = String.Empty;
            bool sflg = true;
            try
            {
                // To Check Destination Path is Exist or Not, if not create the path and folder structure
                if (!Directory.Exists(@sDestinationPath)) Directory.CreateDirectory(@sDestinationPath);
                //Open file in read only mode 
                using (FileStream fs = new FileStream(@sMultiPageTif, FileMode.Open, FileAccess.Read))
                {
                    //To Form the Destination Tiff File Name
                    String _sDestinatinPath = Path.Combine(@sDestinationPath, Path.GetFileName(@sMultiPageTif));
                    //To Create the Image Codec Info                   
                    ImageCodecInfo imageCodecInfo = GetEncoderInfo("image/tiff");

                    //To Create the New bmp Image from the existing Multipage Tif
                    //Image bmp = Image.FromStream(fs);
                    Image newTiff = null;
                    //To Select the Encoder and Compresstion formates for the Tif Images
                    EncoderParameters SaveEncoderParameters = new EncoderParameters(2);
                    System.Drawing.Imaging.Encoder SaveEncoder = System.Drawing.Imaging.Encoder.SaveFlag;
                    EncoderParameter CompressEncodeParam = new EncoderParameter(SaveEncoder, (long)(EncoderValue.MultiFrame));
                    SaveEncoderParameters.Param[0] = CompressEncodeParam;
                    SaveEncoder = System.Drawing.Imaging.Encoder.Compression;
                    CompressEncodeParam = new EncoderParameter(SaveEncoder, (long)(EncoderValue.CompressionCCITT4));
                    SaveEncoderParameters.Param[1] = CompressEncodeParam;

                    System.Drawing.Imaging.Encoder AddEncoder = System.Drawing.Imaging.Encoder.SaveFlag;
                    EncoderParameter AddEncodeParam = new EncoderParameter(AddEncoder, (long)EncoderValue.FrameDimensionPage);
                    System.Drawing.Imaging.Encoder AddCompressionEncoder = System.Drawing.Imaging.Encoder.Compression;
                    EncoderParameter AddCompressionEncodeParam = new EncoderParameter(AddCompressionEncoder, (long)EncoderValue.CompressionCCITT4);
                    EncoderParameters AddEncoderParams = new EncoderParameters(2);

                    AddEncoderParams.Param[0] = AddEncodeParam;
                    AddEncoderParams.Param[1] = AddCompressionEncodeParam;

                    using (Image bmp = Image.FromStream(fs))
                    {
                        //Get the Page Count, i.e Frame Count
                        int frameCount = bmp.GetFrameCount(FrameDimension.Page);

                        //To Create Temp New Tiff                 
                        newTiff = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height));

                        // To Save the Multipage Tiff
                        newTiff.Save(_sDestinatinPath, imageCodecInfo, SaveEncoderParameters);
                        Application.DoEvents();
                        //To Update frame by frame to the multipage
                        int pageCount = 0;
                        for (; pageCount < frameCount; pageCount++)
                        {
                            switch (pageCount)
                            {
                                case 0:

                                    newTiff.Save(_sDestinatinPath, imageCodecInfo, SaveEncoderParameters);
                                    break;
                                default:
                                    bmp.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, pageCount);
                                    // Convert image to bitonal for saving to file

                                    using (Bitmap newPage = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height)))
                                    {
                                        //Bitmap newPage = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height));
                                        newTiff.SaveAdd(newPage, AddEncoderParams);
                                    }

                                    break;
                            }
                            Application.DoEvents();
                        }
                    }

                    // To Merage the Single Page Tif Image to Multipage Tif Image form Single Tifs List
                    foreach (String SingleTif in lstSingleTifs)
                    {
                        try
                        {
                            CommonDeclarations.WriteLog("Single Page TIF: " + Path.GetFileName(SingleTif), false, false);
                            using (FileStream fs1 = new FileStream(@SingleTif, FileMode.Open, FileAccess.Read))
                            {
                                //To over come Out of memory.
                                //Image Singlebmp = Image.FromStream(fs1);
                                using (Image Singlebmp = Image.FromStream(fs1))
                                {
                                    Application.DoEvents();
                                    int frameCount = Singlebmp.GetFrameCount(FrameDimension.Page);
                                    //To Check the Given Single Page tif is having More than One Frame
                                    if (frameCount > 1)
                                    {
                                        CommonDeclarations.WriteLog("The Selected Single Page TIF Having More Then One Page. Page Count: " + frameCount, false, false);
                                        //This works if Multipage exist 
                                        int pageCount = 0;
                                        for (; pageCount < frameCount; pageCount++)
                                        {
                                            Singlebmp.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, pageCount);
                                            // Convert image to bitonal for saving to file
                                            Bitmap SinglePageTiff = Converter.ConvertToBitonal(new Bitmap(Singlebmp, Singlebmp.Width, Singlebmp.Height));
                                            newTiff.SaveAdd(SinglePageTiff, AddEncoderParams);
                                            //  break;
                                        }
                                    }
                                    else
                                    {
                                        //This will works while Single Frame of Single Tiff Image
                                        using (Bitmap tempBmp = new Bitmap(Singlebmp, Singlebmp.Width, Singlebmp.Height))
                                        {
                                            using (Bitmap SinglePageTifs = Converter.ConvertToBitonal(tempBmp))
                                            {
                                                //Bitmap SinglePageTifs = Converter.ConvertToBitonal(new Bitmap(Singlebmp, Singlebmp.Width, Singlebmp.Height));
                                                Application.DoEvents();
                                                newTiff.SaveAdd(SinglePageTifs, AddEncoderParams);

                                                SinglePageTifs.Dispose();
                                                GC.Collect();
                                            }
                                            tempBmp.Dispose();
                                            GC.Collect();
                                        }
                                    }

                                    Singlebmp.Dispose();
                                    GC.Collect();
                                }

                                fs1.Close();
                                fs1.Dispose();
                                GC.Collect();
                            }
                        }
                        catch (Exception ex)
                        {
                            CommonDeclarations.WriteLog(ex.Message.ToString(), false, false);
                            sflg = false;
                        }

                    }
                    //Save the New Tiff and Encode the Tiff  Images
                    AddEncoderParams.Param[0] = new EncoderParameter(AddEncoder, (long)EncoderValue.Flush);
                    newTiff.SaveAdd(AddEncoderParams);
                    newTiff.Dispose();
                    GC.Collect();

                }
            }
            catch (Exception ex)
            {
                //CommonDeclarations.ShowMessage(ex.Message.ToString(), "ERROR", lblShowMessage);
                CommonDeclarations.WriteLog("Some Error in MergeTifImages, Error: " + ex.Message.ToString(), false, false);
                sflg = false;
            }
            return sflg;
        }

        /// 

        /// To Get the ImageCodecInfo. For Example *.TIF,*.TIFF or *.JPG,*.BMP,*.GIF
        /// 
        /// This denotes MimeType. Kind of Imgage to Get the Image Files Type/// 
        private static ImageCodecInfo GetEncoderInfo(String mimeType)
        {
            int j;
            try
            {
                ImageCodecInfo[] encoders;
                encoders = ImageCodecInfo.GetImageEncoders();
                for (j = 0; j < encoders.Length; ++j)
                {
                    if (encoders[j].MimeType == mimeType)
                        return encoders[j];
                }
                return null;
            }
            catch (Exception ex)
            {
                CommonDeclarations.WriteLog("Some Error in ImageCodecInfo,Error:"+ ex.Message.ToString(), false, false);
                return null;
            }
        }

After running the application I looked how much memory the Proces use. Object.Dispose(); doesn't release the memory occuped by the object immediately. It shows constant memory increase when seeing. After using GC.Collect(); if frees the memory. Afterwares no OutOfMemory Exception thrown. Here we added each image to a panel(pnlThumbnail) pnlThumbnail.Controls.Add(picBoxArray[iThumbCurrPage]); This occupies more memory. We have to clear that in the following way.

//Clear the picture box stored in thumbnailArray
                if (picBoxArray != null)
                {
                    foreach (PictureBox tempPb in picBoxArray)
                    {
                        if (tempPb.Image != null)
                        {
                            tempPb.Image.Dispose();
                            tempPb.Image = null;

                            GC.Collect();
                        }
                    }
                    picBoxArray = null;
                }

                for (int clearPictureBox = 0; clearPictureBox < pnlThumbnail.Controls.Count; clearPictureBox++)
                {

                    PictureBox pb = pnlThumbnail.Controls[clearPictureBox] as PictureBox;

                    pnlThumbnail.Controls.Remove(pb);

                    if (pb.Image != null)
                    {
                        pb.Image.Dispose();
                        pb.Image = null;
                    }
                }
                
                if (pbShowTiffImage.Image != null)
                {
                    pbShowTiffImage.Image = null;
                    pbShowTiffImage.Invalidate();
                    GC.Collect();
                }
                                
                //The above one is better than this
                //foreach (IDisposable control in pnlThumbnail.Controls)
                //    control.Dispose(); 

                pnlThumbnail.Controls.Clear();
                GC.Collect();

REferences Tracking down managed memory leaks (how to find a GC leak) IDisposable.Dispose Method Detecting .NET application memory leaks memory leak with delegates and workflow foundation http://msdn.microsoft.com/en-us/magazine/cc163491.aspx Memory Leak Detection in .NET

Stored Procedure Generator for SQL SERVER

Download the tool


1. Get Sql Server Name.
2. Get Sql Server IP Address.
3. Load Server from local machine.
4. Load Server from your network.
5. Press the button load to load the Server Name or IP Address.
6. Press the button load to load the DataBase name for the the Selected Server Name or IP Address..
7. Save to File save the Procudure as .sql file.
8. Append The Scripts In SQLFile puts all the Procedure in a single .sql file.
9. Create New File For Each SP creates new .sql file with the name of the procedure.
10. Execute Script To Server, executes your procedure directly to your SQL server.
11. Overwrite the SP If Already Exists. If any procedure already exists it drops the procedure and creates a new one.
12. The left list box contails all the tables in your selected database.
13. >> button to move from left listbox to right for creating procedure for the table.
14. << botton to move from the right listbox to left if you don't want to create procedure for the table.
15. Select All To Move Right to select all tables in the left listbox for moving right.
16. Select All To Move Left to select all tables in the right listbox for moving left.
17. Click Create Scripts button to generate procedure according to the conditions.


Happy Coding.

SQL SERVER Parameter Directions

There are 4 types of parameter direction in SQL SERVER.

1. Input
2. Output or Out
3. InputOutput
4. Return

But we can specify only OUT or OUTPUT as parameter direction for the Procedure or Function.

RETURN at the last statement of the Procedure or Function and not in the parameter declaration of the Procedure or Function.

IN

DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID


ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT)
AS
BEGIN
SET @ParentID =10
SELECT @ParentID
END

We can’t able to specify in any where during the execution or in the SP parameter direction.

DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID IN –Not allowed

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT IN) -–Not allowed AS
BEGIN
SELECT @ParentID
END

We will get this error.
Incorrect syntax near the keyword 'in'.

OUTPUT
Output is not only for sending value back to the caller, it also accepts input from the caller. Since by default OUT implies both IN and OUT.

E.g. 1

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID = @ParentID + 5
END


DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID 

Result: 11

There is no need of RETURN or SELECT statement for the OUT direction parameter. Just assing the value, we will get the result.

If we didn’t assing a new value, we will get only the value we sent during execution of the Procedure or Function.(i.e. 6 for the above condion.)

If we didn’t sent any value during execution of the Procedure or Function we will get only nothing for the below condion.

DECLARE @ParentID int
exec [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID

E.g. 2

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID =10
SELECT @ParentID
END

No need to pass value. Just specify the keyword OUT near the parameter.

DECLARE @ParentID INT
EXEC [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID

Result: 10

For the above Procedure or Function we are assigning value to the parameter and also we are using SELECT statement. Here we will get a result-set and aslo the value assigned to our argument.

RETURN

There should be a RETURN statement at the end of the Procedure or Function.

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID =10
RETURN @ParentID
END

DECLARE @ParentID    INT
DECLARE @ReturnValue INT
SET @ParentID     =6
EXEC @ReturnValue = [dbo].[GetTestTable] @ParentID
PRINT @ReturnValue

If there is no RETURN statemnt at the end of the Procedure or Function, we will get only the default value of the RETURN argument datatype.

For example there is no RETURN statemnt for the above Procedure the result will be 0.

We can’t able to return a table variable from a RETURN statement.


Note:

1. By default OUT implies both IN and OUT.
2. Since our parameter is OUT we can pass value, since OUT implies both IN and OUT.
3. The default parameter direction is IN.
4. If the last statement is just RETURN without any value or a variable we will get
   nothing (if we didn’t pass value to the parameter of assign) or default value (if
   we assign value when passing). This is default for all the parameter direction.

These all are wrong.
@ReturnValue EXEC [dbo].[GetTestTable] @ParentID

Line 4: Incorrect syntax near '@ReturnValue'.

@ReturnValue = EXEC [dbo].[GetTestTable] @ParentID

Line 4: Incorrect syntax near '@ReturnValue'.

EXEC [dbo].[GetTestTable] @ParentID, @ReturnValue RETURN

Procedure or Function GetTestTable has too many arguments specified.