September 2, 2009 at 6:37 am
Hi All,
I am having an SP which is like as follows,
CREATE PROC dbo.Test
AS
BEGIN
SELECT 1,2,
SELECT 1,2,3
END
I need to BCP out all the Result set from an SP, to a file. But unfortunately it is writing the file with the Result set for the first SELECT statement. Can some help me like it is the restriction in SQL Server 2005 or any other option i need to add or enable when doing BCP.
This is my BCP command,
DECLARE @sql Nvarchar(1000)
SELECT @sql = 'bcp "exec TT.dbo.test" queryout D:\test.txt -c -t, -T -SRajasekar\2005 -Usa -Psa'
EXEC master..xp_cmdshell @sql
Thanks,
Rajasekar.J
September 2, 2009 at 6:45 am
bcp will always take the first result set it finds in the stored procedure, you cannot overcome this.
What you could do, is create 2 procedures, or append the outputs using a union all, to create 1 file with the 2 datasets in it.
create proc proctest as
begin
select 1,2,null
union all
select 1,2,3
end
This is the only way you can overcome this.
You can also create 2 files, with 2 bcp commands, and merge the files together using a dos command with the xp_cmdShell.
Cheers,
J-F
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply