Functions and Procedures in Hierarchy Order?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 28, 2004 at 2:25 pm

  #61508

  I've created a program which compares a database against an XML Document to check for missing objects in the database.  A byproduct of the analysis is that I can create a script to replace objects that are missing; objects must be created in the proper order due to dependancies.

  Thes tored procedure I created for a list of func/procs in hierarchy order gives  both results and errors in query analyzer on any of three servers I test on:

  Server: Msg 3021, Level 16, State 1, Line 1

  Cannot perform a backup or restore operation within a transaction.

  Server: Msg 3013, Level 16, State 1, Line 1

  BACKUP LOG is terminating abnormally.

  The procedure is not doing any backup or restore, simply calling the msdependencies and trimming the results.

  The proc is pretty simple, and basically uses a call to sp_msdependancies:

  CREATE PROCEDURE pr_GetProcAndFunctionHierarchy

  AS

  BEGIN

   SET NOCOUNT ON

   CREATE TABLE #HIERARCHY (TYPE int, ONAME varchar(517), OWNER varchar(517), SEQ int)

    INSERT #HIERARCHY

     EXEC sp_msdependencies

    DELETE FROM #HIERARCHY WHERE ONAME NOT IN

     (SELECT NAME FROM sysobjects WHERE sysobjects.xtype in ('P','FN','IF','TF'))

    SELECT * FROM #HIERARCHY

   -- Clean up

   DROP TABLE #HIERARCHY

  END

   

  any idea why the error is being raised?

   

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• noeld

  SSC Guru

  Points: 96590

  More actions

  December 28, 2004 at 3:42 pm

  #535199

  This is what happens when you try to execute an undocummented sp without reading (digging ) in its code (if at all possible) to know how to use it.

  this sp is expecting a parameter to tell if is in the middle of a transaction and if it is not then it will try to backup the tempdb log in order to avoid as much as possible log fill up on tempdb.(Supposibly is very intensive)

  To skip that, you would simply execute the sp passing @intrans a not null value 

  e.g

   EXEC sp_msdependencies @intrans = 1

  Or make sure you are not in the middle of a transaction when you call the stored procedure

  HTH

   

  
  * Noel