04 April 2012 9:52
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?
04 April 2012 9:56Penjawab Pertanyaan
No, it looks like you missed something
CREATE PROCEDURE sp1
CREATE TABLE #t VALUES (1)
EXEC sp2 @p=1
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/
04 April 2012 13:55
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
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.
13 April 2012 11:16
Any help about this one?
I'm get stuck with this problem. Should not be difficult to recreate.
13 April 2012 11:36
Then it looks like rsproc failed, and an error should be returned to VB code. What is that error?
16 April 2012 17:03
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.
16 April 2012 17:05If you change it to asyncronous have you set up the event handler that detects the completion and get the return value there.
16 April 2012 17:07
Look here for an example of how you would set up the callback functions
- Ditandai sebagai Jawaban oleh Kalman TothMicrosoft Community Contributor, Moderator 20 April 2012 19:54