BCP To Show Full Text Values I have a long varchar field that I am exporting to a csv with BCP.  The export goes as it should, however in the actual cell itself the csv file shows X.XXXXXX+13 instead of XXXXXXXXXXXXXX 

Am I missing a Cast() or BCP switch that could force the csv file to show XXXXXXXXXXXXXX?  Or since it is BCP this is the output you get?

Declare @sql nvarchar(max), @filename varchar(max)
Set @filename = 'C:\Test.csv'
Create Table #test (field1 varchar(100))
Insert Into #test (field1) Values
('XXXXXXXXXXXXXX')
Select @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM #Test''" queryout "'
SET @sql = @sql + @filename + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql
This works fine for me:

CREATE TABLE test ( field1 VARCHAR(100) );
INSERT  INTO test
        ( field1 )
VALUES  ( 'XXXXXXXXXXXXXX' );

DECLARE @sql VARCHAR(4000) ,
    @filename VARCHAR(4000);
SET @filename = 'C:\temp\Test.csv';
SELECT  @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM Test" queryout "';
SET @sql = @sql + @filename + '" -c -t, -T -S ' + @@SERVERNAME;
PRINT @sql;
EXEC master..xp_cmdshell @sql;


Providing results:


field1
"XXXXXXXXXXXXXX"


Force text formatting on the column by adding a preceeding = thus:

CREATE TABLE test ( field1 VARCHAR(100) );
INSERT  INTO test
        ( field1 )
VALUES  ( 'XXXXXXXXXXXXXX' );

DECLARE @sql VARCHAR(4000) ,
    @filename VARCHAR(4000);
SET @filename = 'C:\temp\Test.csv';
SELECT  @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT ''=''+CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM Test" queryout "';
SET @sql = @sql + @filename + '" -c -t, -T -S ' + @@SERVERNAME;
PRINT @sql;
EXEC master..xp_cmdshell @sql;