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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » BCP Error : Microsoft][SQL Native Client][SQL...

20 posts, Page 2 of 2

««1 2

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

Rate Topic

Display Mode

Topic Options

Author

Message

skb 44459

Posted Thursday, March 21, 2013 12:58 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:58 PM
Points: 50, Visits: 106

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

Post #1433985

skb 44459

Posted Thursday, March 21, 2013 1:03 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:58 PM
Points: 50, Visits: 106

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'.

Post #1433988

opc.three

Posted Thursday, March 21, 2013 1:07 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 6,696, Visits: 11,715

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1433990

skb 44459

Posted Thursday, March 21, 2013 1:31 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:58 PM
Points: 50, Visits: 106

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

Post #1434005

opc.three

Posted Thursday, March 21, 2013 1:50 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 6,696, Visits: 11,715

Another option:

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

Post #1434013

Lynn Pettis

Posted Thursday, March 21, 2013 3:24 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:08 PM
Points: 21,589, Visits: 27,391

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)

Post #1434054

skb 44459

Posted Friday, March 22, 2013 5:27 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:58 PM
Points: 50, Visits: 106

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

Post #1434206

opc.three

Posted Friday, March 22, 2013 7:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 6,696, Visits: 11,715

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1434253

skb 44459

Posted Friday, March 22, 2013 7:20 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:58 PM
Points: 50, Visits: 106

SQL 2005. Our Database Server is 2005 . Is there a way to use -d option with 2005 server ?

Post #1434255

opc.three

Posted Friday, March 22, 2013 8:16 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 6,696, Visits: 11,715

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1434292

« Prev Topic | Next Topic »

20 posts, Page 2 of 2

««1 2

Permissions