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! :-\

That's an odd error to get, considering @Shipper_Nbr doesn't show up in the code you presented. Is there more you can share with us? — Raven Dreamer
– Raven Dreamer, Commented Jun 30, 2010 at 13:13
I was trying to keep it generic. Here is the actual statement: UPDATE WARSQLVS01.ISS3_AND_DHAM_PROD.dbo.ISS_AND_data_Shipments SET Arrive_DT = @Actual_DateTime WHERE Shipper_Nbr = @Shipper_Nbr and Container_Name = @Container_Name — Josh
– Josh, Commented Jun 30, 2010 at 13:24

Yellowfog · Accepted Answer · 2010-06-30 13:51:28Z

1

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.

answered Jun 30, 2010 at 13:51

Yellowfog

2,3332 gold badges20 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alex · Accepted Answer · 2010-06-30 13:13:42Z

0

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?

answered Jun 30, 2010 at 13:13

alex

9635 silver badges8 bronze badges

2 Comments

Josh Over a year ago

Yeah - @Shipper_Nbr is just a parameter to the stored procedure. It's used in other places in the stored procedure, but only indicates an error on this statement.

Josh Over a year ago

No, but i'll post the header to prove that the variable is declared: alter PROCEDURE [dbo].[CSN_UpdatePoints_InTransit] ( @Shipper_Nbr varchar(6), @Container_Name varchar(5), @Point decimal(3,0), @Actual_DateTime datetime, @CustomerID varchar(30) = 'SHAP' )

Madhivanan · Accepted Answer · 2010-06-30 14:10:43Z

0

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

answered Jun 30, 2010 at 14:10

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Collectives™ on Stack Overflow

Update linked SQL server with parameters

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related