compare 5.2 problems

chuprinchuprin Posts: 13
edited July 20, 2006 1:44PM in SQL Compare Previous Versions
1. NO dependencies of the assemblies when their droping.
necessary to take into account dependencies between assembly!

2. interesting mistake was found in part violin creation function:
in old database is absent assembly_function2, which was created in new base. in new base assembly_function1 removed and assembly_function2 created.

in script:
drop fuction assembly_function1
create function assembly_fuction1

(function must be with new name, but it's created with old name)
· Share on Twitter

Comments

  • david connelldavid connell Posts: 172 Silver 1
    Hi there,
    can you give some more information please. I donot think that I understand correctly.
    What I did to try to recreate your problem was
    Set up function1 on database1 & database2
    Modified the base CLR on databases2
    then renamed function1 on database2 to function2
    then tried SQL Compare...
    All seemed fine!
    Please explain further
    Thanks
    David
    · Share on Twitter
  • chuprinchuprin Posts: 13
    1. in database1 drop function1 and create function2
    2. compare: in database2 function1 must be droped and function2 must be created, but in script contains

    drop function1
    create fuction1

    function1 and function2 - assembly function (table-valued)
    part of script:



    PRINT N'Creating [dbo].[wd_getmoskitlist_order]'
    GO

    CREATE FUNCTION [dbo].[wd_moskitlist_order](@idorder int)
    RETURNS @userparametrs TABLE


    in creating "get" why-that is absent, but in print "wd_GETmoskitlist_order"
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    Hi there,
    I could not reproduce your problem, plus I was confused if this problem was regarding a CLR function or SQL style functions..
    SQL Function
    If you rename your functions eg using sp_Rename then you get the following problem. (Which looks like your problem) 
    GO
PRINT N'Creating [dbo].[wd_getmostkitlist_order1]'
GO
CREATE FUNCTION wd_getmostkitlist_order 
(	
	@p1 int
	
)
RETURNS TABLE 
AS
RETURN 
(
	
	SELECT id=0
)

    CLR Function
    However if I use CLR assemblies and rename (or drop and recreate) then everything works out correctly eg 
    PRINT N'Creating [dbo].[dbo.Freddy]'
GO
CREATE FUNCTION [dbo].[dbo.Freddy] (@input [int])
RETURNS TABLE (
[Divisor] [int] NULL)
WITH EXECUTE AS CALLER
EXTERNAL NAME [SqlServerProject1].[UserDefinedFunctions].[Divisor]
GO

    The reason for different results is because the SQL style function definition is stored in sys.syscomments, which is not updated via the sp_rename.
    Deleting and recreating the items should mean that all is fine.
    Please explain more.
    Regards
    David
    · Share on Twitter
  • chuprinchuprin Posts: 13
    I consider that me necessary to send you two my databases.
    data in base not will - an archive will be small.

    where and to whom send?
    this will vastly accelerate searching for of the mistake.
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    Hi there,
    You could send it to me....
    david.connell@red-gate.com
    Regards
    David
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    Hi there,
    thanks for your databases. I have sent a full explanation with snippits from your database to your private message box.
    Regards
    David
    · Share on Twitter
  • chuprinchuprin Posts: 13
    thank, david!

    the problem with names function is locked.

    but that with relationship between assembly?
    · Share on Twitter
  • chuprinchuprin Posts: 13
    recreate assemblies with provision for dependencies...
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    Hi Chuprin,
    When I only selected your the Function wd_getmoskitlist_order, SQL Compare works out that the following objects are dependant
      customer diraction wd_getbytecount wd_getcolorin wd_getcolorout wd_getct wd_getfs wd_getglasslist wd_getmodelparam wd_getmodelsqr wd_getmoskitlist dbo.wd_getmoskitlist dbo.wd_getmoskitlist dbo.wd_getmoskitlist wd_getprofiles wd_getps wd_getquglass wd_getqumoskit wd_getqustv wd_getramasqr AtWDmodel
    As you can see the only assembly that is being updated is AtWDmodel
    The is dragged in because this function uses wd_getmoskitlist, which is in turn use AtWDmodel assembly. Ie as you say there is no relationship between these assemblies. I presume that you are finding the other assemblies being included because you are including something that drags them in ?
    If you want a more visual representation of the relationships between things try out SQL Depenedency Viewer. I find it invaluable.
    Hope that helps
    David
    · Share on Twitter
  • chuprinchuprin Posts: 13
    :-)
    problem1 - dependencies between all assembly of the base!

    After compare going to the script: -> line numbers 85-89

    dropping assemblies without dependencies!
    correct sequence:

    DROP ASSEMBLY [AtWinManufact]
    GO
    DROP ASSEMBLY [AtWDmodel]
    GO
    DROP ASSEMBLY [Atechnology.winDraw.Model]
    GO
    · Share on Twitter
  • chuprinchuprin Posts: 13
    do once again comparison of these bases,
    with that OPTIONS of the comparison, about which I wrote.

    call attention - in violin invalid sequence removing of the assemblies!
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    Hi Chuprin,
    Could you send me your project file please. I was not able to get the following lines: 
    dropping assemblies without dependencies! 
correct sequence: 

DROP ASSEMBLY [AtWinManufact] 
GO 
DROP ASSEMBLY [AtWDmodel] 
GO 
DROP ASSEMBLY [Atechnology.winDraw.Model] 
GO
    I did have the options that you specified. However as yet I could not reproduce your specific issue....
    Best regards
    David
    · Share on Twitter
  • chuprinchuprin Posts: 13
    assembly Atechnology.winDraw.Model must dropped on last step!
    · Share on Twitter
  • chuprinchuprin Posts: 13
    Project configuration->
    Options->
    More Options...->
    Behavior->

    Do not use transactions in sync... - YES
    Do not use ALTER ASSEBLY to change CLR types - YES
    · Share on Twitter
  • chuprinchuprin Posts: 13
    at 83 line in script:

    PRINT N'Dropping CLR assemblies'
    GO
    DROP ASSEMBLY [Atechnology.winDraw.Model]
    GO
    DROP ASSEMBLY [AtWinManufact]
    GO
    DROP ASSEMBLY [AtWDmodel]
    GO

    go to Management Studio->database->... and see to dependencies
    of AtWinManufact or AtWDmodel.
    they have a dependency from Atechnology.winDraw.Model
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    Hi Chuprin,
    Arrr I think I am beginning to understand. Sorry for being so slow...
    So does AtWDModel & AtWinManufact have a .NET reference to Atechnology.winDraw.Model? and then I presume that you have the necessary using statements in the necessary .cs files?
    Regards
    David
    · Share on Twitter
  • chuprinchuprin Posts: 13
    necessary to dropping assemblies consecutively - according to dependencies between them.

    in compare5.2 assemblies dropping without regard dependencies between them.

    necessary (step-by-step):
    1. drop all functions and procedures, founded on assembly
    2. drop all assemblies with provision for dependencies
    3. create all assemblies with provision for dependencies
    4. create all functions, which are founded on assembly
    · Share on Twitter
  • chuprinchuprin Posts: 13
    "So does AtWDModel & AtWinManufact have a .NET reference to Atechnology.winDraw.Model?"

    YES!
    · Share on Twitter
  • david connelldavid connell Posts: 172 Silver 1
    Hi Chuprin,
    Thanks for the explaining what was going on. Now I understand, we will investigate it and I will get back to you in the near future.
    Regards
    David
    · Share on Twitter
Sign In or Register to comment.