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.
As a follow up question: what ever happened to the SQL import connector? Can I get a copy of that if it's not standard anymore?
N
Nic Bourne
said
over 5 years ago
In order to get only the list of columns that are returned from your query we use "SET FMTONLY ON". Place that at the top of your query in SSMS and you will see the same error. You will need to come up with a different way to form your SQL.
Wayne Pozzar
Hi everyone!
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.