Professional Web Applications Themes

stored procedure help - Dreamweaver AppDev

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!...

  1. #1

    Default Stored Procedure Help

    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!

    Maria Guest

  2. #2

    Default stored procedure help

    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- Guest

  3. #3

    Default stored procedure help

    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- Guest

  4. #4

    Default Re: stored procedure help

    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-

    -D- Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. Using a stored procedure
    By MarkWright in forum Coldfusion Database Access
    Replies: 13
    Last Post: April 15th, 05:53 PM
  3. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  4. Stored procedure?
    By SG via DotNetMonster.com in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 23rd, 01:06 PM
  5. help with a stored procedure
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 3rd, 05:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139