SQL Server Performance

sp_reassign_dtspackageowner

Discussion in 'SQL Server DTS-Related Questions' started by raagi2000, Jun 16, 2006.

  1. raagi2000 New Member

    Hey guyz

    I am trying to frame the select query from sysdtspackages to generate the statement like
    select sp_reassign_dtspackageowner name , id , new_owner from sysdtspackages.

    the output should be in the format

    sp_reassign_dtspackageowner 'name' , 'id' , 'new_owner'

    somehelp help me to frame the sql to do this. thanks
  2. Adriaan New Member

    DECLARE @SQL VARCHAR(1000)<br /><br />SELECT @SQL = 'EXEC sp_reassign_dtspackageowner ''' + name + ''', ''' + CAST(id AS VARCHAR(10)) + ''', ''' + new_owner + ''''<br />FROM sysdtspackages<br />WHERE name = '&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />ackage name&gt;'<br /><br />EXEC (@SQL)<br /><br />Insert the name of the package in the WHERE clause - if you don't filter, the statement will take the last row returned from sysdtspackages, and there is no way of predicting which row that will be, without an ORDER BY statement.
  3. raagi2000 New Member

    Sorry i think i did not frame my question correctly
    i need to change ownership of packages (around 100+) . i do not want to do
    sp_reassign_dtspackageowner individually for each package.

    i need to get the sql that can generate below statements for all packages from sysdtspackages , so i can run all in one click . i tried to frame it but i am getting lot of syntax errors
    exec sp_reassign_dtspackageowner 'name' , 'id' , 'new_owner' -- dts1
    exec sp_reassign_dtspackageowner 'name' , 'id' , 'new_owner' -- dts2
    exec sp_reassign_dtspackageowner 'name' , 'id' , 'new_owner' -- dts3

    ________________________

    _______________________

    exec sp_reassign_dtspackageowner 'name' , 'id' , 'new_owner' -- dts100
  4. nigelrivett New Member

    All you need to do to change the package owner is set owner_sid and owner in msdb..sysdtspackages.

    If you get all the package IDs into a table then you can join to it and do the update in a single statement.

Share This Page