Changing column values on table insert trigger

Hello,

i have a table and if a record is inserted i will test a numeric value
in this table. If the this value is greather as 1 million, than an
status column should be changed from 'A' to 'B'.

Yes and sorry, this is a newbie question.

On Oracle this work simple:

create trigger myTrigger on tableX
as
begin
if :old.x 100000 then
:new.y:='B'
end if;
end;

Thanks
MaikYou can look up SQL Server Books Online to learn how to write a trigger in
t-SQL. You'll need to utilize the inserted/deleted virtual tables to
accomodate multi-row inserts. The update statement will be something along
the lines of:

UPDATE tableX
SET y = B
WHERE EXISTS ( SELECT *
FROM inserted i
WHERE i.key_col = tableX.key_col
AND inserted.x 1000000 ) ;

Here key_col is any column or set of columns that can uniquely identify a
row in tableX.

--
Anith|||Hi,

Quote:

Originally Posted by

i have a table and if a record is inserted i will test a numeric value
in this table. If the this value is greather as 1 million, than an
status column should be changed from 'A' to 'B'.
>
Yes and sorry, this is a newbie question.
>
On Oracle this work simple:
>
create trigger myTrigger on tableX
as
begin
if :old.x 100000 then
:new.y:='B'
end if;
end;

MS SQL Server doesn't have "before action" triggers, so you
have to update the table and use the INSERTED virtual table
to know what records were inserted into the table.

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.comsql

changing column type

Hi I have transactional replication and I need a tables column data type
changed from char(30) to varchar(40). What would be the best way and least
dangerous to edit this column and get it to replicate the changes succesfully.
thanks for any advice
Sammy
Sammy,
please check out these articles:
http://www.replicationanswers.com/AddColumn.asp
and
http://www.replicationanswers.com/AlterSchema2005.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

changing column type

hi,
i just want the change the type of a column in a table within sql server
2000 enterprise manager. however it warns that,
'MyTable' table
- Warning: One or more existing columns have ANSI_PADDING 'off' and will be
re-created with ANSI_PADDING 'on'.
- Warning: The table was created with ANSI_NULLS 'off' and will be
re-created with ANSI_NULLS 'on'.
i don't want to get those options changed when i change the type of the
column and this is not the only database on the server that we cannot stop
the server to change the configuration options(ANSI_PADDING, ANSI_NULLS) of
the sql server. it seems that we have to use sp_dboption stored procedure to
change those options for the current database. my main intention is not to
deal with query analyzer to execute sql statements, only get the job done
through enterprise manager. isn't it possible through enterprise manager to
change the database options only for the current database? i looked through
the menus may be i missed.
thanksEM goes through a much more complex CREATE/INSERT/DROP table routine =when changing data types. Have you tried changing the column via Query =Analyzer?
I have not tried it...but I am guessing that it might work better via =Query Analyzer
ALTER TABLE x ALTER COLUMN y DesiredDataTypeGoesHere
You can always try to make the change via Enterprise Manager, but make =sure that you script out the changes instead of having it apply them for =you. You should then be able to modify the code as you wish.
-- Keith
"Richard" <rich@.thetop.com> wrote in message =news:eBhBgH4nDHA.2304@.TK2MSFTNGP11.phx.gbl...
> hi,
> i just want the change the type of a column in a table within sql =server
> 2000 enterprise manager. however it warns that,
> > 'MyTable' table
> - Warning: One or more existing columns have ANSI_PADDING 'off' and =will be
> re-created with ANSI_PADDING 'on'.
> - Warning: The table was created with ANSI_NULLS 'off' and will be
> re-created with ANSI_NULLS 'on'.
> > i don't want to get those options changed when i change the type of =the
> column and this is not the only database on the server that we cannot =stop
> the server to change the configuration options(ANSI_PADDING, =ANSI_NULLS) of
> the sql server. it seems that we have to use sp_dboption stored =procedure to
> change those options for the current database. my main intention is =not to
> deal with query analyzer to execute sql statements, only get the job =done
> through enterprise manager. isn't it possible through enterprise =manager to
> change the database options only for the current database? i looked =through
> the menus may be i missed.
> thanks
> >

Changing Column size/type with Derived Column

I have a number of date columns that are parsed as DT_WSTR (6) and I have written a Derived Column converting them into DT_DATE via this (found on the forums) type expression:
(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))
But I really want to replace the current column, not create a new one. If I use "replace" the data is forced to be a DT_WSTR (6), and I get a truncation error at run-time.
Simeon
Simeon,
You're stuck with it I'm afraid. You can't change the type of a column in the derived column component. Its not a ahrdship to add it as a new column though, jsut don't use the existing one that's all!

-Jamie|||Could you change the source to return a larger column. This would solve your problem.|||Cheers Jamie,
That's what I thought, I was just hoping there was a way to keep it "clean".
On a related thought, I am finding that each time I alter any component near the top of a data flow, I end up needing to delete and re-add most the down stream components due to fields mismatching. Is this why people appear to be building there packages via code?
Simeon.
|||This does depend on the component, some just need double clicking on and the meta data should correct it self, others require you to select the mapped columns. The latter is generally when you change the names of components and inputs.

You shouldn't have to delete components though, I find that surprising.|||

SimonSa wrote:

Could you change the source to return a larger column. This would solve your problem.

That might work, but the derived column sets the type to DT_WSTR, so I'm not sure that putting a entry that is cast to DT_DATE would not upset it also.
|||

I was finding this while I was developing my source component. I had it wired to Raw Files (then later Trash Destinations) with Data Viewers to inspect the data. Running the package (after reloading BI) would give errors, so I found it easier to delete the source and it four outputs, and re-wire.
But going forward I'll try double clicking, and checking the mappings.

Simeon
|||Do you need to cast it to a date? If you do then you will have to have a new column, and the derived column is the best solution|||I would suggest your source component is recreating outputs when it shouldn't. Thus the metadata the downstream components are based on is no longer valid.|||

SimonSa wrote:

I would suggest your source component is recreating outputs when it shouldn't. Thus the metadata the downstream components are based on is no longer valid.

It was. I was slowly adding support for different data types, then adding support for foreign keys. The source component is like a flat file parser, but it handles files that have different rows (with different columns) that have relationships based on order. So really n tables with the foreign keys implied by what a row follows.
I was (for simplicity) developing support incrementally, with the relations setup by a function. The next step is to put that information into a configuration file.
|||A column is identified by it's lineage Id. Deleteing a column and adding it back, even with the same name and same data type properties will cause it to change lineage Id. This means downstream components that have referenced that column (by lineage Id) are now invalid. Opening the UI should bring up the mapping dialog, and one of the options is Map by Name. This normally solves most issues.

A well behaved component will not recreate the output buffer columns each time, but rather detect invalid columns, and remove, add new columns if required, and fix any columns in can detect on both sides, or leave alone matching columns. This can be a pain, as it is lots more code, but try the samples such as the ADO Source for some good template code.

changing column size on publisher side table

Hello,
If I want to change the column size of a table under replication on the
publisher side, can I do this manually? And then manually change the column
size of the corresponding table on the Subscriber side? Or does this require
the use of Replication SP's? If so, what Replication SPs would I need to use
to change a column size?
Thanks,
Rich
Rich - this largely depends on what version of SQL Server you are using:
http://www.replicationanswers.com/AlterSchema2005.asp
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the reply. I am using Sql Server 2000 Standard Edition, and the
table is under Merge Replication. I wasn't clear on if I could manually
change the size. It looks like I need to use the sp_repladdcolumn and
sp_repldropcolumn to accomplish this. I have, in fact, used these SPs to
add a new column. But I am just checking if I need to use them to change the
size of the column. Yes, No, my choice?
Thanks,
Rich
"Paul Ibison" wrote:

> Rich - this largely depends on what version of SQL Server you are using:
> http://www.replicationanswers.com/AlterSchema2005.asp
> http://www.replicationanswers.com/AddColumn.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Changing column size in existing databese?

I must increase column (filed) size in existing datebase but without using
Enterprise manager...(Becouse we use MSDE on our clients PCs)
The Filed is part of primary and foreign key constraints...
And every constraint has diferent index number in each database...
for example (PK_something_9e382hjl8), and I don't know how to pick this
value before "drop constraint" command....

Thank you very much...[posted and mailed, please reply in news]

Poted (dario1975@.post.hnet.hr) writes:
> I must increase column (filed) size in existing datebase but without
> using Enterprise manager...(Becouse we use MSDE on our clients PCs) The
> Filed is part of primary and foreign key constraints... And every
> constraint has diferent index number in each database... for example
> (PK_something_9e382hjl8), and I don't know how to pick this value before
> "drop constraint" command....

Moral: name your constraint explicitly according to some standardized
scheme, so you easily can find the names.

This query gives you the name of the PK for a table:

select name from sysobjects
where xtype = 'PK'
and parent_obj = object_id('tablename')

This query lists all referencing foreign key for a table:

select object_name(constid), object_name(fkeyid), col_name(fkeyid, fkey)
from sysforeignkeys
where rkeyid = object_id('depots')
order by constid, fkey

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Changing column order in a table

This subject has been posted several times, but I haven't seen a good
answer.

Problem:
I want to change the order of the columns in a table using T-SQL only.

Explanation:
After running your code, I want to see the following table...

CREATE TABLE [dbo].[TableName] (
[First_Column] [int] NULL ,
[Second_Column] [varchar] (20) NULL
) ON [PRIMARY]

look like this...

CREATE TABLE [dbo].[TableName] (
[Second_Column] [varchar] (20) NULL ,
[First_Column] [int] NULL
) ON [PRIMARY]

Limitations:
Don't post if your post would fall in the following categories:
1. If you don't think it can be done
2. If you think Enterprise Manager is the only way to do this
3. If you think I should just change the order of my Select
statements
4. If you want to state that order column doesn't matter in a
relational database
5. If you want to ask me why I want to do this

Wish:
Hopefully the answer WON'T involve creating a brand new table, moving
the data from old to new, dropping the old table, then renaming the
new table to the old name. Yes, I can do that. The table I'm working
with is extremely huge -- I don't want to do the data juggling.

Thanks in advance!Dan,

I hope this doesn't fall into your 5 commandments ;-) If by any reason the
order of columns is important to you, you can use a view. I do understand
that I'm not supposed to ask you why you want to do this, but why!!? :-) If
you tell us your original problem then you might be lucky enough to find
somebody that can give you some other alternatives. I'm sure you have a
reason for this. Without revealing much information about what you want to
do it's really hard to find the solution.

Good luck,
Shervin

"Dan Newton" <dnewton@.scriptsave.com> wrote in message
news:280c01a4.0312091312.2f52e02@.posting.google.co m...
> This subject has been posted several times, but I haven't seen a good
> answer.
> Problem:
> I want to change the order of the columns in a table using T-SQL only.
> Explanation:
> After running your code, I want to see the following table...
> CREATE TABLE [dbo].[TableName] (
> [First_Column] [int] NULL ,
> [Second_Column] [varchar] (20) NULL
> ) ON [PRIMARY]
> look like this...
> CREATE TABLE [dbo].[TableName] (
> [Second_Column] [varchar] (20) NULL ,
> [First_Column] [int] NULL
> ) ON [PRIMARY]
> Limitations:
> Don't post if your post would fall in the following categories:
> 1. If you don't think it can be done
> 2. If you think Enterprise Manager is the only way to do this
> 3. If you think I should just change the order of my Select
> statements
> 4. If you want to state that order column doesn't matter in a
> relational database
> 5. If you want to ask me why I want to do this
> Wish:
> Hopefully the answer WON'T involve creating a brand new table, moving
> the data from old to new, dropping the old table, then renaming the
> new table to the old name. Yes, I can do that. The table I'm working
> with is extremely huge -- I don't want to do the data juggling.
> Thanks in advance!|||dnewton@.scriptsave.com (Dan Newton) wrote in message news:<280c01a4.0312091312.2f52e02@.posting.google.com>...
> This subject has been posted several times, but I haven't seen a good
> answer.
> Problem:
> I want to change the order of the columns in a table using T-SQL only.
> Explanation:
> After running your code, I want to see the following table...
> CREATE TABLE [dbo].[TableName] (
> [First_Column] [int] NULL ,
> [Second_Column] [varchar] (20) NULL
> ) ON [PRIMARY]
> look like this...
> CREATE TABLE [dbo].[TableName] (
> [Second_Column] [varchar] (20) NULL ,
> [First_Column] [int] NULL
> ) ON [PRIMARY]
> Limitations:
> Don't post if your post would fall in the following categories:
> 1. If you don't think it can be done
> 2. If you think Enterprise Manager is the only way to do this
> 3. If you think I should just change the order of my Select
> statements
> 4. If you want to state that order column doesn't matter in a
> relational database
> 5. If you want to ask me why I want to do this
> Wish:
> Hopefully the answer WON'T involve creating a brand new table, moving
> the data from old to new, dropping the old table, then renaming the
> new table to the old name. Yes, I can do that. The table I'm working
> with is extremely huge -- I don't want to do the data juggling.
> Thanks in advance!

You will have to create a new table, copy the data, then rename it.
This may be tedious, but it will work and it is supported. The only
'short cut' would be to attempt to hack syscolumns, but since many
columns are 'internal use only' and direct updates to system tables
are not supported, you have no guarantee of success, and no support
from Microsoft if you have problems (which may not appear at first).

Or as Shervin suggested, if you can provide more information on what
your issue is, there may be an alternative solution.

Simon

Changing column names in SSMS for SQL Server 2005

I changed the name of a column in the Summary pane of SSMS. I was a bit surprised (but pleased) to see that I could make the change there.

When I tried to use the new column name in a Query, I received the error message, "column not found."

When I used "modify" in the Object Explorer pane of SSMS I was able to see that "(int, not null)" had been appended to the column name in the table.

I don't know if this is a BUG, or an undocumented feature.

Have others here run into this? I am using SS2005, with SP1.

Dan

"int, not null" is not appended to a column name, it is part of the syntax for defining a column. If you can see the column in Object Explorer after you added and saved it you should be able to refer to it in a query.

Service Pack 2 is out ...

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx

|||

lkh,

Yes, I am sorry to say that "(int, not null)" WAS appended to the column name.

When I went into "Modify" in Object Explorer, I found the column name as

[New_name (int, not null)]. The column characteristics were "(int, not null)".

The square brackets were placed there as a result of my editing the name in the Summary pane.

So yes, "(int, not null)" was part of the new name created when I edited the name in the Summary pane.

Dan

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

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!No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> 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!
>

Changing Column Names

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!No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> 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!
>|||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/Innovartis_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...
> 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/Innovartis_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!
> >
> >
> >.
> >|||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...
>> 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/Innovartis_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!
>> >
>> >
>> >.
>> >
>
>.
>sql

Changing Column Names

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!
No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> 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!
>

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 Column Name on a table

Hi All,
I am trying to change column name on an existing table. I am using SQL
Server 7.

As the table is quite big, it is taking quite long time to do it.
By the way I could change the column name only through the Enterprize
Manager.

Is it possible to change the column name using SQL script?
Why the change of column name will depend on the size of the table?

Thanks for your answer.

-MoklesYou can change a column name using sp_rename.

EXEC sp_rename 'MyTable.OldColumnName', 'NewColumnName', 'COLUMN'

See the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mokles" <mokles@.rogers.com> wrote in message
news:wIGdnaGM46YtX_jcRVn-oA@.rogers.com...
> Hi All,
> I am trying to change column name on an existing table. I am using SQL
> Server 7.
> As the table is quite big, it is taking quite long time to do it.
> By the way I could change the column name only through the Enterprize
> Manager.
> Is it possible to change the column name using SQL script?
> Why the change of column name will depend on the size of the table?
> Thanks for your answer.
> -Mokles|||Hi Mokles

Have you tried to rename the field using sp_rename.

Enterprise manager does the job using same kind of scripts but its not
intelligent enough I have observed in various scenarios that sometimes
it accomplishes simple tasks using long approach.

I wonder if there are any indexes on the table ?

Regards
Shehzad

"mokles" <mokles@.rogers.com> wrote in message news:<wIGdnaGM46YtX_jcRVn-oA@.rogers.com>...
> Hi All,
> I am trying to change column name on an existing table. I am using SQL
> Server 7.
> As the table is quite big, it is taking quite long time to do it.
> By the way I could change the column name only through the Enterprize
> Manager.
> Is it possible to change the column name using SQL script?
> Why the change of column name will depend on the size of the table?
> Thanks for your answer.
> -Mokles|||I could do it using Sp_Rename.
Enterprize Manager was using complicated method to do the simple task
Anyway thanks for the help

-Mokles

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:6Qk9d.2605$Al3.1466@.newssvr30.news.prodigy.co m...
> You can change a column name using sp_rename.
> EXEC sp_rename 'MyTable.OldColumnName', 'NewColumnName', 'COLUMN'
> See the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "mokles" <mokles@.rogers.com> wrote in message
> news:wIGdnaGM46YtX_jcRVn-oA@.rogers.com...
> > Hi All,
> > I am trying to change column name on an existing table. I am using SQL
> > Server 7.
> > As the table is quite big, it is taking quite long time to do it.
> > By the way I could change the column name only through the Enterprize
> > Manager.
> > Is it possible to change the column name using SQL script?
> > Why the change of column name will depend on the size of the table?
> > Thanks for your answer.
> > -Mokles|||Today I tried to do it with sp_Rename and it worked well and fast.

Previously I was using profiler to see what the Enterprize Manager was
doing. And really it was using complicated method to change the column name.

Thanks all for the help

-Mokles

"Shehzad Shabbir" <shehzad@.internav.com> wrote in message
news:503e40d5.0410071952.643d25ee@.posting.google.c om...
> Hi Mokles
> Have you tried to rename the field using sp_rename.
> Enterprise manager does the job using same kind of scripts but its not
> intelligent enough I have observed in various scenarios that sometimes
> it accomplishes simple tasks using long approach.
> I wonder if there are any indexes on the table ?
> Regards
> Shehzad
>
> "mokles" <mokles@.rogers.com> wrote in message
news:<wIGdnaGM46YtX_jcRVn-oA@.rogers.com>...
> > Hi All,
> > I am trying to change column name on an existing table. I am using SQL
> > Server 7.
> > As the table is quite big, it is taking quite long time to do it.
> > By the way I could change the column name only through the Enterprize
> > Manager.
> > Is it possible to change the column name using SQL script?
> > Why the change of column name will depend on the size of the table?
> > Thanks for your answer.
> > -Mokles

Changing column name

I need to change a column's name say from col_ab to col_cd, what's the synta
x?
--
TSLook up sp_rename in Books Online.
"TS" <TS@.discussions.microsoft.com> wrote in message
news:1ED0D0AF-3D61-4C64-B296-28325F69959A@.microsoft.com...
>I need to change a column's name say from col_ab to col_cd, what's the
>syntax?
> --
> TS|||sp_rename '<table name>.<old column name>', '<new column name>', 'COLUMN'
"TS" wrote:

> I need to change a column's name say from col_ab to col_cd, what's the syn
tax?
> --
> TS

Changing column name

Hi all,

I have a table that has integrity constraints in place and it is populated with the data. I need to rename one of the columns in the table. I am hesitant to use sp_rename procedure because when I used it before I get a warning message that says "changing any part of an object name could break scriptd and stored procedure." how big of a problem is it? Is there any other way to do it without hearting anything? Thanks.All that means is that you will have to update references to the column name in any code that has already been created.sql

Changing Column Name

Is there a way to change a column name in one of the tables? Say from XX to
YY. What's the syntax for that?
--
TSTS, try:
EXEC sp_rename 'tablename.[oldcolname]', 'newcolname', 'COLUMN'
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"TS" <TS@.discussions.microsoft.com> wrote in message
news:E666BE78-43C2-4396-9B88-E92054D70B8C@.microsoft.com...
> Is there a way to change a column name in one of the tables? Say from XX
> to
> YY. What's the syntax for that?
> --
> TS

Changing column length of a replicated table

Can I increase the length of a varchar column of table involved in transactional replication without dropping and recreating publication/subscription?

Any help/short-cuts/undocumented features greatly appreciated.

Regards

Opal

Are you using SQL Server 2005? If so, you can turn on @.replicate_ddl option (set it to 1) for a publication then use regular "ALTER TABLE ... ALTER COLUMN ..." DDL syntax to increase the length of the column, the DDL change will be automatically replicated to subscriber by replication. (@.replication_ddl options is automatically turned on if you use management studio to create the publication)

Hope that helps,

Zhiqiang Feng

|||

Thank you Zhiqiang for your reply, but sorry we're using 2000.

Opal

|||

Sorry there is no easy workaround in SQL Server 2000. The only alternative is that you can use sp_repldropcolumn then sp_repladdcolumn to drop and re-create the column, but that way you will lose all the data for that column.

Thanks,

Zhiqiang Feng

Changing Column Length

I have a table which gets it feed from external source. I need to chnge the
length of the column on DBs which are in Prod. The table has couple of
indexes and FK relationship (NOT on the column that I need to change).
Number of records is around 36 million. I have never done something like
this before. Will there be any issues that I need to be aware off?If you are increasing the length of a varchar column using ALTER
TABLE...ALTER COLUMN, only meta-data changes are needed so the operation
should be very fast. This acquires a Sch-M lock though, so no other users
can use the table during the ALTER.
Hope this helps.
Dan Guzman
SQL Server MVP
"XXX" <sa@.nomail.com> wrote in message
news:uePmOBn2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>I have a table which gets it feed from external source. I need to chnge the
>length of the column on DBs which are in Prod. The table has couple of
>indexes and FK relationship (NOT on the column that I need to change).
>Number of records is around 36 million. I have never done something like
>this before. Will there be any issues that I need to be aware off?
>

Changing column from nvarchar to int

I am upgrading an Access database and need to convert some of the fields
from text to a foreign key from another table. This is no problem and I
already have the query batch to do this. I would like to know if it is
possible to change a column type from nvarchar to int.
Thanks,
Drew LaingAs long as all the NVARCHAR values can be converted to an INT, sure.
ALTER TABLE tablename ALTER COLUMN columnname INT
http://www.aspfaq.com/
(Reverse address to reply.)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>|||Drew,
You can do it if the values are numeric or null. If the value can not be
casted, then you will get an error.
Example:
use northwind
go
create table t (
colA varchar(15)
)
go
insert into t values('1')
insert into t values('2')
insert into t values(null)
go
select * from t
go
alter table t
alter column colA int
go
select * from t
go
alter table t
alter column colA varchar(15)
go
insert into t values ('a')
go
-- will fail
alter table t
alter column colA int
go
select * from t
go
drop table t
go
AMB
"Drew" wrote:

> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>
>|||Thanks to both!
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
>I am upgrading an Access database and need to convert some of the fields
>from text to a foreign key from another table. This is no problem and I
>already have the query batch to do this. I would like to know if it is
>possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>