By
Carvin Wilson, published on 25 Oct 2001
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%>
|
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
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
This thread is for discussions of SQL Distributed Management Objects.