Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Update a column in one database from another (Dynamic) database

Posted on 2011-02-23
4
673 Views
Last Modified: 2012-05-11
A row gets inserted into 2 DB tables at same time, but one of the coulmn remains null on one DB and gets value on another. No my task is :-  
I have to update a column in one database from another database After Insert,
to get the Inserted row pulled and update the record I am using a After Insert Trigger.

ALTER TRIGGER [Update_Trg]
   ON [dbo].[location_all]
   AFTER INSERT
AS
BEGIN

DECLARE @Databasename NameType
DECLARE @CurrentDB NameType
DECLARE @SQL varchar(max)

SELECT TOP 1
@site_ref = i.site_ref
, @loc = i.loc  
, @RowPointer =i.RowPointer    
FROM INSERTED i

SELECT @Databasename = Databasename
FROM dbo._Site_DatabaseNames
Where Site = @site_ref

SELECT @CurrentDB = db_name()

set @SQL ='UPDATE l_a '+
           'SET l_a.mrb_flag = l.mrb_flag ' +
           'FROM [' + @CurrentDB + '].dbo.location_all l_a ' +
           'INNER JOIN [' + @Databasename + '].dbo.location_all l ON l.RowPointer = l_a.RowPointer ' +
           'INNER JOIN inserted i ON l_a.RowPointer = i.RowPointer  '+
           'WHERE i.site_ref = l.site_ref and i.loc = l.loc'
EXEC(@SQL)

Invalid Object Name inserted.

What is wrong in this script ? Can anybody help me find a solution. Thanks.
0
Comment
Question by:SaiLoveAll
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34963599
inside the dynamic sql, INSERTED is no longer valid, unfortunately.
so, you need to refer to the real table instead, using the ID value you need to fetch from inserted and add such a condition into the @sql explicitely

something
ALTER TRIGGER [Update_Trg]
   ON [dbo].[location_all] 
   AFTER INSERT
AS 
BEGIN

DECLARE @Databasename NameType
DECLARE @CurrentDB NameType
DECLARE @SQL varchar(max)

SELECT TOP 1 
@site_ref = i.site_ref
, @loc = i.loc   
, @RowPointer =i.RowPointer    
FROM INSERTED i 

SELECT @Databasename = Databasename
FROM dbo._Site_DatabaseNames
Where Site = @site_ref

SELECT @CurrentDB = db_name()

set @SQL ='UPDATE l_a   
           SET l_a.mrb_flag = l.mrb_flag 
           FROM [' + @CurrentDB + '].dbo.location_all l_a
           INNER JOIN [' + @Databasename + '].dbo.location_all l ON l.RowPointer = l_a.RowPointer  
             and l.row_pointer = ''' + @RowPointer  + ''' '
EXEC(@SQL)

Open in new window

0
 

Author Comment

by:SaiLoveAll
ID: 34963989
set @SQL ='UPDATE l_a  
           SET l_a.mrb_flag = l.mrb_flag
           FROM [' + @CurrentDB + '].dbo.location_all l_a
           INNER JOIN [' + @Databasename + '].dbo.location_all l ON l.RowPointer = l_a.RowPointer  
             and l.row_pointer = ''' + @RowPointer  + ''' '

When I do so, I get an ERROR Message :-
The data types nvarchar and uniqueidentifier are incompatible in the add operator.

The variable '@RowPointer ' is a uniqueidentifier.  What could have caused the incomplatability ?

In trigger
DECLARE @RowPointer RowPointerType  

Table column
[RowPointer] [dbo].[RowPointerType] NOT NULL CONSTRAINT [DF_location_all_RowPointer]  DEFAULT (newid())
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34964794
change:

  and l.row_pointer = ''' + @RowPointer  + ''' '

into:

  and l.row_pointer = ''' +cast( @RowPointer as varchar(100))  + ''' '
0
 

Author Comment

by:SaiLoveAll
ID: 34965456
Thanks a Lot. It worked.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question