Cursors vs. Queries: project

Showing posts with label project. Show all posts

Tuesday, March 27, 2012

Cursor problem

Hi!

I have taken over an existing project as a consultant. The project is a Webpage programmed in ASP(VB classic) with MS-SQL. This is really not my favourite platform, but I have to make a living somehow.

I have added two extra columns to a table [group_member_project] and want to include these two columns in a Stored Procedure wich uses a cursor.

Now, I think that I made everythin right, and the script works when I analyse it, but the IIS gives me the following error: Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

This procedure is quite long, but Im going to post it anyway:
The only thing I have done in this script is adding to more new columns [SecurityAspects] and [MarketPotential] and added theri corresponding temporary variables. How can I solve this? Im quite desperate! :eek:

CREATE Procedure gl_getVoteResultForProject
(
@.ProjectId int
)
As
DECLARE @.rowcount int
DECLARE @.tmpProjectId int
DECLARE @.tmpProjectName varchar(255)
DECLARE @.tmpInnovation real
DECLARE @.tmpUserneeds real
DECLARE @.tmpSustainability real
DECLARE @.tmpTransferability real
DECLARE @.tmpSecurityAspects real
DECLARE @.tmpMarketPotential real
DECLARE @.tmpFinished bit

DECLARE @.chkProjectId int
DECLARE @.chkInnovation real
DECLARE @.chkUserneeds real
DECLARE @.chkSustainability real
DECLARE @.chkTransferability real
DECLARE @.chkSecurityAspects real
DECLARE @.chkMarketPotential real
DECLARE @.chkGrandTotal real

DECLARE @.numMembProj int

-- Get number of users for this project
SELECT @.numMembProj = COUNT(m.MemberId)
FROM project p INNER JOIN
memeber m INNER JOIN
group_member gm ON m.MemberId = gm.MemberId INNER JOIN
group_member_project gmp ON gm.GroupMemberId = gmp.GroupMemberId ON p.ProjectId = gmp.ProjectId
WHERE (p.ProjectId = @.ProjectId)

/*
Cursor.
Fetches all members in the project
group.
*/
DECLARE projCursor SCROLL CURSOR FOR
SELECT
p.ProjectId,
p.ProjectName,
Innovation,
Userneeds,
Sustainability,
Transferability,
Finished

FROM
project p INNER JOIN
memeber m INNER JOIN
group_member gm ON m.MemberId = gm.MemberId INNER JOIN
group_member_project gmp ON gm.GroupMemberId = gmp.GroupMemberId ON p.ProjectId = gmp.ProjectId
WHERE (p.ProjectId = @.ProjectId)
ORDER BY p.ProjectId ASC
/*
Temp table for storing the result to be returned
*/
CREATE TABLE #chTmpTable
(
ProjectId int NULL,
ProjectName varchar(255) NULL,
Innovation real NULL,
Userneeds real NULL,
Sustainability real NULL,
Transferability real NULL,
SecurityAspects real NULL,
MarketPotential real NULL,
GrandTotal real NULL,
isFinishedByAll bit NOT NULL
)

/*
Temp table for storing 'not finished by all' projects
*/
CREATE TABLE #chTmpTable2
(
ProjectId int NOT NULL DEFAULT 1
)

-- Default value = 0
SELECT @.chkProjectId = 0
-- Open cursor
OPEN projCursor
-- get number of rows in cursor
SELECT @.rowcount = @.@.CURSOR_ROWS
-- loop
WHILE @.rowcount <> 0
BEGIN
-- get next record from cursor
FETCH FROM projCursor
INTO @.tmpProjectId,
@.tmpProjectName,
@.tmpInnovation,
@.tmpUserneeds,
@.tmpSustainability,
@.tmpTransferability,
@.tmpSecurityAspects,
@.tmpMarketPotential,
@.tmpFinished

-- Add to temp table if not finished
IF @.tmpFinished = 0
INSERT INTO #chTmpTable2 (ProjectId) VALUES (@.tmpProjectId)

-- no default value is specified in db, convert NULL values.
IF @.tmpInnovation IS NULL
SELECT @.tmpInnovation = 0

IF @.tmpUserneeds IS NULL
SELECT @.tmpUserneeds = 0

IF @.tmpSustainability IS NULL
SELECT @.tmpSustainability = 0

IF @.tmpTransferability IS NULL
SELECT @.tmpTransferability = 0

IF @.tmpSecurityAspects IS NULL
SELECT @.tmpSecurityAspects = 0

IF @.tmpMarketPotential IS NULL
SELECT @.tmpMarketPotential = 0
/*
checks if we are processing same ProjectId
The projects have several members that all
show up in this cursor
*/
IF @.chkProjectId <> @.tmpProjectId
BEGIN
-- new ProjectId, insert new row into temp table
INSERT INTO #chTmpTable
(ProjectId,
ProjectName,
Innovation,
Userneeds,
Sustainability,
Transferability,
SecurityAspects,
MarketPotential,
GrandTotal,
isFinishedByAll)
VALUES
(@.tmpProjectId,
@.tmpProjectName,
@.tmpInnovation,
@.tmpUserneeds,
@.tmpSustainability,
@.tmpTransferability,
@.tmpSecurityAspects,
@.tmpMarketPotential,
@.tmpInnovation + @.tmpUserneeds + @.tmpSustainability + @.tmpTransferability + @.tmpSecurityAspects + @.tmpMarketPotential,
1)
-- store away current values
SELECT @.chkProjectId = @.tmpProjectId
SELECT @.chkInnovation = @.tmpInnovation
SELECT @.chkUserneeds = @.tmpUserneeds
SELECT @.chkSustainability = @.tmpSustainability
SELECT @.chkTransferability = @.tmpTransferability
SELECT @.chkSecurityAspects = @.tmpSecurityAspects
SELECT @.chkMarketPotential = @.tmpMarketPotential
SELECT @.chkGrandTotal = @.tmpInnovation +
@.tmpUserneeds + @.tmpSustainability +
@.tmpTransferability +
@.tmpSecurityAspects +
@.tmpMarketPotential

END
ELSE
BEGIN
-- same ProjectId, update existing row in temp table
-- add previously saved values to current values

UPDATE #chTmpTable SET
Innovation = @.chkInnovation + @.tmpInnovation,
Userneeds = @.chkUserneeds + @.tmpUserneeds,
Sustainability = @.chkSustainability + @.tmpSustainability,
Transferability = @.chkTransferability + @.tmpTransferability,
SecurityAspects = @.chkSecurityAspects + @.tmpSecurityAspects,
MarketPotential = @.chkMarketPotential + @.tmpMarketPotential,
GrandTotal = @.chkGrandTotal +
@.tmpInnovation +
@.tmpUserneeds +
@.tmpSustainability +
@.tmpTransferability +
@.tmpSecurityAspects +
@.tmpMarketPotential
WHERE
ProjectId = @.chkProjectId

-- store away current values
SELECT @.chkProjectId = @.tmpProjectId
SELECT @.chkInnovation = @.chkInnovation + @.tmpInnovation
SELECT @.chkUserneeds = @.chkUserneeds + @.tmpUserneeds
SELECT @.chkSustainability = @.chkSustainability + @.tmpSustainability
SELECT @.chkTransferability = @.chkTransferability + @.tmpTransferability
SELECT @.chkSecurityAspects = @.chkSecurityAspects + @.tmpSecurityAspects
SELECT @.chkMarketPotential = @.chkMarketPotential + @.tmpMarketPotential
SELECT @.chkGrandTotal = @.chkGrandTotal +
@.tmpInnovation +
@.tmpUserneeds +
@.tmpSustainability +
@.tmpTransferability +
@.tmpSecurityAspects +
@.tmpMarketPotential

END
-- decrement flag
SELECT @.rowcount = @.rowcount - 1

END
-- gbg collection
CLOSE projCursor
DEALLOCATE projCursor

UPDATE #chTmpTable SET isFinishedByAll = 0
WHERE ProjectId IN (SELECT ProjectId FROM #chTmpTable2)

UPDATE #chTmpTable SET
Innovation = Innovation/@.numMembProj,
Userneeds = UserNeeds/@.numMembProj,
Sustainability = Sustainability/@.numMembProj,
Transferability = Transferability/@.numMembProj,
SecurityAspects = SecurityAspects/@.numMembProj,
MarketPotential = MarketPotential/@.numMembProj,
GrandTotal = ( (Innovation/@.numMembProj) +
(UserNeeds/@.numMembProj) +
(Sustainability/@.numMembProj) +
(Transferability/@.numMembProj) +
(SecurityAspects/@.numMembProj) +
(MarketPotential/@.numMembProj))/6

-- return recordset to user
SELECT * FROM #chTmpTable ORDER BY GrandTotal DESC
GO

Regards, Jonas Eriksson - SwedenYou need to update the SELECT portion of your CURSOR definition to include the two new columns.

- OR -

You need to stop using cursors.|||Thanks for the help!!

This is what happens when you are in a hurry, you miss the obvious details and sercheas everywhere else for the problem.

The problem I have NOW is that MS SQL server tells me that one of these new columns is ambigious, which it isnt. How irritating. I really hope I can come up with a solution to that problem soon.

Btw. I know that Your not supposed to use cursors, but I have no choice this time. If I had the time I would have reprogrammed the whole site in PHP5 and MySQL and total OO. The environment I am in now is the total opposite of what I am used to.

Thanks again!

// Jonas

You need to update the SELECT portion of your CURSOR definition to include the two new columns.

- OR -

You need to stop using cursors.|||You can resolve your ambiguity issue by prepending the column names with the table alias for all columns. I like to do this anyway since it explicitly lets me know which table I am pulling the data from.

Good luck!

Saturday, February 25, 2012

Cube Operator

When we are using more than 10 columns in cube operator it is throwing error that maxiimum limit is 10.
but it is mandatory for my project to use more than 10 columns.
So could any one tell how to do this.
If not at all possible by using cube operator how else can this be done to get the same result

The only way to do this is to generate the query with the necessary GROUP BY clauses yourself. The sheer number of combinations that you need to take care of will be huge. You could write a stored procedure or client side script that can generate a query for each combination of GROUP BY and UNION them together to get the same results. It almost seems like this is more suited for OLAP.

cube measure only displays one total

I did something to my cube where it is only displaying a much smaller value for

the sales amount measure than it should.

I have a project with two dimensions and one measure.

After deploying the cube i browsed it and pulled the sales amount into the

details field area. normally i should end up with some 400 + million in dollars instead i get 90+ thousand. It seems as if the cube is not processing fully or the browser tab is somehow being filtered. I checked and there is nothing in the subcube or any other field.

Can someone direct me on how best to debug this?

thanks

I found the problem and thought I would share what I did.

I'm surprised it works this way maybe it is a bug. Perhaps the pros here could add some info to this.

here is what I did.

I have a field called "Rate" which is multiplied by the "Qty" to come up the "Sales Amount" measure. This is done in the DSV as a "Named Calculation" in the measures table.

This same table is also used as a dimension table. In here i considered the "Rate" field to be non-Aggregatable or (IsAggregatable = false). Since i would never add up the rates for a total.

Apparently SSAS does not like this and hence things got really messed up. Switching it back to (IsAggregatable = true) fixed everything.

Hopefully my hair will grow back soon.

Cube Design - Number Max of Dimensions

Hi all,

Some cubes into my project have been designed containing 11 dimensions ? Is there a number max of dimensions? The performances look fine so far but I was wondering if it will be a good idea to split these cubes to have a limited number of dimension per cube.

Thanks a lot for your support

Juan

I think you're a long way off hitting the maximum:
http://msdn2.microsoft.com/en-us/library/ms365363.

The only thing you need to look out for when you start adding lots of dimensions to a cube is that aggregation design will take longer and the you're less likely to get good results using the Storage Design Wizard alone - you'll probably need to do Usage Based Optimisation to get the aggregation design you really need. It's also gradually emerging that putting all your data in one cube with multiple measure groups might not be the most efficient way of designing your cubes - see
http://prologika.com/CS/blogs/blog/archive/2006/06/28/1331.aspx

HTH,

Chris

|||

Chris,

Many Thanks for your feedback.

The tests of performance (processing of cubes) I have made meet our requirements.

Do you think that there will be an impact in the restitution of these cubes (with a lot of dimensions) via Excel (add-in) ?

Does the cube design with a lot of dimensions have an impact in the restitution performances ?

Thanks again.

Juan

|||

What do you mean by 'restitution', sorry? Do you mean query performance? If so, no there's no reason why a cube with many dimensions should perform worse than a cube with few dimensions, although as I said you need to be more careful with your aggregation design when you have many dimensions in order to get good performance.

Chris

|||

Sorry for my english...

I meant 'browse' the cube thanks to Excel (Add_in).

My tests of performance have been done to measure :

- performance to process cubes

- performance to access cubes with Excel

If I understood well, the design has a direct impact in the processing of the cube but not in the browsing of the cube.

Hope to be clearer...

Cheers

Juan

|||

Well, what I was trying to say was that adding dimensions to the cube doesn't necessarily cause worse query performance so long as you pay attention to your aggregation design. But it would not be true to say that cube design in general has no effect on query performance - it does.

Chris

Friday, February 24, 2012

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.
Parser: The syntax for 'Number' is incorrect.
" />
</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

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:

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

Cheers,

Adam

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

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:

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

Cheers,

Adam

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

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:

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

Cheers,

Adam

Cube browsing for endusers

Hi all,

Browsing a cube using SQL server management studio and Analysis servcices project is possible. Is there any possibility to have custom application built to have the option of browisng without opening Studio management or analysis services project?

Purpose of this is simple. End user should simplay have measures and dimensions and he will just drag and drop in to the browse area.

One thing I really like about Microsoft is that almost anything is possible . Programming one cube/dimension browser yourself is quite an interesting task. I'd use AMO to get the cube and it's dimensions structures, and then I'd use those to build up MDX queries when the user drags-n-drops. To enumerate the cube and dimensions is very easy using AMO. The hard thing here is to convert the drag-n-dropping into well formed and accurate MDX. Like did they drop on columns, rows, as a filter etc? Which hierarchy and level did they expand/drill down?

Some resources:

Query using C#: http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=640

AS objects: http://technet.microsoft.com/en-us/library/ms124924.aspx

There is a good example on how to enumerate an OLAP db in the SQL Server samples, which can be downloaded here:

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

After installation the AMO browser is located at c:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Programmability\AMO\AmoBrowser\

Else there is this ProClarity browser which allow end users (say management) to browse a cube in an intuitive way, but that one costs $$$.

Cube Browser

Hi all,
I created a cube in analysis services project in a computer where sql server 2005 is not installed there, when I try to click on Browser tab in the cube, the following error occurs:
TITLE: Microsoft Visual Studio
A connection cannot be made. Ensure that the server is running.
ADDITIONAL INFORMATION:
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)
An existing connection was forcibly closed by the remote host (System)

Anyone have any idea on how to solve it? Thanks in advance.
Daren

Did you deploy your cube? Did you process it?

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

|||Hi Edward,
I tried to deploy it to my server using the ip address, the same connection error came up.
Daren
|||

This could be permission issue.

Check if you have Admin rights to Analysis Server.

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

|||Hi Edward,
Thanks for helping out. But where do I check the rights to Analysis Server? I tried browsing through the properties of the user, could not find anything about Analysis Server.
Daren
|||

Daren.

First, please make sure you install Analysis Services on your machine.

After you installed it, you should be able to deploy your project to Analysis Server.

I strongly recommend you try and work you way through tutorial for Analysis Services. Looks like you are missing very basic knowledge.

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

|||Hi Edward,
To make the picture clear, I already have analysis services installed and go through the tutorial provided in BOL, and I managed to get it to work fine in another computer (which I installed sql server with analysis services in it as well for testing purpose). Then I redo the same steps on the another computer (installed with the same settings), the only difference on both computer are testing pc is windows 2000 with latest sp installed, the other one (that I have problem browsing the data) is using windows server 2003. I have no idea how to go about from there, that's why I posted my question here.
Daren
|||

Are you an Admin on Win2003 machine?

Can you connect to Analysis Services using SQL Management Studio? Can you see your database ?

The project you develop in BI Dev Studio needs to be deployed. Only after you deploy the project to Analysis Server, you will be able to browse your cube.

After you deployed your cube, you can navigate to it in SQL Management studio and browse it from there.

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

Sunday, February 19, 2012

CTE and XQuery

I'ev been playing around with CTE's and trying to query a table that has the following structure

tbl Projects{
id (int),
Project (xml)
}

sample data within the project column is
id = 100
Project =
'<Projects>
<SubProjects>
<id>150</id>
<id>160</id>
<id>170</id>
</SubProjects>
</Projects>'

basically i'm trying to set up a recursive query to get all sub projects for a given project.. this is PART of a bigger query ..

WITH MySubProjects(SubProjects)
AS
(
SELECT TblProjects.*, P.SubProjectIDs.value('(.)[1]','int') AS SubProjects
FROM RB_Projects TblProjects
CROSS APPLY Project.nodes('/Projects/SubProjects/id') AS P(SubProjectIDs)
where id = 100
)

but i keep getting the following error
Incorrect syntax near ')'.

does CTE's not like xquery functions within it's expression?n/m

guess i gotta use the CTE after definine it...

"A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE

statement that
references some or all the CTE columns."
http://msdn2.microsoft.com/en-us/library/ms175972.aspx

Friday, February 17, 2012

CSS in reporting services

Hi All, I need a suggestion.
Actually I am working on a project where in we will be doing 20 - 30 SQL
Reports The Necessity is that we need to assign all the text boxes(for
captions) and the tables with a CSS so that we need not change all the
reports individually if need arises.
Can anyone please guide me in this regard, is such a thing possible.
How to assign a CSS classes for the following
. Textbox
. Tables
. Images
And where to copy the CSS File?
Thanks..On Jul 14, 4:41 am, "Vijay" <techni...@.peoplewareindia.com> wrote:
> Hi All, I need a suggestion.
> Actually I am working on a project where in we will be doing 20 - 30 SQL
> Reports The Necessity is that we need to assign all the text boxes(for
> captions) and the tables with a CSS so that we need not change all the
> reports individually if need arises.
> Can anyone please guide me in this regard, is such a thing possible.
> How to assign a CSS classes for the following
> . Textbox
> . Tables
> . Images
> And where to copy the CSS File?
> Thanks..
This link should offer your CSS customization options.
http://msdn2.microsoft.com/en-us/library/ms345247.aspx
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Tuesday, March 27, 2012

Cursor problem

Saturday, February 25, 2012

Cube Operator

cube measure only displays one total

Cube Design - Number Max of Dimensions

Friday, February 24, 2012

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

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

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

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

Cube browsing for endusers

Cube Browser

Sunday, February 19, 2012

CTE and XQuery

Friday, February 17, 2012

CSS in reporting services

Cursors vs. Queries

Blog Archive

About Me