January 7, 2011 at 3:56 am
Morning,
I created a batch file to bcp xml out of tables. When executing the file it
just loops on the first bcp statement without executing. It works fine if I copy it into the cmd prompt manually. I have full rights to the server. Here's
the statement:
bcp "Select BookInvoiceXML from test.dbo.BookInvoice WHERE BookInvoiceID = 1" queryout c:\arcgis
\test2.xml -SOlivermorris-pc\R2 -T -c -r -t
bcp "Select BookInvoiceXML from test.dbo.BookInvoice WHERE BookInvoiceID = 2" queryout c:\arcgis
\test3.xml -SOlivermorris-pc\R2 -T -c -r -t
Any ideas,
Many Thanks for your help,
Oliver
January 7, 2011 at 10:15 am
Found a way around using xp_cmdshell, very happy. borrowed lots from others.
Cheers
DECLARE @filename VARCHAR(255)
declare @RowNum int,
@CustId nchar(5),
@Name1 nchar(25)
select @CustId=MAX(ID) FROM _SSA --start with the highest ID
Select @RowNum = Count(*) From _SSA --get total number of records
WHILE @RowNum > 0 --loop until no more records
BEGIN
select @Name1 = id from _SSA where ID = @CustID --get other info from that row
SELECT @filename = [xmlFileName] FROM _SSA where ID = @CustID
-- print cast(@RowNum as char(12)) + ' ' + @CustId + ' ' + @Name1 --do whatever
DECLARE @SQL VARCHAR(400)
DECLARE @SQL2 VARCHAR(255)
DECLARE @SQL3 VARCHAR(255)
SET @sql3 = @filename
SET @sql2 = @filename
SET @sql = ' bcp "Select xml_data from test.dbo._SSA where xmlFileName = ''' + @SQL2 + '''" queryout c:\arcgis\' + @SQL3 + '.xml -SOlivermorris-pc\R2 -T -c -r -t '
--print @SQL
EXEC Master..xp_cmdshell @SQL
select top 1 @CustId=ID from _SSA where ID < @CustID order by ID desc--get the next one
set @RowNum = @RowNum - 1 --decrease count
END
September 2, 2021 at 11:44 pm
Resurrecting this post. It is here so other people who have the same error don't spend an eternity trying to find a solution. The problem is that the your PATH environment variable is mapped to multiple SQL versions of BCP. Use the absolute BCP path in your call, and the issue goes away.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply