Order by on Varchar field in sql server

Hello Reader,

In this articles, i am going to explain you, how to use Order by on Varchar Fields in SQL Server,

Till now you might have implemented order by on Int Fields but you may find difficult to implement order by on Varchar fields.

In Image you can see, i have create one field with Name ONO as varchar(50)..

Now if you will do directly order by like shown below, then you will get wrong data.

This way is wrong for Varchar Field..

select * from db_product DP
order by ONO

You will get wrong data as Output..

The correct way is there..

select * from db_product DP
order by 
case 
    IsNumeric(DP.ONO) when 1 then 
        Replicate(Char(0), 100 - Len(DP.ONO)) + DP.ONO 
    else 
        DP.ONO 
end

Thanks Hope this post will help you, if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Email: raj143svmit@gmail.com
Dobazaar (Dubai, UAE)
www.dobazaar.com

Change ConnectionString when database server is not working

Hello Friends,

Sometime, you might have notice that, your hosting server is working fine but your database is not working due to which you
might be getting error on website and you might be loosing visitor and ranking in website..

This post is regarding that only, you can manage this issue by two database.

You can have two database at different server and you can call this in global.asax

first you can make ping to first database everytime and if its working you can call first server database and
if you find any issue or server is not working then you can switch to second server using below code..

    Dim connStatus As String
        Dim png As New Ping()
        Dim pr As PingReply = png.Send("221.256.357.36")
        connStatus = pr.Status.ToString()
        '--------------------

        If connStatus = "Success" Then
            If ConnString = "OldConnection" Then
                If ConfigurationManager.ConnectionStrings("oldconnection1").ConnectionString <> "" Then
                    ConnObj = New SqlConnection(ConfigurationManager.ConnectionStrings("oldconnection1").ConnectionString)
                Else
                    Throw New Exception("Connection String is Empty")
                End If
            Else
                If ConfigurationManager.ConnectionStrings("oldconnection2").ConnectionString <> "" Then
                    ConnObj = New SqlConnection(ConfigurationManager.ConnectionStrings("oldconnection2").ConnectionString)
                Else
                    Throw New Exception("Connection String is Empty")
                End If
            End If
        Else
            If ConnString = "newconnection" Then
                If ConfigurationManager.ConnectionStrings("newconnection1").ConnectionString <> "" Then
                    ConnObj = New SqlConnection(ConfigurationManager.ConnectionStrings("newconnection1").ConnectionString)
                Else
                    Throw New Exception("Connection String is Empty")
                End If
            Else
                If ConfigurationManager.ConnectionStrings("newconnection2").ConnectionString <> "" Then
                    ConnObj = New SqlConnection(ConfigurationManager.ConnectionStrings("newconnection2").ConnectionString)
                Else
                    Throw New Exception("Connection String is Empty")
                End If
            End If
        End If

Let me know, if you have any query..

Thanks Hope this post will help you, if yes please put comment below of this page,

Rajesh Singh,

Senior Asp.Net Developer

Email: raj143svmit@gmail.com

e-Procurement Technologies Ltd (India)

www.abcprocure.com 

SQL Server Query to Search Text in Stored Procedure

Hello Friends,

This post contains one query, by using that you can make search for any text in your Stored
Procedure Or Triggers,

Here is the query..

Use TempDatabase

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

Frist Select the Database by Use prefix,like below
use TempDatabase

Then copy this query in your query browser of SQL server,

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%proc_%'
AND ROUTINE_TYPE='PROCEDURE'
and just press F5, to run this query

Let me know, if you have any query..

Thanks Hope this post will help you, if yes please put comment below of this page,

Rajesh Singh,

Senior Asp.Net Developer

Email: raj143svmit@gmail.com

e-Procurement Technologies Ltd (India)

www.abcprocure.com 

 

 

Solved : Cannot load the DLL xp_md5.dll

Issue : "Cannot load the DLL xp_md5.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)."


Solution :

You are missing one dll to keep in this folder "D:\Program Files\Microsoft SQL Server\100\Tools\Binn\ "

You can download that file from here
http://download555.mediafire.com/3hw61lrj9njg/ki2omn4dzjm/xp_md5.dll.rar


Then keep this file in folder "D:\Program Files\Microsoft SQL Server\100\Tools\Binn\"

Then open your sql server and run this sql query so that you can register this dll in sql server.

xp_md5.dll

USE [master]
GO

EXEC dbo.sp_addextendedproc N'xp_md5', 'D:\Program Files\Microsoft SQL Server\100\Tools\Binn\xp_md5.dll'
 
GO

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
Email: raj143svmit@gmail.com
e-Procurement Technologies Ltd (India)
www.abcprocure.com

How to create Trigger in SQL server ?

In this post you can see, how to create trigger in SQL server 2005/2008

ALTER TRIGGER t_XYZ_Update 
   ON  tbl_tableName1
   AFTER UPDATE
AS 
IF ( UPDATE (tableField1) OR UPDATE (tableField2) OR UPDATE (tableField3) OR UPDATE (tableField4))
    BEGIN
    SET NOCOUNT ON;
    
    DELETE FROM tbl_tableName2 WHERE FieldId=(select FieldId from inserted)
    print 'Row Deleted Successfully'
    END
GO

Name you trigger like below line

ALTER TRIGGER t_XYZ_Update

Assign the table name on which you have to perform operation like below line

ON  tbl_tableName1

Design when you want to allow this trigger to get fire, here let take after UPDATE operation.

AFTER UPDATE

If any of the tbl_tableName1 field like tableField1,tableField2,tableField3,tableField4 get updated outside by query or in SP, then this trigger will get fire and perform operation assign to this trigger, you can find when field get updated by below query,

 
IF ( UPDATE (tableField1) OR UPDATE (tableField2) OR UPDATE (tableField3) OR UPDATE (tableField4))

Here in below box, you can see the operation you want to perform when this trigger get fire, here we have used "inserted" table, whenever any of field found updated, same row is inserted in "inserted" table so by this table you can find which row is updated and you can make use of that rowid to perform other operation,

 
    BEGIN
    SET NOCOUNT ON;
    
    DELETE FROM tbl_tableName2 WHERE FieldId=(select FieldId from inserted)
    print 'Row Deleted Successfully'
    END

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
Email: raj143svmit@gmail.com
e-Procurement Technologies Ltd (India)
www.abcprocure.com

Prevent table from drop and re-create during saving changes

Hello Friends,

Problem : Whenever you make any changes in the table defination, when table is fill with data or rows,it does not allow you to delete or make any changes to table.


Solution : You might have faced this issue many times, when ever you make any changes in the table defination and you try to save changes, then you might have seen error messages as shown in below image.

So here is the steps by which you can avoid dropping of table or re-creating it again by using below steps..

Step 1. There is one option in sql server, which allow you to save your changes to table without droping it.

You just click on Tool option in top menu, See below images you can see from where you can go to tool option,

Step 2. Then click on "Option",
then you will be prompt with options Box, in that you will see left menu, in that explore "Designers" option.


After Exploring Designers option, just click on "table and database designer", you will see below screen

Step 3. In below snap, you can see the field in Red Box, just untick it, so that you can save change.

Untick - Prevent saving changes that require table re-creation


Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
Email: raj143svmit@gmail.com
e-Procurement Technologies Ltd (India)
www.abcprocure.com

Use of ROLLBACK in SQL server Stored Procedure

Use of ROLLBACK in SQL server Stored Procedure

You might have heard about ROLLBACK but you have never used it right ?

If you have used it, its well and good but if Not, then this blog might help you to know, how you can use ROLLBACK in your SP.

Suppose you want to use two insert statement, one by one.

You want to use, @@identity of first insert in another insert statement then this is the best method to go with.

Suppose you are doing first insert operation and some problem occure and any how you are not able to insert data in second table then there might be some database mapping issue.

so here, ROLLBACK can help you to avoid such database problem,

For example, you have used ROLLBACK in your SP, and you are doing first insert operation and if some problem occur and you are not able to insert in second table then ROLLBACk will undo your first insert operation also which will help to avoid database mapping issue.


Below is the syntax of ROLLBACK,

BEGIN TRY
    BEGIN TRAN
    BEGIN
        'You insert statement
                            
        COMMIT TRAN
                    
    END
END TRY
 
BEGIN CATCH
    BEGIN
        ROLLBACK TRAN
                    
        PRINT ERROR_MESSAGE()
    END
END CATCH

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
Email: raj143svmit@gmail.com
e-Procurement Technologies Ltd (India)
www.abcprocure.com

How to give reference of other table to current table in fields?

Hello friends,

Thanks for all your support, Please read this new post to know how to give reference of other table to current table in fields.

You can find if any table with this name is exist in database of not, if you found delete it and create this one.

To do this operation use this code,

--Table Name: XYZ_SongRequest

    IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('XYZ_SongRequest'))
        DROP TABLE XYZ_SongRequest
    GO

Now by using below code, you can create reference of other table let say, XYZ_User, XYZ_City, XYZ_Rj to table XYZ_SongRequest

--Table Name: XYZ_SongRequest

CREATE TABLE XYZ_SongRequest (
    iRequestID            BIGINT            NOT NULL PRIMARY KEY IDENTITY(1,1),
    iMemberId            BIGINT            NOT NULL CONSTRAINT FK_SongRequest_iMemberId References XYZ_User(iMemberID),
    iCityID                INT                NOT NULL CONSTRAINT FK_SongRequest_iCityID References XYZ_City(iCityID),
    iRjId                INT                NOT NULL CONSTRAINT FK_SongRequest_iRjId References XYZ_Rj(iRJid),
    sRequest            VARCHAR(1000)    NOT NULL, 
    dRequestDate        DATETIME        NOT NULL DEFAULT GETDATE(),
    dProcessDate        DATETIME        ,
    sStatus                Varchar(20)        CHECK(sStatus in ('Pending','Approved')) NOT NULL DEFAULT 'Pending' 
)
GO

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
e-Procurement Technologies Ltd (India)
www.abcprocure.com

Where to put Connnectionstring in web.config file.

Hello bloggers,

Most of the developers have big confusion that, where to put connection string in web.config file,
So this article will teach you which portion you can use to write your connection string.
When you will open web.config file, you will find this tag

<connectionStrings />

Here what you can do is, just replace this string with below connectionstring.

Use this connectionstring when database is on other server or other computer.

<connectionStrings>
  
      <add name="conn" connectionString="Data Source=XYZ;Initial Catalog=DatabaseName;user id=UserId;password=Password; Max Pool Size=7500;Connect Timeout=200;" providerName="System.Data.SqlClient"/>
      
</connectionStrings>

when database is in your computer, you can use local database server.

<connectionStrings>

<add name="conn" connectionString="Data Source=localhost;Database=MyDB;Integrated Security=SSPI " providerName="System.Data.SqlClient"/>
      
</connectionStrings>

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
e-Procurement Technologies Ltd (India)
www.abcprocure.com

Manually Reset Identity Column Value in SQL Server

Problem : If you are creating any table and using an identity column to that table, then at first time, when you insert any row, then identity value will start like 1,2,3... and so on..

But if you delete all the row from the table and when you try to insert new row in same table, the identity value will start from last identity value.

For Example the last identity was 6, so after deleting all data, when you insert new row, the identity value will be 7.

So to reset the identity Column value, you can use this below code.

Syntax

DBCC CHECKIDENT ( <table name>,RESEED,<new value>)

QUERY 

DBDD CHECKIDENT ('tbl_rajesh',RESEED,1)

Explaination :
table name = Your table name should be entered here
RESEED = this is used to reset your identity value.
new value = Starting identity value

If you want to check current identity value of any table, you can use below query.

DBCC CHECKIDENT (’tablename’, NORESEED)

Explaination :
table name = Your table name should be entered here
NORESEED = This will avoid RESEED function.

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Indianic Infotech Ltd (India)
rajesh@indianic.com

Solved : Microsoft SQL Server, Error:233

Issue : Microsoft SQL Server, Error:233

Solution :

Sometimes when you try to open sql server 2005 or 2008, after entering login details, you will be prompted by this below screen, telling "Microsoft SQL Server, Error:233", then you just get struck and restart you computer or even format the computer or uninstall sql server 2005 or 2008, but not getting any solution?

Please read below to find the solution to this issue.


At this time, what you need to do is, just open your network connection and just disable and enable once,as shown below and your problem solved, thats it. try to login again and you will be succussfully login.

Ya,i know you might be rubbing your head. i have even done that when i came to know the solution to this problem. but anyways finally you got solution to this problem.

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Indianic Infotech Ltd (India)
rajesh@indianic.com