Hi, all here,
I have question about copying a cube from other project to my current project by using XMLA, if they have the same data source and data source view? Is it possible?
Thanks a lot for any guidance.
You might have a problem with matching dimension names and ID's.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
First of all, we are using SQL Server 2005 with a SQL Mobile subscriber and we are attempting to use Data Partitions on our current database
schema which contains associative tables for many-to-many relationships.
We have two tables, a User table
and an Audit table.A user can be
assigned more than one Audit.An Audit
can be assigned to more than one User.
So an AuditUser associative table exists.If data partitions are used based on User,
then any Audits that are assigned to one or more users should be copied to the
proper partition for each User (the msmerge_current_partition_mappings table
with the proper partition_id values).
In order to insert records with such a schema, the following
steps occur in order:
new row into Audit table with new rowguid
entry into AuditUser table associating the auditguid with every userguid that
is assigned this audit.
Merge replication triggers are fired on insert of the Audit
row and another one for the insert of the AuditUser row.
When the Audit row is inserted, the replication trigger follows
the following logic:
a copy of that row into the msmerge_contents table.
the row to determine which partition(s) this row should be copied to as
well (msmerge_current_partition_mappings table).To do this, it checks to see if the
AuditGuid is referenced in one or more AuditUser rows.Since we haven’t inserted the AuditUser
row at this point, the trigger’s logic doesn’t find a partition to copy
this row to.
When the AuditUser row is inserted, the replication trigger performs
the same logic as with the Audit row, it:
a copy of that row into the msmerge_contents table.
the row to determine which partition(s) this row should be copied to as
well (msmerge_current_partition_mappings table).Since the row meets the criteria for one
or more partitions, it is copied to the msmerge_current_partition_mappings
table for each partition that exists.
When replication occurs, we see only the AuditUser rows
copied down to our device, and not the corresponding Audit rows.Now that we understand the triggers, it is
plain to see why.If the AuditUser row
could be inserted first, then the trigger on the Audit row would copy that row
into the proper partitions and all would work well.However, the Audit row must be inserted
first, so that foreign key relationship constraints are preserved.
It seems that the Update trigger on the AuditUser row
actually walks the relationships and copies any related child rows to the
msmerge_current_partition_mappings table.
The answer is YES! I'm posting a followup as we've discovered what our problem turned out to be. In the end, it was a security issue - which was not straightforward at all!
First a quick recap:
When attempting to replicate data using merge replication and data
partitions with a schema that contains an associative table, only the
associative table row was being replicated. Given the following table
relationships:
[User] <-> [AuditUser] <-> [Audit]
It is necessary to insert the row into the Audit table first before
inserting the row into the AuditUser table (assuming that the [User] table is
somewhat fixed in this scenario). Because the
msmerge_current_partition_mappings table is maintained on INSERT triggers, when
the trigger fires for the Audit table, it does not determine that the row meets
the filter criteria, because that criteria is based on a User and since the
AuditUser row hasn’t been inserted yet, the Audit row is not seen as belonging
to user’s partition and thus is not copied to the
msmerge_current_partition_mappings table.
When the AuditUser row is inserted next, it easily passes the
filter test and is copied to the msmerge_current_partition_mappings table. To
compensate for the fact the Audit row was missed, this trigger rescans related
rows and attempts to copy any related rows that match this AuditUser row over
to the msmerge_current_partition_mappings table (provided those rows meet
filter criteria set on those tables). It does this by checking to see if those
rows exist in a View that was created based on the filters for that table. Such
a View for our Audit table is:
Replication View for our filtered Audit table:
create view dbo.[MSmerge_Audit_Audit_PARTITION_VIEW] as select
[Audit].[Guid], [Audit].[SystemGuid], [Audit].[StatusGuid], [Audit].[Active],
[AuditUser].partition_id from [dbo].[Audit] [Audit] ,
[dbo].[MSmerge_Audit_AuditUser_PARTITION_VIEW] [AuditUser] where ( (
[AuditUser].[AuditGuid] = [Audit].[Guid]
AND ([Audit].[StatusGuid] = '73fbcc34-260e-430f-bda6-fd6bdf944d85'
OR [Audit].[StatusGuid] = '0B7E27FA-712F-455D-A651-B3C6EC815EE75')
AND [Audit].[Active] = 1
AND [Audit].[SystemGuid] in (SELECT guid FROM [System] WHERE Active
= 1)) ) and ({fn ISPALUSER('E65FCB7D-BD91-480A-8D02-F8101DA974FE')} = 1 or
permissions(469576711) & 0x1b <> 0)
When looking at the how this view is constructed, we noticed that
aside from the defined filter information, an ‘and’ clause is tacked on that
restricts the rows returned based upon the user’s inclusion in the Publication
Access List.
Our production and test environment is set up in the following
manner:
IIS on machine 1
SQL Server 2005 on machine 2
IIS is set to use a domain account called WebReplication that has
access to the Publication (is in the Publication Access List) and has access
the shared UNC repldata directory. However, our website users run under
different domain account, WebUser, which we never granted access to the
Publication. Since the WebUser account wouldn’t be replicating, it didn’t seem
necessary to give that user access to the publication. But without access to
the publication, the WebUser doesn’t have rights to see the data in the View
generated above. So, the trigger determines that there are no child rows
(Audit) that meet the filter criteria, and thus doesn’t copy the related child
rows to the msmerge_current_partition_mappings table.
The result is exactly what we’ve seen: only the AuditUser rows are
replicated and not the Audit rows that relate to them! By adding the WebUser
account to the Publication Access List, and inserting rows into Audit and
AuditUser again, both sets of rows, Audit and AuditUser, are now replicated
since the Audit rows are copied to the msmerge_current_partition_mappings
table!
It would seem to me that granting every user access to a
publication that might alter a table that is to be replicated in that
publication is a bit cumbersome, especially since they already have rights to
the table. It’d be like assigning them rights twice – once to the table and
again to the publication that contains that table. Why not have the view ignore
the checking to see if the user has rights to publication altogether? If the
user has rights to modify the table, then they should implicitly have the right
to have their modification replicated!
I hope this helps someone. It cost us 3 days of digging through replication to find it. And only then, it was because we just happened to look at how these views were created.
-Santino Lamberti
Why are you granting the users permissions to the tables at the publisher? And what permissions are you giving them, dbo access? You should not have to grant permissions to the users for each table, just add them to the PAL. The only reason you would have to grant them access to the underlying tables is if the tables are not owned by "dbo". In this case, the permission chain would break since the PAL users will call the replication views, which are owned by dbo. Other reasons you would grant them access is if they're accessing the tables outside of replication process. Depending on what access you gave them, you may have short-circuited some of the replication security checks, which then expects you to have granted permissions to all your tables properly. Otherwise if user wasn't in PAL group, and didn't have permissions on underlying tables, then the sync would have failed with appropriate security error message.
Let me know if there's any other concerns.
First of all, we are using SQL Server 2005 with a SQL Mobile subscriber and we are attempting to use Data Partitions on our current database
schema which contains associative tables for many-to-many relationships.
We have two tables, a User table
and an Audit table.A user can be
assigned more than one Audit.An Audit
can be assigned to more than one User.
So an AuditUser associative table exists.If data partitions are used based on User,
then any Audits that are assigned to one or more users should be copied to the
proper partition for each User (the msmerge_current_partition_mappings table
with the proper partition_id values).
In order to insert records with such a schema, the following
steps occur in order:
new row into Audit table with new rowguid
entry into AuditUser table associating the auditguid with every userguid that
is assigned this audit.
Merge replication triggers are fired on insert of the Audit
row and another one for the insert of the AuditUser row.
When the Audit row is inserted, the replication trigger follows
the following logic:
a copy of that row into the msmerge_contents table.
the row to determine which partition(s) this row should be copied to as
well (msmerge_current_partition_mappings table).To do this, it checks to see if the
AuditGuid is referenced in one or more AuditUser rows.Since we haven’t inserted the AuditUser
row at this point, the trigger’s logic doesn’t find a partition to copy
this row to.
When the AuditUser row is inserted, the replication trigger performs
the same logic as with the Audit row, it:
a copy of that row into the msmerge_contents table.
the row to determine which partition(s) this row should be copied to as
well (msmerge_current_partition_mappings table).Since the row meets the criteria for one
or more partitions, it is copied to the msmerge_current_partition_mappings
table for each partition that exists.
When replication occurs, we see only the AuditUser rows
copied down to our device, and not the corresponding Audit rows.Now that we understand the triggers, it is
plain to see why.If the AuditUser row
could be inserted first, then the trigger on the Audit row would copy that row
into the proper partitions and all would work well.However, the Audit row must be inserted
first, so that foreign key relationship constraints are preserved.
It seems that the Update trigger on the AuditUser row
actually walks the relationships and copies any related child rows to the
msmerge_current_partition_mappings table.
The answer is YES! I'm posting a followup as we've discovered what our problem turned out to be. In the end, it was a security issue - which was not straightforward at all!
First a quick recap:
When attempting to replicate data using merge replication and data
partitions with a schema that contains an associative table, only the
associative table row was being replicated. Given the following table
relationships:
[User] <-> [AuditUser] <-> [Audit]
It is necessary to insert the row into the Audit table first before
inserting the row into the AuditUser table (assuming that the [User] table is
somewhat fixed in this scenario). Because the
msmerge_current_partition_mappings table is maintained on INSERT triggers, when
the trigger fires for the Audit table, it does not determine that the row meets
the filter criteria, because that criteria is based on a User and since the
AuditUser row hasn’t been inserted yet, the Audit row is not seen as belonging
to user’s partition and thus is not copied to the
msmerge_current_partition_mappings table.
When the AuditUser row is inserted next, it easily passes the
filter test and is copied to the msmerge_current_partition_mappings table. To
compensate for the fact the Audit row was missed, this trigger rescans related
rows and attempts to copy any related rows that match this AuditUser row over
to the msmerge_current_partition_mappings table (provided those rows meet
filter criteria set on those tables). It does this by checking to see if those
rows exist in a View that was created based on the filters for that table. Such
a View for our Audit table is:
Replication View for our filtered Audit table:
create view dbo.[MSmerge_Audit_Audit_PARTITION_VIEW] as select
[Audit].[Guid], [Audit].[SystemGuid], [Audit].[StatusGuid], [Audit].[Active],
[AuditUser].partition_id from [dbo].[Audit] [Audit] ,
[dbo].[MSmerge_Audit_AuditUser_PARTITION_VIEW] [AuditUser] where ( (
[AuditUser].[AuditGuid] = [Audit].[Guid]
AND ([Audit].[StatusGuid] = '73fbcc34-260e-430f-bda6-fd6bdf944d85'
OR [Audit].[StatusGuid] = '0B7E27FA-712F-455D-A651-B3C6EC815EE75')
AND [Audit].[Active] = 1
AND [Audit].[SystemGuid] in (SELECT guid FROM [System] WHERE Active
= 1)) ) and ({fn ISPALUSER('E65FCB7D-BD91-480A-8D02-F8101DA974FE')} = 1 or
permissions(469576711) & 0x1b <> 0)
When looking at the how this view is constructed, we noticed that
aside from the defined filter information, an ‘and’ clause is tacked on that
restricts the rows returned based upon the user’s inclusion in the Publication
Access List.
Our production and test environment is set up in the following
manner:
IIS on machine 1
SQL Server 2005 on machine 2
IIS is set to use a domain account called WebReplication that has
access to the Publication (is in the Publication Access List) and has access
the shared UNC repldata directory. However, our website users run under
different domain account, WebUser, which we never granted access to the
Publication. Since the WebUser account wouldn’t be replicating, it didn’t seem
necessary to give that user access to the publication. But without access to
the publication, the WebUser doesn’t have rights to see the data in the View
generated above. So, the trigger determines that there are no child rows
(Audit) that meet the filter criteria, and thus doesn’t copy the related child
rows to the msmerge_current_partition_mappings table.
The result is exactly what we’ve seen: only the AuditUser rows are
replicated and not the Audit rows that relate to them! By adding the WebUser
account to the Publication Access List, and inserting rows into Audit and
AuditUser again, both sets of rows, Audit and AuditUser, are now replicated
since the Audit rows are copied to the msmerge_current_partition_mappings
table!
It would seem to me that granting every user access to a
publication that might alter a table that is to be replicated in that
publication is a bit cumbersome, especially since they already have rights to
the table. It’d be like assigning them rights twice – once to the table and
again to the publication that contains that table. Why not have the view ignore
the checking to see if the user has rights to publication altogether? If the
user has rights to modify the table, then they should implicitly have the right
to have their modification replicated!
I hope this helps someone. It cost us 3 days of digging through replication to find it. And only then, it was because we just happened to look at how these views were created.
-Santino Lamberti
Why are you granting the users permissions to the tables at the publisher? And what permissions are you giving them, dbo access? You should not have to grant permissions to the users for each table, just add them to the PAL. The only reason you would have to grant them access to the underlying tables is if the tables are not owned by "dbo". In this case, the permission chain would break since the PAL users will call the replication views, which are owned by dbo. Other reasons you would grant them access is if they're accessing the tables outside of replication process. Depending on what access you gave them, you may have short-circuited some of the replication security checks, which then expects you to have granted permissions to all your tables properly. Otherwise if user wasn't in PAL group, and didn't have permissions on underlying tables, then the sync would have failed with appropriate security error message.
Let me know if there's any other concerns.
I have a *.sql script that creates database tables, and I need to modify the database to enable the service broker. In addition, the actual name of the database is not known in advance - it is set per instance of the application.
I know I need to do:
ALTER DATABASE dbname SET ENABLE_BROKER
But I must avoid including the name of the database in the script. I did wonder if this would work:
DECLARE @.DB varchar(50)
SELECT @.DB = DB_NAME()
ALTER DATABASE @.DB SET ENABLE_BROKER
But I just get a syntax error. Presumably this also rules out setting the database name as a parameter to the script (SqlParameter stuff)
The only option I can think of is dynamically creating the statement, either in T-SQL or in the calling .NET environment.
Any thoughts?
Ruth
Hi,I guess you have to create a dynamic statement to make it work, something like:
DECLARE @.DB varchar(50)
SELECT @.DB = DB_NAME()
DECLARE @.SQLString VARCHAR(200)
SET @.SQLString = ' ALTER DATABASE ' + @.DB + 'SET ENABLE_BROKER'
EXEC(@.SqlString)
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
Thanks, that looks good. You can of course replace @.DB with DB_NAME() in the SET.
Ruth
|||Dynamic SQL is the only solution I know of. Make sure you protect yourself against SQL injection problems (e.g. use QUOTENAME on the database name). Also, ALTER DATABASE requires exclusive lock on the database, see http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
HTH,
~ Remus
I assumed that the value returned from DB_NAME() would be acceptable in SQL. Probably a bad assumption, really!
So, I did have:
SET @.AlterStmt = 'ALTER DATABASE ' + DB_NAME() + ' SET ENABLE_BROKER'
but I should really have this?
SET @.AlterStmt = 'ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET ENABLE_BROKER'
Thinking about injection, what is the best method to use when creating SQL on the fly. I don't want to use SqlParameters for everything as they obfuscate the code significantly. Is there an System.Data.SqlClient equivalent of QUOTENAME() ?
Ruth
|||
DB_NAME() is fine, it doesn't need to be passed into QUOTENAME. When I formulated the reply, Jens' post simply wasn't there and I didn't see it and I assumed the database name comes in as an argument.
You should worry about SQL Injection if the database name comes from an external (potentialy untrusted) source like a web form text field.
There is no equivalent to QUOTENAME, I usually use the simplest String.Replace method, like this:
string quotedDbName = "[" + dbnameVariable.Replace("]","]]") + "]";
HTH,
~ Remus
