Text search in Stored Procedures in SQL Server 2005 and 2008

In this ms sql server tutorial we will discuss Text search in Stored Procedures in SQL Server 2005 and 2008. There are many ways to accomplish this task. Lets have a look on those methods.

Method 1:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%FOO%' 
AND ROUTINE_TYPE='PROCEDURE'

Method 2:

SELECT OBJECT_NAME(ID) 
FROM SYSCOMMENTS 
WHERE [TEXT] LIKE '%FOO%' 
AND OBJECTPROPERTY(ID, 'ISPROCEDURE') = 1 
GROUP BY OBJECT_NAME(ID)

Method 3:

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%FOO%' 

Remember routine_definition is cropped at 4000 chars if you have a long stored procedure. sys.sql_modules doesn't have such restriction.

So that's it. Hopefully you will find this tutorial very handy.

Read more...

Optimize query to count total number of records in a table in ms sql server

In this ms sql server tutorial we will learn how to count total number of records (rows) in a table. Many people use given below query for this purpose

SELECT COUNT(*) AS ROWS FROM MY_TABLE

Let me tell you this query is really a killer query if table contains records more than one lac and worst case is more than one million because this query scans the whole table right from first row to last row.

Now what should we do? What is the optimize query? These are the questions might be coming in your mind, ok, below given is the optimized query that you guys must use for counting records purpose.

SELECT ROWS FROM SYSINDEXES WHERE ID = OBJECT_ID('MY_TABLE') AND INDID < 2

That's it. Hopefully you will find this tutorial very helpful. I love to hear your feedback.

Read more...

Create Fake Query in LINQ to avoid unknown return type error while drag and drop sp in dbml

In this tutorial you will learn how to create Fake Query in LINQ to avoid unknown return type error while drag and drop sp in dbml. Now many of you will be thinking what does it means? Let me explain you, using linq when you drag and drop stored procedure in dbml file and get following alert then it means LINQ is unable to create class of your stored procedure in designer.cs (dbml).

When you can face this problem?

• When you will be selecting columns based on conditions, means in one condition you are getting five columns and in other conditions you are getting seven columns
• When you will use temporary tables in stored procedure.
• In string based stored procedure.

Note:-
You can face this problem only in those SPs in which you are retrieving records. So if LINQ will not generate class of your stored procedure then it means you cannot retrieve records.

Steps to get rid of this problem and to create fake query

• After getting aforementioned error, you have to delete stored procedure from your dbml file.
• You have to alter SP, comment whole code written in SP, write fake query, in that fake query you have to mention all columns that you want to retrieve in SELECT command in a way illustrated in following picture

Original SP



Comment the original SP, Write Fake Query and Alter the SP

Execute the SP, it will give you result
Once SP is altered with fake query, drag and drop SP again in dbml file, now this time you will not get any error and class of the SP will be created in designer.cs. In the last you have to alter the SP again but this time you will have to revert the whole SP, comment the Fake Query code and bring back the SP into its original state that it has before the Fake Query. After this alteration in SP there is no need to drag and drop it again.



There is one more thing that I will need to discuss with you guys in next post about fake query which is very important. So stay tuned. So that's it. Cheers!!!!

Read more...

Unable to work on visual studio after windows update installed, visual studio close automatically

Hay guys I faced a problem couple of days ago and then I thought it may come to you guys too so that’s why I am publishing this post. Basically when I installed windows 7 in my system and then installed visual studio 2012 in it. Everything was going smoothly and then interval came :) I installed the windows update and when I tried to open the project in visual studio then it was giving me nasty error and closed automatically.

It was very strange situation for me, I quickly came to a point that it happen due to installation of windows update, I goggled my problem and found solution. Solution is pretty simple, just have to install updates of Visual Studio 2012 which is Microsoft Visual Studio 2012 (KB2781514). I went to this link and download the aforementioned visual studio update. Basically that updates contain patch which is patch_KB2781514.exe, you just have to run the exe and once patch successfully install then open the visual studio and do your work :) No need to restart the system.

Read more...

Split string by multiple character delimiter in asp.net

In this article you will learn how to split string by multiple character delimiter in asp.net. Well it is very simple and we can do it by using the built-in split () function of asp.net. Let's have a look on its implementation.

 
        string actualURL = "http://mywebsite.com/aboutus.aspx/images/test.aspx";
        string[] parts1 = actualURL.Split(new string[] { ".aspx" }, StringSplitOptions.None);

So that's it. In this example we have a wrong url containing two pages name and we have to get the first page so we have split the url by ".aspx" delimeter and store the result in parts1 array. 

Another method to do this task is by using Regex

 
string[] parts2 = Regex.Split(actualURL, @".aspx");

Note: Make sure to use using System.Text.RegularExpressions for using Regex.

Happy Coding!!!

Read more...

Calling one stored procedure within another stored procedure in sql

In this article we will learn how to call one stored procedure within another stored procedure in sql. Its quite easier and you don't have to bear any pain to do this. Lets have a look into example given below

declare @studentid bigint
declare @studentname varchar(50) 
set @studentid=20832083
exec @studentname = Web_Proc_GetStudentName @studentid -- use comma to separate multiple parameters 

exec @studentname = Web_Proc_GetStudentName @studentid

The above mentioned line of code is assigning return value of a stored procedure to @studentname variable. So folks that's it. Hope so this article will be proved handy for you.

Stay tuned for more useful tutorials.

Read more...

Get page load time in asp.net using c#

In this article, i will tell you guys how to get page load time. I have a web form that name is getloadtime.aspx, lets suppose there is lot of code written in it due to that its loading speed is very slow, to calculate the load time following code snippet will be used.

getloadtime.aspx.cs

    DateTime ServerStartTime;
    DateTime ServerEndTime;

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected override void OnPreInit(EventArgs e)
    {
        ServerStartTime = DateTime.Now;
        base.OnPreInit(e);
    }

    protected override void OnLoadComplete(EventArgs e)
    {

        ServerEndTime = DateTime.Now;
        TrackPageTime();//It will give you page load time
    }


    public void TrackPageTime()
    {
        TimeSpan serverTimeDiff = ServerEndTime.Subtract(ServerStartTime);
    }

I hope this article will be proved very handy for you people. Enjoy your work, enjoy coding.

Read more...

Problem while exporting numeric data from ms sql server 2008 to excel

In this article i am discussing a problem with you guys that i faced today. Today i run a query in ms sql server 2008 management studio, query was basically giving the following records from student table

1. Registration_Number
2. First_Name
3. Last_Name
4. Email_Address

Query was

SELECT REGISTRATION_NUMBER,LAST_NAME,FIRST_NAME,EMAIL FROM STUDENT 
ORDER BY FIRST_NAME

Problem was with Registration_Number column, in table its data type was bigint. When i run the that query, copy the result set and then paste into excel then Registration_Number column was giving two problems, mentioned below

1. Some of registration numbers were in unwanted data type for me such as floating type. I had a registration number 1010512500100483 which was showing as 1.01051E+15
2. Secondly, some registration numbers were automatically rounded off, such as 1010512500100505 was rounded into 1010512500100500, moreover in excel it was showing as 1.01051E+15 but when I clicked on the cell then it was showing in address bar as 1010512500100500 instead of 1010512500100505

Now we have two solutions, first one is pretty simple in which your excel cell should have been formatted as text before you pasting the result set. 

Before telling you second and accurate solution, let me tell you methods that i adopt in order to paste the actual result set from sql server to excel without changing the cell data type in excel. 

Methods that i adopt and all were proved fail. 

I converted the data type of registration_number column from bigint to varchar as mentioned below but it was not working.

SELECT CONVERT(VARCHAR,REGISTRATION_NUMBER) AS REGISTRATION_NUMBER,LAST_NAME,FIRST_NAME,EMAIL FROM STUDENT 
ORDER BY FIRST_NAME 

I converted the datatype of registration_number column from bigint to varchar and concatenated space as mentioned below but it was not working.

SELECT CONVERT(VARCHAR,REGISTRATION_NUMBER)+' ' AS REGISTRATION_NUMBER,LAST_NAME,FIRST_NAME,EMAIL FROM STUDENT 
ORDER BY FIRST_NAME 

But the following query proved successful as i achieved my target which was copying the query result set and pasting it into excel sheet without formatting any excel cell.

SELECT CONVERT(VARCHAR,REGISTRATION_NUMBER)+CHAR(160) AS REGISTRATION_NUMBER,LAST_NAME,FIRST_NAME,EMAIL FROM STUDENT 
ORDER BY FIRST_NAME 

That's it. Cheers!!!

Read more...

Unable To Drag and Drop Stored Procedure onto dbml Designer

In this article we will discuss about a problem that i faced couple of days ago, my visual studio 2010 was working fine, i closed the visual studio and after some time when i reopened it and try to drag and drop the stored procedure then it didn't allow me to drag and drop the stored procedure.

I tried my level best to reset the settings of visual studio but got no success then by searching on internet i got the solution and the solution is pretty simple, I found that it is due to one of the dll file (dsref80.dll) of visual studio, the dll was corrupted so i straight away took that dll from one of my colleague and replace it

Path to replace the dll is shown below:

C:\Program Files\Common Files\Microsoft Shared\Visual Database Tools\dsref80.dll

Cheerz...! :) 

Read more...

Could not load file or assembly 'System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified

Today i have got this asp.net nasty error, i fixed it and then decided to share it with you people. I was working on a website developed in asp.net version 2.0 but when i tried to run it through my friend's system (i had given a copy of website to him) then face this error.

To fix this error i simply installed asp.net ajax in that system and website come to life.

To solve issue without installing AJAX.NET:

Copy AjaxControlToolkit.dll and AjaxControlToolkit.pdb version 1.0.61025.0 from my system (in which asp.net ajax is installed) to ASP.NET App bin folder of my friend's system.

(i install my AjaxControlToolkit in C:\Program Files\Microsoft ASP.NET\ASP.NET 2.0 AJAX Extensions\AjaxControlToolkit)

Copy System.Web.Extensions.dll (C:\WINDOWS\assembly\GAC_MSIL\System.Web.Extensions) and System.Web.Extensions.Design.dll (C:\WINDOWS\assembly\GAC_MSIL\System.Web.Extensions.Design) from my system to ASP.NET App bin folder of my friend's system.

Run the website and it's working fine.

Read more...

How to avoid duplicate content in your WordPress blog

Most of us simply know wordpress is a tool for developing blogs/websites(news based, tutorials based), they want to earn from their wordpress blog/website, they want it to rank high in google whenever user type any search query relevant to their website but when they purchase domain, install wordpress, deploy their website then never ever they compete against any search query in google, never get traffic and remain live in darkness and then they blame that it is happening due to use of wordpress, and they think lets develop website by using plain html with server side technology like php or asp.net for their dynamic purposes :)

People develop sites in wordpress and some of them earn significant revenue but most of them are even unable to come in google against any search query, any keyword. Why? This is happen because they have blacklisted in google due to duplicate content and they don’t know about it that they have been blacklisted. Another question that I think coming in your mind is that how can we get plenty of duplicate content if we write post ourselves but I want to clear your concepts that doesn’t matter you write any post yourself by unique content, still very soon you will be blacklisted due to duplicate content. 

Now let me proof how you can be blacklisted due to duplicate content and from where that duplicate content is coming in your website. 

All of you knows that wordpress blog contains Archive, Tags and Categories Sections. Now lets suppose I have written any post for instance in the month of march 2013, you can access that post by clicking on post title in the main page of your blog or can go to that post through Recent Posts section in your blog but my dear fellows that post is too accessible from Archive, Tags and Categories section in your blog. 

Now question is how google mark it as a duplicate content? Answer is very simple, due to url. Google understand every url as a separate page, and if page is separate then content must be separate and unique. When you will click on March 2013 then the url will be http://www.yourwebsite.com/index.php/2013/03/, same issue you will find with Categories and Tags section. 

Now how to avoid duplicate content in your WordPress blog?  

One approach, which is foolish approach is to eliminate all these Sections from Blog, the reason why I say this as foolish approach because these all are the features of wordpress and due to these sections the user of your website can move anywhere, search any post, search post according to his interest and in response the bounce rate of your website will be significantly low. 

Second approach which is professional approach is to use all these Features (Categories, Archives, Tags) in your blog but don’t allow google to index them, if google will not index it then you are 100% safe. You can safe yourself by writing following php code in the header.php file of your wordpress 

<?php 

if((is_home() && ($paged < 2 )) || is_single() || is_page()){
    echo '<meta name="robots" content="index,follow" />';
} else {
    echo '<meta name="robots" content="noindex,follow" />';
}
?>

The best location to add this code snippet is just after <title> 

Hopefully you will find this tutorial very handy. Your comments are welcome.

Read more...

How to center align table or div using css

In this tutorial we will learn how to center align div or table using css. You can center align both of these html tags by using <center> tag. However, in web browsers such as Firefox and Flock or websites which adhere to stricter standards like xHTML 1.0 Strict, align="center" will be ignored, as align attribute has deprecated in preference of styles attribute instead. For text only, align attribute can be substituted with text-align tag as a workaround, but for other elements such as image, it will continue to align to the left, and not center. Moreover, you should know how you can center align div or table using css.

By following strict web coding standard, in order to center a block-type object within another block type object, you have to use the following code on the object that you wants to be centered (i.e. on the inner object):

style="margin-left:auto; margin-right:auto;"

or,

style="margin:0 auto;"

So for aligning div tag on center, you will use following html code

This line of text will be centered

I hope you will find this tutorial very handy. Your comments are welcome.

Read more...

Windows 7 Problem Steps Recorder, a screen capture tool

In this tutorial you will get knowledge about one of the best screen capture tool available in Windows 7. The name of this tool is Problem Steps Recorder, you can use it to create step-by-step recordings of any problems you have. This is the marvelous feature of Windows 7 which Quality Assurance team can use as a substitute of the Snagit Software, which is another famous tool for screen capturing. Open Start menu and type psr.exe in Search/Run. Here launches your Problems Step Recorder:

Key Features and Functionality

1. It records the details of the events even your mouse clicks i.e. left and right as well.
2. You can add comments while recording.
3. It automatically takes the screenshots while you are recording.
4. It mentions the page links you have traversed.
5. It gives the detailed summary of all the events at the end.
6. It saves the output file in mht format which can be opened in IE.
7. It’s so simple that you can use and learn it yourself.

Hopefully you will find this tutorial very informative.

Read more...