I can’t get this query to work, I’ve tried adding quotes, removing quotes, different data types. The date format that the sp_report QuickBooks procedure is expecting is {d’yyy-mm-dd’}, and it works perfectly fine when I do this:
SELECT *
FROM OPENQUERY(QODBC,
'sp_report TrialBalance
show D_Title, C_Title, Label, Deb, Cred
parameters DateFrom = {d''2024-01-01''}, DateTo={d''2024-06-30''}, ReportBasis = ''xyz'' ')
But since I want a dynamic query with variable dates, I can’t get it to work like this:
DECLARE @From NVARCHAR(10) = '2024-01-01'
DECLARE @To NVARCHAR(10) = '2024-06-30'
DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery = '
SELECT *
FROM OPENQUERY(QODBC,
''sp_report TrialBalance
show D_Title, C_Title, Label, Deb, Cred
parameters DateFrom = {d''' + @From + '''}, DateTo = {d''' + @To + '''}, ReportBasis = ''xyz''
'')'
EXEC sp_executesql @SQLQuery
I have tried using double, single quotes. I’m guessing that this was probably because of the sp_report’s date format since it expects a string literal instead of a variable, but I just wanted to confirm and see if there would be any other solution.
My errors are usually:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘2024’
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘Accrual’
Muhammad Naqvi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1