Changing table properties

Hi,
I have got approximately 100 tables. Their all attributes are same. But, I w
ant to change their one attribute.
Such as my table;
NAME nvarchar(50)
SNAME nvarchar(50)
SALARY float
I want to change SALARY's property from float to money. It is possible to th
is operation manually. But I need to this operation automaticallay via progr
amming language or via query analizer.
How can I do?Why would you want 100 identical tables? How about combining them into one.
If you have no choice but to support the current design then use a query out
of INFORMATION_SCHEMA.TABLES to generate the script.
SELECT 'ALTER TABLE '+
QUOTENAME(table_schema)+'.'+
QUOTENAME(table_name)+' ALTER COLUMN salary DECIMAL(10,2)'
FROM information_schema.tables
WHERE table_name LIKE ?
Now just cut and paste the results into Query Analyzer.
I recommend you avoid using MONEY for the salary column because MONEY has
some problems with precision in arithmetic operations. Use DECIMAL/NUMERIC
instead.
David Portas
SQL Server MVP
--|||Hi
I would do it slightly differently!!!
SELECT 'ALTER TABLE '+
QUOTENAME(table_schema)+'.'+
QUOTENAME(table_name)+' ALTER COLUMN ' + QUOTENAME(COLUMN_NAME) + '
DECIMAL(10,2)'
FROM information_schema.columns
WHERE COLUMN_NAME = 'Salary'
John
"David Portas" wrote:

> Why would you want 100 identical tables? How about combining them into one
.
> If you have no choice but to support the current design then use a query o
ut
> of INFORMATION_SCHEMA.TABLES to generate the script.
> SELECT 'ALTER TABLE '+
> QUOTENAME(table_schema)+'.'+
> QUOTENAME(table_name)+' ALTER COLUMN salary DECIMAL(10,2)'
> FROM information_schema.tables
> WHERE table_name LIKE ?
> Now just cut and paste the results into Query Analyzer.
> I recommend you avoid using MONEY for the salary column because MONEY has
> some problems with precision in arithmetic operations. Use DECIMAL/NUMERIC
> instead.
> --
> David Portas
> SQL Server MVP
> --
>
>

Changing table properties

Hi,
I have got approximately 100 tables. Their all attributes are same. But, I w
ant to change their one attribute.
Such as my table;
NAME nvarchar(50)
SNAME nvarchar(50)
SALARY float
I want to change SALARY's property from float to money. It is possible to th
is operation manually. But I need to this operation automaticallay via progr
amming language or via query analizer.
How can I do?See http://tinyurl.com/a3f26
"huseyin_akturk" wrote:

> Hi,
> I have got approximately 100 tables. Their all attributes are same.
> But, I want to change their one attribute.
> Such as my table;
> NAME nvarchar(50)
> SNAME nvarchar(50)
> SALARY float
> I want to change SALARY's property from float to money. It is possible
> to this operation manually. But I need to this operation automaticallay
> via programming language or via query analizer.
> How can I do?
>
> --
> huseyin_akturk
> ---
> Posted via http://www.codecomments.com
> ---
>

Changing table colon's properties

Hi,
I have got approximately 100 tables. Their all attributes are same. But, I
want to change their one attribute.
Such as my table;
NAME nvarchar(50)
SNAME nvarchar(50)
SALARY float
I want to change SALARY's property from float to money. It is possible to th
is operation manually. But I need to this operation automaticallay via
programming language or via query analizer.
How can I do?huseyin_akturk wrote:
> Hi,
> I have got approximately 100 tables. Their all attributes are same.
> But, I want to change their one attribute.
> Such as my table;
> NAME nvarchar(50)
> SNAME nvarchar(50)
> SALARY float
> I want to change SALARY's property from float to money. It is
> possible to th is operation manually. But I need to this operation
> automaticallay via programming language or via query analizer.
> How can I do?
You can add a new column Salary2 Money, Update the table and move all
the float values into the new money type (cast the value and round as
needed), drop the old column, and then rename the new column. Test in
dev before applying to production. You may also want to consider using a
DECIMAL(X) or INT for the salary, especially if you want a fixed number
of decimal places (say 2) or do not require decimal places at all.
The following example moves the salary values directly from one column
to the other.
Create Table dbo.SalaryTestXXX (
MyID INT IDENTITY NOT NULL PRIMARY KEY,
Salary FLOAT NULL )
go
Insert Into dbo.SalaryTestXXX Values (25000)
Insert Into dbo.SalaryTestXXX Values (50000)
Insert Into dbo.SalaryTestXXX Values (75000)
Insert Into dbo.SalaryTestXXX Values (100000)
Select * from dbo.SalaryTestXXX
go
Alter Table dbo.SalaryTestXXX
Add Salary2 Money
go
Select * from dbo.SalaryTestXXX
Update dbo.SalaryTestXXX
Set Salary2 = Salary
Where Salary IS NOT NULL
go
Select * from dbo.SalaryTestXXX
go
Alter Table dbo.SalaryTestXXX
Drop Column Salary
go
Select * from dbo.SalaryTestXXX
Exec sp_rename 'dbo.SalaryTestXXX.Salary2', 'Salary', 'COLUMN'
Select * from dbo.SalaryTestXXX
Drop Table dbo.SalaryTestXXX
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Are you sure you want to change it to MONEY? MONEY data type might not be
exactly what you think it is. You might want to use NUMERIC(18, 2) or
NUMERIC(18, 4) or some other variation of NUMERIC instead of MONEY... MONEY
stores currency display information in the same column as the value, which
can cause headaches.
"huseyin_akturk" <huseyinakturk@.discussions.microsoft.com> wrote in message
news:7C2B0E2B-FEA8-4BA2-8777-CC1D0DED37E3@.microsoft.com...
> Hi,
> I have got approximately 100 tables. Their all attributes are same. But, I
> want to change their one attribute.
> Such as my table;
> NAME nvarchar(50)
> SNAME nvarchar(50)
> SALARY float
> I want to change SALARY's property from float to money. It is possible to
> th
> is operation manually. But I need to this operation automaticallay via
> programming language or via query analizer.
> How can I do?
>|||See if this helps:
use tempdb
go
create table t1 (
salary float
)
go
insert into t1 values(1245.07)
go
select * from t1
go
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cn sysname
declare columns_cursor cursor local fast_forward
for
select
table_schema,
table_name
from
information_schema.columns
where
column_name = 'salary'
and data_type = 'float'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
open columns_cursor
while 1 = 1
begin
fetch next from columns_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'alter table ' + quotename(@.ts) + '.' + quotename(@.tn) + N'
alter column salary money'
print @.sql
exec sp_executesql @.sql
end
close columns_cursor
deallocate columns_cursor
go
select * from t1
go
drop table t1
go
AMB
"huseyin_akturk" wrote:

> Hi,
> I have got approximately 100 tables. Their all attributes are same. But, I
> want to change their one attribute.
> Such as my table;
> NAME nvarchar(50)
> SNAME nvarchar(50)
> SALARY float
> I want to change SALARY's property from float to money. It is possible to
th
> is operation manually. But I need to this operation automaticallay via
> programming language or via query analizer.
> How can I do?
>

Changing Ordering of a Dimension Attribute

I have a dimension attribute called Month. It is sorting alphabetically instead of based on it's underlying key value. How do I change this?

Thanks,

ChrisChange the order by from "Name" to "Key". No idea what "Attribute Name" and "Attribute Key" do however.|||If you choose attribute name, you can define the order by another field.
I have in each dimension an order field and I have configured each dimension to sort by this field. By default I fill in this field with name. But if a user request another order I can change values in this field without changing dimension structure.

Changing FormatString for attribute of a dimension

i can't change the value of a format string for attributes of dimensions.

For Cubes is easy, right-click on the measure -> Proprieties -> FormatString

but for the attributes of a dimension I cannot find where I can change this proprieties..

Someone can help me?

thank you

Riccardo

You cannot find it, because there isn't FormatString for attributes. FormatString is a property which applies to cell values, so specifying different formatting for different attributes doesn't make much sense, as they will simply conflict with each other when applied to the cell. You can specify different formatting for different attribute members, however. Check the CustomRollupOptions property, which allows you to do so.|||

i can't do what i want to do :)

my problem is: I have an attribute in a dimension which assumes values from 0.0001 to 0.0099

I don't know why but this attribute when I browse the dimension is visualized as ".0001" (without the zero)

I want this zero!! :)

ho can I to make it appear with CustomRollupOptions property!?!

|||

Is it not possible to do a named calculation in the data source view and use it in the dimension?

You use TSQL like:

Case When MyColumn < = 0.001 Then 0

Else MyColumn

HTH

Thomas Ivarsson

|||

I think wildthink wants a leading zero, i.e. 0.0001 instead of .0001

Anyway, this is formatting of attribute members, not formatting of cell values, therefore FormatString is not applicable here. You should instead, as Thomas suggests, put a named calculation in DSV which will do formatting for you, using appropriate SQL functions.

|||

>I think wildthink wants a leading zero, i.e. 0.0001 instead of .0001

yes!!

thank you for your answers.

but now i have a new question..

how can I make a named calculation for obtained what I want?

In DSV if I do "Explore Data" on the table of my dimension I can see my attribute correctly (with the leading zero), how create a named calculation to see this zero also when I browse the dimension?

thank you again!

Riccardo

|||

If you can accept a string in the dimension you could try

Named Calculation = MyColumnText

Case When MyColumn between 0.0001 and 0.0099 Then '0' + Cast(MyColumn as Char(4))

Else Cast(MyColumn as char(4))

End

I am not sure about what you would like to do with the other values that are outside of the TSQL-Case here.

This is from memory only so you might have to try:

'0.' + Cast(MyColumn as Char(4))

HTH

Thomas Ivarsson

|||

>If you can accept a string in the dimension you could try

your solution works :)

thank you very much!

Changing FormatString for attribute of a dimension

i can't change the value of a format string for attributes of dimensions.

For Cubes is easy, right-click on the measure -> Proprieties -> FormatString

but for the attributes of a dimension I cannot find where I can change this proprieties..

Someone can help me?

thank you

Riccardo

You cannot find it, because there isn't FormatString for attributes. FormatString is a property which applies to cell values, so specifying different formatting for different attributes doesn't make much sense, as they will simply conflict with each other when applied to the cell. You can specify different formatting for different attribute members, however. Check the CustomRollupOptions property, which allows you to do so.|||

i can't do what i want to do :)

my problem is: I have an attribute in a dimension which assumes values from 0.0001 to 0.0099

I don't know why but this attribute when I browse the dimension is visualized as ".0001" (without the zero)

I want this zero!! :)

ho can I to make it appear with CustomRollupOptions property!?!

|||

Is it not possible to do a named calculation in the data source view and use it in the dimension?

You use TSQL like:

Case When MyColumn < = 0.001 Then 0

Else MyColumn

HTH

Thomas Ivarsson

|||

I think wildthink wants a leading zero, i.e. 0.0001 instead of .0001

Anyway, this is formatting of attribute members, not formatting of cell values, therefore FormatString is not applicable here. You should instead, as Thomas suggests, put a named calculation in DSV which will do formatting for you, using appropriate SQL functions.

|||

>I think wildthink wants a leading zero, i.e. 0.0001 instead of .0001

yes!!

thank you for your answers.

but now i have a new question..

how can I make a named calculation for obtained what I want?

In DSV if I do "Explore Data" on the table of my dimension I can see my attribute correctly (with the leading zero), how create a named calculation to see this zero also when I browse the dimension?

thank you again!

Riccardo

|||

If you can accept a string in the dimension you could try

Named Calculation = MyColumnText

Case When MyColumn between 0.0001 and 0.0099 Then '0' + Cast(MyColumn as Char(4))

Else Cast(MyColumn as char(4))

End

I am not sure about what you would like to do with the other values that are outside of the TSQL-Case here.

This is from memory only so you might have to try:

'0.' + Cast(MyColumn as Char(4))

HTH

Thomas Ivarsson

|||

>If you can accept a string in the dimension you could try

your solution works :)

thank you very much!