sql error: cursor

Showing posts with label cursor. Show all posts

Tuesday, March 6, 2012

"On Error Resume Next" in SQL Server

I am executing a stored procedure that uses a cursor to do
a CONTAINS search for each record and to produce a result
set from that for output. The trouble is that if a good
old "ignored words" error occurs for one of the records
the procedure stops running. I need this to carry on
running regardless of this as the only reason an error
would occur would be due to bad user input which does not
bother me and can therefore be "ignored".
I have followed the advice in the KB article at
http://support.microsoft.com/default.aspx?scid=kb;en-
us;246800 for formatting input for CONTAINS searches but
there is always some nasty ASCII/UNICODE character that
slips through.
Is there a method to isolate the full-text search (or any
part of the procedure for that matter) and to guarantee
that my stored procedure will run to the end of the cursor?
Any help would be much appreciated.Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Andy Wakeling" wrote:

> I am executing a stored procedure that uses a cursor to do
> a CONTAINS search for each record and to produce a result
> set from that for output. The trouble is that if a good
> old "ignored words" error occurs for one of the records
> the procedure stops running. I need this to carry on
> running regardless of this as the only reason an error
> would occur would be due to bad user input which does not
> bother me and can therefore be "ignored".
> I have followed the advice in the KB article at
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;246800 for formatting input for CONTAINS searches but
> there is always some nasty ASCII/UNICODE character that
> slips through.
> Is there a method to isolate the full-text search (or any
> part of the procedure for that matter) and to guarantee
> that my stored procedure will run to the end of the cursor?
> Any help would be much appreciated.
>|||When you are running your query in query analyzer, does it stop running? If
so, then please post the code. If it doesn't, then it is your code that is
causing it to stop. Just have your calling code ignore the errors and
continue on.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:203501c50ad3$6ee202d0$a601280a@.phx.gbl...
>I am executing a stored procedure that uses a cursor to do
> a CONTAINS search for each record and to produce a result
> set from that for output. The trouble is that if a good
> old "ignored words" error occurs for one of the records
> the procedure stops running. I need this to carry on
> running regardless of this as the only reason an error
> would occur would be due to bad user input which does not
> bother me and can therefore be "ignored".
> I have followed the advice in the KB article at
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;246800 for formatting input for CONTAINS searches but
> there is always some nasty ASCII/UNICODE character that
> slips through.
> Is there a method to isolate the full-text search (or any
> part of the procedure for that matter) and to guarantee
> that my stored procedure will run to the end of the cursor?
> Any help would be much appreciated.|||Louis,
I won't post the actual code as it is a massive SP and
besides, we've tried various tests in QA as well but the
gist is as follows:
DECLARE TestCursor CURSOR FOR /*WHATEVER*/
OPEN TestCursor
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM TestCursor INTO @.SearchText
SET @.SearchText = FormatSearchText(@.SearchText)
/*
This is a UDF with output as per KB article as mentioned.
If anything goes wrong this returns '' as I am not
bothered if it cannot resolve input but note: This can
still output junk that can break the CONTAINS search.
*/
INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
/*
When run in QA, if this query causes an ignored-word error
the SP stops dead. I need it to carry on to the end of the
cursor.
*/
END, CLOSE, DEALLOCATE etc.
SELECT * FROM #TEMPTABLE /* Output of entire cursor */
That's pretty much what I'm trying to achieve. What do you
reckon?
Cheers
Andy

>--Original Message--
>When you are running your query in query analyzer, does
it stop running? If
>so, then please post the code. If it doesn't, then it is
your code that is
>causing it to stop. Just have your calling code ignore
the errors and
>continue on.
>--
>----
--
>Louis Davidson - drsql@.hotmail.com
>SQL Server MVP
>Compass Technology Management - www.compass.net
>Pro SQL Server 2000 Database Design -
>http://www.apress.com/book/bookDisplay.html?bID=266
>Blog - http://spaces.msn.com/members/drsql/
>Note: Please reply to the newsgroups only unless you are
interested in
>consulting services. All other replies may be ignored :)
>"Andy Wakeling" <anonymous@.discussions.microsoft.com>
wrote in message
>news:203501c50ad3$6ee202d0$a601280a@.phx.gbl...
do
result
not
any
cursor?
>
>.
>|||No idea, as I don't use full text search at all. However, if the
formatSearchText can output stuff to cause it to fail, is this text
something that would obviously make it fail? Such that you could clean it
up in the UDF? Hopefully someone else who has used full text search can see
the problem with it. Maybe posting some of the values that cause it to
fail?
Sorry I am not much help on this subject.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:21c301c50cff$5894aea0$a401280a@.phx.gbl...
> Louis,
> I won't post the actual code as it is a massive SP and
> besides, we've tried various tests in QA as well but the
> gist is as follows:
> DECLARE TestCursor CURSOR FOR /*WHATEVER*/
> OPEN TestCursor
> WHILE (1 = 1)
> BEGIN
> FETCH NEXT FROM TestCursor INTO @.SearchText
> SET @.SearchText = FormatSearchText(@.SearchText)
> /*
> This is a UDF with output as per KB article as mentioned.
> If anything goes wrong this returns '' as I am not
> bothered if it cannot resolve input but note: This can
> still output junk that can break the CONTAINS search.
> */
> INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
> WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
> /*
> When run in QA, if this query causes an ignored-word error
> the SP stops dead. I need it to carry on to the end of the
> cursor.
> */
> END, CLOSE, DEALLOCATE etc.
> SELECT * FROM #TEMPTABLE /* Output of entire cursor */
> That's pretty much what I'm trying to achieve. What do you
> reckon?
> Cheers
> Andy
>
> it stop running? If
> your code that is
> the errors and
> --
> interested in
> wrote in message
> do
> result
> not
> any
> cursor?

Friday, February 24, 2012

"Invalid Cursor State" - REPOST

MS-SQL 2000, Service Pack 3a and 4
I have customers that are starting to get "Invalid Cursor State" messages
returned from my Clarion MS-SQL. based application.
It appears to happen when i am issuing a "SELECT SUM(TOTAL) FROM..."
statement, but i can be sure of that. I cant pinpoint when the problem
starts. The solution has been to close the screen im on, which closes
connection to the database.
Where can i start to look for this problem?
TIA
Tim MorrisonTim Morrison wrote:
> I have customers that are starting to get "Invalid Cursor State"
> messages returned from my Clarion MS-SQL. based application.
>
Tried reading this article?
http://support.microsoft.com/kb/831997
- Peter
--
Hi! I'm a .signature *virus*!
Copy me into your ~/.signature to help me spread!|||Yup. SP4 is applied to the machine giving me the most problems. It does
however happen to SP3a machines as well.
Tim
"Peter Lykkegaard" <plykkegaard@.nospam.nospam> wrote in message
news:eZOsIYKPHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Tim Morrison wrote:
>> I have customers that are starting to get "Invalid Cursor State"
>> messages returned from my Clarion MS-SQL. based application.
> Tried reading this article?
> http://support.microsoft.com/kb/831997
> - Peter
> --
> Hi! I'm a .signature *virus*!
> Copy me into your ~/.signature to help me spread!
>

Thursday, February 23, 2012

"Invalid Cursor State" - REPOST

MS-SQL 2000, Service Pack 3a and 4
I have customers that are starting to get "Invalid Cursor State" messages
returned from my Clarion MS-SQL. based application.
It appears to happen when i am issuing a "SELECT SUM(TOTAL) FROM..."
statement, but i can be sure of that. I cant pinpoint when the problem
starts. The solution has been to close the screen im on, which closes
connection to the database.
Where can i start to look for this problem?
TIA
Tim MorrisonTim Morrison wrote:

> I have customers that are starting to get "Invalid Cursor State"
> messages returned from my Clarion MS-SQL. based application.
>
Tried reading this article?
http://support.microsoft.com/kb/831997
- Peter
Hi! I'm a .signature *virus*!
Copy me into your ~/.signature to help me spread!|||Yup. SP4 is applied to the machine giving me the most problems. It does
however happen to SP3a machines as well.
Tim
"Peter Lykkegaard" <plykkegaard@.nospam.nospam> wrote in message
news:eZOsIYKPHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Tim Morrison wrote:
>
> Tried reading this article?
> http://support.microsoft.com/kb/831997
> - Peter
> --
> Hi! I'm a .signature *virus*!
> Copy me into your ~/.signature to help me spread!
>

"Invalid Cursor Error"

. But a
few nightly hours searching can drive you mad every now and then; :-D

> 2) What's your code? Do you use ADO or something like that?
>
Just plain simple Enterprise Manager. I tried to work around it with the web
data administrator but that didn't work either.

> I can tell you that *no* alter table /alter column statement supports that
> natively..
> --does *not* work
> ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
> Therefore, I think that the 'cursor error' is raised because some
> object/component that you use, did not test this possibility and now it
bugs
> out...
>
Yep that's what I figured out as well. Enterprise Manager generates some
extra statements to support that I've read somewhere.

> 3) Having the latest ODBC / oledb drivers might help.
> Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so
on...
>
I will check that...

> 4)
> this below works...
>
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Table1
> (
> nID int NOT NULL IDENTITY (1, 1),
> adfs char(10) NULL
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 ON
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
> SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> ALTER TABLE dbo.Table1 ADD CONSTRAINT
> PK_Table1 PRIMARY KEY CLUSTERED
> (
> nID
> ) ON [PRIMARY]
> GO
> COMMIT
>
I will try that as well. It's always usefull to have such workarounds.
Tnx a lot 4 your time.
yours sincerly
Michael

"Invalid Cursor Error"

Sunday, February 19, 2012

"Invalid Cursor Error"

Dear,
Does anyone have a clue when Microsoft is going to release it's next service
pack for SQL-Server? I'm running build 859 at this moment and I can't alter
any table anymore in my enterprise manager. ALTER TABLE is fine as long as
you don't wish to change a column into an identity column. I do not intend
to spend money on this by calling the support line, since I know what the
problem is and spending money on this seems absurd. There is no kb article
for that error as well(although it's is fixed in higher builds than 859) so
there is a big chance they don't want to give me a higher build because of
that (at least that is what appears from other articles). Anyone who can
help me? My development time reduces drastically and reinstalling isn't an
option!
yours sincerly
Michael"Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Dear,
> Does anyone have a clue when Microsoft is going to release it's next
service
> pack for SQL-Server? I'm running build 859 at this moment and I can't
alter
> any table anymore in my enterprise manager. ALTER TABLE is fine as long as
> you don't wish to change a column into an identity column. I do not intend
> to spend money on this by calling the support line, since I know what the
> problem is and spending money on this seems absurd. There is no kb article
1) If they can't fix that problem, you dont have to spend money, they'll pay
that back.
2) What's your code? Do you use ADO or something like that?
I can tell you that *no* alter table /alter column statement supports that
natively..
--does *not* work
ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
Therefore, I think that the 'cursor error' is raised because some
object/component that you use, did not test this possibility and now it bugs
out...
3) Having the latest ODBC / oledb drivers might help.
Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so on...
4)
this below works...
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
nID int NOT NULL IDENTITY (1, 1),
adfs char(10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
nID
) ON [PRIMARY]
GO
COMMIT
> for that error as well(although it's is fixed in higher builds than 859)
so
> there is a big chance they don't want to give me a higher build because of
> that (at least that is what appears from other articles). Anyone who can
> help me? My development time reduces drastically and reinstalling isn't an
> option!
> yours sincerly
> Michael
>|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.invalid> wrote in
message news:#BisWlsNEHA.1276@.TK2MSFTNGP11.phx.gbl...
> "Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
> news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> > Dear,
> >
> > Does anyone have a clue when Microsoft is going to release it's next
> service
> > pack for SQL-Server? I'm running build 859 at this moment and I can't
> alter
> > any table anymore in my enterprise manager. ALTER TABLE is fine as long
as
> > you don't wish to change a column into an identity column. I do not
intend
> > to spend money on this by calling the support line, since I know what
the
> > problem is and spending money on this seems absurd. There is no kb
article
> 1) If they can't fix that problem, you dont have to spend money, they'll
pay
> that back.
>
Well they were nice and are going to send me the patch. I was a bit
frustrated but if they send me the fix, my problems should be over :). But a
few nightly hours searching can drive you mad every now and then; :-D
> 2) What's your code? Do you use ADO or something like that?
>
Just plain simple Enterprise Manager. I tried to work around it with the web
data administrator but that didn't work either.
> I can tell you that *no* alter table /alter column statement supports that
> natively..
> --does *not* work
> ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
> Therefore, I think that the 'cursor error' is raised because some
> object/component that you use, did not test this possibility and now it
bugs
> out...
>
Yep that's what I figured out as well. Enterprise Manager generates some
extra statements to support that I've read somewhere.
> 3) Having the latest ODBC / oledb drivers might help.
> Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so
on...
>
I will check that...
> 4)
> this below works...
>
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Table1
> (
> nID int NOT NULL IDENTITY (1, 1),
> adfs char(10) NULL
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 ON
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
> SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> ALTER TABLE dbo.Table1 ADD CONSTRAINT
> PK_Table1 PRIMARY KEY CLUSTERED
> (
> nID
> ) ON [PRIMARY]
> GO
> COMMIT
>
I will try that as well. It's always usefull to have such workarounds.
Tnx a lot 4 your time.
yours sincerly
Michael

Saturday, February 11, 2012

"cursor" what is that

Can anyone tell me where i could learn about "cursors", what they're for and how to code it ?
Thanks a lot !Cursor is basically a loop through the data. Where you can stop and process each row in this loop separately. They are too slow and not recommended to use often.

Tuesday, March 6, 2012

"On Error Resume Next" in SQL Server

Friday, February 24, 2012

"Invalid Cursor State" - REPOST

Thursday, February 23, 2012

"Invalid Cursor State" - REPOST

"Invalid Cursor State" - REPOST

"Invalid Cursor Error"

"Invalid Cursor Error"

Sunday, February 19, 2012

"Invalid Cursor Error"

Saturday, February 11, 2012

"cursor" what is that

sql error

Blog Archive

About Me