It's your syntax
The from statement should be followed by the table name you are doing the select from,
This value openquery is not valid.
JeffJon is correct as i had forgotton the original example i provided for you used a linked server to access the source data as it is a few years since i did it (hence the openquery command). Will check to see if i have a more appropriate example if you cannot get help on-site but are your SAP tables on the same server as FDM?
The SAP Tables are on another server, after changing the script i now get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'from'. Error during data import
strSQL = "INSERT INTO " & strWorkTableName
strSQL = strSQL & "(PartitionKey,CatKey,PeriodKey,DataView,CalcAcctType,DataValue,Account,Entity,ICP)"
strSQL = strSQL & "Select " & RES.PlngLocKey & "," & RES.PlngCatKey & ","
strSQL = strSQL & " Year,Period,Entity,Account,Inter_Company_Entity_HFM,'YTD',Value, 9, "
strSQL = strSQL & " from ODI_WORK_MARS.dbo.TMP_HFM_DATA_EXTRACT_TIN1 "
I clreared the syntax error but it came back with following:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tWfdmadm636464887870'. Error during data import.
Anyone seen this before? It refers to the FDM tables but can't see why?
Ian, the SAP Tables are on different server to FDM, why does the error on import refer to 'invalid object name - FDM table refs?
for any syntax error I would suggest to debug your SQL query in FDM.
For that you can write it into flat file, and then execute this query in any SQL tool (toad, sql developer, etc.)
That table "tWfdmadm636464887870" is a temporary table created by FDM during the import process. You don't see it because it is created and dropped. Only under specific situations this table is not dropped but 99% of the times it is.