OLAP calculated membres and Report Model/Builder

My question is related to Report Builder but my data model is build using OLAP database so I am posting my question in OLAP group in case this issues is related to OALP side.

I have OLAP database. There are 5 cubes and one linked cube. My one linked cube has mapped all 5 cubes (like virtual cube in AS 2000) I am using AS 2005.

In my linked cube I have calculated members. I created a Report model using my olap database in reporting services.

In report builder I am not able to see my calculated members when I select linked cube. Is there any thing I am missing?

Thanks for help - Ashok

I think this may be related to whether the calculations are associated with a measure group. The Report Model only seems to list measures that are associated with a measure group. Try going into the calculations tab for your cube clicking on the calculation properties button (looks like a little window with a yellow hand over it) as associating your calculations with a measure group. Unfortunately I think you will need to delete and re-generation your report model.

Odd SQLTrigger behavior

I have three tables I am using, aspnet_Users, Stories,CustomizedStory. Stories andCustomizedStory are related via a foreign key StoryID. I've setup the tables so that when I delete aStory row it cascade deletes the corresponding row from CustomizedStory. Each CustomizedStory row has a reference to UserID from aspnet_Users.Since, I didn't want to mess with the table definition by adding a cascadedelete option on aspnet_Users, I decide to use a trigger, essentially delete all customized stories and associated stories if a user is deleted:

ALTERTRIGGER[dbo].[DeleteCustomizedStories]

ON[dbo].[aspnet_Users]
FOR DELETE
AS
BEGIN
DELETE FROMdbo.Story
WHEREStoryID=
(SELECTStoryIDFROMdbo.CustomizedStoryWHEREUserID =
(SELECTUserIDFROMdeleted))
END

The problem I am having is that it deletes all of theCustomizedStory rows as specified by the cascading option, but doesn't deletethe Story rows. I can't seem to understandwhy this is happening, especially when Iexplicitly told it to delete story rows.

The behavior is a bit weird, I must admit. Your DELETE-query is wrong though, which may cause problems.

DELETE FROMdbo.Story
WHEREStoryIDIN
(SELECTStoryIDFROMdbo.CustomizedStoryWHEREUserIDIN
(SELECTUserIDFROMdeleted))

or

DELETE s FROMdbo.Storys
INNER JOINdbo.CustomizedStory c ON s.StoryID=c.StoryID
INNER JOINdeleted d ON c.UserID=d.UserID

ODBC SQL Server driver-Canot generate SSPl context

Dear all,

I am getting the message "Canot generate SSPI context",while i am working in a client system.I hope this is something related to networking.How to solve the problem.I can not connect to SQL Server once i got above mentioned message.

Please give your advice to understand and rectify the problem.

regards
Gracesoncheck this link here (http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B267588)|||make sure your NT account has a windows auth login to the SQL Server.|||Check out KB811889
http://support.microsoft.com/default.aspx?scid=kb;EN-US;811889

ODBC login problem

This is possibly related to my restoration of the MS SQL DB from MSSQL 2000
to MSSQL 2005:
I backed up a database from MSSQL 2000 to move to a new server running MSSQL
2005.
The DB appeared to restore without a problem (although not yet confirmed);
however, my ODBC login is now not accepted?
In setting up the ODBC DSN configuration, I use my old SQL Server
Authentication login and password, which I get the following error message:
"Login failed for user 'XXXXX'. The user is not associated with a Trusted
SQL Server connection".
My ODBC login was what I used on my MSSQL 2000 setup and I presume the
backup and restore would copy these Authentication securities across to the
MSSQL 2005 setup?
Please let me know what I have overlooked.
Thanks> In setting up the ODBC DSN configuration, I use my old SQL Server
> Authentication login and password, which I get the following error
> message:
> "Login failed for user 'XXXXX'. The user is not associated with a Trusted
> SQL Server connection".
First, make sure the target SQL 2005 server is configured to allow both
Windows and SQL authentication. The trusted connection message indicates
that you are attempting Windows authentication rather than SQL
authentication.
Also, logins are not restored when you restore a database so you'll need to
create the SQL login on the target server if it doesn't already exists.
You'll also need to execute sp_change_users_login to remap the existing
database user to the SQL login on the target server. Example below. See
the Books Online for details.
USE MyDatabase
GO
--create a new login
CREATE LOGIN MyLogin
WITH PASSWORD = 'asecrtpasw0rd',
DEFAULT_DATABASE = MyDatabase
GO
--remap existing database user to new or exisitng login
EXEC sp_change_users_login
@.Action = 'Update_One',
@.UserNamePattern = 'MyLogin',
@.LoginName = 'MyLogin'
Hope this helps.
Dan Guzman
SQL Server MVP
"Calculator" <Calculator@.discussions.microsoft.com> wrote in message
news:F3252C86-D583-4B2A-AFC8-854E96DCCBCA@.microsoft.com...
> This is possibly related to my restoration of the MS SQL DB from MSSQL
> 2000
> to MSSQL 2005:
> I backed up a database from MSSQL 2000 to move to a new server running
> MSSQL
> 2005.
> The DB appeared to restore without a problem (although not yet confirmed);
> however, my ODBC login is now not accepted?
> In setting up the ODBC DSN configuration, I use my old SQL Server
> Authentication login and password, which I get the following error
> message:
> "Login failed for user 'XXXXX'. The user is not associated with a Trusted
> SQL Server connection".
> My ODBC login was what I used on my MSSQL 2000 setup and I presume the
> backup and restore would copy these Authentication securities across to
> the
> MSSQL 2005 setup?
> Please let me know what I have overlooked.
> Thanks|||Is the new server setup for both Windows and SQL
Authentication? Did you add the login to the SQL Server 2005
server?
You can also check for users not mapped to a login as well
as map users and SQL logins using sp_change_users_login. To
list the users, execute the following in the database:
EXEC sp_change_users_login 'Report'
See books online for more information on
sp_change_users_login
-Sue
On Mon, 11 Jun 2007 01:39:02 -0700, Calculator
<Calculator@.discussions.microsoft.com> wrote:

>This is possibly related to my restoration of the MS SQL DB from MSSQL 2000
>to MSSQL 2005:
>I backed up a database from MSSQL 2000 to move to a new server running MSSQ
L
>2005.
>The DB appeared to restore without a problem (although not yet confirmed);
>however, my ODBC login is now not accepted?
>In setting up the ODBC DSN configuration, I use my old SQL Server
>Authentication login and password, which I get the following error message:
>"Login failed for user 'XXXXX'. The user is not associated with a Trusted
>SQL Server connection".
>My ODBC login was what I used on my MSSQL 2000 setup and I presume the
>backup and restore would copy these Authentication securities across to the
>MSSQL 2005 setup?
>Please let me know what I have overlooked.
>Thanks|||Thanks Dan
I followed your advice and ran the queries which ran well until I got to:
--remap existing database user to new or exisitng login
EXEC sp_change_users_login
@.Action = 'Update_One',
@.UserNamePattern = 'MyLogin',
@.LoginName = 'MyLogin'
which gave me the following error:
"Msg 15291, level 16, State 1, Procedure sp_change_users_login , line 108
Terminating this procedure. The Username 'XXXXX' is absent or invalid."
The Username in sp_change_users_login is the same 'MyLogin' that I set up in
CREATE LOGIN MyLogin?
Thanks
"Dan Guzman" wrote:

> First, make sure the target SQL 2005 server is configured to allow both
> Windows and SQL authentication. The trusted connection message indicates
> that you are attempting Windows authentication rather than SQL
> authentication.
> Also, logins are not restored when you restore a database so you'll need t
o
> create the SQL login on the target server if it doesn't already exists.
> You'll also need to execute sp_change_users_login to remap the existing
> database user to the SQL login on the target server. Example below. See
> the Books Online for details.
> USE MyDatabase
> GO
> --create a new login
> CREATE LOGIN MyLogin
> WITH PASSWORD = 'asecrtpasw0rd',
> DEFAULT_DATABASE = MyDatabase
> GO
> --remap existing database user to new or exisitng login
> EXEC sp_change_users_login
> @.Action = 'Update_One',
> @.UserNamePattern = 'MyLogin',
> @.LoginName = 'MyLogin'
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Calculator" <Calculator@.discussions.microsoft.com> wrote in message
> news:F3252C86-D583-4B2A-AFC8-854E96DCCBCA@.microsoft.com...
>|||Thanks Sue
1) Your Question: > Is the new server setup for both Windows and SQL
> Authentication? Did you add the login to the SQL Server 2005
> server?
I am using the built-in account on the local system as shown in the "SQL
Server properties" in the SQL Configuration Manager.
From there I perform the following:
under "Administrative Tools" I select the "ODBC Data Source Administrator",
where I enter data in the "Microsoft SQL Server DSN Configuation".
Here I select the second option button: "With SQL Server Authentication
using a login ID and password entered by the user."
I also tick "Connect to SQL Server ..." and enter my Login ID and my Passwor
d.
My new Login ID and Password do not work with the newly created login and
Password using:
CREATE LOGIN MyLogin
WITH PASSWORD = 'asecrtpasw0rd',
DEFAULT_DATABASE = MyDatabase
GO
The same error results:
"Login failed for user 'XXXXX'. The user is not associated with a Trusted
SQL Server connection".
2) The results from your procedure: EXEC sp_change_users_login 'Report'
gave me 2 x UserName and 2 x UserSID
The UserSIDs I have never seen or used before: Should they be familar?
Thanks
"Sue Hoegemeier" wrote:

> Is the new server setup for both Windows and SQL
> Authentication? Did you add the login to the SQL Server 2005
> server?
> You can also check for users not mapped to a login as well
> as map users and SQL logins using sp_change_users_login. To
> list the users, execute the following in the database:
> EXEC sp_change_users_login 'Report'
> See books online for more information on
> sp_change_users_login
> -Sue
> On Mon, 11 Jun 2007 01:39:02 -0700, Calculator
> <Calculator@.discussions.microsoft.com> wrote:
>
>|||> which gave me the following error:
> "Msg 15291, level 16, State 1, Procedure sp_change_users_login , line 108
> Terminating this procedure. The Username 'XXXXX' is absent or invalid."
I would expect this error if the 'MyLogin' user does not exist in the target
database. My understanding from your initial post is that the user already
exists so only steps needed were to add the login and remap.
If the user does not exist in the database, simply run:
CREATE USER MyLogin;
This will create new user 'MyLogin' and map it to the existing login
'MyLogin' you previously created.
Hope this helps.
Dan Guzman
SQL Server MVP
"Calculator" <Calculator@.discussions.microsoft.com> wrote in message
news:C20F8BC2-18A8-4F83-9BAD-527100D5F585@.microsoft.com...[vbcol=seagreen]
> Thanks Dan
> I followed your advice and ran the queries which ran well until I got to:
> --remap existing database user to new or exisitng login
> EXEC sp_change_users_login
> @.Action = 'Update_One',
> @.UserNamePattern = 'MyLogin',
> @.LoginName = 'MyLogin'
> which gave me the following error:
> "Msg 15291, level 16, State 1, Procedure sp_change_users_login , line 108
> Terminating this procedure. The Username 'XXXXX' is absent or invalid."
> The Username in sp_change_users_login is the same 'MyLogin' that I set up
> in
> CREATE LOGIN MyLogin?
> Thanks
>
> "Dan Guzman" wrote:
>|||As Sue and I mentioned earlier, make sure you SQL Server is configured to
allow both SQL Server and Windows authentication. You can do this from SQL
Server Management Studio. Right-click on the server in object explorer,
select Properties-->Security and then select 'SQL Server and Windows
Authentication mode.
Hope this helps.
Dan Guzman
SQL Server MVP
"Calculator" <Calculator@.discussions.microsoft.com> wrote in message
news:F1F02198-D32F-481F-9E23-4F17BA62AECC@.microsoft.com...[vbcol=seagreen]
> Thanks Sue
> 1) Your Question: > Is the new server setup for both Windows and SQL
> I am using the built-in account on the local system as shown in the "SQL
> Server properties" in the SQL Configuration Manager.
> From there I perform the following:
> under "Administrative Tools" I select the "ODBC Data Source
> Administrator",
> where I enter data in the "Microsoft SQL Server DSN Configuation".
> Here I select the second option button: "With SQL Server Authentication
> using a login ID and password entered by the user."
> I also tick "Connect to SQL Server ..." and enter my Login ID and my
> Password.
> My new Login ID and Password do not work with the newly created login and
> Password using:
> CREATE LOGIN MyLogin
> WITH PASSWORD = 'asecrtpasw0rd',
> DEFAULT_DATABASE = MyDatabase
> GO
> The same error results:
> "Login failed for user 'XXXXX'. The user is not associated with a Trusted
> SQL Server connection".
> 2) The results from your procedure: EXEC sp_change_users_login 'Report'
> gave me 2 x UserName and 2 x UserSID
> The UserSIDs I have never seen or used before: Should they be familar?
> Thanks
> "Sue Hoegemeier" wrote:
>