Unanswered: dynamic sql and resource limitation issue on ASE 11.5
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
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??????