CODEWITHSUNDEEP
sql-servercsvbcp
Amir A.
Amir A.

Reputation: 31

BCP utility in sql server 2008 - Procedure expects parameter 'no_output' of type varchar

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

Upvotes: 3

Views: 9615

Answers (2)

Ian Currie
Ian Currie

Reputation: 111

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)

Upvotes: 6

Stoleg
Stoleg

Reputation: 9300

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)

Upvotes: 2

Related Questions