How to pass parameters for OPENDATASOURCE

I can connect to a linked server with this:

SELECT testNo, soruTuruId, soruNo, cevap , degerlendirenTcNo, degerlendirilenTcNo FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=192.168.150.42;User ID=readerUser;Password=1').akreditasyon.dbo.tblPerfCevap

But I have to pass the password as parameter. and I try like this:

SET @connectionString = 'Data Source=192.168.150.42;User ID=readerUser;Password='+@pw

SELECT testNo, soruTuruId, soruNo, cevap , degerlendirenTcNo, degerlendirilenTcNo FROM OPENDATASOURCE('SQLOLEDB', @connectionString ).akreditasyon.dbo.tblPerfCevap

开发者_StackOverflow社区

and

SELECT testNo, soruTuruId, soruNo, cevap , degerlendirenTcNo, degerlendirilenTcNo FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=192.168.150.42;User ID=readerUser;Password='+@pw ).akreditasyon.dbo.tblPerfCevap

but didnt work:S

does anyone have an idea?

---

use exec () function to run the query. Wrap your query into a valid string.

---

Here's my working implementation of Ojulari's answer. I wanted the same stored proc to run from our Prod, QA, etc.. environment, each one needs to access a different BizTalk Server's database.

declare @DataSource varchar(100)
set @DataSource = 
    case 
        when dbo.GetEnvironment() = 'PROD' then 'Data Source=ProdBizTalkServer;UID=test;PWD=test'
        when dbo.GetEnvironment() = 'QA'   then 'Data Source=QABizTaklServer;UID=test;PWD=test'
        ELSE null
    end       
declare @myExec varchar(max) 
set @myExec =   'select 
                    nvcMessageType,
                    COUNT(*) 
                    from OpenDataSource(''SQLNCLI10'',''' + @DataSource + ''').BizTalkMsgBoxDb.dbo.Spool
                    where nvcMessageType like ''%#FlightMessageReceivedEvent''
                    group BY nvcMessageType
                    order by nvcMessageType 
            '  
print '@myExec=' + IsNull(@myExec,'null') 

EXEC(@myExec)