Altering table & trans. replication

Hi,

How can I modify table with publication (change of one column length)
without completely breaking replication.

Thanks in advance"Wagner" <wagner@.email.t-com.hr> wrote in message
news:1x0k6ml5is0vs$.mmdq6nunj5l6$.dlg@.40tude.net.. .
> Hi,
> How can I modify table with publication (change of one column length)
> without completely breaking replication.

Besides the method you found, I've also done the following:

Create a NEW column of the type you want, call it foo_temp.

Copy data into it.

Then sp_repldropcolumn on the existing column.

Then sp_repladdcolumn with the same name, but new definition.

Copy data back.

> Thanks in advance

Altering linked tables

I have a table (in Access) that is linked to the SQL server, and I
need to add a column to it. I wrote the following:

ALTER TABLE Census
ADD COLUMN 'ActiveFacility' BIT;

and I get a syntax error that I do not know how to solve. The column
needs to contain yes/no data for each record.

Any help for a struggling newbie?
Thanks,

ChristineChristine (cbrandel@.rehabmanagement.com) writes:
> I have a table (in Access) that is linked to the SQL server, and I
> need to add a column to it. I wrote the following:
> ALTER TABLE Census
> ADD COLUMN 'ActiveFacility' BIT;
> and I get a syntax error that I do not know how to solve. The column
> needs to contain yes/no data for each record.
> Any help for a struggling newbie?

It is always wise to include the error message you get. In this case it
was very easy for me to reproduce the error, but sometimes this is
may be impossible with access to the database.

The best place to find out the syntax for a command, is to look it up
in Books Online, which comes with SQL Server. In the book T-SQL Syntax
Reference you find all T-SQL commands described with syntax diagrams.
Sure, the diagram for ALTER TABLE may seem unwieldy, since you can
alter a table in many ways. On the flip side, there are also examples
for the most common uses of the command.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Altering constraint in a column in a publication

Hi All,
I have a replicated (published) database with 1 push subscription to it on
another server. Replication type is Transactional with "pushing" occuring
every 2 hours.
1) In BOL it is mentioned, for making a change (besides adding or dropping a
column) in a published table -
a) delete all subscriptions to the publication
b) remove the article/object from the publication
c) change the table as required
d) add the article back to the publication
e) create the subscription again
My questions -
- Do I always need to go through the entire subscription creation process
each time I need to alter a table in my publication? I mean for a table
change that will take 2 minutes, I need to spend 3 hours (thats the time the
snapshot creation takes for me) creating the subscription?
- In step a), does it mean removing the subsribing database ? What about the
subcribing server ?
2) In BOL, its mentioned that to delete subscriptions through Enterprise
Manager, I need to go to SQL Server Group -> <Registration Name> ->
Replication -> Publications -> <Publication name>. In the right pane, I'll
see the subscription. I need to right click on it and say delete.
My questions -
- If I go to Tools -> Replication -> Configure Publishing, Subscribers and
Distributors, go to the Subscribers tab and remove the tick on the
subscriber, is it the same thing as deleting the subcription?
- Secondly, instead of going through the process of creating a new
subscription after altering my table, if I just go back to the above
mentioned location and tick the required subscriber, is it the same thing as
creating a subscription?
- If answers to both above questions is Yes, can I replace steps a) and e)
in question 1) with above two steps (unticking/ticking)?
Unrelated queries -
- If I disable publishing through option "Disable Publishing" in right click
of SQL Server Group -> <Registration Name> -> Replication, is there any
option to "enable" the publishing or do I need to go through the publication
creation process?
- How do I monitor the performance of replication?
Salil.
Salil,
some changes can be made using sp_addscriptexec; it depends on what you are
trying. If this isn't permitted, you can do a no-sync initialization if you
want to avoid the cost of the snapshot. If you go down this path, be sure
that the data is synchronized first.
Dropping the subscription doesn't require you to drop the subscriber's
database, and is distinct from enabling a subscriber.
Disabling publishing has to be followed by enabling publishing if you want
to use replication again.
You can monitor the performance using the replication-specific counters in
Performance Monitor. Also you can query the replication history tables for
short-term monitoring, and msdistribution_status in transactional
replication.
HTH,
Paul Ibison
|||Thank you Paul.
Point 1 (about no-sync initialization) and 4 (replication monitoring)
definitely helps.
But my query about "ticking/unticking" still remains unanswered.
i.e. If I do -
1) Untick subscribing server
2) remove article from publisher
3) alter article
4) add article back to publisher
5) tick subscribing server
Is 1 and 5 the same as "deleting" and "adding" subscirbers respectively?
Salil.
"Paul Ibison" wrote:

> Salil,
> some changes can be made using sp_addscriptexec; it depends on what you are
> trying. If this isn't permitted, you can do a no-sync initialization if you
> want to avoid the cost of the snapshot. If you go down this path, be sure
> that the data is synchronized first.
> Dropping the subscription doesn't require you to drop the subscriber's
> database, and is distinct from enabling a subscriber.
> Disabling publishing has to be followed by enabling publishing if you want
> to use replication again.
> You can monitor the performance using the replication-specific counters in
> Performance Monitor. Also you can query the replication history tables for
> short-term monitoring, and msdistribution_status in transactional
> replication.
> HTH,
> Paul Ibison
>
>
|||Salil,
unticking the subscriber in the distributor properties box will unsubscribe
all subscriptions to this server, not just the one you are concerned about.
Afterwards you'll need to recheck this box and subsequently resubscribe to
the publication.
HTH,
Paul Ibison
|||Thank you Paul.
"Paul Ibison" wrote:

> Salil,
> unticking the subscriber in the distributor properties box will unsubscribe
> all subscriptions to this server, not just the one you are concerned about.
> Afterwards you'll need to recheck this box and subsequently resubscribe to
> the publication.
> HTH,
> Paul Ibison
>
>

altering columns performance issues

Good Day:
When altering column width in SQL2000SP3a we find that this is taking a long
running time on tables with many rows.
We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
the data should not need to be transformed, only the column altered.
There are some 20 million rows and some 200 columns that need this
alteration.
Any thoughts on how to speed this up?
We could create new tables and copy the data, but that requires all user
activity to be stopped, and that is not palatable.
I note that Oracle can do this sort of an alter instantly, for some reason.
THANKS
Mr. Lynn Teska
Mayo ClinicHi Lynn
I just wrote an article on ALTER TABLE for SQL Server Magazine, and why some
changes take a long time and other don't. Unfortunately, it won't appear
until January, so I'll give you a sneak preview.
You are right that the data doesn't need to be transformed, but I'm not sure
what you mean by 'only the column altered'. Yes, the column needs to be
altered, and in every single row in the table. For some changes, like
changing to a smaller datatype, only the metadata needs to be changed (but
the existing data does need to be validated to make sure it 'fits' into the
smaller type.)
If you alter a column's datatype to one that needs more storage space, SQL
Server will actually make the physical change to every row to allow the
additional bytes that are needed to store numeric(8,3).
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a
long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some
reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||In addition to Kalen's response:
Watch out if you use EM for this. EM generally creates a new table, copy the data etc etc instead of
executing an ALTER TABLE tblname ALTER COLUMN colname command.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lynn Teska" <lteska@.mayo.edu> wrote in message news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||Good Day:
Well here is a hypothetical situation:
We have a 24x7x365 app with a particular table that has some 200
numeric(7,2) columns.
There are a number of other columns with constraints and indexes, but none
of the numeric (7,2) columns is indexed or constrained.
The applications store new data in the table every 1 minute, or thereabouts.
There are 25 Million rows in the table.
We need to widen all the numeric (7,2) columns to numeric (8,3).
We need to minimize or eliminate application outage.
SQL server is doing a huge amount of I/O to alter the columns.
SQL server appears to only be able to alter one column at a time.
Now when I add new columns SQL server is quick as you please.
What strategy would this group think best to accomplish the task of widening
the columns?
Thanks for any insight!
Lynn Teska
Mayo Clinic
--
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eyk5vqrnDHA.688@.TK2MSFTNGP10.phx.gbl...
> In addition to Kalen's response:
> Watch out if you use EM for this. EM generally creates a new table, copy
the data etc etc instead of
> executing an ALTER TABLE tblname ALTER COLUMN colname command.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > Good Day:
> >
> > When altering column width in SQL2000SP3a we find that this is taking a
long
> > running time on tables with many rows.
> > We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> > the data should not need to be transformed, only the column altered.
> > There are some 20 million rows and some 200 columns that need this
> > alteration.
> >
> > Any thoughts on how to speed this up?
> > We could create new tables and copy the data, but that requires all user
> > activity to be stopped, and that is not palatable.
> >
> > I note that Oracle can do this sort of an alter instantly, for some
reason.
> >
> > THANKS
> > Mr. Lynn Teska
> > Mayo Clinic
> >
> >
>|||Hi Lynn,
I would like to thank Kalen and Tibor for their help. As I understand, you
want to widen all the numeric (7,2) columns to numeric (8,3) in the table
on the machine. If I have misunderstood, please feel free to let me know.
To change the schema of the table, we can perform SQL statements using
Query Analyzer or Change the table schema directly in SQL Server Enterprise
Manager. Because Enterprise Manager will create a new tale and drop the
original table for changing the schema, I think it is better to use Query
Analyzer with ALTER TABLE statement.
Example:
alter table <table name> alter column <column name> numeric(8,3)
For additional information regarding ALTER TABLE, please refer to the
following article on SQL Server Books Online.
Topic: "ALTER TABLE"
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I understand how to do the task with the EM or TSQL.
The problem is the time it takes with 25 million rows is not acceptable in
at 24x7x365 system.
TSQL can only alter one column at a time, and with 200 columns to do that is
not so good.
EM makes copies as you note and that is not efficient in terms of disk i/o.
I am trying to get a strategy that will minimize or eliminate down time and
blocking of the table.
Interestingly Oracle can do this sort of alteration instantly, according to
our vendor.
Thanks
Lynn Teska
Mayo Clinic
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> Hi Lynn,
> I would like to thank Kalen and Tibor for their help. As I understand, you
> want to widen all the numeric (7,2) columns to numeric (8,3) in the table
> on the machine. If I have misunderstood, please feel free to let me know.
> To change the schema of the table, we can perform SQL statements using
> Query Analyzer or Change the table schema directly in SQL Server
Enterprise
> Manager. Because Enterprise Manager will create a new tale and drop the
> original table for changing the schema, I think it is better to use Query
> Analyzer with ALTER TABLE statement.
> Example:
> alter table <table name> alter column <column name> numeric(8,3)
> For additional information regarding ALTER TABLE, please refer to the
> following article on SQL Server Books Online.
> Topic: "ALTER TABLE"
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Lynn
The vendors says Oracle can do this instantly, but you've never tried it
yourself?
I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
instantly too, that's why you come to a Technical forum to find out. Have
you asked this on an Oracle Technical forum?
As I mentioned, there are some kinds of ALTER TABLEs that can be done
instantly, and there is a lot of confusion, so someone who had not
researched this issue in technical detail could easily tell you that this
was an instantaneous operation, even on SQL Server.
You are wanting to physically change 25 million rows? How could this
possible be an instantaneous operation?
One faster way you might consider is to create a new table using select
into, which is a very fast command, and then dropping the old table, and
renaming the new one to the old name.
SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
col2 ...
INTO newtable
FROM original_table
GO
--recreate constraints, indexes etc on newtable
DROP original_table
EXEC sp_rename newtable, original_table
Good Luck!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I understand how to do the task with the EM or TSQL.
> The problem is the time it takes with 25 million rows is not acceptable in
> at 24x7x365 system.
> TSQL can only alter one column at a time, and with 200 columns to do that
is
> not so good.
> EM makes copies as you note and that is not efficient in terms of disk
i/o.
> I am trying to get a strategy that will minimize or eliminate down time
and
> blocking of the table.
> Interestingly Oracle can do this sort of alteration instantly, according
to
> our vendor.
> Thanks
> Lynn Teska
> Mayo Clinic
>
> "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > Hi Lynn,
> >
> > I would like to thank Kalen and Tibor for their help. As I understand,
you
> > want to widen all the numeric (7,2) columns to numeric (8,3) in the
table
> > on the machine. If I have misunderstood, please feel free to let me
know.
> >
> > To change the schema of the table, we can perform SQL statements using
> > Query Analyzer or Change the table schema directly in SQL Server
> Enterprise
> > Manager. Because Enterprise Manager will create a new tale and drop the
> > original table for changing the schema, I think it is better to use
Query
> > Analyzer with ALTER TABLE statement.
> >
> > Example:
> >
> > alter table <table name> alter column <column name> numeric(8,3)
> >
> > For additional information regarding ALTER TABLE, please refer to the
> > following article on SQL Server Books Online.
> > Topic: "ALTER TABLE"
> >
> > Please feel free to post in the group if this solves your problem or if
> you
> > would like further assistance.
> >
> > Regards,
> >
> > Michael Shao
> > Microsoft Online Partner Support
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> >
>|||Thanks for the info.
The vended product runs on both SQL server and Oracle, I have no reason to
doubt that the change is fast on Oracle.
They seem to be experienced in both platforms.
Thanks for your suggestion I will have a look at it.
Lynn Teska
PS:
This is an expert of a note I received from an Oracle expert:
Oracle always reserves a minimum of space in every database block for
updates, determined by the PCTFREE parameter (10% of the block size by
default, which we don't modify). Hence, Oracle does not have to reserve any
extra space, it's already there. Initially the old rows will surely fit
inside the new definition, as we have just widened the columns. If some
value needs some extra space, it will use the PCTFREE space. If this is not
enough, a phenomenon called row chaining will take place, as part of the row
will have to be stored in another block, and this is not so good for
performance, but it can be attacked later on. In our case this would never
happen as we won't go updating old values.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||All the more interesting since numeric (7,2) and numeric (8,3) both appear
to be stored in 5 bytes.
Lynn
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||Yes, there does appear to be some unexpected behavior going on. I am still
researching this issue.
OTOH, I have just learned that although you are correct, that Oracle does
make this change and all ALTER TABLE changes instantly, as only a metadata
change, the tradeoff is that actual row size adjustment must be made during
actual data use. In fact, if you change the datatype size to something very
different, or smaller, like char(5) to char(3) or char to int, Oracle will
not do any validation at the time of the alter table, but will wait until
your read the row. So you can end up getting conversion errors just by
reading data, which can be very problematic.
So, like many choices, there is a tradeoff. Normally, since altering tables
is a rare occurance, but updating and modifying data is done during
production operations, I'd rather the overhead was accrued during the alter,
when I can plan for it.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> All the more interesting since numeric (7,2) and numeric (8,3) both appear
> to be stored in 5 bytes.
> Lynn
> --
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > Lynn
> >
> > The vendors says Oracle can do this instantly, but you've never tried it
> > yourself?
> >
> > I wouldn't be surprised if a SQL Server vendor said SQL Server could do
it
> > instantly too, that's why you come to a Technical forum to find out.
Have
> > you asked this on an Oracle Technical forum?
> >
> > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > instantly, and there is a lot of confusion, so someone who had not
> > researched this issue in technical detail could easily tell you that
this
> > was an instantaneous operation, even on SQL Server.
> >
> > You are wanting to physically change 25 million rows? How could this
> > possible be an instantaneous operation?
> >
> > One faster way you might consider is to create a new table using select
> > into, which is a very fast command, and then dropping the old table, and
> > renaming the new one to the old name.
> >
> > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
as
> > col2 ...
> > INTO newtable
> > FROM original_table
> > GO
> >
> > --recreate constraints, indexes etc on newtable
> >
> > DROP original_table
> >
> > EXEC sp_rename newtable, original_table
> >
> > Good Luck!
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > I understand how to do the task with the EM or TSQL.
> > > The problem is the time it takes with 25 million rows is not
acceptable
> in
> > > at 24x7x365 system.
> > >
> > > TSQL can only alter one column at a time, and with 200 columns to do
> that
> > is
> > > not so good.
> > > EM makes copies as you note and that is not efficient in terms of disk
> > i/o.
> > > I am trying to get a strategy that will minimize or eliminate down
time
> > and
> > > blocking of the table.
> > >
> > > Interestingly Oracle can do this sort of alteration instantly,
according
> > to
> > > our vendor.
> > >
> > > Thanks
> > >
> > > Lynn Teska
> > > Mayo Clinic
> > >
> > >
> > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > Hi Lynn,
> > > >
> > > > I would like to thank Kalen and Tibor for their help. As I
understand,
> > you
> > > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> > table
> > > > on the machine. If I have misunderstood, please feel free to let me
> > know.
> > > >
> > > > To change the schema of the table, we can perform SQL statements
using
> > > > Query Analyzer or Change the table schema directly in SQL Server
> > > Enterprise
> > > > Manager. Because Enterprise Manager will create a new tale and drop
> the
> > > > original table for changing the schema, I think it is better to use
> > Query
> > > > Analyzer with ALTER TABLE statement.
> > > >
> > > > Example:
> > > >
> > > > alter table <table name> alter column <column name> numeric(8,3)
> > > >
> > > > For additional information regarding ALTER TABLE, please refer to
the
> > > > following article on SQL Server Books Online.
> > > > Topic: "ALTER TABLE"
> > > >
> > > > Please feel free to post in the group if this solves your problem or
> if
> > > you
> > > > would like further assistance.
> > > >
> > > > Regards,
> > > >
> > > > Michael Shao
> > > > Microsoft Online Partner Support
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > >
> > >
> > >
> >
> >
>|||OK Thanks.
Let me know what you find.
A 4 hour outage is not a plus for us.
Thanks
Lynn Teska
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Jo3UejoDHA.3040@.TK2MSFTNGP11.phx.gbl...
> Yes, there does appear to be some unexpected behavior going on. I am still
> researching this issue.
> OTOH, I have just learned that although you are correct, that Oracle does
> make this change and all ALTER TABLE changes instantly, as only a metadata
> change, the tradeoff is that actual row size adjustment must be made
during
> actual data use. In fact, if you change the datatype size to something
very
> different, or smaller, like char(5) to char(3) or char to int, Oracle will
> not do any validation at the time of the alter table, but will wait until
> your read the row. So you can end up getting conversion errors just by
> reading data, which can be very problematic.
> So, like many choices, there is a tradeoff. Normally, since altering
tables
> is a rare occurance, but updating and modifying data is done during
> production operations, I'd rather the overhead was accrued during the
alter,
> when I can plan for it.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > All the more interesting since numeric (7,2) and numeric (8,3) both
appear
> > to be stored in 5 bytes.
> >
> > Lynn
> >
> > --
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > > Lynn
> > >
> > > The vendors says Oracle can do this instantly, but you've never tried
it
> > > yourself?
> > >
> > > I wouldn't be surprised if a SQL Server vendor said SQL Server could
do
> it
> > > instantly too, that's why you come to a Technical forum to find out.
> Have
> > > you asked this on an Oracle Technical forum?
> > >
> > > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > > instantly, and there is a lot of confusion, so someone who had not
> > > researched this issue in technical detail could easily tell you that
> this
> > > was an instantaneous operation, even on SQL Server.
> > >
> > > You are wanting to physically change 25 million rows? How could this
> > > possible be an instantaneous operation?
> > >
> > > One faster way you might consider is to create a new table using
select
> > > into, which is a very fast command, and then dropping the old table,
and
> > > renaming the new one to the old name.
> > >
> > > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
> as
> > > col2 ...
> > > INTO newtable
> > > FROM original_table
> > > GO
> > >
> > > --recreate constraints, indexes etc on newtable
> > >
> > > DROP original_table
> > >
> > > EXEC sp_rename newtable, original_table
> > >
> > > Good Luck!
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > I understand how to do the task with the EM or TSQL.
> > > > The problem is the time it takes with 25 million rows is not
> acceptable
> > in
> > > > at 24x7x365 system.
> > > >
> > > > TSQL can only alter one column at a time, and with 200 columns to do
> > that
> > > is
> > > > not so good.
> > > > EM makes copies as you note and that is not efficient in terms of
disk
> > > i/o.
> > > > I am trying to get a strategy that will minimize or eliminate down
> time
> > > and
> > > > blocking of the table.
> > > >
> > > > Interestingly Oracle can do this sort of alteration instantly,
> according
> > > to
> > > > our vendor.
> > > >
> > > > Thanks
> > > >
> > > > Lynn Teska
> > > > Mayo Clinic
> > > >
> > > >
> > > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in
message
> > > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > > Hi Lynn,
> > > > >
> > > > > I would like to thank Kalen and Tibor for their help. As I
> understand,
> > > you
> > > > > want to widen all the numeric (7,2) columns to numeric (8,3) in
the
> > > table
> > > > > on the machine. If I have misunderstood, please feel free to let
me
> > > know.
> > > > >
> > > > > To change the schema of the table, we can perform SQL statements
> using
> > > > > Query Analyzer or Change the table schema directly in SQL Server
> > > > Enterprise
> > > > > Manager. Because Enterprise Manager will create a new tale and
drop
> > the
> > > > > original table for changing the schema, I think it is better to
use
> > > Query
> > > > > Analyzer with ALTER TABLE statement.
> > > > >
> > > > > Example:
> > > > >
> > > > > alter table <table name> alter column <column name> numeric(8,3)
> > > > >
> > > > > For additional information regarding ALTER TABLE, please refer to
> the
> > > > > following article on SQL Server Books Online.
> > > > > Topic: "ALTER TABLE"
> > > > >
> > > > > Please feel free to post in the group if this solves your problem
or
> > if
> > > > you
> > > > > would like further assistance.
> > > > >
> > > > > Regards,
> > > > >
> > > > > Michael Shao
> > > > > Microsoft Online Partner Support
> > > > > Get Secure! - www.microsoft.com/security
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Altering column with index

Hello there
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?You'll need to drop the indexes/constraints and recreate after changing the
collation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||Drop the indexes (and any constraints) first, then alter the column and
restore the indexes (and constraints).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
Hello there
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?|||Whell Tom
It seems to be very agly to do that.
When i do it with the enterprise Manager i don't do all this stuff
Are you sure i need to do all of that?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Drop the indexes (and any constraints) first, then alter the column and
> restore the indexes (and constraints).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||
Roy Goldhammer wrote:

>Whell Tom
>It seems to be very agly to do that.
>When i do it with the enterprise Manager i don't do all this stuff
>
Enterprise Manager probably does it for you behind the scenes. While
EM doesn't do everything in the best way, you could generate the change
script from EM and use it as a template for creating your own script. If
an index exists on a varchar column, and then the collation is changed,
the index must be rebuilt. There's no way around it, since a change in
collation may change the ordering of the values, which is what the
index is there to implement.
Steve Kass
Drew University

>Are you sure i need to do all of that?
>"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
>
>

Altering column names?

I have been looking in the SQL Server Help Files, but I cant seem to find the syntax to change the name of a column name..?
Can it be done? if so, what is the syntax?well, i guess you could do this:alter table foo
add column barnew datatype etc.

update table foo
set barnew = bar

update table foo
drop column bar

but why? i mean, you can change the name in any select statement:select bar as barnew
from foo|||I want to be able to change the names of columns because I am building an interface to an SQL Database.

That code you gave me doesnt work, you dont use the word 'column' when adding a new column, you just use 'add' alone.|||sorry, i do not always test out the syntax i suggest

but at least i got across the idea of the approach to use

you're welcome|||Well .. i think sp_rename works on columnt too ... need to check that out ..

create table myTab (mycol int)
go
sp_rename 'mytab.mycol' ,'mytab.mycol1'
go
select mycol1 from myTab
go

Seems to worksql

Altering column in a large table

Hi,
I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?
Thanks in advance
ManuThis will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.
I would use ALTER TABLE ... ALTER COLUMN.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"manu" wrote:

> Hi,
> I need to alter one column which is a part of a table with billions of row
s
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> Hi,
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||The change is just to alter the not null property of a column in this huge
table to NULL.
Thanks
Manu
"TheSQLGuru" wrote:

> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
>
>|||I'm pretty certain it is a meta-data only change (provided you use ALTER TAB
LE and not the GUI
tool). I suggest you create a table with some million rows and test, just to
be certain...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3C68C5CC-0532-4035-9D56-7599FBF9C53C@.microsoft.com...[vbcol=seagreen]
> The change is just to alter the not null property of a column in this huge
> table to NULL.
> Thanks
> Manu
> "TheSQLGuru" wrote:
>

Altering column in a large table

Hi,
I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?
Thanks in advance
Manu
This will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.
I would use ALTER TABLE ... ALTER COLUMN.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"manu" wrote:

> Hi,
> I need to alter one column which is a part of a table with billions of rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu
|||What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> Hi,
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu
|||The change is just to alter the not null property of a column in this huge
table to NULL.
Thanks
Manu
"TheSQLGuru" wrote:

> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
>
>

Altering column in a large table

Hi,
I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?
Thanks in advance
ManuThis will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.
I would use ALTER TABLE ... ALTER COLUMN.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"manu" wrote:
> Hi,
> I need to alter one column which is a part of a table with billions of rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> Hi,
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||The change is just to alter the not null property of a column in this huge
table to NULL.
Thanks
Manu
"TheSQLGuru" wrote:
> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> > Hi,
> >
> > I need to alter one column which is a part of a table with billions of
> > rows
> > in it. What would be the finest and fastest approach to do it?
> >
> > Thanks in advance
> > Manu
>
>|||I'm pretty certain it is a meta-data only change (provided you use ALTER TABLE and not the GUI
tool). I suggest you create a table with some million rows and test, just to be certain...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3C68C5CC-0532-4035-9D56-7599FBF9C53C@.microsoft.com...
> The change is just to alter the not null property of a column in this huge
> table to NULL.
> Thanks
> Manu
> "TheSQLGuru" wrote:
>> What, exactly, is the alteration? Some changes could be done more
>> efficiently by bulk exporting data, dropping all structures on table,
>> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
>> Others are nothing more than a meta-data change.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
>> > Hi,
>> >
>> > I need to alter one column which is a part of a table with billions of
>> > rows
>> > in it. What would be the finest and fastest approach to do it?
>> >
>> > Thanks in advance
>> > Manu
>>

Altering column fields with a Stored Procedure

I have some columns of data in SQL server that are of NVARCHAR(420)
format but they are dates. The dates are in DD/MM/YY format. I want to
be able to convert them to our accounting system format which is
YYYYMMDD. I know the format is strange but it will make things easier
in the long run if all of the dates are the same when working between
the 2 different databases. Basically, I need to take a look at the
year portion (with a SUBSTRING function maybe) to see if it is greater
than 50 (there will not be any dates that are less than 1950) and if
it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
month and day from the rest to form the date we need in NUMERIC(8).
So, a date of January 17, 2003 (currently in the format of 17/01/03)
would become 20030117. In VB, the function I would write is something
like the following:
/*
Dim sCurrentDate as String
Dim sMon as string
Dim sDay as String
Dim sYear as String
Dim sNewDate as String

sCurrentDate = "17/01/03"
sMon = Mid(sCurrentDate, 4, 2)
sDay = Mid(sCurrentDate, 1, 2)
sYear = Mid(sCurrentDate, 7, 2)

If sYear < 50 Then
sYear = "20" & sYear
ElseIf sYear > 50 Then
sYear = "19" & sYear
End if
sNewDate = sYear & sMon & sDay
*/
I was thinking of doing this in a Stored Procedure but am really rusty
with SQL (it's been since college).

The datatype would end up being NUMERIC(8). How I would write it if I
new how to write it would be: grab the column name prior to the
procedure, create a temp column, format the values, place them into
the temp column, delete the old column, and then rename the temp
column to the name of the column that I grabbed in the beginning of
the procedure. Most likely this is the only way to do it but I have no
idea how to go about it.mwoodward@.quinnpumps.com (Milo Woodward) wrote in message news:<1615a5e3.0308142112.30d6548c@.posting.google.com>...
> I have some columns of data in SQL server that are of NVARCHAR(420)
> format but they are dates. The dates are in DD/MM/YY format. I want to
> be able to convert them to our accounting system format which is
> YYYYMMDD. I know the format is strange but it will make things easier
> in the long run if all of the dates are the same when working between
> the 2 different databases. Basically, I need to take a look at the
> year portion (with a SUBSTRING function maybe) to see if it is greater
> than 50 (there will not be any dates that are less than 1950) and if
> it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
> month and day from the rest to form the date we need in NUMERIC(8).
> So, a date of January 17, 2003 (currently in the format of 17/01/03)
> would become 20030117. In VB, the function I would write is something
> like the following:
> /*
> Dim sCurrentDate as String
> Dim sMon as string
> Dim sDay as String
> Dim sYear as String
> Dim sNewDate as String
> sCurrentDate = "17/01/03"
> sMon = Mid(sCurrentDate, 4, 2)
> sDay = Mid(sCurrentDate, 1, 2)
> sYear = Mid(sCurrentDate, 7, 2)
> If sYear < 50 Then
> sYear = "20" & sYear
> ElseIf sYear > 50 Then
> sYear = "19" & sYear
> End if
> sNewDate = sYear & sMon & sDay
> */
> I was thinking of doing this in a Stored Procedure but am really rusty
> with SQL (it's been since college).
> The datatype would end up being NUMERIC(8). How I would write it if I
> new how to write it would be: grab the column name prior to the
> procedure, create a temp column, format the values, place them into
> the temp column, delete the old column, and then rename the temp
> column to the name of the column that I grabbed in the beginning of
> the procedure. Most likely this is the only way to do it but I have no
> idea how to go about it.

I strongly suggest that you rethink your approach, and change the
column to datetime. You can then do date calculations using the
standard functions (DATEADD etc.), compare the values to datetime
variables without conversion, etc. You can use CONVERT() to extract
dates in a particular format for passing to other systems.

Using numeric will give you serious problems in the long run, although
I appreciate that you may have limited control over the data model.
But if you really have no option but to use numeric, then this should
work (assuming that as you said, all dates are 1950 or later):

update dbo.MyTable
set DateColumn = convert(char(8), convert(datetime, DateColumn, 3),
112)

alter table dbo.MyTable
alter column DateColumn numeric(8)

Simon

Altering a Published Table

Hi! I've added a column to a published table in the Publisher using SP_REPLADDCOLUMN. After doing this the replication triggers for that table(i.e. del_%,upd_%,ins_%) are doubled both at the Publisher and at the Subscriber. If i update anyone of the column in the table at the Subscriber; i get the following error:

Server: Msg 208, Level 16, State 1, Procedure upd_3E6DE124B82D42A5AEB169557C0D757C, Line 60
Invalid object name 'ctsv_3E6DE124B82D42A5AEB169557C0D757C'.

Any ideas, what has gone wrong??

I have the following SQL setup.
Publisher: Enterprise Edition, SP3.
Subscriber: Standard-Edition, SP3.
The error is at the Subscriber.Did you set both @.force_invalidate_snapshot and @.force_reinit_subscription to 1. My guess is not. You have to reinitialize all subscriptions now to resolve the problem.|||Originally posted by joejcheng
Did you set both @.force_invalidate_snapshot and @.force_reinit_subscription to 1. My guess is not. You have to reinitialize all subscriptions now to resolve the problem.

No i didn't set these variables to 1, for i can't afford a full SNAPSHOT over the internet. The PUBLISHER is of 3 GB in size. What steps i can take so that it doesn't happen again.!!!?
I've temporarily fixed the problem by removing the newly created replication trigger manually at both the PUBLISHER and SUBSCRIBER.|||When you add a columns that is not PK, and you dont wish to reinitiliase during operation hours, you can choose this way, try to create a column by using Publisher properties, go to Filter columns in Publisher properties, then add the column using the Add column button, and then dont tick on that column you just added. Now you didnt ask merge agent to replicate that column for you. What you doing is to add a column for your user to make use of that column in the publisher database server. Wait till you have time to replicate the whole snapshot off the operation hours. Good Luck !

Originally posted by TALAT
No i didn't set these variables to 1, for i can't afford a full SNAPSHOT over the internet. The PUBLISHER is of 3 GB in size. What steps i can take so that it doesn't happen again.!!!?
I've temporarily fixed the problem by removing the newly created replication trigger manually at both the PUBLISHER and SUBSCRIBER.|||Thanx for your help eshl. I use to apply the SNAPSHOT only when there's a change in the PK or an ID seed column of a published table. The publication-database has 24 hours production and the subscribers have online websites running on them. I am eagerly lookin for a way to completely bypass the SNAPSHOT.|||http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20800513.html

Altering a column which has an index defined on it

Hello,

I'm trying the following test (which works like a charm on Oracle)

create table x1(c1 numeric(10), c2 numeric(5,1))
create index x1_c2_idx on x1(c2)
alter table x1 alter column c2 numeric(9,1)

I get the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'x1_c2_idx' is dependent on column 'c2'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN c2 failed because one or more objects access this column.

Is there a way to alter the column WITHOUT dropping the index ?



Regards,

Tal Olier
otal@.mercury.co.ilNo, you must drop the index first.|||Originally posted by Paul Young
No, you must drop the index first.

Thanks.

Altering a column on a replicated table

Tom,
nice that someone read it
If you're running these commands as a script, you'll need
a GO after each command, otherwise you can just run them
individually one-by-one.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
OK, that worked and I saw the queue reader agent process the commands and
the distribution agent's last action taken now reads "The initial snapshot
for article 'RequistionDetail' is not yet available'.
Now what do I need to do to generate a new snapshot of this one article in
order to get these changes to propogate over to the my subscriber?
Thanks-Tom
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:0f4b01c51511$e4181ce0$a401280a@.phx.gbl...
> Tom,
> nice that someone read it
> If you're running these commands as a script, you'll need
> a GO after each command, otherwise you can just run them
> individually one-by-one.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Tom,
why are you using , @.force_reinit_subscription = 1?
Ordinarily this is left out and there is no invalidation
of the snapshot - the changes are propagated as a result
of the sp_repl... commands using hte existing replication
framework. If you want to snapshot the table then have a
look at the other method in the article where you drop
the subscription to the article then drop the article.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Altering a column on a NoSync Replicated Table

Hi,
I saw an article on www.replicationanswers.com, about Altering a column on a
Replicated Table (from Paul Ibison):
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
I DID THE SAME FOR NO-SYNC INITITIALIZATION, BUT THE STRUCTURE OF THE FIELD
IN SUBCRIBER IS THE SAME, THIS CHANGE IS NOT BEING PUBLISHED IN THE
SUBSCRIBER.
CAN SOMEBODY HELP ME TO SOLVE THIS ISSUE FOR NO-SYNC INITIALIZATION, HOW THE
SCRIPT SHOULD LOOK LIKE OR WHAT DO IA HEVE TO DO?
Thanks,
BaniSQL.
Isn't @.sync_type = automatic the default one, I mean if you don't specify it
will take the automatic one. I tried like that, but than some foreign keys
referencing that table were firing.
I'm trying to alter a column, is not another way of doing it beside this one
droping the whole article and replicating again. This soltion is fine for me
as logn is it will work, but the problem is that the foeign keys are firing
and some related records to other related tables can't find.
Please help.
Thnx,
BaniSQL
"Paul Ibison" wrote:

> It all depends on whether you want to do an automatic one or another nosync
> one. The easiest way is to drop the article and subscription then readd it
> as @.sync_type = automatic.
> Cheers,
> Paul Ibison
>
>

altering a column of a table

Hello,
I have an internet site, that supports ms-sql server 2000.
The hosting company for some reason have problems on there wizard of
creating columns on db,
and doesn't support the auto-increment.
How can I do alter to a column, with an sql command, to an auto-increment
one ?
Need sample code please.
Thanks
I suggest that ALL changes on a live system should be made through SQL
scripts rather than using Enterprise Manager / Wizards. That way you can
more reliably reproduce and test your installation process.
IDENTITY is the correct name for the "auto-incrementing" column property in
SQL Server.
You can ADD an IDENTITY column to a table using an ALTER TABLE statement:
ALTER TABLE YourTable ADD col INTEGER IDENTITY
You cannot add the IDENTITY property to an existing column. Enterprise
Manager achieves this by creating a new table with IDENTITY, repopulating it
with the old data and then dropping the old table. That's something you may
want to avoid doing on a production system. If you do want to use that
approach then use Enterprise Manager to change the column on a development
copy of your data and select the Save Change Script option to save the
commands to a file. That way you can see exactly what the steps are.
David Portas
SQL Server MVP
|||Hi Eitan,
you cannot alter columns to auto-increment. You can only add columns that
auto-increment.
try dropping the column and re-creating it. hopefully, you dont need the
existing values in that column.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Eitan" <no_spam_please@.nospam_please.com> wrote in message
news:#KVZ#BX8EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have an internet site, that supports ms-sql server 2000.
> The hosting company for some reason have problems on there wizard of
> creating columns on db,
> and doesn't support the auto-increment.
> How can I do alter to a column, with an sql command, to an auto-increment
> one ?
> Need sample code please.
> Thanks
>
sql

altering a column of a table

Hello,
I have an internet site, that supports ms-sql server 2000.
The hosting company for some reason have problems on there wizard of
creating columns on db,
and doesn't support the auto-increment.
How can I do alter to a column, with an sql command, to an auto-increment
one ?
Need sample code please.
Thanks I suggest that ALL changes on a live system should be made through SQL
scripts rather than using Enterprise Manager / Wizards. That way you can
more reliably reproduce and test your installation process.
IDENTITY is the correct name for the "auto-incrementing" column property in
SQL Server.
You can ADD an IDENTITY column to a table using an ALTER TABLE statement:
ALTER TABLE YourTable ADD col INTEGER IDENTITY
You cannot add the IDENTITY property to an existing column. Enterprise
Manager achieves this by creating a new table with IDENTITY, repopulating it
with the old data and then dropping the old table. That's something you may
want to avoid doing on a production system. If you do want to use that
approach then use Enterprise Manager to change the column on a development
copy of your data and select the Save Change Script option to save the
commands to a file. That way you can see exactly what the steps are.
David Portas
SQL Server MVP
--|||Hi Eitan,
you cannot alter columns to auto-increment. You can only add columns that
auto-increment.
try dropping the column and re-creating it. hopefully, you dont need the
existing values in that column.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Eitan" <no_spam_please@.nospam_please.com> wrote in message
news:#KVZ#BX8EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have an internet site, that supports ms-sql server 2000.
> The hosting company for some reason have problems on there wizard of
> creating columns on db,
> and doesn't support the auto-increment.
> How can I do alter to a column, with an sql command, to an auto-increment
> one ?
> Need sample code please.
> Thanks
>

altering a column of a published table (trans repl)

Hi,
In BOL there is reference only to sp_repladdcolumn and sp_repldropcolumn
(add and drop), but nothing about altering a column, or have I missed it?
I have to change the collation of a column (that belongs to a table that is
transactionally replicated ) from sensitive to insensitive. Is the only way
to do that is to:
1 - add a New column with the correct collation (sp_repladdcolumn )
2 - copy the data from the old column into the New column (update statement)
3 - drop the old column (sp_repldropcolumn)
Any better way? or have I missed anything?
Thanks
You didn't miss anything - that's a problem we all have faced at some time
or other . Actually there's another level of iteration you missed out, as
your table will be missing the column with the oldname, so if this is to be
maintained you have to do the whole process again. There is an alternative
of dropping the subscriptions to the table, removing the table from the
publication, altering the table then readding to the publication then adding
subscriptions to this table. In this way you can effectively reinitialize on
a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
Table statement will itself be sufficient for most things.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
right, since add comes before drop, I would need 2 pairs of (add, drop ) ;
the first to alter the collation, the second to alter the column name (to
set it back to the original name) - correct?
and probably to have these 4 sp_Replxxx bracketed by Begin Tran - Commit
Tran.
is the alternative you indicated better in some ways (safer, faster, ...) ?
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#CM4dxyyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> You didn't miss anything - that's a problem we all have faced at some time
> or other . Actually there's another level of iteration you missed out,
as
> your table will be missing the column with the oldname, so if this is to
be
> maintained you have to do the whole process again. There is an alternative
> of dropping the subscriptions to the table, removing the table from the
> publication, altering the table then readding to the publication then
adding
> subscriptions to this table. In this way you can effectively reinitialize
on
> a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
> Table statement will itself be sufficient for most things.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It's potentially less processing time - largely depends on the 'width' of
your table, ie for a table not especially wide then I'd do the drop method.
if I had 200 columns, I'd do the column technique.
Cheers,
Paul Ibison
|||Thank you very much !
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#A2HWS1yFHA.464@.TK2MSFTNGP15.phx.gbl...
> It's potentially less processing time - largely depends on the 'width' of
> your table, ie for a table not especially wide then I'd do the drop
method.
> if I had 200 columns, I'd do the column technique.
> Cheers,
> Paul Ibison
>
|||Hi Paul,
Now I am in troubleshooting mode
I dropped the subscriptions to the table to alter, dropped the article (tale
from the publication), altered the table, added the article back to the
publication, and added the subscriptions to the publications. The schema
changes to the table became effective and were replicated to the destination
table (in the subscription), but the snapshot agent failed when I ran it.
The error is: The process could not create file....
I searched on MS and found a couple items (285997, 821480), but I am not
sure.
Any ideas?
The details:
I have 3 publications, each has few articles. There is one subscriber, and
only one subscription to all publications.
I did the following,
EXEC sp_dropsubscription @.publication = 'Pub_2'
, @.article = 'Orders_2'
, @.subscriber = 'SubscriberServer'
, @.destination_db = 'Dest_DB'
EXEC sp_droparticle @.publication = 'Pub_2'
, @.article = 'Orders_2'
ALTER TABLE Orders_2 ALTER COLUMN ....
EXEC sp_addarticle @.publication = 'Pub_2'
, @.article = 'Orders_2'
, @.source_table = 'Orders_2'
, @.destination_table = 'Orders_2'
, @.force_invalidate_snapshot = 1
-- the next is from scripting out the publication (prior to making the
changes)
EXEC sp_addsubscription @.publication = N'Pub_2'
, @.article = N'all'
, @.subscriber = N'SubscriberServer'
, @.destination_db = N'Dest_DB'
, @.sync_type = N'automatic'
, @.update_mode = N'read only'
, @.offloadagent = 0
, @.dts_package_location = N'distributor'
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#CM4dxyyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> You didn't miss anything - that's a problem we all have faced at some time
> or other . Actually there's another level of iteration you missed out,
as
> your table will be missing the column with the oldname, so if this is to
be
> maintained you have to do the whole process again. There is an alternative
> of dropping the subscriptions to the table, removing the table from the
> publication, altering the table then readding to the publication then
adding
> subscriptions to this table. In this way you can effectively reinitialize
on
> a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
> Table statement will itself be sufficient for most things.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Ramadan,
please check to see if there is an automatic virus-scanner set up. If so,
disable scanning of the repldata folder.
Also, check that there is space in the distribution working folder to create
the file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
it was as simple as a missing folder could be.
Thank you very much for your help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oa2$isDzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Ramadan,
> please check to see if there is an automatic virus-scanner set up. If so,
> disable scanning of the repldata folder.
> Also, check that there is space in the distribution working folder to
create
> the file.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

ALTER'ing an XML Column

I was wondering, is it possible to ALTER and existing XML column? I need the abiltiy to be able to type the column to a different xsd if the need arose. For example, the column was created and typed to an XSD called UserClaimsXSD. Then later on, the XSD itself changes and needs to be typed to the column.

I was looking through ALTER table but couldn't find anything. I ran SQL PRofiler and saw it is handled via SSMS - it builds a new table, with that new XSD and then copies the adta from the original table into the temp table. It then drops the original table and renames the temp table...

There has to be an easier way...

Thanks!!

You can first alter the column to untyped xml:

ALTER TABLE YourTable ALTER COLUMN xmlColumn xml NOT NULL;

Then modify or create the new schema

Lastly, change the column to the new schema (the data in the table must comply with the new schema)

ALTER TABLE YourTable ALTER COLUMN xmlColumn xml(NewSchemaCollection) NOT NULL;

ALTER'ing an XML Column

I was wondering, is it possible to ALTER and existing XML column? I need the abiltiy to be able to type the column to a different xsd if the need arose. For example, the column was created and typed to an XSD called UserClaimsXSD. Then later on, the XSD itself changes and needs to be typed to the column.

I was looking through ALTER table but couldn't find anything. I ran SQL PRofiler and saw it is handled via SSMS - it builds a new table, with that new XSD and then copies the adta from the original table into the temp table. It then drops the original table and renames the temp table...

There has to be an easier way...

Thanks!!

You can first alter the column to untyped xml:

ALTER TABLE YourTable ALTER COLUMN xmlColumn xml NOT NULL;

Then modify or create the new schema

Lastly, change the column to the new schema (the data in the table must comply with the new schema)

ALTER TABLE YourTable ALTER COLUMN xmlColumn xml(NewSchemaCollection) NOT NULL;

Alter View Hangs - Merge Replication SQL 2005

Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.

Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.

Here is what I am doing:

Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,

table2.txt_sort_order

FROM table1 join table2 on table1.status_code = table2.status_code

The query I am executing:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW myview

AS

Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc

FROM table1 join table2 on table1.status_code = table2.status_code

If this view is the only article in the publication, then it is a known issue.

Add a dummy table to the publication and your alter should succeed.