remote procedure and EXECUTE

Question

remote procedure and EXECUTE

RCoston

Mr or Mrs. 500

Points: 585
More actions
August 26, 2005 at 6:22 pm

#91003

Here is my code:
declare @p1 int, @p2 int, @server varchar(16), @dB varchar(16), @sproc_call varchar(2000), @cust_code varchar(25), @prod_code varchar(25)
set @server = 'WMSDB'
set @dB = 'pDEV'
set @cust_code = 'QFFLLC'
set @prod_code = '033789'
set @sproc_call =@server + '.' + @db + '..sp_QU_QFFLLC_sku_check_insert ''' + @cust_code + ''',''' + @prod_code + '''' +',T'
print @sproc_call
exec @p1 = @sproc_call
--exec @p1 = exec(@sproc_call)
--declare @p1 int
--exec @p1 = wmsdb.pdev..sp_QU_QFFLLC_sku_check_insert 'QFFLLC','033787',T
IF @p1 = 0
BEGIN
print 'the sproc returned zero'
END
ELSE
BEGIN
print 'the sproc returned a one'
END
Here is the error message I keep getting:
WMSDB.pDEV..sp_QU_QFFLLC_sku_check_insert 'QFFLLC','033789',T
Server: Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure 'pDEV..sp_QU_QFFLLC_sku_check_insert 'QFFLLC','033789',T'.
the sproc returned zero

HELP!!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

this could just be a syntax error - i ran this through the parser and changed it to:

declare @p1 int, @p2 int, @server varchar(16), @dB varchar(16), @sproc_call varchar(2000), @cust_code varchar(25), @prod_code varchar(25)

set @server = 'WMSDB'
set @dB = 'pDEV'
set @cust_code = 'QFFLLC'
set @prod_code = '033789'
set @sproc_call =@server + '.' + @db + '..sp_QU_QFFLLC_sku_check_insert ''' + @cust_code + ''',''' + @prod_code + '''' +',T'
print @sproc_call


exec @sproc_call
set @p1 = @@error

IF @p1 = 0
BEGIN
 print 'the sproc returned zero'
END
ELSE
BEGIN
 print 'the sproc returned a one'
END

**ASCII stupid question, get a stupid ANSI !!!**

RCoston Mr or Mrs. 500 Points: 585 More actions · Answer 2

Hi sushila,

Thanks for responding. I tried your code and I got the same error, it says:

WMSDB.pDEV..sp_QU_QFFLLC_sku_check_insert 'QFFLLC','033789',T

Server: Msg 2812, Level 16, State 62, Line 8

Could not find stored procedure 'pDEV..sp_QU_QFFLLC_sku_check_insert 'QFFLLC','033789',T'.

the sproc returned a one

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

sorry if i'm stating the obvious but are you sure the spelling of your stored procedure is correct ?!?!

have you double-checked in your sysobjects table in the database to make sure it's there and spelled exactly as you have it ?!

also...have you been able to directly execute this sp in the database ?!

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

RCoston - one more thing - enclose your exec @sproc_call within paranthesis..like so...

exec (@sproc_call)

**ASCII stupid question, get a stupid ANSI !!!**

RCoston Mr or Mrs. 500 Points: 585 More actions · Answer 5

Hi,

I'm sure of the spelling because this works

exec @p1 = wmsdb.pdev..sp_QU_QFFLLC_sku_check_insert 'QFFLLC','033787',T

You'll see this commented out ( -- ) in my original post.

My whole goal is to be able to turn the server name and database into variables that I can feed it as it is now in development and will eventually be put into production. This is one of three different places I would like it to read the server and database as variables.

WORKED!!! I just tried with parantheses, and it worked. I had to add some RAISEERROR statements in the procedure itself but I think I can make this work.

Thanks for your help.

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

Great - sorry about not mentioning the parantheses earlier...that's the first thing I should've noticed...

at any rate...better late than never

**ASCII stupid question, get a stupid ANSI !!!**