November 24, 2009 at 10:04 am
After some wise words of wisdom ...
Ive got this so far ....
declare @linkedservervarchar(100),
@dbnamevarchar(100),
@destinationvarchar(500),
@sqlstrvarchar(1000)
set@dbname='AdventureWorks'
select @linkedserver = 'QAServer1'
set@destination = @linkedserver+'.'+@dbname+'.'+'sales.'
--print @destination
select *
from @destination+SalesOrderHeader P
join @destination+SalesOrderDetail D
on p.salesorderID = d.salesorderID
I know the select does not work ... is there a way to get it to work without having to resort to dynamic sql for the whole statement ?
set @sqlstr = 'select * from '+@destination+'SalesOrderHeader P join '+@destination+'SalesOrderDetail D on p.salesorderID = d.salesorderID'
exec (sqlstr)
thanks simon
November 24, 2009 at 10:26 am
Dynamic SQL is the only way I know to go about it. Why is that a problem?
I've got several jobs that run off a table that supplies Linked Server and Database names. It works well.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 24, 2009 at 10:39 am
thanks bob and thanks for my new favourite quote 🙂
November 24, 2009 at 12:23 pm
Which quote?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 24, 2009 at 12:46 pm
"Against stupidity the gods themselves contend in vain. -- Friedrich Schiller "
about sums my day up so far...
November 24, 2009 at 12:53 pm
It's a comfort to know that you're not alone.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 24, 2009 at 2:24 pm
as for why I was trying to avoid dymanic sql it was due to the potential for performance hits etc
November 24, 2009 at 3:20 pm
i ran what u had and it looked good unless i'm missing something.
declare @linkedserver varchar(100),
@dbname varchar(100),
@destination varchar(500),
@sqlstr varchar(1000)
set @dbname = 'AdventureWorks'
select @linkedserver = 'QAServer1'
set @destination = @linkedserver+'.'+@dbname+'.'+'sales.'
set @sqlstr = 'select * from '+@destination+'SalesOrderHeader P join '+@destination+'SalesOrderDetail D on p.salesorderID = d.salesorderID'
select @sqlstr
returns
select *
from QAServer1.AdventureWorks.sales.SalesOrderHeader P
join QAServer1.AdventureWorks.sales.SalesOrderDetail D
on p.salesorderID = d.salesorderID
--also just to clarify and forgive me if i missed something but the only reason you'd use dynamic sql is if the list of linked server and databases to select from are not known at runtime. If those are determined at runtime dynamically then yes ud have to use dynamic sql otherwise it would just be
select *
from QAServer1.AdventureWorks.sales.SalesOrderHeader P
join QAServer1.AdventureWorks.sales.SalesOrderDetail D
on p.salesorderID = d.salesorderID
November 24, 2009 at 3:30 pm
Simon, can you use OPENQUERY? It's got an 8k limit on the query string, but the linked server name is specified separately.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply