Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Stored procedure- access temp table from openquery

Author  Topic  twkurtka Starting Member 1 Post Posted - 2012-05-18 : 06:35:14 Hi, I have a stored procedure where I setup 2 temporary tables, store data there, access them, drop tables at the end. I want to be able to insert values into a temp table with a condition referencing another temp table. Like this : SET @SQL = 'INSERT INTO '+@TABLE_NAME1 +' (Item) SELECT A.Item FROM OPENQUERY('+@PR1DSN +','+ '''select Item from (select Item from YYY where Item IN (select Item from '+@TABLE_NAME2 +') // PROBLEM with ur'''+' ) AS A' @TABLE_NAME2 is already setup in a similar fashion prior to this sql and contains single column 'Item' with values. My sql chokes up on the where clause, it gives error that @PR1DSN.@TABLE_NAME2 is not recognized. That's an issue, since this table is not in @PR1DSN; how can I make it force to look for this table on the sql server and not on @PR1DSN database? Is there some syntax I am not using correctly here? Or is it technically impossible to look up data from temporary table while within openquery?