changeing the table name while using transfer sql server object

Hi,
I am wondering using transfer sql server objects task in a sub-package and feeding tableslist property from the parent package. which works fine.
problem :

I want to be able to change the name in the fly so if I have
TableA I want to copy it for the destination as TableB
is there any work arround this just using transfer sql server objects task.
Thanks

A link to a similar thread of yours on this topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1094010&SiteID=1|||Thanks Phil for checking.
Although it is similar question but it is pointing to a different functionality of Transfer sql server object task.
I want to be able to copy a table between servers the tricky part is that I don not have the table in the destination and task creates the table in the target server.

so it captures a snapshot of the schema of the source table and applies it to the remote server and the then standard data copy between to tables.

problem: I want to be able to have a different table name for the target database. so I am wondering if it is possible with scripting to do so ( that is fi the task it self has got such a property ) or the task only will create the table with the same name (as the source).

Thanks
|||Just asking and am not trying to take away from your issue, but aren't there better tools for performing schema management rather than using SSIS? I'm not a DBA, so I don't really know the answer to that question, but it seems here that you're stretching the limits of what SSIS was designed to do (even if it *can* do it).

Are you going to run this one time, or many times?|||many times and con-currently
|||Not supported in the Transfer Objects task. However, you could use an Execute SQL after the table is copied to call sp_rename.|||yes, that's how I've done it , but I was wondering if there was a a nyway to stretch the Transfer sql server object task capability.
Cheers

change ownership of objects

A co-worker recently quit the company, and all of his stored
procedures and DTS packages are apparently under his name. Is there
any way to change his account to another (ie administrator or SA)'
I am afraid to delete his account in fear all the stored procedures
will be lost. (we have backups, but ... ugh)
(running mssql server 2000)Hello,
Fiirst of all change the password for his account to a one with special
charecters, so as no one can access. After that you could use
sp_changeobjectowner system proc to change the owner of all object from that
user to DBO. take
alook into sp_changeobjectowner in books online..Once you completed the
object owner to DBO you could drop the user and
login associated.
Thanks
Hari
"berwiki" <steveberwick@.gmail.com> wrote in message
news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>A co-worker recently quit the company, and all of his stored
> procedures and DTS packages are apparently under his name. Is there
> any way to change his account to another (ie administrator or SA)'
> I am afraid to delete his account in fear all the stored procedures
> will be lost. (we have backups, but ... ugh)
>
> (running mssql server 2000)
>|||To add on to my previous post to change DTS owner use:-
use msdb
go
sp_reassign_dtspackageowner [@.name =] 'name',[@.id =] 'id',[@.newl
oginname =]
'newloginname'
Thanks
Hari
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:...
> Hello,
> Fiirst of all change the password for his account to a one with special
> charecters, so as no one can access. After that you could use
> sp_changeobjectowner system proc to change the owner of all object from
> that user to DBO. take
> alook into sp_changeobjectowner in books online..Once you completed the
> object owner to DBO you could drop the user and
> login associated.
> Thanks
> Hari
> "berwiki" <steveberwick@.gmail.com> wrote in message
> news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>|||To add on Hari's reply
Run the output in the QA
SELECT 'EXEC sp_changeobjectowner '''+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
"berwiki" <steveberwick@.gmail.com> wrote in message
news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>A co-worker recently quit the company, and all of his stored
> procedures and DTS packages are apparently under his name. Is there
> any way to change his account to another (ie administrator or SA)'
> I am afraid to delete his account in fear all the stored procedures
> will be lost. (we have backups, but ... ugh)
>
> (running mssql server 2000)
>

Change ownership of all objects owned by

HI, I thought I read somewhere about a Stored Procedure that can be used to
change all objects owned by x to owned by y. Does anyone know of such SP,
tell me about it, please. OR Tell me a better way of changing ownership of
ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
another SQL login I've created with SA privileges, Then I can change the PW
of sa. OR can this not be done. ? PLEASE ADVISE!!This free tool may be a start...
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1116990,00.html?track=NL-464&ad=524311USCA
It uses a parameter of 'IF ^$^ <> ^dbo^ EXECUTE sp_changeobjectowner
^$.*^,^dbo^' to change the owner of selected objects.
"WANNABE" wrote:
> HI, I thought I read somewhere about a Stored Procedure that can be used to
> change all objects owned by x to owned by y. Does anyone know of such SP,
> tell me about it, please. OR Tell me a better way of changing ownership of
> ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
> another SQL login I've created with SA privileges, Then I can change the PW
> of sa. OR can this not be done. ? PLEASE ADVISE!!
>
>

Change ownership of all objects owned by

HI, I thought I read somewhere about a Stored Procedure that can be used to
change all objects owned by x to owned by y. Does anyone know of such SP,
tell me about it, please. OR Tell me a better way of changing ownership of
ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
another SQL login I've created with SA privileges, Then I can change the PW
of sa. OR can this not be done. ? PLEASE ADVISE!!This free tool may be a start...
http://searchsqlserver.techtarget.c...4&ad=524311USCA
It uses a parameter of 'IF ^$^ <> ^dbo^ EXECUTE sp_changeobjectowner
^$.*^,^dbo^' to change the owner of selected objects.
"WANNABE" wrote:

> HI, I thought I read somewhere about a Stored Procedure that can be used t
o
> change all objects owned by x to owned by y. Does anyone know of such SP,
> tell me about it, please. OR Tell me a better way of changing ownership
of
> ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
> another SQL login I've created with SA privileges, Then I can change the P
W
> of sa. OR can this not be done. ? PLEASE ADVISE!!
>
>

Change ownership for User Defined Data Types

Hello,

Does anybody know an easy way to change User Defined Data Types ownership to dbo, without dropping dependent objects?

I know that there is a st. proc sp_changeobject owner, but it does not deal with this particular db objects. Is there a similar st.procedure or script that would do the same operation?

Thank youThere are no dependent object to user-defined datatypes. If you try scripting out a table that was created with user-defined datatypes, you'll find it scripts the original datatype.
Neither do objects "inherit" changes made to User-defined datatype.
Thus: User-defined datatypes are candidates for the most useless and misleading features of SQL Server, perhaps exceeded only by the Maintenance Plan Wizard.|||Well, may be I expressed myself wrong. I was trying to change ownership for UDDTs, and I the only way I knew was to drop them and recreate as "dbo". But these UDDTs were already used by many tables and stored procedures. So I would need to drop those as well and then restore the data. Too much hassle.
Anyway, I have already found the solution. This is the script:

sp_configure 'Allow Update',1
Reconfigure WITH OVERRIDE

Update sysTypes set uid = user_id('dbo') Where name = …

sp_configure 'Allow Update',0
Reconfigure WITH OVERRIDE

Change of User Defined Datatype

I want to change the user defined data type from varchar(5000) to
varchar(8000). Do I need to drop all the objects referencing the udt
and re-create them?Hi
Yes, Currently UDT's can not be changed, so a drop and re-create is
required. To drop it. it has to be removed from all the objects that depend
on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||Actually, instead of dropping the objects that reference it, you may change
the data type of the columns to the native data type and then change it
back..This is the primary reason I do not use UDTs...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#xLY9e9yEHA.1264@.TK2MSFTNGP12.phx.gbl>...
> Actually, instead of dropping the objects that reference it, you may change
> the data type of the columns to the native data type and then change it
> back..This is the primary reason I do not use UDTs...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Fred" <fredericksiu@.hotmail.com> wrote in message
> news:a5795dd.0411152215.17971860@.posting.google.com...
> > I want to change the user defined data type from varchar(5000) to
> > varchar(8000). Do I need to drop all the objects referencing the udt
> > and re-create them?
Thanks a lot.|||why not use a tool to do it instead of all that manual labour?
check out DB Ghost at www.dbghost.com
DB Ghostâ?¢ provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghostâ?¢ will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"Fred" wrote:
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
>

Change of User Defined Datatype

I want to change the user defined data type from varchar(5000) to
varchar(8000). Do I need to drop all the objects referencing the udt
and re-create them?
Hi
Yes, Currently UDT's can not be changed, so a drop and re-create is
required. To drop it. it has to be removed from all the objects that depend
on it.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.co m...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
|||Actually, instead of dropping the objects that reference it, you may change
the data type of the columns to the native data type and then change it
back..This is the primary reason I do not use UDTs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.co m...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#xLY9e9yEHA.1264@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Actually, instead of dropping the objects that reference it, you may change
> the data type of the columns to the native data type and then change it
> back..This is the primary reason I do not use UDTs...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Fred" <fredericksiu@.hotmail.com> wrote in message
> news:a5795dd.0411152215.17971860@.posting.google.co m...
Thanks a lot.
|||why not use a tool to do it instead of all that manual labour?
check out DB Ghost at www.dbghost.com
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"Fred" wrote:

> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
>

Change of User Defined Datatype

I want to change the user defined data type from varchar(5000) to
varchar(8000). Do I need to drop all the objects referencing the udt
and re-create them?Hi
Yes, Currently UDT's can not be changed, so a drop and re-create is
required. To drop it. it has to be removed from all the objects that depend
on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||Actually, instead of dropping the objects that reference it, you may change
the data type of the columns to the native data type and then change it
back..This is the primary reason I do not use UDTs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#xLY9e9yEHA.1264
@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Actually, instead of dropping the objects that reference it, you may chang
e
> the data type of the columns to the native data type and then change it
> back..This is the primary reason I do not use UDTs...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Fred" <fredericksiu@.hotmail.com> wrote in message
> news:a5795dd.0411152215.17971860@.posting.google.com...
Thanks a lot.|||why not use a tool to do it instead of all that manual labour?
check out DB Ghost at www.dbghost.com
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will bu
ild
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, fo
r
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"Fred" wrote:

> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
>

Change Object Owner to dbo

Using SQL Server 7.0

Trying to change an objects ownership to dbo with

EXEC sp_changeobjectowner 'AssignInvoiceNumbers', 'dbo'

which gives the following error:

Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner,
Line 26
AssignInvoiceNumbers does not exist.

which it does.

Any help would be greatly appreciated

--

Nath

(Email mangling obvious)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Nath Wilson <chanester@.a0l.com> wrote in message news:<3f2f79f2$0$192$75868355@.news.frii.net>...
> Using SQL Server 7.0
> Trying to change an objects ownership to dbo with
> EXEC sp_changeobjectowner 'AssignInvoiceNumbers', 'dbo'
> which gives the following error:
> Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner,
> Line 26
> AssignInvoiceNumbers does not exist.
> which it does.
> Any help would be greatly appreciated
> --
> Nath
> (Email mangling obvious)
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

EXEC sp_changeobjectowner 'CurrentOwner.AssignInvoiceNumbers', 'dbo'

Simon