Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 9th, 2004, 06:26 PM
Authorized User
Points: 457, Level: 7
Points: 457, Level: 7 Points: 457, Level: 7 Points: 457, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: Burbank, CA, USA.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Arsi
Default Update two databases at the same time

Hi!

I need a push in the right direction. I am trying to figure out how to update two different sql server databases at the same time. The databases are both on the same server. Not all the fields in both databases are the same. I want to know should I use triggers or should I just set up an update statement for the both databases and just run those right after each other? Any help with this question is greatly appreciated.

Thanks,

*******(*)*******
__________________
*******(*)*******
  #2 (permalink)  
Old November 10th, 2004, 07:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Arsi,

Yes, you could write a trigger on the table on which you run the update statement, so that on update event happening on that table would trigger another update event that updates the values to the other table on another database. It could be something like this.
Code:
CREATE TRIGGER [MyTrig] ON [TABLE1] 
FOR UPDATE
AS
If update(COLUMN1)
    Update OTHERDB..TABLE1 set COLUMN1 = i.COLUMN1
        from DELETED d, OTHERDB..TABLE1 t, INSERTED i
        where d.COLUMN1 = t.COLUMN1
return
Hope this gives you the needed push you in the right direction.;)
Cheers!

_________________________
- Vijay G
Strive for Perfection
  #3 (permalink)  
Old November 10th, 2004, 01:34 PM
Authorized User
Points: 457, Level: 7
Points: 457, Level: 7 Points: 457, Level: 7 Points: 457, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: Burbank, CA, USA.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Arsi
Default

Hi!

Thank you for your help! This is what I came up with, with your help. It does not update the second database though. What am I doing wrong?

ALTER TRIGGER [Update_DB] ON [dbo].[ProjectInfo]
FOR UPDATE
AS
If UPDATE(ProjectName)
    UPDATE ProjectStatus..ProjectInfo2
    SET ProjectName = i.ProjectName
    FROM ProjectInfo d, ProjectStatus..ProjectInfo t, ProjectInfo2 i
    WHERE d.ProjectName = t.ProjectName
RETURN

*******(*)*******
  #4 (permalink)  
Old November 12th, 2004, 11:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Arsi,

You were not using the INSERTED and DELETED tables as given in my suggested code. INSERTED and DELETED tables are the ones that can be used only within TRIGGERS, where INSERTED holds the the ROW with the NEW values that you pass for UPDATE statement, and DELETED holds the record with the OLD VALUES that were there in the orginal table before UPDATE was done.

Hope this explains better. I was under assumption that you would be having knowledge about these two table, that is the reason why I didnot explain on that.
Code:
ALTER  TRIGGER [Update_DB] ON [dbo].[ProjectInfo] 
FOR UPDATE 
AS
If UPDATE(ProjectName)
    UPDATE ProjectStatus..ProjectInfo2
    SET ProjectName = i.ProjectName
    FROM DELETED d, ProjectStatus..ProjectInfo t, INSERTED i
    WHERE d.ProjectName = t.ProjectName
RETURN
This should be how it should look like, if needed make some alterations to this code so as to work as expected.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
  #5 (permalink)  
Old November 12th, 2004, 07:12 PM
Authorized User
Points: 457, Level: 7
Points: 457, Level: 7 Points: 457, Level: 7 Points: 457, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: Burbank, CA, USA.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Arsi
Default

Hi happygv!

Again, thank you for your help.

You're right i did not know about the deleted & inserted tables. I am very new to Sql server. I can only do very basic things. I changed my trigger per your example, but something keeps going wrong. The update is passed to the database via a Sqlcommand and connection in C#. It updates correctly when the trigger is not on the database, but once I put the trigger on the database, the first update does not even execute. I don't understand why it doesn't allow for the code to finish executing. It errors out at dbComm.ExecuteNonQuery(); this only occurs when the trigger is placed on the database. What do you think I should do?

*******(*)*******
  #6 (permalink)  
Old November 15th, 2004, 02:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:the first update does not even execute
Does that mean the other UDPATE execute? But how many UPDATEs are you running? Is that you want to run an UPDATE stement that would inturn trigger the other update from the backend(from within the database) OR is that you are trying to run two update statements still?

I am little confused on that.

Did you try the Update statement from the SQL query analyser? Does that successfully run and update the other table too? If that happens, something should be wrong the way you use that in your frontend app.

Also it is better to post the error what you see when executing this.

Cheers!

_________________________
- Vijay G
Strive for Perfection
  #7 (permalink)  
Old November 15th, 2004, 12:41 PM
Authorized User
Points: 457, Level: 7
Points: 457, Level: 7 Points: 457, Level: 7 Points: 457, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: Burbank, CA, USA.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Arsi
Default

Sorry about making things so confusing. But here is what I'm trying to do: When one sql server database is updated, a trigger has to update and sql server database on the same server or insert that record if it does not exist. At this point I am not getting an error, it just stops and does nothing. I am going to see if I can Catch the error then I'll post it. Thank you so much for your help and patience.

Arsi

*******(*)*******
  #8 (permalink)  
Old November 15th, 2004, 12:53 PM
Authorized User
Points: 457, Level: 7
Points: 457, Level: 7 Points: 457, Level: 7 Points: 457, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: Burbank, CA, USA.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Arsi
Default

WOW!!!

I was able to get it working thanks to your help! I am very excited! Thank you, thank you, thank you.

I changed:

FROM DELETED d, ProjectStatus..ProjectInfo t, INSERTED i

To:
FROM DELETED d, ProjectStatus..ProjectInfo2 t, INSERTED i

now i have to work on inserting the record if it does not exist in the 2nd database. If I run into anymore problems I know where to go.

Thanks again,

Arsi



*******(*)*******
  #9 (permalink)  
Old November 16th, 2004, 03:01 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

So within the trigger check if that record exists in the other table and update if exists. Else you got to insert that.

Hope that should be easy now.
Cheers!

_________________________
- Vijay G
Strive for Perfection
  #10 (permalink)  
Old November 18th, 2004, 06:14 PM
Authorized User
Points: 457, Level: 7
Points: 457, Level: 7 Points: 457, Level: 7 Points: 457, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: Burbank, CA, USA.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Arsi
Default

Ok. I think I messed things up. I'm trying to do what you told me with the insert, but I have made a mess of it. Can you please help me with this too?
ALTER TRIGGER [Update_DB] ON [dbo].[ProjectInfo]
FOR INSERT, UPDATE
AS
If UPDATE(ProjectName)
IF EXISTS (SELECT ProjectStatus..ProjectName
        FROM ProjectStatus
        WHERE ProjectStatus..ProjectName = ProjectInfo.ProjectName)
    UPDATE ProjectStatus..ProjectInfo2
    SET ProjectName = i.ProjectName
    FROM DELETED d, ProjectStatus..ProjectInfo2 t, INSERTED i
    WHERE d.ProjectName = t.ProjectName
ELSE
INSERT ProjectStatus..ProjectInfo2 (ProjectName)
VALUES (ProjectInfo..ProjectName)
RETURN

I would really appreciate it. Thank you.

*******(*)*******




Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find the last update time of a table dsekar_nat SQL Server 2000 4 July 27th, 2006 06:40 PM
why not UPDATE all instead of 10 at a time? gilgalbiblewheel Classic ASP Databases 0 April 21st, 2005 11:46 AM
UpDATE IN VB FROM TWO DATABASES akash VB How-To 5 April 20th, 2005 03:44 AM
Access Databases won't update through code wwwaynes Access ASP 2 October 3rd, 2004 03:33 PM
SQL2000 Date/Time field update Lucian Ion Dreamweaver (all versions) 9 January 2nd, 2004 09:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.