Update linked SQL server with parameters

Question

I have two linked SQL servers and i am trying to issue an update on the other server but am getting an error "Statement(s) could not be prepared.", followed by "Must declare the scalar variable "@Shipper_Nbr""

The syntax i am using is:

update DBSERVER.DBNAME.DBO.TABLE set Field = @Value WHERE ID = @id

What is the correct way to do this?

UPDATE:

I tried using a stored procedure and get the same result. Also, i noticed additional information. The syntax checks out, i only get the error when i attempt to save the stored procedure. Also, i noticed the error also states "OLE DB provider "SQLNCLI" for linked server "WARSQLVS01" returned message "Deferred prepare could not be completed."."

ACK - i figured it out. It was a separate line where i was trying to pass the parameter from within OPENQUERY select statement. Sorry about that! :-\

Answer 1

Are you sure the problem is with the server link, and not something about the rest of your SQL statement? Where is the @Shipper_Nbr value assigned?

Answer 2

Not entirely sure what this problem is, but we have had to work round similar kinds of issues by setting up a stored proc on the remote server and calling that, rather than trying to manipulate the items on the remote server locally.

Answer 3

Se if this works

UPDATE T
SET Arrive_DT = @Actual_DateTime 
WHERE Shipper_Nbr = @Shipper_Nbr and Container_Name = @Container_Name 
FROM WARSQLVS01.ISS3_AND_DHAM_PROD.dbo.ISS_AND_data_Shipments  as T