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