Enabling Service Broker in SQL Server 2008

Thursday

Activate  Service Broker in SQL Server 2008:
                          If you are working in sql dependency, SignalR related projects you must enable service broker for  messaging and queuing functions between instances. The basic functions of sending and receiving messages forms a part of a “conversation.”

What is services broker?

                        According  from Microsoft SQL Server Service Broker provides native support for messaging and queuing applications in the SQL Server Database Engine. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications.

                        Service broker find applications when single or multiple SQL server instances are used. This functionality helps in sending messages to remote databases on different servers and processing of the messages within a single database. In order to send messages between the instances, the Service Broker uses TCP/IP.

Check the sql server services broker status?

                          Before enable service broker better  we need check the current services broker status of the database. The following simple command used to do that.

SELECT name, is_broker_enabled FROM sys.databases

Then activate services broker on "codingclusterDB" database by using the following command.

ALTER DATABASE CodingClusterDB SET ENABLE_BROKER
GO

Enable SQL Server Broker taking too long time?

                                         If your Alter Database query takes long time to process, then use the following command to activate services broker on your database

ALTER DATABASE CodingClusterDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO

Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection.

Friday

SQL Server connection Error: 18452 Login failed for user ‘(null)’.

I had an error that I believe a lot of new user for SQL server may face during create a new Database.

             

Below is the solution that I'm used to resolve my issue

Change the Authentication Mode of the SQL server from “Windows Authentication Mode (Windows Authentication)”
to “Mixed Mode (Windows Authentication and SQL Server Authentication)”.

Run following script in SQL Analyzer to change the authentication

LOGIN sa ENABLE
GO
ALTER LOGIN sa WITH PASSWORD = ‘’
GO

OR

In Object Explorer, expand Security, expand Logins, right-click 'sa', and then click Properties. On the General page, you may have to create and confirm a password for the 'sa' login. On the Status page, in the Login section, click Enabled, and then click OK.

.NET Framework: Csharp(C#) Equivalents of SQL Server 2005 DataTypes

Tuesday

SQL Server Data Types and Their .NET Framework (c#) Equivalents:
                    The following table lists Microsoft SQL Server data types, their equivalents in the common language runtime (CLR) for SQL Server in the System.Data.SqlTypes namespace, and their native CLR equivalents in the Microsoft .NET Framework.

SQL Server data type	CLR data type (SQL Server)	CLR data type (.NET Framework)
varbinary	SqlBytes, SqlBinary	Byte[]
binary	SqlBytes, SqlBinary	Byte[]
varbinary(1), binary(1)	SqlBytes, SqlBinary	byte, Byte[]
image	None	None
varchar	None	None
char	None	None
nvarchar(1), nchar(1)	SqlChars, SqlString	Char, String, Char[]
nvarchar	SqlChars, SqlString SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations.	String, Char[]
nchar	SqlChars, SqlString	String, Char[]
text	None	None
ntext	None	None
uniqueidentifier	SqlGuid	Guid
rowversion	None	Byte[]
bit	SqlBoolean	Boolean
tinyint	SqlByte	Byte
smallint	SqlInt16	Int16
int	SqlInt32	Int32
bigint	SqlInt64	Int64
smallmoney	SqlMoney	Decimal
money	SqlMoney	Decimal
numeric	SqlDecimal	Decimal
decimal	SqlDecimal	Decimal
real	SqlSingle	Single
float	SqlDouble	Double
smalldatetime	SqlDateTime	DateTime
datetime	SqlDateTime	DateTime
sql_variant	None	Object
User-defined type(UDT)	None	Same class that is bound to the user-defined type in the same assembly or a dependent assembly.
table	None	None
cursor	None	None
timestamp	None	None
xml	SqlXml	None

For more details please review this link http://msdn.microsoft.com/fr-fr/library/ms131092(v=sql.90).aspx

ASP.NET: How to Show/ Hide Div Tags based on radio button selection

Wednesday

Show/hide div based on radio button selection in asp.net:
                                                                       This post will show you how to create a hidden Div and display it with the click of a link. The following code will Show / Hide a div tags  when changing the selection of a radio button.

Sample code for show/hide div tag from code-behind using asp.net:

@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Hide div</title>

    <script runat="server">
        protected void rbtnHideDiv_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            if (rbtnHideDiv.SelectedItem.Text == "Red")
            {
                divRed.Style.Add("display", "none");
                divGreen.Style.Add("display", "inline");
            }
            if (rbtnHideDiv.SelectedItem.Text == "Green")
            {
                divGreen.Style.Add("display", "none");
                divRed.Style.Add("display", "inline");
            }
        }
    </script>

</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:RadioButtonList ID="rbtnHideDiv" runat="server" AutoPostBack="true" OnSelectedIndexChanged="rbtnHideDiv_SelectedIndexChanged">
            <asp:ListItem>Red</asp:ListItem>
            <asp:ListItem>Green</asp:ListItem>
        </asp:RadioButtonList>
    </div>
    <div style="background-color: Red" id="divRed" runat="server">
        <asp:Label ID="lblTextRed" SkinID="label_information" Text="My background is RED"
            ForeColor="White" runat="server"></asp:Label>
    </div>
    <div style="background-color: Green" id="divGreen" runat="server">
        <asp:Label ID="lblGreen" SkinID="label_information" Text="My background is GREEN"
            ForeColor="White" runat="server"></asp:Label>
    </div>
    </form>
</body>
</html>

                                   

Sample code for show/hide div tag using java script in asp.net:

<html>
<head runat="server">
    <title></title>
     <script type="text/javascript" language="javascript">

         function hideDiv() {
             document.getElementById('div1').style.display = 'none';
             document.getElementById('div2').style.display = 'none';

             if (document.getElementById('rbtnMain_0') != null) {
                 if (document.getElementById('rbtnMain_0').checked) {
                     document.getElementById('div1').style.display = 'block';
                 }
             }

             if (document.getElementById('rbtnMain_1') != null) {
                 if (document.getElementById('rbtnMain_1').checked) {

                     document.getElementById('div2').style.display = 'block';

                 }
             }
         }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
      <asp:RadioButtonList ID="rbtnMain" runat="server" onchange="hideDiv()">
            <asp:ListItem Text="rb1" Value="1" Selected="True"></asp:ListItem>
            <asp:ListItem Text="rb2" Value="2"></asp:ListItem>
        </asp:RadioButtonList>
        <div id="divmain">
        <div id="div1">
           div1 <asp:Button ID="Button1" runat="server" Text="Button" />
        </div>
        <div id="div2" style="display:none">
           div2  <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        </div>
        </div>
        
    </div>
    </form>
</body>
</html>

If this post was help to you. Then Share this to your friends. Thanks!

How to convert a database from mysql to sql server

Tuesday

Migrate MySQL to Microsoft SQL Server:
                                                           In one of my dotnet project I'm working with mysql database that is such a huge one, but I want a schema diagram, so decided to create  this same database on MS SQL Server for create schema diagram easily. That time this tool was so much help for me. The tool name is "Full Convert Enterprise".
                                                         Full Convert Enterprise is the easiest and most feature-rich database converter on the market. It will effortlessly migrate your database tables with all the data, create indexes, foreign keys - and more. This  is one of  a best tool for database migration.

This tool supporting the following converts.

•  Microsoft Access
• Microsoft Excel
• MySQL
• Microsoft SQL Server
• SQL Server Compact/SQLCE
• Oracle
• PostgreSQL
• Interbase
• Firebird
• Delimited text files (CSV)
• XML

Download:
                   You can download this database converter from here

Login failed for user ‘sa’. The user is not associated with a trusted SQL Server connection.(Microsoft SQL Server error: 18456):

Login failed for user 'sa' sql server 2005 :(Microsoft SQL Server error: 18456)
                        Normally if the  SQL Server is not set to use both SQL Server and Windows Authentication Mode, this error will occurred. To fix this issue you have to change the Authentication Mode of the SQL server from “Windows Authentication Mode (Windows Authentication)” to “Mixed Mode (Windows Authentication and SQL Server Authentication)”.

                             

Solution:

1.      Go to Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio.
2.      Right-click the Server name. Select Properties > Security.
3.      Under Server Authentication, select SQL Server and Windows Authentication Mode   (refer the   screenshot below)
4.     The server must be stopped and re-started before this will take effect.

                                 

            

How to get last inserted Identity value in SQL server - ASP.NET

Thursday

Getting the Identity of the last Inserted row - ASP.net/C#:
                                    The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.
           The Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately. It is also therefore possible, though not necessary, to create a new Command object to run the Select @@Identity query. The following code shows this in action where the Connection object is opened, then the first query is executed against cmd using ExecuteNonQuery() to perfom the Insert, followed by changing the CommandText property of cmd to "Select @@Identity" and running that.
Code:

protected void btnSave_Click(object sender, EventArgs e)
    {
        string strConnection = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
        SqlConnection sqlConn = default(SqlConnection);
        SqlCommand sqlCmd = default(SqlCommand);
        try
        {
            string query2 = "Select @@Identity";
            sqlConn = new SqlConnection(strConnection);
            sqlCmd = new SqlCommand("INSERT into student (firstname, lastname, street, city, state) VALUES ('" + txtFirstname.Text + "', '" + txtLastname.Text + "', '" + txtStreet.Text + "','" + txtCity.Text + "','" + txtState.Text + "')", sqlConn);
            sqlConn.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCmd.CommandText = query2;
            int idx = Convert.ToInt32(sqlCmd.ExecuteScalar());
            if (idx != 0)
            {
                Response.Write("<script>alert('Successfully saved')</script>");
            }
            else
                Response.Write("<script>alert('Not saved')</script>");
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString() + "<br>");
        }
        finally
        {
            sqlConn.Close();
        }
    }

Please share this post if it's useful to you. Thanks!.

How to remove duplicate rows from a table in SQL Server

Tuesday

Remove Duplicate Rows from a Table in SQL Server:

                    Duplication rows in database tables will exists by running the data repeatedly With out  having the primary key on table. Here is an example to remove the duplicate records  from a table in SQL Server

                       

DELETE FROM Employee emp1 WHERE ROW_NUMBER()<>(SELECT MIN( ROW_NUMBER())FROM       EMployee emp2 WHERE emp1.empname= emp2.empname) 

Please share this post if it's useful to you. Thanks!.

Get stored procedure count in sql server

Thursday

Some Important  SQL Queries:

                                     The following SQL queries  are very much used to my one of  a stored procedure based .net project. It may useful you too.

Get stored procedure count from a sql table:

SELECT count(*) SPCOUNT FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'

Get number of function from a sql table:

SELECT count(*) FUNCTIONCOUNT FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='FUNCTION'

Get all stored procedure name from a sql table:

SELECT name AS spname
FROM sysobjects
WHERE (xtype = 'p') AND (name NOT LIKE dt%')
ORDER BY name 

Get all table name in a sql database:

Select table_name from information_schema.tables
where table_type='Base table' order by table_name 

Get stored procedure(s) for a sql table:

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%table name%'