Professional Web Applications Themes

Doozy: Stored procedure feedback during transaction - Microsoft SQL / MS SQL Server

Tom, "Tom" <com> wrote in message news:google.com...  There is no need to step out of the transaction. Update a status table row as execution progresses. Perform DIRTY reads (nolock) on that table from another thread. Doesn't help you if the transaction gets rolled back, but I presume you will generate an error message for this anyway. Useful things to write a percent complete, and a status message. If you want to get fancier, some of the output of sp_who may be useful to combine. HTH AJ...

  1. #1

    Default Re: Doozy: Stored procedure feedback during transaction

    Tom,


    "Tom" <com> wrote in message news:google.com... 

    There is no need to step out of the transaction. Update a status table row
    as execution progresses. Perform DIRTY reads (nolock) on that table
    from another thread. Doesn't help you if the transaction gets rolled back,
    but I presume you will generate an error message for this anyway.
    Useful things to write a percent complete, and a status message. If you want
    to get fancier, some of the output of sp_who may be useful to combine.

    HTH
    AJ


    Andrew Guest

  2. #2

    Default Re: Doozy: Stored procedure feedback during transaction

    > as execution progresses. Perform DIRTY reads (nolock) on that table 

    Thanks AJ, that sounds like a viable solution. How do you implement
    this? is it something in ADO? In the stored proc? both?

    I've looked the Lock Type enums in ado, and none seem to speak about
    dirty reads. SQL Books Online describes what a dirty read is, but not
    how to do it.

    Thanks
    tom
    Tom Guest

  3. #3

    Default Re: Doozy: Stored procedure feedback during transaction

    "Tom" <com> wrote in message
    news:google.com... [/ref]
    back, 

    Check the doentation for SELECT and look for "table hints" on the FROM
    clause. I think what you are after is READUNCOMMITTED

    Steve Troxell
    Krell Software
    Professional Database Development Tools for MS SQL Server
    http://www.krell-software.com


    Steve Guest

  4. #4

    Default Re: Doozy: Stored procedure feedback during transaction

    Well I figured out how to use the WITH (NOLOCKS) option on a select
    statement - I wasn't familiar with that one.

    However, I am facing another problem that is driving my nuts. The
    above solution seems to work... if the stored procedures don't take
    long to run. But I'll back up and give more background:

    VB app calls a single stored proc (spTransferData). spTransferData in
    turn calls dozens of other stored procedures, which do inserts,
    updates, selects and deletes across multiple servers. It also creates
    several temp tables. All this is done inside a distributed
    transaction. This may or may not play a factor in the problem:

    Pseudo-code for spTransferData:

    update statustable 'step 1'
    exec spStep1

    update statustable 'step 2'
    exec spStep2

    update statustable 'step 3'
    exec spStep3

    .... etc ... there are 11 steps

    ====

    Now, spStep1 calls several other stored proc's inside of it.
    Here's the VB code:

    strProcessId = NewProcessId(sReturnErrorMessage)

    ' Start the transfer stored proc ansynchronously
    strConnect = "DSN=FIRRepos"
    Set objConn = GetADOConnection(strConnect)
    Set objCmd = New ADODB.Command
    Set objCmd.ActiveConnection = objConn
    objCmd.CommandType = 4
    objCmd.CommandText = "spTransferFirData"
    objCmd.CommandTimeout = 5400
    objCmd.Parameters("remoteservername").Value = msSourcePC
    objCmd.Parameters("processid").Value = strProcessId
    objCmd.Execute
    'objCmd.Execute , , adAsyncExecute

    ' Open up the Status Process stored proc
    Set objStatusConn = GetADOConnection(strConnect)
    Set objStatusCmd = New ADODB.Command
    'Set rsStatus = New ADODB.Recordset
    objStatusConn.CursorLocation = adUseServer
    'rsStatus.CursorLocation = adUseClient
    Set objStatusCmd.ActiveConnection = objConn
    objStatusCmd.CommandType = 4
    'objStatusCmd.CommandText = "spTransferFirDataProcessStatusSelect"
    objStatusCmd.CommandTimeout = 180
    'objStatusCmd.Parameters("processid").Value = strProcessId
    'rsStatus.Open objStatusCmd, , adOpenForwardOnly, adLockReadOnly

    'intInterval = 1
    'intStatusRecord = 0

    ' While the transfer is executing, constantly check for updates to
    the status
    'While objCmd.State = adStateExecuting

    'rsStatus.Requery
    'If rsStatus.RecordCount > 0 Then
    ' Move to the most recent record
    ' rsStatus.Move intStatusRecord
    ' While Not rsStatus.EOF
    ' ChangeTransferStatus rsStatus!StatusMessage
    ' Update the most recent record
    ' intStatusRecord = intStatusRecord + 1
    ' rsStatus.MoveNext
    ' Wend
    'End If

    ' Wait specified amount of time before checking again
    'intTimeInSecs = Timer
    'While Timer < intTimeInSecs + intInterval: Wend

    'Wend

    ' Wait another couple seconds
    'intTimeInSecs = Timer
    'While Timer < intTimeInSecs + 10: Wend

    ' Check the status one more time
    'rsStatus.Requery
    'If rsStatus.RecordCount > 0 Then
    ' rsStatus.Move intStatusRecord
    ' While Not rsStatus.EOF
    ' ChangeTransferStatus rsStatus!StatusMessage
    ' rsStatus.MoveNext
    ' Wend
    'End If

    ' Purge all the status updates
    'objStatusCmd.Cancel
    objStatusCmd.CommandText = "spTransferFirDataProcessStatusPurge"
    objStatusCmd.Parameters("processid").Value = strProcessId
    objStatusCmd.Execute

    =====

    ChangeTransferStatus() shows the status on screen in a text box. What
    inevitable happens is that it will show the status incremently up to
    step 5. Steps 6 - 11 are dumped at the end, as if they weren't written
    or something until the stored proc finished. However, when I reduce
    some of the code (for testing purposes) from the step5 stored proc, it
    shows everything incrementaly.

    So the question is, would the amount of code or length of time it
    takes to run code cause ADO not be able to read from the status table?

    t
    Tom Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. MS SQL stored procedure
    By lfsxdth in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 23rd, 02:14 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

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