Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Unanswered: dynamic sql and resource limitation issue on ASE 11.5

    Hey guys,

    I have an ASE 11.5 setup as a dev db server.

    We needed to write a procedure that incorporated dynamic sql.
    As you know ASE 11.5 as limitations to doing this.
    I found tricks on this website http://www.sypron.nl/dynsqlcis.html
    that allowed me to download some scripts and run them. These scripts would in turn create a system procedure called "sp_exec_dynsql"that works on the basis of a remote connection from your server to your server, basically calling on your server like it is a remote connection you're making. you execute your dynamic sql by calling this proc and then your variable that contains the sql string e.g

    exec sp_exec_dynsql @sql_string

    Now there are seeral limitations to this and they warn you. For example you might not be able to call the procedure concurrently in a
    high-volume, multi-user, critical production system, which ours really isn't but still we'd like to call it numerous times without issues.

    Well, I set this thing up and it worked fine for a while until this afternoon (after calling it like 100 times) when we got this error


    SQL Server Error on (CFDEV) in (sp_remotesql) Error:11219 at Line:0
    Message:Cannot open a connection to site 'CFDEV_MYSELF' because there are no free remote descriptors (RDES). Increase the configuration parameter for user connections and reboot the server.


    CFDEV_MYSELF being the name of the remote server although it exists as a local server on my ASE install

    I checked ASE 11.5 documention and this is what it had to say

    Explanation:

    Command failed due to resource limitation. Modify the command to fit available resources, retry when the resource is available, or contact your System Administrator to address the resource problem.



    I went ahead and increased the parameter for the user connections (from 150 to 300) and everything is fine now.

    My question is (esp. if someone here has add experience with this before) is there a way to lock other users while another user is executing the proc (only takes a sec to return results) or are there other ways to make sure that I don't run out of resources when this procedure is being executed heavily by concurrent users??????

    please help????????
    Last edited by Wale; 04-03-03 at 16:11.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You could lock the login while the procedure is executing -- that would return an error message to your client though.
    Thanks,

    Matt

Posting Permissions

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