3

I'm trying to export the column names of all my DB tables to a csv file using bcp utility to be later merged with the data from the tables merged into one file with the retrieved column names as headers in the file.

I've copied the code in the answer from this question: export table to file with column headers (column names) using the bcp utility and SQL Server 2008. However I am getting this error:

Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1 Procedure expects parameter 'no_output' of type 'varchar'.

Here's my code: 

DECLARE @var NVARCHAR(MAX)

DECLARE curRunning
CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sysobjects WHERE type = 'U'

OPEN curRunning

FETCH NEXT FROM curRunning INTO @var

WHILE @@FETCH_STATUS = 0
BEGIN
     DECLARE @Exec2 NVARCHAR(MAX),@sql1 VARCHAR(1000)
     SET @sql1 ='"DECLARE @colnames VARCHAR(max);SELECT @colnames= COALESCE(@colnames + '+''','''+' , '''') + column_name from dbname.INFORMATION_SCHEMA.columns where TABLE_NAME='+@var+'; select @colnames;"'
     SET @Exec2 = 'exec master.dbo.xp_cmdshell ''bcp '+@sql1+' queryout "C:\migration\HeadersOnly'+@var+'.csv" -c -k -T -t,'''
     PRINT @Exec2
     EXECUTE sp_executesql @Exec2
FETCH NEXT FROM curRunning INTO @var
END

CLOSE curRunning
DEALLOCATE curRunning
Community
  • 1
  • 1
Amir A.
  • 31
  • 1
  • 2

2 Answers2

6

You can’t use varchar(max) with xp_cmdshell.

Try a number between 1 and 8000.

varchar(max) has a capacity of 2GB & works as TEXT data type in background so it is no good

You will need to use VARCHAR(256) or similar upto a maximum of VARCHAR(8000)

Ian Currie
  • 111
  • 1
  • 6
  • This is a correct comment about the code in question, but this will result in the error "Procedure expects parameter 'command_string' of type 'varchar'." and as that is not there error in the question, is not the answer to the question. – Thronk Jul 02 '15 at 18:57
2

In your code:

set @Exec2 = 'exec master.dbo.xp_cmdshell ''bcp '+@sql1+ 'queryout "C:\migration\HeadersOnly'+@var+'.csv" -c -k -T -t,'''

There is a comma (,) at the end:

-t,'''

It tells xp_cmdsheel, that there should be next parameter. That parameter is [NO OUTPUT]. If you are not going to supply that patrameter, delete comma.

Ref: xp_cmdshell (Transact-SQL)

Stoleg
  • 8,972
  • 1
  • 21
  • 28
  • Removed the -t, parameter now gives me this error: Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string ' , ') + column_name from DB.INFORMATION_SCHEMA.columns where TABLE_NAME=table; select @colnames;" queryout "C:\migration\HeadersOnlytable.csv" -c -k -T'. – Amir A. Jun 26 '13 at 15:43
  • You need to remove just comma ",". leave everything as it is – Stoleg Jun 26 '13 at 17:21