>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