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 table with merge replication

Hello,
We are using sql2000.
1.Is there a better way to change field data type with
merge replication than add column, copy data and then drop
column?
2.How can i create index with merge replication?
3.If the only way is stop and restart the replication,
what is the easiest way to do so?
Many Many Thanks For Reply.>
> We are using sql2000.
> 1.Is there a better way to change field data type with
> merge replication than add column, copy data and then drop
> column?
--
No, there isn't. You use sp_repladdcolumn to add the new column and then
sp_repldropcolumn to remove the old column. Note that ALTER TABLE and ALTER
COLUMN does this transparently.
> 2.How can i create index with merge replication?
>
--
You use the @.schema_option argument of the sp_repladdcolumn to generate a
corresponding index. A value of 0x010 will generate a clustered index and
0x40 will generate a nonclustered index.
> 3.If the only way is stop and restart the replication,
> what is the easiest way to do so?
--
You don't need to break merge replication to perform make schema changes in
SQL Server 2000.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

alter table problem?

in a stored procedure , i've the following coding
...
create table tbl (
a int, b int
)
insert into tbl values (1, 2)
alter table tbl drop column b
select * from tbl
...
sql server returns "Column name or number of supplied values does not match
table definition."
Could anyone help me please.the table is temp table
"Win" <aaa@.aaa.com> wrote in message
news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not
match
> table definition."
> Could anyone help me please.
>|||Win
create table #tbl (
a int, b int
)
insert into #tbl values (1, 2)
GO
alter table #tbl drop column b
GO
select * from #tbl
drop table #tbl
"Win" <aaa@.aaa.com> wrote in message
news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
> the table is temp table
> "Win" <aaa@.aaa.com> wrote in message
> news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> match
>|||Cannot alter table '#tbl' because this table does not exist in database
'abc'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
> Win
> create table #tbl (
> a int, b int
> )
> insert into #tbl values (1, 2)
> GO
> alter table #tbl drop column b
> GO
> select * from #tbl
> drop table #tbl
>
> "Win" <aaa@.aaa.com> wrote in message
> news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
>|||Win
On my machine it works file. What version are you using?
"Win" <aaa@.aaa.com> wrote in message
news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
> Cannot alter table '#tbl' because this table does not exist in database
> 'abc'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
>|||The procedure is parsed as a unit, before any execution has taken place. So,
when the SELECT is
parsed, the ALTER hasn't occurred yet, hence the error message. This is expe
cted. If you post the
logic behind doing this, someone might provide an alternative, or you can us
e dynamic SQL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Win" <aaa@.aaa.com> wrote in message news:%23XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...darkred">
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not matc
h
> table definition."
> Could anyone help me please.
>|||my coding is inside a stored procedure
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uToyZl2OGHA.428@.tk2msftngp13.phx.gbl...
> Win
> On my machine it works file. What version are you using?
>
> "Win" <aaa@.aaa.com> wrote in message
> news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
not
>

ALTER TABLE MyT ALTER COLUMN IdtyCol t_idty NOT NULL Identity

Hello World,
Is there a way not to drop and create the table with temp table to alter a
column as in the subject?
Thanks,
C TO
> Is there a way not to drop and create the table with temp table to alter a
> column as in the subject?
Ehm, as far as I know, the "subject" should work just fine.
Is there an error that you're getting? If so, what is it?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com|||YOu have to recreate the column on order to create a identity column:
http://www.windowsitpro.com/Article...2080/22080.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"C TO" <CTO@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2193C5FD-CC98-417A-90EC-751F71128B19@.microsoft.com...
> Hello World,
> Is there a way not to drop and create the table with temp table to alter a
> column as in the subject?
> Thanks,
> C TO|||
a
> Ehm, as far as I know, the "subject" should work just fine.
> Is there an error that you're getting? If so, what is it?
Woops, mixed this up with "not null".
Bugger.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

alter table drop column and dbcc cleantable

All,
I just want to make sure my understanding is correct:
1) alter table drop column does not release space of varchar columns and
other columns of variable length.
2) dbcc cleantable frees this space
Open questions:
3) What about the space used by fixed size columns? Is it reclaimed
during drop (timing lets me suspect that it's not)? dbcc cleantable "does
not reclaim space after a fixed length column is dropped."
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
Are rows inserted after a DROP COLUMN smaller?
Thanks a lot!
Kind regards
robert
Robert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert
|||Well, its actually two questions.
1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does the
ALTER TABLE ... DROP COLUMN statement. Correct.
2. Are the rows inserted after the ALTER TABLE statement has been issued?
Yes, new rows will only occupy the storage required for the new data
definition contingent on the values of PAD_INDEX and FILLFACTOR for the
Clustered Index.
Now, the next logical question would be: how to I reclaim the space after
issue the ALTER TABLE ... DROP COLUMN statement with a fixed-length column?
Rebuild the Clustered Index.
Sincerely,
Anthony Thomas

"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
Robert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert
|||Anthony Thomas wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Robert Klemme wrote:
>
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
> No one wants to answer this one? Come on... I can even offer a
> virtual hug. :-)

> Well, its actually two questions.
> 1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does
> the ALTER TABLE ... DROP COLUMN statement. Correct.
Ok.

> 2. Are the rows inserted after the ALTER TABLE statement has been
> issued? Yes, new rows will only occupy the storage required for the
> new data definition contingent on the values of PAD_INDEX and
> FILLFACTOR for the Clustered Index.
Ok.

> Now, the next logical question would be: how to I reclaim the space
> after issue the ALTER TABLE ... DROP COLUMN statement with a
> fixed-length column?
> Rebuild the Clustered Index.
Ok, so basically the space is not reclaimed until either the complete
table is rebuild or old records are deleted.
Thanks a lot! You get the virtual hug: *hug*
:-)
Kind regards
robert

alter table drop column and dbcc cleantable

All,
I just want to make sure my understanding is correct:
1) alter table drop column does not release space of varchar columns and
other columns of variable length.
2) dbcc cleantable frees this space
Open questions:
3) What about the space used by fixed size columns? Is it reclaimed
during drop (timing lets me suspect that it's not)? dbcc cleantable "does
not reclaim space after a fixed length column is dropped."
bah.asp" target="_blank">http://msdn.microsoft.com/library/d...
bah.asp
Are rows inserted after a DROP COLUMN smaller?
Thanks a lot!
Kind regards
robertRobert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/d...s_dbcc_4bah.asp[vb
col=seagreen]
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert[/vbcol]
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert|||Well, its actually two questions.
1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does the
ALTER TABLE ... DROP COLUMN statement. Correct.
2. Are the rows inserted after the ALTER TABLE statement has been issued?
Yes, new rows will only occupy the storage required for the new data
definition contingent on the values of PAD_INDEX and FILLFACTOR for the
Clustered Index.
Now, the next logical question would be: how to I reclaim the space after
issue the ALTER TABLE ... DROP COLUMN statement with a fixed-length column?
Rebuild the Clustered Index.
Sincerely,
Anthony Thomas
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
Robert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/d...s_dbcc_4bah.asp[vb
col=seagreen]
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert[/vbcol]
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert|||Anthony Thomas wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Robert Klemme wrote:
>
http://msdn.microsoft.com/library/d...s_dbcc_4bah.asp[vb
col=seagreen]
> No one wants to answer this one? Come on... I can even offer a
> virtual hug. :-)[/vbcol]

> Well, its actually two questions.
> 1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does
> the ALTER TABLE ... DROP COLUMN statement. Correct.
Ok.

> 2. Are the rows inserted after the ALTER TABLE statement has been
> issued? Yes, new rows will only occupy the storage required for the
> new data definition contingent on the values of PAD_INDEX and
> FILLFACTOR for the Clustered Index.
Ok.

> Now, the next logical question would be: how to I reclaim the space
> after issue the ALTER TABLE ... DROP COLUMN statement with a
> fixed-length column?
> Rebuild the Clustered Index.
Ok, so basically the space is not reclaimed until either the complete
table is rebuild or old records are deleted.
Thanks a lot! You get the virtual hug: *hug*
:-)
Kind regards
robert

alter table drop column and dbcc cleantable

All,
I just want to make sure my understanding is correct:
1) alter table drop column does not release space of varchar columns and
other columns of variable length.
2) dbcc cleantable frees this space
Open questions:
3) What about the space used by fixed size columns? Is it reclaimed
during drop (timing lets me suspect that it's not)? dbcc cleantable "does
not reclaim space after a fixed length column is dropped."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp
Are rows inserted after a DROP COLUMN smaller?
Thanks a lot!
Kind regards
robertRobert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert|||Well, its actually two questions.
1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does the
ALTER TABLE ... DROP COLUMN statement. Correct.
2. Are the rows inserted after the ALTER TABLE statement has been issued?
Yes, new rows will only occupy the storage required for the new data
definition contingent on the values of PAD_INDEX and FILLFACTOR for the
Clustered Index.
Now, the next logical question would be: how to I reclaim the space after
issue the ALTER TABLE ... DROP COLUMN statement with a fixed-length column?
Rebuild the Clustered Index.
Sincerely,
Anthony Thomas
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
Robert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert|||Anthony Thomas wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Robert Klemme wrote:
>> All,
>> I just want to make sure my understanding is correct:
>> 1) alter table drop column does not release space of varchar columns
>> and other columns of variable length.
>> 2) dbcc cleantable frees this space
>> Open questions:
>> 3) What about the space used by fixed size columns? Is it reclaimed
>> during drop (timing lets me suspect that it's not)? dbcc cleantable
>> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp
>> Are rows inserted after a DROP COLUMN smaller?
>> Thanks a lot!
>> Kind regards
>> robert
> No one wants to answer this one? Come on... I can even offer a
> virtual hug. :-)
> Well, its actually two questions.
> 1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does
> the ALTER TABLE ... DROP COLUMN statement. Correct.
Ok.
> 2. Are the rows inserted after the ALTER TABLE statement has been
> issued? Yes, new rows will only occupy the storage required for the
> new data definition contingent on the values of PAD_INDEX and
> FILLFACTOR for the Clustered Index.
Ok.
> Now, the next logical question would be: how to I reclaim the space
> after issue the ALTER TABLE ... DROP COLUMN statement with a
> fixed-length column?
> Rebuild the Clustered Index.
Ok, so basically the space is not reclaimed until either the complete
table is rebuild or old records are deleted.
Thanks a lot! You get the virtual hug: *hug*
:-)
Kind regards
robert

ALTER Table

I am trying to use the ALTER table Alter column command to set the default
value of a column and to drop the default value of a column. can anyone pls
give me the syntax.I tried the one given in sql help file but gives me a
syntax error.
thanks!To add a default after the table has been created:
alter table <table name>
add constraint <constraint name>
default (<expression> )
for <column name>
To drop a default:
alter table <table name>
drop constraint <constraint name>
Is that what you need?
ML
http://milambda.blogspot.com/|||No i need to alter a column's default... like set a new default value or dro
p
default.but iam getting syntax errors.
"ML" wrote:

> To add a default after the table has been created:
> alter table <table name>
> add constraint <constraint name>
> default (<expression> )
> for <column name>
> To drop a default:
> alter table <table name>
> drop constraint <constraint name>
> Is that what you need?
>
> ML
> --
> http://milambda.blogspot.com/|||You cannot change the default in a single statement. You have to drop the
old default constraint, and add the new one, like ML illustrated.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"HP" <HP@.discussions.microsoft.com> wrote in message
news:50D57999-EC50-4E7C-9E04-D538FFB2EE7A@.microsoft.com...
> No i need to alter a column's default... like set a new default value or
> drop
> default.but iam getting syntax errors.
> "ML" wrote:
>
>|||Are you talking about a default as a database object that you have bound to
a
column? Have you tried sp_unbindefault?
ML
http://milambda.blogspot.com/

Alter Table

Hi, i think that my question is stupid, but i will ask anyway ..
I want to allow a user to create stored procedure, alter stored procedure,
and drop procedure, but the same user cant alter any table, can i do that '
?
Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200508/1You can
GRANT CREATE PROC TO username
The procedures that the user creates will that user also be able to alter an
d drop. But the user
will only be able to create procedures with that user as the owner, and the
user will only be able
to alter and drop the procedures that he/she is the owner of.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Plantador R via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:525A944123AA0@.webservertalk.com...
> Hi, i think that my question is stupid, but i will ask anyway ..
> I want to allow a user to create stored procedure, alter stored procedure,
> and drop procedure, but the same user cant alter any table, can i do that
?
> Thanks
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200508/1|||Sure you can do that.
AMB
"Plantador R via webservertalk.com" wrote:

> Hi, i think that my question is stupid, but i will ask anyway ..
> I want to allow a user to create stored procedure, alter stored procedure,
> and drop procedure, but the same user cant alter any table, can i do that
?
> Thanks
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200508/1
>|||Thanks . Ill try it ...
Message posted via http://www.webservertalk.com

alter table

Hi, my tb1 already have a primary key, how to drop it and recreate a new one
is auto id primary? Please help...Here is a good post from Itzik Ben-Gan:
http://www.windowsitpro.com/Article...qlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:Ob$nMZCTFHA.3464@.tk2msftngp13.phx.gbl...
> Hi, my tb1 already have a primary key, how to drop it and recreate a new
> one is auto id primary? Please help...
>

alter table

Hi All
How can I Change The column Name in The table whit out drop it
column_b -- to --column_c
ALTER TABLE doc_exa (ADD) column_b VARCHAR(20) NULLUse sp_rename:
EXEC sp_rename 'doc_exa.column_b' 'column_c' 'COLUMN'
But you'll need to modify all references to column_b to use column_c
Hope that helps,
Lubdha|||Hi Taha
Please look up sp_rename in the Books Online.
EXEC sp_rename 'doc_exa.column_b', 'column_c'
HTH
Kalen Delaney, SQL Server MVP
"Taha" <taha105@.hotmail.com> wrote in message
news:%23Iz0WE2jGHA.4504@.TK2MSFTNGP05.phx.gbl...
> Hi All
> How can I Change The column Name in The table whit out drop it
> column_b -- to --column_c
> ALTER TABLE doc_exa (ADD) column_b VARCHAR(20) NULL
>
>|||Thanks For Reply
When The Table Name Like [Table1] Its Work Fine
But When The Name Is [Table_1] Don't Work
Exec sp_rename 'Table1.[MyColumn]', 'NewName', 'COLUMN' Its Work
Exec sp_rename 'Table_1.[MyColumn]', 'NewName', 'COLUMN' Don't Work
Any idea
Thanks
"Taha" <taha105@.hotmail.com> wrote in message
news:%23Iz0WE2jGHA.4504@.TK2MSFTNGP05.phx.gbl...
> Hi All
> How can I Change The column Name in The table whit out drop it
> column_b -- to --column_c
> ALTER TABLE doc_exa (ADD) column_b VARCHAR(20) NULL
>
>|||This is The Message
Either the parameter @.objname is ambiguous or the claimed @.objtype (COLUMN)
is wrong.
"Taha" <taha105@.hotmail.com> wrote in message
news:%23Iz0WE2jGHA.4504@.TK2MSFTNGP05.phx.gbl...
> Hi All
> How can I Change The column Name in The table whit out drop it
> column_b -- to --column_c
> ALTER TABLE doc_exa (ADD) column_b VARCHAR(20) NULL
>
>|||Show us the sp_help output for the table.
HTH
Kalen Delaney, SQL Server MVP
"Taha" <taha105@.hotmail.com> wrote in message
news:OX6sG32jGHA.4512@.TK2MSFTNGP02.phx.gbl...
> This is The Message
> Either the parameter @.objname is ambiguous or the claimed @.objtype
> (COLUMN) is wrong.
>
>
> "Taha" <taha105@.hotmail.com> wrote in message
> news:%23Iz0WE2jGHA.4504@.TK2MSFTNGP05.phx.gbl...
>

Alter Table

Hi, i think that my question is stupid, but i will ask anyway ..
I want to allow a user to create stored procedure, alter stored procedure,
and drop procedure, but the same user cant alter any table, can i do that '
?
Thanks
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200508/1Hi,
ALTER PROCEDURE and DROP PROCEDURE commands are not grantable. Only solution
is to give db_ddladmin tole to
this login account. In this case that user will be able to ALTER and DROP
tables.
Thanks
Hari
SQL Server MVP
"Plantador R via droptable.com" <forum@.droptable.com> wrote in message
news:525A9612E2CA8@.droptable.com...
> Hi, i think that my question is stupid, but i will ask anyway ..
> I want to allow a user to create stored procedure, alter stored procedure,
> and drop procedure, but the same user cant alter any table, can i do that
> ?
> Thanks
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...curity/200508/1

Alter identity -field?

Hello.
I have a table with int identity field (X INT IDENTITY(1,1)).
I want update SEED value to 4000.
I cannot drop column because I have a foreign key to it from other table.
How I can do this (update/alter identity's SEED value to column)?
dbcc checkident
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>
|||Hi,
Execute the below command, replace the dbname and table name with actual
USE DBNAME
GO
DBCC CHECKIDENT (tablename, RESEED, 4000)
Thanks
Hari
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>

ALTER COLUMN

Hi All,
I'm trying to use the ALTER TABLE....ALTER COLUMN command to drop the
DEFAULT attribute from a table. I have 2 questions:
1. I try to alter multiple columns in one call but it never works. I get
error messages. Do you need to use multiple ALTER TABLE calls to modify
multiple columns?
2. How to drop the DEFAULT attribute of a column?
Thank you for your help.
Conway1. Pretty sure you will need to do that in multiple calls, you can put it
in a tran if you need to
2. You find the name of the object if you dont know it:
create table test
(
value1 varchar(10) default (10),
value2 varchar(10)
)
go
select name as column_name, object_name(cdefault)
from syscolumns
where cdefault <> 0
Then:
ALTER TABLE test
DROP Constraint DF__test__value1__78B3EFCA
Your name will be different...
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Conax" <ConaxLiu@.hotmail.com> wrote in message
news:eCUeNWgoFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I'm trying to use the ALTER TABLE....ALTER COLUMN command to drop the
> DEFAULT attribute from a table. I have 2 questions:
> 1. I try to alter multiple columns in one call but it never works. I get
> error messages. Do you need to use multiple ALTER TABLE calls to modify
> multiple columns?
> 2. How to drop the DEFAULT attribute of a column?
> Thank you for your help.
> Conway
>|||Here is a slightly modified example from BOL on dropping multiple columns in
a single call
CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL
,column_c varchar(20) null)
GO
ALTER TABLE doc_exb DROP COLUMN column_b,column_c
GO
go
EXEC sp_help doc_exb
GO
DROP TABLE doc_exb
GO
HTH...
http://zulfiqar.typepad.com
BSEE, MCP
"Louis Davidson" wrote:

> 1. Pretty sure you will need to do that in multiple calls, you can put it
> in a tran if you need to
> 2. You find the name of the object if you dont know it:
> create table test
> (
> value1 varchar(10) default (10),
> value2 varchar(10)
> )
> go
> select name as column_name, object_name(cdefault)
> from syscolumns
> where cdefault <> 0
> Then:
> ALTER TABLE test
> DROP Constraint DF__test__value1__78B3EFCA
> Your name will be different...
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Conax" <ConaxLiu@.hotmail.com> wrote in message
> news:eCUeNWgoFHA.2484@.TK2MSFTNGP15.phx.gbl...
>
>|||On Tue, 16 Aug 2005 13:55:15 +1200, "Conax" <ConaxLiu@.hotmail.com> wrote:
in <eCUeNWgoFHA.2484@.TK2MSFTNGP15.phx.gbl>

>Hi All,
>I'm trying to use the ALTER TABLE....ALTER COLUMN command to drop the
>DEFAULT attribute from a table. I have 2 questions:
>1. I try to alter multiple columns in one call but it never works. I get
>error messages. Do you need to use multiple ALTER TABLE calls to modify
>multiple columns?
>2. How to drop the DEFAULT attribute of a column?
>Thank you for your help.
>Conway
>
I think you want this if you want to drop the default constraint on multiple
columns at once.
ALTER TABLE tablename DROP
CONSTRAINT DF_firstconstraintname,
CONSTRAINT DF_secondconstraintname
Stefan Berglund|||Yes, droping stuff work with multiple columns/constraints, but alter will
not (though I might be wrong, but I don't thik so)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"ZULFIQAR SYED" <DRSQLnospam2005@.hotmail.com> wrote in message
news:C2BDF777-720A-4CF0-90FA-ADFDB680B75F@.microsoft.com...
> Here is a slightly modified example from BOL on dropping multiple columns
> in
> a single call
> CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL
> ,column_c varchar(20) null)
> GO
> ALTER TABLE doc_exb DROP COLUMN column_b,column_c
> GO
> go
> EXEC sp_help doc_exb
> GO
> DROP TABLE doc_exb
> GO
> HTH...
> --
> http://zulfiqar.typepad.com
> BSEE, MCP
>
> "Louis Davidson" wrote:
>|||Thanks to all who replied.
Unfortunately I won't know the constrant name beforehand so I can't really
DROP CONSTRAINT name...
The thing is, I work on development server, and then pass a SQL script to
another person to update the live server. I cannot be 100% sure that the
constraint names on the live server will be exactly same as the test server.
Therefore, looks like we will have to manually delete the defaults from
Enterprise Manager.
But thanks again for your kind helps.
Regards,
Conway
"Conax" <ConaxLiu@.hotmail.com> wrote in message
news:eCUeNWgoFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I'm trying to use the ALTER TABLE....ALTER COLUMN command to drop the
> DEFAULT attribute from a table. I have 2 questions:
> 1. I try to alter multiple columns in one call but it never works. I get
> error messages. Do you need to use multiple ALTER TABLE calls to modify
> multiple columns?
> 2. How to drop the DEFAULT attribute of a column?
> Thank you for your help.
> Conway
>|||>> I cannot be 100% sure that the constraint names on the live server will
Why, were they built using different scripts? Why?
If you want control over this, you should only deploy scripts where
constraints are explicitly defined and named, and deploy the same script(s)
to all environments. Then you can run the same script in both places and it
will always work.
> Therefore, looks like we will have to manually delete the defaults from
> Enterprise Manager.
Why? You can generate this script easily, but please see my point above.
This will generate a script to drop all PK, FK and UQ constraints:
SELECT 'ALTER TABLE '+TABLE_NAME+' DROP CONSTRAINT '+CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
And this will get the defaults:
SELECT 'ALTER TABLE '+t.TABLE_NAME+' DROP CONSTRAINT '+o.name
FROM sysconstraints c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON OBJECT_ID(t.TABLE_SCHEMA+'.'+t.TABLE_NAME) = c.id
INNER JOIN sysobjects o
ON o.id = c.constid
WHERE o.type='D'
Once you generate these alter statements, you can copy them all to the top
pane and run them (and if you want, you can browse through them and remove
the ones you don't want to execute).|||Thanks a lot Aaron. Your information is pretty useful. Much appreciated.
Conway
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eNsGr0yoFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Why, were they built using different scripts? Why?
> If you want control over this, you should only deploy scripts where
> constraints are explicitly defined and named, and deploy the same
script(s)
> to all environments. Then you can run the same script in both places and
it
> will always work.
>
> Why? You can generate this script easily, but please see my point above.
> This will generate a script to drop all PK, FK and UQ constraints:
> SELECT 'ALTER TABLE '+TABLE_NAME+' DROP CONSTRAINT '+CONSTRAINT_NAME
> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> And this will get the defaults:
> SELECT 'ALTER TABLE '+t.TABLE_NAME+' DROP CONSTRAINT '+o.name
> FROM sysconstraints c
> INNER JOIN INFORMATION_SCHEMA.TABLES t
> ON OBJECT_ID(t.TABLE_SCHEMA+'.'+t.TABLE_NAME) = c.id
> INNER JOIN sysobjects o
> ON o.id = c.constid
> WHERE o.type='D'
> Once you generate these alter statements, you can copy them all to the top
> pane and run them (and if you want, you can browse through them and remove
> the ones you don't want to execute).
>

Allowing users to type in characters in a drop down list?

is this even possible in reporting services? I already deployed reports for our client using Reporting Services 2000, one of their complaints was that the dropdownlist of the reports contains a very long list of data, this list cannot be shorten since all data are required. Is there a way to let the users type in characters, not only one character to find the exact data they want. The data in the drop down list are needed because these data are parameters.

Are there any web based reporting tools which can provide this kind of requirement?

Reporting Services utilises standard HTML controls (apart from multi-value parameters). The only typing supported in a html drop-down is by first letter.

You could come up with a 2 dependant parameter approach, where users type into a textbox and the dropdown list filters based on the pattern typen into the textbox. This however would not happen as they type, they would need to leave focus from the textbox which would then postback and do the filtering server-side.

The only other option is to code your own parameter UI.

All selection in Reporting Services

I am looking for some help with reporting services.
I have a drop down menu with the list of products populated from a
table. And I want a n 'All' selection so that all the product
information is retreived. I am aware that I cannot pass array of
parameters to the stored proc.
Is there a way out,
Looks like this
'Product'
TV
Camcorder
Digital Camera
ALL
and 'ALL' is where it should select TV, Camcorder and Digital Camera.If you search this group for "all parameter", you'll find many viable
solutions. Almost too many.
For the most straight forward explanation I've seen, check this post in
Chris Hays' blog:
http://blogs.msdn.com/chrishays/archive/2004/07/27.aspx
HTH
toolman