Post reply

Output of system stored procedure to temp table

  • May 18, 2010 at 5:50 am

    #224531

    Hi,

    Am trying to load the output of a stored procedure into a table. But i am encountering strange error. I dont know why this error is coming.

    But the data is getting into the temp table. Would like to know the reason for the below error.

    Error

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

    BACKUP LOG is terminating abnormally.

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

    Cannot perform a backup or restore operation within a transaction.

    Below are the two way's i used to populate a temp table but in both cases the same error.

    Note: My database is in FULL recovery model.

    Method-1

    -----------

    SELECT *

    -- INTO #MyHead

    FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=db1',

    'Set FmtOnly OFF; EXEC sp_MSdependencies N''dbo.tname'', null, 1315327')

    Method-2

    --------

    create table #tmp2 (

    otypevarchar(10),

    oObjNamevarchar(100),

    oOwnervarchar(30),

    oSequencevarchar(10)

    )

    insert into #tmp1 EXEC sp_MSdependencies N'dbo.tname', null, 1315327

    Any suggestions would be greatly appreciated.

    Thanks in Advance.

  • May 18, 2010 at 6:21 am

    #1168746

    not sure about the error; it's so wierd that it refers to a backup.

    whenever i've used sp_msdependencies , i've always used the parameter @intrans = 1 ,

    and i'm thinking you need to do the same;

    --our list of objects in dependancy order

    INSERT #MyObjectHierarchy (TYPE,OBJNAME,OBJOWNER,SEQ)

    EXEC sp_msdependencies @intrans = 1

    could you try adding that param and see if it works for you?

    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!

  • May 19, 2010 at 12:42 am

    #1169245

    Hi, it worked fine! But what could be reason?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply