I'm having problems executing the following in SQL Server. I'm using a linked Progress DB with link name SCPROD and also using OPENQUERY, but must enclose stmt in string, so can pass params into:
declare @DateBegin datetime
declare @DateEnd datetime
DECLARE @TSQL varchar(8000)
set @DateBegin = '01/01/2009'
set @DateEnd = '02/01/2009'
--note: APPEARS THAT '''' IS NECESSARY AROUND DATES, (WITH OR WITHOUT +) - will not work without anything around params. GETTING 'INVALID DATE STRING (7497)' WHEN RUNNING it and can't find any info about....
select @TSQL = 'select * from
openquery(SCPROD,''SELECT pt_history.client_id, pt_program.last_name + '''', '''' + pt_program.first_name AS Pt_Name, pt_program.team_id, pt_history.date_of_change, pt_history.event AS HistoryEvent, pt_history.new_data, pt_Program.Program_Status
FROM (pub.pt_history INNER JOIN pub.pt_program ON pt_history.client_id = pt_program.client_id)
WHERE ((pt_program.team_id is not Null) AND (pt_history.date_of_change >= '''' @DateBegin '''' And pt_history.date_of_change <= '''' @DateEnd '''' ) AND (pt_history.event_id) In (1,5,8,101,102,103))
ORDER BY pt_history.client_id, pt_history.date_of_change'')as h
INNER JOIN Program ON h.program_status = Program.[ProgramID]'
exec (@TSQL)
error follows....
OLE DB provider "MSDASQL" for linked server "SCPROD" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Invalid date string (7497)".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT pt_history.client_id, pt_program.last_name + ', ' + pt_program.first_name AS Pt_Name, pt_program.team_id, pt_history.date_of_change, pt_history.event AS HistoryEvent, pt_history.new_data, pt_Program.Program_Status
FROM (pub.pt_history INNER JOIN pub.pt_program ON pt_history.client_id = pt_program.client_id)
WHERE ((pt_program.team_id is not Null) AND (pt_history.date_of_change >= ' @DateBegin ' And pt_history.date_of_change <= ' @DateEnd ' ) AND (pt_history.event_id) In (1,5,8,101,102,103))
ORDER BY pt_history.client_id, pt_history.date_of_change" for execution against OLE DB provider "MSDASQL" for linked server "SCPROD".
If I try removing the single quotes around the date params in code, I get the following error
Syntax error in SQL statement at or about "@DateBegin And pt_history.date_of_chan" (10713)".