Stored procedure error with linked server

Question

I have set up a stored procedure to update a database on my linked server

CREATE procedure MyProcedure
@myfield varchar(50),
@mycolumn varchar(10)

AS

UPDATE MyDB
SET myfield = @myfield
WHERE mycolumn = @mycolumn
GO

And then executing it like this

exec linkedDB...MyProcedure @myfield = '0', @mycolumn = '12345'

But I keep getting this error

Could not set up parameter for remote server 'linkedDB'

I've done a search on the web for it but it doesn't seem to come up with anything

UPDATE

Here's what i'm trying now

EXEC ('MyProcedure (?,?)', '0', '12345') AT [linkedDB]

Seems to be giving me this error now

Incorrect syntax near ','

I'm using OLE DB with SQL Server 2000

UPDATE

Also tried this

exec linkedDB...MyProcedure '0', '12345' 

And I now get this error

Syntax error or access violation "parse error near ';'"

Answer 1

To allow paramaters i think you have to activate the Dynamic Parameter option in your linked server provider properties: You can find them in Sql Server managament studio, under Server Objects/ Linked Servers/ Providers/ Right click Properties on the desired providers

Dynamic parameter

Indicates that the provider allows '?' parameter marker syntax for parameterized queries. Set this option only if the provider supports the ICommandWithParameters interface and supports a '?' as the parameter marker. Setting this option allows SQL Server to execute parameterized queries against the provider. The ability to execute parameterized queries against the provider can result in better performance for certain queries.

EDIT From Comment:

For OLEDB the parameter marker is ? (question mark) without any name. Youshould try to call the stored procedure with this kind of sintax:

EXEC ('exec MyProcedure ?, ?', @parVal1, @parVal2) AT [LINKED SERVER]