BCP - SP returns multiple record set

• rajasekar.x.jeyarajan

  SSC Veteran

  Points: 273

  More actions

  September 2, 2009 at 6:37 am

  #210395

  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

• J-F Bergeron

  SSCrazy Eights

  Points: 8593

  More actions

  September 2, 2009 at 6:45 am

  #1048076

  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