Here is the small example i created and gives me same results.
Here i am running procedure ki_TestLoop2 which is calling ki_TestLoop1.
In ki_TestLoop1 i have a code which checks if extgen_ki_Testloop1 exists (which does not exist)
/********** ki_TestLoop1 *************/
create PROCEDURE [dbo].[ki_TestLoop1] (
@PCustNum CustNumType = NULL
) AS
select 'shashi'
/*************** ki_TestLoop2 ************/
CREATE PROCEDURE [dbo].[ki_TestLoop2] (
@PCustNum CustNumType
) AS
DECLARE @rc int
DECLARE @P1CustNum CustNumType
IF OBJECT_ID(N'dbo.extgen_ki_TestLoop1') IS NOT NULL
BEGIN
EXECUTE @rc = [Dev1_PHIL_App].[dbo].[extgen_ki_TestLoop1] @P1CustNum
RETURN 0
END
EXECUTE @rc = [Dev1_PHIL_App].[dbo].[ki_TestLoop1] @P1CustNum
select 'shashi'
/*********************************** Main Code ********************************/
DECLARE @Cmd VarChar(4000)
DECLARE @SProcName VarChar(40)
DECLARE @SP_Parm1Value VarChar(40)
DECLARE @SP_Parm2Value VarChar(4000)
set @SProcName = 'ki_TestLoop2'
set @SP_Parm2Value = '|sds|'
set @SP_Parm2Value = REPLACE (@SP_Parm2Value,'|', '''')
-- Build BCP Command
set @CMD = ''
Set @Cmd = @Cmd + 'bcp "exec ' + db_name()
Set @Cmd = @Cmd + '..' + @SProcName + ' '
Set @Cmd = @Cmd + @SP_Parm2Value + '"'
Set @Cmd = @Cmd + ' Queryout '
+ ' "c:\temp\query.txt" '
+ ' -T -c -t^| -S '+ @@servername
SELECT @Cmd
--output looks like this
EXEC master..xp_cmdshell @cmd
Gives Error
SQLState = 37000, NativeError = 2812
Error = [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'Dev1_PHIL_App.dbo.extgen_ki_TestLoop1'.
NULL