Error in Transacitonal Replication - SQL Server 2005

Hi,

I'm deploying tran repln between 2 SQL Server 2005 databases and when viewing the 'Distributor to Subscription History' see a error message:

Incorrect syntax near the keyword 'where'. (Source: MSSQLServer, Error number: 156)

On the subscriber, I don't see the snapshot being generated....Please advice.

There are a couple of known issues related to the generation of reconciliation procedures for concurrent snapshot that will lead to the kind of error that you are seeing. These issues can be classified by the following scenarios:

1) You are publishing a table with only timestamp as the primary key column, and the article is configured to replicate timestamp as timestamp on the subscriber (default)

2) You are publishing a table with a primary key consisting only of computed columns

3) You are publishing a table where all non-primary key columns are not updateable (timestamp, computed, or identity)

To find out if any of the above actually applies to your case, you need to find out whether the distribution agent was hitting the error while delivering a .sch script to the subscriber from the agent history (just find the message before the error). Once you have identified the .sch script that was causing the failure, you should open the script in notepad and see if the update statement in the sp_MSins*_msrepl_css procedure was generated incorrectly. If you are indeed hitting one of the known issues mentioned above, you should see that either the update column list or the where clause is empty.

The solution (or workaround) will then depend on which one of the stated scenarios matches your own. If 1) is your case, then you need to replicate the timestamp column as a binary(8) column by disabling the 0x08 schema option via sp_changearticle and then regenerate/reapply the snapshot. Both 2) and 3) can be resolved by disabling concurrent snapshot through sp_changepublication <YourPublicationName>, 'sync_method', 'native', 1, 1 and then regenerating/re-applying the snapshot. Notice that by disabling concurrent snapshot, shared table locks will be taken out for an extended period of time while the snapshot is being generated so you may want to take a less intrusive approach of "fixing" the schema scripts instead. If 2) is your scenario, you can supply a where clause on the primary key parameters. On the other hand, if 3) matches your case, you can simply replace the faulty update statement with a return statement. You can post the problematic procedure definition here if you are not exactly sure how you should modify it.

If you are running the enterprise edition of SQL2005 on your publisher, you can also use the unofficial sync_method of 'database snapshot' to resolve case 2) or 3) without locking out your published tables for an extended period of time while the snapshot is being generated.

Hope that helps,

-Raymond

|||

I am having a similar problem. I know that I fall into the 3rd category. Basically, I want to replicate an identity to the subscriber, keeping the same value as what is in the publisher. I also have a situation where only the publisher can make changes to this table, not the subscriber. Is there any way I can alter the system stored procedures that are used to create the insert stored procedure so that it adds my ID field to the "set" area of the auto generated stored procedure?

Eric

|||

This should be fixed in the latest SP2 CTP and I would encourage you to try that out.

-Raymond

|||

Ray

I was looking through the notes for the SP2 CTP November release and I didn't see anything that mentioned that the problems with Replication were addressed in this Service Pack.

Eric

|||

Eric, if you can post the (sanitized) schema of the table that you are having trouble with, perhaps I can quickly run through it on our current SP2 build and see if your particular issue is resolved.

-Raymond

|||

Raymond,

Try the following (its not the exact table that I'm using, but the concept is the same)

State char(2) PK

County varchar(100) PK

CountyID int identity

Thanks,

Eric

|||

Yep, this is fixed in SP2, you can pick up the CTP here:

http://www.microsoft.com/sql/ctp.mspx

-Raymond

|||Thanks!

Error in Transacitonal Replication - SQL Server 2005

Hi,

I'm deploying tran repln between 2 SQL Server 2005 databases and when viewing the 'Distributor to Subscription History' see a error message:

Incorrect syntax near the keyword 'where'. (Source: MSSQLServer, Error number: 156)

On the subscriber, I don't see the snapshot being generated....Please advice.

There are a couple of known issues related to the generation of reconciliation procedures for concurrent snapshot that will lead to the kind of error that you are seeing. These issues can be classified by the following scenarios:

1) You are publishing a table with only timestamp as the primary key column, and the article is configured to replicate timestamp as timestamp on the subscriber (default)

2) You are publishing a table with a primary key consisting only of computed columns

3) You are publishing a table where all non-primary key columns are not updateable (timestamp, computed, or identity)

To find out if any of the above actually applies to your case, you need to find out whether the distribution agent was hitting the error while delivering a .sch script to the subscriber from the agent history (just find the message before the error). Once you have identified the .sch script that was causing the failure, you should open the script in notepad and see if the update statement in the sp_MSins*_msrepl_css procedure was generated incorrectly. If you are indeed hitting one of the known issues mentioned above, you should see that either the update column list or the where clause is empty.

The solution (or workaround) will then depend on which one of the stated scenarios matches your own. If 1) is your case, then you need to replicate the timestamp column as a binary(8) column by disabling the 0x08 schema option via sp_changearticle and then regenerate/reapply the snapshot. Both 2) and 3) can be resolved by disabling concurrent snapshot through sp_changepublication <YourPublicationName>, 'sync_method', 'native', 1, 1 and then regenerating/re-applying the snapshot. Notice that by disabling concurrent snapshot, shared table locks will be taken out for an extended period of time while the snapshot is being generated so you may want to take a less intrusive approach of "fixing" the schema scripts instead. If 2) is your scenario, you can supply a where clause on the primary key parameters. On the other hand, if 3) matches your case, you can simply replace the faulty update statement with a return statement. You can post the problematic procedure definition here if you are not exactly sure how you should modify it.

If you are running the enterprise edition of SQL2005 on your publisher, you can also use the unofficial sync_method of 'database snapshot' to resolve case 2) or 3) without locking out your published tables for an extended period of time while the snapshot is being generated.

Hope that helps,

-Raymond

|||

I am having a similar problem. I know that I fall into the 3rd category. Basically, I want to replicate an identity to the subscriber, keeping the same value as what is in the publisher. I also have a situation where only the publisher can make changes to this table, not the subscriber. Is there any way I can alter the system stored procedures that are used to create the insert stored procedure so that it adds my ID field to the "set" area of the auto generated stored procedure?

Eric

|||

This should be fixed in the latest SP2 CTP and I would encourage you to try that out.

-Raymond

|||

Ray

I was looking through the notes for the SP2 CTP November release and I didn't see anything that mentioned that the problems with Replication were addressed in this Service Pack.

Eric

|||

Eric, if you can post the (sanitized) schema of the table that you are having trouble with, perhaps I can quickly run through it on our current SP2 build and see if your particular issue is resolved.

-Raymond

|||

Raymond,

Try the following (its not the exact table that I'm using, but the concept is the same)

State char(2) PK

County varchar(100) PK

CountyID int identity

Thanks,

Eric

|||

Yep, this is fixed in SP2, you can pick up the CTP here:

http://www.microsoft.com/sql/ctp.mspx

-Raymond

|||Thanks!

Error in Deployment SSAS Project

Hi all,

I am getting an error while deploying an Analysis services project to my local server.

The error is as follows:

"Internal error: An unexpected exception occured."

I have no idea what this error refers to.....

Regards...

Girija Shankar

Hi,

I encountered this error before.

you should be able to see the detailed erroe message which you can discover from 'Event Viewer'.

Probably you have not started up your Analysis services server (you may want to look at if your analysis services has started up and if not, restart it and deploy your cube again).

Hope it helps.

With best regards,

Yours sincerely,

Error in Deploying Cube

I am working on a BI prototype development. Everything seemed working smoothly and, all of a sudden, I got the following error in deploying the changes made to some properties of a dimension:

Error - 1056964586: Internal error: An unexpected exception occured.

Error - 1055653859: Errors in the metadata manager. An error occuurred when loading the Slot Game Daily Fact measure group, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\BI PoC Slot.0.db\BI PoC 1.96.cub\Slot Game Daily Fact.106.det.xml.'

Since I could not find the way to work around this error, I created a new project and could deploy the new changes without any error for a while. Again, suddenly, I got the same type of error as listed above and can no longer proceed with my development work.

Grateful if someone could advise me how to get rid of the errors and prevent it from heppening again.

This looks like a case of data corruption.

Please file your case with http://connect.microsoft.com/sql. Make sure your are running latest service pack SP2.

Please post it in this forum or on connect site if you ever get the same problem again.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Error in deploying cube

Hi,

I'm getting an internal exception error while deploying the solution. The error is as follows -

"Error - 1056964586: Internal error: An unexpected exception occured."

When deployed for the first time, it went through and subsequent changes processed as well. For a specific reason I had to delete the database and redeploy.

From then on I'm getting this error. Earlier threads said try SP2, still the error persists.

Had to share this solution with my teammate as well and even he is getting this error, even though he is deploying it for the first time.

Any help on this would really help, otherwise I would have to recreate the entire solution, changes etc.

Thanks in advance.

Hi,

I do not know this sepcific error message or number.

But I now - you cannot deploy the cube if you have a role with a user which does not exists. Remove incorrect users from you roles (or try remove all and then deploy again)

I had such issues in most of my deployment scenarios.

Else - does the message come from deployment or processing - try only deploy the solution and do not process.

HANNES

|||

Hi Hannes,

I dont have any user roles defined. Also I'm the administrator of the system and the Analysis Services server as well.

And the error comes when I'm trying to deploy the solution.

|||

"Unexpected Exception" errors usually indicate a bug in the server, and we would require either a crash dump or a copy of the solution in order to diagnose exactly why we're hitting it. I'd recommend opening a support ticket if that option is available to you.

- Brad

|||

I was able to track down the problem in my case.

I had 3 cubes and there was 2 relationships (linked objects), viz. Cube 1 refering Cube 2 and Cube 2 refering Cube 3.

When I removed one of the references, the deployment and processing went through.

I guess this is a limitation not to have such kind of references.

-

Vivek C.

Error in Deploying an Analysis Services Project

Hi,

I am trying to deploy an Analysis Project and I get the error:

Error 1 The Description element at line 292, column 29 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) cannot appear under Envelope/Body/Execute/Command/Batch/Alter/ObjectDefinition/Database/Cubes/Cube/Dimensions/Dimension. 0 0

Any idea?

Thanks in advance,

--Dimitris Doukas

Hi,

This is a defect that will be addressed in the service pack(s); meanwhile, to work around it, please do the followings:

- open each cube editor and, in the first tab (Cube Structure), check each cube dimension if it has a Description set

- remove all those Descriptions (by just specifying an empty string there) and then re-deploy

Adrian.

|||

Hi Adrian,

Thanks for the hint. Actually I had to edit the XML file to erase the description clause. If you just empty the string from the BI Studio if doesn't work.

Regards,

--DImitris Doukas

Error help when deploying a cube

I'm getting an error message and i'm not sure what it's trying to tell me.

Errors

Warning 1 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_rpt_vr_flat_cmb_ih_so, Column: idcol, Value: 441183. 0 0

Warning 2 Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Rpt Vr Flat Cmb Ih So of Dimension: Rpt Vr Flat Cmb Ih So from Database: sdw4, Cube: SDW, Measure Group: Rpt Vr Flat Cmb Ih So, Partition: Rpt Vr Flat Cmb Ih So, Record: 441183. 0 0

Error 3 Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. 0 0

Error 4 Errors in the OLAP storage engine: An error occurred while processing the 'Rpt Vr Flat Cmb Ih So' partition of the 'Rpt Vr Flat Cmb Ih So' measure group for the 'SDW' cube from the sdw4 database. 0 0

Error 5 Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. 0 0

-background-

My cube is just one flat table the only linking it has is to a time table. This table is put together over night for users to query off of, and when we built this table a primary key wasn't a option so we added a auto incermenting col just to give us a key and that is the key is called idcol which i think is telling me something is wrong with that record. but i'm not sure.

suggestions?

Hello! A guess is that you have a factrecord with a time key that is not in the time table/flat file.

HTH

Thomas Ivarsson