Database Change Management
Database Change Management
Home | Profile | Register | Active Topics | Active Polls | Members | Private Messages | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Support
 DB Ghost Support Forum
 Object dependencies		  New Topic  New Poll New Poll
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

prologit


5 Posts
Posted - 04/17/2009 :  11:46:51  Show Profile Send prologit a Private Message  Reply with Quote
Hi!

I'm evaluating your software. It's realy nice. :)
But I think there is a problem with creating a delta script for dependent objects.

Scenario:
SQL Server 2005 SP3.
I've a trigger that depends on a view. When I alter or drop/create this view then the object reference from the trigger to this view is no longer valid and the trigger must be recreated. The DB Ghost Change Manager is aware of this situation (he shows it in the ExamDiff) but into the delta script he writes only the change statements for the view.

After all I would prefer to just update the object references and not cascade a recreation of all dependent objects. I've tried the new in SP2 procedure sp_refreshsqlmodule but it doesn't work for trigger.

DB Ghost Change Manager 5.0.0.1045
Edited by - prologit on 04/20/2009 04:36:46

Mark Baekdal



86 Posts
Posted - 04/24/2009 :  01:56:43  Show Profile  Visit Mark Baekdal's Homepage Send Mark Baekdal a Private Message  Reply with Quote
Recently we've done work to do with references especially around views and user defined functions, so I need to check this out a bit more. The SQL documentation doesn't refer to using sp_refreshsqlmodule with triggers so I don't think we can do this. That said I need to look into this a little further to see how the situation can be handled in the most elegant way.

regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
Take control of your SQL Server code.
Go to Top of Page

prologit



5 Posts
Posted - 04/24/2009 :  10:08:48  Show Profile Send prologit a Private Message  Reply with Quote
For views, procedures and functions sys.sp_refreshsqlmodule works well, but you have to find out the right sequence of dependent objects to execute it. Because sys.sp_refreshsqlmodule not only repairs but also destroys object dependencies.

For triggers I use this workaround (maybe in an own stored procedure sp_refreshsqltrigger):

declare @def as nvarchar(max)
select @def = m.definition from sys.objects o join sys.sql_modules m on o.object_id = m.object_id and name = 'TRIGGER_NAME'
set @def = REPLACE ( @def , 'CREATE TRIGGER' , 'ALTER TRIGGER' )
EXECUTE sp_executesql @def
Go to Top of Page

Mark Baekdal



86 Posts
Posted - 04/27/2009 :  00:43:55  Show Profile  Visit Mark Baekdal's Homepage Send Mark Baekdal a Private Message  Reply with Quote
Hi there,

the work on references is now in the live version - 5.0.1050. sp_refreshsqlmodule for views and user defined functions is run in the correct order of dependence so references are created correctly. At this point we haven't done anything about triggers - that is now in the list of things to do, but it has to be a low priority as it doesn't stop the triggers from working correctly. Views and User defined functions required this work to ensure a SQL Delta creation will always work which is why they became a priority.

regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
Take control of your SQL Server code.
Edited by - Mark Baekdal on 04/27/2009 00:44:44
Go to Top of Page

prologit



5 Posts
Posted - 04/27/2009 :  05:06:20  Show Profile Send prologit a Private Message  Reply with Quote
Hi!

The references are now created correctly for views.
But there is still a little bug, when a user defined function uses an other user defined function (f.e. a table function F1 uses a scalar function F2). The references between the functions are not found in the source database, but on the other hand found in the destination database.

cheers
Go to Top of Page

Mark Baekdal



86 Posts
Posted - 04/28/2009 :  01:14:22  Show Profile  Visit Mark Baekdal's Homepage Send Mark Baekdal a Private Message  Reply with Quote
Is the source database created using DB Ghost?

regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
Take control of your SQL Server code.
Go to Top of Page

prologit



5 Posts
Posted - 04/28/2009 :  02:48:14  Show Profile Send prologit a Private Message  Reply with Quote
Yes, the source database is scripted and created using DB Ghost.
I have created an empty test database with two functions:

CREATE FUNCTION F2 () RETURNS int
AS
BEGIN
  RETURN 1
END

CREATE FUNCTION F1 ( @p1 int )
RETURNS @Result TABLE
(
  col1 int
)
AS
BEGIN
  insert into @Result select [dbo].[F2] ()
  RETURN 
END


But good question. I've tested this problem again and the problem is the build task of DB Ghost. After build there are no references between F1 and F2, because they are created in alphabetical order.
Go to Top of Page

Mark Baekdal



86 Posts
Posted - 04/28/2009 :  22:31:42  Show Profile  Visit Mark Baekdal's Homepage Send Mark Baekdal a Private Message  Reply with Quote
The problem comes down to the SQL engine allowing the user defined function to be created in the first place. There isn't anything returned that indicates there is a reference problem as in a sys depends warning for stored procedures. So there's nothing that the builder can trap and order the functions correctly. There's not a lot I can do about that. I would hope that MS would modify this behaviour in a future version/service pack.

regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
Take control of your SQL Server code.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  New Poll New Poll
 Reply to Topic
 Printer Friendly
Jump To:
Database Change Management © Copyright 2005 Innovartis Ltd. Go To Top Of Page
Snitz Forums 2000
RSS Feed 1 RSS Feed 2
Powered by ForumCo 2000-2008
TOS - AUP - URA