Change included columns when modifying table

Is there a reason you can't modify included columns when modifying an index on a table?

Well, the primary reason would be due to the fact that adding additional included columns would touch all of the leaf pages in an index, and probably force a fair majority of pages to split multiple times, so in reality most of the time recreating the index from scratch would probably be faster and more efficient once completed. Additionally, a heavy heavy number of disk seeks would be incurred jumping from the position last updated in the index to the table data to read the data to be added and then jump back again.

Basically, I'm sure you'd find that rebuilding the index will almost always give you both faster results, and additionally a more efficient (clean) index when the operation completes.

HTH,

|||I full appreciate that the index needs to be rebuilt, however SSMS manages that for you when you change the columns, clustering, file groups etc. So why can't I change the included columns and SSMS just do a drop_existing.

Change in legend of chart based on values?

I am developing a chart with type as column and subtype as stacked. The values (different columns from my dataset) are shown as series in the chart. If I have an entire column with no values, nothing in shown in the graph but the column name comes in the legend. I do not want to show the particular column in legend if all the values in that column are 0 or null. Is it possible through an expression or any other way?

Please let me know.

Thanks in advance !!!

If you use a dynamic series grouping in the chart and you want to get rid of a particular series grouping instance, you could use a filter expression on the series grouping, e.g.
Filter expression: =Sum(Fields!Name.Value, "DynamicSeriesGroupingName")
Filter operator: >
Filter value: =0

Again, the filter approach will only work in the case of dynamic series groupings.

-- Robert

Change Identify/Primary Key Column Data Type?

Hi ...

I've

taken over a project where the tables were created with

identity/primary key columns of type DECIMAL(12,0). The latest addition

to the project is to replicate data down to Pocket PC applications.

Replication requires that identify/primary key columns be of type

INT/BIGINT.

I've attempted to ALTER TABLE xxx ALTER COLUMN yyy

BIGINT; and it fails. Failed due to all the foreign key constraints that have been created. While it didn't give any error messages associated with the the field being the PRIMARY KEY - I'm assuming I may get that error as well.

I then did an ALTER TABLE xxx NOCHECK CONSTRAINT

ALL; for every table in the system to disable checking of foreign keys

and then attempted to alter the column to a bigint and it still failed.

How

can I change the column from Decimal to BIGINT - or do I have to create

new tables, import all the data, get rid of the original tables? Please

tell me I don't have to do the latter.

Thanks ...Unfortunately the only way is to recreate the table or drop & recreate the column. This would require removing the existing constraints (PKs, FKs) and recreating them. Alternatively, you can create a view on the table(s) that does the conversion and replicate those. But this may or may not work depending on your replication scheme and you will have to ask in the Replication newsgroup.

Change from char to varchar and RTRIM

Hi,
I have a big database with most of the columns having the datatype char
(char(10), (char(50) etc.)
I would like to change all these columns to varchar (varchar(10),
varchar(50), ...), and have all the spaces on the right trimmed of. So
instead of 'ABC ' I want to have 'ABC'.
Is there a 'nice' way to do this? Changing all the char-columns to varchar
doesn't seem too difficult to me, but would there be a way that automaticly
cuts of this trailing spaces? Or is there some script that exists that does
this for me?
The biggest problem in my opinion is that some of these columns are used as
foreign keys. So I don't know if it is possible that they will be trimmed in
one table, but not yet in the foreign-key-table?
Any help our hints would be really appreciated!
Thanks a lot in advance,
PieterHello, Pieter
To change the data types of the columns, you need to drop the foreign
keys, change all the columns involved (using "ALTER TABLE tbl ALTER
COLUMN col varchar(n)") and then recreate the foreign keys. That's
because a foreign key requires the referencing columns to have the same
data type as the referenced columns (additionally, a column involved in
any kind of constraint cannot be altered).
The trimming can be done before or after recreating the foreign keys,
because when SQL Server compares 'a' with 'a ', they will be equal. For
example, the following would work just fine:
USE tempdb
CREATE TABLE t1 (x varchar(10) primary key)
CREATE TABLE t2 (y varchar(10) references t1)
INSERT INTO t1 VALUES('a')
INSERT INTO t2 VALUES('a ')
SELECT x+'!', y+'!' FROM t1 INNER JOIN t2 ON x=y
DROP TABLE t2,t1
Razvan

Change from char to varchar and RTRIM

Hi,
I have a big database with most of the columns having the datatype char
(char(10), (char(50) etc.)
I would like to change all these columns to varchar (varchar(10),
varchar(50), ...), and have all the spaces on the right trimmed of. So
instead of 'ABC ' I want to have 'ABC'.
Is there a 'nice' way to do this? Changing all the char-columns to varchar
doesn't seem too difficult to me, but would there be a way that automaticly
cuts of this trailing spaces? Or is there some script that exists that does
this for me?
The biggest problem in my opinion is that some of these columns are used as
foreign keys. So I don't know if it is possible that they will be trimmed in
one table, but not yet in the foreign-key-table?
Any help our hints would be really appreciated!
Thanks a lot in advance,
PieterHello, Pieter
To change the data types of the columns, you need to drop the foreign
keys, change all the columns involved (using "ALTER TABLE tbl ALTER
COLUMN col varchar(n)") and then recreate the foreign keys. That's
because a foreign key requires the referencing columns to have the same
data type as the referenced columns (additionally, a column involved in
any kind of constraint cannot be altered).
The trimming can be done before or after recreating the foreign keys,
because when SQL Server compares 'a' with 'a ', they will be equal. For
example, the following would work just fine:
USE tempdb
CREATE TABLE t1 (x varchar(10) primary key)
CREATE TABLE t2 (y varchar(10) references t1)
INSERT INTO t1 VALUES('a')
INSERT INTO t2 VALUES('a ')
SELECT x+'!', y+'!' FROM t1 INNER JOIN t2 ON x=y
DROP TABLE t2,t1
Razvan

Change Column Order in Report

Dear Anyone,

Is there a way to dynamically change the order of the columns displayed in an RS report in RS2005?

Thanks,
Joseph

have u found out the solution. i m also trying to implement but i couldn. can u help me to proceed.|||

There is no way to change the column order in RS2005.

The next viable alternative is to change the field that is assigned to the column programatically. You can do this by using the expression edittor of the text box that holds the column information. You will need to assign conditions on what field should be displayed in the column

|||

thanks.. i have also tried that. I m having many cases and also i couldn judge the column order.. its all dynamic.

Change Column Order in Report

Dear Anyone,

Is there a way to dynamically change the order of the columns displayed in an RS report in RS2005?

Thanks,
Joseph

have u found out the solution. i m also trying to implement but i couldn. can u help me to proceed.|||

There is no way to change the column order in RS2005.

The next viable alternative is to change the field that is assigned to the column programatically. You can do this by using the expression edittor of the text box that holds the column information. You will need to assign conditions on what field should be displayed in the column

|||

thanks.. i have also tried that. I m having many cases and also i couldn judge the column order.. its all dynamic.

Change collation of column with User-Defined Datatype

I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?Hi
"DBA72" wrote:

> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change t
he
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John

Change collation of column with User-Defined Datatype

I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?
Hi
"DBA72" wrote:

> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John

Change collation of column with User-Defined Datatype

I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?Hi
"DBA72" wrote:
> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John

change collation of a type of text column

Hi!
I have to change a complate database collation. After I had changed the
database collation I go through the tables and its columns and make a DDL
command on collated columns :
"ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"
It work on varchar and char columns but I receive an error message in case
of text columns :
"Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'Ic_DbFields' because it is 'text'."
I use OLEDB.
Enterprise Manager can change the collation on text columns.
What can I do?
I nedd to use SQL-DMO?
thanks for any help :
ImreCheck if you have a full-text index using that column. if so, then you have
to remove it before changing the collation.
AMB
"Imre Ament" wrote:

> Hi!
> I have to change a complate database collation. After I had changed the
> database collation I go through the tables and its columns and make a DDL
> command on collated columns :
> "ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"
> It work on varchar and char columns but I receive an error message in case
> of text columns :
> "Server: Msg 4928, Level 16, State 1, Line 1
> Cannot alter column 'Ic_DbFields' because it is 'text'."
> I use OLEDB.
> Enterprise Manager can change the collation on text columns.
> What can I do?
> I nedd to use SQL-DMO?
> thanks for any help :
> Imre|||I don't think you can alter collation for blob columns.
One option can be to add a new column with desired collation, update the new
column with the value
of the old column, drop the old column and rename the new column. Column ord
er will not be
preserved, of course.
Another option is to create a new table. My guess is that this is what Enter
prise Manager does.
Imre: pressing "save change script" will show you how EM does this. I doubt
it is exposed in DMO, as
the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But yo
u could have a look, of
course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@.microsoft.com...
> Check if you have a full-text index using that column. if so, then you hav
e
> to remove it before changing the collation.
>
> AMB
> "Imre Ament" wrote:
>|||You are right. Thanks for the comment.
AMB
"Tibor Karaszi" wrote:

> I don't think you can alter collation for blob columns.
> One option can be to add a new column with desired collation, update the n
ew column with the value
> of the old column, drop the old column and rename the new column. Column o
rder will not be
> preserved, of course.
> Another option is to create a new table. My guess is that this is what Ent
erprise Manager does.
> Imre: pressing "save change script" will show you how EM does this. I doub
t it is exposed in DMO, as
> the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But
you could have a look, of
> course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@.microsoft.com...
>

Change collation all db tables and db

If I need to copy a db to another server but I need to chnage the new db
collation and the collation of all the columns, is there a better way than:
1. Create new DB with desired collation
2. Create table scripts without collation option or constraints; run them
3. Use BCP or DTS to move the data
4. Script contraints, stored procs, views....; run them
I ran DTS object move but that always seems to copy the column collation.
Ideally I would like to somehow force a backup or restore to use a given
collation.
-Peterhttp://groups.google.co.uk/groups?q...
&rnum=2
Jacco Schalkwijk
SQL Server MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
> If I need to copy a db to another server but I need to chnage the new db
> collation and the collation of all the columns, is there a better way
> than:
> 1. Create new DB with desired collation
> 2. Create table scripts without collation option or constraints; run them
> 3. Use BCP or DTS to move the data
> 4. Script contraints, stored procs, views....; run them
> I ran DTS object move but that always seems to copy the column collation.
> Ideally I would like to somehow force a backup or restore to use a given
> collation.
> --
> -Peter|||Thanks Jacco. I guess there is no easy way to change collation.
-Peter
"Jacco Schalkwijk" wrote:

> http://groups.google.co.uk/groups?q...r />
bl&rnum=2
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Peter L." <PeterL@.discussions.microsoft.com> wrote in message
> news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
>
>

Change collation all db tables and db

If I need to copy a db to another server but I need to chnage the new db
collation and the collation of all the columns, is there a better way than:
1. Create new DB with desired collation
2. Create table scripts without collation option or constraints; run them
3. Use BCP or DTS to move the data
4. Script contraints, stored procs, views....; run them
I ran DTS object move but that always seems to copy the column collation.
Ideally I would like to somehow force a backup or restore to use a given
collation.
-Peter
http://groups.google.co.uk/groups?q=...phx.gbl&rnum=2
Jacco Schalkwijk
SQL Server MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
> If I need to copy a db to another server but I need to chnage the new db
> collation and the collation of all the columns, is there a better way
> than:
> 1. Create new DB with desired collation
> 2. Create table scripts without collation option or constraints; run them
> 3. Use BCP or DTS to move the data
> 4. Script contraints, stored procs, views....; run them
> I ran DTS object move but that always seems to copy the column collation.
> Ideally I would like to somehow force a backup or restore to use a given
> collation.
> --
> -Peter
|||Thanks Jacco. I guess there is no easy way to change collation.
-Peter
"Jacco Schalkwijk" wrote:

> http://groups.google.co.uk/groups?q=...phx.gbl&rnum=2
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Peter L." <PeterL@.discussions.microsoft.com> wrote in message
> news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
>
>

Change collation all db tables and db

If I need to copy a db to another server but I need to chnage the new db
collation and the collation of all the columns, is there a better way than:
1. Create new DB with desired collation
2. Create table scripts without collation option or constraints; run them
3. Use BCP or DTS to move the data
4. Script contraints, stored procs, views....; run them
I ran DTS object move but that always seems to copy the column collation.
Ideally I would like to somehow force a backup or restore to use a given
collation.
--
-Peterhttp://groups.google.co.uk/groups?q=bart+duncan+collate+database+jacco&hl=en&lr=&group=microsoft.public.sqlserver.*&selm=%23%24c1OFoaEHA.1652%40TK2MSFTNGP09.phx.gbl&rnum=2
--
Jacco Schalkwijk
SQL Server MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
> If I need to copy a db to another server but I need to chnage the new db
> collation and the collation of all the columns, is there a better way
> than:
> 1. Create new DB with desired collation
> 2. Create table scripts without collation option or constraints; run them
> 3. Use BCP or DTS to move the data
> 4. Script contraints, stored procs, views....; run them
> I ran DTS object move but that always seems to copy the column collation.
> Ideally I would like to somehow force a backup or restore to use a given
> collation.
> --
> -Peter|||Thanks Jacco. I guess there is no easy way to change collation.
-Peter
"Jacco Schalkwijk" wrote:
> http://groups.google.co.uk/groups?q=bart+duncan+collate+database+jacco&hl=en&lr=&group=microsoft.public.sqlserver.*&selm=%23%24c1OFoaEHA.1652%40TK2MSFTNGP09.phx.gbl&rnum=2
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Peter L." <PeterL@.discussions.microsoft.com> wrote in message
> news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
> > If I need to copy a db to another server but I need to chnage the new db
> > collation and the collation of all the columns, is there a better way
> > than:
> >
> > 1. Create new DB with desired collation
> > 2. Create table scripts without collation option or constraints; run them
> > 3. Use BCP or DTS to move the data
> > 4. Script contraints, stored procs, views....; run them
> >
> > I ran DTS object move but that always seems to copy the column collation.
> >
> > Ideally I would like to somehow force a backup or restore to use a given
> > collation.
> >
> > --
> > -Peter
>
>

Change background color on toggle

I have a report with a table, four columns. I added a second row to the
table and inserted a subreport. The subreport's ToggleItem is the first
column in the first detail row of the table. When I toggle a row to display
the subreport, I'd like to change the background color of the row. I can't
seem to get a reference to the ToggleState of the Textbox, or the Visibility
of the subreport. Any ideas?
--
KenHi Ken,
Thank you for your posting!
Based on my research, we could not get the reference to the Visibility of a
report item or the togglestate of a textbox.
Unfortunately, I could not found a workaround now.
To provide your feedback directly to the product groups:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Thank you for your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||There is an example on www.msbicentral.com called
ConditionalGroupBkgrdColor.rdl. It is in the downloads section for Reporting
Services Reports. Its description is:
This report demonstrates how to change a group's background color when it is
toggled to show its children. The key areas to focus on are the rectangles
and their children. Note the use of a toggle textbox, visible textbox, and a
hidden textbox.
You'll have to register(free) to download it. This might be what you are
looking for.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"GopherGold" wrote:
> I have a report with a table, four columns. I added a second row to the
> table and inserted a subreport. The subreport's ToggleItem is the first
> column in the first detail row of the table. When I toggle a row to display
> the subreport, I'd like to change the background color of the row. I can't
> seem to get a reference to the ToggleState of the Textbox, or the Visibility
> of the subreport. Any ideas?
> --
> Ken|||Thanks, Wayne. I'm using RS2005. When I open the rdl file, I'm asked to
convert it, leading me to believe that this was done for RS2000. Is that
correct? After converting it, something must have broken, because in preview
mode the groups don't toggle.
--
Ken
"Wayne Snyder" wrote:
> There is an example on www.msbicentral.com called
> ConditionalGroupBkgrdColor.rdl. It is in the downloads section for Reporting
> Services Reports. Its description is:
> This report demonstrates how to change a group's background color when it is
> toggled to show its children. The key areas to focus on are the rectangles
> and their children. Note the use of a toggle textbox, visible textbox, and a
> hidden textbox.
> You'll have to register(free) to download it. This might be what you are
> looking for.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "GopherGold" wrote:
> > I have a report with a table, four columns. I added a second row to the
> > table and inserted a subreport. The subreport's ToggleItem is the first
> > column in the first detail row of the table. When I toggle a row to display
> > the subreport, I'd like to change the background color of the row. I can't
> > seem to get a reference to the ToggleState of the Textbox, or the Visibility
> > of the subreport. Any ideas?
> >
> > --
> > Ken|||Hi Ken,
Please try to find the ToggleTextBox and modify the width to 0.3 in. Then
you could see the (+) mark and you could toggle the group.
Thanks for Wayne's great help!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Ken,
Have you tried the suggestion? Please let me know if you need any help on
this issue. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

change all char columns to varchar

Hi ,
Our company decided to convert all char columns to varchar in our next
software release.
We have lot (40+) of client databases which is more than 75GB .
Below the query I wrote for this ,but it's taking long long hours to
complete the upgrade.
Is there any better way to write this '
Thanks
--*******************************************************************
-- Script to convert all char columns to varchar
--*******************************************************************
declare @.tbname varchar(255)
declare @.column_name varchar(255)
declare @.is_nullable varchar(255)
declare @.character_maximum_length varchar(255)
declare @.Query varchar(8000)
declare c1 cursor for
select
collist.table_name,collist.column_name,collist.is_nullable,collist.character
_maximum_length from information_schema.columns
collist,information_schema.tables tablist
where collist.table_name=tablist.table_name and tablist.table_type='BASE
TABLE' and data_type='char'
open c1
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
while @.@.fetch_status=0
begin
if (@.is_nullable='No')
set @.is_nullable='NOT NULL'
else
set @.is_nullable='NULL'
set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
VARCHAR('+@.character_maximum_length+') '+@.is_nullable
exec (@.Query)
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
end
deallocate c1
GO
PRINT '** Script 07 -- 07.char to varchar.sql completed **'
goI think you can change the Cursor to table variable ... Add Set NOCOUNT ON
to remove the un-necessary verbose of "n rows affected" ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.character
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>|||Each ALTER TABLE statement will require updating every row in the table.
This will take quite some time if you have many char columns in a single
table because you'll be updating each row many times. Furthermore, this
will not trim trailing spaces from the char column and leave a lot of
other wasted space in the table.
You will be better off creating a set of tables with the new structure
and loading via DTS. You can trim spaces during the process. This
method will provide the additional flexibility of allowing you to keep
existing char datatypes where appropriate.
Another method is to create new tables using SELECT INTO. This can be a
bit tricky and is problematic with identity columns. Example below.
CREATE TABLE MyTable
(
Col1 int NOT NULL,
Col2 char(10) NOT NULL,
Col3 char(10) NULL
)
INSERT INTO MyTable VALUES (1, 'a', 'b')
GO
SELECT
Col1,
-- need to specify ISNULL to create a NOT NULL
ISNULL(CAST(RTRIM(Col2) AS varchar(10)), '') AS Col2,
CAST(RTRIM(Col2) AS varchar(10)) AS Col3
INTO MyTable_New
FROM MyTable
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.chara
cter
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and
tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>

challenge...

How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)?

Like I have a table employee

Employee

(

colA int

colB int

colC varchar

)

If i run the tsql..

it should give me

Employee

(

colA varchar

colB varchar

colC varchar

)

Is the table empty? If not, what is the disposition of the data that is already in the table?

|||

Well you could try using a change script generated by Enterprise Manager, like this one:

Code Snippet

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Employee
(
ColA varchar(1000) NULL,
ColB varchar(1000) NULL,
ColC varchar(1000) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Employee)
EXEC('INSERT INTO dbo.Tmp_Employee (ColA, ColB, ColC)
SELECT CONVERT(varchar(1000), ColA), CONVERT(varchar(1000), ColB), ColC FROM dbo.Employee (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'Employee', 'OBJECT'
GO
COMMIT

|||

Please don't use the graphical tools to make schema changes. It can generate scripts that are inefficient and unnecessary. You can just use ALTER TABLE to change the column from int to varchar in this case. If you do use the tools then please make sure to review the scripts because there is lot of things that can be simplified or improved. For example, in above case there is no reason to do CREATE TABLE and INSERT. You can do SELECT...INTO - this can perform minimally logged operations & can run magnitudes of time faster.

Challenge - Swap two columns

Ok .. a challenge ...
How do you swap two columns based on some condition without using a cursor ( I mean a set based solution).alter table foo add tempcol

update foo set tempcol=field1

update foo set field1 = field2

update foo set field2=tempcol

alter table drop column tempcol

you did not say it had to be good, you did not say it had to be efficient, you said merely no cursor|||did you forget it is not possible to add a column and update it in the same sql batch ...|||forget? no, because i never knew that to begin with

okay, so just run those steps in separate batches|||forget? no, because i never knew that to begin with

You are joking ? Right ?|||not in the slightest, no, i was not joking

i don't know what a "batch" is

i just know it's something you DBAs concern yourself with

is a batch like a transaction block? isn't there a COMMIT statement you can use?|||not in the slightest, no, i was not joking

i don't know what a "batch" is

i just know it's something you DBAs concern yourself with

is a batch like a transaction block? isn't there a COMMIT statement you can use?
"Go" and Read up on "GO" statement in the holy book ...
Also
Try this

use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
update MyTable99 set b = 2
go

Then Try this

drop table MyTable99
go
use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
go
update MyTable99 set b = 2
go

drop table MyTable99
go|||Try this
use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
update MyTable99 set b = 2
go

Error: Invalid column name 'b'. (State:S0022, Native Code: CF)

Then Try this
drop table MyTable99
go
use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
go
update MyTable99 set b = 2
go

drop table MyTable99
go

This command did not return data, and it did not return any rows

sweet

and your point is?

and how does this swap two columns??????|||I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

update table99 set a=b, b=a

with an optional where condition.|||I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

update table99 set a=b, b=a

with an optional where condition. At least the way that I understand Enigma's question, your answer is exactly what I would have suggested. The part that I'm still fuzzy on is that the rest of this thread seems to have veered off on a tangent, which makes me wonder if I understood the original question.

-PatP|||That was the answer I was looking for ... coz I was intrigued by the way sql server handles updates in this case

R937 : the point is the sql batch ... all statements in a sql batch are compiled into one execution plan ... and you cannot alter and update in the same sql batch ... Transaction has no effect on a sql batch ...|||I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

update table99 set a=b, b=a

with an optional where condition.

Not a shining moment for the gurus of dbforums.com...|||Yep. For those geeky enough to care, the ISO standard calls for all of the RValues in an UPDATE statement to evaluate before any of the LValues are resolved. This means that all of the computations have to be complete before the first change is allowed to happen.

-PatP

cells not diplaying

I have 2 dimensions dimA and dimB. Dim A has members Aa and Ab while dimB has members Ba, Bb and Bc. The fact table has columns dimAKey, DimBkey, CountofX.

I need to display a result that shows counts for all members for dimA (whether there is a value in CountofX or not) but for dimB I only want to see the members that have values. eg.

DimA DimB CountofX
-
Aa Ba 3
Aa Bb 4
Aa Bc 2
Ab Ba 7
Ab <blank> <blank>
Ab Bc 3

Can someone help me?

Thanks

Your example does not really make sense, you might have simplified it a bit too much, you could get something like the following, but it does not sound like this is what you are after.

DimA DimB CountofX
-
Aa Ba 3
Aa Bb 4
Aa Bc 2
Ab Ba 7
Ab Bb <blank>
Ab Bc 3

Assuming that you had a value of Ac another alternative would be something like the following, where effectively you get every value of DimA against DimB.All and also all the members of DimB that have values.

DimA DimB CountofX
-
Aa Ba 3
Aa Bb 4
Aa Bc 2
Aa All 9
Ab Ba 7
Ab Bc 3
Ab All 10
Ac All <blank>

Would something like this suit your requirements?

|||I'll try it. Please post the syntax|||

Basically I would create two sets on the row axis, union them and then put them back in their hierarchy order. I don't know what your cube is called or what sort of hierarchy attribute structure you have, but in psuedo code, it would be something like the following (which is SSAS 2005 syntax).

SELECT
{Measures.CountofX} ON COLUMNS
Hierarchize(UNION({DimA.<Hierarchy>.Members} * {DimB.<Hierarchy>.[All]}
, NONEMPTY({DimA.<Hierarchy>.Members} * DimB.<Hierarchy>.Members)),POST) ON ROWS
FROM <Cube>

|||

I could not get the result of

Ac All <blank>

to show up. If CountofX is null for all members of dimB against value Ac, that whole line does not even show up. I need to be able to see all members of dimA at all times regardless of whether intersection with dimB members has values or not. However, for dimB I want to see only those members that have values at intersection with dimA. I hope I have conveyed my problem well enough for you to help me. I am using SSAS 2005

Thanks,

newtomdx

|||

I can't think how any of the tuples involving dimA and the all member of dimB could be excluded, unless your query tool is doing some extra filtering.

What are you using to run the query?

Can you try running it using SQL Server Management Studio?

Can you post the exact query you are running?

|||Yes, there was a filter. It does work correctly once I removed it. Is there any way I can just have all the members of dimA and show members of dimB that have values in countofX without the "All"? Thanks for your help.|||No, in multi-dimensional space (at least in SSAS 2005) there is no such thing as a null value in a dimension. Even if you were not displaying dimB at all, technically the cube is still returning the value that relates to the default member in dimB (which in this case is "All"). If you are using some sort of reporting tool like SSRS, you might be able to surpress the output of the all label.

cdata all columns automatically

I have an application that is extremely dynamic.
Users can type in their own sql statements and run queries however they
want.
Is there a way using SQL to automaticall wrap all the columns with a
CDATA notation?
EX:
Query
SELECT field1, field2, field3 FROM myTable FOR XML ...
Desired result
<row>
<field1><![CDATA[field1 data]]></field1>
<field2><![CDATA[field2 data]]></field2>
<field3><![CDATA[field3 data]]></field3>
</row>
**PLEASE NOTE** I have no control of the SELECT statement, the users
can select whatever they want. So i cannot use the regular EXPLICIT
col!cdata notation :(And why would you want to wrap the data into a CDATA section? CDATA sections
are there for lazy hand authors of XML documents that do not want to write
< or & everytime they need to write < or &. On the output-side CDATA
should not make any difference whatsoever since FOR XML automatically does
the needed entitization.
Best regards
Michael
"jamesd" <jamesd@.ring4freedom.com> wrote in message
news:1164860203.165600.219350@.j44g2000cwa.googlegroups.com...
>I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation :(
>