Solved

Use Tables from another database in SQL SERVER

Posted on 2008-10-04
13
1,040 Views
Last Modified: 2008-10-07
Can I do thi sin a trigger?
there is no error but My Data wouldnt update when the ID is not in current DB (SRS2) , but if the ID is in current DB (SRS2)it will update ?
Any adea?
IF ID Exists in SRS2 update will perform ,
If ID not EXISTS in SRS2 we should search in SSRS1 that have the ID and fid the name but update doesnt perform in this situation.
IF UPDATE(RoutingID)

BEGIN

	DECLARE @RoutingName nvarchar(50)

	DECLARE @ORG char(10)

	DECLARE @DEST char(10)

	DECLARE @RoutingID Decimal 
 

	SET  @RoutingID = (SELECT RoutingID FROM INSERTED)
 

	IF EXISTS ((SELECT TOP (1) RoutingName FROM [SRS2].[dbo].vw_AVI WHERE @RoutingID = @RoutingID)  )

		BEGIN

			SET  @RoutingName = (SELECT TOP (1) RoutingName FROM [SRS2].[dbo].vw_AVI WHERE RoutingID = @RoutingID) 

			SET  @ORG = (SELECT TOP (1) ORG FROM [SRS2].[dbo].vw_AVI WHERE RoutingID = @RoutingID) 

			SET  @DEST = (SELECT TOP (1) DEST FROM [SRS2].[dbo].vw_AVI WHERE RoutingID = @RoutingID) 
 

			update [SRS2].[dbo].vw_AVI set RoutingName = @RoutingName , ORG = @ORG , DEST = @DEST WHERE ClassID = @ClassID

			IF @@ROWCOUNT != 0

				RETURN

			ELSE

				RAISERROR(N'could not update', 16, 1);

		END

	ELSE 

		BEGIN

			SET @RoutingName = (SELECT TOP (1) RoutingName FROM [SRS1].[dbo].Routing WHERE RoutingID = @RoutingID)

			SET @ORG =  SUBSTRING(@RoutingName , 0, CHARINDEX('-', @RoutingName));

			SET @DEST =  SUBSTRING(@RoutingName ,CHARINDEX('-', @RoutingName) ,LEN(@RoutingName) );

			update vw_AVI set RoutingName = @RoutingName , ORG = @ORG , DEST = @DEST WHERE ClassID = @ClassID

			IF @@ROWCOUNT != 0

				RETURN

			ELSE

				RAISERROR(N'could not update', 16, 1);

		END

END

Open in new window

0
Comment
Question by:Lord_Death
  • 7
  • 5
13 Comments
 
LVL 4

Expert Comment

by:jindalankush
ID: 22639935
check that , ur linked server are update able or read only
0
 
LVL 2

Author Comment

by:Lord_Death
ID: 22639974
DB SRS2 : vw_AVI (RoutingID , RoutingName,...)
DB SRS1 : Routng(RoutingID , RoutingName, RoutingUserID,RoutingDescription)

if RoutingID is in SRS2.vw_AVI then record's RoutingName will update by trigger
but if RoutingID doesnt exists in if ID is in SRS2.vw_AVI  trigger would serach in SRRS1.Routing Table to find the RoutingName , in this situation update wont perform
:(
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22644832
Which table is the trigger on ? seems you are qualifying the table to update using a 4 part identifier in the first instance, but not the second... Are you looking at the correct tables ? also vw_ is traditionally a prefix often used for views - are you sure the local vw_AVI is updateable ?

Would be inclined to debug the routine first. Make it a script in a query window and set up the variables for @class_id and @routingid then run it intereactively with a few well chosen print statements in there...

Need to be careful when describing your problem. Take time to read it and double check. e.g.
>> wouldnt update when the ID is not in current DB (SRS2) , but if the ID is in current DB (SRS2)it will update  <<

So which one is current ?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22644837
And taking a lesson from my own comment, what I meant was, are you running this from SRS2 or SRS1 ? What does "current" mean ?
0
 
LVL 2

Author Comment

by:Lord_Death
ID: 22646275
if RoutingID is in SRS2.vw_AVI then record's RoutingName will update by trigger (Line 12) but if RoutingID doesnt exists in SRS2.vw_AVI  trigger would serach in SRS1.Routing  table to find the RoutingName , in this situation update wont perform  (Line 24)
& yes the vw_AVI is actually a Table not a view only the name is not properly set.

and also yes I have tested the statements in QueryAnalizer  and its Updating but as a trigger it doesnt do anything.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22648710
OK, got the first bit, still do not know where that trigger is and if it is on the table vw_AVI on SRS1 (or SRS2). Mighthave said it, but I still have a block.

Thanks for clarifying the names, was a stab in the dark...

Do you have RECURSION enabled (ie sp_configure option 'server trigger recursion' 1 ) ? have your seen it complain about recursion? Have you RECURSIVE_TRIGGERS option set for that database ?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Author Comment

by:Lord_Death
ID: 22657044
BTW thx for reply but
SRS2.vw_AVI  means that vw_AVI table is in SRS2 database and the trigger is in SRS2 database on vw_AVI table,
and no there was no complain about recursion.

as I said if data exist in table that trigger is fired on update will perform but if data not exist in table that trigger is fired so it should search in another database that has a table with respective data


e.g  DB1 have T1 that have all the data & DB2 have T2 that have an update trigger on T2 so if data is not in DB2 we should look for it in DB1 and in respective table T1

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22658478
Hi Lord_Death, yes, understand the multipart table identifier.... in fact, what you have said is technically wrong SRS2.vw_AVI lives in database SRS2 if using MySQL, but in MS SQL it means it lives in schema SRS2 - the MS SQL identifier is a four part beast in the form <server>.<database>.<schema>.<tablename> and the code correctly has the intervening schema name (which in this case is optional) between database and table - ie DBO... But I do undertsand what you were meaning...

The reason for my question was the table in the "else" part of the "if" statement does not have any identifier other than the table itself, and obviosuly will ne updating that table in the "local" database (ie no db identifier). Now it does become important to understand which table that trigger belongs to...

So, you are trying to update the table using a trigger on that table being fired with Inserts (and changes  ?) but only if RoutingID is part of the changes, and it exists (or not) in the table that we are currently updating... Which coincidentally will fire a change trigger...

Has all the right ingredience for recursion... so thought it might be a worthwhile ask... but possibly a non-issue if you have not seen any evidence (is trigger for changes as well ?)

Now, there are a couple of "interesting" facts....

1) SET  @RoutingID = (SELECT RoutingID FROM INSERTED)    -- there can be multiple rows in inserted so this will fail if there is...
2) Here is the real rub, and most likely your problem :
   a) Triggers fire after the event - ie data is in the database.
   b) if updating routingid - and only checking what to do if routingid is part of the update, then it is already committed
   c) it means that routingid will always / already exist in the check ; IF EXISTS ((SELECT TOP (1) RoutingName FROM [SRS2].[dbo].vw_AVI WHERE @RoutingID = @RoutingID)  )
   d) now, if routingname is only being populated by this trigger (and ORG and DEST) then do not do the check in step 2.c. above. Always maintain from SRS1
   e) if 2.d. not desirable, then need to check if routingname has been pre-populated (not routingid), but the "ELSE" part suggests otherwise.

So, bottom line ?  2.d. given the evidence so far...


0
 
LVL 2

Author Comment

by:Lord_Death
ID: 22659286
BTW thx mark_wills for your time & sharing your knowledge

your reply helped too much ,
but I have a problem of performance the SRS2 has few tables with huge queries (SELECT) on it ,
and records in this table have some simmilar data , like Routing usually is repeated in multiple records so if i use a select on this(SRS2) DB and not on main (SRS1) DB i have a select with no INNER JOINs so I will have a very much better performance.
and cause of using other DataBase is for further use in other phases of our project (I know I can use a table in same DB)
SRS1 is the main DB and contain all the data. and those Quries (SELECTS) I mentioned before execute on SRS1 there would be Lots of Joins for example at least on 8 Tables. & i need the highest speed so here comes the second DB that helpes and in this phase solve those joins .

for reaching  to this higher performance I put that " IF EXISTS ((SELECT TOP (1) ...." on line 10.
now I Underestand why it worked in QueryAnalyzer & not in Trigger (2.b)

and is there any other way to reach that performance and bypass those INNER JOINs ? (check SRS2 DATABASE if id is not found in it then Search in SRS1 DATABASE and get the data)
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 50 total points
ID: 22660390
Interesting - if it is all on the one server, then the server has a big workload almost regardless of connecting to SRS1... Anyway...

INNER JOINs do not neccessarily mean slow performance - it is a balancing act of all that work duplicating data, bigger row size, indexes and how much of the index can fit on a page, and therefore take advantage of caching. As it stands it is not very relational, and continuing down that path might be more detrimental than you think. But will leave all that up to you for now, but hopefully the suggestion below will indicate how performance is easily affected.

OK, if routingname (and org and dest) can only be maintained / originated via the link, then the first time in it will drag across from SRS1, and subsequently, from SRS2. To do that simply change that IF EXISTS (and fixing the eroneous where @RoutingID = @RoutingID - may as well say where 1=1)  to :

IF EXISTS ((SELECT TOP (1) RoutingName FROM [SRS2].[dbo].vw_AVI WHERE RoutingID = @RoutingID and RoutingName not NULL and classid <> @classid )  )

But to improve it a little more (because it is always going to do a few selects in it's current guise anyway) then I would be inclined to change it to :

( and surely must be worth more than 50 points for crying out loud )



SELECT TOP (1) @routingname = RoutingName, @org=org, @dest=dest FROM [SRS2].[dbo].vw_AVI WHERE RoutingID = @RoutingID and RoutingName not NULL and classid <> @classid
 

IF len(rtrim(isnull(@RoutingName ,''))) < 1 

begin

        SELECT TOP (1) @routingname = RoutingName, @org=org, @dest=dest FROM [SRS1].[dbo].vw_AVI WHERE RoutingID = @RoutingID and RoutingName not NULL

-- or if org and dest are in fact different then have to revert to:

--        SET @RoutingName = (SELECT TOP (1) RoutingName FROM [SRS1].[dbo].Routing WHERE RoutingID = @RoutingID)

--        SET @ORG =  SUBSTRING(@RoutingName , 0, CHARINDEX('-', @RoutingName))

--        SET @DEST =  SUBSTRING(@RoutingName ,CHARINDEX('-', @RoutingName) ,LEN(@RoutingName) )

end
 

update [SRS2].[dbo].vw_AVI set RoutingName = @RoutingName , ORG = @ORG , DEST = @DEST WHERE ClassID = @ClassID
 

IF @@ROWCOUNT != 0

        RETURN

ELSE

        RAISERROR(N'could not update', 16, 1);
 
 

--Now, you still have the problem of multiple rows in inserted. So might want a cursor on that, and will leave that to you.

-- and given the reduction from 4 selects down to 1 select if it exists, then arguable do the SRS1 everytime...

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22664217
You are a hard person to please - not even a thank you (the more traditional acknowledgement in lieu of points) - suppose it is all in the name...
0
 
LVL 2

Author Comment

by:Lord_Death
ID: 22838927
Dear Mark Wills about your last post I said before:

"BTW thx mark_wills for your time & sharing your knowledge"
"your reply helped too much ,"

but again Thanks a lot ...


-Share Your knowledge , gain more.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22839542
Thanks Lord_Death,  I do see that now, and owe you an apology, did not really see the thx. So, I now need to say thank you for taking the time to point it out and reiterate the "Thanks a lot" - it is very much appreciated.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now