Passing Parmeters to another DB

[Nova980]

Hello,

How can I insert these parameters into another table in another db using a stored proc? Making sure that both id's are not duplicated?

ALTER PROCEDURE [dbo].[pUpdateAn]

@Parameters...

set nocount on
declare @NextId int
declare @RowCnt int

if (@Action = 0 )
begin 
   Select @AnotherId = @AnId
   update Something set Something_Nm = @SomethingNm, This_Nm = @ThisNm, Active_Fl = @ActiveFl, Location_Id = @LocationId,
     Address1_An = @Address1An, Phone_An = @PhoneAn, Mail_An = @MailAn, Type_Cd = @Type,
     Brand_Cd = @BrandCd, Group_Cd = @GroupCd, ship_Cd = @shipCd,
     People_Nr = @PeopleNr, Building_Nr = @BuildingNr, Site_Cd = @SiteCd, Rate_Nr = @RateNr
   where An_Id = @AnId
end

if (@Action = 1 )
begin 
   select @AnotherId = max(An_Id) + 1 from An
   insert into An 
     (An_Id, Something_Nm, Active_Fl, Location_Id, This_Nm, Address1_An, Phone_An, Mail_An, Type_Cd,  
     Brand_Cd,  Group_Cd,  Ship_Cd, People_Nr, Building_Nr, Site_Cd, Rate_Nr )
   values (@NextId,@AnNm, @ActiveFl,@LocationId, @ThisNm, @Address1An, @PhoneAn, @MailAn, @Type, 
      @BrandCd, @GroupCd, @ShipCd, @PeopleNr, @BuildingNr, @SiteCd, @RateNr)

-->Here is where I'd like to place my stored proc <--

exec pUpdateAnotherTable...


end

Thank you.

 

[bborissov]

Where pUpdateAnotherTable is defined?
In current DB or in other one?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Nova980]

It will be defined in the current DB passing @ into another db. Sorry for not clarifying.

 

[Nova980]

sql server 2005

 

[bborissov]

What is the code in that SP?
I see no problems here, just use Full qualified names in it (OK, not full, you can miss the server name )

UPDATE OtherDB.dbo.TableName SET Field = Something

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Look at sp_ExecuteSQL in Help - looks like this is what you want here to pass your parameters.

 

[bborissov]

Why?
sp_executesql is a dynamic SQL, why should use Dynamic SQL when you can use a pure one

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Nova980]

the sp does not exist yet. I want to use pUpdateAnotherTable (not created) to insert the same AnId row to another DB table. Is it more beneficial to create this in the remote db or the local?

 

[markros]

To Boris - because we want to execute another SP from the original SP - this is how I understood the requirement.

 

[bborissov]

It will be defined in the current DB passing @ into another db. Sorry for not clarifying.

And what stops you?
sp_executesql is SP isn't it?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Nova980]

To Boris, using the

OtherDB.dbo.TableName SET Field = Something

How may i create a procedure in my db to update my remote db table? Not sure where to start with this.

I do not wish to use sp_executesql.

 

[bborissov]

Is the other DB resides on the same server or not?
Also I am not sure what you wan to do. Update what? How?
Did you not the DB name where you want to update something?
Crateion of the procedure is easy, but w/o knowing what you want to do nobody can help you.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Nova980]

Yes, both db's reside on the same server. (Looking at my sp above) I want my sp (pUpdateAn) to include a sp that inserts the same record from (pupdateAn) to another table. For example, if someone adds a new row into mytable than update myothertable too with the newly added row.

 if (@Action = 1 )
begin
   select @AnotherId = max(An_Id) + 1 from An
   insert into An
     (An_Id, Something_Nm, Active_Fl, Location_Id, This_Nm, Address1_An, Phone_An, Mail_An, Type_Cd,  
     Brand_Cd,  Group_Cd,  Ship_Cd, People_Nr, Building_Nr, Site_Cd, Rate_Nr )
   values (@NextId,@AnNm, @ActiveFl,@LocationId, @ThisNm, @Address1An, @PhoneAn, @MailAn, @Type,
      @BrandCd, @GroupCd, @ShipCd, @PeopleNr, @BuildingNr, @SiteCd, @RateNr)[code/][smurf]

How can I take the values and insert them not in An but in another table?

 

[bborissov]

The syntax is the same:

INSERT INTO OtherDBName.dbo.An (...) //rest of the command

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.