locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
DanIt depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
Eric Li
SQL DBA
MCDBA
Dan wrote:

> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
>
statement.[vbcol=seagreen]|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
statement.[vbcol=seagreen]
>

Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is 'Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is '
>|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

Locking

Can you change the locking granularity on a single table
at the database level? I want to set up a single table at
table level locking without the programmer having to send
a hint.
Thanks for the help,
JoelThis is a multi-part message in MIME format.
--=_NextPart_000_003D_01C354F8.393E6980
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Check out sp_indexoption in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Joel" <joel.griffin@.teijinmonofil.com> wrote in message =news:00b201c35518$a96b8d80$a101280a@.phx.gbl...
Can you change the locking granularity on a single table at the database level? I want to set up a single table at table level locking without the programmer having to send a hint.
Thanks for the help,
Joel
--=_NextPart_000_003D_01C354F8.393E6980
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out sp_indexoption in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Joel" wrote in message news:00b201c35518$a9=6b8d80$a101280a@.phx.gbl...Can you change the locking granularity on a single table at the database =level? I want to set up a single table at table level locking =without the programmer having to send a hint.Thanks for the help,Joel

---

--=_NextPart_000_003D_01C354F8.393E6980--

locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
DanIt depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
--
Eric Li
SQL DBA
MCDBA
Dan wrote:
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
--
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
> > How does SS2000 lock by default for a select, update and delete
statement.
> > Is it row level, page, table?
> >
> > Thanks,
> >
> > Dan
> >
> >|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> > How does SS2000 lock by default for a select, update and delete
statement.
> > Is it row level, page, table?
> >
> > Thanks,
> >
> > Dan
> >
> >
>

Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is ?
Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is ?
>
|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
Dan
It depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
Eric Li
SQL DBA
MCDBA
Dan wrote:

> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
statement.[vbcol=seagreen]
|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the[vbcol=seagreen]
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
statement.
>
sql

Lock Timeout

I am getting this error on a 2000 server:
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.
Nevermind. I figured it out.
"CLM" wrote:

> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 120
> seconds. But the above query fails instantly, so it's not even waiting the
> 120 seconds. What gives? Any tips would be much appreciated.
>

Lock Timeout

I am getting this error on a 2000 server:
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.Nevermind. I figured it out.
"CLM" wrote:

> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 12
0
> seconds. But the above query fails instantly, so it's not even waiting th
e
> 120 seconds. What gives? Any tips would be much appreciated.
>

Lock Timeout

I am getting this error on a 2000 server:
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 120
> seconds. But the above query fails instantly, so it's not even waiting the
> 120 seconds. What gives? Any tips would be much appreciated.
>

Locate Database Object Given The Page

Hi Folks,

Getting the following error after a DBCC CHECKDB

Server: Msg 8906, Level 16, State 1, Line 1
Page (3:15010) in database ID 9 is allocated in the SGAM (3:3) and PFS (3:8088), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

Trying to located the object that the allocation has gone to pot over.

Any quick way to resolve a datbase object using a page ID?

Thank In Advancewhoops. not enough coffee.|||"database ID 9" relates to the the entry in master.dbo.sysdatabases

I need to know what object within this database the page is allocated to.

Thanks

Local Cursor question... please help...

ANy idea what I missed?
I am getting this error Server: Msg 16916, Level 16, State 1, Procedure IMPGrpEscalationX, Line 28
A cursor with the name 'crImgGrp' does not exist.

Thank you.

*************

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE IMPGrpEscalationX

AS

Declare @.IMID int
Declare @.FS1 int
Declare @.FS2 int

Declare crReqRec cursor local for
select IMID from ImplementationGroup where IMStatus= 'Y'
open crReqRec
fetch next from crReqRec
into @.IMID
Set @.FS1 = @.@.fetch_status

Begin
Set @.IMID = (select IMID from Employees_ImplementationGroup where IMID = @.IMID)

Declare crImpGrp cursor local for
select IMID from Employees_ImplementationGroup where IMID = @.IMID

open crImpGrp

fetch next from crImgGrp
into @.IMID
Set @.FS2 = @.@.Fetch_Status
begin
Print 'test'
End

while @.FS2 = 0

fetch next from crImpGrp into
@.IMID
end
while @.FS1 = 0
fetch next from crReqRec into @.IMID

close crImpGrp
deallocate crImpGrp

FETCH next from crReqRec
into
@.IMID

close crReqRec
deallocate crReqRec

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOlooks like your cursor is going out of scope when you fetch the second time.|||how can I fixed the out of scope?

Local Cube

I am trying to create a Local Cube.I get this error: "Column expression associated with level '_TemplateLevel' is not found"
Has anybody else encountered this?

My create statement is:
PROVIDER=MSOLAP;
DATA SOURCE=C:\ControllingExe\CT_BusinessLogic\OLAP\TES TY\t5.cub;
SOURCE_DSN="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;
Initial Catalog=ControllingDTS_v13_GK_PC;Data Source=CTWDR;Connect Timeout=15";

CREATECUBE="CREATE CUBE [test] (
Dimension [MAGAZYN]
DIMENSION_STRUCTURE PARENT_CHILD MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN,
LEVEL [Cao MAGAZYN] TYPE ALL,
LEVEL [MAGAZYN] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_SELF ,
MEASURE [Miara] Function SUM Format 'Standard' )";
INSERTINTO=INSERT INTO [test]
( [MAGAZYN].[MAGAZYN].NAME,
[MAGAZYN].[MAGAZYN].KEY,
[MAGAZYN].[MAGAZYN].parent,
[Measures].[Miara])
SELECT [MAGAZYN].[MAGAZYN],[MAGAZYN].[MAGAZYNID],[MAGAZYN].[MAGAZYN_ParentID1],[test].[Miara]
FROM [test],[MAGAZYN] WHERE [test].[MAGAZYNID] = [MAGAZYN].[MAGAZYNID];Hi,
I have this problem, it seems that it gets solved when you remove
"LEVEL [MAGAZYN] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_SELF ,"
However I can't seem to view the dimension in my control.
What are you using to display the cube in?
Maher

CREATECUBE="CREATE CUBE [test] (
Dimension [MAGAZYN]
DIMENSION_STRUCTURE PARENT_CHILD MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN,
LEVEL [Cao MAGAZYN] TYPE ALL,
LEVEL [MAGAZYN] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_SELF ,
MEASURE [Miara] Function SUM Format 'Standard' )";
INSERTINTO=INSERT INTO [test]
( [MAGAZYN].[MAGAZYN].NAME,
[MAGAZYN].[MAGAZYN].KEY,
[MAGAZYN].[MAGAZYN].parent,
[Measures].[Miara])
SELECT [MAGAZYN].[MAGAZYN],[MAGAZYN].[MAGAZYNID],[MAGAZYN].[MAGAZYN_ParentID1],[test].[Miara]
FROM [test],[MAGAZYN] WHERE [test].[MAGAZYNID] = [MAGAZYN].[MAGAZYNID]; [/SIZE][/QUOTE]|||Hi,
Forget what I have written,
The cube gets build but it does not store any parent-child relationship.

If you have figured out the solution, can you please post it.
Thank you|||Originally posted by mmehchi
Hi,
Forget what I have written,
The cube gets build but it does not store any parent-child relationship.

If you have figured out the solution, can you please post it.
Thank you

Hi ,
I have solved my problem. The correct cube creation statement is:
PROVIDER=MSOLAP;DATA SOURCE=C:\ControllingExe\CT_ActiveX\OLAP\testy\t7. cub;
SOURCE_DSN="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ControllingDTS_v13_GK_PC;Data Source=CTWDR;Connect Timeout=15";
CREATECUBE=
"CREATE CUBE [test] (
DIMENSION [MAGAZYN] DIMENSION_STRUCTURE PARENT_CHILD MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN,
LEVEL [Cao MAGAZYN] TYPE ALL,
LEVEL [MAGAZYN] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_SELF ,
MEASURE [Miara] Function SUM Format 'Standard' )";
INSERTINTO=INSERT INTO [test]
( [MAGAZYN].NAME,
'->NOT [MAGAZYN].[MAGAZYN].NAME
[MAGAZYN].KEY,
'->NOT [MAGAZYN].[MAGAZYN].KEY
[MAGAZYN].PARENT,
'->NOT [MAGAZYN].[MAGAZYN].PARENT
[Measures].[Miara])
SELECT
[MAGAZYN].[MAGAZYN],
[MAGAZYN].[MAGAZYNID],
[MAGAZYN].[MAGAZYN_ParentID1],[test].[Miara] FROM [test],[MAGAZYN] WHERE [test].[MAGAZYNID] = [MAGAZYN].[MAGAZYNID];

I view my local cube in TETA_CONTROLLING aplication.

Kind Regards
Dorota Papiernik|||Hi,
Thank you for you reply. It basically saved me.
However how did you get the following syntax:
MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN
I could not find any documentation about it.
Thank you
Maher Mehchi|||Originally posted by mmehchi
Hi,
Thank you for you reply. It basically saved me.
However how did you get the following syntax:
MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN
I could not find any documentation about it.
Thank you
Maher Mehchi

I get this syntax from dBforums archives
Dorota Papiernik|||can you please help me with this. I think I know the place where its getting messed up. please look for markers /* ... */

[Fact_OK_WV_KS] is the cube and [Standard_OK_WV_KS] is a shared parent-child dimension. The following are the fields from the database table used for creating this dimension.

value for Key : StandardID
value for Name : Goal
value for Parent : ParentStdID

Any help is greatly appreciated. Thanx

Dim strMDX As String
Dim strCube As String
Dim sConnection As String
Dim sLocation As String
Dim sSourceDSN As String
Dim sCreateCube As String
Dim sInsertInto As String

sCreateCube = ""

sLocation = "LOCATION= C:\testLocal.cub"

sSourceDSN = "SOURCE_DSN = ""Provider=MSOLAP;data source=LocalHost;INITIAL CATALOG=test1"""

strCube = "CREATECUBE=CREATE CUBE testLocal ( "

strCube = strCube & " DIMENSION [Standard_OK_WV_KS] DIMENSION_STRUCTURE PARENT_CHILD MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN, "
strCube = strCube & " LEVEL [All Standard] TYPE ALL, "
strCube = strCube & " LEVEL [Standard_OK_WV_KS] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_BLANK_OR_SELF_OR_MISSING, " 'ROOT_MEMBER_IF ROOT_IF_PARENT_IS_SELF ,"

strCube = strCube & " DIMENSION District, "
strCube = strCube & " LEVEL [All District] TYPE ALL, "
strCube = strCube & " LEVEL [District ID], "
strCube = strCube & " LEVEL [School ID], "
strCube = strCube & " LEVEL [Teacher ID], "
strCube = strCube & " LEVEL [Class ID], "
strCube = strCube & " DIMENSION [Test Date], "
strCube = strCube & " LEVEL [All Time] TYPE ALL, "
strCube = strCube & " LEVEL [Year] TYPE YEAR OPTIONS(SORTBYNAME), "
strCube = strCube & " LEVEL [Month] TYPE MONTH OPTIONS(SORTBYKEY),"
strCube = strCube & " LEVEL [Day] TYPE MONTH OPTIONS(SORTBYKEY),"
strCube = strCube & " MEASURE [Total] FUNCTION COUNT FORMAT '#' TYPE DBTYPE_I8, "
strCube = strCube & " MEASURE [Correct] FUNCTION SUM TYPE DBTYPE_I8) "

sInsertInto = ""
sInsertInto = sInsertInto & "INSERTINTO=INSERT INTO testLocal " & vbCrLf
sInsertInto = sInsertInto & " (" & vbCrLf

sInsertInto = sInsertInto & " [Standard_OK_WV_KS].NAME, "
sInsertInto = sInsertInto & " [Standard_OK_WV_KS].KEY, "
sInsertInto = sInsertInto & " [Standard_OK_WV_KS].PARENT, "

sInsertInto = sInsertInto & " [District].[District ID].NAME," & vbCrLf
sInsertInto = sInsertInto & " [District].[District ID].KEY," & vbCrLf
sInsertInto = sInsertInto & " [District].[School ID].NAME," & vbCrLf
sInsertInto = sInsertInto & " [District].[School ID].KEY," & vbCrLf
sInsertInto = sInsertInto & " [District].[Teacher ID].NAME," & vbCrLf
sInsertInto = sInsertInto & " [District].[Teacher ID].KEY," & vbCrLf
sInsertInto = sInsertInto & " [District].[Class ID].NAME," & vbCrLf
sInsertInto = sInsertInto & " [District].[Class ID].KEY," & vbCrLf

sInsertInto = sInsertInto & " [Test Date].[Year].NAME," & vbCrLf
sInsertInto = sInsertInto & " [Test Date].[Year].KEY," & vbCrLf
sInsertInto = sInsertInto & " [Test Date].[Month].NAME," & vbCrLf
sInsertInto = sInsertInto & " [Test Date].[Month].KEY," & vbCrLf
sInsertInto = sInsertInto & " [Test Date].[Day].NAME," & vbCrLf
sInsertInto = sInsertInto & " [Test Date].[Day].KEY," & vbCrLf
sInsertInto = sInsertInto & " [Measures].[Total]," & vbCrLf
sInsertInto = sInsertInto & " [Measures].[Correct] " & vbCrLf

sInsertInto = sInsertInto & ") "
sInsertInto = sInsertInto & "SELECT" & vbCrLf

/* i think the error is in the next 3 lines. Can you please tell me what the values should be for the Parent-child dimensions should be */

/* the database value for the line below is Goal. This is the description for the Dimension. How do I represent that here */
sInsertInto = sInsertInto & "[Fact_OK_WV_KS].[Standard_OK_WV_KS!?] ,"
/* the database value for the line below is StdID. This is the Id for the dimension. How do I represent that here */
sInsertInto = sInsertInto & "[Fact_OK_WV_KS].[Standard_OK_WV_KS!?] , "
/* the database value for the line below is ParentStdID. This is the parent ID . How do I represent that here */
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Standard_OK_WV_KS!??] , "

sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:District ID]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:District ID!KEY]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:School ID]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:School ID!KEY]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:Teacher ID]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:Teacher ID!KEY]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:Class ID]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:Class ID!KEY]," & vbCrLf

sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Year]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Year!KEY]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Month]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Year!KEY]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Day]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Day!KEY]," & vbCrLf

sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Measures:Total]," & vbCrLf
sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Measures:Correct] " & vbCrLf
sInsertInto = sInsertInto & " From [Fact_OK_WV_KS] " & vbCrLf

sInsertInto = sInsertInto & " WHERE (([Fact_OK_WV_KS].[District:School ID] = '[District].[All District ID].[2029].[1908]'))" & vbCrLf
'sInsertInto = sInsertInto & " group by (([Standard_OK_WV_KS], [District], [Test Date] ))"
'sInsertInto = sInsertInto & " OR ([Sales].[Product:Product Family] = '[Product].[All Products].[Non-Consumable]'))" & vbCrLf
'sInsertInto = sInsertInto & "AND (([Sales].[Customers:Country] = '[Customers].[All Customers].[Mexico]')" & vbCrLf
'sInsertInto = sInsertInto & " OR ([Sales].[Customers:Country] = '[Customers].[All Customers].[USA]'))" & vbCrLf

'Response.Write(strCube & "<br>" & sInsertInto)
'Response.End()
Dim objConnection As New ADODB.Connection()

sConnection = sLocation & ";" & vbCrLf & sSourceDSN & ";" & vbCrLf & strCube & ";" & vbCrLf & sInsertInto

objConnection.ConnectionString = sConnection
objConnection.Provider = "MSOLAP"
objConnection.Open()
'Response.Write("cube created")

objConnection.Close()

Thanx again