Error converting data type varchar to numeric

Question

Error converting data type varchar to numeric

Cataster 661

I am running the following bcp command

bcp "select * from (select 'Style Code' as [Style Code], 'MY Code' as [MY Code] union select [Style Code], [MY Code] from UnPivoted_Table) q order by case [Style Code] when 'Style Code' then 0 ELSE 1 END" queryout "\\server.domain.com\CSV_Files\file1.csv" -c -t, -T -S "server1.db.com" -d "DB1"

but i am getting error:

Starting copy...
SQLState = 37000, NativeError = 8114
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Error converting data type varchar to numeric.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations

Now I think i know why this is happening, because one of the columns is a decimal datatype. but what can i do in this case then? Of course, casting is one option, but im afraid that when i want to reimport the resulting csv file into a table with corresponding decimal dataype columns, it will fail. I faced an issue in the past like this with a sql bulk copy script i wrote:

Exception calling "WriteToServer" with "1" argument(s): "The given value of type String from the data source cannot be converted to type real of the specified target column."

so I am trying to explore other options besides casting from decimal to varchar.

Ive seen recommendations to use "native format" and looked into it on MS Docs, but not sure if thats the right approach and how do i apply it to the above bcp command I have so far?

EchoLiu-MSFT 14,621 Reputation points

2021-01-12T08:03:49.08+00:00

Do you have any updates？
Please also remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

Accepted answer

4 additional answers

Your answer

EchoLiu-MSFT 14,621 Reputation points

2021-01-12T08:03:49.08+00:00

Do you have any updates？
Please also remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

Answer 1

For the sake of this question context, @Guoxiong answer to cast the columns does work. However, in the grand scheme of things, this answer will be problematic when we want to reimport back to SQL, specifically because we have to deal with the header which without RN will be randomly placed in the generated file, and with RN it will be consider as an extra row...although To skip the first header row in import operation, you can specify the -F 2 option.

SELECT [Style Code], [MY Code] FROM (SELECT 'Style Code' AS [Style Code], 'MY Code' AS [MY Code], 0 AS RN UNION SELECT CAST([Style Code] AS varchar(20)), CAST([MY Code] AS varchar(20)), 1 AS RN FROM [dbo].[UnPivoted_Table]) AS t ORDER BY RN

Therefore, @Erland Sommarskog approach/recommendation is more feasible to the requirements I have, as the goal is to export from a SQL view then reimport into an equivalent SQL table.

The better solution therefore using bcp is as follows:

Referencing MS Docs, follow the following steps:

Create a format file of the table you plan to import into (or from the source table/view...either is fine as long as they are identical, e.g. same columns, same data types, etc.) bcp DBName.dbo.ViewOrTableName format null -f "\\...\FormatFileName.fmt" -n -T -S "sqldbserver.domain.com" If your column names contain spaces, you MUST replace them with underscore in the format file based on answer here by steve smith, otherwise you'll run into this error:

SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation

Generate a bcp file to export the data from source table/view using the format file created in step1 bcp DBName.dbo.ViewOrTableName out "\\...\fileName.bcp" -f "\\...\FormatFileName.fmt" -T -S "sqldbserver.domain.com"
Import data into new table using bcp file generated in step2 bcp DBName.dbo.TableName IN "\\...\fileName.bcp" -T -S "sqldbserver.domain.com" -n

Note: -T is for Integrated Security, so use -U and -P options to specify username/password respectively if needed instead.
Note2: When bulk copying to SQL Server, specify the TABLOCK bulk copy hint and set a large batch size to improve performance; you can use -h "TABLOCK" and -b switches. Ref

Answer 2

EchoLiu-MSFT 14,621

Hi @Cataster ，

Do you have any updates？

Echo

Cataster 661 Reputation points

2021-01-13T06:23:04+00:00

@EchoLiu-MSFT Hi Echo, I already posted an answer :)

Answer 3

Tom Phillips 17,771

Because you added a "header" row with a UNION, you must convert ALL your output to conform to that data type.

You will be much happier if the first thing you do is run your query in SSMS. Then you can use BCP to run the query once it works.

Cataster 661 Reputation points

2021-01-08T22:40:42.007+00:00

@Tom Phillips I see. I did run it in SSMS and got this error. Do i really have to cast the columns? I thought native format will be able to mitigate this kinda error...
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-01-08T23:03:46.443+00:00

Yes, if you would pursue this path, you need to cast. This has nothing to do with BCP, but the fact that the data type for a column from a query is the same for all rows.

Answer 4

Guoxiong 8,206

Use this query:

SELECT * FROM (
SELECT 'Style Code' AS [Style Code], 'MY Code' AS [MY Code], 0 AS RN
UNION
SELECT [Style Code], [MY Code], 1 AS RN FROM [dbo].[UnPivoted_Table]
) AS t
ORDER BY RN

Cataster 661 Reputation points

2021-01-09T02:36:10.287+00:00

@Guoxiong This didnt work, got the same error:

Error converting data type varchar to numeric.
Guoxiong 8,206 Reputation points

2021-01-09T18:51:21.427+00:00

I think the problem might be the data type of the column [Style Code] or [MY Code]. If the data type is decimal, you would get this error. If that is the case you need to cast the columns to varchar:

SELECT [Style Code], [MY Code] FROM (SELECT 'Style Code' AS [Style Code], 'MY Code' AS [MY Code], 0 AS RN UNION SELECT CAST([Style Code] AS varchar(20)), CAST([MY Code] AS varchar(20)), 1 AS RN FROM [dbo].[UnPivoted_Table]) AS t ORDER BY RN
Cataster 661 Reputation points

2021-01-10T08:33:42.48+00:00

ok i ran the query now with the cast and it works... i see the 1st row contains the headers which is good, but its weird because theres already a header line, so this makes it duplicate header...thats what im seeing in SSMS...
Also im seeing an extra column called RN and theres a bunch of 0 nd 1's for the rows...
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-01-10T10:54:48.577+00:00

but its weird because theres already a header line, so this makes it duplicate header...thats what im seeing in SSMS...

Well, if SSMS is already giving you header, why do you ask for a second one? :-)

The RN column is there to ensure that the header row is really on top.

But maybe it is time to ask this question: why do you want the header line in the first place? What is the purpose with this export?
Cataster 661 Reputation points

2021-01-10T20:15:13.513+00:00

@Erland Sommarskog ya i suppose i dont need the header...this is my first time dabbling with bcp so i thought headers are needed because in my mind, how will bcp know which column to insert the data under? but i understand now that bcp knows how to do this naturally, so i guess i have been overthinking it :)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-01-10T20:58:18.257+00:00

BCP will certainly not read the headers.

If you don't provide a format file, BCP will work from the definition of the source table. And if it does not match the file - there will be a mess. (Most likely a slew of error messages.)
Cataster 661 Reputation points

2021-01-10T21:05:59.457+00:00

@Erland Sommarskog which is why I'm convinced to use a format file , just need it to work though lol

Answer 5

Erland Sommarskog 121.4K MVP Volunteer Moderator

I have an article on my web site on using BCP and BULK INSERT. It includes an description of how you can create a file with a header without using UNION. http://www.sommarskog.se/bulkload.html

Cataster 661 Reputation points

2021-01-08T22:48:24.307+00:00
@Erland Sommarskog Interesting! I am looking under section 6.2 Including Headers (I think thats the right section?) and i am abit confused about this:

'firstfield,num,midfield,lastfield' + char(13) + char(10)

Am i supposed to include the datatype (e.g. Char 13, 10, etc..)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-01-08T23:01:51.517+00:00

char(13) + char(10) has nothing to do with data types. This is the function char which returns the character with the ASCII code you send in. This gives you a carriage return-line feed.
Cataster 661 Reputation points

2021-01-09T03:01:33.86+00:00
@Erland Sommarskog ok I tried this out with below query in SSMS:

SELECT CASE row_number() OVER(ORDER BY(SELECT 1)) WHEN 1 THEN 'Style Code,MY Code' + char(13) + char(10) END, * FROM [dbo].[UnPivoted_Table] ORDER BY 1 DESC

and this is the result:

(no column name) Style Code MY Code Style Code,MY Code Style1 012345.7 NULL Style1 0.008726 NULL Style2 0.008765

How can i get rid of the first column, i.e. (no column name)? Or is this supposed to be included in the result in SSMS but when using BCP it would be excluded?

Also Im a bit confused about the .fmt file. Is that something I need to create before I execute the BCP command? Ive noticed that it would contain some formatting criteria but not sure if thats required in my case...

I appreciate your help btw, this seems to be the best solution ive come accross!
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-01-09T09:45:29.707+00:00

This is a query to be executed from BCP, not from SSMS. That extra column will produce the header for the first row, and then nothing at all for the remaining rows.

Yes, you need to create the format file before running BCP. The format file is a way to describe how you data file looks like. The options -r and -t to specify record and field terminators are just shortcuts. The format file is the more general way to describe the file.
Cataster 661 Reputation points

2021-01-10T08:19:39.847+00:00

@Erland Sommarskog I am honestly very confused with the format file topic. I did read up on MS Docs about it here and here, and from my understanding i could simply use -r and -t switches for the bcp command to circumvent the need for .fmt file...so i may just do that.
but suppose im using a .fmt file, Is this what my format file would look like? like is this the general format file standard you mentioned? what is 9.0 and 4?

9.0
4
1 SQLCHAR 0 0 "" 1 header Latin1_General_CI_AS
2 SQLCHAR 0 0 ",\"" 2 idcol ""
3 SQLCHAR 0 0 "\"," 3 quotedtext Latin1_General_CI_AS
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-01-10T10:50:26.327+00:00

like is this the general format file standard you mentioned? what is 9.0 and 4?

Would you believe it, this is something I discuss in my article. I would recommend it that you read it from start to end. Well, you don't need to read it to the end, but at least from the start to section 6.2.

Share via

Error converting data type varchar to numeric

4 additional answers

Your answer