Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

BCP Error : Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.EXTGEN_InitSessionContextSp'. Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 12:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
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
Posted Thursday, March 21, 2013 1:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
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
Posted Thursday, March 21, 2013 1:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1433990
Posted Thursday, March 21, 2013 1:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
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
Posted Thursday, March 21, 2013 1:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1434013
Posted Thursday, March 21, 2013 3:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 20,676, Visits: 32,267
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




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
Posted Friday, March 22, 2013 5:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
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
Posted Friday, March 22, 2013 7:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1434253
Posted Friday, March 22, 2013 7:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
SQL 2005. Our Database Server is 2005 . Is there a way to use -d option with 2005 server ?
Post #1434255
Posted Friday, March 22, 2013 8:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1434292
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse