April 8, 2004 at 5:57 am
hi all , Even i solved the problem by giving the whole query which has to be executed for getting the xml output in the bcp command itself with nolock.Its working fine.
Now i got one more problem. If i try to change some fileds dynamicallyit is showing the following error.
SET @TestId = 3
SET @FilePath = 'c:\Temp.xml'
SET @MyCmd = '''bcp "SELECT * fROM tblDOcData Where DocId ='+cast(@TestId as varchar)+' " queryout '+ @FilePath+ ' -c -S DATASERVER -U sa -P -o c:\temp\temp2.out'''
exec master..xp_cmdshell @MyCmd
If i am running this it is telling that "bcp is not a recognized command"
What to do for this.
Thanks and Regards,
Shreedhar
Hi All,
If I run bcp through my stored procedure it is working Where as If I call the stored procedure using some trigger, It is hanging in infinite execution.
Is there any specific point about SET QUOTED_IDENTIFIER ON and
SET ANSI_NULLS ON
my trigger is
ALTER TRIGGER samptrig ON [dbo].[tblDocData]
FOR INSERT, UPDATE
AS
exec TestDCT.dbo.testproc
My procedure TestDCT.dbo.testproc is
ALTER PROCEDURE testproc AS
exec master..xp_cmdshell 'bcp "exec TestDCT.dbo.testprocedure" queryout c:\test\Authors5955.xml -c -T -S DATASERVER -o c:\temp\temp.out', no_output
PRINT 'Execution Over'
My other procedure TestDCT.dbo.testprocedure which is resulting XML data is
ALTER PROCEDURE testprocedure
AS
SELECT
1 as Tag
,NULL as Parent
,tblDocData.DocIdas [tbl!1!id]
,NULL as [DocDescription!2!]
,NULL as [DocData!3!]
FROM tblDocData
UNION ALL
SELECT
2
,1
,tblDocData.DocId
,tblDocData.Description
,NULL
FROM tblDocData
UNION ALL
SELECT 3
,1
,tblDocData.DocId
,tblDocData.Description
,tblDocData.DocData
FROM tblDocData
WHERE tblDocData.DocId = 1
ORDER BY [tbl!1!id], [DocDescription!2!], [DocData!3!]
FOR XML EXPLICIT
Thanks in advance for any clue,
April 8, 2004 at 6:51 am
The bcp will be blocked by the insert/update locks. Try adding NOLOCK hint to the tables in testprocedure.
Far away is close at hand in the images of elsewhere.
Anon.
April 8, 2004 at 9:06 am
Thanks
Even i solved the problem by giving the whole sql query which will get the xml output in the bcp command itself with nolock.
Now i am getting some more problem when i am trying to give some of the fields dynamically.
My Code is
DECLARE @FilePath varchar(250), @MyCmd Varchar(1000)
SET @MyCmd = '''bcp "SELECT * from tblDocData WITH (NOLOCK) WHERE [TestDCT]..tblDocData.DocId ='+cast(@TestId as varchar)+'FOR XML EXPLICIT" queryout '+ @FilePath+ ' -c -S DATASERVER -U sa -P -o c:\temp\temp2.out'''
exec master..xp_cmdshell @MyCmd
It is telling that "bcp is not an executable batch program or command".
what to do for this.
Thanks and regards,
Shreedhar
April 8, 2004 at 9:15 am
You have put a single quote around the command, change the 3 single quotes at the beginning and end of the line like this
SET @MyCmd = 'bcp "SELECT * from tblDocData WITH (NOLOCK) WHERE [TestDCT]..tblDocData.DocId ='+
cast(@TestId as varchar)+
'FOR XML EXPLICIT" queryout '+
@FilePath+
' -c -S DATASERVER -U sa -P -o c:\temp\temp2.out'
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply