Library tutorials & articles

SQL Distributed Management Objects

By Carvin Wilson, published on 25 Oct 2001
Page 4 of 5
  1. Overview
  2. What is SQL-DMO?
  3. How can we use this stuff?
  4. The sample application
  5. Summary

The sample application

Let's create a simple ASP and SQL-DMO application that connects to a server, gets a list of stored procedures for a given database, allows you to modify the text, and finally applies the changes to the databases.

First, we'll create a simple HTML page called dmo.htm. This code asks for the server name, database name, username and password. When submitted, it will post the results to schema.asp.

dmo.htm

<HTML>
<HEAD><TITLE>SQL Server DMO Sample</TITLE></HEAD>
<BODY>
<form name="frmLogin" action="schema.asp" method="post">
<table>
 <tr>
  <td align="right">Server Name:</td><td><input name="fServerName" size="10" tabindex="1"></td></tr>
 <tr>
  <td align="right">Database:</td><td><input name="fDatabaseName" size="10" tabindex="1"></td></tr>
 <tr>
   <td align="right">User Name:</td><td><input type="text" name="fUserName" size="10" tabindex="2"></td></tr>
 <tr>
  <td align="right">Password:</td><td><input type="password" name="fpassword" size="10" tabindex="3"></td></tr>
</table>
<input type="submit" value="Connect" tabindex="4"> <input type="reset" value="Reset"></td></tr>
</form>
</BODY>
</HTML>

schema.asp connects to the database using the information specified, and saves the login information using the Session object. Once connected, it lists all the stored procedures in that database.

schema.asp

<%@ Language=VBScript %>
<%Response.Buffer = True%>
<HTML>
<HEAD><TITLE>SQL Server DMO Sample</TITLE></HEAD>
<BODY>
<%
'The SQLDMO.DLL will registered on the web server The library for SQL2000 will allow you to issue commands against
'SQL2000, SQL7, MSDE. The SQL 7 will work against SQL7 and MSDE only
 Dim srv
 Dim sp
 
 'Referencing the SQLServer object to login in.
 set Srv = Server.CreateObject("SQLDMO.SQLServer")
 If Session("glDbName") = "" Then
  Session("glDbName") = Request("fDatabaseName")
  Session("glUserName") = Request("fUserName")
  Session("glPassword") = Request("fPassword")
  Session("glServerName") = Request("fServerName")
 End if
 
 'Connecting to the database. Using Server name, User name, and Password.
 Srv.Connect Session("glServername"), Session("glUserName"), Session("glPassword")
 
'Referencing the Storedprocedure object.
 Set sp = Server.CreateObject("SQLDMO.StoredProcedure")%>

<table border="1" cellspacing="0", cellpadding="0">
 <tr>
  <td width="200">Name</td><td width="50">Owner</td><td width="100">Type</td><td width="150">Created Date</td></tr>
 <tr> 
<%
'For each stored procedure in the database, get the information.  Notice I'm assuming the owner is dbo.  We will
'one row into an HTML table for every stored procedure within the database.'
 for each sp in Srv.Databases(cstr(session("glDbName")),"dbo").Storedprocedures%>
 <td><a href="alter.asp?fname=<%=sp.name%>"><%=sp.name%></a></td>
 <td><%=sp.owner%></td>
 <td><%=sp.Type%></td>
 <td><%=sp.createdate%></td></tr>
 <%Next%>
</table>
<%
'Releasing the objects
set sp = nothing
set srv = nothing
%>
</BODY>
</HTML>
<%Response.End%>


Next, we need to create a page that allows the user to edit the stored procedure. We'll call it alter.asp. This simply connects to the database, and provides a textarea displaying the contents of the stored procedure.

alter.asp

<%@ Language=VBScript %>
<%Response.Buffer = True%>
<HEAD><TITLE>SQL Server DMO Sample</TITLE></HEAD>
<%
Dim srv
set Srv = Server.CreateObject("SQLDMO.SQLServer")
'Connecting to the database. Using Server name, User name, and Password.
Srv.Connect Session("glServername"), Session("glUserName"), Session("glPassword")
%>
<form name="frmAlter" method="post" action="execute.asp">
<HTML>
<BODY>
<%'Use the stored procedure name to look into the database to retreive the text%>
<B>Make sure you change CREATE to ALTER if you are modifying the procedure</B>
<textarea name="fspText" ROWS="20" COLS="100">
<%=srv.Databases(session("glDbName"), "dbo").StoredProcedures(Cstr(Request("fName")),"dbo").Text%>
</TEXTAREA><br><br>
 <input type="button" value="Back" name="back" onClick="window.history.back(1)">
 <input type="Submit" value="Execute" name="btnSubmit">
 </form>
</BODY>
</HTML>
<%
'Release the object
set srv = nothing
Response.End%>

execute.asp simply saves these changes to the database again, and tells the user that the changes have been made.

execute.asp

<%@ Language=VBScript %>
<%Response.Buffer = True%>
<HTML>
<HEAD><TITLE>SQL Server DMO Sample</TITLE></HEAD>
<%
Dim srv

'Referencing the SQLServer object to login in.
Set Srv = Server.CreateObject("SQLDMO.SQLServer")

'Connecting to the database. Using Server name, User name, and Password.
Srv.Connect Session("glServername"), Session("glUserName"), Session("glPassword")

'Use ExecuteImmediate object to execute the command
srv.Databases(session("glDbName"), "dbo").ExecuteImmediate Request("fspText")

'Release the object
set srv = nothing
%>
<BODY>
The stored procedure was updated. <a href="schema.asp">Click here</a> to return to the list.
</BODY>
</HTML>
<%Response.End%>

Comments

  1. liselle
    17 Oct 2002 at 10:29
    Hi,

    I read your article and it is indeed an informative one.  However I am having some serious problems creating a Job Schedule in VB.NET.  I have managed to reduce the number of errors in the properties of the JobSchedule Object but I am still experiencing some difficulty...

    You said you would help with any questions soI appeal to you for help!!

    Regards,
    Liselle
  2. WeSam Abdallah
    01 Oct 2002 at 15:44

    Thank you for this article I didn't know about DMO before,it was really important to me, I had the same problem I am working on a project and I have to give and solve problems from time to time , please write more about DMO

  3. Developer Fusion Bot
    01 Jan 1999 at 00:00

    This thread is for discussions of SQL Distributed Management Objects.

Leave a comment

Sign in or Join us (it's free).

Related tags

visual basic, asp, db, sql server, sql
Carvin Wilson
AddThis

Related articles

Ask a question

Related discussion

Related podcasts

  • Stack Overflow: Podcast #28

    This is the twenty-eighth episode of the StackOverflow podcast, where Joel and Jeff discuss Windows Azure, SQL Server 2008 full text search, Bayesian filtering, porn detection, and project management &mdash; among other things. Jeff met the inestimable Joey DeVilla aka Accordion Guy...

Events coming up

  • Nov 19

    SQLBits V

    Newport, United Kingdom

    SQLBits is Europe's largest SQL Server conference, and SQLBits V will be the biggest and best yet. On November 19th we are holding a day of pre-conference seminars; on November 20th we have a pay-to-attend day of SQL Server 2008 and R2 content; and on Saturday November 21st we have our usual free community conference.

Languages

Web Development

Frameworks & Architecture

Other major sections

Cancel
We'd love to hear what you think! Submit ideas or give us feedback