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!

Cube structure to be "redone" everytime on changing Data Source in Analysis Services20

Hi Folks,

I have a strange discovery. This use to work fine with AS 2000.

Here's the problem : I have a AS 2005 cube "Cube1", existing with all the functionality. I want to test some things - so I did cube "Save As" tstCube1. Now tstCube1 is my test cube. I changed the data source in tstCube1 to a "new" SQL database ( this new database is exactly the same schema structure as compared to the schema that original Cube1 refers). Here's what I see now in tstCube1 -- all the calculated members, relationships in data source view, all hierarchies -- all gone. I don't see them in tstCube1, although I see them in original Cube1.

Do I have to redo the cube structure everytime I change a datasource. This could be a potential nightmare - because on migrating cubes from Dev -> Test--> Production, I have to change the data sources all the time. Does this mean everytime I change the data source -- the whole cube has to be redone.

Your suggestions are most welcomed. Thanks for any ideas...

-- Savvy

There is a difference between moving to a new data source and editing the existing data source to point to a different database. I edit the data source connect string all of the time.

_-_-_ Dave

|||Thanks Dave. That worked..

Cube structure to be "redone" everytime on changing Data Source in Analysis Services20

Hi Folks,

I have a strange discovery. This use to work fine with AS 2000.

Here's the problem : I have a AS 2005 cube "Cube1", existing with all the functionality. I want to test some things - so I did cube "Save As" tstCube1. Now tstCube1 is my test cube. I changed the data source in tstCube1 to a "new" SQL database ( this new database is exactly the same schema structure as compared to the schema that original Cube1 refers). Here's what I see now in tstCube1 -- all the calculated members, relationships in data source view, all hierarchies -- all gone. I don't see them in tstCube1, although I see them in original Cube1.

Do I have to redo the cube structure everytime I change a datasource. This could be a potential nightmare - because on migrating cubes from Dev -> Test--> Production, I have to change the data sources all the time. Does this mean everytime I change the data source -- the whole cube has to be redone.

Your suggestions are most welcomed. Thanks for any ideas...

-- Savvy

There is a difference between moving to a new data source and editing the existing data source to point to a different database. I edit the data source connect string all of the time.

_-_-_ Dave

|||Thanks Dave. That worked..

csv questions

I have two questions.
If I upload a csv with a new value for an existing record will it update the
record, will it error out, or will it create havoc?
I have seen how manually entering a duplicate record can cause issues.
Related to this, i am using Excel as a tool to drop data in to then generate
the CSV.
I did a sample create and upload to a new table, and found that the CSV file
had truncated the text in the individual fields. I am using the database as
a
holder for a multilanguage service, so it really is just text being stored.
But since the csv is cutting the text, the translation fields are all
incomplete.
Is this a CSV limit or an Excel 2003 limit.Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:

> I have two questions.
> If I upload a csv with a new value for an existing record will it update
> the record, will it error out, or will it create havoc?
> I have seen how manually entering a duplicate record can cause issues.
It depends on how you do the update. You could upload to an empty table,
then run an UPDATE query to modify existing rows by joining on the key(s),
and then an APPEND to add new rows to the table.

> Related to this, i am using Excel as a tool to drop data in to then
> generate the CSV.
> I did a sample create and upload to a new table, and found that the CSV
> file had truncated the text in the individual fields. I am using the
> database as a holder for a multilanguage service, so it really is just
> text being stored. But since the csv is cutting the text, the translation
> fields are all incomplete.
> Is this a CSV limit or an Excel 2003 limit.
IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
limit other than the number of characters that can be on a single line,
which is dependent upon on how the data is read.
Dan|||"Daniel Crichton" wrote:

> Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:
>
> It depends on how you do the update. You could upload to an empty table,
> then run an UPDATE query to modify existing rows by joining on the key(s),
> and then an APPEND to add new rows to the table.
>
I am using the potatohead wizard.
I see that it can take an excel file as an import so I may try that.
I have the database on a virtual server now so I can play with it and see
what happens to the data when I move things in and out.

> IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
> limit other than the number of characters that can be on a single line,
> which is dependent upon on how the data is read.
> Dan
>
>

csv questions

I have two questions.
If I upload a csv with a new value for an existing record will it update the
record, will it error out, or will it create havoc?
I have seen how manually entering a duplicate record can cause issues.
Related to this, i am using Excel as a tool to drop data in to then generate
the CSV.
I did a sample create and upload to a new table, and found that the CSV file
had truncated the text in the individual fields. I am using the database as a
holder for a multilanguage service, so it really is just text being stored.
But since the csv is cutting the text, the translation fields are all
incomplete.
Is this a CSV limit or an Excel 2003 limit.
Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:

> I have two questions.
> If I upload a csv with a new value for an existing record will it update
> the record, will it error out, or will it create havoc?
> I have seen how manually entering a duplicate record can cause issues.
It depends on how you do the update. You could upload to an empty table,
then run an UPDATE query to modify existing rows by joining on the key(s),
and then an APPEND to add new rows to the table.

> Related to this, i am using Excel as a tool to drop data in to then
> generate the CSV.
> I did a sample create and upload to a new table, and found that the CSV
> file had truncated the text in the individual fields. I am using the
> database as a holder for a multilanguage service, so it really is just
> text being stored. But since the csv is cutting the text, the translation
> fields are all incomplete.
> Is this a CSV limit or an Excel 2003 limit.
IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
limit other than the number of characters that can be on a single line,
which is dependent upon on how the data is read.
Dan
|||"Daniel Crichton" wrote:

> Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:
>
> It depends on how you do the update. You could upload to an empty table,
> then run an UPDATE query to modify existing rows by joining on the key(s),
> and then an APPEND to add new rows to the table.
>
I am using the potatohead wizard.
I see that it can take an excel file as an import so I may try that.
I have the database on a virtual server now so I can play with it and see
what happens to the data when I move things in and out.

> IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
> limit other than the number of characters that can be on a single line,
> which is dependent upon on how the data is read.
> Dan
>
>

csv questions

I have two questions.
If I upload a csv with a new value for an existing record will it update the
record, will it error out, or will it create havoc?
I have seen how manually entering a duplicate record can cause issues.
Related to this, i am using Excel as a tool to drop data in to then generate
the CSV.
I did a sample create and upload to a new table, and found that the CSV file
had truncated the text in the individual fields. I am using the database as a
holder for a multilanguage service, so it really is just text being stored.
But since the csv is cutting the text, the translation fields are all
incomplete.
Is this a CSV limit or an Excel 2003 limit.Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:
> I have two questions.
> If I upload a csv with a new value for an existing record will it update
> the record, will it error out, or will it create havoc?
> I have seen how manually entering a duplicate record can cause issues.
It depends on how you do the update. You could upload to an empty table,
then run an UPDATE query to modify existing rows by joining on the key(s),
and then an APPEND to add new rows to the table.
> Related to this, i am using Excel as a tool to drop data in to then
> generate the CSV.
> I did a sample create and upload to a new table, and found that the CSV
> file had truncated the text in the individual fields. I am using the
> database as a holder for a multilanguage service, so it really is just
> text being stored. But since the csv is cutting the text, the translation
> fields are all incomplete.
> Is this a CSV limit or an Excel 2003 limit.
IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
limit other than the number of characters that can be on a single line,
which is dependent upon on how the data is read.
Dan|||"Daniel Crichton" wrote:
> Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:
> > I have two questions.
> >
> > If I upload a csv with a new value for an existing record will it update
> > the record, will it error out, or will it create havoc?
> > I have seen how manually entering a duplicate record can cause issues.
> It depends on how you do the update. You could upload to an empty table,
> then run an UPDATE query to modify existing rows by joining on the key(s),
> and then an APPEND to add new rows to the table.
>
I am using the potatohead wizard.
I see that it can take an excel file as an import so I may try that.
I have the database on a virtual server now so I can play with it and see
what happens to the data when I move things in and out.
> > Related to this, i am using Excel as a tool to drop data in to then
> > generate the CSV.
> >
> > I did a sample create and upload to a new table, and found that the CSV
> > file had truncated the text in the individual fields. I am using the
> > database as a holder for a multilanguage service, so it really is just
> > text being stored. But since the csv is cutting the text, the translation
> > fields are all incomplete.
> >
> > Is this a CSV limit or an Excel 2003 limit.
> IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
> limit other than the number of characters that can be on a single line,
> which is dependent upon on how the data is read.
> Dan
>
>