Hi Marcia,

- check if HT and HTC are not defined as synonym on Oracle
- Don't use AS for table alias: FROM HEAT HT instead FROM HEAT AS HT

MartinaJ

>I HATE OpenQuery!!!!!
>
>What is it complaining about?
>
>I just can’t see it:
>
>OLE DB provider "MSDASQL" for linked server "HIP" returned message "[Oracle][ODBC][Rdb]%SQL-F-SYNTAX_ERR, Syntax error".
>Msg 7321, Level 16, State 2, Line 1
>An error occurred while preparing the query
>
>"SELECT EXTRACT(YEAR FROM HT.TAP_DT), EXTRACT(MONTH FROM HT.TAP_DT), HT.TAP_DT, HT.HEAT_NMBR, HCT.SAMPLE_LOCATION, HCT.CHMCL_ELM_SYMBL, HCT.ELM_CONTENT
>FROM HEAT AS HT INNER JOIN HEAT_CHEM_TEST AS HCT
>ON HT.ID_HEAT = HCT.ID_HEAT
>WHERE EXTRACT(YEAR FROM HT.TAP_DT) > "2009" AND HEAT_CHEM_TEST.SAMPLE_NUMBER = "1" AND HT.MELT_SOURCE = "HSP"
>AND (HCT.SAMPLE_LOCATION IN ("SR", LF","LR") OR HCT.SAMPLE_LOCATION LIKE "F%")
>AND HCT.CHMCL_ELM_SYMBL IN ("Sn","V","Al","Cu","Mo","Ni","Cr","Si","S","P","Mn","C")"
>
>for execution against OLE DB provider "MSDASQL" for linked server "HIP".
>
>Here is my code:
>
> DECLARE @month_num INT, @year_num INT, @week_num INT, @SQLstring NVARCHAR(MAX), @SQLcmd NVARCHAR(MAX)
>
> SET @SQLstring = 'SELECT EXTRACT(YEAR FROM HT.TAP_DT), EXTRACT(MONTH FROM HT.TAP_DT), '
> SET @SQLstring = @SQLstring + 'HT.TAP_DT, HT.HEAT_NMBR, '
> SET @SQLstring = @SQLstring + 'HCT.SAMPLE_LOCATION, HCT.CHMCL_ELM_SYMBL, HCT.ELM_CONTENT FROM HEAT AS HT INNER JOIN HEAT_CHEM_TEST AS HCT '
> SET @SQLstring = @SQLstring + 'ON HT.ID_HEAT = HCT.ID_HEAT '
> SET @SQLstring = @SQLstring + 'WHERE EXTRACT(YEAR FROM HT.TAP_DT) > "' + CAST(YEAR(GETDATE()) - 3 AS CHAR(4))
> SET @SQLstring = @SQLstring + '" AND HEAT_CHEM_TEST.SAMPLE_NUMBER = "1" '
> SET @SQLstring = @SQLstring + ' AND HT.MELT_SOURCE = "HSP" AND (HCT.SAMPLE_LOCATION IN ("SR", LF","LR") '
> SET @SQLstring = @SQLstring + ' OR HCT.SAMPLE_LOCATION LIKE "F%")'
> SET @SQLstring = @SQLstring + ' AND HCT.CHMCL_ELM_SYMBL IN ("Sn","V","Al","Cu","Mo","Ni","Cr","Si","S","P","Mn","C")'''
>
> SET @SQLcmd = 'SELECT * FROM OPENQUERY(HIP, ''' + @SQLstring + ')'
> INSERT INTO chemical_history (year_num, month_num, tap_dte, heat_num, location_cde, element_cde, element_content) EXEC(@SQLcmd)
>
>TIA
>
>Marcia