How to Update Remote SQL Server Databases with a Single Click "SQL Remote All Updater"

Vuyiswamb
Posted by in Sql Server category on for Advance level | Points: 600 | Views : 8291 red flag
Rating: 4.91 out of 5  
 22 vote(s)

This article describes how to update Remote SQL Server Databases with a Single Click.

 

Download source code for How to Update Remote SQL Server Databases with a Single Click "SQL Remote All Updater.

Introduction

Redgate is the best company in the world so far that offers software’s that compare data on Sql databases and even script data if you want to script a database with its data. They offer a service that is reliable and what i like about them is that their products work well on SQL Server no matter what version you are using they will do the job.

My Company has Clients from different Geographic locations and these Clients use the same software. This Software is structured in a way data at the end of the day you might find a client sitting with 20 databases, each have a different function. This is not a problem, but it is a challenge that one must face to support this Software.

The challenge is that if one change needs to be made on one database, it means it applies to the other 20 databases or more databases for some clients. If one client in China gets a bug, that means this bug needs to be fixed for someone in Wales or US. If the client in Wales has 10 databases and one in USA has 20 Databases, this means that I will use Redgate to Generate the Script that will fix the bug , but that means I have to VPN to all these clients and cope with a slow remote desktop and try to run a single script for 30 databases, in my case if I have more than 10 clients who are supposed to have a bug fix then its maintenance chaos.


Because Redgate are the leaders in this type of inventions, I took my time and contact them and i explained my situation. I wanted software that will connect to an existing vpn and loop through all the databases and update them one by one and be able to exclude certain databases. This must happen with a Single Click.

But Redgate admitted they don’t have such software and have never seen one that does this. I thought to myself that this is a room for a inventions. I First sit down and plan the project. This will include everything I wanted in order to have this to work. My First objective was to write a code that will open a VPN Connection of an existing VPN connection and connect using that connection and prompt the user to enter the credentials, after connecting, paste the script to be ran for certain databases.

In this Article i am going to demonstrate on how I built “SQL Remote All Updater”. I named the application because am looking at enhancing it and giving it for free that is why i am going to share the code.

 

Using the code

We are going to user C# as our language.

Start

Setting up the VPN

The first thing to do is to setup vpn connections for clients. There might be different types of vpn connections that one might be using, depend on the institution taste and type of business. As long as they can connect this will still work. In this Article I am going to concentrate on the normal windows vpn. I have already had 13 clients that i have vpn connection setup for them and when i go to my connections i get something similar to the following 

Setting up the VPN

On the existing vpn’s always save the passwords so that when the dialogbox pops’ up you can just click ok. You will see how it pops up later in our application. Now that we have our vpn connection setup and all tested individually if  they can connect, its means that we are ready to go to the second part of my invention.

Create a Windows Project

Now Create a Windows Project, in your form add controls so that it looks like the one depicted below
Creating a window project

And in your solution make sure that the following references are added and add a app.config and a class that is named VPN as depicted below

add reference

Ignore the infragistics assemblies; they are only UI controls that i use for most of my applications. Open your app.config file and make sure it looks like the one depicted below and i will explain later what those entries mean.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<appSettings>

<add key ="Client1" value ="Data Source=tcp:19.344.64.32\Client1,1433;Initial Catalog=oconfig;User ID=sa;Password=eish;"/>

<add key ="Client2" value ="Data Source=tcp:19.34.64.33\Client2,1433;Initial Catalog=oconfig;User ID=sa;Password=efdkjsd;"/>

<add key ="Client3" value ="Data Source=tcp:19.34.64.74\Client3,1433;Initial Catalog=oconfig;User ID=sa;Password=eisdfsh;"/>

<add key ="Client4" value ="Data Source=tcp:19.34.64.32\Client4,1433;Initial Catalog=oconfig;User ID=sa;Password=abasddcus;"/>

<add key ="Client5" value ="Data Source=tcp:19.32.64.23\Client5,1433;Initial Catalog=oconfig;User ID=sa;Password=sadsd;"/>

<add key ="Client6" value ="Data Source=tcp:19.22.64.11\Client6,1433;Initial Catalog=oconfig;User ID=sa;Password=sdsee;"/>

<add key ="Client7" value ="Data Source=tcp:19.22.64.3\Client7,1433;Initial Catalog=oconfig;User ID=sa;Password=sdsds;"/>

<add key ="Client8" value ="Data Source=tcp:19.84.64.33\Client8,1433;Initial Catalog=oconfig;User ID=sa;Password=essda;"/>

<add key ="Client9" value ="Data Source=tcp:19.32.64.222\Client9,1433;Initial Catalog=oconfig;User ID=sa;Password=dasds;"/>

<add key ="Client10" value ="Data Source=tcp:19.24.64.97\Client10,1433;Initial Catalog=oconfig;User ID=sa;Password=asdasda;"/>

<add key ="Client11" value ="Data Source=tcp:19.344.64.34\Client11,1433;Initial Catalog=oconfig;User ID=sa;Password=asdasd;"/>

<add key ="Client12" value ="Data Source=tcp:17.322.10.343\Client12,1433;Initial Catalog=oconfig;User ID=sa;Password=aadsd;"/>

<add key ="Client13" value ="Data Source=tcp:17.212.10.322\Client13,1433;Initial Catalog=oconfig;User ID=sa;Password=aeeffsd;"/>

<add key ="VPNPROCESS" value ="C:\\WINDOWS\\system32\\rasphone.exe"/>

</appSettings>

</configuration>

 

Let me explain what I did here. Let’s take one line of the app.config

 

<add key ="Client11" value ="Data Source=tcp:196.344.64.34\Client11,1433;Initial Catalog=oconfig;User ID=sa;Password=asdasd;"/>

The above line Connects to a SQL Server that is on the remote server. The ip address of that server is important in this case, this is the ip address you use when you want to do a remote desktop. Let me explain it all.

Data Source=tcp:196.344.64.34\Client11,1433

The above entry is that Data source part of the connection string. Here we are saying that we are going to connect to the server using tcp as the protocol and we are connection to a server with that ip address and in that server there is a named instance of Sql server name is named client11 and there is a last comma that 1433. This is the default port that is used by Sql. This is important when you do a remote connection to the server.

Initial Catalog=oconfig

This is the database that we are connecting to. Remember the objective of this application is to connect to a sql server and run a script that will update all the database that will meet our criteria. The Config will carry our stored procedure that will loop through the databases and update the correct ones.

User ID=sa;Password=asdasd;

We all know this entry; here are the credentials of the remote Sql server instance. Before we start coding I need you to make sure the following is correct on the remote server that is hosting your Sql server.

Remote Server Configuration

On the Remote server the following should be checked. By default Sql does enable TCP/IP, you need to enable it. To do that, go to Sql server Configuration Manager as depicted below

Remote Server Configuration

As you can see the Sql Server Browser is not running, start the server and make sure it start automatically by following the steps below

Start service

At the end the server will be running as depicted below

server running

We are not done yet, Click on the protocols under the Sql server network configuration and you will see the protocols that are available for your Sql instance as depicted below

SQL Server Configuration Manager

Do not be deceived by the enabled status on tcp/ip  double click on the tcp/ip and make sure the following is the same as mine

tcp/ip properties

Notice the moment you click apply and ok you will be prompted with the following message

Warning  message

This means that before the changes can take effect you need to restart the Sql server service, we will do that later, now we need to do the last two things.

The next thing we need to do is to enable the Named Pipes Protocol. On the same Sql Server Configuration Manager on the Protocols, just enable the named pipes as depicted below

Named pipe properties

After you click ok, the same message that appeared on the TCP/IP dialogbox will appear, don’t mind it for now, just ok, and ignore it.

Now almost everything that can prevent us from connecting to this Sql server is done.  These last steps are the last one and we will look at our code again.

Open SQL Management Studio and right click on the Sql server instance and go to properties and in the properties look for the connection option and make sure that it allow remote connections as depicted below

Server Properties

And the last part is to make sure that there are exceptions for port 1433 on the firewall. Go to Control Panel and click on the firewall, and on the firewall go to the exception tab as depicted below

Windows Firewall

Click on add port and add a port 1433 as an exception to the firewall as depicted below

Edit a Port

Now we are finished with the remote configurations. Now let us go back to our project.

Back to Windows Project

Remember i told you to create a class named vpn? Let us go and visit that class. That is the class that will be used by our application to connect the vpn.

Add the following code into your class “VPN”

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Diagnostics;

using System.Configuration;

namespace VPNManager

{

/// <summary>

/// Class to maintain connectivity to a specific VPN connection

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public class VPN

{

#region --Const--

/// <summary>

/// Where the rasphone.exe lives

/// </summary>

String VPNPROCESS = ConfigurationManager.AppSettings.Get("VPNPROCESS");// "C:\\WINDOWS\\system32\\rasphone.exe";

#endregion

#region --Fields--

/// <summary>

/// Internal variable for VPNConnectionName

/// </summary>

public string _VPNConnectionName;// = "VUT";

public string VPNConnectionName

{

set

{

_VPNConnectionName = value;

}

}

/// <summary>

/// Internal variable for IPToPing

/// </summary>

public string _IPToPing;// "196.21.157.2";

public string IPtoPing

{

set

{

_IPToPing = value;

}

}

/// <summary>

/// Internal variable for IsConnected

/// </summary>

private bool _isConnected = false;

/// <summary>

/// Timer that manages the Manage function

/// </summary>

private System.Timers.Timer MonitorTimer;

/// <summary>

/// Bool to flag if the system is currently checking for network validity

/// </summary>

private bool _isChecking = false;

/// <summary>

/// Bool to flag if the system is currently in a Manage loop

/// </summary>

private bool _isManaging = false;

#endregion

#region --Events--

public delegate void PingingHandler();

public delegate void ConnectingHandler();

public delegate void DisconnectingHandler();

public delegate void IdleHandler();

public delegate void ConnectionStatusChangedHandler(bool Connected);

/// <summary>

/// Fires when validating connectivity

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public event PingingHandler Pinging;

/// <summary>

/// Fired when it is trying to connect to the VPN

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public event ConnectingHandler Connecting;

/// <summary>

/// Fired when it is trying to disconnect from the VPN

/// </summary>

public event DisconnectingHandler Disconnecting;

/// <summary>

/// Fired when it is done working for the moment

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public event IdleHandler Idle;

/// <summary>

/// Fired when the IsConnected Property changes

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public event ConnectionStatusChangedHandler ConnectionStatusChanged;

/// <summary>

/// Call to raise Pinging event

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

protected void OnPinging()

{

if (Pinging != null)

{

Pinging();

}

}

/// <summary>

/// Call to raise Connecting event

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

protected void OnConnecting()

{

if (Connecting != null)

{

Connecting();

}

}

/// <summary>

/// Call to raise Disconnecting event

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

protected void OnDisconnecting()

{

if (Disconnecting != null)

{

Disconnecting();

}

}

/// <summary>

/// Call to raise Idle event

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

protected void OnIdle()

{

if (Idle != null)

{

Idle();

}

}

/// <summary>

/// Call to raise ConnectionStatusChanged event

/// </summary>

/// <param name="Connected">If connected to network</param>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

protected void OnConnectionStatusChanged(bool Connected)

{

if (ConnectionStatusChanged != null)

{

ConnectionStatusChanged(Connected);

}

}

#endregion

#region --Properties--

/// <summary>

/// Returns if you are connected to the network

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public bool IsConnected

{

get { return _isConnected; }

}

/// <summary>

/// IP to ping to validate connectivity

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public string IPToPing

{

get { return _IPToPing; }

set { _IPToPing = value; }

}

/// <summary>

/// Name of VPN connection as seen in network connections (not case sensitive)

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

//public string VPNConnectionName

//{

// get { return _VPNConnectionName; }

// set { _VPNConnectionName = value; }

//}

#endregion

#region --Private Methods--

/// <summary>

/// Pings the provided IP to validate connection

/// </summary>

/// <returns>True if you are connected</returns>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public bool TestConnection()

{

bool RV = false;

_isChecking = true;

try

{

OnPinging();

System.Net.NetworkInformation.Ping ping = new System.Net.NetworkInformation.Ping();

if (ping.Send(_IPToPing).Status == System.Net.NetworkInformation.IPStatus.Success)

{

RV = true;

}

else

{

RV = false;

}

ping = null;

if (RV != _isConnected)

{

_isConnected = RV;

OnConnectionStatusChanged(_isConnected);

}

OnIdle();

}

catch (Exception Ex)

{

Debug.Assert(false, Ex.ToString());

RV = false;

OnIdle();

}

_isChecking = false;

return RV;

}

/// <summary>

/// Shells the command to connect to the VPN

/// </summary>

/// <returns>True if connected</returns>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public bool ConnectToVPN()

{

bool RV = false;

try

{

OnConnecting();

Process.Start(VPNPROCESS, " -d " + _VPNConnectionName);

System.Windows.Forms.Application.DoEvents();

System.Threading.Thread.Sleep(5000);

System.Windows.Forms.Application.DoEvents();

RV = true;

OnIdle();

}

catch (Exception Ex)

{

Debug.Assert(false, Ex.ToString());

RV = false;

OnIdle();

}

return RV;

}

/// <summary>

/// Shells the command to disconnect from the VPN connection

/// </summary>

/// <returns>True if successfully disconnected</returns>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public bool DisconnectFromVPN()

{

bool RV = false;

try

{

OnDisconnecting();

System.Diagnostics.Process.Start(VPNPROCESS, " -h " + _VPNConnectionName);

System.Windows.Forms.Application.DoEvents();

System.Threading.Thread.Sleep(8000);

System.Windows.Forms.Application.DoEvents();

RV = true;

OnIdle();

}

catch (Exception Ex)

{

Debug.Assert(false, Ex.ToString());

RV = false;

OnIdle();

}

return RV;

}

/// <summary>

/// Handles the grunt work.

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

private void Manage()

{

try

{

if (!_isManaging)

{

_isManaging = true;

if (!_isChecking)

{

if (!TestConnection())

{

ConnectToVPN();

if (!TestConnection())

{

DisconnectFromVPN();

ConnectToVPN();

if (!TestConnection())

{

DisconnectFromVPN();

ConnectToVPN();

}

}

}

}

_isManaging = false;

}

}

catch (Exception)

{

_isManaging = false;

}

}

#endregion

#region --Public Methods--

/// <summary>

/// Overloaded end point to begin monitoring VPN status

/// </summary>

/// <param name="VPNName">Name of VPN connection as seen in network connections (not case sensitive)</param>

/// <param name="IPtoPing">IP to ping to validate connectivity</param>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public void StartManaging(string VPNName, string IPtoPing)

{

_VPNConnectionName = VPNName;

_IPToPing = IPtoPing;

StartManaging();

}

/// <summary>

/// End point to begin monitoring VPN status

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public void StartManaging()

{

if (!string.IsNullOrEmpty(_VPNConnectionName) & !string.IsNullOrEmpty(_IPToPing))

{

MonitorTimer = new System.Timers.Timer(15000);

MonitorTimer.Enabled = true;

MonitorTimer.Elapsed += new System.Timers.ElapsedEventHandler(MonitorTimer_Elapsed);

System.Net.NetworkInformation.NetworkChange.NetworkAvailabilityChanged += new System.Net.NetworkInformation.NetworkAvailabilityChangedEventHandler(NetworkChange_NetworkAvailabilityChanged);

Microsoft.Win32.SystemEvents.PowerModeChanged += new Microsoft.Win32.PowerModeChangedEventHandler(SystemEvents_PowerModeChanged);

Manage();

}

}

#endregion

#region --Constructors--

/// <summary>

/// Overloaded constructor

/// </summary>

/// <param name="VPNName">Name of VPN connection as seen in network connections (not case sensitive)</param>

/// <param name="IPtoPing">IP to ping to validate connectivity</param>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public VPN(string VPNName, string IPtoPing)

{

StartManaging(VPNName, IPtoPing);

}

/// <summary>

/// Default empty constructor

/// </summary>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

public VPN()

{

}

#endregion

#region --Event Handlers--

/// <summary>

/// Handles the event that is raised when the computer goes into, or comes out of, standby. Very useful for laptops

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

void SystemEvents_PowerModeChanged(object sender, Microsoft.Win32.PowerModeChangedEventArgs e)

{

if (e.Mode == Microsoft.Win32.PowerModes.Resume)

{

MonitorTimer.Stop();

System.Threading.Thread.Sleep(15000);

MonitorTimer.Start();

}

if (e.Mode == Microsoft.Win32.PowerModes.Suspend)

{

MonitorTimer.Stop();

}

}

/// <summary>

/// Handles the event that is raised when the network status changes

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

void NetworkChange_NetworkAvailabilityChanged(object sender, System.Net.NetworkInformation.NetworkAvailabilityEventArgs e)

{

if (e.IsAvailable)

{

if (!MonitorTimer.Enabled)

{

MonitorTimer.Start();

}

}

else

{

MonitorTimer.Stop();

}

}

/// <summary>

/// Handles the event the timer raises when it elapses

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

/// <history>

/// [Vuyiswa Maseko] 01/24/2010 Created

/// </history>

void MonitorTimer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)

{

Manage();

}

#endregion

}

}


This class has private and public functions and properties that will be accessed in our form. The purpose of this class is to open the vpn connection of an existing connection. Build your project and check if there are no errors. When done lets go to our form.

SQL Remote Updater

The first textbox is accepting the vpn ip address and the second one is the name of the vpn like client1. Please note that the ip address in the connection string is not a vpn ip address, but it’s the ip address of the server. Remember that before you access the server you must fist access the network. The next button is the button that will connect you to the vpn network. Double click on that button and you will taken to the click event of the button and add the following code.

if (txtScript.Text != "" && txtVPNIP.Text != "")

{

VPNManager.VPN obj = new VPNManager.VPN();

//TestConnection()

obj._IPToPing = txtVPNIP.Text;

obj._VPNConnectionName = txtVPNAME.Text;

Connection = obj.ConnectToVPN();

if (Connection)

{

MessageBox.Show("Its Connected");

btnsqlconnect.Enabled = true;

}

else

{

MessageBox.Show("Its not Connected");

}

}

 

Double click the other button and add the following code

 

private void ultraButton3_Click(object sender, EventArgs e)

{

Boolean SQl_Status = Connect_to_SQl_Execute_Script(txtVPNAME.Text);

if (SQl_Status == true)

{

MessageBox.Show("All Databases are Updated");

}

else

{

MessageBox.Show("Not Connected to SQL Server");

}

}

 

This code will execute the script on all the databases that are selected by the query that we will look at later. The last button disconnects the vpn connection and the code that should be there is as follows

 

private void ultraButton2_Click(object sender, EventArgs e)

{

VPNManager.VPN obj = new VPNManager.VPN();

Connection = obj.DisconnectFromVPN();

MessageBox.Show("VPN is now Disconnected");

}

 

And you have noticed that there is a large text area, there is where we are going to paste the script that we want our application to run on all the databases. Overall the cs file including the functions that are being used by the buttons must look like this.

 

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Management;

using DotRas;

namespace RemoteUpdater

{

public partial class Form1 : Form

{

Boolean Connection = false;

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

}

private void ultraButton1_Click(object sender, EventArgs e)

{

if (txtScript.Text != "" && txtVPNIP.Text != "")

{

VPNManager.VPN obj = new VPNManager.VPN();

//TestConnection()

obj._IPToPing = txtVPNIP.Text;

obj._VPNConnectionName = txtVPNAME.Text;

Connection = obj.ConnectToVPN();

if (Connection)

{

MessageBox.Show("Its Connected");

btnsqlconnect.Enabled = true;

}

else

{

MessageBox.Show("Its not Connected");

}

}

}

 

 

private void ultraButton2_Click(object sender, EventArgs e)

{

VPNManager.VPN obj = new VPNManager.VPN();

Connection = obj.DisconnectFromVPN();

MessageBox.Show("VPN is now Disconnected");

}

private void ultraButton3_Click(object sender, EventArgs e)

{

Boolean SQl_Status = Connect_to_SQl_Execute_Script(txtVPNAME.Text);

if (SQl_Status == true)

{

MessageBox.Show("All Databases are Updated");

}

else

{

MessageBox.Show("Not Connected to SQL Server");

}

}

private String Get_Connection_String(String VPNClinets)

{

String Constring = null;

if (VPNClinets == "Client1")

{

Constring = ConfigurationManager.AppSettings.Get("Client1");

}

if (VPNClinets == "Client2")

{

Constring = ConfigurationManager.AppSettings.Get("Client2");

}

if (VPNClinets == "Client3")

{

Constring = ConfigurationManager.AppSettings.Get("Client3");

}

if (VPNClinets == "Client4")

{

Constring = ConfigurationManager.AppSettings.Get("Client4");

}

if (VPNClinets == "Client5")

{

Constring = ConfigurationManager.AppSettings.Get("Client5");

}

if (VPNClinets == "Client6")

{

Constring = ConfigurationManager.AppSettings.Get("Client6");

}

if (VPNClinets == "Client7")

{

Constring = ConfigurationManager.AppSettings.Get("Client7");

}

if (VPNClinets == "Client8")

{

Constring = ConfigurationManager.AppSettings.Get("Client8");

}

if (VPNClinets == "Client9")

{

Constring = ConfigurationManager.AppSettings.Get("Client9");

}

if (VPNClinets == "Client10")

{

Constring = ConfigurationManager.AppSettings.Get("Client10");

}

return Constring;

}

private Boolean Connect_to_SQl_Execute_Script(String SQLSERVER)

{


String Constring = Get_Connection_String(txtVPNAME.Text);

SqlConnection con = new SqlConnection(Constring);

SqlCommand cmdselect = new SqlCommand();

cmdselect.CommandType = CommandType.StoredProcedure;

cmdselect.CommandTimeout = 0;

 

cmdselect.CommandText = "dbo.sp_Update_Databases";

cmdselect.Parameters.Add("@Query", SqlDbType.VarChar).Value = txtScript.Text.Trim();

cmdselect.Connection = con;

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = cmdselect;

 

Boolean Status = false;

DataSet ds = new DataSet();

try

{

con.Open();

if (con != null)

{

cmdselect.ExecuteNonQuery();

 

Status = true;

}

else

{

Status = false;

}

}

catch (SqlException ex)

{

MessageBox.Show(ex.Message);

}

finally

{

con.Close();

}

return Status;

}

}

}

There is a function named Connect_to_SQl_Execute_Script this function execute a storedprocedure,you stored procedure might sit on the master database, but I choose a settings database of my application and run it from there. These storedprocedure is the heart of this application, This storeprocedure accepts a string, which is a query and it will execute that query on all the databases except the master database and other system database and other that I felt I should not update. The nice thing about my System that is sold to the clients is that it can keep record of the databases that are used by that system and I can easly go and query that database and update those that are only in the database and ignore others including the master database. The Storedprocedure is defined as follows

 

CREATE PROC sp_Update_Databases

(

--This Stored Procedure will Update All the Databases in a SQL Server

@Query varchar(max)

)

as

DECLARE @SQL VARCHAR(max)

DECLARE @DB sysname

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR

SELECT [name]

FROM master..sysdatabases

WHERE [name] NOT IN ('model', 'tempdb','master','msdb','OCONFIG','oDirectv3','TTBLv3Master')

AND [NAME] NOT IN(SELECT DB FROM oDirectv3.dbo.tbl_dbref WHERE TYPE IN (1,3)) --Take only Class and Exam

AND [NAME] IN (SELECT DB FROM oDirectv3.dbo.tbl_dbref) --Only Class and Exam not Booking

ORDER BY [name]

OPEN curDB

FETCH NEXT FROM curDB INTO @DB

WHILE @@FETCH_STATUS = 0

BEGIN

set @SQL ='Use ' + @DB

exec(@SQL + '; ' + @Query)

FETCH NEXT FROM curDB INTO @DB

END

CLOSE curDB

DEALLOCATE curDB

 

AS you can see , it ignores the following databases

'model', 'tempdb','master','msdb','OCONFIG','oDirectv3','TTBLv3Master'


And the following part of the sp

-   AND [NAME] IN (SELECT DB FROM oDirectv3.dbo.tbl_dbref) --Only 
Class and


It takes only those that are part of the system. You might not that kind of control mechanism, but you can create one or exclude the second part of the query. When someone enter a script to fix a bug.

If you have been in my situation when clients use common application and your applications have a lot databases, then this is the way to go, because with a click you will be able to update 20 databases, and this saves time that you spent to connect to vpn and access slow remote desktops.

Conclusion

I have attached a sample project and I know it’s not easy to do this, but hey I am just across the world and you can reach me on DotnetFunda or through Google talk and I will talk you through all the steps i took. The Application will save you time making connection and updating individual databases on the client side. What you need is a vpn connection and you can fix bugs from your development machine.  I am looking at enhancing this application and make it a freeware.

Thank you for visiting DotnetFunda

Vuyiswa Maseko 

Page copy protected against web site content infringement by Copyscape

About the Author

Vuyiswamb
Full Name: Vuyiswa Maseko
Member Level: NotApplicable
Member Status: Member,MVP,Administrator
Member Since: 7/6/2008 11:50:44 PM
Country: South Africa
Thank you for posting at Dotnetfunda [Administrator]
http://www.Dotnetfunda.com
Vuyiswa Junius Maseko is a Founder of Vimalsoft (Pty) Ltd (http://www.vimalsoft.com/) and a forum moderator at www.DotnetFunda. Vuyiswa has been developing for 16 years now. his major strength are C# 1.1,2.0,3.0,3.5,4.0,4.5 and vb.net and sql and his interest were in asp.net, c#, Silverlight,wpf,wcf, wwf and now his interests are in Kinect for Windows,Unity 3D. He has been using .net since the beta version of it. Vuyiswa believes that Kinect and Hololen is the next generation of computing.Thanks to people like Chris Maunder (codeproject), Colin Angus Mackay (codeproject), Dave Kreskowiak (Codeproject), Sheo Narayan (.Netfunda),Rajesh Kumar(Microsoft) They have made vuyiswa what he is today.

Login to vote for this post.

Comments or Responses

Posted by: Levyt on: 4/29/2010
Good Day Vuyiswamb

i must say i am impressed by what you have done in this article. i have been using RedGate products and you are right , they don't have such product. i just confirmed that i have once asked a question in daniweb and you gave me a solution.

If Redgate can see this , they will hunt for your cv

keep up the good work

Levy !!!!!!!
Posted by: Thulanik on: 4/30/2010
Wow this is nice.

So tell me , Does this work on all SQL Versions ?
Posted by: Vuyiswamb on: 4/30/2010
This will work on all SQL Version. I tested this with SQl7 and 2000 and 2008. You will not have a problem using this in the next version of SQL

Thank you for Visiting Dotnetfunda

Vuyiswa Maseko
Posted by: Selephibuda on: 5/10/2010
Nice Article man. it might come inhandy during my DBA work to rebuilt the indexes on all the Db's
Posted by: Vuyiswamb on: 5/11/2010
Good Day Selephi

I am Glad you have joined our Forum long time, hope you are well. I have a complete package for you. I have an Article that has a script that rebuilt all the indexes for all databases in the sql server
http://www.dotnetfunda.com/articles/article432-how-to-rebuilt-all-indexes-in-all-databases-in-sql-20002005-.aspx

you can paste the script in this Application and it will save you work and time.

Login to post response

Comment using Facebook(Author doesn't get notification)