What am I missing to execute stored procedure in OpenQuery

Question

I am trying to use OpenQuery to insert data into a temporary table. OpenQuery suppose to execute a stored procedure that outputs hundreds of rows.

In order to not to defining Create Table #temp1, I am trying to execute OpenQuery and inserting the result into #temp1 table using into statement in Select query. I would like to know what is causing it not to execute?

1. It seems like it may quotes issue which is not placed correctly. How can I get help related to this?

2. My server contains multiple databases (instances). How do I need to define it here?

I am providing an example here of my query here

DECLARE @startDate DATETIME = CONVERT(DATE, GETDATE());
DECLARE @endDate DATETIME = CONVERT(DATE, GETDATE()+1);

DECLARE @stDate NVARCHAR(MAX) = CHAR(39) + CONVERT(NVARCHAR(255), @startDate, 23)+ CHAR(39) + ',';
DECLARE @enDate NVARCHAR(MAX) = CHAR(39) + CONVERT(NVARCHAR(255), @endDate, 23) + CHAR(39);
DECLARE @execCommand NVARCHAR(MAX) = CHAR(39) + 'EXEC dbo.getRecordsByOrderDate ' ;
DECLARE @concatCommand NVARCHAR(MAX) = @execCommand + @stDate + @enDate + CHAR(39);

DECLARE @opQuery Nvarchar(MAX) = 'Select * Into #Temp1 from Openquery(LOCALSERVER, '+ @concateCommand +') oq'

EXEC (@opQuery);

Error Message:

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

If I try to execute in below format

Select * Into #Temp1 from Openquery(LOCALSERVER, 'EXEC dbo.getRecordsByOrderDate ''2020-12-11'',''''2020-12-12''''') oq

Msg 11529, Level 16, State 1, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 31] The metadata could not be determined because every code path results in an error; see previous errors for some of these.

Msg 2812, Level 16, State 62, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 31] Could not find stored procedure 'dbo.getRecordsByOrderDate'.

Answer 1

The problem is your lack of escapting the quotes on your injected parameters. The simplest way to debug dynamic code is to PRINT/SELECT it. If you do that you'll quickly see the problem:

DECLARE @startDate DATETIME = CONVERT(DATE, GETDATE());
DECLARE @endDate DATETIME = CONVERT(DATE, GETDATE()+1);

DECLARE @stDate NVARCHAR(MAX) = CHAR(39) + CONVERT(NVARCHAR(255), @startDate, 23)+ CHAR(39) + ',';
DECLARE @enDate NVARCHAR(MAX) = CHAR(39) + CONVERT(NVARCHAR(255), @endDate, 23) + CHAR(39);
DECLARE @execCommand NVARCHAR(MAX) = CHAR(39) + 'EXEC dbo.getRecordsByOrderDate ' ;
DECLARE @concatCommand NVARCHAR(MAX) = @execCommand + @stDate + @enDate + CHAR(39);

DECLARE @opQuery Nvarchar(MAX) = 'Select * Into #Temp1 from Openquery(LOCALSERVER, '+ @concatCommand +') oq'

PRINT @opQuery;

Which returns...

Select *
Into #Temp1
from Openquery(LOCALSERVER, 'EXEC dbo.getRecordsByOrderDate '2020-12-11','2020-12-12'') oq

Voila your first parameter escapes the second parameter, and thus the error. (I Have added additional lines, as SO's choice of "prettifier" thinks # is a comment character in SQL, and I want the escaping to be highlighted in the colouring.)

When using single quotes inside a literal string, you need to escape them by "doubly them up" (''). Thus the final statement needs to me the below:

Select *
Into #Temp1
from Openquery(LOCALSERVER, 'EXEC dbo.getRecordsByOrderDate ''20201211'',''20201212''') oq

(Additional lines added for presentation again.)

So you need to fix the definitions of your 2 injected values:

DECLARE @stDate NVARCHAR(8) = CHAR(39) + CHAR(39) + CONVERT(NVARCHAR(8), @startDate, 112)+ CHAR(39) + CHAR(39) + ',';
DECLARE @enDate NVARCHAR(8) = CHAR(39) + CHAR(39) + CONVERT(NVARCHAR(8), @endDate, 112) + CHAR(39) + CHAR(39);

Note I change the data types and styles too. The style as yyyy-MM-dd isn't unambiguous in SQL Server, and the data types, as you don't need 2 billion characters for a date.

---

Note, however, that after executing this you still won't be able to access #Temp1. A temporary table only persists for the scope it was created in, and that scope is the dynamic SQL's. You'll need to use a permanent table inside the dynamic statement, or CREATE a temporary table outside of the dynamic statement to use it outside of it.