Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  Data Programming  »  SQL Server  »  An introduction to SQL Server Management Objects
An introduction to SQL Server Management Objects
by David Jeavons | Published  01/02/2007 | SQL Server | Rating:
David Jeavons
I have been programming database applications for almost 12 years starting out with VB3 and using every major version since then. I currently work for a retailer analysis company writing ASP.NET code for our ever evolving web site. I also enjoy helping other programmers with Visual Basic and .NET and am a moderator at vbCity. 

View all articles by David Jeavons...
Finding and connecting to an SQL Server

Before we can perform any operations against an SQL Server we must first connect to an instance of an SQL Server. To do this, we can either connect directly to a known instance or we can browse for all available SQL Servers that are on the network. Fortunately, retrieving a list of available SQL Servers is an extremely simple process; however, we must first make a reference to the Microsoft.SQLServer.SMO, Microsoft.SQLServer.ConnectionInfo and Microsoft.SQLServer.SqlEnum libraries and add the following Imports statement to our code file:

    1 Imports Microsoft.SqlServer.Management.Smo

    2 Imports Microsoft.SqlServer.Management.Common

 

To retrieve a list of available SQL Servers we can use the EnumAvailableSqlServers shared method of the SMOApplication object:

 

    1         Dim availableServers As DataTable = SmoApplication.EnumAvailableSqlServers

 

This method has two overloads, the first allows you to specify whether you want to enumerate through all servers on the network or only the local instance on your machine and the second allows you to specify the name of a computer where the instances of SQL Server are installed. An interesting point to note is that if you are not connected to a network then the method will simply look for the local instance of SQL Server regardless of the overloads you have specified. For demonstration purposes, this article will assume that you are connected to your local instance, although I will show you the code required to connect to any instance of SQL Server that you have access too.

 

Once this method call has finished, you will be returned a DataTable containing a list of all SQL Server instances found. Note, that if you are connecting to a local instance only then the DataTable will be empty as it does not return details for the local instance. The DataTable contains a column called Name which you can use when specifying the name of the SQL Server that you want to connect to.

 

To connect to the SQL Server of interest you need to create a server object and specify which SQL Server you are connecting to and how you wish to connect (SQL Server or Windows Authentication). The following code snippet demonstrates how to connect to a local instance of SQL Server using SQL Server authentication:

 

    1         Dim sqlServer As New Server()

    2 

    3         With sqlServer.ConnectionContext

    4             .ServerInstance = "(local)"

    5             .LoginSecure = False

    6             .Login = "username"

    7             .Password = "password"

    8             .Connect()

    9             .Disconnect()

   10         End With

 

If you wanted to connect to a different SQL Server instance then modify the ServerInstance property to the name of the SQL Server instance you want to connect too.

 

In the above example, the LoginSecure property is set to False to indicate that SQL Server authentication is to be used. By default, this property is set to True so if you wanted to use Windows Authentication to connect to the SQL Server instance then you can simply remove the LoginSecure, Login and Password properties:

 

    1         Dim sqlServer As New Server()

    2 

    3         With sqlServer.ConnectionContext

    4             .ServerInstance = "(local)"

    5             .Connect()

    6             .Disconnect()

    7         End With
Pages: « Back 1 2 3 4 5 6 Next »
Next: Enumerating and managing databases ยป
Comments    Submit Comment

Comment #1  (Posted by an unknown user on 03/21/2007)
Rating
coz i dont be run the sample
 
Comment #2  (Posted by an unknown user on 03/23/2007)
Rating
You've got me going with SMO. Nice article.
 
Comment #3  (Posted by an unknown user on 03/23/2007)
Rating
You've got me going with SMO. Nice article.
 
Comment #4  (Posted by Jose on 05/31/2007)
Rating
How would I get a connection string with a specified database as the Initial Catalog?
 
Comment #5  (Posted by David Jeavons on 05/31/2007)
Rating
Hi Jose

I'm not quite sure I understand what you mean, but if you simply want to connect to a particular SQL Server instance then set the ServerInstance property of the ConnectionContext object to the name of the SQL Server of interest.

To automatically use a single database, you can declare a database object and assign it a value from the Databases collection of the Server object. So for example, assuming I was already connected to the SQL Server and I wanted to work with a database called "TestDB" directly then the could would be similar too:

Dim db As Database = Server.Databases("TestDB")

If this is not what you meant, could you clarify your question a bit.


HTH
 
Comment #6  (Posted by paul on 06/11/2007)
Rating
I Get the following error when i try to create stored procedure

BC30057: Too many arguments to 'Public Sub New()'.

at this line

Dim sp As New StoredProcedure , "MyNewStoredProcedure")

 
Comment #7  (Posted by David Jeavons on 06/11/2007)
Rating
The syntax for creating a stored procedure is similar too:

Dim db As Database = sqlServer.Databases("DBName")
Dim sp As New StoredProcedure(db, "sp_Name")

sp.TextMode = False
sp.TextBody = "SELECT FieldList FROM Table"
sp.Create()

Basically, you reference the database where you want to create the stored procedure and then when instantiating a new stored procedure object you specify the database that it will be related to and the name for the new stored procedure.


HTH
 
Comment #8  (Posted by paul on 06/11/2007)
Rating
I get the same error when i tryed to create a new store dprocedure on button click event

error : Compiler Error Message: BC30057: Too many arguments to 'Public Sub New()'.

my click even code is given below :

Dim sqlServer As New Server()
Dim db As Database = sqlServer.Databases("sample")
Dim sp As New StoredProcedure(db, "TestSmo")
sp.TextMode = False
sp.TextBody = "select state_name from state"
sp.Create()

ALL MY SMO COMMAND WORKES EXCEPT STORED PROCEDURE , KINDLY GET ME OUT OF THIS ERROR

 
Comment #9  (Posted by David Jeavons on 06/12/2007)
Rating
Hi Paul

The majority of your code should work as expected, however, I noticed that you are declaring a new server object and then using it immediately to grab a reference to the database of interest. You should in fact first create a connection to the server and then use that connection to create your reference to the database and create the procedure.

The above code example demonstrates connecting to the SQL Server instance of interest and once you have that you should then be able to utilise the code that you have to create the procedure.


HTH
 
Comment #10  (Posted by paul on 06/12/2007)
Rating
Hi David
I'm not able to correct the error till now just go through this code and please correct the lines where i make mistake

Dim connectionstring As String
connectionstring = "Data Source=.;Initial Catalog=sample;Integrated Security=True;Pooling=False"
Dim connection As New SqlConnection(connectionstring)
Dim sqlServer As New Server()
connection.Open()
Dim Server = New ServerConnection(connection)
Dim db As Database = sqlServer.Databases("sample")
Dim sp As New StoredProcedure(db, "testproc")
sp.TextMode = False
sp.TextBody = "SELECT * FROM state"
sp.Create()
Thanks and Regards
 
Comment #11  (Posted by David Jeavons on 06/13/2007)
Rating
Hi Paul

Try the following:

Dim sqlServer As New Server("ServerName")
sqlServer.ConnectionContext.Connect()

Dim db As Database = sqlServer.Databases("sample")
Dim sp As New StoredProcedure(db, "testproc")
sp.TextMode = False
sp.TextBody = "SELECT * FROM State"
sp.Create()

sqlServer.ConnectionContext.Disconnect()


HTH
 
Comment #12  (Posted by paul on 06/13/2007)
Rating
Hi david
still i get error on the line

Dim sp As New StoredProcedure(db, "testproc")

error : Too many arguments to 'Public Sub New()'.

i got a code written in c# which workes fine if possible tell me where lies the problem in vb code


c# code i used

string connectionString = "data source=.;initial catalog=sample;timeout=200;user id=sample; pwd=sample123;";
SqlConnection connection = new SqlConnection(connectionString);
Server server = new Server(new ServerConnection(connection));

Database db = server.Databases["sample"];

StoredProcedure SP = new StoredProcedure(db, "GetstateID");
SP.TextMode = false;
SP.AnsiNullsStatus = false;
SP.QuotedIdentifierStatus = false;

StoredProcedureParameter idParam = new StoredProcedureParameter(SP, "@ID", DataType.Int);
SP.Parameters.Add(idParam);

SP.TextBody = "Select * from state WHERE [ID] = @ID";

SP.Create();

thanks and regards
 
Comment #13  (Posted by paul on 06/28/2007)
Rating
Hi
is there a way to display the records inside the table i.e i would like to use a select statement on a particular table
 
Comment #14  (Posted by an unknown user on 01/03/2008)
Rating
Hi
Is there a way to export and import data from one server to another using SMO ? If so please post the syntax and procedure
 
Comment #15  (Posted by an unknown user on 09/11/2008)
Rating
Nice and Easy
 
Comment #16  (Posted by vaibhav kadian on 09/27/2008)
Rating
the drop command on database doesn't work.
also tried the db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted before dropping , it also failed.

any suggestions
 
Comment #17  (Posted by Anke on 10/12/2008)
Rating
don't just hit the back button once, they hit it twice. ,
 
Comment #18  (Posted by Luetta Mcglone on 10/12/2008)
Rating
channel or someday they may well find ,
 
Sponsored Links