Information générale
Forum:
Microsoft SQL Server
Versions des environnements
SQL Server:
SQL Server 2005
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement