Changing Column Names

for a 100% guarentee to pick up any defects due to the
result of an object rename, build a database from the
source code of the database. Due to problems with
sp_depends and the flaws with the information_schema
views, these are no guarentee that you have highlighted
all your potential problems. If you do a build using your
source code, then all errors will show. Of course
building has it's problems as your build order my be an
issue. These are some of the problems that our software
addresses DB Ghost. It can build a database from source
code and it takes care of all dependancies - flagging up
errors within your source code. This is change management
software and answers the question, What happens if I make
a change?
If your interested why not read our white paper on the
subject
http://www.innovartis.co.uk/pdf/Inn...An_Automated_Ap
proach_To_Do_Change_Mgt.pdf
DB Ghost - the only database change management software
for SQL Server on the market today.

>--Original Message--
>Hey folks,
>I have alot of column names in a database that I'd like
to change however
>there are alot of Stored Procedures that reference the
existing names. Is
>there a way to make SQL Server update my Stored
Procedures whenever I change
>a table's column name?
>Thanks!
>
>.
>Will it go so far as telling you that data will fail to be inserted into a
table because the foreign key dependency data is not in the foreign key
table?
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...[vbcol=seagreen]
> for a 100% guarentee to pick up any defects due to the
> result of an object rename, build a database from the
> source code of the database. Due to problems with
> sp_depends and the flaws with the information_schema
> views, these are no guarentee that you have highlighted
> all your potential problems. If you do a build using your
> source code, then all errors will show. Of course
> building has it's problems as your build order my be an
> issue. These are some of the problems that our software
> addresses DB Ghost. It can build a database from source
> code and it takes care of all dependancies - flagging up
> errors within your source code. This is change management
> software and answers the question, What happens if I make
> a change?
> If your interested why not read our white paper on the
> subject
> http://www.innovartis.co.uk/pdf/Inn...An_Automated_Ap
> proach_To_Do_Change_Mgt.pdf
> DB Ghost - the only database change management software
> for SQL Server on the market today.
>
> to change however
> existing names. Is
> Procedures whenever I change|||yes

>--Original Message--
>Will it go so far as telling you that data will fail to
be inserted into a
>table because the foreign key dependency data is not in
the foreign key
>table?
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...
your[vbcol=seagreen]
up[vbcol=seagreen]
management[vbcol=seagreen]
make[vbcol=seagreen]
http://www.innovartis.co.uk/pdf/Inn...An_Automated_Ap[vbcol=seagreen]
like[vbcol=seagreen]
>
>.
>

Changing Column Names

for a 100% guarentee to pick up any defects due to the
result of an object rename, build a database from the
source code of the database. Due to problems with
sp_depends and the flaws with the information_schema
views, these are no guarentee that you have highlighted
all your potential problems. If you do a build using your
source code, then all errors will show. Of course
building has it's problems as your build order my be an
issue. These are some of the problems that our software
addresses DB Ghost. It can build a database from source
code and it takes care of all dependancies - flagging up
errors within your source code. This is change management
software and answers the question, What happens if I make
a change?
If your interested why not read our white paper on the
subject
http://www.innovartis.co.uk/pdf/Inno...n_Automated_Ap
proach_To_Do_Change_Mgt.pdf
DB Ghost - the only database change management software
for SQL Server on the market today.

>--Original Message--
>Hey folks,
>I have alot of column names in a database that I'd like
to change however
>there are alot of Stored Procedures that reference the
existing names. Is
>there a way to make SQL Server update my Stored
Procedures whenever I change
>a table's column name?
>Thanks!
>
>.
>
Will it go so far as telling you that data will fail to be inserted into a
table because the foreign key dependency data is not in the foreign key
table?
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...[vbcol=seagreen]
> for a 100% guarentee to pick up any defects due to the
> result of an object rename, build a database from the
> source code of the database. Due to problems with
> sp_depends and the flaws with the information_schema
> views, these are no guarentee that you have highlighted
> all your potential problems. If you do a build using your
> source code, then all errors will show. Of course
> building has it's problems as your build order my be an
> issue. These are some of the problems that our software
> addresses DB Ghost. It can build a database from source
> code and it takes care of all dependancies - flagging up
> errors within your source code. This is change management
> software and answers the question, What happens if I make
> a change?
> If your interested why not read our white paper on the
> subject
> http://www.innovartis.co.uk/pdf/Inno...n_Automated_Ap
> proach_To_Do_Change_Mgt.pdf
> DB Ghost - the only database change management software
> for SQL Server on the market today.
> to change however
> existing names. Is
> Procedures whenever I change
|||yes

>--Original Message--
>Will it go so far as telling you that data will fail to
be inserted into a
>table because the foreign key dependency data is not in
the foreign key
>table?
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...
your[vbcol=seagreen]
up[vbcol=seagreen]
management[vbcol=seagreen]
make[vbcol=seagreen]
http://www.innovartis.co.uk/pdf/Inno...n_Automated_Ap[vbcol=seagreen]
like
>
>.
>

Changing a linkedserver datasource

Hi all,

I'm trying to change the datasource of a SQL Server LinkedServer using SMO

I've got the relevant linkedserver as an object, changed the DataSource property, and selected it again to confirm the change.
However, the change is lost as soon as the object is destroyed.
Looking at BoL I think I need to be using alter() method of the linkedserver class but I just get an error
Alter failed for LinkedServer '<servername>'.

any ideas how I should be using this class to do what I want?

I can post my script (PowerShell) if it would help.

Thanks, Robin.

Hi Robin,

The DataSource property of an existing LinkedServer cannot be modified. To modify the DataSource, you need to drop and re-create the linked server.

Thanks,

Kuntal

|||Hi Kuntal,
Could you demonstrate how I would do this in code (any language)
I have tried various ways but either manage to build the linkedserver object with the properties of the old server and then get an error when i try to create it, or i can create it but then not set the properties.

Many thanks,

Robin.
|||

Hi Robin,

Here is a small sample which might be of some help.

Server server = newServer("localhost");

LinkedServer lserver = newLinkedServer(server, "testServer");

lserver.DataSource = "Server1";

lserver.ProductName = "SQLNCLI";

lserver.ProviderName = "SQLNCLI";

lserver.Create();

// now you need to change the datasource and keep the rest of properties the same

lserver.DataSource = "Server2";

StringCollection script = lserver.Script();

lserver.Drop();

server.ConnectionContext.ExecuteNonQuery(script);

The above code first creates a linked server and then executes the script to create the same with a different datasource. Now you can create a new linked server by running that script after dropping the original server.

Hope that helps.

Thanks,

Kuntal

|||Many thanks, that's brilliant!

So just to check I'm understanding correctly, another way to express your solution would be, assuming there was a linked server called TESTLS already defined:

Server server = newServer("localhost");

LinkedServer lserver = server.linkedservers.item("TESTLS")

// now you need to change the datasource and keep the rest of properties the same

lserver.DataSource = "Server2";

StringCollection script = lserver.Script();

lserver.Drop();

server.ConnectionContext.ExecuteNonQuery(script);

Changing a linkedserver datasource

Hi all,

I'm trying to change the datasource of a SQL Server LinkedServer using SMO

I've got the relevant linkedserver as an object, changed the DataSource property, and selected it again to confirm the change.
However, the change is lost as soon as the object is destroyed.
Looking at BoL I think I need to be using alter() method of the linkedserver class but I just get an error
Alter failed for LinkedServer '<servername>'.

any ideas how I should be using this class to do what I want?

I can post my script (PowerShell) if it would help.

Thanks, Robin.

Hi Robin,

The DataSource property of an existing LinkedServer cannot be modified. To modify the DataSource, you need to drop and re-create the linked server.

Thanks,

Kuntal

|||Hi Kuntal,
Could you demonstrate how I would do this in code (any language)
I have tried various ways but either manage to build the linkedserver object with the properties of the old server and then get an error when i try to create it, or i can create it but then not set the properties.

Many thanks,

Robin.
|||

Hi Robin,

Here is a small sample which might be of some help.

Server server = new Server("localhost");

LinkedServer lserver = new LinkedServer(server, "testServer");

lserver.DataSource = "Server1";

lserver.ProductName = "SQLNCLI";

lserver.ProviderName = "SQLNCLI";

lserver.Create();

// now you need to change the datasource and keep the rest of properties the same

lserver.DataSource = "Server2";

StringCollection script = lserver.Script();

lserver.Drop();

server.ConnectionContext.ExecuteNonQuery(script);

The above code first creates a linked server and then executes the script to create the same with a different datasource. Now you can create a new linked server by running that script after dropping the original server.

Hope that helps.

Thanks,

Kuntal

|||Many thanks, that's brilliant!

So just to check I'm understanding correctly, another way to express your solution would be, assuming there was a linked server called TESTLS already defined:

Server server = new Server("localhost");

LinkedServer lserver = server.linkedservers.item("TESTLS")

// now you need to change the datasource and keep the rest of properties the same

lserver.DataSource = "Server2";

StringCollection script = lserver.Script();

lserver.Drop();

server.ConnectionContext.ExecuteNonQuery(script);

Changing 100 table object owner names at once

Is there any way I can change the object owner for over
100 tables in a single data base at once in SQL 7 without
using the sp on each table?
Well you can do an update on sysobjects but if you look at the sp it does a lot of checking and clearing up.
Better to code a loop to execute it on all the tables necessary.
"Smith" wrote:

> Is there any way I can change the object owner for over
> 100 tables in a single data base at once in SQL 7 without
> using the sp on each table?
>
sql

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 owner

Is there a system SP to change object owner in the DB?Yes. Look up
sp_changeobjectowner

In Books on Line.

Jackie

"William F. O'Neill" <wfoneill@.bellsouth.net> wrote in message
news:J2A9d.299313$%n4.203169@.bignews6.bellsouth.ne t...
> Is there a system SP to change object owner in the DB?

Change of Ownership??

I am having a problem with object ownership.
The person who setup the SQL Server setup the "dbo" account with his personal permissions. I need to change "dbo" so that the login name is the system account and not a personal account. Is this possible?

If this is not possible can I change the ownership of a system object?
I was able to change ownershipof user objects using the "sp_changeobjectowner" stored procedure, but it did not seem to work for system objects.

Any way around this?

- Ericdid you try sp_changedbowner? This would change the database ownership which I believe should change the system object ownership?>?|||The dbo user is so tightly "hardwired" into the database that I'd give the user a new login, change the ownership of their tables to that new user, and stop right there. I wouldn't even consider allowing the user to keep using dbo.

-PatP

Change Object Ownership

I'm running SQL2K on an Win 2K Server. Because of addition of additions to our network, we've installed AD on the Win 2K to graduate
it to a Domain Controller Server. Checking the event log, one finds
an ownership warning:

Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 1/15/2002
Time: 8:00:01 PM
User: N/A
Computer: XXXXX-XXX
Description:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'' (0x72097D93A71E1842AA6C46E6AC59750B) - Status: Failed - Invoked on: 2002-01-15 20:00:01 - Message: The job failed. The owner () of job DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' does not have server access.

I believe this warning is generated because the SQLServer system is
looking to execute the DB Backup plan using the new Domain
administrative account. I came to this conclusion after reviewing
replication ownership warnings in the event log. I was easly able to
change replication jobs from then new Domain administrative account
to sa.

I would like to change the ownership to sa for a Maintenance Plan.
Is there a way to do this? Is this the solution?You would be better off not using a maintenance plan but coding the requirement yourself in a stored procedure. It is very easy to do and gets round these problems as you know what it is doing.

Change Object Ownership

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)berwiki (steveberwick@.gmail.com) writes:

Quote:

Originally Posted by

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)

For the stored procedures do:

SELECT 'EXEC sp_changeobjectowner ''' + name + ''', '''dbo''''
FROM sysobjects
WHERE uid = USER_ID('co-worker')

copy, paste and run result set. This will take care of stored procedures,
tables etc. Note that any references to these procedures from elsewhere will
need to be updated.

I have never worked with DYS, so I cannot answer that part.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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

Change object all object onwer

I have a script like below to change all the object owner from "xxxxx"
to "dbo".
==================================== declare @.name varchar(250)
declare @.olduser varchar(250)
declare @.newuser varchar(250)
declare @.sp_str varchar(250)
set @.olduser = 'xxxxx.'
set @.newuser = 'dbo'
declare cursor1 cursor for
select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
uid of 'xxxxx')
open cursor1
fetch next from cursor1 into @.name
WHILE @.@.FETCH_STATUS = 0
--while begin transaction
begin
set @.sp_str = @.olduser + @.name
exec sp_changeObjectOwner @.sp_str,@.newuser
fetch next from cursor1 into @.name
end
--commit transaction
close cursor1
deallocate cursor1
====================================
How if I want to change only a particular database, don't care what's
the existing object owner is, but just change all the object owner to
"xxxxx"?
Does anyone have good script to change that?
Or any way to modify the above script to acomplish that?
Peter CCHChange one line:
select sysobjects.name from sysobjects where sysobjects.xtype = 'u' and
sysobjects.uid NOT IN (SELECT sysusers.uid FROM sysusers WHERE sysusers.name
<> @.newuser)
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/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133099207.669540.204000@.g14g2000cwa.googlegroups.com...
>I have a script like below to change all the object owner from "xxxxx"
> to "dbo".
> ====================================> declare @.name varchar(250)
> declare @.olduser varchar(250)
> declare @.newuser varchar(250)
> declare @.sp_str varchar(250)
> set @.olduser = 'xxxxx.'
> set @.newuser = 'dbo'
> declare cursor1 cursor for
> select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
> uid of 'xxxxx')
> open cursor1
> fetch next from cursor1 into @.name
> WHILE @.@.FETCH_STATUS = 0
> --while begin transaction
> begin
> set @.sp_str = @.olduser + @.name
> exec sp_changeObjectOwner @.sp_str,@.newuser
> fetch next from cursor1 into @.name
> end
> --commit transaction
> close cursor1
> deallocate cursor1
> ====================================> How if I want to change only a particular database, don't care what's
> the existing object owner is, but just change all the object owner to
> "xxxxx"?
> Does anyone have good script to change that?
> Or any way to modify the above script to acomplish that?
>
>
> Peter CCH
>

Change object all object onwer

I have a script like below to change all the object owner from "xxxxx"
to "dbo".
====================================
declare @.name varchar(250)
declare @.olduser varchar(250)
declare @.newuser varchar(250)
declare @.sp_str varchar(250)
set @.olduser = 'xxxxx.'
set @.newuser = 'dbo'
declare cursor1 cursor for
select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
uid of 'xxxxx')
open cursor1
fetch next from cursor1 into @.name
WHILE @.@.FETCH_STATUS = 0
--while begin transaction
begin
set @.sp_str = @.olduser + @.name
exec sp_changeObjectOwner @.sp_str,@.newuser
fetch next from cursor1 into @.name
end
--commit transaction
close cursor1
deallocate cursor1
====================================
How if I want to change only a particular database, don't care what's
the existing object owner is, but just change all the object owner to
"xxxxx"?
Does anyone have good script to change that?
Or any way to modify the above script to acomplish that?
Peter CCH
Change one line:
select sysobjects.name from sysobjects where sysobjects.xtype = 'u' and
sysobjects.uid NOT IN (SELECT sysusers.uid FROM sysusers WHERE sysusers.name
<> @.newuser)
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/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133099207.669540.204000@.g14g2000cwa.googlegr oups.com...
>I have a script like below to change all the object owner from "xxxxx"
> to "dbo".
> ====================================
> declare @.name varchar(250)
> declare @.olduser varchar(250)
> declare @.newuser varchar(250)
> declare @.sp_str varchar(250)
> set @.olduser = 'xxxxx.'
> set @.newuser = 'dbo'
> declare cursor1 cursor for
> select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
> uid of 'xxxxx')
> open cursor1
> fetch next from cursor1 into @.name
> WHILE @.@.FETCH_STATUS = 0
> --while begin transaction
> begin
> set @.sp_str = @.olduser + @.name
> exec sp_changeObjectOwner @.sp_str,@.newuser
> fetch next from cursor1 into @.name
> end
> --commit transaction
> close cursor1
> deallocate cursor1
> ====================================
> How if I want to change only a particular database, don't care what's
> the existing object owner is, but just change all the object owner to
> "xxxxx"?
> Does anyone have good script to change that?
> Or any way to modify the above script to acomplish that?
>
>
> Peter CCH
>

Change object all object onwer

I have a script like below to change all the object owner from "xxxxx"
to "dbo".
====================================
declare @.name varchar(250)
declare @.olduser varchar(250)
declare @.newuser varchar(250)
declare @.sp_str varchar(250)
set @.olduser = 'xxxxx.'
set @.newuser = 'dbo'
declare cursor1 cursor for
select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
uid of 'xxxxx')
open cursor1
fetch next from cursor1 into @.name
WHILE @.@.FETCH_STATUS = 0
--while begin transaction
begin
set @.sp_str = @.olduser + @.name
exec sp_changeObjectOwner @.sp_str,@.newuser
fetch next from cursor1 into @.name
end
--commit transaction
close cursor1
deallocate cursor1
====================================
How if I want to change only a particular database, don't care what's
the existing object owner is, but just change all the object owner to
"xxxxx"?
Does anyone have good script to change that?
Or any way to modify the above script to acomplish that?
Peter CCHChange one line:
select sysobjects.name from sysobjects where sysobjects.xtype = 'u' and
sysobjects.uid NOT IN (SELECT sysusers.uid FROM sysusers WHERE sysusers.name
<> @.newuser)
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/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133099207.669540.204000@.g14g2000cwa.googlegroups.com...
>I have a script like below to change all the object owner from "xxxxx"
> to "dbo".
> ====================================
> declare @.name varchar(250)
> declare @.olduser varchar(250)
> declare @.newuser varchar(250)
> declare @.sp_str varchar(250)
> set @.olduser = 'xxxxx.'
> set @.newuser = 'dbo'
> declare cursor1 cursor for
> select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
> uid of 'xxxxx')
> open cursor1
> fetch next from cursor1 into @.name
> WHILE @.@.FETCH_STATUS = 0
> --while begin transaction
> begin
> set @.sp_str = @.olduser + @.name
> exec sp_changeObjectOwner @.sp_str,@.newuser
> fetch next from cursor1 into @.name
> end
> --commit transaction
> close cursor1
> deallocate cursor1
> ====================================
> How if I want to change only a particular database, don't care what's
> the existing object owner is, but just change all the object owner to
> "xxxxx"?
> Does anyone have good script to change that?
> Or any way to modify the above script to acomplish that?
>
>
> Peter CCH
>