Stored procedure error with linked server
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 ('MyPro开发者_StackOverflow中文版cedure (?,?)', '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 ';'"
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]
精彩评论