stored procedure help

[Maria]

Hi all!
I am in need of writing a few stored procedures.

The first one is to create a stored procedure to recover a database from
backup and the second one is to create a stored procedure to execute a
transaction log backup (even though I know this can be done through a
maintainence plan). Any help would be greatly appreciated.
Thanks in advance!

[-D-]

I have the following stored procedure: CREATE procedure app_get_alloc_ABR
@alloc_type varchar(20) , @ddo varchar(3), @ay varchar(4) as if
UPPER(@alloc_type) = 'STRATEGY' BEGIN select STRATEGY_NBR_DSCR,
STRTGY_ALLOC_AMT from DIM_STRTGY_ALLOC, DLIST_STRATEGY, DIM_DDO where
STRTGY_ALLOC_AY = @ay AND STRTGY_ALLOC_DDO_ID = DDO_ID AND DDO_DSCR_SHORT =
@ddo AND STRTGY_ALLOC_AMT <> 0.00 ORDER BY STRATEGY_NBR_DSCR END ELSE IF
UPPER(@alloc_type) = 'STAFFING' BEGIN select FTE_CLASS_DSCR, STAFF_ALLOC_COUNT
from dbo.DIM_STAFF_ALLOC, dbo.DIM_FTE_CLASS, DIM_DDO where STAFF_ALLOC_AY =
@ay AND STAFF_ALLOC_DDO_ID = DDO_ID and STAFF_ALLOC_FTE_CLASS_ID = FTE_CLASS_ID
AND DDO_DSCR_SHORT = @ddo AND STAFF_ALLOC_COUNT <> 0.00 ORDER BY
FTE_CLASS_DSCR END ELSE IF UPPER(@alloc_type) = 'BUDGET OBJECT' BEGIN select
BUDG_OBJT_TYPE_DSCR, BUDG_OBJT_ALLOC_AMT from dbo.DIM_BUDG_OBJT_ALLOC,
dbo.DLIST_BUDGET_OBJECT, DIM_DDO where BUDG_OBJT_ALLOC_AY = @ay AND
BUDG_OBJT_ALLOC_DDO_ID = DDO_ID and BUDG_OBJT_ALLOC_BUDG_OBJT_ID = BUDG_OBJT_ID
AND DDO_DSCR_SHORT = @ddo AND BUDG_OBJT_ALLOC_AMT <> 0.00 ORDER BY
BUDGET_OBJECT_NBR_DSCR END GO The resultset is different depending on what is
passed in. I haven't dealt we this particular type of situation and not sure
how I can bind the necessary values to the page. When I create the recordset
in Dreamweaver the recordset doesn't show any values to bind, which makes sense
because it depends on what is passed in. I'm not sure how to handle this type
of logic? Can anyone offer any help? Here is the recordset code: <% Dim
rsAlloAmts__alloc_type rsAlloAmts__alloc_type = 'xyz' If
(Request.Form('allo_type') <> '') Then rsAlloAmts__alloc_type =
Request.Form('allo_type') End If %> <% Dim rsAlloAmts__ddo rsAlloAmts__ddo =
'-1' If (Request.Form('ddomenu') <> '') Then rsAlloAmts__ddo =
Request.Form('ddomenu') End If %> <% Dim rsAlloAmts__ay rsAlloAmts__ay = '-1'
If (Request.Form('AY') <> '') Then rsAlloAmts__ay = Request.Form('AY') End
If %> <% Dim rsAlloAmts Dim rsAlloAmts_numRows Set rsAlloAmts =
Server.CreateObject('ADODB.Recordset') rsAlloAmts.ActiveConnection =
MM_DBConn_STRING rsAlloAmts.Source = '{call dbo.app_get_alloc_ABR('' +
Replace(rsAlloAmts__alloc_type, ''', '''') + '','' + Replace(rsAlloAmts__ddo,
''', '''') + '','' + Replace(rsAlloAmts__ay, ''', '''') + '')}'
rsAlloAmts.CursorType = 0 rsAlloAmts.CursorLocation = 2 rsAlloAmts.LockType = 1
rsAlloAmts.Open() rsAlloAmts_numRows = 0 %> I get an error on this line when
I try to determine if a resultset exists: <% If Not rsAlloAmts.EOF Or Not
rsAlloAmts.BOF Then %> Any help is appreciated. Thanks. -D-

[-D-]

I have several parameters that I need to pass into a stored procedure. Each
parameter holds a comma delimited list. I tried splitting the parameters and
executing the stored procedure doing the following: Dim CmtIdLoop CmtIdLoop =
Split(Request.Form('commentsID'),', ') revwStatLoop =
Split(Request.Form('review_status'),', ') abrstatLoop =
Split(Request.Form('abr_status'),', ') commentsLoop =
Split(Request.Form('comments'),', ') For l = 0 to Ubound(CmtIdLoop) s =
CmtIdLoop(l) t = revwStatLoop u = abrstatLoop v = commentsLoop Dim
cmd_SpUpdateComments__cmtid cmd_SpUpdateComments__cmtid = -1
if(Request('commentsID') <> '') then cmd_SpUpdateComments__cmtid = s Dim
cmd_SpUpdateComments__statusrevw cmd_SpUpdateComments__statusrevw = ''
if(Request('revw_status') <> '') then cmd_SpUpdateComments__statusrevw = 't'
Dim cmd_SpUpdateComments__statusabr cmd_SpUpdateComments__statusabr = ''
if(Request('abr_status') <> '') then cmd_SpUpdateComments__statusabr = 'u' Dim
cmd_SpUpdateComments__comments cmd_SpUpdateComments__comments = ''
if(Request('comments') <> '') then cmd_SpUpdateComments__comments = 'v' set
cmd_SpUpdateComments = Server.CreateObject('ADODB.Command')
cmd_SpUpdateComments.ActiveConnection = MM_DBConn_STRING
cmd_SpUpdateComments.CommandText = 'dbo.ABR_REVWR_CMTS_UPDATE'
cmd_SpUpdateComments.CommandType = 4 cmd_SpUpdateComments.CommandTimeout = 0
cmd_SpUpdateComments.Prepared = true cmd_SpUpdateComments.Parameters.Append
cmd_SpUpdateComments.CreateParameter('@RETURN_VALU E', 3, 4)
cmd_SpUpdateComments.Parameters.Append
cmd_SpUpdateComments.CreateParameter('@cmtid', 3,
1,4,cmd_SpUpdateComments__cmtid) cmd_SpUpdateComments.Parameters.Append
cmd_SpUpdateComments.CreateParameter('@statusrevw' , 200,
1,50,cmd_SpUpdateComments__statusrevw) cmd_SpUpdateComments.Parameters.Append
cmd_SpUpdateComments.CreateParameter('@statusabr', 200,
1,50,cmd_SpUpdateComments__statusabr) cmd_SpUpdateComments.Parameters.Append
cmd_SpUpdateComments.CreateParameter('@comments', 200,
1,8000,cmd_SpUpdateComments__comments) cmd_SpUpdateComments.Execute() Next
The parameters I passed in are the following: abr_status = ONHOLD, INPROCESS
commentsID = 97, 101 review_status = WAITING, INREVIEW comments = This is a
test, This is another comments test I receive the following error:
'Application uses a value of the wrong type for the current operation' The
error is thrown on the following line: cmd_SpUpdateComments.Parameters.Append
cmd_SpUpdateComments.CreateParameter('@statusabr', 200,
1,50,cmd_SpUpdateComments__statusabr) Not sure what is causing the error? Any
help is appreciated. Thanks. -D-

[-D-]

Anyone have any suggestions or ideas? I'm lost on this? I'm not getting an
error, but nothing is getting written into the database? Here is the code to
execute the procedure: Dim CmtIdLoop CmtIdLoop =
Split(Request.Form('commentsID'),', ') revwStatLoop =
Split(Request.Form('review_status'),', ') abrstatLoop =
Split(Request.Form('abr_status'),', ') commentsLoop =
Split(Request.Form('comments'),', ') For l = 0 to Ubound(CmtIdLoop) s =
(CmtIdLoop(l)) t = (revwStatLoop(l)) u = (abrstatLoop(l)) v = (commentsLoop(l))
Dim cmd_SpUpdateComments__cmtid cmd_SpUpdateComments__cmtid = s Dim
cmd_SpUpdateComments__statusrevw cmd_SpUpdateComments__statusrevw = t Dim
cmd_SpUpdateComments__statusabr cmd_SpUpdateComments__statusabr = u Dim
cmd_SpUpdateComments__comments cmd_SpUpdateComments__comments = v set
cmd_SpUpdateComments = Server.CreateObject('ADODB.Command')
cmd_SpUpdateComments.ActiveConnection = MM_DBConn_STRING
cmd_SpUpdateComments.CommandText = 'dbo.ABR_REVWR_CMTS_UPDATE'
cmd_SpUpdateComments.CommandType = 4 cmd_SpUpdateComments.CommandTimeout = 0
cmd_SpUpdateComments.Prepared = true cmd_SpUpdateComments.Parameters.Append
cmd_SpUpdateComments.CreateParameter('@RETURN_VALU E', 3, 4)
cmd_SpUpdateComments.Parameters.Append
cmd_SpUpdateComments.CreateParameter('@cmtid', 3,
1,4,cmd_SpUpdateComments__cmtid) cmd_SpUpdateComments.Parameters.Append
cmd_SpUpdateComments.CreateParameter('@statusrevw' , 200,
1,50,cmd_SpUpdateComments__statusrevw) cmd_SpUpdateComments.Parameters.Append
cmd_SpUpdateComments.CreateParameter('@statusabr', 200,
1,50,cmd_SpUpdateComments__statusabr) cmd_SpUpdateComments.Parameters.Append
cmd_SpUpdateComments.CreateParameter('@comments', 200,
1,8000,cmd_SpUpdateComments__comments) cmd_SpUpdateComments.Execute() Next
Thanks, -D-