Remote call to a procedure with Exec inside

Question

Hi

I have a VB application that calls a SQL Stored Procedure with an argument that is the name of another Stored Procedure.

I've been using these application without any problem till I try it with SQL Server 2005 Express SP4.

The called procedure stores the argument (that is the name of another procedure) inside a table and if this name is recognized as a valid stored procedure is executed.

My problem is that I call the procedure, the argument is stored inside the table but is not executed.

If simulate all this inside SQL it works fine.

It seems that when calling these procedure remotely the "Exec" that is inside this procedure and should execute another Stored procedure, just don't work.

Is this a security issue?

Regards

Answer 1

No, it looks like you missed something

CREATE PROCEDURE sp1

AS

CREATE TABLE #t VALUES (1)

EXEC sp2 @p=1

--Usage

EXEC sp1 ---will create a temporary table and insert the value 1 INTO, then it will execute a stored procedure sp2 with paranmeter value=1

What do you mean by "calling remote sp"

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Hi

Thanks for your fast reply.

When I say Calling remote sp i mean  calling an sp from a non SQL application, in my case is a VB application that calls the sp.

My code is something like this:

create procedure rsproc(@v1 varchar(50))

-- check if @v1 is a valid sp

          insert proc_sp select @v1

          exec(@v1)

          delete from proc_sp where appname=@v1

Running this script inside SQL, it works fine:

exec rsproc 'my_sp'

Procedure my_sp is executed without any problem and at the end I don't see it inside proc_sp table

If I execute rsproc from my vb application, I can check that procedure name argument is passed because inside table proc_sp I see 'my_sp'. But

the procedure 'my_sp' is not executed and the name is not deleted from proc_sp table.

Regards

Answer 3

Any help about this one?

I'm get stuck with this problem. Should not be difficult to recreate.

Thanks

Answer 4

Then it looks like rsproc failed, and an error should be returned to VB code. What is that error?

---

Serg

Answer 5

When trying to get the returned vb code, I change sp execution from Asynchronous to Synchronous. With this change SQL is executed without any problem.

However, my VB application will run several sps and cannot wait till one sp goes to the end.

Like I said before, with SQL Server 2005 Express I didn't get this problem, only with SQL Server 2008 Express. It seems that my vb code needs to wait till the Sp is executed. Does this makes any sense?

I was trying not to create a threat each time I launch a sp.

Answer 6

If you change it to asyncronous have you set up the event handler that detects the completion and get the return value there. 

---

Chuck

Answer 7

Look here for an example of how you would set up the callback functions

http://www.devx.com/dotnet/Article/26747/1954

---

Chuck