SET @SQL='
	(select * from openquery
		('+ @One +',''
                    select *
                    from state
                    where
                    (city in 
                        (select city from openquery 
                            ('+ @Two +',''	
                            select city
                            from state_smaller ''
                            )
                        )   
                    )
                    with ur ''
		) as A
	) B '

ALTER  PROCEDURE [dbo].[FluidStores]
( 
@One varchar(20),
@Two varchar(20)
)

AS
Begin
DECLARE @SQL as varchar(8000)
DECLARE @SQL_t as varchar(8000)
DECLARE @SQL_t_insert as varchar(8000)
DECLARE @SQL_t_drop as varchar(8000)
DECLARE @TABLE_NAME VARCHAR(50)

SET NOCOUNT ON

SET @TABLE_NAME = '##FLUID_STORES'
SET @SQL_t = 'CREATE TABLE '+@TABLE_NAME +'([store] [VARCHAR] (10)  NOT NULL) '
EXEC (@SQL_t)

SET @SQL_t_insert = 'INSERT INTO '+@TABLE_NAME +'  (store)
    SELECT fluid_stores_from_Two.store FROM OPENQUERY
    ('+@Two +','+
        '''select store 
        from dbo.transactionlog 
        where column1 like ''''FLUID%''''
    '''+') AS fluid_stores_from_Two'
EXEC(@SQL_t_insert)


SET @SQL = '
SELECT *
FROM OPENQUERY
( ' +@Two+ ',' +
    '''select  Store from  TransactionLog''' + '
) all_stores_from_Two

INNER JOIN 

(	select *
	from openquery
	(	'+ @One +',''
		select route
		from cartons 
/* if i comment out the next line, everything works just fine */
                where route in (SELECT DISTINCT store FROM  '+ @TABLE_NAME +')
		with ur ''
	) 
) stores_from_One on (stores_from_One.route = all_stores_from_Two.store)

'

EXEC (@SQL)

SET @SQL_t_drop = 'DROP TABLE ' + @TABLE_NAME
EXEC (@SQL_t_drop)

End

ALTER     PROCEDURE [dbo].[fluid_simple]
( @One varchar(20), @Two varchar(20) )

AS
Begin
DECLARE @SQL as varchar(8000)
DECLARE @SQL_t as varchar(8000)
DECLARE @SQL_t_insert as varchar(8000)
DECLARE @SQL_t_drop as varchar(8000)
DECLARE @TABLE_NAME VARCHAR(50)

SET NOCOUNT ON

SET @TABLE_NAME = '##FLUID_STORES'
SET @SQL_t = 'CREATE TABLE '+@TABLE_NAME +'([store] [VARCHAR] (10)  NOT NULL) '
EXEC (@SQL_t)

SET @SQL_t_insert = 'INSERT INTO '+@TABLE_NAME +'  (store)
SELECT x.store FROM OPENQUERY('+@Two +','+
'''select distinct case 
        when tl_typeid = 1 then column7
        when tl_typeid = 35 then column4
        end as store 
from transactionlog 
where (column1 like ''''FLUID%'''' or column3 like ''''FLUID%'''')
and tl_typeid in (1, 35)'''+') AS x'

PRINT(@SQL_t_insert )
EXEC(@SQL_t_insert)

SET @SQL = '
SELECT distinct coalesce (Two.store, One.rte_id) as store
FROM OPENQUERY
( ' +@Two+ ',' +
    '''
	select  case when tl_typeid = 1 then column7
		when tl_typeid = 35 then column4
		end as Store
	from  TransactionLog
	where   tl_typeid = 35
	and datediff(dd, tl_date,GetDate()) <= 0
	and column1 like ''''FLUID%''''
	''' + '
) Two

INNER JOIN 

(	select *
	from openquery
	(	'+ @One +',''
		select distinct rte_id
		from carton_hdr
		where stat_code = 50
		and rte_id in (SELECT DISTINCT store FROM  '+ @TABLE_NAME +')		with ur ''
	) 
) One on (One.rte_id = Two.store)
'

PRINT(@SQL)
EXEC (@SQL)

SET @SQL_t_drop = 'DROP TABLE ' + @TABLE_NAME
EXEC (@SQL_t_drop)

End

Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDASQL' reported an error.  

INSERT INTO ##FLUID_STORES  (store)
SELECT x.store FROM OPENQUERY(THD_PM_TLDB_5085,'select distinct case 
        when tl_typeid = 1 then column7
        when tl_typeid = 35 then column4
        end as store 
from transactionlog 
where (column1 like ''FLUID%'' or column3 like ''FLUID%'')
and tl_typeid in (1, 35)') AS x


SELECT distinct coalesce (Two.store, One.rte_id) as store
FROM OPENQUERY
( THD_PM_TLDB_5085,'
	select  case when tl_typeid = 1 then column7
		when tl_typeid = 35 then column4
		end as Store
	from  TransactionLog
	where   tl_typeid = 35
	and datediff(dd, tl_date,GetDate()) <= 0
	and column1 like ''FLUID%''
	'
) Two

INNER JOIN 

(	select *
	from openquery
	(	DPR671DC,'
		select distinct rte_id
		from carton_hdr
		where stat_code = 50
		and rte_id in (SELECT DISTINCT store FROM  ##FLUID_STORES)
		with ur '
	) 
) One on (One.rte_id = Two.store)

[OLE/DB provider returned message: [IBM][CLI Driver][DB2/AIX64] SQL0204N  "WMUSER01.##FLUID_STORES" is an undefined name.  SQLSTATE=42704
]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005:   ].

SET @SQL='
                        openquery 
                            ('+ @Two +',''	
                            select city
                            from state_smaller ''
                            )'

SELECT @SQL = REPLACE(@SQL, '''', '''''')

SELECT @SQL='
	openquery
		('+ @One +',''
                    select *
                    from state
                    where
                    (city in
                        (select city from ' + @SQL + '
                        )   
                    ) with ur ''
		)'

SELECT @SQL='
	(select * from ' + @SQL + '
                    as A
	) B '

PRINT @SQL

	(select * from 
	openquery
		(ServerOne,'
                    select *
                    from state
                    where
                    (city in
                        (select city from 
                        openquery 
                            (ServerTwo,''	
                            select city
                            from state_smaller ''
                            )
                        )   
                    ) with ur '
		)
                    as A
	) B 

SELECT DISTINCT store FROM  ##FLUID_STORES

quote:

---

Originally posted by Kristen

You need to double-up the quotes in the inner OPENQUERY

---

quote:

---

Originally posted by yosiasz

manually do the following, anything in RED i believe is problematic

---

quote:

---

Originally posted by yosiasz

3. Does this work and bring anything?

SELECT DISTINCT store FROM  ##FLUID_STORES

---

quote:

---

Originally posted by yosiasz
[IBM][CLI Driver][DB2/AIX64] SQL0204N "WMUSER01.##FLUID_STORES"

---

quote:

---

Originally posted by yosiasz

:) so either qualify it with it's database schema name or create it in @One or make it a table variable

---

quote:

---

Originally posted by umniza

quote:

---

Originally posted by Kristen

You need to double-up the quotes in the inner OPENQUERY

---

Thaks Kristen, ... I will try your code next.

---

I am soooooo confused with the quotes . Is there anyway I could get below code reviewed for the correctness of quotes. I tried to model after Kristen's code, it didnt work (I admit I couldnt follow the flow of it once I started retrofitting my code to Kristen's sample).

Stored procedure code :

ALTER       PROCEDURE [dbo].[fluid_simple_doubleup]
( @One varchar(20),@Two varchar(20))

AS
Begin
DECLARE @SQL as varchar(8000)
                      
SET @SQL = 'SELECT distinct *

FROM OPENQUERY
( ' +@Two+ ',' +
    '''
	select  case when tl_typeid = 1 then column7
		when tl_typeid = 35 then column4
		end as Store
	from  TransactionLog
	where   tl_typeid = 35
	and datediff(dd, tl_date,GetDate()) <= 0
	and column1 like ''''FLUID%''''
	''' + '
) Two
INNER JOIN 
(	select *
	from openquery
	(	'+ @One +',''
		select distinct rte_id
		from carton_hdr
		where stat_code = 50 
		and rte_id in
		(	select * OPENQUERY
			('+@Two +',''
				select distinct case 
        			when tl_typeid = 1 then column7
        			when tl_typeid = 35 then column4
        			end as store 
				from transactionlog 
				where (column1 like ''''FLUID%'''' or column3 like ''''FLUID%'''')
				and tl_typeid in (1, 35)''
			) 
		)
		with ur ''
	) 
) One on (One.rte_id = Two.store)
'

End

print (@SQL)
EXEC (@SQL)

SELECT distinct *

FROM OPENQUERY
( THD_PM_TLDB_5085,'	select  case when tl_typeid = 1 then column7
		when tl_typeid = 35 then column4
		end as Store
	from  TransactionLog
	where   tl_typeid = 35
	and datediff(dd, tl_date,GetDate()) <= 0
	and column1 like ''FLUID%''
	'
) Two
INNER JOIN 
(	select *
	from openquery
	(	DPR671DC,'
		select distinct rte_id
		from carton_hdr
		where stat_code = 50 
		and rte_id in
		(	select * OPENQUERY
			(THD_PM_TLDB_5085,'
				select distinct case 
        				when tl_typeid = 1 then column7
        				when tl_typeid = 35 then column4
        				end as store 
				from transactionlog 
				where (column1 like ''FLUID%'' or column3 like ''FLUID%'')
				and tl_typeid in (1, 35)'
			) 
		)
		with ur '
	) 
) One on (One.rte_id = Two.store)

quote:

---

Originally posted by yosiasz

you are not creating a table variable. you are creating a temp table which would be created on tempdb. using table variable will help you

declare @TableVar table (store [VARCHAR] (10) NOT NULL)

---

quote:

---

Originally posted by yosiasz

you are not creating a table variable. you are creating a temp table which would be created on tempdb. using table variable will help you

declare @TableVar table (store [VARCHAR] (10) NOT NULL)

---

declare @FLUID_STORES table (store [VARCHAR] (50) NOT NULL)

SET NOCOUNT ON


SET @SQL_t_insert = 

'INSERT INTO @FLUID_STORES (store)
SELECT x.store FROM OPENQUERY('+@Two+',
	'+
		'''select distinct case 
        	when tl_typeid = 1 then column7
        	when tl_typeid = 35 then column4
        	end as store 
		from transactionlog 
		where (column1 like ''''FLUID%'''' or column3 like ''''FLUID%'''')
		and tl_typeid in (1, 35)
		'''
	+') AS x
'

PRINT(@SQL_t_insert )
EXEC(@SQL_t_insert)

INSERT INTO @FLUID_STORES (store)
SELECT x.store FROM OPENQUERY(THD_PM_TLDB_5085,
	'select distinct case 
        	when tl_typeid = 1 then column7
        	when tl_typeid = 35 then column4
        	end as store 
		from transactionlog 
		where (column1 like ''FLUID%'' or column3 like ''FLUID%'')
		and tl_typeid in (1, 35)
		') AS x

quote:

---

Originally posted by yosiasz

ok shapeshifter you keep changing you query every time you post :)

---

SET @SQL_t_insert = 

'declare @FLUID_STORES table (store [VARCHAR] (50) NOT NULL)
INSERT INTO @FLUID_STORES (store)
SELECT x.store FROM OPENQUERY('+@Two+',
	'+
		'''select distinct case 
        	when tl_typeid = 1 then column7
        	when tl_typeid = 35 then column4
        	end as store 
		from transactionlog 
		where (column1 like ''''FLUID%'''' or column3 like ''''FLUID%'''')
		and tl_typeid in (1, 35)
		'''
	+') AS x
'

PRINT(@SQL_t_insert )
EXEC(@SQL_t_insert)


SET @SQL_t_drop = 'DROP TABLE @FLUID_STORES'
EXEC (@SQL_t_drop)

declare @FLUID_STORES table (store [VARCHAR] (50) NOT NULL)
INSERT INTO @FLUID_STORES (store)
SELECT x.store FROM OPENQUERY(THD_PM_TLDB_5085,
	'select distinct case 
        	when tl_typeid = 1 then column7
        	when tl_typeid = 35 then column4
        	end as store 
		from transactionlog 
		where (column1 like ''FLUID%'' or column3 like ''FLUID%'')
		and tl_typeid in (1, 35)
		') AS x