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)  Linked Servers,Stored Proc & Parameter

Author  Topic  elomon Starting Member 37 Posts Posted - 2001-10-18 : 10:51:36 I need to retreive data from a remote server and populate a local SQL table on demand (project requirement). I've set up a linked server and can pass queries back and forth but I would like to create a stored procedure to retreive data with a passed parameter. Here's what I've got so far: (techid is text) CREATE PROCEDURE spu_getData @techid varchar(15) AS insert into local_data select * from openquery(LINKED_SERVER,'select * FROM tablename where user_id=''@techid''') GO This doesn't return any rows since the linked server queries by the string '@techid' instead of the value. I've also tried: CREATE PROCEDURE spu_getData @techid varchar(15) AS insert into local_data select * from openquery(LINKED_SERVER,'select * FROM tablename where user_id=@techid') GO which returns: An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'. [OLE/DB provider returned message: ORA-00936: missing expression ] I guess because the linked server doesn't recognize the paramater will be passed. I asked this question on 4guys but no joy. Before you answer, I can do it without a stored procedure but would prefer not to, long story short is replication.