Category: Tutorials

By the help of following code you can get all Sql instance with in Local or Network in your Datatable Object so that you can easily display these instances to user.

First you need to add following references-
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo

[vb.net]
import Microsoft.SqlServer.Management.Smo with the help of ‘Imports’ keyword in code file

Imports Microsoft.SqlServer.Management.Smo

and use code:

    Private Sub Getdatatables()
        'Get all Network Instance
        Dim dataTable1 As System.Data.DataTable = SmoApplication.EnumAvailableSqlServers(False)
 
        'Get all Local Instance
        Dim dataTable1 As System.Data.DataTable = SmoApplication.EnumAvailableSqlServers(True)
    End Sub

[C#]

import Microsoft.SqlServer.Management.Smo with the help of ‘Using’ keyword in code file

Using Microsoft.SqlServer.Management.Smo;

and use this code:

private void Getdatatables()
{
	//Get all Network Instance
	System.Data.DataTable dataTable1 = SmoApplication.EnumAvailableSqlServers(false);
 
	//Get all Local Instance
	System.Data.DataTable dataTable1 = SmoApplication.EnumAvailableSqlServers(true);
}

By the help of following code you can get all Sql Database of any sql instance Data table Object so that you can easily display these Database names to user. In the following code we are binding all database in drop down control.

First you need to add following references-
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo

[VB.Net]

    Private Sub BindDatabaseNames(ByVal SqlAuth As Boolean, ByVal cmbDataBase1 As ComboBox)
 
        Dim serverName As String = "Put your server name"
 
        Dim serverConnection As Microsoft.SqlServer.Management.Common.ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection()
        serverConnection.ServerInstance = serverName
        serverConnection.LoginSecure = True
        If SqlAuth Then
            serverConnection.LoginSecure = False
            serverConnection.Login = "Put Login name"
            serverConnection.Password = "Put password"
        End If
        Dim server As Server = New Server(serverConnection)
        Try
            For Each db As Database In server.Databases
                cmbDataBase1.Items.Add(db.Name)
            Next
        Catch ex As Exception
            Dim exception As String = ex.Message
        End Try
 
    End Sub

[C#]

private void BindDatabaseNames(bool SqlAuth, ComboBox cmbDataBase1)
{
	string serverName = "Put your server name";
 
	Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection();
	serverConnection.ServerInstance = serverName;
	serverConnection.LoginSecure = true;
	if (SqlAuth) {
		serverConnection.LoginSecure = false;
		serverConnection.Login = "Put Login name";
		serverConnection.Password = "Put password";
	}
	Server server = new Server(serverConnection);
	try {
		foreach (Database db in server.Databases) {
			cmbDataBase1.Items.Add(db.Name);
		}
	} catch (Exception ex) {
		string exception = ex.Message;
	}
 
}

Some time when SQL query process goes in suspended state, it automatically locks the table and we cannot do any other operation on this table. If this process take much time and the result is not valuable then we need to wait until it finished.

So in following example we can learn how to find suspended process and how to kill that and unblock table.

Continue reading “Find suspended state and unlock table in Sql Server”