May 11, 2007 at 12:51 am
We are want to call a stored proc using the fully qualified name, however we want to be able to set the servername, database, owner dynamically to call the stored proc.
So Given:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create Proc [dbo].[testreturnstatus]
@value int = 0
as
If @Value <> 0 Return (1)
Return (0)
Go
And Then:
Declare @ReturnStatus int
@ReturnStatus = [servername].[database].[owner].testreturnstatus 1
If @ReturnStatus <> 0 Print 'Yahoo it works'
Becomes:
Declare
@servername sysname
Declare @database sysname
Declare @owner sysname
Declare @string nvarchar(4000)
Declare @ReturnCode int
Set @servername = 'MyServer'
Set @database = 'MyDb'
Set @owner = 'dbo'
Set @string = (N'@ReturnCode = ' + QUOTENAME(@servername) + '.' + QUOTENAME(@database) + '.' + QUOTENAME(@owner) + '.' + 'testreturnstatus 1')
Exec (@string)
IF @ReturnCode <> 0 Print 'Yahoo it works'
Problem:
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@ReturnCode'.
Does not work; it appears that EXECUTE runs in a separate process and does not have access to the local veriable @ReturnStatus.
Any ideas, thoughts, slaps upside the head ??
Thanks in advance
May 11, 2007 at 4:17 am
I think you'll have to do a workaround and run this with some sort of script that gets the return value (using openrowset or openquery), then selects that return value so that you have a recordset sent back on the pipes. I have no working code to show you but I'm sure there's a way to make this work somehow.
May 11, 2007 at 4:23 am
Read BOL about sp_executesql.
_____________
Code for TallyGenerator
May 11, 2007 at 4:36 am
It works for remote queries too?
Do you have restrictions such has you must have the linked server setup?
May 11, 2007 at 4:55 am
Yes, it works.
Just today there was a reference to this article:
http://support.microsoft.com/kb/314520
_____________
Code for TallyGenerator
May 11, 2007 at 5:13 am
Ok lets see....
1.) You need to read the question as posted, it has nothing to do with the article (but thank you), the article covers basic dynamic queries.
sp_executesql has the same issue, and the only difference between Execute and sp_executesql are the params.
I have no problems executing remote queries dymanic or static. The issue is getting the return status from a stored proc that is executed as a dynamic string.
The query runs fine without attempting to get the return status. When getting the return status I get a local var declaraion error as it seems the exec can not see the declaration of the local variable. I am not sure but it seems that it is running in a separate process.
I can come up with a work-around using RAISERROR, just want to be uniform and always test the success of a stored proc using the return status.
2. ) Yes a linked server is required anytime you execute a remorte stored proc
In any case, I thank you for your quick replys.
May 11, 2007 at 5:27 am
That should give you an idea of where to start with sp_execsql :
IF @NewLogSize IS NOT NULL
BEGIN
SET @SizeTooSmall = 0
SET @NSQL = 'SELECT @SizeTooSmall = COUNT(*) FROM [' + @DBName + ']..SysFiles WHERE FileID = 2 AND Size >= 128 * ' + CONVERT(VARCHAR(20), @NewLogSize)
IF @Debug = 1
PRINT 'Check log file size = ' + @SQL
EXEC sp_executesql @NSQL, N'@SizeTooSmall INT OUTPUT', @SizeTooSmall OUTPUT
IF @SizeTooSmall = 1 --New size smaller or equal to currentsize
BEGIN
SET @ErrMsg = @ErrMsg + 'The new size of the log file (@NewLogSize) is smaller or equal to the current size of the data file.' + @VbCRLF
END
END
IF @ErrMsg > ''
GOTO ExitWithErrors
May 11, 2007 at 5:31 am
You must declare your variable as OUTPUT parameter for sp_executesql.
I believe it's the only bit you're missing.
_____________
Code for TallyGenerator
May 11, 2007 at 12:03 pm
I have run into this personally, and it's a fustrating thing.
try something along the following
declare @sql varchar(256), @Foo int
SET @sql = 'testreturnstatus 0'
Execute @Foo= sp_executesql @sql
select @Foo --this should be your expected return value
SET @err = @@error
SELECT @err as "ERROR1" --this will be the result of the exec statement
May 11, 2007 at 1:38 pm
Ok maybe I am not making myself clear.
1.) Don't want to use an output param, want the Return Status, if you don't know what that is it is what is returned from the RETURN (n) statement. All system stored procs set a return status to indicate success or failure.
2.) Can't use the error var because the RETURN (n) does not set the err var, and the very use of the RETURN (n) statment, as with any executeable line of code clears the err var.
3.) I am not about to rewrite system stored procs or create my own flavors. All system stored procs use the return status to indicate success or failure. You CAN NOT determine the success or failure or any errors from a system stored proc (or any stored proc that uses the Return Status), because of the resaon in number 2.
Please if you don't really know the anwser, I would rather you didn't try I keep coming back here thinking someone has answered the question that I acutally posted.
Thank You
PS
We're talking with MS now, I will update you. Just didn't want to burn one of our MSDN support incidents if I didn't have to.
May 11, 2007 at 3:16 pm
Dude it's the same code. I've not tested this but I'm pretty sure it will work :
Declare @NSQL AS NVarchar(4000)
DECLARE @Return AS INT
set @NSQL = 'EXEC @Return = linkedsvr.db.dbo.spname...'
EXEC sp_executesql @NSQL, N'@Return INT OUTPUT', @Return OUTPUT
Let us know what they come up with!!!
May 11, 2007 at 4:51 pm
if you want to use the return status programatically you might have to use the above suggested solutions, but if you want to see it when you run the stored proc then maybe you can try this
create
proc cp_TestReturn
as
select
5
return
2
------------
declare
@STR nvarchar(1000)
set @STR = N'DECLARE @return_value int EXEC @return_value = [dbo].[cp_TestReturn]'
set
@STR = @STR + 'SELECT ''Return Value'' = @return_value'
execute
sp_executesql @STR
May 11, 2007 at 4:58 pm
Vetran and Ninja had the closest solution!!!
MS just came back with this (took them all day so I don't feel so bad) ....
The solution is to nest the execute and sp_execute, apparently sp_executesql has access to the Execute namespace and is able to return these to the local space.
On the surface this sure seems like a kluge, but it works:
The key is including the Execute statement in the dynamic string and calling the Execute statement by having the Execute Statement call the sp_executesql that then executes the dymanic Execute..... (man thats a lot of executes!!). But it works....
Declare @servername sysname
Declare @database sysname
Declare @owner sysname
Declare @string nvarchar(4000)
Declare @ReturnCode int
Set @servername = 'srvsqlias03'
Set @database = 'NAS'
Set @owner = 'dbo'
Set @string = (N'Execute @ReturnCode = ' + QUOTENAME(@servername) + '.' + QUOTENAME(@database) + '.' + QUOTENAME(@owner) + '.' + 'testreturnstatus 0')
print @string
Exec sp_executesql @string, N'@ReturnCode int Output', @ReturnCode = @ReturnCode output
Select @ReturnCode
Thanks guys for all your input... hope I didn't sound TOO frustrated !!!
May 11, 2007 at 5:00 pm
Sorry Ninja... missed your post....
YOU WERE CORRECT !!!!!
THANK YOU !!!!!!
May 11, 2007 at 5:35 pm
NP, I had plenty of oversights like this one in the past... too bad you had to waste a ticket for this one.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply