How to change collation on MODEL ?

> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
You have to rebuild the system databases, as you cannot have different colla
tions across the system
databases. This is done using the SETUP program in 2005. This is described i
n
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-
78a57852364d.htm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?I have a 2005 SP1 server with the wrong server collation.
All user databases on this server have the right collation.
I am getting collation conflict errors when I use temp tables.
I believe this is because tempdb is created based on "model", and model has
the server collation.
I have tried to change the "model" collation using ALTER DATABASE, but I get
the error message "Cannot alter the database 'model' because it is a system
database.
"
How can I force tempdb to have a specific collation, instead of the same
collation as the server collation?|||> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
You have to rebuild the system databases, as you cannot have different colla
tions across the system
databases. This is done using the SETUP program in 2005. This is described i
n
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-
78a57852364d.htm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?|||Hi Pat,
Thank you for your posting!
I agreed with Tibor. You need to rebuild the system database.
Also, here is an article for your reference.
325335 How to transfer a database from one collation to another collation
in SQL Server
http://support.microsoft.com/defaul...kb;EN-US;325335
Hope this will be helpful!
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 Pat,
Thank you for your posting!
I agreed with Tibor. You need to rebuild the system database.
Also, here is an article for your reference.
325335 How to transfer a database from one collation to another collation
in SQL Server
http://support.microsoft.com/defaul...kb;EN-US;325335
Hope this will be helpful!
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.|||The doc mentions that all service packs and hotfixes are lost when rebuildin
g
the system databases, and need to be re-applied.
We have SP1 and several hotfixes, this sounds like a lot of work for just
changing the collation.
"Tibor Karaszi" wrote:

> You have to rebuild the system databases, as you cannot have different col
lations across the system
> databases. This is done using the SETUP program in 2005. This is described
in
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a42
0-78a57852364d.htm.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>|||> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
Yep, that is why I always advocate to be very careful when selecting the col
lation during
installation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:DFA53097-0EB5-4125-A9EC-E0EF157212D9@.microsoft.com...[vbcol=seagreen]
> The doc mentions that all service packs and hotfixes are lost when rebuild
ing
> the system databases, and need to be re-applied.
> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
> "Tibor Karaszi" wrote:
>|||The doc mentions that all service packs and hotfixes are lost when rebuildin
g
the system databases, and need to be re-applied.
We have SP1 and several hotfixes, this sounds like a lot of work for just
changing the collation.
"Tibor Karaszi" wrote:

> You have to rebuild the system databases, as you cannot have different col
lations across the system
> databases. This is done using the SETUP program in 2005. This is described
in
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a42
0-78a57852364d.htm.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>|||> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
Yep, that is why I always advocate to be very careful when selecting the col
lation during
installation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:DFA53097-0EB5-4125-A9EC-E0EF157212D9@.microsoft.com...[vbcol=seagreen]
> The doc mentions that all service packs and hotfixes are lost when rebuild
ing
> the system databases, and need to be re-applied.
> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
> "Tibor Karaszi" wrote:
>|||"Pat" wrote:

> I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
In addition to the other replies to your query, there may be another way to
do this.
If you create a new temporary table in a stored procedure, use the good
collation.
example:
Create Table #Status
(
State nvarchar(128) collate database_default
)
This might be a good solution if there are not too many places where
temporary tables are created.
hope this helps.
kind regards,
Bart.

How to call job step X from job step Y:

I have a SQL Server Agent job with multiple steps. I want
to be able to goto a specific job steps by choice from a
step within the job. How do I make a acll to another step
from a job step.
For example, based on data returned in step 5, I could go
to step 1, 2, 3 or 4. 'OnSuccess', OnFailure' flow is not
good enough in the given scenario. I don't want to use
sp_start_job to start the job at a specific step because
the job is already running and I just want to set the
control-of-flow to a different step.
Thanks
Abdul Gill
DBA/Royal Caribbean Cruise LimitedHi Abdul
Job Step control does limit your options for controlling process flow, so
how about using a DTS package? DTS gives you easy control over process flow
& can be scheduled into a SQL Agent Job as well. The tools for implementing
complex processes are far more advanced in DTS, there's even a GUI to
support visualisation of the process logic..
Regards,
Greg Linwood
SQL Server MVP
"Abdul M. Gill" <agill@.rccl.com> wrote in message
news:1474e01c41381$3b8f6a20$a101280a@.phx
.gbl...
> I have a SQL Server Agent job with multiple steps. I want
> to be able to goto a specific job steps by choice from a
> step within the job. How do I make a acll to another step
> from a job step.
> For example, based on data returned in step 5, I could go
> to step 1, 2, 3 or 4. 'OnSuccess', OnFailure' flow is not
> good enough in the given scenario. I don't want to use
> sp_start_job to start the job at a specific step because
> the job is already running and I just want to set the
> control-of-flow to a different step.
> Thanks
> Abdul Gill
> DBA/Royal Caribbean Cruise Limited

How to calculate the percentage?

In my cube, I've a date dimension and a time dimension. I would like to know how can I calculate the percentage of order count for a specific time?

I can get this.

Hour11/6/0611/7/069am601010am801011am6020

But how can I get this.

Hour11/6/0611/7/06

9am30%25%

10am40%25%

11am30%50%

I would like to use the calculation feature in available in the cube. How can I do this? Thanks!

Depends on whether the percentage is always based on the time hierarchy, regardless of which query axis it lies on, or is based on whichever hierarchy is on rows - Axis(1). In the former case, something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [TimeOfDay].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

Thanks for your reply but there are something that I don't understand. In your code, you use [TimeOfDay].Parent in the member calculation. However, in my own cube, the date dimension and time dimension are 2 separate dimensions. So, I don't expect my TimeOfDay.Parent will get the expected set.

The following is my Date and Time dimensions structure.
DimDate - The Date dimension is populated by extracting data from my OLTP.
Year
Month
DayOfMonth
Date
DayNameOfWeek

DimTime - The Time dimension is generated by cross join all the available hour, minute and second. (No. of records: 24 x 60 x 60)
Hour
Minute
Second

P.S. I'm quite new to BI and datawarehouse. If my Time hierarchy is not correct or using best practice, please let me know so that I can improve it.

Regards,
Alex|||

Hi Alex,

Are you using AS 2000 or AS 2005 - if it's AS 2000, then something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [DimTime].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

I'm using AS 2005. The problem that I don't understand what [DimTime].Parent is pointing to. The Time dimension and Date dimension doesn't have any direct relationship in my cube. Is there any design fault?

Regards,
Alex|||

Alex,

With AS 2005, the hierarchy should also be specified with [DimTime], so [DimTime].[TimeHierarchy].Parent points to the parent of the current [DimTime] member. For example, the parent of the hour "01" will be [DimTime].[TimeHierarchy].[All].

http://msdn2.microsoft.com/en-us/library/ms145513.aspx

>>

SQL Server 2005 Books Online

Parent (MDX)

Updated: 17 July 2006

Returns the parent of a member.

...

>>

How to calculate the percentage?

In my cube, I've a date dimension and a time dimension. I would like to know how can I calculate the percentage of order count for a specific time?

I can get this.

Hour11/6/0611/7/069am601010am801011am6020

But how can I get this.

Hour11/6/0611/7/06

9am30%25%

10am40%25%

11am30%50%

I would like to use the calculation feature in available in the cube. How can I do this? Thanks!

Depends on whether the percentage is always based on the time hierarchy, regardless of which query axis it lies on, or is based on whichever hierarchy is on rows - Axis(1). In the former case, something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [TimeOfDay].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

Thanks for your reply but there are something that I don't understand. In your code, you use [TimeOfDay].Parent in the member calculation. However, in my own cube, the date dimension and time dimension are 2 separate dimensions. So, I don't expect my TimeOfDay.Parent will get the expected set.

The following is my Date and Time dimensions structure.
DimDate - The Date dimension is populated by extracting data from my OLTP.
Year
Month
DayOfMonth
Date
DayNameOfWeek

DimTime - The Time dimension is generated by cross join all the available hour, minute and second. (No. of records: 24 x 60 x 60)
Hour
Minute
Second

P.S. I'm quite new to BI and datawarehouse. If my Time hierarchy is not correct or using best practice, please let me know so that I can improve it.

Regards,
Alex|||

Hi Alex,

Are you using AS 2000 or AS 2005 - if it's AS 2000, then something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [DimTime].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

I'm using AS 2005. The problem that I don't understand what [DimTime].Parent is pointing to. The Time dimension and Date dimension doesn't have any direct relationship in my cube. Is there any design fault?

Regards,
Alex|||

Alex,

With AS 2005, the hierarchy should also be specified with [DimTime], so [DimTime].[TimeHierarchy].Parent points to the parent of the current [DimTime] member. For example, the parent of the hour "01" will be [DimTime].[TimeHierarchy].[All].

http://msdn2.microsoft.com/en-us/library/ms145513.aspx

>>

SQL Server 2005 Books Online

Parent (MDX)

Updated: 17 July 2006

Returns the parent of a member.

...

>>

How to calculate AVG. Disk Queue Length

Hi All,

we have collected perfmon data for a specific lun. Here is the background of the lun. The lun is Raid10 with 4 physical disks. We have problems interpreting the data. In the perfmon counter screen we have a max of 435 and average of 0.512. Can somebody tell us what is that we are missing? Any help is greatly appreciated.

Thanks,

Venkat.

In theory the 'magic' number for avg. disk queue length is 2 per spindle.

There are however counters which are more interesting like avg. disk sec/read and avg.disk sec/write which should be below 20ms for data disks and below 3ms for log disks.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

thanks for answering. Remember, when you look at the PerfmonCounters, you always have them in units as 1.00 or 100. how do you correlate those units with the actual readings you are seeing?

|||

The value is always in the unit the counter mentions. The graphs can have a different scale and this can be changed through the properties.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

How to calculate AVG. Disk Queue Length

Hi All,

we have collected perfmon data for a specific lun. Here is the background of the lun. The lun is Raid10 with 4 physical disks. We have problems interpreting the data. In the perfmon counter screen we have a max of 435 and average of 0.512. Can somebody tell us what is that we are missing? Any help is greatly appreciated.

Thanks,

Venkat.

In theory the 'magic' number for avg. disk queue length is 2 per spindle.

There are however counters which are more interesting like avg. disk sec/read and avg.disk sec/write which should be below 20ms for data disks and below 3ms for log disks.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

thanks for answering. Remember, when you look at the PerfmonCounters, you always have them in units as 1.00 or 100. how do you correlate those units with the actual readings you are seeing?

|||

The value is always in the unit the counter mentions. The graphs can have a different scale and this can be changed through the properties.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com