Msg 8114 error in SQL Server while performing query

Asked

Viewed 1,011 times

1

I’m having a problem executing a query in the SQL Server, where I try to bring a column with a conversion of done in a column like DATE and another column with an HTML code mounted.

Follow the query below:

select convert(varchar, R.Data, 121) , '</td><td>' + C.No + '</td><td>' + C.Nome
    + '</td><td>' + V.IdVendedor + '</td><td>' + V.Vendnm + '</td></tr>'
    From dbo.Reclamacoes R 
    Inner Join dbo.PHC_CLIENTES_SAMSYS C On R.IdCliente = C.Id
    Inner Join dbo.PHC_VENDEDORES_SAMSYS V on R.IdVendedor = V.IdVendedor

The error below is displayed

Converting data type error varchar to Numeric Msg 8114, Level 16, State 5, Line 1

Can someone help me?

sql sql-server select

  • Inform the full message so we can help you

  • Type convection error , according to a survey, see this "Convert" ...

  • "Converting data type error varchar to Numeric" appears in the first line of the code.

  • @Sorack, Miguel sent me an e-mail( Img1, Img2 ) asking for help on your problem, by asking a few questions for it, I was able to help you via Hangouts, however, I thought it was nice to bring the case to Sopt in case someone has the same problem Msg 8114 in SQL Server this post serves as the solution. If you find it valid to reopen this question, I will post my answer that I used to solve his problem.

  • @Andréfilipe I think valid yes, I have even written an answer, but it is necessary that more people vote to reopen

  • Tranquil @Sorack, I made an issue of his question explaining your case better. I thank the feedback.

  • @Andréfilipe thanks for the edition. I am new to sql, and I just want to learn as soon as possible, but I am not good at explaining myself and I apologize for that.

Show 2 more comments

2 answers

2

The problem is that you are trying to concatenate some field that is a numeric so the DBMS tries to convert the rest of the text to numeric when trying add up and not concatenate. In order for this problem not to happen you should tell the DBMS that the numeric fields should be transformed into VARCHAR. I don’t know what fields your query are not text, so "kicked" which would be. Even if not the ones I suggested, the essence is the same.

SELECT CONVERT(VARCHAR, r.data, 121),
       '</td><td>' + CAST(c.no AS VARCHAR) + '</td><td>' + c.nome + '</td><td>' + CAST(v.idvendedor AS VARCHAR) + '</td><td>' + CAST(v.vendnm AS VARCHAR) + '</td></tr>'
  FROM dbo.reclamacoes r
 INNER JOIN dbo.phc_clientes_samsys c ON r.idcliente = c.id
 INNER JOIN dbo.phc_vendedores_samsys v ON r.idvendedor = v.idvendedor

  • I thank Sorack for wasting his time trying to help me, but the reply of @Andréfilipe solved the problem. Thanks

  • No Miguel Meireles, there is no lost time when it comes to sharing knowledge. We must thank you for the availability of @Sorack, only. Young hugs!

2


When I started to analyze your query:

  1. I thought there might be something wrong with CONVERT(VARCHAR, r.data, 121) because there is no lenght(optional), I did a search (although there is nothing wrong here), I thought it was nice a reply of @Colin in the stackoverflow.with about stylization results(third parameter of this function, style) possible in the CONVERT(), a very interesting information.

  2. Like the @Sorack commented in his reply, the SQL Server does not allow concatenation of data_types different, in your case, the concatenation of VARCHAR like INTEGER, so it is necessary to transform all of them into a single type so that your database does not raise exception, I used the own CONVERT(), to modify all data types used in its concatenation to the character type. 

    SELECT convert(nvarchar(MAX), R.Data, 121) AS DATA,
     '</td><td>' + convert(nvarchar(MAX), C.No) + '</td><td>' + C.Nome + '</td><td>' + convert(nvarchar(MAX), V.IdVendedor) + '</td><td>' + convert(nvarchar(MAX), V.Vendnm)+'</td></tr>' AS tabela_HTML
FROM dbo.Reclamacoes R
INNER JOIN dbo.PHC_CLIENTES_SAMSYS C ON R.IdCliente = C.Id
INNER JOIN PHC_VENDEDORES_SAMSYS V ON R.IdVendedor = V.IdVendedor

Remember that if you don’t add one alias AS For your column, the column Omeclatura will come a little confused.

Learn more about the CONVERT function() here.

  • Thank you very much André Filipe! For having edited the answer and having solved your problem. I highly recommend those who have difficulty in this topic to follow by this answer.

Browser other questions tagged sql sql-server select

You are not signed in. Login or sign up in order to post.