I am having a hard time with temp tables in a SQL export connector. I have an query that runs in SSMS but does not run in the IOM SQL interpreter.
Here is an example that shows what I am talking about
SELECT * into #temptest FROM RECORDS WHERE RECORDS.ID = 100
SELECT * FROM #temptest
DROP TABLE #temptest
That work fine in SSMS but gets an error about the #temptest being an invalid object in IOM. What's the deal?
P.S. I know that I should probably be declaring the temp table first but according to SQL Server docs I don't need to do that and there are already many queries written here that don't so I would like to not have to rewrite them.