DSO in SQL2005: receive

Showing posts with label receive. Show all posts

Thursday, March 22, 2012

Drop User

When I attempt to drop a user I receive an error that the user objects
objects in the SQL 2000 DB, and the login could not be dropped. Does anyone
have a workaround to this? How can you get around this. Perhaps changing
ownership globally, and then deleteing the login? Any Ideas?Hi,
You can not drop the user if the user owns any objects.
How to change the object owner:
sp_changeobjectowner 'obj_name','new_owner'
You can also change the owner by updating the sysobjects tables
update sysobjects
set uid=<new uid>
where uid='uid for the user you need to drop'
Thanks
Hari
MCDBA
"Casey" <casey.canales@.bestsoftware.com> wrote in message
news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> When I attempt to drop a user I receive an error that the user objects
> objects in the SQL 2000 DB, and the login could not be dropped. Does
anyone
> have a workaround to this? How can you get around this. Perhaps changing
> ownership globally, and then deleteing the login? Any Ideas?
>|||> You can also change the owner by updating the sysobjects tables
> update sysobjects
> set uid=<new uid>
> where uid='uid for the user you need to drop'
Hari, although this may work, Casey should probably use the supported method
(sp_changedbowner).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You can not drop the user if the user owns any objects.
> How to change the object owner:
> sp_changeobjectowner 'obj_name','new_owner'
> You can also change the owner by updating the sysobjects tables
> update sysobjects
> set uid=<new uid>
> where uid='uid for the user you need to drop'
> Thanks
> Hari
> MCDBA
>
> "Casey" <casey.canales@.bestsoftware.com> wrote in message
> news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > When I attempt to drop a user I receive an error that the user objects
> > objects in the SQL 2000 DB, and the login could not be dropped. Does
> anyone
> > have a workaround to this? How can you get around this. Perhaps
changing
> > ownership globally, and then deleteing the login? Any Ideas?
> >
> >
>|||Hi,
I agree with Dan. I just mentioned various possibilities to change the
object owner.
Casey,
Please use sp_changeobjectowner system stored procedure to change the object
owners. This is always safe.
Updating system tables is always risky.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OiJAsV1IEHA.3840@.TK2MSFTNGP11.phx.gbl...
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> Hari, although this may work, Casey should probably use the supported
method
> (sp_changedbowner).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > You can not drop the user if the user owns any objects.
> >
> > How to change the object owner:
> >
> > sp_changeobjectowner 'obj_name','new_owner'
> >
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Casey" <casey.canales@.bestsoftware.com> wrote in message
> > news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > > When I attempt to drop a user I receive an error that the user objects
> > > objects in the SQL 2000 DB, and the login could not be dropped. Does
> > anyone
> > > have a workaround to this? How can you get around this. Perhaps
> changing
> > > ownership globally, and then deleteing the login? Any Ideas?
> > >
> > >
> >
> >
>|||Oops, I meant sp_changeobjectowner.
--
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OiJAsV1IEHA.3840@.TK2MSFTNGP11.phx.gbl...
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> Hari, although this may work, Casey should probably use the supported
method
> (sp_changedbowner).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > You can not drop the user if the user owns any objects.
> >
> > How to change the object owner:
> >
> > sp_changeobjectowner 'obj_name','new_owner'
> >
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Casey" <casey.canales@.bestsoftware.com> wrote in message
> > news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > > When I attempt to drop a user I receive an error that the user objects
> > > objects in the SQL 2000 DB, and the login could not be dropped. Does
> > anyone
> > > have a workaround to this? How can you get around this. Perhaps
> changing
> > > ownership globally, and then deleteing the login? Any Ideas?
> > >
> > >
> >
> >
>

Sunday, March 11, 2012

Drop indexes for data import

We need to import lots of data into some tables. To speed
up the import, we deside to drop the indexes related to
the tables that will receive the data before the import
and then recreate the index after the import. What's the
best practice for the senarios below? When to drop/keep
indexes? What kind of indexes(clustered/non-
clustered/unique)to drop? Many thanks.
1. The import scripts do select first and then insert.
2. The import scripts do insert first and then delete.
3. The import scripts do select first and then update.
It depends on what kind of indexes you have on the tables; sometimes,
indexes can help with data imports (e.g. seqential clustered indexes tend to
speed up data import). For a better answer, post DDL (CREATE TABLE
statements) for your tables. Include all indexes and constraints. Also
describe in more detail what the import will be doing. There are no best
practices; it all depends on the situation.
"Bill" <fei0405@.yahoo.com> wrote in message
news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
> We need to import lots of data into some tables. To speed
> up the import, we deside to drop the indexes related to
> the tables that will receive the data before the import
> and then recreate the index after the import. What's the
> best practice for the senarios below? When to drop/keep
> indexes? What kind of indexes(clustered/non-
> clustered/unique)to drop? Many thanks.
> 1. The import scripts do select first and then insert.
> 2. The import scripts do insert first and then delete.
> 3. The import scripts do select first and then update.
>
|||Thank you very much for the reply.
eg.There is a table we will insert about 150,000 records.
Here is the scripts I got from gernerate scripts utility:
CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
[SETID] [char] (5) COLLATE Latin1_General_BIN NOT
NULL ,
[INST_PROD_ID] [char] (20) COLLATE
Latin1_General_BIN NOT NULL ,
[MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
NULL ,
[ATTRIBUTE_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_ITEM_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTRIBUTE_VALUE] [char] (254) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_DATE] [PSDATE] NULL ,
[ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
[ROW_ADDED_DTTM] [PSDATETIME] NULL ,
[ROW_ADDED_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
[ROW_LASTMANT_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[SYNCID] [int] NOT NULL ,
[SYNCDTTM] [PSDATETIME] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
[dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
[ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
But when I issue command "sp_helpindex PS_RF_ATTR_INSP
go" I got more nonclustered index names as below:
index_name index_description
index_keys
PS_RF_ATTR_INSP clustered, unique located on PRIMARY
SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
index_name index_description
index_keys
PS0RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CUST, SETID, INST_PROD_ID
PS1RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CONTACT, SETID, INST_PROD_ID
.... ...
Please advise. Many thanks.

>--Original Message--
>It depends on what kind of indexes you have on the
tables; sometimes,
>indexes can help with data imports (e.g. seqential
clustered indexes tend to
>speed up data import). For a better answer, post DDL
(CREATE TABLE
>statements) for your tables. Include all indexes and
constraints. Also
>describe in more detail what the import will be doing.
There are no best[vbcol=seagreen]
>practices; it all depends on the situation.
>
>"Bill" <fei0405@.yahoo.com> wrote in message
>news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
speed
>
>.
>
|||Bill,
I am a bit confused about those nonclustered indexes; they contain columns
that don't appear to be in the table (BO_ID_CUST, etc).
Anyway, with no knowledge of your data it is still quite difficult to answer
this question; 150,000 rows is not a very large insert and if you already
have millions of rows in the table dropping and re-creating the indexes may
take more time than the insert itself, even if there are a lot of page
splits taking place. I think you really need to run a test batch with each
method to determine what's right for you.
"Bill" <fei0405@.yahoo.com> wrote in message
news:3a9301c49f17$d664c910$a401280a@.phx.gbl...
> Thank you very much for the reply.
> eg.There is a table we will insert about 150,000 records.
> Here is the scripts I got from gernerate scripts utility:
> CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
> [SETID] [char] (5) COLLATE Latin1_General_BIN NOT
> NULL ,
> [INST_PROD_ID] [char] (20) COLLATE
> Latin1_General_BIN NOT NULL ,
> [MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
> NULL ,
> [ATTRIBUTE_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_ITEM_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTRIBUTE_VALUE] [char] (254) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_DATE] [PSDATE] NULL ,
> [ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
> [ROW_ADDED_DTTM] [PSDATETIME] NULL ,
> [ROW_ADDED_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
> [ROW_LASTMANT_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [SYNCID] [int] NOT NULL ,
> [SYNCDTTM] [PSDATETIME] NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
> [dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
> [ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> But when I issue command "sp_helpindex PS_RF_ATTR_INSP
> go" I got more nonclustered index names as below:
> index_name index_description
> index_keys
> PS_RF_ATTR_INSP clustered, unique located on PRIMARY
> SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
> index_name index_description
> index_keys
> PS0RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CUST, SETID, INST_PROD_ID
> PS1RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CONTACT, SETID, INST_PROD_ID
> ... ...
>
> Please advise. Many thanks.

Drop indexes for data import

We need to import lots of data into some tables. To speed
up the import, we deside to drop the indexes related to
the tables that will receive the data before the import
and then recreate the index after the import. What's the
best practice for the senarios below? When to drop/keep
indexes? What kind of indexes(clustered/non-
clustered/unique)to drop? Many thanks.
1. The import scripts do select first and then insert.
2. The import scripts do insert first and then delete.
3. The import scripts do select first and then update.It depends on what kind of indexes you have on the tables; sometimes,
indexes can help with data imports (e.g. seqential clustered indexes tend to
speed up data import). For a better answer, post DDL (CREATE TABLE
statements) for your tables. Include all indexes and constraints. Also
describe in more detail what the import will be doing. There are no best
practices; it all depends on the situation.
"Bill" <fei0405@.yahoo.com> wrote in message
news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
> We need to import lots of data into some tables. To speed
> up the import, we deside to drop the indexes related to
> the tables that will receive the data before the import
> and then recreate the index after the import. What's the
> best practice for the senarios below? When to drop/keep
> indexes? What kind of indexes(clustered/non-
> clustered/unique)to drop? Many thanks.
> 1. The import scripts do select first and then insert.
> 2. The import scripts do insert first and then delete.
> 3. The import scripts do select first and then update.
>|||Thank you very much for the reply.
eg.There is a table we will insert about 150,000 records.
Here is the scripts I got from gernerate scripts utility:
CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
[SETID] [char] (5) COLLATE Latin1_General_BIN NOT
NULL ,
[INST_PROD_ID] [char] (20) COLLATE
Latin1_General_BIN NOT NULL ,
[MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
NULL ,
[ATTRIBUTE_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_ITEM_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTRIBUTE_VALUE] [char] (254) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_DATE] [PSDATE] NULL ,
[ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
[ROW_ADDED_DTTM] [PSDATETIME] NULL ,
[ROW_ADDED_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
[ROW_LASTMANT_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[SYNCID] [int] NOT NULL ,
[SYNCDTTM] [PSDATETIME] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
[dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
[ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
But when I issue command "sp_helpindex PS_RF_ATTR_INSP
go" I got more nonclustered index names as below:
index_name index_description
index_keys
PS_RF_ATTR_INSP clustered, unique located on PRIMARY
SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
index_name index_description
index_keys
PS0RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CUST, SETID, INST_PROD_ID
PS1RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CONTACT, SETID, INST_PROD_ID
... ...
Please advise. Many thanks.
>--Original Message--
>It depends on what kind of indexes you have on the
tables; sometimes,
>indexes can help with data imports (e.g. seqential
clustered indexes tend to
>speed up data import). For a better answer, post DDL
(CREATE TABLE
>statements) for your tables. Include all indexes and
constraints. Also
>describe in more detail what the import will be doing.
There are no best
>practices; it all depends on the situation.
>
>"Bill" <fei0405@.yahoo.com> wrote in message
>news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
>> We need to import lots of data into some tables. To
speed
>> up the import, we deside to drop the indexes related to
>> the tables that will receive the data before the import
>> and then recreate the index after the import. What's the
>> best practice for the senarios below? When to drop/keep
>> indexes? What kind of indexes(clustered/non-
>> clustered/unique)to drop? Many thanks.
>> 1. The import scripts do select first and then insert.
>> 2. The import scripts do insert first and then delete.
>> 3. The import scripts do select first and then update.
>>
>
>.
>|||Bill,
I am a bit confused about those nonclustered indexes; they contain columns
that don't appear to be in the table (BO_ID_CUST, etc).
Anyway, with no knowledge of your data it is still quite difficult to answer
this question; 150,000 rows is not a very large insert and if you already
have millions of rows in the table dropping and re-creating the indexes may
take more time than the insert itself, even if there are a lot of page
splits taking place. I think you really need to run a test batch with each
method to determine what's right for you.
"Bill" <fei0405@.yahoo.com> wrote in message
news:3a9301c49f17$d664c910$a401280a@.phx.gbl...
> Thank you very much for the reply.
> eg.There is a table we will insert about 150,000 records.
> Here is the scripts I got from gernerate scripts utility:
> CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
> [SETID] [char] (5) COLLATE Latin1_General_BIN NOT
> NULL ,
> [INST_PROD_ID] [char] (20) COLLATE
> Latin1_General_BIN NOT NULL ,
> [MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
> NULL ,
> [ATTRIBUTE_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_ITEM_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTRIBUTE_VALUE] [char] (254) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_DATE] [PSDATE] NULL ,
> [ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
> [ROW_ADDED_DTTM] [PSDATETIME] NULL ,
> [ROW_ADDED_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
> [ROW_LASTMANT_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [SYNCID] [int] NOT NULL ,
> [SYNCDTTM] [PSDATETIME] NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
> [dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
> [ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> But when I issue command "sp_helpindex PS_RF_ATTR_INSP
> go" I got more nonclustered index names as below:
> index_name index_description
> index_keys
> PS_RF_ATTR_INSP clustered, unique located on PRIMARY
> SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
> index_name index_description
> index_keys
> PS0RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CUST, SETID, INST_PROD_ID
> PS1RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CONTACT, SETID, INST_PROD_ID
> ... ...
>
> Please advise. Many thanks.

Friday, March 9, 2012

drop col from published table

I wish to add a col to a table I have published for merge replication.
When I attempt to do this I receive errors in regard to the rowguid col that
was added when creating my publication.
Do I need to drop the publication before adding col ?
Or is there another way out of this ?
You shouldn't have to if you use sp_repldropcolumn. What is the exact error
message you are getting?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eTmObDGBHHA.1012@.TK2MSFTNGP04.phx.gbl...
>I wish to add a col to a table I have published for merge replication.
> When I attempt to do this I receive errors in regard to the rowguid col
> that was added when creating my publication.
> Do I need to drop the publication before adding col ?
> Or is there another way out of this ?
>
|||Hi Hilary, and thanks for the response.
When I attempt to add (not drop) a col to a table I have published for
mobile merge replication I receive the following errors :
"Error validation the default for column 'rowguid'."
"It is invalid to drop the default constraint on the rowguid column that is
used by merge replication. The DDL operation failed inside merge DDL
replication manipulation. The transaction ended in the trigger. The batch
has been aborted."
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ObR97mGBHHA.3380@.TK2MSFTNGP04.phx.gbl...
> You shouldn't have to if you use sp_repldropcolumn. What is the exact
> error message you are getting?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:eTmObDGBHHA.1012@.TK2MSFTNGP04.phx.gbl...
>

Friday, February 24, 2012

drillthrough security error whereas the cube has no security

I have defined several drillthrouh actions on a big cube - each action asigned to a measuregroup. While I want to run one of this actions I receive the error below (BI Dev Studio)

The message states a security restriction, but the database does not have any security roles defined - i work as local admin on the machine with no specific security defined.

The Server is local installed - 9.0.3042.0

This drillthrouh runs on a distinct count measure and returns several attributes from one dimension and the measure itself. I have dropped and recreated several times but it does not change. The profiler shows Error number -1056308962 in the Error trace column.

Additionally I have defined several other drillthrough actions on distinct count measures with the same dimension attribute information - and there the drillthrough works.

ANY Ideas?

Best Regards, HANNES

===================================

The selected action cannot be completed because of the following error.
The drillthrough operation cannot be performed because cell security does not allow access to the source data.
This error may have occurred because the definition for the action is not valid. Verify the definition using the Actions view. (Microsoft Visual Studio)

Program Location:

at Microsoft.AnalysisServices.Browse.ActionsAwarePivotTable.OnAction(MenuCommand menuCommandSender, CommandHandlingArgs args)
at Microsoft.DataWarehouse.Design.CommandHandlingInfoMap.HandleCommand(MenuCommand menuCommand)
at Microsoft.AnalysisServices.Controls.PivotTableControl.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.AnalysisServices.Browse.CubeBrowser.BrowserStateSwitchManager.ConnectedAndActiveStateCommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.AnalysisServices.Browse.CubeBrowser.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)

===================================

The drillthrough operation cannot be performed because cell security does not allow access to the source data. (Microsoft SQL Server 2005 Analysis Services)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteTabular(CommandBehavior behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.AnalysisServices.Browse.Actions.DataReaderCompatibleAction.Invoke(IServiceProvider iserviceProvider)
at Microsoft.AnalysisServices.Browse.ActionsAwarePivotTable.OnAction(MenuCommand menuCommandSender, CommandHandlingArgs args)

The error message is not ideal, but I have hard time thinking what else could cause drill through not work. Wild guess - if you change IgnoreUnrelatedDimensions to false for the measure group in question - do you still get the error ?|||

you are a genius. This solved the problem.

Could you explain the background of this - why I need to set this property for this measruegroup and not for others?

I am not 100% sure about the effects this have on some of my calculations - a have lots of calculations like ValueX/DistinctCount and then STDDEV({Bigset},(ValueX/DistinctCount)) - if the distinct count is related to all dimensions then this should have no effect - I am right? Whereas ValueX is not related to all dimensions

Oh no - the distinct count is not the lowest granularity - for the time its on day level - but there are some measures which are on hour level (the distinct count is not related to the key attribute in the time dimension) - do calculations with valueonhourlevel/distinctcount getting wrong?

(My cube has 500 calculated measures)

May Thanks, HANNES

|||Hannes - you should not have switched to IgnoreUnrelatedDimension=false. Drillthrough is supposed to work fine when it is true. I only asked you to check the value of false, because that was the only thing I could think of. So, I am convinced that this is a bug - and you should contact Product Support about it. You can reference this thread.

Thursday, March 22, 2012

Drop User

Drop User

Drop User

Sunday, March 11, 2012

Drop indexes for data import

Drop indexes for data import

Friday, March 9, 2012

drop col from published table

Friday, February 24, 2012

drillthrough security error whereas the cube has no security

DSO in SQL2005

Blog Archive

About Me