cursor type error

this is an error which i happen to encounter..can you guys
help me out here..
cursor type should be :rdopenForwardonly
lock type should be :rdConcurReadonly
Rowsetsize should be : 1
how do i solve this error.i tried the isql/w script but
its not working either..any chance you guys know..
thanks
Please post this to the SQL Server Programming newsgroup for assistance
from other SQL developers.
Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.
This posting is provided "as is" with no warranties and confers no rights.

cursor problem

hi,
I have a stored procedure in which i have a cursor cur1.
when there is an error in the SP the cursor is not closed on exit of the SP.
i am currently looping syscursors to find the currently open cursor n then
deallocating it as shown below.. but i realize the user needs perm to access
the syscursors which i dont want to give. what other way can i deallocate
these cursors?
IF EXISTS
(SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
DEALLOCATE UpdateCursor
thanks
IChorCursors are evil. do not use them!
OK. Seriously 99% of the time, there will be a set based alternative.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"ichor" <ichor@.hotmail.com> wrote in message
news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the
> SP.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to
> access the syscursors which i dont want to give. what other way can i
> deallocate these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>|||ichor
You can try on your risk dbcc activecursors to find an active cursor/s. I'd
not recommend you using this command in the production.
"ichor" <ichor@.hotmail.com> wrote in message
news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the
> SP.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to
> access the syscursors which i dont want to give. what other way can i
> deallocate these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>|||Hi
Posting DDL for the procedure may help to answer this question, you may want
to read http://www.aspfaq.com/etiquette.asp?id=5006 and
http://www.aspfaq.com/show.asp?id=2081
If the error can be handled in T-SQL then you should know which cursors are
open and close them!
For error handling check out:
http://www.sommarskog.se/error-handling-II.html
and
http://www.sommarskog.se/error-handling-I.html
A set based solution is usually more efficient than a cursor based one
therefore you may want to see about changing the design to use less cursors.
John
"ichor" wrote:

> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the S
P.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to acce
ss
> the syscursors which i dont want to give. what other way can i deallocate
> these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>
>|||1. Rewrite your error handling so that the SP terminates more easily.
2. Get rid of the cursors altogether.
David Portas
SQL Server MVP
--|||hi where can i learn more about set based solutions?
i would like to avoid cursors completely.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyIeW$LmFHA.2156@.TK2MSFTNGP14.phx.gbl...
> ichor
> You can try on your risk dbcc activecursors to find an active cursor/s.
> I'd not recommend you using this command in the production.
>
> "ichor" <ichor@.hotmail.com> wrote in message
> news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
>|||Hi
I am not sure if there us any one place where you can do this!
A starter may be to look at itzik Ben-Gan's articles in SQL Server magazine:
http://www.windowsitpro.com/Authors...ID/638/638.html
And the book that was co-authored with Tom Moreau
"Advanced Transact-SQL for SQL Server 2000" ISBN: 1893115828
http://search.barnesandnoble.com/bo...3115828

John
"ichor" wrote:

> hi where can i learn more about set based solutions?
> i would like to avoid cursors completely.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uyIeW$LmFHA.2156@.TK2MSFTNGP14.phx.gbl...
>
>|||Hi
Donot use Cursor . They hurt performance badly.
But if you need to use cursor , you may use
CURSOR_STATUS
(
{ 'local' , 'cursor_name' }
| { 'global' , 'cursor_name' }
| { 'variable' , 'cursor_variable' }
)
to find the status of the cursor
and then close it and deallocate it
May this help you solve the problem
With warm regards
Jatinder Singh|||Use a local cursor instead of a global cursor. Local cursors are implicitly
deallocated when the batch that created it terminates. If they are declared
in a stored procedure, then they are deallocated when the stored procedure
exits, unless returned as an output paramenter from the procedure.
DECLARE @.X CURSOR
SET @.X = CURSOR LOCAL [other options] FOR ...
"ichor" <ichor@.hotmail.com> wrote in message
news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the
SP.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to
access
> the syscursors which i dont want to give. what other way can i deallocate
> these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>|||SQL PROGRAMMING STYLE, chapters 8, 9 and 10 for some help.

Cursor is not open

Hi All,
I have a SQL query that open a cursor, the query running well on SQL 2000
query analyser but give me an error message "Cursor is not open" on SQL 2005
management studio.
the query running on the same database, same server (compatibilty level: SQL
2000 (80)).
any help ?
Hi
Can you post the source to be tested?
"Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
news:uUZ4gh8tHHA.4488@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a SQL query that open a cursor, the query running well on SQL 2000
> query analyser but give me an error message "Cursor is not open" on SQL
> 2005 management studio.
> the query running on the same database, same server (compatibilty level:
> SQL 2000 (80)).
> any help ?
>
|||select ID,AutoID
into #Duplo
from Table1,
(select ID,count(id) as T
from Table1
where StoreID= xx
group by id
having count(id)>1) as M1
where Table1.ID=M1.ID
order by Table1.ID,Table1.AutoID
declare @.PrevID int,@.ID int,@.RecautoID int
DECLARE Table1_Cursor CURSOR dynamic FOR
select ID,AutoID
from #Duplo
for update
OPEN Table1_Cursor
FETCH FIRST FROM Table1_Cursor into @.PrevID,@.RecAutoID
FETCH NEXT FROM Table1_Cursor into @.ID,@.RecAutoID
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.PrevID=@.ID
begin
delete Table1 WHERE AutoID=@.RecAutoID
FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
end
else
begin
set @.PrevID=@.ID
FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
end
END
CLOSE Table1_Cursor
DEALLOCATE Table1_Cursor
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:efTJvq8tHHA.4844@.TK2MSFTNGP04.phx.gbl...
> Hi
> Can you post the source to be tested?
>
>
> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
> news:uUZ4gh8tHHA.4488@.TK2MSFTNGP05.phx.gbl...
>
|||Hi
I could not reproduce the problem. What if you execute first SELECT *INTO
... command and then run a cursor, do you still have an error?
"Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
news:%23tgZFy8tHHA.3400@.TK2MSFTNGP03.phx.gbl...
> select ID,AutoID
> into #Duplo
> from Table1,
> (select ID,count(id) as T
> from Table1
> where StoreID= xx
> group by id
> having count(id)>1) as M1
> where Table1.ID=M1.ID
> order by Table1.ID,Table1.AutoID
> declare @.PrevID int,@.ID int,@.RecautoID int
> DECLARE Table1_Cursor CURSOR dynamic FOR
> select ID,AutoID
> from #Duplo
> for update
> OPEN Table1_Cursor
> FETCH FIRST FROM Table1_Cursor into @.PrevID,@.RecAutoID
> FETCH NEXT FROM Table1_Cursor into @.ID,@.RecAutoID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.PrevID=@.ID
> begin
> delete Table1 WHERE AutoID=@.RecAutoID
> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
> end
> else
> begin
> set @.PrevID=@.ID
> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
> end
> END
> CLOSE Table1_Cursor
> DEALLOCATE Table1_Cursor
> GO
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:efTJvq8tHHA.4844@.TK2MSFTNGP04.phx.gbl...
>
|||do you mean SELECT * instead of SELECT ID,AUTOID only to be replaced or what
?
my problem is why it is working on SQL 2000 query analyzer only ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O7LSkG9tHHA.4916@.TK2MSFTNGP05.phx.gbl...
> Hi
> I could not reproduce the problem. What if you execute first SELECT *INTO
> ... command and then run a cursor, do you still have an error?
>
>
> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
> news:%23tgZFy8tHHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||Hi
No it is OK SELECT ID,AUTOID as well
"Mahmoud Amin" <mm.amin@.gmail.com> wrote in message
news:eKTJMY9tHHA.1768@.TK2MSFTNGP04.phx.gbl...
> do you mean SELECT * instead of SELECT ID,AUTOID only to be replaced or
> what ?
> my problem is why it is working on SQL 2000 query analyzer only ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O7LSkG9tHHA.4916@.TK2MSFTNGP05.phx.gbl...
>

Cursor is not open

Hi All,
I have a SQL query that open a cursor, the query running well on SQL 2000
query analyser but give me an error message "Cursor is not open" on SQL 2005
management studio.
the query running on the same database, same server (compatibilty level: SQL
2000 (80)).
any help ?Hi
Can you post the source to be tested?
"Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
news:uUZ4gh8tHHA.4488@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a SQL query that open a cursor, the query running well on SQL 2000
> query analyser but give me an error message "Cursor is not open" on SQL
> 2005 management studio.
> the query running on the same database, same server (compatibilty level:
> SQL 2000 (80)).
> any help ?
>|||select ID,AutoID
into #Duplo
from Table1,
(select ID,count(id) as T
from Table1
where StoreID= xx
group by id
having count(id)>1) as M1
where Table1.ID=M1.ID
order by Table1.ID,Table1.AutoID
declare @.PrevID int,@.ID int,@.RecautoID int
DECLARE Table1_Cursor CURSOR dynamic FOR
select ID,AutoID
from #Duplo
for update
OPEN Table1_Cursor
FETCH FIRST FROM Table1_Cursor into @.PrevID,@.RecAutoID
FETCH NEXT FROM Table1_Cursor into @.ID,@.RecAutoID
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.PrevID=@.ID
begin
delete Table1 WHERE AutoID=@.RecAutoID
FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
end
else
begin
set @.PrevID=@.ID
FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
end
END
CLOSE Table1_Cursor
DEALLOCATE Table1_Cursor
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:efTJvq8tHHA.4844@.TK2MSFTNGP04.phx.gbl...
> Hi
> Can you post the source to be tested?
>
>
> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
> news:uUZ4gh8tHHA.4488@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> I have a SQL query that open a cursor, the query running well on SQL 2000
>> query analyser but give me an error message "Cursor is not open" on SQL
>> 2005 management studio.
>> the query running on the same database, same server (compatibilty level:
>> SQL 2000 (80)).
>> any help ?
>|||Hi
I could not reproduce the problem. What if you execute first SELECT *INTO
... command and then run a cursor, do you still have an error?
"Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
news:%23tgZFy8tHHA.3400@.TK2MSFTNGP03.phx.gbl...
> select ID,AutoID
> into #Duplo
> from Table1,
> (select ID,count(id) as T
> from Table1
> where StoreID= xx
> group by id
> having count(id)>1) as M1
> where Table1.ID=M1.ID
> order by Table1.ID,Table1.AutoID
> declare @.PrevID int,@.ID int,@.RecautoID int
> DECLARE Table1_Cursor CURSOR dynamic FOR
> select ID,AutoID
> from #Duplo
> for update
> OPEN Table1_Cursor
> FETCH FIRST FROM Table1_Cursor into @.PrevID,@.RecAutoID
> FETCH NEXT FROM Table1_Cursor into @.ID,@.RecAutoID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.PrevID=@.ID
> begin
> delete Table1 WHERE AutoID=@.RecAutoID
> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
> end
> else
> begin
> set @.PrevID=@.ID
> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
> end
> END
> CLOSE Table1_Cursor
> DEALLOCATE Table1_Cursor
> GO
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:efTJvq8tHHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> Can you post the source to be tested?
>>
>>
>> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
>> news:uUZ4gh8tHHA.4488@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> I have a SQL query that open a cursor, the query running well on SQL
>> 2000 query analyser but give me an error message "Cursor is not open" on
>> SQL 2005 management studio.
>> the query running on the same database, same server (compatibilty level:
>> SQL 2000 (80)).
>> any help ?
>>
>|||do you mean SELECT * instead of SELECT ID,AUTOID only to be replaced or what
?
my problem is why it is working on SQL 2000 query analyzer only ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O7LSkG9tHHA.4916@.TK2MSFTNGP05.phx.gbl...
> Hi
> I could not reproduce the problem. What if you execute first SELECT *INTO
> ... command and then run a cursor, do you still have an error?
>
>
> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
> news:%23tgZFy8tHHA.3400@.TK2MSFTNGP03.phx.gbl...
>> select ID,AutoID
>> into #Duplo
>> from Table1,
>> (select ID,count(id) as T
>> from Table1
>> where StoreID= xx
>> group by id
>> having count(id)>1) as M1
>> where Table1.ID=M1.ID
>> order by Table1.ID,Table1.AutoID
>> declare @.PrevID int,@.ID int,@.RecautoID int
>> DECLARE Table1_Cursor CURSOR dynamic FOR
>> select ID,AutoID
>> from #Duplo
>> for update
>> OPEN Table1_Cursor
>> FETCH FIRST FROM Table1_Cursor into @.PrevID,@.RecAutoID
>> FETCH NEXT FROM Table1_Cursor into @.ID,@.RecAutoID
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> if @.PrevID=@.ID
>> begin
>> delete Table1 WHERE AutoID=@.RecAutoID
>> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
>> end
>> else
>> begin
>> set @.PrevID=@.ID
>> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
>> end
>> END
>> CLOSE Table1_Cursor
>> DEALLOCATE Table1_Cursor
>> GO
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:efTJvq8tHHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> Can you post the source to be tested?
>>
>>
>> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
>> news:uUZ4gh8tHHA.4488@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> I have a SQL query that open a cursor, the query running well on SQL
>> 2000 query analyser but give me an error message "Cursor is not open"
>> on SQL 2005 management studio.
>> the query running on the same database, same server (compatibilty
>> level: SQL 2000 (80)).
>> any help ?
>>
>>
>|||Hi
No it is OK SELECT ID,AUTOID as well
"Mahmoud Amin" <mm.amin@.gmail.com> wrote in message
news:eKTJMY9tHHA.1768@.TK2MSFTNGP04.phx.gbl...
> do you mean SELECT * instead of SELECT ID,AUTOID only to be replaced or
> what ?
> my problem is why it is working on SQL 2000 query analyzer only ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O7LSkG9tHHA.4916@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> I could not reproduce the problem. What if you execute first SELECT *INTO
>> ... command and then run a cursor, do you still have an error?
>>
>>
>> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
>> news:%23tgZFy8tHHA.3400@.TK2MSFTNGP03.phx.gbl...
>> select ID,AutoID
>> into #Duplo
>> from Table1,
>> (select ID,count(id) as T
>> from Table1
>> where StoreID= xx
>> group by id
>> having count(id)>1) as M1
>> where Table1.ID=M1.ID
>> order by Table1.ID,Table1.AutoID
>> declare @.PrevID int,@.ID int,@.RecautoID int
>> DECLARE Table1_Cursor CURSOR dynamic FOR
>> select ID,AutoID
>> from #Duplo
>> for update
>> OPEN Table1_Cursor
>> FETCH FIRST FROM Table1_Cursor into @.PrevID,@.RecAutoID
>> FETCH NEXT FROM Table1_Cursor into @.ID,@.RecAutoID
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> if @.PrevID=@.ID
>> begin
>> delete Table1 WHERE AutoID=@.RecAutoID
>> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
>> end
>> else
>> begin
>> set @.PrevID=@.ID
>> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
>> end
>> END
>> CLOSE Table1_Cursor
>> DEALLOCATE Table1_Cursor
>> GO
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:efTJvq8tHHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> Can you post the source to be tested?
>>
>>
>> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
>> news:uUZ4gh8tHHA.4488@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> I have a SQL query that open a cursor, the query running well on SQL
>> 2000 query analyser but give me an error message "Cursor is not open"
>> on SQL 2005 management studio.
>> the query running on the same database, same server (compatibilty
>> level: SQL 2000 (80)).
>> any help ?
>>
>>
>>
>

Cursor is not open

Hi All,
I have a SQL query that open a cursor, the query running well on SQL 2000
query analyser but give me an error message "Cursor is not open" on SQL 2005
management studio.
the query running on the same database, same server (compatibilty level: SQL
2000 (80)).
any help ?Hi
Can you post the source to be tested?
"Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
news:uUZ4gh8tHHA.4488@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a SQL query that open a cursor, the query running well on SQL 2000
> query analyser but give me an error message "Cursor is not open" on SQL
> 2005 management studio.
> the query running on the same database, same server (compatibilty level:
> SQL 2000 (80)).
> any help ?
>|||select ID,AutoID
into #Duplo
from Table1,
(select ID,count(id) as T
from Table1
where StoreID= xx
group by id
having count(id)>1) as M1
where Table1.ID=M1.ID
order by Table1.ID,Table1.AutoID
declare @.PrevID int,@.ID int,@.RecautoID int
DECLARE Table1_Cursor CURSOR dynamic FOR
select ID,AutoID
from #Duplo
for update
OPEN Table1_Cursor
FETCH FIRST FROM Table1_Cursor into @.PrevID,@.RecAutoID
FETCH NEXT FROM Table1_Cursor into @.ID,@.RecAutoID
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.PrevID=@.ID
begin
delete Table1 WHERE AutoID=@.RecAutoID
FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
end
else
begin
set @.PrevID=@.ID
FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
end
END
CLOSE Table1_Cursor
DEALLOCATE Table1_Cursor
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:efTJvq8tHHA.4844@.TK2MSFTNGP04.phx.gbl...
> Hi
> Can you post the source to be tested?
>
>
> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
> news:uUZ4gh8tHHA.4488@.TK2MSFTNGP05.phx.gbl...
>|||Hi
I could not reproduce the problem. What if you execute first SELECT *INTO
... command and then run a cursor, do you still have an error?
"Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
news:%23tgZFy8tHHA.3400@.TK2MSFTNGP03.phx.gbl...
> select ID,AutoID
> into #Duplo
> from Table1,
> (select ID,count(id) as T
> from Table1
> where StoreID= xx
> group by id
> having count(id)>1) as M1
> where Table1.ID=M1.ID
> order by Table1.ID,Table1.AutoID
> declare @.PrevID int,@.ID int,@.RecautoID int
> DECLARE Table1_Cursor CURSOR dynamic FOR
> select ID,AutoID
> from #Duplo
> for update
> OPEN Table1_Cursor
> FETCH FIRST FROM Table1_Cursor into @.PrevID,@.RecAutoID
> FETCH NEXT FROM Table1_Cursor into @.ID,@.RecAutoID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.PrevID=@.ID
> begin
> delete Table1 WHERE AutoID=@.RecAutoID
> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
> end
> else
> begin
> set @.PrevID=@.ID
> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
> end
> END
> CLOSE Table1_Cursor
> DEALLOCATE Table1_Cursor
> GO
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:efTJvq8tHHA.4844@.TK2MSFTNGP04.phx.gbl...
>|||do you mean SELECT * instead of SELECT ID,AUTOID only to be replaced or what
?
my problem is why it is working on SQL 2000 query analyzer only ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O7LSkG9tHHA.4916@.TK2MSFTNGP05.phx.gbl...
> Hi
> I could not reproduce the problem. What if you execute first SELECT *INTO
> ... command and then run a cursor, do you still have an error?
>
>
> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
> news:%23tgZFy8tHHA.3400@.TK2MSFTNGP03.phx.gbl...
>|||Hi
No it is OK SELECT ID,AUTOID as well
"Mahmoud Amin" <mm.amin@.gmail.com> wrote in message
news:eKTJMY9tHHA.1768@.TK2MSFTNGP04.phx.gbl...
> do you mean SELECT * instead of SELECT ID,AUTOID only to be replaced or
> what ?
> my problem is why it is working on SQL 2000 query analyzer only ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O7LSkG9tHHA.4916@.TK2MSFTNGP05.phx.gbl...
>

Cursor Help

Hi - Trying to work w/cursors but this is the error I receive:
'Cursorfetch: The number of variables declared in the INTO list must match
that of selected columns.' What am I a doing wrong. Below is my code.
Here's the code:
DECLARE @.Address_Line1 VARCHAR (100)
EXECUTE spGet_Statement_Rate '8', '2005'
--spGet_Statement_Rate looks like this:
--DECLARE RATE_CURSOR CURSOR GLOBAL FOR
--SELECT Address_Line1, Rate_ID FROM COMPANY
OPEN RATE_CURSOR
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
WHILE (@.@.FETCH_STATUS = 0) BEGIN
print @.Address_Line1--FROM RATE_CURSOR
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
END
--
CLOSE RATE_CURSOR
DEALLOCATE RATE_CURSORif u could post the ddl and sample data we could say whether u could do w/o
a cursor itself..
im not a fan of cursor based coding..
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
> Hi - Trying to work w/cursors but this is the error I receive:
> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong. Below is my code.
> Here's the code:
> DECLARE @.Address_Line1 VARCHAR (100)
> EXECUTE spGet_Statement_Rate '8', '2005'
> --spGet_Statement_Rate looks like this:
> --DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> --SELECT Address_Line1, Rate_ID FROM COMPANY
> OPEN RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> WHILE (@.@.FETCH_STATUS = 0) BEGIN
> print @.Address_Line1--FROM RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> END
> --
> CLOSE RATE_CURSOR
> DEALLOCATE RATE_CURSOR
>|||> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong.
The message says it all. Your cursor select statement:
SELECT Address_Line1, Rate_ID FROM COMPANY
has 2 columns specified. However, your FETCH statements:
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
have only 1 column specified.
You can correct the issue either by omitting Rate_ID from the select list or
adding a variable for Rate_ID to your FETCH statements.
You might also revisit your code to see if you can accomplish the task
without a cursor. Set-based processing is often much more efficient than
cursor processing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
> Hi - Trying to work w/cursors but this is the error I receive:
> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong. Below is my code.
> Here's the code:
> DECLARE @.Address_Line1 VARCHAR (100)
> EXECUTE spGet_Statement_Rate '8', '2005'
> --spGet_Statement_Rate looks like this:
> --DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> --SELECT Address_Line1, Rate_ID FROM COMPANY
> OPEN RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> WHILE (@.@.FETCH_STATUS = 0) BEGIN
> print @.Address_Line1--FROM RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> END
> --
> CLOSE RATE_CURSOR
> DEALLOCATE RATE_CURSOR
>|||What do you mean by 'set based processing?' In a nutshell, I need to create
a result set of data. Then for each record returned, I need to execute a
bunch of stored procedures. I understand that cursors aren't the most
efficient, but this will run off hours and only once a month.
"Dan Guzman" wrote:

> The message says it all. Your cursor select statement:
> SELECT Address_Line1, Rate_ID FROM COMPANY
> has 2 columns specified. However, your FETCH statements:
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> have only 1 column specified.
> You can correct the issue either by omitting Rate_ID from the select list
or
> adding a variable for Rate_ID to your FETCH statements.
> You might also revisit your code to see if you can accomplish the task
> without a cursor. Set-based processing is often much more efficient than
> cursor processing.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
>
>|||> What do you mean by 'set based processing?'
Process the whole SET of rows at a time rather than one row at a time.
SQL Server is designed and optimized for set-based operations rather
than for cursor operations.
Aside from performance there are plenty of good reasons not to use
cursors. Set-based code is generally much more concise and easier to
develop, test, debug and maintain.
David Portas
SQL Server MVP
--|||> What do you mean by 'set based processing?' In a nutshell, I need to
> create
> a result set of data. Then for each record returned, I need to execute a
> bunch of stored procedures. I understand that cursors aren't the most
> efficient, but this will run off hours and only once a month.
You can't use set-based processing to execute procs for each row returned.
The point is that you can often use a set-based process instead of a looping
construct. Consider basic example:
DECLARE MyCursor CURSOR GLOBAL FOR
SELECT MyData FROM SomeTable
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyData
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM MyCursor INTO @.MyData
INSERT INTO MyTable(MyData)
VALUES(@.MyData)
END
CLOSE MyCursor
DEALLOCATE MyCursor
The above can also be accomplished with the statement below and perform much
better:
INSERT INTO MyTable (MyData)
SELECT MyData FROM SomeTable
It's best to use inline SQL statements when possible. If you need to keep
code in different procs, you can pass data using temp table instead of a
cursor.
Hope this helps.
Dan Guzman
SQL Server MVP|||Okay, here's the cursor i was going to use (this would be in a stored
procedure):
DECLARE RATE_CURSOR CURSOR GLOBAL FOR
SELECT
S.Statement_ID,
S.Rate_ID,
C.Company_Name,
C.Address_Line1,
C.Address_Line2,
C.City,
C.State,
C.Zip,
C.Phone,
C.creditAllow,
R.Rate_Type,
R.Amount_Per_Transaction,
ISNULL (Amount_Per_Transaction_RCK, 0) AS Amount_Per_Transaction_RCK,
ISNULL (Amount_Per_Transaction_ARC, 0) AS Amount_Per_Transaction_ARC,
R.Percentage_Per_Transaction,
ISNULL (Percentage_Per_Transaction_RCK, 0) AS
Percentage_Per_Transaction_RCK,
ISNULL (Percentage_Per_Transaction_ARC, 0) AS
Percentage_Per_Transaction_ARC,
R.Monthly_Fee,
R.Monthly_minimum,
R.Rate_Per_Batch,
R.Discount_VPT,
R.Discount_Monthly_Fee,
R.Discount_RPB,
R.statement_fee,
R.service_fee,
R.ACHgateway_fee,
R.Reject_company_fee,
R.Reject_customer_fee,
R.ACHRefund_Fee,
R.ChargeBack_fee,
R.BadDE_fee,
R.NOC,
R.reserve_percentage,
R.GrossNet,
R.tranFeeCredit,
R.discountFeeCredit,
R.statementFeeCredit,
R.monthlyminCredit,
R.creditOutReturnFee,
RCK,
ARC,
C.RCKRebate,
C.CCDPPD_rebate_amount,
C.ARC_rebate_amount,
C.isRCK_rebate,
C.isARC_rebate,
C.isCCDPPD_rebate,
COMPANY_ID
FROM ST S, RT R, CO C
WHERE S.Rate_ID = R.Rate_ID and
S.Account_ID = C.Company_ID and
S.statement_Month = @.MONTH and
S.statement_Year = @.YEAR AND
C.ACCOUNT_STATUS = 1
Now, for earch record returned above, I will need to execute about 6 stored
procedures, passing in vaious parameters. I'm unfamilar as to how to loop
through a result set (as opposed to using a cursor) to achieve the same, so
if that's a better option, a code example would be appreciated.|||If you have to iterate through a result set for each row returned then
a cursor may well be the best way to do it. What you should generally
try to avoid is iteration in ANY form - other methods of doing so are
just a cursor in disguise. As you have a legacy proc designed to work
only with single rows you will have to decide whether it's worth a
re-write or if you can live with the cursor solution. The better
alternative in most cases is to avoid designing procs that impose any
single row limitations on your SQL code.
David Portas
SQL Server MVP
--|||To add on to David's response, I'd like to emphasize his point that the real
issue is your procs that operate on one record at a time. Ideally, you can
ditch both the cursor and the single-row procs and instead use set-based
processing. For example:
Instead of:
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM RATE_CURSOR INTO <variable list here>
EXEC MyInsertProc <variable list here>
END
Try:
INSERT INTO MyTable(<column list here> )
SELECT ...
I know that rewriting existing code can be painful and might not be worth
the effort in this case if your cursor performance is acceptable. However,
you should at least be familiar with set-based techniques so you can employ
them going forward as appropriate.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:D1E5E64E-3AA9-40C6-975D-C7A079AD20DF@.microsoft.com...
> Okay, here's the cursor i was going to use (this would be in a stored
> procedure):
> DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> SELECT
> S.Statement_ID,
> S.Rate_ID,
> C.Company_Name,
> C.Address_Line1,
> C.Address_Line2,
> C.City,
> C.State,
> C.Zip,
> C.Phone,
> C.creditAllow,
> R.Rate_Type,
> R.Amount_Per_Transaction,
> ISNULL (Amount_Per_Transaction_RCK, 0) AS Amount_Per_Transaction_RCK,
> ISNULL (Amount_Per_Transaction_ARC, 0) AS Amount_Per_Transaction_ARC,
> R.Percentage_Per_Transaction,
> ISNULL (Percentage_Per_Transaction_RCK, 0) AS
> Percentage_Per_Transaction_RCK,
> ISNULL (Percentage_Per_Transaction_ARC, 0) AS
> Percentage_Per_Transaction_ARC,
> R.Monthly_Fee,
> R.Monthly_minimum,
> R.Rate_Per_Batch,
> R.Discount_VPT,
> R.Discount_Monthly_Fee,
> R.Discount_RPB,
> R.statement_fee,
> R.service_fee,
> R.ACHgateway_fee,
> R.Reject_company_fee,
> R.Reject_customer_fee,
> R.ACHRefund_Fee,
> R.ChargeBack_fee,
> R.BadDE_fee,
> R.NOC,
> R.reserve_percentage,
> R.GrossNet,
> R.tranFeeCredit,
> R.discountFeeCredit,
> R.statementFeeCredit,
> R.monthlyminCredit,
> R.creditOutReturnFee,
> RCK,
> ARC,
> C.RCKRebate,
> C.CCDPPD_rebate_amount,
> C.ARC_rebate_amount,
> C.isRCK_rebate,
> C.isARC_rebate,
> C.isCCDPPD_rebate,
> COMPANY_ID
> FROM ST S, RT R, CO C
> WHERE S.Rate_ID = R.Rate_ID and
> S.Account_ID = C.Company_ID and
> S.statement_Month = @.MONTH and
> S.statement_Year = @.YEAR AND
> C.ACCOUNT_STATUS = 1
> Now, for earch record returned above, I will need to execute about 6
> stored
> procedures, passing in vaious parameters. I'm unfamilar as to how to loop
> through a result set (as opposed to using a cursor) to achieve the same,
> so
> if that's a better option, a code example would be appreciated.

Cursor Error...

...I can't seem to work it out, maybe you can help. I get a
Server: Msg 137, Level 15, State 2, Line 22
Must declare the variable '@.tbl_name'.
when I run...
declare @.counter int, @.tbl_name varchar(40)
select @.counter = 1
declare loop_cursor cursor for SELECT distinct name FROM sysobjects(nolock)
WHERE NAME in (
'marc', 'marc1') and xtype = 'U'
open loop_cursor
fetch loop_cursor into @.tbl_name
WHILE @.@.fetch_status = 0
BEGIN
UPDATE @.tbl_name
SET marcA = substring(marcA,1,2)
WHERE marc = 1
PRINT @.counter
PRINT '____________________________'
PRINT @.tbl_name + 'Post Codes Updated'
PRINT ' '
SELECT @.counter = @.counter + 1
END
FETCH loop_cursor INTO @.tbl_name
CLOSE loop_cursor
DEALLOCATE loop_cursor
am i missing something blatently obvious, it's friday so probably so arrrgh
The creates are as follows for anyone wishing to try it
drop table marc, marc1
create table marc (marc int, marcA varchar(5))
create table marc1 (marc int, marcA varchar(5))
insert into marc values(1, 'ABC')
insert into marc values(2, 'ABC')
insert into marc1 values(1, 'ABC')
insert into marc1 values(2, 'ABC')The SQL Server parser doesn't resolve variables as table/column names.
You'll need to use dynamic SQL to do this in Transact-SQL.
See http://www.sommarskog.se/dynamic_sql.html for an article on dynamic SQL
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:38C354EE-43DC-42D8-B4B9-E503B72D34EA@.microsoft.com...
> ...I can't seem to work it out, maybe you can help. I get a
> Server: Msg 137, Level 15, State 2, Line 22
> Must declare the variable '@.tbl_name'.
> when I run...
> declare @.counter int, @.tbl_name varchar(40)
> select @.counter = 1
> declare loop_cursor cursor for SELECT distinct name FROM
> sysobjects(nolock)
> WHERE NAME in (
> 'marc', 'marc1') and xtype = 'U'
> open loop_cursor
> fetch loop_cursor into @.tbl_name
> WHILE @.@.fetch_status = 0
> BEGIN
> UPDATE @.tbl_name
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1
> PRINT @.counter
> PRINT '____________________________'
> PRINT @.tbl_name + 'Post Codes Updated'
> PRINT ' '
> SELECT @.counter = @.counter + 1
> END
> FETCH loop_cursor INTO @.tbl_name
> CLOSE loop_cursor
> DEALLOCATE loop_cursor
>
> am i missing something blatently obvious, it's friday so probably so
> arrrgh
>
> The creates are as follows for anyone wishing to try it
> drop table marc, marc1
> create table marc (marc int, marcA varchar(5))
> create table marc1 (marc int, marcA varchar(5))
> insert into marc values(1, 'ABC')
> insert into marc values(2, 'ABC')
> insert into marc1 values(1, 'ABC')
> insert into marc1 values(2, 'ABC')|||thanks Dan, I replaced
/*
UPDATE @.tbl_name1
SET marcA = substring(marcA,1,2)
WHERE marc = 1
*/
to dynamic as follows but it only updates the first row of the first
table...Any ideas
Set @.Command = ' UPDATE ' + @.tbl_name + '
SET marcA = substring(marcA,1,2)
WHERE marc = 1 '
Execute sp_executesql @.Command|||> but it only updates the first row of the first
> table...Any ideas
It appears you need a FETCH within the WHILE loop. Try:
declare @.counter int, @.tbl_name varchar(40)
declare @.command nvarchar(4000)
select @.counter = 1
declare loop_cursor cursor for SELECT name FROM sysobjects(nolock)
WHERE NAME in (
'marc', 'marc1') and xtype = 'U'
open loop_cursor
fetch loop_cursor into @.tbl_name
WHILE @.@.fetch_status = 0
BEGIN
Set @.Command = ' UPDATE ' + @.tbl_name + '
SET marcA = substring(marcA,1,2)
WHERE marc = 1 '
Execute sp_executesql @.Command
PRINT @.counter
PRINT '____________________________'
PRINT @.tbl_name + 'Post Codes Updated'
PRINT ' '
SELECT @.counter = @.counter + 1
FETCH loop_cursor INTO @.tbl_name
END
CLOSE loop_cursor
DEALLOCATE loop_cursor
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E06D4572-A6BC-463C-A6E0-17FE3F68E802@.microsoft.com...
> thanks Dan, I replaced
> /*
> UPDATE @.tbl_name1
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1
> */
> to dynamic as follows but it only updates the first row of the first
> table...Any ideas
> Set @.Command = ' UPDATE ' + @.tbl_name + '
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1 '
> Execute sp_executesql @.Command
>|||thankyou Dan, appreciate it.
"Dan Guzman" wrote:

> It appears you need a FETCH within the WHILE loop. Try:
>
> declare @.counter int, @.tbl_name varchar(40)
> declare @.command nvarchar(4000)
> select @.counter = 1
> declare loop_cursor cursor for SELECT name FROM sysobjects(nolock)
> WHERE NAME in (
> 'marc', 'marc1') and xtype = 'U'
> open loop_cursor
> fetch loop_cursor into @.tbl_name
> WHILE @.@.fetch_status = 0
> BEGIN
> Set @.Command = ' UPDATE ' + @.tbl_name + '
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1 '
> Execute sp_executesql @.Command
> PRINT @.counter
> PRINT '____________________________'
> PRINT @.tbl_name + 'Post Codes Updated'
> PRINT ' '
> SELECT @.counter = @.counter + 1
> FETCH loop_cursor INTO @.tbl_name
> END
> CLOSE loop_cursor
> DEALLOCATE loop_cursor
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:E06D4572-A6BC-463C-A6E0-17FE3F68E802@.microsoft.com...
>
>sql

cursor error must declare scalar variable

when the following SELECT is executed via A cursor to SQL 2005 it works

SELECT * FROM LCTRY
WHERE LANGUAGE = 'EN' AND CTRY_CODE = :CTRY

BUT

SELECT * FROM LCTRY
WHERE CTRY_CODE = :CTRY AND LANGUAGE = 'EN'

PRODUCES AN ERROR 000137 MUST DECALRE SCALAR VARIABLE
'@.P1AND'

The application works with MySQL.Hi,

It could be that you are missing single quotes around one of your parameters.

Cheers
C

Cursor

hello; I have an error on the level of declaration of cursor for update:

Sqlstate 37000: the clause update is authorized only for DECLARE CUSOR. which is the solution?

can you post in English? what is the declare statement that caused this error ?|||I don't know French, but did you declare your cursor FOR UPDATE? If you try to update columns that are not allowed, you will get an error. I can tell you error says something about clause UPDATE. Also make sure that your syntax is correct on the cursor. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp|||

I have an error in declaration of Cursor.

sql state : 37000 :The Clause Update is autorized only for Declare Cursor.

|||Can you post the SQL? Sounds like you are doing an update on a non-updatable cursor or the syntax is incorrect.|||the problem is in ODBC for SqlServer 2005?

Cursed Error Messages

Hi everyone,
How do I get the error message?
I have a very long sproc that needs to be done in one transaction. I
have an error happening somewhere in the middle, but with a low enough
severity it doesn't terminate the procedure. To make sure I don't
miss any errors, I am storing @.@.error after every statement:
If @.Error<=@.@.error Set @.Error=@.@.error
that way at the end I can say if @.error<>0 rollback trans.
How do I get the error message? I have the number, and what I get
from sysmessages has the wildcards in it %d and so on.
Also , I can't use Xact_Abort, the web user permissions don't allow
it.

Or better yet is there a better way to do this? Sql has
@.@.total_errors - since the server was started, how about since the
transaction or the sproc was started.
Thanks a ton
Pachydermitis[posted and mailed, please reply in news]

Pachydermitis (dedejavu@.hotmail.com) writes:
> How do I get the error message?
> I have a very long sproc that needs to be done in one transaction. I
> have an error happening somewhere in the middle, but with a low enough
> severity it doesn't terminate the procedure. To make sure I don't
> miss any errors, I am storing @.@.error after every statement:
> If @.Error<=@.@.error Set @.Error=@.@.error
> that way at the end I can say if @.error<>0 rollback trans.
> How do I get the error message? I have the number, and what I get
> from sysmessages has the wildcards in it %d and so on.
> Also , I can't use Xact_Abort, the web user permissions don't allow
> it.
> Or better yet is there a better way to do this? Sql has
> @.@.total_errors - since the server was started, how about since the
> transaction or the sproc was started.

There is no way to get the text of the error message in SQL. You must
catch it on client level.

If your code is as above, there is a serious problem in your error
handling. @.@.error is set after each statement, so you will always
set @.error to 0 above.

I have an article on my web site about error handling, that you may
find useful. http://www.sommarskog.se/error-handling-I.html.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Current Recordset does not support updating. This may be a limitation of the pro

I keep getting this error when I try to update

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

It worked fine in Access, but when I switched to SQL Server it stopped working.

Can someone help! Please!

thx

JustinHave you declared a primary key in SQL Server for the table(s) in your rowset?

-PatP|||Originally posted by Pat Phelan
Have you declared a primary key in SQL Server for the table(s) in your rowset?

-PatP

yes i have|||Is the column that SQL Server recognizes as the primary key included in your keyset? Can you post the DDL (the CREATE TABLE statement) and the DML (the SELECT statement) so we have some more to go on?

-PatP|||Originally posted by Pat Phelan
Is the column that SQL Server recognizes as the primary key included in your keyset? Can you post the DDL (the CREATE TABLE statement) and the DML (the SELECT statement) so we have some more to go on?

-PatP

Actually I found out what the problem was.

Access will allow 'ORDER BY' in the SQL statement, but SQL Server won't.

Thanks for your help though!

Justin|||Originally posted by justinbezanson
Actually I found out what the problem was.

Access will allow 'ORDER BY' in the SQL statement, but SQL Server won't.

Thanks for your help though!

Justin

What do you mean by "SQL Server won't"?
[Order By] is a commonly used in SQL Server. Somthing else must be the cause of the problem and I doubt that it is [Order By].|||Originally posted by GDMI
What do you mean by "SQL Server won't"?
[Order By] is a commonly used in SQL Server. Somthing else must be the cause of the problem and I doubt that it is [Order By]. I had some trouble swallowing that one too, but as long as Justin is happy with his answer, I'm Ok with it. I use ORDER BY all of the time, and have never had any problem with it, even in Recordsets.

-PatP

Current Activity Window always times out on Sql Server

I always get this error 1222 time out if I go on my SQL Server and open
Enterprise Manager, go under management, Current acvtivity.
How do I set up SQL server to show me the activity.
The error is (both on server and client)
Error 1222 Lock request time otu period exceeded.
THanksThis is because it switches out of READ UNCOMMITTED about halfway through
for whatever reason. Use sp_who2 in query analyzer instead. If you're
interested in locking/blocking check out aba_lockinfo
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"freesoul777" <freesoul777@.discussions.microsoft.com> wrote in message
news:74405E36-07ED-4A3A-88D8-5E407505035E@.microsoft.com...
>I always get this error 1222 time out if I go on my SQL Server and open
> Enterprise Manager, go under management, Current acvtivity.
> How do I set up SQL server to show me the activity.
> The error is (both on server and client)
> Error 1222 Lock request time otu period exceeded.
> THanks
>

Current Activity Window always times out on Sql Server

I always get this error 1222 time out if I go on my SQL Server and open
Enterprise Manager, go under management, Current acvtivity.
How do I set up SQL server to show me the activity.
The error is (both on server and client)
Error 1222 Lock request time otu period exceeded.
THanks
This is because it switches out of READ UNCOMMITTED about halfway through
for whatever reason. Use sp_who2 in query analyzer instead. If you're
interested in locking/blocking check out aba_lockinfo
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"freesoul777" <freesoul777@.discussions.microsoft.com> wrote in message
news:74405E36-07ED-4A3A-88D8-5E407505035E@.microsoft.com...
>I always get this error 1222 time out if I go on my SQL Server and open
> Enterprise Manager, go under management, Current acvtivity.
> How do I set up SQL server to show me the activity.
> The error is (both on server and client)
> Error 1222 Lock request time otu period exceeded.
> THanks
>

Current Activity Window always times out on Sql Server

I always get this error 1222 time out if I go on my SQL Server and open
Enterprise Manager, go under management, Current acvtivity.
How do I set up SQL server to show me the activity.
The error is (both on server and client)
Error 1222 Lock request time otu period exceeded.
THanksThis is because it switches out of READ UNCOMMITTED about halfway through
for whatever reason. Use sp_who2 in query analyzer instead. If you're
interested in locking/blocking check out aba_lockinfo
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"freesoul777" <freesoul777@.discussions.microsoft.com> wrote in message
news:74405E36-07ED-4A3A-88D8-5E407505035E@.microsoft.com...
>I always get this error 1222 time out if I go on my SQL Server and open
> Enterprise Manager, go under management, Current acvtivity.
> How do I set up SQL server to show me the activity.
> The error is (both on server and client)
> Error 1222 Lock request time otu period exceeded.
> THanks
>

Cubes process error

Hi all!

After processing my cubes I have this error: Server error : Process error [object doesn't exist] 'Partner' ; ?

Someone can help me?
Thanks!

Try to do this - go to Business Intelligence development studio, open your data source view and then on the diagram do right mouse click choose option "Refresh...". This will tell you what changes are between what you have in SSAS and what is really in database. It looks like one of your dimensions or measure groups is referencing table that does not more exists in source database.

Vidas Matelis

My Blog: http://www.ssas-info.com/content/blogcategory/14/36/

Cubes Generation error. different between MOLAP, ROLAP

I am using one cubes for my sales analysis.
When I try to redesign storage and Process the cube from MOLAP to ROLAP, it
create error with something related to fail to create index.
I didn't change the cube design.
What information I need to be careful ?
Hi Kam.
This error is usally related to Real-Time OLAP. Are you trying to re-design
the storage mode of your cube as a Real-Time? This feature requires special
settings in the relational database.
You may find more information here
http://msdn.microsoft.com/library/de...eties_0o4z.asp
Hope that helps.
"Kam" wrote:

> I am using one cubes for my sales analysis.
> When I try to redesign storage and Process the cube from MOLAP to ROLAP, it
> create error with something related to fail to create index.
> I didn't change the cube design.
> What information I need to be careful ?

Cubes Generation error. different between MOLAP, ROLAP

I am using one cubes for my sales analysis.
When I try to redesign storage and Process the cube from MOLAP to ROLAP, it
create error with something related to fail to create index.
I didn't change the cube design.
What information I need to be careful ?Hi Kam.
This error is usally related to Real-Time OLAP. Are you trying to re-design
the storage mode of your cube as a Real-Time? This feature requires special
settings in the relational database.
You may find more information here
http://msdn.microsoft.com/library/d...ieties_0o4z.asp
Hope that helps.
"Kam" wrote:

> I am using one cubes for my sales analysis.
> When I try to redesign storage and Process the cube from MOLAP to ROLAP, i
t
> create error with something related to fail to create index.
> I didn't change the cube design.
> What information I need to be careful ?

cubes error

Hi,

I want to know, if i can use (work) with the Analysis service tutorial of adventurewoks database in MS XP Pro

i use the SQL server 2005 enterprice edition .

When i deploy my analysis service tutorial, i can't connect to my server?(lesson reviewing cubes and demension, in SQL book online)

I checked my firewall,Protocols, restarted my SQL browser and SQL server,checked my windows authentication and i can't connect to my server.

Help help help.

Not sure if you can install SQL Server Enterprise edition on the XP. But I think you can install Developer edition there.

Try and open Adventure works sample project and try deploying it to the Analysis Server. If you were able to successfully deploy the project, you should be able to browse it as well.

Hope that helps.

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

Cube with oracle + sql server data

Hello,

I have a cube with 1 fact table from SQL Server and 1 dimension from Oracle.

when I process the cube I get this error:

OLE DB error: OLE DB or ODBC error: Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "(null)".; 42000; The OLE DB provider "OraOLEDB.Oracle.1" for linked server "(null)" reported an error. The provider did not give any information about the error.; 42000.

when I remove the fact table of SQL Server and replaced it with a fact from oracle, the cube will process fine.

The system specs is:

Windows 2003 sp1 X64 (AMD CPU)

SQL Server 2005 SP1

Oracle 10.02g with support to 64 bit.

Thanks in advance,

Yoav.

My guess about your situation:

When you trying to create a cube that is based on 2 datasources at some point Analysis Server is trying to figure out better way to construct SQL queries against the relational database.

In some complex situations it might decide it cannot send queries separately to each data source. At this moment Analysis Server constructs a query using OPENROWSET semantics. So it would send a query to SQL Server asking for partial results brought from Oracle. You can trace these SQL queries in your processing dialog. And you'd see OPENROWSET that is failing for you.

There are several solutions. For one you can define linked server in SQL Server pointing to Oracle table/s. This way you can make sure you test connectivity to Oracle ahead of time instead of relying on OPENROWSET. This solution also eliminates the need for 2 datasources.

Second, you can look at your cube design, and try to simplify your measure group-dimension relationships so Analysis Server wouldnt be sending OPENROWSET.

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

Cube throws an error while browsing

Hi,
One of cubes when browsed through proclarity showed
nothing. To debug the same, I browsed through analysis
services and the values were "#ERR". There was no
change to the structure of the cube. The cube
processing log shows that the cube was refreshed
successfully.
I then again reprocessed the cube in "Refresh" mode and
it worked fine.
Any clue of why had this happen to just one of the cube?
I was fortunate that this happenned to a smaller cube,
otherwise I would have been to a toss?
Any advice?
Thanks,
Lakshman.
if double click on #ERR, what error you see?
Wei Zhang
Microsoft OLAP Support