March 18, 2013 at 12:47 pm
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 ?
March 19, 2013 at 6:21 pm
Which server you want your bcp to connect to?
_____________
Code for TallyGenerator
March 19, 2013 at 7:49 pm
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
March 21, 2013 at 5:33 am
I am running the code from the SQL Server Management Studio.
Do i need to specify the Server name , user name and password ?
March 21, 2013 at 5:35 am
Yes In the code i am appending the Servername but i get same result with or without the ServerName.
March 21, 2013 at 10:01 am
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
March 21, 2013 at 10:02 am
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
March 21, 2013 at 10:47 am
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'.
March 21, 2013 at 10:55 am
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
March 21, 2013 at 12:22 pm
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.
March 21, 2013 at 12:58 pm
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
March 21, 2013 at 1:03 pm
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'.
March 21, 2013 at 1:07 pm
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
March 21, 2013 at 1:31 pm
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
March 21, 2013 at 1:50 pm
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply