Running a Stored procedure

[DirtDawg]

this is my stored procedure
create table tbl_control (tbl_sqnc int , tbl_nm varchar(50))

And the proc code could be something like this.


create proc sp_del_tables @dbname varchar(30) , @owner varchar(30)
as
declare @tbl_nm varchar(50) , @sql varchar(200)

declare del_curs cursor for
select tbl_nm from tbl_control order by tbl_sqnc

open del_curs
fetch del_curs into @tbl_nm

while @@fetch_status = 0
begin
set @sql = 'truncate table '+@dbname+'.'+@owner+'.'+@tbl_nm
execute(@sql)
fetch del_curs into @tbl_nm
end

close del_curs
deallocate del_curs

I would like to access this via an asp page. by using a click of a button.

Can anyone help me thanks in advance.

 

[nicklieb]

Somethins like this. juts make the button go to a new page with the code in it.


You need to make you objConn object to connect to the database. You can add in parameters to the stored prodeure as well.

set cmObj = Server.CreateObject("ADODB.Command&quot
cmObj.ActiveConnection = objConn
cmObj.CommandText = "sp_del_tables"
cmObj.CommandType = adCmdStoredProc

add parameters like this

cmObj.Parameters.Append cmObj.CreateParameter("@some_id", adInteger, adParamInput, 4, some_id)

 

[DirtDawg]

This is the code on the asp
<% @ LANGUAGE=&quot;VBSCRIPT&quot; %>
<%
Dim objConn
dim cmObj
SET objConn = Server.CreateObject(&quot;ADODB.Connection&quot
set cmObj = Server.CreateObject(&quot;ADODB.Command&quot
objConn.Open &quot;DSN=bds&quot;, &quot;user&quot;, &quot;passwd&quot;
cmObj.ActiveConnection = objConn
cmObj.CommandText = &quot;sp_del_tables&quot;

cmObj.Parameters.Append cmObj.CreateParameter(&quot;@dbname&quot;, adVarChar, adParamInput, 4, DBS)
cmObj.Execute
%>

I am receiving the follwing error when I run this command
Error Type:
ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/LOG/Report/del1.asp, line 11


I have a table setup already with the information in it. the table name is tbl_control and 2 columns with info. ! tbl_sqnc and tbl_nm. I am trying to delete 3 databases with 250 tables each at one time. Thanks in adavance.

 

[zakman]

It could be that the constants that you have used
in your .Append statement have not been defined,
such as adVarChar and adParamInput. Usually these
are defined in an include file known as adovbs.inc

Hope this helps,
Kevin

 

[DirtDawg]

Ok I have added the file but it still say that

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'sp_del_tables' expects parameter '@dbname', which was not supplied.
/LOG/Report/del1.asp, line 15


Thanks

 

[nicklieb]

Hunnicut,

check to see that the varirable is a corect type, you can use the Vartype function

a good explanation is here

http://www.devguru.com/Technologies/vbscript/quickref/vartype.html

one thing that I can't see is where the var DBS is coming from. One thing that I always do is to check and recheck every variable you are using.

A good way to see if the Stored procedure is working properly is to use the query analyzer.
eg.

sp_del_tables dbname owner

( where dbname and owner are real names) it gives good feeback about how many rows are affected and gives full results too

Hope this helps

 

[ISPrincess]

I have found that you need to include single quotes in your string params like this:
(&quot;'&quot; & @dbname & &quot;'&quot;, ......


Try that...

 

[ISPrincess]

Plus, I would like to add one other thing. The following excerpt is from the link below.


Do not prefix your stored procedure names with &quot;sp_&quot;. The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. So you can really save time in locating the stored procedure by avoiding the &quot;sp_&quot; prefix

http://www.sql-server-performance.com/vk_sql_best_practices.asp