none
BCP and dynamic SQL

    Question

  • Hello All,

    Been looking into this for a couple of days, and I keep hitting brick walls, so I'm hoping someone can offer me a bit of inspiration. What I'm trying to do is write a stored procedure that lets the user specify a list of tables, and an output directory, and the SP creates a series of BCP statements that export these tables to comma delimited files.

    This wouldn't be too hard, but I need to output the field headings in the first row of the table (and use quotes as text qualifiers). I'm doing this by looping round sys.columns, pulling out all the fieldnames, creating two select statements, and UNION ALL-ing them together. e.g.......

    select 'FIELD1','FIELD2','FIELD3','FIELD4'
    union all
    select field1,field2,field3,field4 from tablename

    It all works fine until you try it on a table with a lot of columns. Although you can build a big SQL statement in an NVARCHAR(MAX), BCP only appears to read the first 4000 characters of it, so it fails.

    To get round this, I've moved all of the code that builds the big SQL statement to its own stored procedure (i.e. you pass the tablename, and it returns the table with the field names in the first row). Then, I can just call this new SP in my BCP statement, with a couple of parameters. 

    The problem I'm getting is BCP is complaining saying '[Microsoft][SQL Native Client]BCP host-files must contain at least one column'.  I'm setting no count off, and there are no print statements, so I'm assuming this is because the data is getting returned via an exec sp_executesq (although this is a guess). I can't think of a way round this though, as the SQL need to be dynamic.



    alter PROCEDURE [dbo].[sp_QBMultiFileExportGetData]
    @tablename varchar(100),
    @dbname varchar(100)
    AS
    BEGIN
    declare @Execstring as nvarchar(MAX)
    declare @currentfieldname as varchar(100)
    declare @selectlist as varchar(8000)
    declare @fieldnamelist as varchar(8000)
    declare @colnames table
    (
    columnname varchar(100)
    )


    begin

    set nocount on

    set @execstring='select COLUMN_NAME '+
    'from ' + @dbname + '.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''' + @tablename + ''''

    insert into @colnames(columnname)
    exec sp_executesql @execstring

    set @selectlist=''
    set @fieldnamelist=''

    --Loop through fieldnames, and build two strings
    --One for outputting fieldnames and one for selecting the actual data
    while exists(select * from @colnames)
    begin
    select top 1 @currentfieldname=columnname from @colnames
    set @selectlist=@selectlist + 'quotename(['+ @currentfieldname + '],char(34)),'
    set @fieldnamelist=@fieldnamelist + '''' + @currentfieldname + ''' [' +@currentfieldname + '],'
    delete from @colnames where columnname=@currentfieldname
    end

    --remove last quote
    set @selectlist=substring(@selectlist,1,len(@selectlist)-1)
    set @fieldnamelist=substring(@fieldnamelist,1,len(@fieldnamelist)-1)

    --Built string to execute, with fieldnames, and select fields
    set @execstring='select ' + @fieldnamelist  + ' union all select ' + @selectlist + ' from ' + @dbname + '..'  + @tablename
    return exec sp_executesql @execstring
    end
    END


    this returns exactly what I want, but when I try to use it in a BCP statement, I get the error....

    i.e.

    EXEC master..xp_cmdshell 'bcp "exec QCDev.dbo.sp_QBMultiFileExportGetData ''tablename'',''dbname''" queryout C:\\outputfile.txt -T -t","'

    Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column


    Anyone ever tried this before?



    • Moved by Tom PhillipsModerator Wednesday, October 07, 2009 1:17 PM TSQL Question (From:SQL Server Database Engine)
    Wednesday, October 07, 2009 12:04 PM

Answers

  • I've tried it a variety of ways, but none seem to make any difference.

    Thanks for your suggestion though.



    Two things that I see right off:

    1)  You *must* make the change that richbrownesq pointed out.  You sProc will not work in SQL without it (though this is still insufficient to wokr with BCP).

    2) Your sProc does not work with SET FMTONLY ON, which is to say that it returns no column-set which is what BCP is complaining about.  It *must* return a column-set with SET FMTONLY ON or BCP cannot tell what the column-set will be when it runs it for real.

    What I would suggest is that you modify you sProc to detect if FMTONLY is ON, turn it OFF while you are building your execution string, then turn it back ON right before you execute the dynamic SQL.  Here is an example of this that works for me:

    ALTER PROCEDURE [dbo].[sp_QBMultiFileExportGetData]
    	 @tablename varchar(100),
    	 @dbname varchar(100)
    AS
    BEGIN
    
     DECLARE @DummyCount int, @CountCount int;
     SELECT @DummyCount = COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS -- this is just to force @@RowCount to be set.
     SET @CountCount = @@ROWCOUNT
     -- If FMTONLY is ON, then @CountCount will be 0, the only way that that should be possible
     -- If FMTONLY is ON, then turn it off temporarily:
     IF @CountCount = 0  SET FMTONLY OFF
    
     declare @Execstring as nvarchar(MAX)
     declare @currentfieldname as varchar(100)
     declare @selectlist as varchar(8000)
     declare @fieldnamelist as varchar(8000)
     declare @colnames table ( columnname varchar(100) )
    
     begin
    	set nocount on
    
    	set @execstring='select COLUMN_NAME '+ 
    	'from ' + @dbname + '.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''' + @tablename + ''''
    
    	insert into @colnames(columnname)
    	exec sp_executesql @execstring
    
    	set @selectlist=''
    	set @fieldnamelist=''
    
    	--Loop through fieldnames, and build two strings
    	--One for outputting fieldnames and one for selecting the actual data
    	while exists(select * from @colnames)
    	begin
    		select top 1 @currentfieldname=columnname from @colnames
    		set @selectlist=@selectlist + 'quotename(['+ @currentfieldname + '],char(34)),'
    		set @fieldnamelist=@fieldnamelist + '''' + @currentfieldname + ''' [' +@currentfieldname + '],'
    		delete from @colnames where columnname=@currentfieldname
    	end
    
    	--remove last quote
    	set @selectlist=substring(@selectlist,1,len(@selectlist)-1)
    	set @fieldnamelist=substring(@fieldnamelist,1,len(@fieldnamelist)-1)
    
    	--Built string to execute, with fieldnames, and select fields
    	set @execstring='select ' + @fieldnamelist  + ' union all select ' + @selectlist + ' from ' + @dbname + '..'  + @tablename
    
    	--If we turned FMTONLY OFF, then turn it back on before we execute the output SELECT:
    	IF @CountCount = 0  SET FMTONLY ON
    	exec sp_executesql @execstring
     end
    END
    







    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."
    Wednesday, October 07, 2009 3:17 PM
  • Hi Guys,

    Thanks for the suggestions. I had been trying to avoid temp tables (don't really like them), but I think eventually, they were the only way to go. Unfortunately, this opened a whole can of scoping worms, and after a couple of hours, its all given me a right headache. However, the good news is I've finally got it working as I wanted.

    I was finding I was having issues using temp tables, as the tables being used were dynamic, so I would have to create them in a dynamic SQL string, and they weren't propagating upwards from child to parent. I seemed to be getting the same problem using global temporary tables too, although I'm not sure why, as they should have worked They seemed to be out of scope by the time the SP that was calling my sp_QBMultiFileExportGetData tried to output the data. This might possibly have been because BCP wasn't seeing the same scope, but I've not tested it fully (and its very possible I was making a mistake).

    The solution was to abandon sp_QBMultiFileExportGetData, and merge the code back into the calling script. However, rather than trying to pass an enormous SQL string to bcp, running it separately with sp_executesql, and dumping the results in a global temp table. Then let bcp just call a 'select * from temptable', to avoid the select statement getting too long. Its not the most elegant solution, but it seems to work fine.

    ALTER PROCEDURE [dbo].[sp_QBMultiFileExport]
    -- Add the parameters for the stored procedure here
    @tablenames varchar(1000), --list of tables to be exported
    @outputpath varchar(1000), --output path ***AS SEEN BY THE SERVER, NOT THE CLIENT***
    @servername varchar(100), --Server where data resides
    @dbname varchar(100), --database name
    @delimiter varchar(1) --output delimiter
    AS
    BEGIN
    declare @Execstring as nvarchar(max)
    declare @currenttable as varchar(100)
    declare @colnames table
    (
    columnname varchar(100)
    )

    declare @currentfieldname as varchar(100)
    declare @selectlist as varchar(max)
    declare @fieldnamelist as varchar(max)

    --Get rid of CRLFs in the tablenames parameter
    set @tablenames=replace(@tablenames,char(10),'')
    set @tablenames=replace(@tablenames,char(13),'')

    --add extra comma to the end of the list (needed later for consistency)
    set @tablenames=@tablenames+','
    --Get first table in the list
    set @currenttable=substring(@tablenames,1,charindex(',',@tablenames)-1)

    while @tablenames<>''
    begin

    --Get a list of fieldnames from syscols
    insert into @colnames(columnname)
    select COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @currenttable

    set @selectlist=''
    set @fieldnamelist=''

    while exists(select * from @colnames)
    begin
    --get first column name
    select top 1 @currentfieldname=columnname from @colnames
    --add to select statement lists
    set @selectlist=@selectlist + 'quotename(['+ @currentfieldname + '],char(34)),'
    set @fieldnamelist=@fieldnamelist + '''' + @currentfieldname + ''' [' +@currentfieldname + '],'
    --remove column from temptable
    delete from @colnames where columnname=@currentfieldname
    end

    --remove last quote from field lists
    set @selectlist=substring(@selectlist,1,len(@selectlist)-1)
    set @fieldnamelist=substring(@fieldnamelist,1,len(@fieldnamelist)-1)

    --check for temp table, and drop if necessary
    IF object_id('tempdb..##MultiFileExportTempTable') IS NOT NULL
    BEGIN
    DROP TABLE ##MultiFileExportTempTable
    END

    --Build list of fieldnames, and select list, unioned together
    --and put the results in temptable
    set @execstring='select ' + @fieldnamelist  + ' into ##MultiFileExportTempTable union all select ' + @selectlist + ' from ' + @dbname + '..'  + @currenttable
    exec sp_executesql @execstring

    --get BCP to pull data back from ##temptable, and dump in file
    set @execstring='EXEC master..xp_cmdshell ''bcp "select * from ##MultiFileExportTempTable" queryout ' + @outputpath + '\' + @currenttable + '.txt' + ' -c -T -t"' + @delimiter + '"'''
    exec sp_executesql @execstring
    --drop tablename from list
    set @tablenames=replace(@tablenames,@currenttable + ',','')
    --if tablenames list is not empty, get the next one
    if @tablenames<>''
    set @currenttable=substring(@tablenames,1,charindex(',',@tablenames)-1)
    else
    set @currenttable=''
    end

    IF object_id('tempdb..##MultiFileExportTempTable') IS NOT NULL
    BEGIN
    DROP TABLE ##MultiFileExportTempTable
    END

    END


    So, you call this with...

    exec dbo.[sp_QBMultiFileExport] 'table1,table2,table3',filepath,servername,dbname,delimiter

    ...and it creates delimited files called table1.txt, table2.txt and table3.txt in the specified folder, with field headings and text qualifiers.



    Many thanks for all your suggestions

    • Marked as answer by Nicko71 Wednesday, October 07, 2009 3:41 PM
    Wednesday, October 07, 2009 3:33 PM

All replies

  • Are you sure the end of your stored procedure is correct?

    set @execstring='select ' + @fieldnamelist  + ' union all select ' + @selectlist + ' from ' + @dbname + '..'  + @tablename
    return exec sp_executesql @execstring
    end
    END
    
    -- should be
    
    set @execstring='select ' + @fieldnamelist  + ' union all select ' + @selectlist + ' from ' + @dbname + '..'  + @tablename
    exec sp_executesql @execstring
    return 
    end
    END

    every day is a school day
    Wednesday, October 07, 2009 12:31 PM
    Moderator
  • I've tried it a variety of ways, but none seem to make any difference.

    Thanks for your suggestion though.



    Wednesday, October 07, 2009 1:22 PM
  • Few ideas:

    1. Use the INFORMATION_SCHEMA views  like INFORMATION_SCHEMA.COLUMNS
    2. You can communicate between parent and child( Dynamic SQL) processes by creating a #temptable in parent
    3. You can also do it since this appears to be single-user app, by creating a ##globaltemptable in child
    4. Use QUOTENAME see Dynamic SQL examples at link below:

    http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/


    Let us know how it goes.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Wednesday, October 07, 2009 1:33 PM
    Moderator
  • It is possible that adding SET FMTONLY OFF before your exec will make it work (but it will execute your code twice!).
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, October 07, 2009 2:17 PM
    Moderator
  • I've tried it a variety of ways, but none seem to make any difference.

    Thanks for your suggestion though.



    Two things that I see right off:

    1)  You *must* make the change that richbrownesq pointed out.  You sProc will not work in SQL without it (though this is still insufficient to wokr with BCP).

    2) Your sProc does not work with SET FMTONLY ON, which is to say that it returns no column-set which is what BCP is complaining about.  It *must* return a column-set with SET FMTONLY ON or BCP cannot tell what the column-set will be when it runs it for real.

    What I would suggest is that you modify you sProc to detect if FMTONLY is ON, turn it OFF while you are building your execution string, then turn it back ON right before you execute the dynamic SQL.  Here is an example of this that works for me:

    ALTER PROCEDURE [dbo].[sp_QBMultiFileExportGetData]
    	 @tablename varchar(100),
    	 @dbname varchar(100)
    AS
    BEGIN
    
     DECLARE @DummyCount int, @CountCount int;
     SELECT @DummyCount = COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS -- this is just to force @@RowCount to be set.
     SET @CountCount = @@ROWCOUNT
     -- If FMTONLY is ON, then @CountCount will be 0, the only way that that should be possible
     -- If FMTONLY is ON, then turn it off temporarily:
     IF @CountCount = 0  SET FMTONLY OFF
    
     declare @Execstring as nvarchar(MAX)
     declare @currentfieldname as varchar(100)
     declare @selectlist as varchar(8000)
     declare @fieldnamelist as varchar(8000)
     declare @colnames table ( columnname varchar(100) )
    
     begin
    	set nocount on
    
    	set @execstring='select COLUMN_NAME '+ 
    	'from ' + @dbname + '.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''' + @tablename + ''''
    
    	insert into @colnames(columnname)
    	exec sp_executesql @execstring
    
    	set @selectlist=''
    	set @fieldnamelist=''
    
    	--Loop through fieldnames, and build two strings
    	--One for outputting fieldnames and one for selecting the actual data
    	while exists(select * from @colnames)
    	begin
    		select top 1 @currentfieldname=columnname from @colnames
    		set @selectlist=@selectlist + 'quotename(['+ @currentfieldname + '],char(34)),'
    		set @fieldnamelist=@fieldnamelist + '''' + @currentfieldname + ''' [' +@currentfieldname + '],'
    		delete from @colnames where columnname=@currentfieldname
    	end
    
    	--remove last quote
    	set @selectlist=substring(@selectlist,1,len(@selectlist)-1)
    	set @fieldnamelist=substring(@fieldnamelist,1,len(@fieldnamelist)-1)
    
    	--Built string to execute, with fieldnames, and select fields
    	set @execstring='select ' + @fieldnamelist  + ' union all select ' + @selectlist + ' from ' + @dbname + '..'  + @tablename
    
    	--If we turned FMTONLY OFF, then turn it back on before we execute the output SELECT:
    	IF @CountCount = 0  SET FMTONLY ON
    	exec sp_executesql @execstring
     end
    END
    







    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."
    Wednesday, October 07, 2009 3:17 PM
  • Hi Guys,

    Thanks for the suggestions. I had been trying to avoid temp tables (don't really like them), but I think eventually, they were the only way to go. Unfortunately, this opened a whole can of scoping worms, and after a couple of hours, its all given me a right headache. However, the good news is I've finally got it working as I wanted.

    I was finding I was having issues using temp tables, as the tables being used were dynamic, so I would have to create them in a dynamic SQL string, and they weren't propagating upwards from child to parent. I seemed to be getting the same problem using global temporary tables too, although I'm not sure why, as they should have worked They seemed to be out of scope by the time the SP that was calling my sp_QBMultiFileExportGetData tried to output the data. This might possibly have been because BCP wasn't seeing the same scope, but I've not tested it fully (and its very possible I was making a mistake).

    The solution was to abandon sp_QBMultiFileExportGetData, and merge the code back into the calling script. However, rather than trying to pass an enormous SQL string to bcp, running it separately with sp_executesql, and dumping the results in a global temp table. Then let bcp just call a 'select * from temptable', to avoid the select statement getting too long. Its not the most elegant solution, but it seems to work fine.

    ALTER PROCEDURE [dbo].[sp_QBMultiFileExport]
    -- Add the parameters for the stored procedure here
    @tablenames varchar(1000), --list of tables to be exported
    @outputpath varchar(1000), --output path ***AS SEEN BY THE SERVER, NOT THE CLIENT***
    @servername varchar(100), --Server where data resides
    @dbname varchar(100), --database name
    @delimiter varchar(1) --output delimiter
    AS
    BEGIN
    declare @Execstring as nvarchar(max)
    declare @currenttable as varchar(100)
    declare @colnames table
    (
    columnname varchar(100)
    )

    declare @currentfieldname as varchar(100)
    declare @selectlist as varchar(max)
    declare @fieldnamelist as varchar(max)

    --Get rid of CRLFs in the tablenames parameter
    set @tablenames=replace(@tablenames,char(10),'')
    set @tablenames=replace(@tablenames,char(13),'')

    --add extra comma to the end of the list (needed later for consistency)
    set @tablenames=@tablenames+','
    --Get first table in the list
    set @currenttable=substring(@tablenames,1,charindex(',',@tablenames)-1)

    while @tablenames<>''
    begin

    --Get a list of fieldnames from syscols
    insert into @colnames(columnname)
    select COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @currenttable

    set @selectlist=''
    set @fieldnamelist=''

    while exists(select * from @colnames)
    begin
    --get first column name
    select top 1 @currentfieldname=columnname from @colnames
    --add to select statement lists
    set @selectlist=@selectlist + 'quotename(['+ @currentfieldname + '],char(34)),'
    set @fieldnamelist=@fieldnamelist + '''' + @currentfieldname + ''' [' +@currentfieldname + '],'
    --remove column from temptable
    delete from @colnames where columnname=@currentfieldname
    end

    --remove last quote from field lists
    set @selectlist=substring(@selectlist,1,len(@selectlist)-1)
    set @fieldnamelist=substring(@fieldnamelist,1,len(@fieldnamelist)-1)

    --check for temp table, and drop if necessary
    IF object_id('tempdb..##MultiFileExportTempTable') IS NOT NULL
    BEGIN
    DROP TABLE ##MultiFileExportTempTable
    END

    --Build list of fieldnames, and select list, unioned together
    --and put the results in temptable
    set @execstring='select ' + @fieldnamelist  + ' into ##MultiFileExportTempTable union all select ' + @selectlist + ' from ' + @dbname + '..'  + @currenttable
    exec sp_executesql @execstring

    --get BCP to pull data back from ##temptable, and dump in file
    set @execstring='EXEC master..xp_cmdshell ''bcp "select * from ##MultiFileExportTempTable" queryout ' + @outputpath + '\' + @currenttable + '.txt' + ' -c -T -t"' + @delimiter + '"'''
    exec sp_executesql @execstring
    --drop tablename from list
    set @tablenames=replace(@tablenames,@currenttable + ',','')
    --if tablenames list is not empty, get the next one
    if @tablenames<>''
    set @currenttable=substring(@tablenames,1,charindex(',',@tablenames)-1)
    else
    set @currenttable=''
    end

    IF object_id('tempdb..##MultiFileExportTempTable') IS NOT NULL
    BEGIN
    DROP TABLE ##MultiFileExportTempTable
    END

    END


    So, you call this with...

    exec dbo.[sp_QBMultiFileExport] 'table1,table2,table3',filepath,servername,dbname,delimiter

    ...and it creates delimited files called table1.txt, table2.txt and table3.txt in the specified folder, with field headings and text qualifiers.



    Many thanks for all your suggestions

    • Marked as answer by Nicko71 Wednesday, October 07, 2009 3:41 PM
    Wednesday, October 07, 2009 3:33 PM
  • Thanks for the suggestions Barry.  I suspect you were probably right, as some of the errors I was seeing suggested the column headers still weren't getting through to BCP.
    Wednesday, October 07, 2009 3:50 PM
  • The procedure that I posted will work for you, without having to go to temporary tables.
    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."
    Wednesday, October 07, 2009 4:55 PM
  • Hi Barry, Just had a go at implementing your code, and it works a treat. Many thanks for your help.
    Thursday, October 08, 2009 10:16 AM