linked Progress query (from SQL Server), that uses parameters -- trouble with date

[JJames100]

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)".

 

[MaximMonin]

Re: linked Progress query (from SQL Server), that uses parameters -- trouble with da

I had the same problem when used dynamic progress queries.
And I solved it by using DATE(datevar)

I am not sure it helps in your case.
Just try:
set @DateBegin =DATE('01/01/2009')
or
..... AND (pt_history.date_of_change >= ' DATE(@DateBegin) '

 

[JJames100]

Re: linked Progress query (from SQL Server), that uses parameters -- trouble with da

thanks for the reply. This isn't working. Date is not recognized by SQL Server as a valid function - - and I am already declaring these as datetime
My guess is that the variable is not getting converted in the SQL string, so it's trying to convert a word to a date.

 

[JJames100]

Re: linked Progress query (from SQL Server), that uses parameters -- trouble with da

I got it to work ...this string which is encased in variable ...:

'select h.client_id,h.PtName,h.team_ID,h.date_of_change,h.HistoryEvent,h.new_data,h.Program_Status, h.pt_History_ID from
openquery(SCPROD,''SELECT pt_history.client_id, pt_program.last_name + '''', '''' + pt_program.first_name AS PtName, pt_program.team_id, pt_history.date_of_change, pt_history.event AS HistoryEvent, pt_history.new_data, pt_Program.Program_Status, pt_history.pt_History_ID
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]'

....results in the following, which works:

select h.client_id,h.PtName,h.team_ID,h.date_of_change,h.HistoryEvent,h.new_data,h.Program_Status, h.pt_History_ID from
openquery(SCPROD,'SELECT pt_history.client_id, pt_program.last_name + '', '' + pt_program.first_name AS PtName, pt_program.team_id, pt_history.date_of_change, pt_history.event AS HistoryEvent, pt_history.new_data, pt_Program.Program_Status, pt_history.pt_History_ID
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 >= ''01/01/09'' And pt_history.date_of_change <= ''02/01/09'' ) 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]

 

[JJames100]

Re: linked Progress query (from SQL Server), that uses parameters -- trouble with da

To use in SQL Server stored proc had to change as follows (otherwise got error about converting from date to string):

select h.client_id,h.PtName,h.team_ID, h.date_of_change,h.HistoryEvent,h.new_data,h.Program_Status, h.pt_History_ID from
openquery(SCPROD,''SELECT pt_history.client_id, pt_program.last_name + '''', '''' + pt_program.first_name AS PtName, pt_program.team_id, pt_history.date_of_change, pt_history.event AS HistoryEvent, pt_history.new_data, pt_Program.Program_Status, pt_history.pt_History_ID
FROM (pub.pt_history INNER JOIN pub.pt_program ON pt_history.client_id = pt_program.client_id)
WHERE ( pt_history.client_id > 3000 and (pt_program.team_id is not Null) AND (pt_history.date_of_change >= ' + '''''' + CONVERT(VARCHAR(10),@DateBegin,101) + '''''' + ' And pt_history.date_of_change <= ' + '''''' + CONVERT(VARCHAR(10),@DateEnd,101) + '''''' + ' ) 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]
where h.pt_History_ID not in (select PtHistoryID from History) '