Cube Build Fails - The syntax for ''Number'' is incorrect

I am receiving the following error when attempting to build the project server 2007 cube. This error has been occurring since a project manager reported creating an MDX expressions. After creating the MDX expression and getting the cube build failure, the PM deleted the MDX expression. The cube failure has not gone away. Not being a guru in MDX expressions, is that something that is stored somewhere in the Project Server DB(s)? Or does anyone have suggestions on where to to from here?

Thanks for any help anyone can provide.

Project Server - MA854EPMD

DB Server - MA803DBSD\SQL2005_DEV

Error:

Error summary/areas:
CBS message processor failed
CBSOlapProcessingFailure
Queue
GeneralQueueJobFailed
Error details:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="CBS message processor failed">
<error id="17004" name="CBSOlapProcessingFailure" uid="33b225e1-9a18-4861-8745-78e02c0f1732" QueueMessageBody="Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=MA803DBSD\SQL2005_DEV ASDBName=ProjectServer2007AnalysisServicesRepository ASExtraNetAddress= RangeChoice=2 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=01/01/2007 00:00:00 ToDate=08/22/2007 00:00:00 HighPriority=True" Error="Analysis Services session failed with the following error: Failed to process the Analysis Services database ProjectServer2007AnalysisServicesRepository on the MA803DBSD\SQL2005_DEV server. Error: Server: Operation completed with 210 problems logged.&#xA;Parser: The syntax for 'Number' is incorrect.&#xA;" />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed" uid="71377bb7-87a9-43e5-969a-11cf8619eb76" JobUID="0e784c52-7e91-46cb-8aab-d54eb484fd33" ComputerName="MA851EPMD" GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" />
</class>
</general>
</errinfo>

It sounds like you're asking about Analysis Services for Project Server. Moving to the SQL Analysis Services forum.

Cheers,

Adam

|||I'm not sure how the project server cubes work, but normally you would open up the cube in the BI Development Studio to view these. This is probably something in the calculations tab of the cube, although there one or two other areas that expressions can be stored.|||

Creation of the Cube for Project Server 2007 is managed through the Project Server UI. You basically give Project Server the Analysis Server name, the source (Project Server Reporting Database) and what you want to cube to be called. Then click "Build Cube." Very nice when it works. I am quite unfamiliar with where the MDX expressions are kept. Any suggestions on where I may look to try and clean that up?

Cube Build Fails - The syntax for ''Number'' is incorrect

I am receiving the following error when attempting to build the project server 2007 cube. This error has been occurring since a project manager reported creating an MDX expressions. After creating the MDX expression and getting the cube build failure, the PM deleted the MDX expression. The cube failure has not gone away. Not being a guru in MDX expressions, is that something that is stored somewhere in the Project Server DB(s)? Or does anyone have suggestions on where to to from here?

Thanks for any help anyone can provide.

Project Server - MA854EPMD

DB Server - MA803DBSD\SQL2005_DEV

Error:

Error summary/areas:
CBS message processor failed
CBSOlapProcessingFailure
Queue
GeneralQueueJobFailed
Error details:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="CBS message processor failed">
<error id="17004" name="CBSOlapProcessingFailure" uid="33b225e1-9a18-4861-8745-78e02c0f1732" QueueMessageBody="Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=MA803DBSD\SQL2005_DEV ASDBName=ProjectServer2007AnalysisServicesRepository ASExtraNetAddress= RangeChoice=2 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=01/01/2007 00:00:00 ToDate=08/22/2007 00:00:00 HighPriority=True" Error="Analysis Services session failed with the following error: Failed to process the Analysis Services database ProjectServer2007AnalysisServicesRepository on the MA803DBSD\SQL2005_DEV server. Error: Server: Operation completed with 210 problems logged.&#xA;Parser: The syntax for 'Number' is incorrect.&#xA;" />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed" uid="71377bb7-87a9-43e5-969a-11cf8619eb76" JobUID="0e784c52-7e91-46cb-8aab-d54eb484fd33" ComputerName="MA851EPMD" GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" />
</class>
</general>
</errinfo>

It sounds like you're asking about Analysis Services for Project Server. Moving to the SQL Analysis Services forum.

Cheers,

Adam

|||I'm not sure how the project server cubes work, but normally you would open up the cube in the BI Development Studio to view these. This is probably something in the calculations tab of the cube, although there one or two other areas that expressions can be stored.|||

Creation of the Cube for Project Server 2007 is managed through the Project Server UI. You basically give Project Server the Analysis Server name, the source (Project Server Reporting Database) and what you want to cube to be called. Then click "Build Cube." Very nice when it works. I am quite unfamiliar with where the MDX expressions are kept. Any suggestions on where I may look to try and clean that up?

Cube Build Fails - The syntax for ''Number'' is incorrect

I am receiving the following error when attempting to build the project server 2007 cube. This error has been occurring since a project manager reported creating an MDX expressions. After creating the MDX expression and getting the cube build failure, the PM deleted the MDX expression. The cube failure has not gone away. Not being a guru in MDX expressions, is that something that is stored somewhere in the Project Server DB(s)? Or does anyone have suggestions on where to to from here?

Thanks for any help anyone can provide.

Project Server - MA854EPMD

DB Server - MA803DBSD\SQL2005_DEV

Error:

Error summary/areas:
CBS message processor failed
CBSOlapProcessingFailure
Queue
GeneralQueueJobFailed
Error details:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="CBS message processor failed">
<error id="17004" name="CBSOlapProcessingFailure" uid="33b225e1-9a18-4861-8745-78e02c0f1732" QueueMessageBody="Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=MA803DBSD\SQL2005_DEV ASDBName=ProjectServer2007AnalysisServicesRepository ASExtraNetAddress= RangeChoice=2 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=01/01/2007 00:00:00 ToDate=08/22/2007 00:00:00 HighPriority=True" Error="Analysis Services session failed with the following error: Failed to process the Analysis Services database ProjectServer2007AnalysisServicesRepository on the MA803DBSD\SQL2005_DEV server. Error: Server: Operation completed with 210 problems logged.&#xA;Parser: The syntax for 'Number' is incorrect.&#xA;" />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed" uid="71377bb7-87a9-43e5-969a-11cf8619eb76" JobUID="0e784c52-7e91-46cb-8aab-d54eb484fd33" ComputerName="MA851EPMD" GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" />
</class>
</general>
</errinfo>

It sounds like you're asking about Analysis Services for Project Server. Moving to the SQL Analysis Services forum.

Cheers,

Adam

|||I'm not sure how the project server cubes work, but normally you would open up the cube in the BI Development Studio to view these. This is probably something in the calculations tab of the cube, although there one or two other areas that expressions can be stored.|||

Creation of the Cube for Project Server 2007 is managed through the Project Server UI. You basically give Project Server the Analysis Server name, the source (Project Server Reporting Database) and what you want to cube to be called. Then click "Build Cube." Very nice when it works. I am quite unfamiliar with where the MDX expressions are kept. Any suggestions on where I may look to try and clean that up?

Cube Build Fails - The syntax for ''Number'' is incorrect

I am receiving the following error when attempting to build the project server 2007 cube. This error has been occurring since a project manager reported creating an MDX expressions. After creating the MDX expression and getting the cube build failure, the PM deleted the MDX expression. The cube failure has not gone away. Not being a guru in MDX expressions, is that something that is stored somewhere in the Project Server DB(s)? Or does anyone have suggestions on where to to from here?

Thanks for any help anyone can provide.

Project Server - MA854EPMD

DB Server - MA803DBSD\SQL2005_DEV

Error:

Error summary/areas:
CBS message processor failed
CBSOlapProcessingFailure
Queue
GeneralQueueJobFailed
Error details:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="CBS message processor failed">
<error id="17004" name="CBSOlapProcessingFailure" uid="33b225e1-9a18-4861-8745-78e02c0f1732" QueueMessageBody="Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=MA803DBSD\SQL2005_DEV ASDBName=ProjectServer2007AnalysisServicesRepository ASExtraNetAddress= RangeChoice=2 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=01/01/2007 00:00:00 ToDate=08/22/2007 00:00:00 HighPriority=True" Error="Analysis Services session failed with the following error: Failed to process the Analysis Services database ProjectServer2007AnalysisServicesRepository on the MA803DBSD\SQL2005_DEV server. Error: Server: Operation completed with 210 problems logged.&#xA;Parser: The syntax for 'Number' is incorrect.&#xA;" />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed" uid="71377bb7-87a9-43e5-969a-11cf8619eb76" JobUID="0e784c52-7e91-46cb-8aab-d54eb484fd33" ComputerName="MA851EPMD" GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" />
</class>
</general>
</errinfo>

It sounds like you're asking about Analysis Services for Project Server. Moving to the SQL Analysis Services forum.

Cheers,

Adam

|||I'm not sure how the project server cubes work, but normally you would open up the cube in the BI Development Studio to view these. This is probably something in the calculations tab of the cube, although there one or two other areas that expressions can be stored.|||

Creation of the Cube for Project Server 2007 is managed through the Project Server UI. You basically give Project Server the Analysis Server name, the source (Project Server Reporting Database) and what you want to cube to be called. Then click "Build Cube." Very nice when it works. I am quite unfamiliar with where the MDX expressions are kept. Any suggestions on where I may look to try and clean that up?

Cube Browser FAILS to open. It HANGS

Hi, I am working on the Analysis Services Tutorial.

When I am in Business Intelligence Studio and I right-click on the cube and I choose browse to open the cube-browser, the Cube-Browser HANGS FOREVER. it never finishes opening. I always end up using the taskmgr to kill off Business Intelligent Studio. How is this HANGING problem fixed?

AIMDBA

The Event Viewer Applicaiton log is showing the following:

Hanging application devenv.exe, version 8.0.50727.42, hang module hungapp, version 0.0.0.0, hang address 0x00000000.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Fault bucket 230214627.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

AIMDBA

|||

Try to see if re-installing Analysis Services will solve the problem.

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

CTE and data type

I have a recursive CTE and within it is a concatenated string field. It fails with this message

Msg 240, Level 16, State 1, Procedure spGetBill, Line 14

Types don't match between the anchor and the recursive part in column "BID" of recursive query "ExplodedParts".

Here is the T-Sql:

WITH ExplodedParts (PID, CID, BID, PBOMID)

AS (SELECT ParentID, ChildID, convert(varchar,ParentID) as BID, PBOMID

FROM dbo.vwLatestPBOMIDs

WHERE ParentID=@.ParentID

UNION ALL

SELECT NextLevel.ParentID, NextLevel.ChildID, EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID, NextLevel.PBOMID

FROM dbo.vwLatestPBOMIDs AS NextLevel INNER JOIN

ExplodedParts AS EP ON NextLevel.ParentID = EP.CID)

SELECT PID, CID, BID, PBOMId

FROM ExplodedParts AS ExplodedParts_1

ORDER BY PID

The offending Sql is "EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID", which if I change to "convert(varchar,NextLevel.ParentID) AS BID" runs fine. I have tried using the Convert fn in the source view, no difference.

Does anyone have a clue why this is causing the datatype issue?

Any help is appreciated.

In anchor part try changing:

convert(varchar,ParentID) as BID

to:

convert(varchar(max),ParentID) as BID

|||

Change:

convert(varchar, ParentID)

to:

cast(ParentID as varchar(255)) -- or whatever maximum length you need

And change:

EP.PID + '.' + convert(varchar,NextLevel.ParentID)

to:

cast(EP.PID + '.' + convert(varchar,NextLevel.ParentID) as varchar(255))

By default if you don't specify the length for varchar/char/nchar/nvarchar/varbinary/binary in convert, the resulting length will be 30. So your anchor member column is of length 30 whereas the recursive member is 30 + 1 + length(PID). It is good rule to always use explicit cast around expressions so that you know the resulting type and this is more important to follow with recursive CTEs.

|||Thank you, that solved the issue.

CTE and data type

I have a recursive CTE and within it is a concatenated string field. It fails with this message

Msg 240, Level 16, State 1, Procedure spGetBill, Line 14

Types don't match between the anchor and the recursive part in column "BID" of recursive query "ExplodedParts".

Here is the T-Sql:

WITH ExplodedParts (PID, CID, BID, PBOMID)

AS (SELECT ParentID, ChildID, convert(varchar,ParentID) as BID, PBOMID

FROM dbo.vwLatestPBOMIDs

WHERE ParentID=@.ParentID

UNION ALL

SELECT NextLevel.ParentID, NextLevel.ChildID, EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID, NextLevel.PBOMID

FROM dbo.vwLatestPBOMIDs AS NextLevel INNER JOIN

ExplodedParts AS EP ON NextLevel.ParentID = EP.CID)

SELECT PID, CID, BID, PBOMId

FROM ExplodedParts AS ExplodedParts_1

ORDER BY PID

The offending Sql is "EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID", which if I change to "convert(varchar,NextLevel.ParentID) AS BID" runs fine. I have tried using the Convert fn in the source view, no difference.

Does anyone have a clue why this is causing the datatype issue?

Any help is appreciated.

In anchor part try changing:

convert(varchar,ParentID) as BID

to:

convert(varchar(max),ParentID) as BID

|||

Change:

convert(varchar, ParentID)

to:

cast(ParentID as varchar(255)) -- or whatever maximum length you need

And change:

EP.PID + '.' + convert(varchar,NextLevel.ParentID)

to:

cast(EP.PID + '.' + convert(varchar,NextLevel.ParentID) as varchar(255))

By default if you don't specify the length for varchar/char/nchar/nvarchar/varbinary/binary in convert, the resulting length will be 30. So your anchor member column is of length 30 whereas the recursive member is 30 + 1 + length(PID). It is good rule to always use explicit cast around expressions so that you know the resulting type and this is more important to follow with recursive CTEs.

|||Thank you, that solved the issue.