What is SET NOCOUNT in SQL Server

You must have seen the statement SET NOCOUNT ON in most of the stored procedure. When I saw this message first time I was thinking why this has been used in almost all stored procedure what will happen if I set SET NOCOUNT OFF in place of ON. Here in this article I thought I will share you the advantage of using SET NOCOUNT ON in SQL Stored Procedure and how this will improve the performance of your query.

The main advantage of using SET NOCOUNT ON in your stored procedure or TSQL statement is, this statement automatically suppress the message which shows how many rows has been affected by the query. Which means, this will reduce the overhead of the server and improves the performance when you execute query in a table which has got millions of data.

What exactly is happening inside the server is when you execute a stored procedure with SET NOCOUNT ON  it prevents the sending of DONE_IN_PROC messages to the client for each statement in your stored procedure. Imagine you have a loop or several statement has to be executed!!! So using SET NOCOUNT ON will definitely boost your query performance and adding the line SET NOCOUNT ON is one of the main DBA tasks when they find that query or procedure is not performing well.

If you wanted to know the message of affected rows then you can add SET NOCOUNT OFF in your query. This will show the number of rows affected by executing the query.

Irrespective of using SET NOCOUNT ON or SET NOCOUNT ON  SQL server internally updates @@ROWCOUNT function. So querying @@ROWCOUNT function will give you the affected rows details at any time.

Now let’s see how it shows in SQL query analyzer,

Below is my stored procedure, you can notice that I have commented the line SET NOCOUNT ON. 

CREATE PROCEDURE [dbo].[sp_GetCustomerList]

AS

BEGIN

    --SET NOCOUNT ON;

    SELECT * from Customer

END

We will execute this procedure and see the result.

Now we will un comment the line SET NOCOUNT ON and see the output,

CREATE PROCEDURE [dbo].[sp_GetCustomerList]

AS

BEGIN

    SET NOCOUNT ON;

    SELECT * from Customer

END

Hope you are now clear about the usage of SET NOCOUNT ON.

How to use NuGet Package Manager for Visual Studio 2013

Technorati Tags: How to use NuGet Package,How to install AjaxControlToolKit using NuGet Package Manager,Install AjaxControlToolKit for VisualStudio 2013

Have you ever heard about NuGet Package? Do you know how to use NuGet Package manager and how to install AJAXControlToolkit using NuGet Package Manager. Here is the answer for all your doubts related to NuGet Package manager for Visual Studio.

Pre-Requisite: System with Visual Studio 2013 and Good Internet Connection

First we will download NuGet Package manager for Visual Studio 2013. You may use the this link :NuGet Package Manager for Visual Studio 2013 for downloading the package.

Once you install the package open Visual Studio 2013 and open the Package Manager Console.

Refer below image to know how to open NuGet Package Manager Console.

Once you click on the Package Manager Console you will find a consol window link below

Here is the place you execute the command install AjaxControlToolkit. As I mentioned earlier you should have good internet connection to download the AjaxControlToolKit otherwise it will throw error.

The command you execute to install AjaxControlToolkit is :PM> Install-Package AjaxControlToolkit

Once you execute the command Install-Package AjaxControlToolkit it may take few seconds and you will below updates,

Finally when the installation completes you will find below updates,

There are many inbuilt commands available in NuGet you can find the help by running the command get-help NuGet and explore all possible options.

How to edit .rdlc file to add new column

I have a requirement to add new column in existing report. Unfortunately rdlc file was given by a third party vendor and we don’t have the complete project and dataset to edit the file.

As you are aware .rdlc is just an XML file, once you open that in notepad or any editor you can see what and all contents are visible in reports. But if you start editing the .rdlc file manually it will be a never ending process.

Here we are going to edit .rdlc file by using Visual Studio. Of course you require Visual Studio Business Intelligence to edit .rdlc file, also I am expecting you to have an access to the database to add new field in your stored procedure or SQL query which is executed from .rdlc to populate the data in reports.

Now we will go to step by step procedure.

1. Convert .rdlc to .rdl first—> This is a very simple technique. You just need to change the extension from .rdlc to .rdl. This will automatically convert the file.

2. Open Visual Studio business intelligence and select new—> Project—> Report project.

3. Right Click solution and select Add—>Existing Items—> Locate your renamed .rdl file.

 

4. Double Click .rdl file to open it in design mode.

5. Select the last field or the field near to the new one you are going to add. In this example I am adding the new column as the last one.

6. Right Click on the selected field and select Insert Column. You can opt either left or right depends on your choice.

7. Provide required name for the new fields. Here I am giving the name as Test.

8. Now we will add new field in the DataSet. Click on View menu and select report Data to show the dataset.

9. Right Click Dataset and select DataSet Properties.

6. Click on Add button to add new field in the DataSet.

7. And the last steps is to modify your Stored procedure/SQL query to add the new field you added in the reports.

8. Now it’s the time to convert .rdl to .rdlc. and it is exactly same as the firs step, just change the extension from .rdl to .rdlc.

Just copy the .rdlc file in your project folder where it has been placed earlier and access it from your application…That’s it!!!

If you have any doubt or confusion in above steps you can always contact me using my mail ID or write comment below.

Rename Stand-Alone Instance of SQL Server 2012 Database

Are you looking for an option to rename the SQL 2012 database? If you have an existing stand-alone instance of an SQL 2012 database you wanted to rename the database then this cannot be down from object explorer. When you right click on your database you will be surprised that Rename option will be disabled. Ok, don’t get panic we an do this renaming by running simple query.

Let’s how does it looks if you right click on your database in SQL Server 2012,

Since this is disabled we will use SQL query to rename the database.

USE master;

GO

ALTER DATABASE DemoRename

Modify Name = DemoRenameDB ;

GO

 

Now just refresh your Database and see the result,

How to use Ajax FilteredTextBoxExtender

Did you ever think of getting a ready made textbox with validation in ASP.NET? In this article I am going to explain you an Ajax control names FilteredTextBoxExtender. This control will help you to filter the user input without any additional script or coding.

Advantage of FilteredTextBoxExtender is there is not much configuration required. IT has only few properties.

1. TargetControlID—> You may specify the control you wanted to filter.

2. FilterType—> Here you mention what kind of filter you want to apply on your textbox control.

3. ValidChars—>As it’s name suggests you may enter the valid characters user supposed to enter in your textbox.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="FilteredTextbox.aspx.cs" Inherits="Blog.FilteredTextbox" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
        </ajaxToolkit:ToolkitScriptManager>
        <br />
        <asp:Label ID="Label1" runat="server" Text="Please Enter Only Numeric Value"></asp:Label>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <ajaxToolkit:FilteredTextBoxExtender ID="TextBox1_FilteredTextBoxExtender" runat="server" Enabled="True" TargetControlID="TextBox1" ValidChars="0123456789" FilterType="Numbers">
        </ajaxToolkit:FilteredTextBoxExtender>
    </div>
    </form>
</body>
</html>

Output will be like below,

FilterType="LowercaseLetters"

FilterType="UppercaseLetters"

FilterType="Numbers"

Hope you enjoyed this article. As usual your comments are always welcome.

How to insert comma separated value in a table using Stored procedure

Are you looking for a way to insert comma separated value in a table using SQL Stored Procedure? This article will help you to insert comma separated value in a different row. I thought I will write an article with full details because I used to see this question in Microsoft forums. 

Let’s start by creating a sample table first.

 

--Create table: 

CREATE TABLE tblNoOfAttempt(UserID INT IDENTITY,NoOfAttemp VARCHAR(50)) 

Since we have the table ready we will create the stored procedure to insert the comma separated value in our table.

 

-- Create Stored Procedure: 

Create PROCEDURE sp_InsertNoOfAttempt 

-- Add the parameters for the stored procedure here 

      @NoOfAttempt VARCHAR(100) 

AS 

BEGIN 

SET NOCOUNT ON; 

declare @XML xml 

SELECT @XML=CONVERT(xml,'<root><s>' + REPLACE(@NoOfAttempt,',','</s><s>') + '</s></root>') 

INSERT INTO tblNoOfAttempt 

SELECT [Value] = XM.SP.value('.','varchar(50)') 

FROM @XML.nodes('/root/s')XM(SP) 

END 

GO 

Now it’s the time to execute the stored procedure by passing some comma separated value.

-- Execute stored procedure by passing the comma separated value 
sp_InsertNoOfAttempt '8,7,10,9,5' 

Since the first field is an identity column we don’t need to pass the parameter for that. Value will be inserted automatically.

At last let’s see how the value is inserted in to he table by running select query.

-- Select the table values to see the out put 

select * from tblNoOfAttempt

Out put of the above query will be,

0x800a138f - JavaScript runtime error: Unable to get property 'UI' of undefined or null reference

You might be wondering what is this error and what you have to do to resolve “0x800a138f - JavaScript runtime error: Unable to get property 'UI' of undefined or null reference” error. Here is the answer for you.

Reason for this error: This error normally occurs when you use ScriptManager, like below, because most of the controls from AjaxControlToolKit work using Ajax script unfortunately many Ajax scripts are not updated in ScriptManager.

<asp:ScriptManager ID="ScriptManager1" runat="server">

        </asp:ScriptManager> 

Solution to resolve this error: You have to use ToolkitScriptManager instead of ScriptManager. ToolkitScriptManager adds most of the updated Ajax Script. Your code after changing must be like below,

<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">

 </asp:ToolkitScriptManager>

After changing to ToolkitScriptManager run your project and see the output.

Technorati Tags: 0x800a138f - JavaScript runtime error: Unable to get property 'UI' of undefined or null reference

The Report Server Installation Is Not Initialized (rsReportServer Not Activated)

Technorati Tags: The Report Server Installation Is Not Initialized (rsReportServer Not Activated)

This is one of the common error you may get when you rebuild or restore or upgrade your reporting server database and browse the report service manager URL. You may get the similar error when you browse the .rdl report also which was working earlier when your server was running fine.

To resolve this error you may follow one of the below steps,

Option 1:

• Open Reporting Services Configuration Manager
• Go to Encryption Keys
• Delete Encrypted Content
• Click on Delete

Option 2:

• Go to Reporting Services Configuration Manager.
• Go to "Encryption Keys" to backup the key.
• Navigate to "Database" tab.
• Click "Change Credentials" to reset the connections.

Now you will be able to browse the report without any issue.

How to find all Saturday and Sunday of the Current Month in MS SQL

I have seen many people asking the question “How to show all Saturdays and Sundays of the current month” in forums, so I thought I will try which is the best and easiest solution for this requirement.

Here I am going to use common table expression (CTE) to show the week ends with the date of the month. Below is the actual query you can use to find all the weekends of the current month with date.

WITH CTE(DATE, DayOfTheMonth)

AS

(

SELECT DATEADD(DAY, -DAY(GETDATE()-1), GETDATE()), 1

UNION ALL

SELECT DATE+1, DayOfTheMonth+1 FROM CTE WHERE DayOfTheMonth < (SELECT DAY(DATEADD(DAY, -DAY(DATEADD(MM, 1, GETDATE())), DATEADD(MM, 1, GETDATE()))))

)

SELECT DATENAME(WEEKDAY, DATE) AS WeekDay_Name, DayOfTheMonth INTO #TempDateTable FROM CTE OPTION(MAXRECURSION 30)

SELECT * FROM #TempDateTable WHERE WeekDay_Name IN('Saturday', 'Sunday')

DROP table #TempDateTable

When you execute above query you will get the out put as

WeekDay_Name	DayOfTheMonth
Saturday	3
Sunday	4
Saturday	10
Sunday	11
Saturday	17
Sunday	18
Saturday	24
Sunday	25
Saturday	31

Depends on the Month your DayOfTheMonth value will change. Since I am executing this in August 2013 it shows the records for that month.

List all tables in the SQL Database without Primary Key

Last week I was facing some issues with the application which I have been developing. Query was not working properly when somebody loaded huge test data in a table. when I checked the table I found that primary key was not created for that table even though it was not mentioned the database schema we have prepared. So I thought I will run a query to find out all the tables in the database which doesn’t have primary key.

Since I already created the query to find out all the tables without primary key in MS SQL database of course I have to share that with you also…

SELECT [schema_id] AS [Schema Name],[name] AS [Table Name], [type_desc] as [Table Type]

FROM sys.tables

WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0

Output of above query will be,

How to enable Script debugging in web browser?

In this article I am going to explain you how to debug JavaScript or how to enable Script Debugging.

I have seen many people posting question in forum asking I am getting “Java script rut time error” and how to resolve this issue. Interestingly they post whole JavaScript also for the solution. With my experience it is bit hard to find out the actual issue in JavaScript if you are not able to find the exact line you are getting this exception.

Many people still not aware that we can debug JavaScript and find out the exact line where we are getting the script error.

To find the exact line where the exception is showing first we will enable the Script debugging in web browser. In this example I am using Internet Explorer.

To enable the JavaScript debugging in Internet Explorer follow below steps.
Open IE-->Go to Tools-->Internet Options—>Select Advanced Tab—>

Scroll down to Browsing section-->Un Check "Disable Script debugging (Internet Explorer)" and Uncheck "Disable script debugging (Other)" and Check "Display a notification about every script error".

Click Ok to save the changes.

Now close your browser and open your web page and see the error message as pop up.

Click yes to open the script debugging and see in which line you are getting this error. Exact line you are getting this exception will be highlighted.

Now it will be very easy for you locate the line your script from your .aspx page.

Server Error in '/' Application, Maximum request length exceeded

Below is one of the common error developers get when they try to upload huge files using ASP.NET application.

Error description:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Maximum request length exceeded.
Description: An unhanded exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Maximum request length exceeded.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Reason:

users are trying to upload huge files which is more than the default size allowed by .NET Framework.

Solution:

Change below settings in web.config depends on the IIS version you are using.

For IIS6

<configuration>

    <system.web>

        <httpRuntime maxRequestLength="1048576" />

    </system.web>

</configuration>

For IIS7

<system.webServer>

   <security>

      <requestFiltering>

         <requestLimits maxAllowedContentLength="1073741824" />

      </requestFiltering>

   </security>

 </system.webServer>

You may change the values depends on the file size you want to allow the users to upload.

Try to run your application and upload some files!!!

Renewed Microsoft MVP award fourth time a row…

I am deeply honored and humbled to have been selected for the MVP (Microsoft Most Valuable Professional ) award 4th time a row, I would like to express my sincere thanks to all the DotNetSpider members, Tony John and all who supported me during this journey...

Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Incorrect syntax near ''

I have received this ‘Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Incorrect syntax near '<some Keyword>' error when I migrated my application from SQL server 2005 to SQL server 2008.

What happened is after migration, database compatibility was still set to previous version. So when we ran any SQL script which doesn’t support previous version will throw error.

To resolve this, you just need to login to the SQL server and change the compatibility level.

If you are getting this error while migrating the database from SQL 2000 to SQL 2005 then you may select the option “SQL Server 2005 (90)”

If you are getting this error while migrating the database from SQL 2005 to SQL 2008 then you may select the option “SQL Server 2008 (100)”

If you are a database admin then you can directly open the SQL query analyzer and execute below SQL query,

EXEC sp_dbcmptlevel 'DatabaseName', 90 ---This is for SQL 2005 Migration
EXEC sp_dbcmptlevel 'DatabaseName', 100 --–This is for SQL 2008 Migration

Steps to follow if you are doing it directly on server,

• Login to the database server.
• Locate the database you are getting this error
• Right click on the database and select Properties
• Go to “Options” Page
• Here you will find the option to change the “Compatibility Level”

Below screen print will give you the clear idea of all the options and what you need to change.

You are always welcome to post your comments below.

How to read PDF content using iTextSharp in .NET

“How to read PDF content using .NET?” is one of the very common questions you normally found in almost all Microsoft forum. Since I have been answering this question with sample code most of the time in I thought I will write a short article with detailed explanation.

Here I am going to use iTextSharp.dll to read the PDF file. iTextSharp is a C# port of iText, and open source Java library for PDF generation and manipulation. You can download the DLL from sourceforge.net using this download iTextSharp link.

Now we will start the .NET coding part to use the iTextSharp.

As this is a sample programe I am going to add only 3 controls. One FileUpload Control to locate/browse the PDF file, one button to show the content in a label and finally a label display the PDF content.

First we will see the PDF file and it’s content we are going to read.

No we will design our .ASPX page, as I mentioned above we have only three controls.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="Sample_2012_Web_App.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
        <asp:Label ID="Label1" runat="server" Text="Please select the PDF File"></asp:Label>
&nbsp;<asp:FileUpload ID="PDFFileUpload" runat="server" />
        <br />
        <br />
        <asp:Button ID="btnShowContent" runat="server" OnClick="btnShowContent_Click" Text="Show PDF Content" />
        <br />
        <br />
        <asp:Label ID="lblPdfContent" runat="server"></asp:Label>
    </form>
</body>
</html>

Below image shows you the interface we have created,

Now we will see the C# code to read the PDF content. Before start writing the code we need to add reference to the iTextSharp.dll. So from your solution explorer right click on the Reference and click on Browse button to locate the DLL file you have stored from the downloaded source code.

Once you add the reference we have to add the namespaces like below,

using iTextSharp.text.pdf;
using iTextSharp.text.pdf.parser;

Now we will see the complete source code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using iTextSharp.text.pdf;
using iTextSharp.text.pdf.parser;
using System.Text;
namespace Sample_2012_Web_App
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void btnShowContent_Click(object sender, EventArgs e)
        {
            if (PDFFileUpload.HasFile)
            {
                string strPDFFile = PDFFileUpload.FileName;
                PDFFileUpload.SaveAs(Server.MapPath(strPDFFile));
                StringBuilder strPdfContent = new StringBuilder();
                PdfReader reader = new PdfReader(Server.MapPath(strPDFFile));
                for (int i = 1; i <= reader.NumberOfPages; i++)
                {
                    ITextExtractionStrategy objExtractStrategy = new SimpleTextExtractionStrategy();
                    string strLineText = PdfTextExtractor.GetTextFromPage(reader, i, objExtractStrategy);
                    strLineText = Encoding.UTF8.GetString(ASCIIEncoding.Convert(Encoding.Default, Encoding.UTF8, Encoding.Default.GetBytes(strLineText)));
                    strPdfContent.Append(strLineText);
                    reader.Close();
                    strPdfContent.Append("<br/>");
                }
                lblPdfContent.Text = strPdfContent.ToString();
            }
        }
    }
}

Finally we will see the output.

As usual you are always welcome to post your comment below.

How to do animation in .NET using jQuery

In general everybody likes animation. Earlier animation in a page was a difficult tasks. Now after jQuery library animation can be done using two lines of code.

In this article I am going to explain you how to do animation in a webpage using jQuery.

First you have to download the jQuery library. Use the jQuery download page to download the library. If you don’t wanted to download you can add the reference directly as well. Here in this article I am directly referring the jQuery library.

Now we will see the complete code used for animation. I have tried to explain most of the details in the code itself and rest of the codes are self explanatory.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Animations.aspx.cs" Inherits="Sample_2012_Web_App.CustomerDetails" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>jQuery Animation Sample</title>
        <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
    </head>
<body>
    <button id="GoLeft">&laquo;Move Left</button>
    <button id="GoRight">&raquo;Move Right</button>
    <div class="block" style="position:absolute;background-color:#f00;left:100px;width:200px;height:100px;margin:20px;">This box is going to Animate</div>
    <script>
        $("#GoRight").click(function () {
            $(".block")
            .animate({ "left": "+=100px" }, "slow")//This code will move the box Right.
            .animate({ "height": "250px" }, 500)//This code will increase the height.
            .animate({ "width": "250px" }, 500)//This code will increase the width.
            .animate({ "opacity": "0.15" }, "slow")//This code will reduce the Opacity.
            .animate({ "opacity": "1" }, "slow")//This code will increase the Opacity.
            .animate({ "height": "100px" }, 500)//This code will reduce the increased height.
            .animate({ "width": "100px" }, 500);//This cide will reduce the increased width.
        });
        $("#GoLeft").click(function () {
            $(".block")
            .animate({ "left": "-=100px" }, "slow")
            .animate({ "height": "250px" }, 500)//This code will increase the height.
            .animate({ "width": "250px" }, 500)//This code will increase the width.
            .animate({ "opacity": "0.15" }, "slow")//This code will reduce the Opacity.
            .animate({ "opacity": "1" }, "slow")//This code will increase the Opacity.
            .animate({ "height": "100px" }, 500)//This code will reduce the increased height.
            .animate({ "width": "100px" }, 500);//This cide will reduce the increased width.
        });
</script>
</body>
</html>

When you look at the code you can see that I have written many animate code on click of the button. Actually that is the beauty of jQuery. Add as many animation code and make it interesting.

There are many more animation options available but I have limited here with 6 or 7 options.

Below is the interface I have created for animation.

Let me know your feed back after trying this feature in jQuery

Techweets–MVPs from South Asia region will be on Twitter on Friday

 

I am very excited to blog this event. All south Asia MVP’s are on twitter on all Fridays from 9 AM to 9PM.

IF you are looking for tips and techniques from Microsoft Most Valuable Professionals then be on twitter.

Here is the official announcement: Techweets