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
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 309
DECLARE @Cmd VarChar(4000)
DECLARE @SProcName VarChar(40)
DECLARE @SP_Parm1Value VarChar(40)
DECLARE @SP_Parm2Value VarChar(4000)

set @SProcName = 'Rpt_JobTransactionsSp'
set @SP_Parm2Value = '|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|'
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
bcp "exec RSDV_App..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 -c -t^|

The following statement works great and gives me desired data in the grid.
exec RSDV_App..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'

But when i execute the follwing command
EXEC master..xp_cmdshell @cmd

Errors out
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = 37000, NativeError = 2812
Error = [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.EXTGEN_InitSessionContextSp'.
NULL

There is a prcedure 'dbo.EXTGEN_InitSessionContextSp' running inside 'Rpt_JobTransactionsSp'.

Whats the workaround for this ?
Sergiy
Sergiy
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44301 Visits: 12639
Which server you want your bcp to connect to?
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77868 Visits: 14499
Are you sure that is the entire output? I see you are appending -S but it is not in the comment where you say "output looks like this"

bcp "exec RSDV_App..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 -c -t^|


Make sure that SSMS is set to return more all the characters, see Tools > Options > Query Results > Results to Text/Grid.

Try taking the [complete] command line output from your code and execute it at a cmd shell prompt, i.e. let's take xp_cmdshell out of the equation for a minute to see if it is part of the issue, or whether we see an issue just using bcp. I think I know what's going on, but please post the results.

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

Group: General Forum Members
Points: 1954 Visits: 309
I am running the code from the SQL Server Management Studio.
Do i need to specify the Server name , user name and password ?
skb 44459
skb 44459
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 309
Yes In the code i am appending the Servername but i get same result with or without the ServerName.
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77868 Visits: 14499
skb 44459 (3/21/2013)
I am running the code from the SQL Server Management Studio.
Do i need to specify the Server name , user name and password ?

I do not think you do. -T means to use Windows Auth so you only need one or the other.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77868 Visits: 14499
skb 44459 (3/21/2013)
Yes In the code i am appending the Servername but i get same result with or without the ServerName.

Have you modified SSMS so you can see the entire output showing what xp_cmdshell would run? i.e. the entire contents of the variable?

WHen you can, copy the bcp command line and run it directly from a cmd shell prompt. Let us know the output.

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

Group: General Forum Members
Points: 1954 Visits: 309
Here is the command and output.
FYI: 'dbo.EXTGEN_InitSessionContextSp' is getting called from Rpt_JobTransactionsSp

bcp "exec PHIL_App..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
-c -t^| -Sphlsqlsl01
SQLState = 37000, NativeError = 2812
Error = [Microsoft][SQL Native Client][SQL Server]Could not find stored procedur
e 'dbo.EXTGEN_InitSessionContextSp'.
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77868 Visits: 14499
And just to confirm, when you run this in SSMS what does it do?

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



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

Group: General Forum Members
Points: 1954 Visits: 309
i get the desired output.

(1 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(12932 row(s) affected)

then all the records are displayed.
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