Try the foll script:
USE MSDB
DECLARE
@name sysname, @id uniqueidentifier,
@old_owner sysname, @new_owner sysname
SELECT @old_owner = 'old_owner_login'
SELECT @new_owner = 'new_owner_login'
IF (NOT EXISTS (SELECT * FROM sysdtspackages WHERE [owner] = @old_owner))
BEGIN
DECLARE @errMsg varchar(255)
SET @errMsg = 'Owner ' + @old_owner + ' does not own any packages'
RAISERROR(@errMsg, 16, 1)
RETURN
END
DECLARE cur_sysdtspackages CURSOR FOR
SELECT DISTINCT [name], [id]
FROM sysdtspackages
WHERE [owner] = @old_owner
OPEN cur_sysdtspackages
FETCH NEXT FROM cur_sysdtspackages
INTO @name, @id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_reassign_dtspackageowner @name=@name, @id=@id,
@newloginname=@new_owner
FETCH NEXT FROM cur_sysdtspackages
INTO @name, @id
END
CLOSE cur_sysdtspackages
DEALLOCATE cur_sysdtspackages
GO
-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed]
Sent: Friday, November 21, 2003 7:31 PM
To: [Email Address Removed] Mohan.[Email Address Removed] LazyDBA.com Discussion
Subject: RE: DTS question
You guys are genius, the DTSbackup utility really works great, but it also
brought the old ownership over, could you tell me how to change these DTS
packages' owner?
Thank you again.
Sincerely, Fang
[Email Address Removed]
.GC.CA
To
11/21/2003 12:20 "LazyDBA.com Discussion"
PM <[Email Address Removed]
cc
Subject
RE: DTS question
Use the DTS backup utility.
This will backup all the DTS package you have on your server and copy them
to your new server.
-----Original Message-----
From: Surendran, Mohan [mailto:Mohan.[Email Address Removed] November 21, 2003 12:58 PM
To: LazyDBA.com Discussion
Subject: RE: DTS question
There are a few ways that you can do this.
1. Backup and restore the MSDB database from the original server. This is a
bit drastic but it will also bring across any DTS packages that were
scheduled as well. 2. Dowload the DTSBackup utility from www.sqldts.com
which gives you a nice app that you can use to transfer the packages around
3. Open the package in the original server and "Save As" into the new
server.
Hope this helps..
Best regards,
Mohan
-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed] Friday, November 21, 2003 5:40 PM
To: LazyDBA.com Discussion
Subject: DTS question
Hi, buddy,
I built a new SQL SERVER, and restored a database ther, but there are a lot
of local DTS (data transformation packages) on the original server, my
question is how to move these DTSs to the new server.
Any help will appreciate.
Thanks, Fang
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Get today's
cartoon: http://www.LazyDBA.com To unsubscribe, e-mail:
mssqldba-[Email Address Removed] For additional commands, e-mail:
mssqldba-[Email Address Removed] is a commercial communication from Commerzbank AG.
This communication is confidential and is intended only for the person to
whom it is addressed. If you are not that person you are not permitted to
make use of the information and you are requested to notify
<mailto:LONIB.[Email Address Removed] immediately that you have
received it and then destroy the copy in your possession.
Commerzbank AG may monitor outgoing and incoming e-mails. By replying to
this e-mail you consent to such monitoring. This e-mail message and any
attached files have been scanned for the presence of computer viruses.
However, you are advised that you open attachments at your own risk.
This email was sent either by Commerzbank AG, London Branch, or by
Commerzbank Securities, a division of Commerzbank. Commerzbank AG is a
limited liability company incorporated in the Federal Republic of Germany.
Registered Company Number in England BR001025. Our registered address in the
UK is 23 Austin Friars, London, EC2P 2JD. We are regulated by the Financial
Services Authority for the conduct of investment business in the UK and we
appear on the FSA register under number 124920.
**********************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Get today's
cartoon: http://www.LazyDBA.com To unsubscribe, e-mail:
mssqldba-[Email Address Removed] For additional commands, e-mail:
mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Get today's
cartoon: http://www.LazyDBA.com To unsubscribe, e-mail:
mssqldba-[Email Address Removed] For additional commands, e-mail:
mssqldba-[Email Address Removed]
This is a commercial communication from Commerzbank AG.
This communication is confidential and is intended only for the person to
whom it is addressed. If you are not that person you are not permitted to
make use of the information and you are requested to notify
<mailto:LONIB.[Email Address Removed] immediately that you have
received it and then destroy the copy in your possession.
Commerzbank AG may monitor outgoing and incoming e-mails. By replying to
this e-mail you consent to such monitoring. This e-mail message and any
attached files have been scanned for the presence of computer viruses.
However, you are advised that you open attachments at your own risk.
This email was sent either by Commerzbank AG, London Branch, or by
Commerzbank Securities, a division of Commerzbank. Commerzbank AG is a
limited liability company incorporated in the Federal Republic of Germany.
Registered Company Number in England BR001025. Our registered address in
the UK is 23 Austin Friars, London, EC2P 2JD. We are regulated by the
Financial Services Authority for the conduct of investment business in the
UK and we appear on the FSA register under number 124920.
**********************************************************************
MS Sql Server LazyDBA home page