Linked Server - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Linked Server

Author

Topic

asuni
Yak Posting Veteran

55 Posts

Posted - 2010-03-18 : 07:07:31

Hi All,

I am using SQLServer 2005. I am running oracle queries in sqlserver using linked server.
I am able to execute the queries, but how to execute the query which had bind variables.
For ex: SELECT * FROM ([L1], 'SELECT * FROM PARTY WHERE P_CODE = ''A001''')

I am able to run the above query, I want to give bind variable as @CODE instead of directly giving the party code.

I have to use this query in a small procedure, so i declared that variable in the procedure.

please any help

Thanks

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 07:17:19

please create a proc on the L1 SERVER.from your local server execute it just by passing the param to the linked server and retrive which would be better fastest way..

create a temp tbale and store tghe result set.
SET @sql = 'SELECT * FROM OPENQUERY(L1, ''EXEC dbnmame.dbo.selectproc_sp'''''+ @inputparam+ ''''''' )'
INSERT INTO #temp
EXEC (@sql)
select * from #temp

asuni
Yak Posting Veteran

55 Posts

Posted - 2010-03-18 : 07:55:43

Hi haroon2k9,

thank you for your reply.
No not like that i have to use the query in the procedure it self.
I have to select particular party using that select query in the procedure, any help please.

thanks

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 08:03:38

TRY THIS
create a temp tbale and store tghe result set.

SET @sql = 'SELECT * FROM OPENQUERY(L1, ''SELECT * FROM SERVERNAME. dbnmame.dbo.TBLNAME'''''+ @inputparam+ ''''''' )'
INSERT INTO #temp
EXEC (@sql)
select * from #temp
DROP TABLE #TEMP

asuni
Yak Posting Veteran

55 Posts

Posted - 2010-03-18 : 08:07:55

Thank you very much haroon2k9.
I will try and tell you.

thanks

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 08:12:18

pLEASE TRY THIS.LET US KNOW IF IT HELPS

DECLARE @SQL varchar(max)
DECLARE @IPPARAM VARCHAR(50)
SET @IPPARAM='A001'

SELECT @SQL = 'SELECT * FROM OPENQUERY(L1,''SELECT * FROM DBNAME.dbo.TBLNAME WHERE P_CODE= ''''' + @IPPARAM+ ''''''')'
EXEC (SQL)

asuni
Yak Posting Veteran

55 Posts

Posted - 2010-03-18 : 08:18:55

Thank you very very much haroon2k9. It is working very nicely, exactly this i want. You are so genius.
Once again thank you.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 08:19:57

quote:
Originally posted by asuni

Thank you very very much haroon2k9. It is working very nicely, exactly this i want.
Once again thank you.

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources