0

I have seen a couple solutions to this SQL error(8115) but none seems to work for me. I think its because I am using dynamic SQL script. I dynamically pull data from multiple servers that have the same structure and insert into a central table. The issue I have having is that when using dynamic script as seen below I keep getting an error.

DECLARE @SQL NVARCHAR(4000);
DECLARE @counter int = 0;
DECLARE @scriptcnt int;
declare @startdate varchar(8) = '20180304';-- cast(@startdate as date)
declare @enddate varchar(8) = '20180305';--cast(@enddate as date)

select srv.name
INTO #ServerNames    
from sys.servers srv
where name like 'Pulse%'
order by 1;

select name,right(name,5) store_no
into #locationCode
from #SERVERNAMES;


With ServerCTE AS (
SELECT Root=NAME + '.[POS].[dbo].' 
FROM #SERVERNAMES)
Select 
Root + '[Customer]' as Customer
INTO #Tables
from ServerCTE

Begin Transaction
delete from WFLCustomerMaster
--where Location_Code = '16450'--@storeNum
commit;

select 
'
        Begin TRY
        Begin Transaction
        Insert into WFLCustomerMaster(.....)
    SELECT .....
        FROM '+ Customer + '
        where [Last_Order_Date] between '+ @startdate+ ' and '+ @enddate+ ' [This line is the issue]
        commit;
        END TRY
        BEGIN CATCH
        SELECT 
                ERROR_PROCEDURE() as nameofprocedure,
                ERROR_NUMBER() AS errorcode,
                error_line() as errorline,
                ERROR_MESSAGE() as errormessage

        END CATCH' as Customer into #query
from #Tables a, #locationCode b
where left(a.Customer,13) = b.name

select @scriptcnt = count(*) from #query

while @counter < @scriptcnt
begin
    set @counter = @counter + 1
    SET @SQL = (SELECT Customer FROM 
                (SELECT  ROW_NUMBER() OVER (ORDER BY Customer) AS ID, Customer FROM #query) MySQL
                WHERE ID=@Counter )
    EXEC SP_EXECUTESQL @SQL
end

I tried converting @startdate and @enddate to date, datetime and datetime2 but nothing works. I realize that when I hard code the date it works as per below:

...'where [Last_Order_Date] between ''20180306'' and ''20180306'''....

So I know that this part is the issue. Can someone assist?

Castell James
  • 329
  • 6
  • 23
  • 3
    Since you haven't enclosed the @startdate and @enddate values in single-quotes, the value is treated as an integer literal. Although you could specify `where [Last_Order_Date] between '''+ @startdate+ ''' and '''+ @enddate+ '''`, I suggest you instead use a parameterized query, with the parameter type matching the data type of the `[Last_Order_Date]` column. – Dan Guzman Mar 11 '18 at 03:41
  • I see. This works. Can you post this as the answer? I want to accept it. – Castell James Mar 11 '18 at 14:16
  • I still found this to be weird given that it was defined as a string but I understand. – Castell James Mar 11 '18 at 14:18
  • What is the data type of [Last_Order_Date]? Is it the same in all tables? I can post a more detailed answer if you provide that info. – Dan Guzman Mar 11 '18 at 14:44
  • The data type is datetime in all tables. – Castell James Mar 11 '18 at 15:18

0 Answers0