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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy