Changing the caption of a member name in a Named Set

I have created a named set that includes a list of five customers. One of the customers is named "XYZ Corp.". For the named set, I want to display the customer with a name of "XYZ Corporation". Could someone supply me with the syntax needed to do this? Thank you.

David

Normally it is not possible to change captions/names of the existing hierarchy members. The usual workaround is to create calculated member which aliases the original member. This, however, has couple of implications, for example Exists rules for the calculation member are different etc.

CREATE Customers.[XYZ Corporation] = Customers.[XYZ Corp];

CREATE SET NamedSet AS { ..., [XYZ Corporation], ... };

|||

Mosha,

If there are implications when creating an alias to be used in a named set, would it be better to handle this issue in the reporting client rather than in Analysis Services. For example, I can rename an item in an Excel 2007 pivot table connected to an AS cube. The renamed item becomes a caption while the MDX still points to the original member name in the cube.

The only problem with this approach is that I would have to repeat this process for each report instead of doing it once as part of the named set.

David

|||This is really up to you which approach to take. I think you recognize now advantages and disadvantages of each approach so you can make informed decision.

Changing SQL Service account passowrd on a cluster configuration

Hi,
I have SQL 2005 server (with Named Instance) running on a two node Cluster
Configuration (Active/Passive).
SQL Instance, SQL Server Agent, SQL Server Browser services are running
under a service account (domain account). Similarly Cluster service is also
running under a cluster service account (domain account).
I am looking for accurate steps to update password of SQL Service account
and Cluster service account that will cause minimum disruption of these
services. If there’s a link to documentation on how to update password, that
will be most useful.
For the cluster service account password:
http://support.microsoft.com/kb/305813/en-us
For the SQL 2005 Instance, use the SQL Configuration Manager to change the
password. It handles the "cluster magic".
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Mehul" <Mehul@.discussions.microsoft.com> wrote in message
news:C4EE5D43-00E3-4322-92C2-538B8CDEC12D@.microsoft.com...
> Hi,
> I have SQL 2005 server (with Named Instance) running on a two node Cluster
> Configuration (Active/Passive).
> SQL Instance, SQL Server Agent, SQL Server Browser services are running
> under a service account (domain account). Similarly Cluster service is
> also
> running under a cluster service account (domain account).
> I am looking for accurate steps to update password of SQL Service account
> and Cluster service account that will cause minimum disruption of these
> services. If there’s a link to documentation on how to update password,
> that
> will be most useful.
>
|||It could be just me, but I had problem using Configuration Manager to change
the SQL service account password from time to time. I don't remember the
exact error message now, but I remember not having success in getting the
change replicated among the nodes.
I've had more success with just using the services.msc mgmt console to
change the SQL service account password on each node. This is just a hassle
since on each node there are a few services to change. I know Configuration
Manager is not cluster aware, but had hoped that at least any change made
through it to the SQL registry entries would be automatically replicated by
the cluster service.
Linchi
"Geoff N. Hiten" wrote:

> For the cluster service account password:
> http://support.microsoft.com/kb/305813/en-us
> For the SQL 2005 Instance, use the SQL Configuration Manager to change the
> password. It handles the "cluster magic".
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Mehul" <Mehul@.discussions.microsoft.com> wrote in message
> news:C4EE5D43-00E3-4322-92C2-538B8CDEC12D@.microsoft.com...
>
|||Thanks Geoff. But I would really like to know what are the steps to perform.
Do I need to change password on both the nodes or only on the active node ?
Once the password for the account is updated in AD, is it immediately
reflected on the SQL servers ?
"Geoff N. Hiten" wrote:

> For the cluster service account password:
> http://support.microsoft.com/kb/305813/en-us
> For the SQL 2005 Instance, use the SQL Configuration Manager to change the
> password. It handles the "cluster magic".
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Mehul" <Mehul@.discussions.microsoft.com> wrote in message
> news:C4EE5D43-00E3-4322-92C2-538B8CDEC12D@.microsoft.com...
>
|||Linchi, Thanks for your feedback.
So what steps do you follow to update password for all these services using
services.msc ?
"Linchi Shea" wrote:
[vbcol=seagreen]
> It could be just me, but I had problem using Configuration Manager to change
> the SQL service account password from time to time. I don't remember the
> exact error message now, but I remember not having success in getting the
> change replicated among the nodes.
> I've had more success with just using the services.msc mgmt console to
> change the SQL service account password on each node. This is just a hassle
> since on each node there are a few services to change. I know Configuration
> Manager is not cluster aware, but had hoped that at least any change made
> through it to the SQL registry entries would be automatically replicated by
> the cluster service.
> Linchi
> "Geoff N. Hiten" wrote:
|||Here are the steps I went through recently to update the password for the SQL
service account (the previous password expired. It shouldn't be set to
expire, but that's a different story):
1. Remote desktop to each node
2. Start -> Run, type services.msc
3. In the service list, locate all the SQL Server related services that use
the password.
4. Double click on each such service
5. Click on the "Log On' tab.
6. Type in the new password in the Password and Confirm Password textboxes.
7. Click on Apply and OK.
Linchi
"Mehul" wrote:
[vbcol=seagreen]
> Linchi, Thanks for your feedback.
> So what steps do you follow to update password for all these services using
> services.msc ?
> "Linchi Shea" wrote:
|||LInchi's advice on using the services applet on each node is a bit more
complex, but it is a certaintly.
As for when changes "take", AD may need up to fifteen minutes to replicate
the password change through the system when you have multiple controllers.
If an account is logged in to a resource, such as a service account already
running, you can leave it running or a while before changing it. The system
will not force it out immediately but the application may no longer be able
to access network resources after some time.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Mehul" <Mehul@.discussions.microsoft.com> wrote in message
news:7F18E2CD-E089-4642-898E-FB303324181E@.microsoft.com...[vbcol=seagreen]
> Thanks Geoff. But I would really like to know what are the steps to
> perform.
> Do I need to change password on both the nodes or only on the active node
> ?
> Once the password for the account is updated in AD, is it immediately
> reflected on the SQL servers ?
> "Geoff N. Hiten" wrote:
|||Linchi,
Here's what worked for me. Quite similar to the steps you have outlined:
1.Changed SQL Service account password in AD. Waited for about half an hour
so that password change gets replicated to all the domain controllers.
2.Updated the password on the active node of the SQL Cluster using SQL
Configuration manager for all the services.
3.Restarted the services using SCM. Everything looked fine till now.
4.On the passive node, using Services MMC, manually updated password for
the SQL services.
5.Fail over the cluster from node 1 to node 2, everything worked fine with
no errors.
I will be blogging these steps, but hope that other people who are in the
same situation will find this discussion useful.
Thanks Geoff and Linchi for your quick responses.
"Linchi Shea" wrote:
[vbcol=seagreen]
> Here are the steps I went through recently to update the password for the SQL
> service account (the previous password expired. It shouldn't be set to
> expire, but that's a different story):
> 1. Remote desktop to each node
> 2. Start -> Run, type services.msc
> 3. In the service list, locate all the SQL Server related services that use
> the password.
> 4. Double click on each such service
> 5. Click on the "Log On' tab.
> 6. Type in the new password in the Password and Confirm Password textboxes.
> 7. Click on Apply and OK.
> Linchi
> "Mehul" wrote:
|||I'm glad that you included step 5 to failover the SQL group among the nodes.
That's an abosolutely critical step to close loop the whole task.
Linchi
"Mehul" wrote:
[vbcol=seagreen]
> Linchi,
> Here's what worked for me. Quite similar to the steps you have outlined:
> 1.Changed SQL Service account password in AD. Waited for about half an hour
> so that password change gets replicated to all the domain controllers.
> 2.Updated the password on the active node of the SQL Cluster using SQL
> Configuration manager for all the services.
> 3.Restarted the services using SCM. Everything looked fine till now.
> 4.On the passive node, using Services MMC, manually updated password for
> the SQL services.
> 5.Fail over the cluster from node 1 to node 2, everything worked fine with
> no errors.
> I will be blogging these steps, but hope that other people who are in the
> same situation will find this discussion useful.
> Thanks Geoff and Linchi for your quick responses.
>
> "Linchi Shea" wrote:

Changing port numbers

On an active/active cluster what is the proper way of changing the port # on
the named instance after an install is complete and in use.
Use the Server Network Utility to assign a new port number. The utility is
not cluster-aware so make the changes on all nodes in the cluster
individually. SQL server requires a service stop-start to change port
numbers.
Geoff N, Hiten
Microsoft SQL Server MVP
"zach_john" <zach_john@.discussions.microsoft.com> wrote in message
news:01F5E155-2002-4C8A-8549-80DA732C7B7F@.microsoft.com...
> On an active/active cluster what is the proper way of changing the port #
> on
> the named instance after an install is complete and in use.
|||Thanks Geoff - just wanted to hear that confirmation.
Cheers,
Zach
"Geoff N. Hiten" wrote:

> Use the Server Network Utility to assign a new port number. The utility is
> not cluster-aware so make the changes on all nodes in the cluster
> individually. SQL server requires a service stop-start to change port
> numbers.
> Geoff N, Hiten
> Microsoft SQL Server MVP
> "zach_john" <zach_john@.discussions.microsoft.com> wrote in message
> news:01F5E155-2002-4C8A-8549-80DA732C7B7F@.microsoft.com...
>
>
|||It's always easier to learn from someone else's mistakes.
GNH
"zach_john" <zach_john@.discussions.microsoft.com> wrote in message
news:9C40F75E-2E21-4026-9A34-CF4857CF78DD@.microsoft.com...[vbcol=seagreen]
> Thanks Geoff - just wanted to hear that confirmation.
> Cheers,
> Zach
> "Geoff N. Hiten" wrote:

Changing data type from Char to Datetime

Thanks in adance

Platform: SQL 2000

A SQL table has a field named "pay-day" with Char(8) data type.

I tried to change its data type to datetime but only with an error message like this. I did right-click the table and tried to modify a data type.

- Unable to modify table.
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Jay

This means that some of your data in that column cannot be implicitly converted to a datetime value.

Here is some more info on CAST and CONVERT -- you may want to try using these functions specifically to help identify which data is out of range.

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

Hope that helps

-Steve

|||

Being that all of the data in your table is valid date data:

It seems that your field (8) has a date like mmyydd if this is correct,

It would seem that SQL thinks this is a number which it want's to convert to a date.

Create new field and write a stored proceidure to update the new field to your field converted to date.

format your field using format(yourField,"mmyydd") then insert into the new date field.

When you convert SQL has to know what date format you are starting from.

|||

The conversion is not working because some of the data has non-conforming values -values that do NOT convert to valid dates.

You will need to locate the non-conforming values and correct them first. The following query should help you find them.

SELECT [Pay-Day]
FROM MyTable
WHERE isdate( [Pay-Day] ) = 0

changing data source on deploy?

We developed all our reports (a bunch of them) using our development server
named "development" which is not a local server... meaning we cant set our
connection string to localhost when we make them... but then we need to
deploy them to our Live server which then can be localhost as they will only
run on that server... how can we change the connection string's server upon
deployment? also we need to change the traget server to the live one instead
of development... can this be scripted or do we have to change the
connection string manually each and every time we make a change on
development and redeploy to the live server? thanks!The default when deploying is to not overwrite data sources. That means you
only have to change things the first time you deploy to production, after
that you are good to go. No more changes necessary.
As far as target server, in VS you pick the solution configuration, each
solution configuration has its own target server. So you just select the
configuration you want that is appropriate and then deploy to it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Smokey Grindle" <nospam@.dontspamme.com> wrote in message
news:%23AamjiKRHHA.496@.TK2MSFTNGP06.phx.gbl...
> We developed all our reports (a bunch of them) using our development
> server named "development" which is not a local server... meaning we cant
> set our connection string to localhost when we make them... but then we
> need to deploy them to our Live server which then can be localhost as they
> will only run on that server... how can we change the connection string's
> server upon deployment? also we need to change the traget server to the
> live one instead of development... can this be scripted or do we have to
> change the connection string manually each and every time we make a change
> on development and redeploy to the live server? thanks!
>|||Awesome thanks, but what about for integrated data sources? I found a script
in the same scripts that seems to change the data source its called
PublishSampleReports.rss would this work for changing reports that have
integrated non shared data sources? thanks!
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eN1imwKRHHA.912@.TK2MSFTNGP06.phx.gbl...
> The default when deploying is to not overwrite data sources. That means
> you only have to change things the first time you deploy to production,
> after that you are good to go. No more changes necessary.
> As far as target server, in VS you pick the solution configuration, each
> solution configuration has its own target server. So you just select the
> configuration you want that is appropriate and then deploy to it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Smokey Grindle" <nospam@.dontspamme.com> wrote in message
> news:%23AamjiKRHHA.496@.TK2MSFTNGP06.phx.gbl...
>> We developed all our reports (a bunch of them) using our development
>> server named "development" which is not a local server... meaning we cant
>> set our connection string to localhost when we make them... but then we
>> need to deploy them to our Live server which then can be localhost as
>> they will only run on that server... how can we change the connection
>> string's server upon deployment? also we need to change the traget server
>> to the live one instead of development... can this be scripted or do we
>> have to change the connection string manually each and every time we make
>> a change on development and redeploy to the live server? thanks!
>|||I don't know about that. I always used shared data sources.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Smokey Grindle" <nospam@.dontspamme.com> wrote in message
news:uWzPVBLRHHA.3996@.TK2MSFTNGP04.phx.gbl...
> Awesome thanks, but what about for integrated data sources? I found a
> script in the same scripts that seems to change the data source its called
> PublishSampleReports.rss would this work for changing reports that have
> integrated non shared data sources? thanks!
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eN1imwKRHHA.912@.TK2MSFTNGP06.phx.gbl...
>> The default when deploying is to not overwrite data sources. That means
>> you only have to change things the first time you deploy to production,
>> after that you are good to go. No more changes necessary.
>> As far as target server, in VS you pick the solution configuration, each
>> solution configuration has its own target server. So you just select the
>> configuration you want that is appropriate and then deploy to it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Smokey Grindle" <nospam@.dontspamme.com> wrote in message
>> news:%23AamjiKRHHA.496@.TK2MSFTNGP06.phx.gbl...
>> We developed all our reports (a bunch of them) using our development
>> server named "development" which is not a local server... meaning we
>> cant set our connection string to localhost when we make them... but
>> then we need to deploy them to our Live server which then can be
>> localhost as they will only run on that server... how can we change the
>> connection string's server upon deployment? also we need to change the
>> traget server to the live one instead of development... can this be
>> scripted or do we have to change the connection string manually each and
>> every time we make a change on development and redeploy to the live
>> server? thanks!
>>
>