Unclosed quotation mark after the character string 'BDR_POST-BI ( SCT'

Question

I have store procedure that can run smoothly before. But right now when I try to run it get me that error "Msg 105, Level 15, State 1, Line 84 Unclosed quotation mark after the character string 'BDR_POST-BI ( SCT'. Msg 102, Level 15, State 1, Line 84 Incorrect syntax near 'BDR_POST-BI ( SCT'.". This is my store procedure by the way

USE [dbbib]
GO
/****** Object:  StoredProcedure [dbo].[sp_QuerySummary]    Script Date: 10/4/2018 4:13:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_QuerySummary]
AS
BEGIN
DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + cast(FailureMode as varchar) + ']',
'[' + cast(FailureMode as varchar)+ ']')
FROM (SELECT    distinct    FailureMode FROM tblBIB )as s
GROUP BY FailureMode
order by FailureMode

DECLARE @columns2 VARCHAR(8000)

SELECT @columns2 = COALESCE(@columns2 + ',isnull([' + cast(FailureMode as varchar) + '],0) as ['+ cast(FailureMode as varchar) +']',
'isnull([' + cast(FailureMode as varchar)+ '],0) as ['+ cast(FailureMode as varchar)+']')
FROM (SELECT    distinct    FailureMode FROM tblBIB )as s
GROUP BY FailureMode
order by FailureMode

DECLARE @columns3 VARCHAR(8000)

SELECT @columns3 = COALESCE(@columns3 + '+isnull([' + cast(FailureMode as varchar) + '],0)',
'isnull([' + cast(FailureMode as varchar)+ '],0)')
FROM (SELECT    distinct    FailureMode FROM tblBIB where FailureMode not like 'QUARANTINE' and FailureMode not like 'BARE' and FailureMode not like 'PM' and FailureMode not like 'TEMPORARY HOLD_SET B')as s
GROUP BY FailureMode
order by FailureMode

DECLARE @query VARCHAR(8000)

SET @query = '

declare @table table (BibType nvarchar(100), CntActive int)

insert into @table
select BibType,count(BibID) as CntActive from(
select substring(BibID,1,6) as BibType, BibID from(
select * ,
case
when len(BibID)>1   and len(BIBRecall)>1 then 1
when len(BibID)=1   and len(BIBRecall)>1 then 1
else 0
end as selection
from(
SELECT        isnull(a.BibID,0) as BibID, isnull(tblBIB.BIBRecall,0)as BIBRecall
FROM            (SELECT DISTINCT BibID
                          FROM            Orca.dbo.tblLatestTurnAllBIB
                          WHERE        (CONVERT(date, DTDone) = CONVERT(date, GETDATE()))) AS a full outer JOIN
                         tblBIB ON a.BibID = tblBIB.BIBRecall
)as s
)as s where selection=0
)as q group by BibType



declare @table2 table ( CntActive int, rn int)

insert into @table2
select count(BibID) as CntActive ,0 as rn from(
select substring(BibID,1,6) as BibType, BibID from(
select * ,
case
when len(BibID)>1   and len(BIBRecall)>1 then 1
when len(BibID)=1   and len(BIBRecall)>1 then 1
else 0
end as selection
from(
SELECT    isnull(a.BibID,0) as BibID, isnull(tblBIB.BIBRecall,0)as BIBRecall
FROM            (SELECT DISTINCT BibID
                          FROM            Orca.dbo.tblLatestTurnAllBIB
                          WHERE        (CONVERT(date, DTDone) = CONVERT(date, GETDATE()))) AS a full outer JOIN
                         tblBIB ON a.BibID = tblBIB.BIBRecall
)as s
)as s where selection=0
)as q 


select*,row_number()over(order by total desc) as rn from(
SELECT Device,DeviceType,'+ @columns3 +' as Total,isnull(q.CntActive,0) as CntActive,'+ @columns2 +',q.BibType
FROM (SELECT        a.DeviceType, tbldevtypev2.Device, a.Cnt, a.FailureMode
FROM            (SELECT        DeviceType, COUNT(BIBRecall) AS Cnt, FailureMode
                          FROM            tblBIB
                          GROUP BY DeviceType, FailureMode) AS a left outer JOIN
                         tbldevtypev2 ON a.DeviceType = tbldevtypev2.[BIB Type]
)as a
PIVOT
(
MAX(Cnt)
FOR [FailureMode]
IN (' + @columns + ')
)
AS a left outer  join @table q on a.DeviceType = q.BibType
)as p 


union  all


SELECT ''Grand Total'' as Device,'''' as DeviceType,'+@columns3+' as Total,*,'''' as BibType  from(
Select isnull(i.CntActive,0) as CntActive,o.* from(
SELECT '+@columns2+',0 as rn
FROM (
SELECT        COUNT(BIBRecall) AS Cnt, FailureMode
                          FROM            tblBIB
        
                          GROUP BY  FailureMode 
)as a
PIVOT
(
MAX(Cnt)
FOR [FailureMode]
IN (' + @columns + ')
)
AS p
)as o full outer join @table2 i on o.rn= i.rn
)as p

'

EXECUTE(@query)
END

Maybe you have ' or " characters on a "line String". Consider use parameters, or perform a line.Replace (""","") etc. to remove them — Morcilla de Arroz, Sep 26 '18 at 07:48

score 1 · Accepted Answer · answered Sep 26 '18 at 08:10

When inserting data into a database consider using SQL parameters to avoid SQL injection:

cmd.Parameters.Add("@line", SqlDbType.VarChar).Value = line

I would also consider implementing Using:

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

Using con As New SqlConnection(str),
      cmd As New SqlCommand("INSERT INTO table2 ([a], [roll], [c]) VALUES (1, 2, @line)", con)

    cmd.Parameters.Add("@line", SqlDbType.VarChar).Value = line

    con.Open()

    cmd.ExecuteNonQuery()
  End Using

I would also consider looping through the file inside the Using statement to save creating the SQL objects over and over again:

Using con As New SqlConnection(str),
      cmd As New SqlCommand("INSERT INTO table2 ([a], [roll], [c]) VALUES (1, 2, @line)", con)
    cmd.Parameters.Add("@line", SqlDbType.VarChar)

    con.Open()

        Using sr As New StreamReader(path)
            Do While sr.Peek() >= 0
                cmd.Parameters("@line").Value = sr.ReadLine
                cmd.ExecuteNonQuery()
            Loop
        End Using
    End Using
End Sub

_{This code is untested, I haven't the environment but it should give you something to work with.}

score 0 · Answer 2 · answered Sep 26 '18 at 06:22

0

what is the value of line?

try

cmd = New SqlCommand("insert into table2 ([a], [roll],[c]) values (1, 2, '''" & line & "''')", con)

answered Sep 26 '18 at 06:22

Sonja

575
1
6
20

Data of the log file – sapkoklu Sep 26 '18 at 06:45
in the data of the log file you have the sign ' or the sign ". Try with double or triple ' like i posted – Sonja Sep 26 '18 at 09:43

score 0 · Answer 3 · answered Sep 26 '18 at 08:01

0

Definitely use parameters. Check you database to get the correct 'SqlDbType'

Dim cmd As New SqlCommand(("insert into table2 ([a], [roll],[c]) values (1, 2, @line);", con))
cmd.Parameters.Add("@line", SqlDbType.VarChar).Value = line

answered Sep 26 '18 at 08:01

Mary

14,926
3
18
27

Unclosed quotation mark after the character string 'BDR_POST-BI ( SCT'

3 Answers3