Msg 207, Level 16, State 1 and Msg 4413, Level 16, State 1 error

Asked

Viewed 342 times

2

When I try to execute the following query, these two errors appear to me:

  • Msg 207, Level 16, State 1, Procedure V_RECLAMACOES, Line 3 [Batch Start Line 0]Invalid column name 'Reason'.
  • Msg 4413, Level 16, State 1, Line 43 Could not use view or Function 'dbo.V_RECLAMACOES' because of Binding errors.

This is my code, and I’ve also indicated where the bugs are:

use SAM_GestaoReclamacoes

declare  @DESTINATARIOS nVARCHAR(max) = NULL, -- 1º erro
  @tipo varchar(55) = NULL,
  @diaBase date = NULL,
  @ATUALIZAR_DADOS BIT = 0

declare @d date = dateadd(d, -4, getdate())

set @DESTINATARIOS = '[email protected]'
set @tipo = 'Mensal'
set @diaBase = @d


IF @DESTINATARIOS IS NULL
BEGIN
    SET @DESTINATARIOS = '[email protected]'
END

IF @tipo IS NULL
BEGIN
    SET @tipo = 'Mensal'
END

IF @diaBase IS NULL
BEGIN
    SET @diaBase = GETDATE()
END

IF @ATUALIZAR_DADOS IS NULL
BEGIN
    SET @ATUALIZAR_DADOS = 0
END

SET LANGUAGE Portuguese

IF @ATUALIZAR_DADOS = 1
BEGIN
    DROP TABLE SAM_GestaoReclamacoes

    SELECT *
    INTO SAM_GestaoReclamacoes
    FROM dbo.V_RECLAMACOES -- 2º erro
END

declare @top10reclamacoes varchar(max)
declare @html varchar(max)
declare @numero_reclamacoes_abertas_mes integer 
declare @numero_reclamacoes_fechadas_mes integer
declare @startofcurrentmonth datetime
declare @dataini datetime


SELECT @numero_reclamacoes_abertas_mes = count(*)
FROM Reclamacoes R
WHERE R.Data >= DATEADD(month, -1, @startOfCurrentMonth)


set @html = '<html>
<body>
<font face="verdana" size="6"><b><u><center>INFORMAÇÃO PARA TODA A EMPRESA</center></u></b></font><br>
<br>
<font face ="verdana "size="5"><b><center>Número reclamações abertas este mês:' + convert(varchar(50), @numero_reclamacoes_abertas_mes)

SELECT @numero_reclamacoes_fechadas_mes = count(*)
FROM Reclamacoes R
WHERE R.DataConclusao >= DATEADD(month, -1, @startOfCurrentMonth)
And Tratado = 1

set @html = @html + '<br><br>Número reclamações fechadas este mês:' + convert(varchar(50), @numero_reclamacoes_fechadas_mes)

Select top 10 @html = @html + '<tr><td>' + convert(nvarchar(50), ID) + '</td><td>' + convert(char(10), R.Data)  + '</td><td>' + ISNULL(convert(char(10),DataConclusao) , '') + '</td><td>' + convert(nvarchar(max), Exposicao) + '</td><td>' + convert(nvarchar(50), Tratado) + '</td></tr>' 
From Reclamacoes R 
Order by R.Data ASC


set @html = @html + '</html></table>'


print @html

Someone can help me?

sql procedure syntax-error binding
  • 1

    I think something’s wrong with itself VIEW. Has effectively validated the column Motivo exists in the VIEW V_RECLAMACOES?

  • I can’t access the view, but I can access the column called complaints too, and there is no column called Reason but Idmotivo.

  • 1

    Then the VIEW is that it should be at the heart of the problem. If you don’t have access to it you won’t be able to solve it.

  • So thank you for your help @Joãomartins . I will try to talk to someone higher to see if I select this problem. Vlw

  • 1

    @Joãomartins, I also imagined that it might be a permission problem, maybe this solves the second error of line 43. I just don’t know what could have caused the error in row 3.

  • @Andréfilipe Because, this error of line 43 I was able to understand, now the error of line 3 I’m not really understanding what it might be.

  • You are probably using some column that does not exist, or that your writing is incorrect...

  • @Andréfilipe already managed to solve the two problems, the problems were in the table view.

  • 1

    What were the problems in the view?

  • somehow the motif column was not well "connected", the Inner Join in the two queries had not been well made.

Show 5 more comments

1 answer

0


To solve the problem, we only had to do these two steps, as follows: - Attach the Reasons table to the Complaints table.

Inner Join Motivos M on R.IDMotivo = M.Id

  • And in the view design add also this code: In Select:

    dbo.Motivos.Name

In the Inner Join: INNER JOIN dbo.ON REASONS dbo.Reclamion.Idreason = dbo.Motivos.Id

Browser other questions tagged sql procedure syntax-error binding

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