-2

While trying to export a SQL Server table to a .csv file using bulk copy, the following errors occur:

Conversion failed when converting the varchar value to data type int.

Unable to resolve column level collations

Copying starts and then an error is thrown.

enter image description here

enter image description here

EDIT: Now I get this as an output...

enter image description here

DECLARE @table_name VARCHAR(50);
DECLARE @columnHeader VARCHAR(8000);
DECLARE @raw_sql VARCHAR(8000);



SET @table_name ='clients'

SELECT @columnHeader = COALESCE(@columnHeader + ',', '') + QUOTENAME('name', 
'''')
FROM    [dbo].INFORMATION_SCHEMA.COLUMNS AS C
WHERE   C.TABLE_NAME = @Table_Name
select @raw_sql = 'bcp "select '+ @columnHeader +' name, 
uniqClientID,registrationNumber,
                                name,vatNumber, CAST(legalEntity AS 
varchar(10)) AS legalEntity from [dbo]..Clients" ' +' queryout 
C:\ClientsHeaderF.csv -c -t -T -S' + @@servername
EXEC  xp_cmdshell @raw_sql
Community
  • 1
  • 1
Temp034
  • 141
  • 11
  • Your column headers are all strings and your columns values are probably not all strings. You need to replace `SELECT *` with a column list and ensure that all non string columns are explicitly converted to string. – Alex Nov 09 '17 at 09:29
  • 1
    To debug these errors it helps to ensure that your underlying source SQL query is valid. It can be easily validated by executing it on its own. – Alex Nov 09 '17 at 09:32
  • Alex, when I declare all column names in my query and cast all non varchar types to varchar I receive this... output usage: bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type] [-w wide character type] [-N keep non-text native] [-V file format version] [-q quoted identifier] – Temp034 Nov 09 '17 at 10:38
  • Isolated query executes just fine – Temp034 Nov 09 '17 at 10:39
  • Please edit your question to show the code that you executed in isolation. Note: I am looking for a similar dynamic SQL code (with column header query etc.) to what you currently have minus the `bcp` parts. Once you get this version working, simply add `bcp` parts to it to get the final result. – Alex Nov 09 '17 at 10:46
  • Debugging rule number 2: when you change something and you receive a different error it does not mean that what you did is totally wrong and that you should go back, maybe you just did not make enough changes to get it right. – Alex Nov 09 '17 at 10:47
  • On StackOverflow, people prefer code as formatted text, not images. It is easier to copy paste it to test with, rather than typing it all ourselves. – HoneyBadger Nov 09 '17 at 11:08
  • Thanks for the code as text. In your edited code you still use `'name'` as a literal string. This will still result in a comma separated string with the word 'name' multiple times. You should use `COLUMN_NAME` from `INFORMATION_SCHEMA.COLUMNS `. – HoneyBadger Nov 09 '17 at 13:55
  • I appreciate your effort. I get the same output when I replace literal with COLUMN_NAME. – Temp034 Nov 09 '17 at 14:19
  • Can you edit the question to add the value of `@raw_sql` as well as the query you executed (the one which you said "executes just fine")? – HoneyBadger Nov 09 '17 at 15:57
  • Can you take a look at my last comment, I managed to output headers, but there is no any data. – Temp034 Nov 10 '17 at 11:32

1 Answers1

-1

This is too long for a comment:

I guess I understand what you are trying to do with

SELECT @ColumnHeader = COALESCE(@ColumnHeader, ',', '') + '''' + 'name' + ''''
FROM    INFORMATION_SCHEMA.COLUMNS AS C
WHERE   C.TABLE_NAME = @Table_Name

But have you actually tried it?

The resulting @ColumnHeader looks something like: ,'name''name''name''name'.

You say "Isolated query executes just fine". I doubt that.

I think you mean to use:

SELECT @ColumnHeader = COALESCE(@ColumnHeader + ',', '') + QUOTENAME(C.COLUMN_NAME, '''')
FROM    INFORMATION_SCHEMA.COLUMNS AS C
WHERE   C.TABLE_NAME = @Table_Name
HoneyBadger
  • 14,750
  • 3
  • 34
  • 48
  • I receive null as an output, when I execute a procedure. – Temp034 Nov 09 '17 at 10:59
  • @T.Dejan I am not sure what I'm supposed to do with that comment. I've edited the 'answer' to include a corrected version. – HoneyBadger Nov 09 '17 at 11:06
  • 1
    @HoneyBadger, I have attempted to explain to OP how to debug and solve the problem on his own. I get the feeling that this is not the expected help. The expectation is to receive a fully working code dump. – Alex Nov 09 '17 at 12:00
  • Expectation is to solve this somehow, I tried your approach and edited my answer to indicate the output I get. – Temp034 Nov 09 '17 at 13:51