Sign in to follow this  
Followers 0
suthern101

[Solved] "Operation not allowed when object is closed" on valid result

1 post in this topic

#1 ·  Posted (edited)

I've got a curious problem: (solved: see bottom of post)

When I run SQL which calls a stored function, I get "This operation is not allowed when the object is closed"

But when I copy that exact same SQL to the clipboard and run it on SQL server, I get a result without any problem.

I can also change the SQL code to something much simpler, and the error vanishes.

$sSQL = " declare @result int, @p11 varchar(255) " & @CRLF & _
    " set @p11='No Error' " &  @CRLF &   _
    " exec @result = uspWFInitializeChecklist @Company=" & $iCo & "," & _
    "@Template='" & $v_template & "'," & _
    "@SourceType='" & $sourcetype & "'," & _
    "@Status=" & $status & "," & _
    "@Name='" & $v_name & "'," & _
    "@EnforceOrder='" & $enforceorder & "'," & _
    "@UseEmail='" & $useemail & "'," & _
    "@IsPrivate='" & $isprivate & "'," & _
    "@AssignedTo='" & $v_username & "'," & _  
    "@Level=" & $v_level & "," & _  
    "@DueDate='" & $v_date & "'," & _ 
    "@DueTime='" & $v_time & "'," & _  
    "@SendNotification='" & $sendnotification & "',@msg=@p11 output " & @CRLF & _
    "select @result Result, @p11 Message"
    
 ;$sSQL = 'Select TOP 5 * from bJCCD with(nolock)'  ; this works without a problem

  
 Local $iRows         ;Number of rows
 Local $iColumns    ;Number of columns
 Local $aResult
 
 SQL_Connect()
 
 ClipPut ( $sSQL )

  ;if _SQL_GetTable2D(-1, $sSQL, $aResult, $iRows, $iColumns) = $SQL_ERROR Then 
  if _SQL_QuerySingleRow(-1, $sSQL, $aResult) = $SQL_ERROR Then
    Msgbox(0 + 16 +262144,"Error","SQL Error: " & $SQLErr  & _SQL_GetErrMsg() )
  Else
    _ArrayDisplay($aResult)
  EndIf
  
  
  _SQL_Close()

Does anyone have any idea why? The result when run on the server itself displays in one row with two columns. No errors.

Things I've tried:

  • Searching google (and these forums)
  • Closing the connection at the end of the first query (in GetVPChecklists), then opening it again right before we attempt to run the more difficult stored procedure

This error happens in _sql.au3 at line 467 when $objquery.eof is checked to see if there is any data. This is immediatly after _SQL_Execute was called which did not return an error.

I've been scratching my head for a couple of days now, and would appriciate any pointers.

Thanks!

Edit: This code

$sSQL = " declare @p11 varchar(255) set @p11='No Error' select @p11 "

also pops up the same "operation not allowed" error.

Edit: Solution!!

Putting

SET NOCOUNT ON

at the beginning of my query solved the problem. Perhaps someone else will find this post while looking for a solution. My full query now looks like this:

$sSQL = "SET NOCOUNT ON declare @result int, @p11 varchar(255) " & @CRLF & _
    " set @p11='No Error' " &  @CRLF &   _
    " exec @result = uspWFInitializeChecklist @Company=" & $iCo & "," & _
    "@Template='" & $v_template & "'," & _
    "@SourceType='" & $sourcetype & "'," & _
    "@Status=" & $status & "," & _
    "@Name='" & $v_name & "'," & _
    "@EnforceOrder='" & $enforceorder & "'," & _
    "@UseEmail='" & $useemail & "'," & _
    "@IsPrivate='" & $isprivate & "'," & _
    "@AssignedTo='" & $v_username & "'," & _  
    "@Level=" & $v_level & "," & _  
    "@DueDate='" & $v_date & "'," & _ 
    "@DueTime='" & $v_time & "'," & _  
    "@SendNotification='" & $sendnotification & "',@msg=@p11 output " & @CRLF & _
    "select @result Result, @p11 Message"
Edited by suthern101

Share this post


Link to post
Share on other sites



Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • Valnurat
      By Valnurat
      I hope my title is good enough.
      I'm using the ADO UDF and I have question regarding editing SQL records with this UDF.
      The owner of the UDF suggested an idea, but maybe there is another trix.
    • Bowmore
      By Bowmore
      A few months ago someone posted a function that scanned a PC registry and returned a list of all the available ADODB connections installed. I've been searching on and off all afternoon trying to locate it. Does anyone remember which topic this was in? As far as my failing memory goes it was a post part way through an ADO related topic.
      Thanks
       
    • mLipok
      By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      This is modifed version of _sql.au3 UDF.
       

      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH
       
      This is first public release , and still is as BETA
       
       
      DOWNLOAD LINK (in download section): 
       

       
      Have fun,
      mLipok
       
       
      EDIT: 2016-06-03
      Below some interesting topics about databases:
       
       
      EDIT 2016/07/04:
      For more info about ADO look here:
      https://www.autoitscript.com/wiki/ADO
       
       
    • mLipok
      By mLipok
      Currently I'm working on MS SQL >> PostgreSQL migration.
      Here are some of interesting Video tutorials about PostgreSQL
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
    • mLipok
      By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      Support topic is here: http://www.autoitscript.com/forum/index.php?showtopic=180850
       
      This UDF is modifed version of _sql.au3 UDF.

      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH