SQL Server stored procedure dynamic query (openquery) for a linked SQLite database returns errors

Question

I have SQL Server 2019 and have linked an SQLite database called [PSMDB_WIN_SVR] to it.

My objective is to retrieve data from the linked database for all records greater than or equal to a double variable parameter representing Julian Date/Time. The query runs fine in the native SQLite environment:

    SELECT julianDateUTC,
           frequency,
           Vrms, 
           I1rms,
           I2rms,
           I3rms,
           P1,
           P2, 
           P3,
           ProcessorTemp,
           Quality
    FROM tblRealTimeData
    WHERE  julianDateUTC >=  :JD
    ORDER BY julianDateUTC;

I have created the following dynamic stored procedure in SQL Server:

USE [pMonNetworkServer]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE getNewDataProc
    @jd VARCHAR(50)
AS
    DECLARE @sql VARCHAR(MAX)
    SET @sql = 'SELECT * FROM OPENQUERY(PSMDB_WIN_SVR, ' + 
               '' + 'SELECT julianDateUTC,
                     frequency,
                     Vrms, 
                     I1rms,
                     I2rms,
                     I3rms,
                     P1,
                     P2, 
                     P3,
                     ProcessorTemp,
                     Quality
            FROM tblRealTimeData
            WHERE  julianDateUTC >= ' + '' + @jd + '' + 
          ' ORDER BY julianDateUTC;' + '' + ')'
    EXEC (@sql)
GO

When I execute this using SSMS I get the following errors:

Execution of Stored procedure

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.

Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ')'.

Obviously my syntax is incorrect. The OPENQUERY SQL string has to be set in quotes as a literal and I am using '' for this.

I have also surrounded the @jd parameter in a single quote. I also tried without this, but I get the error whatever I do.

Can someone help with this please?

The way you debug dynamic SQL is replace `exec(@sql)` with `print(@sql)` at which point you have static SQL and its easy to debug. If you are still stuck at that point, then post the result of `print(@sql)` so we can assist. — Dale K, May 24 '20 at 01:15
This `+ '' +` does nothing useful. You are simply concatenating an empty string. You probably meant to use `+ '''' + ` (count those single quotes carefully). More importantly, you must actually SEE your query in order to debug it. — SMor, May 24 '20 at 01:33

score 0 · Answer 1 · edited May 24 '20 at 07:15

The insertion of the ' delimiter by using + '' + doesn't work.

The solution is to turn off delimeters and use + "'" +

I kind of suspected this, but because Microsoft removed debugging from SSLS 2019 duhhh! I installed 17 and confirmed the problem.

The correct code is

USE [pMonNetworkServer]
GO
/****** Object:  StoredProcedure [dbo].[getNewDataProc]    Script Date: 24/05/2020 13:13:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[getNewDataProc2]
@jd VARCHAR(50)
AS
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT * FROM OPENQUERY(PSMDB_WIN_SVR, ' +   
                "'" + 'SELECT julianDateUTC,
                     frequency,
                     Vrms, 
                     I1rms,
                     I2rms,
                     I3rms,
                     P1,
                     P2, 
                     P3,
                     ProcessorTemp,
                     Quality ' +
            ' FROM tblRealTimeData ' +
            ' WHERE  julianDateUTC >= ' + @jd +  
            ' ORDER BY julianDateUTC;' + "'" + ')'
END
EXEC (@sql)

SQL Server stored procedure dynamic query (openquery) for a linked SQLite database returns errors

1 Answers1