SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP Error : Microsoft][SQL Native Client][SQL Server]Could not find stored procedure...


BCP Error : Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.EXTGEN_InitSessionContextSp'.

Author
Message
skb 44459
skb 44459
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 297
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
skb 44459
skb 44459
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 297
There is a code in the calling procedure which checks if object 'dbo.EXTGEN_InitSessionContextSp' exists. And it does not exist.

IF OBJECT_ID(N'dbo.EXTGEN_InitSessionContextSp') IS NOT NULL
BEGIN
-- Some code
RETURN 0
END
---------------------------------------------------
SQLState = 37000, NativeError = 2812
Error = [Microsoft][SQL Native Client][SQL Server]Could not find stored procedur
e 'dbo.EXTGEN_InitSessionContextSp'.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15133 Visits: 14396
It looks like you are fully-qulaifying the call to the proc, but not the existence check. In your sample code try replacing this:

IF OBJECT_ID(N'dbo.extgen_ki_TestLoop1') IS NOT NULL



with this:

if exists (select * from Dev1_PHIL_App.sys.objects where name = N'extgen_ki_TestLoop1' and schema_id = 1)



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
skb 44459
skb 44459
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 297
Thanks it does work with the solution.

But we have hundred's of procedure provided by our ERP vendor which we can not change technically.
Is there a work arounnd for
IF OBJECT_ID(N'extgen_ki_TestLoop1') IS NOT NULL

Thanks
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15133 Visits: 14396
Another option:

IF OBJECT_ID(N'Dev1_PHIL_App.dbo.extgen_ki_TestLoop1') IS NOT NULL



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40450 Visits: 38567
Try this bcp command. I moved the name of the database to the -d switch:

bcp "exec dbo.Rpt_JobTransactionsSp 'SRMQDIC','ROD','B','HNS','0','B',
null,null,null,null,'000','9999','1/1/2013','3/17/2013',null,null,null,null,null
,null,null,null,'T',null,null,'1','0','1033'" Queryout "c:\temp\query.txt" -T -d PHIL_App
-c -t^| -Sphlsqlsl01

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
skb 44459
skb 44459
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 297
got error

output
bcp: unknown option d

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15133 Visits: 14396
Sounds like you might be calling the SQL 2005 version of bcp. What version of SQL is your database on?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
skb 44459
skb 44459
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 297
SQL 2005. Our Database Server is 2005 . Is there a way to use -d option with 2005 server ?
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15133 Visits: 14396
skb 44459 (3/22/2013)
SQL 2005. Our Database Server is 2005 . Is there a way to use -d option with 2005 server ?

Unfortunately, no.

Can you help us understand the setup a little more about this statement:

But we have hundred's of procedure provided by our ERP vendor which we can not change technically.
Is there a work arounnd for
IF OBJECT_ID(N'extgen_ki_TestLoop1') IS NOT NULL


Why are you trying to check for the existence of a stored procedure in a database other than the one you are in? And if that is needed, why are you opposed to fully-qualifying the existence-check?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search