Showing posts with label structure. Show all posts
Showing posts with label structure. Show all posts

Tuesday, March 27, 2012

CURSOR is driving me insane!

We have a tree structure containing section names. Each node is asection name and each section can have subsections. I have to copy thetree structure but need to maintain the parent-child relationshipestablished within the id / parent_id fields. How do i acheive this?

For example i have the tree
Section 1
|-Section 1.1
Section 2
|-Section 2.1

The"Section" table contains 3 fields: id, parent_id, and caption. ID isthe identity of the section record and parent_id contains NULL or theID of this record's parent to create a child. So "Section 1" (id=1,parent_id=null), "Section 2" (id=2, parent_id=null), "Section 1.1"(id=3, parent_id=1), "Section 2.1" (id=4,parent_id=2).

I wouldlike to copy this sucture to create 4 new sections but they need tomaintain their id/parent_id relationships BUT with new IDs. For this icreated the following stored procedure:
------
CREATE PROCEDURE [dbo].[CopySection]
AS
-- Declare a temporary variable table for storing the sections
DECLARE @.tblSection TABLE
(
id int,
parent_id int,
caption varchar(max),
)

DECLARE @.newAgendaID int, @.newSectionID int;
DECLARE @.tid int, @.tparent_id int, @.tcaption varchar(max);

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Copy the desired sections into the local temp variable table
INSERT INTO @.tblSection SELECT id, parent_id, caption FROM tblSection ORDER BY parent_id;

-- Using a cursor, step through all temp sections and add them to the tblSection but note its new ID
DECLARE c1 CURSOR FOR SELECT * FROM @.tblSection ORDER BY parent_id FORUPDATE OF parent_id;
OPEN c1;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption;
WHILE @.@.FETCH_STATUS = 0
BEGIN

-- Insert the new Section and record the identity
INSERT INTO tblSection (agenda_id, parent_id, caption) VALUES (@.tparent_id, @.tcaption);
SET @.newSectionID = SCOPE_IDENTITY();

-- Update the temp variable table with the new identity from the newly created real section in tblSection
-- Update all temp variable records to point to the new parent_id
UPDATE @.tblSection SET parent_id = @.newSectionID WHERE parent_id = @.tid;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption;
END

CLOSE c1
DEALLOCATE c1

END
------

Thecritical "UPDATE @.tblSection" part doesnt seem to update the tempvariable table with the @.newSectionID (the actual section identityobtained after inserting a real record into the tblSection table). Soin the end the inserted records into tblSection still point to theincorrect parent_id instead of the copied record's parent_id.

Maybe I'm using CURSOR incorrectly or not setting a parameter so that it refreshes its recordset? I've tried using both table variables and temp tables but no luck.

I've only quickly scanned your code, but it makes no sense that the UPDATE statement does not work. It should. And since it's not, that means your WHERE condition is not being met.

So, backtracking and looking at your INSERT statement, you have 3 columns listed yet are inserting only 2 values. I am guessing that this is the cause of your problem. You are assigning the @.tcaption value to the parent_id column:

INSERT INTO tblSection (agenda_id,parent_id, caption) VALUES (@.tparent_id,@.tcaption);


|||Great stuff. Thanks Terri. All is good.

Thursday, March 22, 2012

Cursor - Structure of.

I wanted to know the internal workings of a 'cursor'.
What happens inside SQL Server from the time that it is declared, opened,
when it is used and finally closed and deallocated?
Have searched the web - but apart from examples and the pros/cons of using
them - have not come across the information that I want.
Can someone please provide this info to me or direct me to a site that has
this info?
Cheers!
SQLCatz.
I assume you are talking about Server side cursors?
This article might help you:
http://www.perftuning.com/_whitepape...ql_Cursors.pdf
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>I wanted to know the internal workings of a 'cursor'.
> What happens inside SQL Server from the time that it is declared, opened,
> when it is used and finally closed and deallocated?
> Have searched the web - but apart from examples and the pros/cons of using
> them - have not come across the information that I want.
> Can someone please provide this info to me or direct me to a site that has
> this info?
> Cheers!
> SQLCatz.
|||Wei Xiao,
Thank you for the quick response!
But, this is not what I want.
MSSql_Cursors.pdf ~ There is much more information like this available on
SQL BOL. I want the internals.
Cheers!
SQLCatz.
"wei xiao [MSFT]" wrote:

> I assume you are talking about Server side cursors?
> This article might help you:
> http://www.perftuning.com/_whitepape...ql_Cursors.pdf
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>
>
|||what specifically do you want to know?
It depends on the cursor type:
Declare cursor is just metadata operation.
static cursor is the most costly at open time, because the whole result is
generated during open. (async population makes it somewhat less a problem).
keyset requires population of the keys, so it is less expesive at open time.
Dynamic is even less expensive at oepn time, but more expensive at fetch
time. Not all cursor can be dynamic. it depends on the query.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:C634525C-5C5B-473A-B41A-CE4D7D22849C@.microsoft.com...[vbcol=seagreen]
> Wei Xiao,
> Thank you for the quick response!
> But, this is not what I want.
> MSSql_Cursors.pdf ~ There is much more information like this available on
> SQL BOL. I want the internals.
> Cheers!
> SQLCatz.
> "wei xiao [MSFT]" wrote:
rights.[vbcol=seagreen]
opened,[vbcol=seagreen]
using[vbcol=seagreen]
has[vbcol=seagreen]

Cursor - Structure of.

I wanted to know the internal workings of a 'cursor'.
What happens inside SQL Server from the time that it is declared, opened,
when it is used and finally closed and deallocated?
Have searched the web - but apart from examples and the pros/cons of using
them - have not come across the information that I want.
Can someone please provide this info to me or direct me to a site that has
this info?
Cheers!
SQLCatz.I assume you are talking about Server side cursors?
This article might help you:
http://www.perftuning.com/_whitepap...Sql_Cursors.pdf
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>I wanted to know the internal workings of a 'cursor'.
> What happens inside SQL Server from the time that it is declared, opened,
> when it is used and finally closed and deallocated?
> Have searched the web - but apart from examples and the pros/cons of using
> them - have not come across the information that I want.
> Can someone please provide this info to me or direct me to a site that has
> this info?
> Cheers!
> SQLCatz.|||Wei Xiao,
Thank you for the quick response!
But, this is not what I want.
MSSql_Cursors.pdf ~ There is much more information like this available on
SQL BOL. I want the internals.
Cheers!
SQLCatz.
"wei xiao [MSFT]" wrote:

> I assume you are talking about Server side cursors?
> This article might help you:
> http://www.perftuning.com/_whitepap...Sql_Cursors.pdf
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>
>|||what specifically do you want to know?
It depends on the cursor type:
Declare cursor is just metadata operation.
static cursor is the most costly at open time, because the whole result is
generated during open. (async population makes it somewhat less a problem).
keyset requires population of the keys, so it is less expesive at open time.
Dynamic is even less expensive at oepn time, but more expensive at fetch
time. Not all cursor can be dynamic. it depends on the query.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:C634525C-5C5B-473A-B41A-CE4D7D22849C@.microsoft.com...[vbcol=seagreen]
> Wei Xiao,
> Thank you for the quick response!
> But, this is not what I want.
> MSSql_Cursors.pdf ~ There is much more information like this available on
> SQL BOL. I want the internals.
> Cheers!
> SQLCatz.
> "wei xiao [MSFT]" wrote:
>
rights.[vbcol=seagreen]
opened,[vbcol=seagreen]
using[vbcol=seagreen]
has[vbcol=seagreen]

Cursor - Structure of.

I wanted to know the internal workings of a 'cursor'.
What happens inside SQL Server from the time that it is declared, opened,
when it is used and finally closed and deallocated?
Have searched the web - but apart from examples and the pros/cons of using
them - have not come across the information that I want.
Can someone please provide this info to me or direct me to a site that has
this info?
Cheers!
SQLCatz.I assume you are talking about Server side cursors?
This article might help you:
http://www.perftuning.com/_whitepapers/MSSql_Cursors.pdf
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
>I wanted to know the internal workings of a 'cursor'.
> What happens inside SQL Server from the time that it is declared, opened,
> when it is used and finally closed and deallocated?
> Have searched the web - but apart from examples and the pros/cons of using
> them - have not come across the information that I want.
> Can someone please provide this info to me or direct me to a site that has
> this info?
> Cheers!
> SQLCatz.|||Wei Xiao,
Thank you for the quick response!
But, this is not what I want.
MSSql_Cursors.pdf ~ There is much more information like this available on
SQL BOL. I want the internals.
Cheers!
SQLCatz.
"wei xiao [MSFT]" wrote:
> I assume you are talking about Server side cursors?
> This article might help you:
> http://www.perftuning.com/_whitepapers/MSSql_Cursors.pdf
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
> >I wanted to know the internal workings of a 'cursor'.
> > What happens inside SQL Server from the time that it is declared, opened,
> > when it is used and finally closed and deallocated?
> > Have searched the web - but apart from examples and the pros/cons of using
> > them - have not come across the information that I want.
> > Can someone please provide this info to me or direct me to a site that has
> > this info?
> > Cheers!
> > SQLCatz.
>
>|||what specifically do you want to know?
It depends on the cursor type:
Declare cursor is just metadata operation.
static cursor is the most costly at open time, because the whole result is
generated during open. (async population makes it somewhat less a problem).
keyset requires population of the keys, so it is less expesive at open time.
Dynamic is even less expensive at oepn time, but more expensive at fetch
time. Not all cursor can be dynamic. it depends on the query.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:C634525C-5C5B-473A-B41A-CE4D7D22849C@.microsoft.com...
> Wei Xiao,
> Thank you for the quick response!
> But, this is not what I want.
> MSSql_Cursors.pdf ~ There is much more information like this available on
> SQL BOL. I want the internals.
> Cheers!
> SQLCatz.
> "wei xiao [MSFT]" wrote:
> > I assume you are talking about Server side cursors?
> >
> > This article might help you:
> >
> > http://www.perftuning.com/_whitepapers/MSSql_Cursors.pdf
> >
> >
> > --
> > Wei Xiao [MSFT]
> > SQL Server Storage Engine Development
> > http://blogs.msdn.com/weix
> >
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> > news:E6AAEB16-3DB5-422C-A106-0D7E49F24397@.microsoft.com...
> > >I wanted to know the internal workings of a 'cursor'.
> > > What happens inside SQL Server from the time that it is declared,
opened,
> > > when it is used and finally closed and deallocated?
> > > Have searched the web - but apart from examples and the pros/cons of
using
> > > them - have not come across the information that I want.
> > > Can someone please provide this info to me or direct me to a site that
has
> > > this info?
> > > Cheers!
> > > SQLCatz.
> >
> >
> >

Thursday, March 8, 2012

Cubes not available after processing

The problem is that the cube is not available for queries. But process structure has been made, a full process of the dimensions and of the measure groups.

This is what I did do:
I did Import the SSAS Project from my live server to my local machine (laptop). I did some changings in the project in the dimensions and measure groups and deployed the solution to the server. After this I did a process structure. I logged into Analysis Server, opened a mdx script and the cube was available. Then I did a FULL Process of all the dimensions to be sure that everything is allright with them. Then I did reprocess all my Measure Groups.

Now the cube is not available. This happend the second time now. I process the cube by using ascmd.exe executing xmla commands.
After I do a process structure again it works. I can see the cube and the measure data is available after processing the measure groups.

I cannot explain this behaviour. Can you?

Best regards,
Stefoon

What happens is that when you do a full process of a dimension. The structures of any related cubes become "unprocessed". Processing a measure group under the cube does not implicitly re-process the structure of the cube.

After you do a full process of a dimension you will need to reprocess the structure again.

|||

Hi,

I think you are right. I did forget about the fact that the full process destroys the maps auf the dimensions for the cube construct.

Best regards,

Stefoon

Wednesday, March 7, 2012

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..