SQL Server Performance Forum – Threads Archive

sp_reassign_dtspackageowner

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

DECLARE @SQL VARCHAR(1000) SELECT @SQL = ‘EXEC sp_reassign_dtspackageowner ”’ + name + ”’, ”’ + CAST(id AS VARCHAR(10)) + ”’, ”’ + new_owner + ”” FROM sysdtspackages WHERE name = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />ackage name>' EXEC (@SQL) 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.

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

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.

]]>