I thought maybe the failure to reference the server and schema explicitly might be a violation of syntax? I seem to remember getting a syntax error in TSQL building a string like that and not using server.schema as part of the table reference.

Admittedly it is a guess on my part <s>

>>Could the problem be in referencing the linked server? e.g. in SQL server you need to use the schema between the server and table
>>
>>as Servername.dbo.tablename for example
>>
>>What happens if you explicitly reference the server.schema before the tablenames?
>>
>>
>That's a good point, but since there is a syntax error, the error in the query needs to be corrected first.
>
>
>>>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