Production error: "The schema script [...] could not be propagated to the subscriber"

Hi,
A member of my team recently made a few minor changes to our database.
Several (not all) subscribers are now failing to replicate with the
following error:
The schema script 'exec sp_repldropcolumn
'[dbo].[IP_Application_Updates]', 'update_id', 1' could not be
propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
A column was added to or dropped from the replicated table.
(Source: l07052 (Agent); Error number: 27160)
------
' because it is a primary key column.
(Source: l07052 (Data source); Error number: 21264)
------
This was a problem a few months ago but seemed to have been resolved.
In particular, nothing at all has changed recently in the
IP_Application_Updates table. I'm very happy for that table to remain
exactly as it is.
Can anyone suggest a way to fix this? I've got a bunch of users who
are unable to replicate their database and they'll turn nasty shortly.
In the absence of an elegant fix I'll cheerfully go with a way to
change the offending script to something innocuous, but I don't know
where or why the script exists or why the subscribers are trying to run
it. I will be very grateful if anyone can help me to get my
subscribers past this error.
Many thanks
Alex
Whenever the Merge Agent runs, it first makes sure that the schema [any
change as a result of DDL] is in sync, before the
data could be replicated. All the schema changes in the publisher are stored
in sysmergeschemachange table on the
publisher database. You can take a look at this table and see the schema
changes that have been executed at the publisher.
I believe the Merge Agent is trying to drop the column at the subscriber,
but unable to do it because it is the primary key column.
One work around would be to remove the primary key constraint at the
subscriber, if you don't need that column any more.
<WombatDeath@.gmail.com> wrote in message
news:1169249897.423772.218970@.l53g2000cwa.googlegr oups.com...
> Hi,
> A member of my team recently made a few minor changes to our database.
> Several (not all) subscribers are now failing to replicate with the
> following error:
> The schema script 'exec sp_repldropcolumn
> '[dbo].[IP_Application_Updates]', 'update_id', 1' could not be
> propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> A column was added to or dropped from the replicated table.
> (Source: l07052 (Agent); Error number: 27160)
> ------
> ' because it is a primary key column.
> (Source: l07052 (Data source); Error number: 21264)
> ------
> This was a problem a few months ago but seemed to have been resolved.
> In particular, nothing at all has changed recently in the
> IP_Application_Updates table. I'm very happy for that table to remain
> exactly as it is.
> Can anyone suggest a way to fix this? I've got a bunch of users who
> are unable to replicate their database and they'll turn nasty shortly.
> In the absence of an elegant fix I'll cheerfully go with a way to
> change the offending script to something innocuous, but I don't know
> where or why the script exists or why the subscribers are trying to run
> it. I will be very grateful if anyone can help me to get my
> subscribers past this error.
> Many thanks
> Alex
>
|||Hi Gayathri
Thank you for replying so quickly. The problem I have is that the
table in question is in use, and the column in question should be the
PK of that table.
We have a scheduled job which recreates the snapshot once a week, on
Saturday. I've just taken a look and most tables have three records in
sysmergeschemachange, but the IP_Application_Updates table has an
additional four:
exec sp_repldropcolumn '[dbo].[IP_Application_Updates]', 'update_id', 1
alter table [dbo].[IP_Application_Updates] drop column update_id
exec sp_repladdcolumn '[dbo].[IP_Application_Updates]','update_id',[int
IDENTITY (1, 1) NOT NULL], '%', 1
alter table [dbo].[IP_Application_Updates] add update_id int IDENTITY
(1, 1) NOT NULL
This is presumably what's causing the problem. I could perhaps "fix"
it by just deleting those four records from the system table but I can
just imagine causing all manner of havoc with my ignorant tinkering.
Assuming that nobody in my team has had any reason whatsoever to make
any alterations to this table in recent months, is it possible to
hazard a guess as to why the merge agent is attempting to recreate the
update_id column? And is there any way to make it stop?
Thanks
Alex
|||Hi,
The only reason I see a DDL row in 'sysmergeschemachange' table is because
of a user action after
the intial snapshot has been taken.
Trying to delete the rows from the system table could pop up some more
issues.
You could do two things:
1. drop the primary key constraint at the failing subscriber. This should
probably allow
the merge agent to complete succesfully [if this is the only problem].
2. Else, you might have to re-initalize the failing subscribers ...
Hope this helps.
-- Gayathri TK
<WombatDeath@.gmail.com> wrote in message
news:1169255444.925914.213970@.11g2000cwr.googlegro ups.com...
> Hi Gayathri
> Thank you for replying so quickly. The problem I have is that the
> table in question is in use, and the column in question should be the
> PK of that table.
> We have a scheduled job which recreates the snapshot once a week, on
> Saturday. I've just taken a look and most tables have three records in
> sysmergeschemachange, but the IP_Application_Updates table has an
> additional four:
> exec sp_repldropcolumn '[dbo].[IP_Application_Updates]', 'update_id', 1
> alter table [dbo].[IP_Application_Updates] drop column update_id
> exec sp_repladdcolumn '[dbo].[IP_Application_Updates]','update_id',[int
> IDENTITY (1, 1) NOT NULL], '%', 1
> alter table [dbo].[IP_Application_Updates] add update_id int IDENTITY
> (1, 1) NOT NULL
> This is presumably what's causing the problem. I could perhaps "fix"
> it by just deleting those four records from the system table but I can
> just imagine causing all manner of havoc with my ignorant tinkering.
> Assuming that nobody in my team has had any reason whatsoever to make
> any alterations to this table in recent months, is it possible to
> hazard a guess as to why the merge agent is attempting to recreate the
> update_id column? And is there any way to make it stop?
> Thanks
> Alex
>
|||Hi Gayathri
Thanks very much; we'll try dropping the PK constraint and see what
happens.
Thanks again for your help,
Alex

ProClarity Expert Needed for our BI Team - Texas Job Opportunity

Hi All,

We are starting on a tool called Proclarity, which is used on top of Microsoft SQL Server Analysis Services. Having this as the basic idea, I’m not clear on some information as listed below: Any clarification would be very helpful.

1 Having SSRS (SQL Server Reporting Service) available to perform reports, what is Proclarity for Reporting Services. What it does by integrating with SSRS and its advantages. How is it different from Proclarity Web/Desktop professional.

2 Having Business Scorecard manager from Microsoft, what is the purpose of Proclarity KPI Designer, its advantages/disadvantages over BSM? Still proclarity has one more add-on Proclarity for BSM (not sure why we need this)

3 Proclarity Selector – Purpose of this tool?

4. Why is that Proclarity Dashboard server? We can create a view of dashboard using Sharepoint. Not sure about the advantage of this.

Regards, Kart

Hello!

1. This is a project that is added to the SSRS designer in BI-DEV studio. It will start up a ProClarity professional interface in Reporting services and also help you with formatting the report.

2. The KPI-designer is a tool for building and saving KPI:s in ProClarity Analytics server. Its main use is to build KPI:s on top of SSAS2005 cubes. Business scorecard manager is a tool for building balanced scorecards. The main difference is that BSM can use weights for KPI:s and also create hierarchies for KPI:s. BSM can do this with more data sources than ProClarity.

3. This tool helps you with building named sets, like the top 10 best selling products.

4. PDS is a dashboard server. Sharepoint can do a little more than that. The main advantage with PDS is that it is easy to create and visualize KPI:s from Analytics server, without having to write MDX.

HTH

Thomas Ivarsson

|||

Hi Thomas,

Thanks for the Info.

1. When you say Proclarity for Reporting Services.is a project used for formatting reports, how it benifits us. We can do formatting in BI studio of SSRS.

2. When you say BSM can create Hierarchies for KPIs, it is like by adding a KPI to scorecard and adding Row Members (in scorecard view). Is my understanding correct. Doesn't this come automatically in proclarify when we define a KPI using a measure - because the we would have defined hierarchies in cube itself.

3. I hope it is added as a add-on (menu item in toolbar) of Proclarity Designer

4. Got it.

Regards, kart

|||

1. The ProClarity designer in SSRS2005 is better than Reporting Services own. It is a matter of taste.

2. This is from memory, but in BSM you create objectives and place one or serveral KPI:s in that objective. This objective is a KPI that is defined by the KPI:s mapped to it. In ProClarity and SSAS2005(the KPI-tab) the KPI:s you define are not presented in hierarchies but flat. Measures do not have hierarchies in SSAS2005.

3. Selector is placed on the toolbar. It is a part of the Business Logic Server if you install that.

Regards

Thomas Ivarsson

|||

Hi Thomas,

Thanks again and I got good clarity on Proclarity now.

2. Can I conclude like -KPI definition through BSM is better than doing the same using Proclarity KPI Designer and Defining KPI in the Cube itself. (But, still go ahed using Proclarity for Detailed analysis of any value that is defined by KPI)

Regards, Kart

|||

Hi,

In my humble opinion, you should not prefer to use BSM KPIs when you plan to use SSAS2005 as target.

BSM KPIs have many advantatges if you need to define KPIs based on ODBC datasources, Excel or just type the values manually.

However, if the source for the KPI data is SSAS, I will define the KPI at SSAS, making it centrally managed and reusable in other places (like SSRS), and import the KPI tp BSM (using the KPIUtil tool).

HTH,

Jordi Rambla

Solid Quality Learning

|||

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

|||

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

|||

Hello Jordi,

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

Processing SCDs in bulk

I read another post. I'm hoping I'm just doing something wrong, and that the SSIS team wouldn't have done this:

I read in a batch of records that should cause changes in an SCD in some cases. The table is empty originally. In the wizard, its handling changing attibutes ,fixed and historical.

3 records in ( all with the same business key) = 3 records out?

In this case, it should only produce 1 record.

Are you kidding me? There must be a way to set the batch size to 1, right?

Are they all going down the "new" output?

I see your point. Not all of them are new are they and I assume they all valid to be in the pipeline. It'd be nice if the LOOKUP cahce (for that is what it is under the covers) could be updated as a row comes in - a dynamic cache if you will. I haven't much (any in fact) experience with the SCD component.

It could be that this is a hole in the product. Can you post a repro?

-Jamie

|||

For better or worse, this is by design. The data in the pipeline does not update the lookup table when the lookup is performed. Our data pipeline is buffered not single row so the initial row doesn't make it to the destination before the next row is compared and the SCD doesn't have a dynamic cache. If your data is like this then you would need to aggregate it for your initial insert case and then run it through again for your update cases.

Thanks,

Matt

|||thanks for responding. I found a work around by having a for each loop container that holds the dataflow component, and then passing through the set that way in some fashion, its just more complicated.|||

HI, I am very interested in this. Can you tell me what have been your solution? Something like doing inserts first (1st pass) and thn processing updates in another pass.

I need to imlement something like this in my project and it is the only way I found I could do it. If your solution is better that what I just described, can you share it please?

Thank you very much,

Ccote

|||

I put the dataflow inside a for each loop container.

In my case, I can identify ordered subsets of the original set, which I use as my pass criteria

In my case, we're processing policy transactions

e.g.

Policy# Transaction#

ABC 1

ABC 2

XYZ 1

XYZ 2

I want to process all the 1s first, then all the 2s, etc. so that if any SCD in the transaction occurs, it will be reflected.

For each transaction # , I make a pass through the dataflow, so I send through sets, and force the SCD to work. The beginning on my dataflow has a stored procedure call. I pass in the parameter of the transaction # I want it to process.

My SCD still isn't working, but its not related to the above.

hope that helps. good luck.

Processing SCDs in bulk

I read another post. I'm hoping I'm just doing something wrong, and that the SSIS team wouldn't have done this:

I read in a batch of records that should cause changes in an SCD in some cases. The table is empty originally. In the wizard, its handling changing attibutes ,fixed and historical.

3 records in ( all with the same business key) = 3 records out?

In this case, it should only produce 1 record.

Are you kidding me? There must be a way to set the batch size to 1, right?

Are they all going down the "new" output?

I see your point. Not all of them are new are they and I assume they all valid to be in the pipeline. It'd be nice if the LOOKUP cahce (for that is what it is under the covers) could be updated as a row comes in - a dynamic cache if you will. I haven't much (any in fact) experience with the SCD component.

It could be that this is a hole in the product. Can you post a repro?

-Jamie

|||

For better or worse, this is by design. The data in the pipeline does not update the lookup table when the lookup is performed. Our data pipeline is buffered not single row so the initial row doesn't make it to the destination before the next row is compared and the SCD doesn't have a dynamic cache. If your data is like this then you would need to aggregate it for your initial insert case and then run it through again for your update cases.

Thanks,

Matt

|||thanks for responding. I found a work around by having a for each loop container that holds the dataflow component, and then passing through the set that way in some fashion, its just more complicated.|||

HI, I am very interested in this. Can you tell me what have been your solution? Something like doing inserts first (1st pass) and thn processing updates in another pass.

I need to imlement something like this in my project and it is the only way I found I could do it. If your solution is better that what I just described, can you share it please?

Thank you very much,

Ccote

|||

I put the dataflow inside a for each loop container.

In my case, I can identify ordered subsets of the original set, which I use as my pass criteria

In my case, we're processing policy transactions

e.g.

Policy# Transaction#

ABC 1

ABC 2

XYZ 1

XYZ 2

I want to process all the 1s first, then all the 2s, etc. so that if any SCD in the transaction occurs, it will be reflected.

For each transaction # , I make a pass through the dataflow, so I send through sets, and force the SCD to work. The beginning on my dataflow has a stored procedure call. I pass in the parameter of the transaction # I want it to process.

My SCD still isn't working, but its not related to the above.

hope that helps. good luck.

processadmin role

I need a mamber of my team to monitor some jobs set up by a DBA using SQL Job
I dont want to give him sysadmin prviledge
He should be able to see the job history and re-run the job if it fail
Should i give him processadmin rol
what does processadmin role exactly d
ThanksHi,
Process admin Role
--
Members of Processadmin role can only kill a process. Although the
description for this role says that members can manage
the processes running in SQL Server, the only management option they have is
to kill a process.
By giving the process admin job the users cant execute or see the status of
SQL Jobs.
To view the history of job, DBA can assign the execute prev on
"sp_help_jobhistory" in MSDB database to the user.
To view the job status a normal user can execute the procedure "sp_help_job"
(When the user is not a member of the sysadmin group, sp_help_job will
impersonate the SQL Server Agent proxy account,
which is specified using xp_sqlagent_proxy_account. If the proxy account is
not available, sp_help_job will fail.)
Execute a Job:
sp_start_job
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role
can start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:9F863661-784C-45D7-AD32-AE86C88E5493@.microsoft.com...
> I need a mamber of my team to monitor some jobs set up by a DBA using SQL
Jobs
> I dont want to give him sysadmin prviledges
> He should be able to see the job history and re-run the job if it fails
> Should i give him processadmin role
> what does processadmin role exactly do
> Thanks

processadmin role

I need a mamber of my team to monitor some jobs set up by a DBA using SQL Jobs
I dont want to give him sysadmin prviledges
He should be able to see the job history and re-run the job if it fails
Should i give him processadmin role
what does processadmin role exactly do
Thanks
Hi,
Process admin Role
Members of Processadmin role can only kill a process. Although the
description for this role says that members can manage
the processes running in SQL Server, the only management option they have is
to kill a process.
By giving the process admin job the users cant execute or see the status of
SQL Jobs.
To view the history of job, DBA can assign the execute prev on
"sp_help_jobhistory" in MSDB database to the user.
To view the job status a normal user can execute the procedure "sp_help_job"
(When the user is not a member of the sysadmin group, sp_help_job will
impersonate the SQL Server Agent proxy account,
which is specified using xp_sqlagent_proxy_account. If the proxy account is
not available, sp_help_job will fail.)
Execute a Job:
sp_start_job
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role
can start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:9F863661-784C-45D7-AD32-AE86C88E5493@.microsoft.com...
> I need a mamber of my team to monitor some jobs set up by a DBA using SQL
Jobs
> I dont want to give him sysadmin prviledges
> He should be able to see the job history and re-run the job if it fails
> Should i give him processadmin role
> what does processadmin role exactly do
> Thanks