Search code examples
sql-servert-sqlbcp

BCP copy out failed -- data type conversion & column level collation errors

Context: I'm attempting to output the contents of a table in SQL Server to a CSV file via BCP. I'm trying to explicitly name the column headers in the CSV file by performing a UNION ALL so the column headers arent empty.

Question: Can someone please share some pointers on why I'm raising these errors, in particular the data type conversion error when I've explicity casted the data types in the query? 

bcp "select 'col1', 'col2', 'col3' union all select cast(id as varchar(10)), name, cast(someinfo as varchar(28)) from Question1355876" queryout myTable.csv /SmyServer01 /c /t, -T
111

I've followed the logic above which was suggested here: export table to file with column headers (column names) using the bcp utility and SQL Server 2008

Below is the T-SQL snippet that keeps failing: 

exec master.dbo.xp_cmdshell 'bcp "SELECT ''cast(Company_ID as int)'', ''cast(MatterMark_URL as nvarchar(150))'', ''cast(Company_Name as nvarchar(100))'', ''cast(Domain as nvarchar(150))'' UNION ALL SELECT DISTINCT cast(id as int), cast(url as nvarchar(150)), cast(company_name as nvarchar(100)), cast(domain as nvarchar(150)) FROM ##jsonparsed" queryout C:\Users\ebarnes\etl_project\company_data.csv -c -T -t'

enter image description here

Solution

  • Stu is correct about what is happening, but something will need to be casted, there is more to add. The error you are getting is because you explicitly cast a data type in the 2nd query and so SQL tried to cast the first query (implicitly cast as varchar to start and until SQL encounters the cast in your 2nd query) as an int as well. However, the value in the first column in the first query cannot be converted to int.. it's a varchar string "'cast(Company_ID as int)'".

    You have to get the first and 2nd query in your union to be similar data types. You're never going to get the string "cast(Company_ID as int)" to be an integer, I suggest casting your columns in the 2nd query as char/varchar datatypes. You're sending data out to a text file anyway right? You used -c option of bcp, so just make everything in the 2nd match the first, not vice versa.

    exec master.dbo.xp_cmdshell 'bcp "SELECT ''Company_ID'' UNION ALL SELECT DISTINCT cast(id as varchar( 12 ) ) FROM ##jsonparsed" queryout C:\Users\ebarnes\etl_project\company_data.csv -c -T -t'