0

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

Josh
  • 1,001
  • 9
  • 18
  • 2
    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 Jun 30 '10 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 Jun 30 '10 at 13:24

3 Answers3

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.

Yellowfog
  • 2,323
  • 2
  • 20
  • 36
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?

alex
  • 943
  • 5
  • 7
  • 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 Jun 30 '10 at 13:21
  • 1
    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' ) – Josh Jun 30 '10 at 14:01
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
Madhivanan
  • 13,470
  • 1
  • 24
  • 29