Invalid cursor state?

I am trying to make design changes to a SQL table i just created and I keep
getting this error:
'Drug_Alcohol' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
It says in books online that this error can be caused if the sql server runs
out of resources while attempting to save. The database I created is new
and the file size is still only 4megs. I tried increasing the size, but I
still get the error.
Does anyone know what might be happening? The only way I can make changes
to a table now is to recreate a new table with the updated changes and
delete the old one. I am running SQL server 2000 with service pack 3a.Maybe you could show the current structure of the table, sample data, and
the ALTER TABLE statement.
Or, we could guess all day...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"REB" <russ@.bjorgaard.com> wrote in message
news:#VKtrpR5DHA.504@.TK2MSFTNGP11.phx.gbl...
> I am trying to make design changes to a SQL table i just created and I
keep
> getting this error:
> 'Drug_Alcohol' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
> It says in books online that this error can be caused if the sql server
runs
> out of resources while attempting to save. The database I created is new
> and the file size is still only 4megs. I tried increasing the size, but I
> still get the error.
> Does anyone know what might be happening? The only way I can make changes
> to a table now is to recreate a new table with the updated changes and
> delete the old one. I am running SQL server 2000 with service pack 3a.
>|||The table has 5 columns:
Drug_AlcoholID int 4
DriverID int 4 Allow Nulls
PullDate smalldatetime 4
Pulltype int 4 Allow Nulls
Results char 20 Allow Nulls
I just created the table and there is no data in it.
I get the error if I try to uncheck allow nulls, change the first field to
an identity field, delete a column, or try to add a new column.
I am using Enterprise Manager so I do not see the ALTER TABLE statement.|||Has your local Enterprise Manager (assuming client tools only) been updated
to SP3? Is it even SQL Server 2000? Make sure both client and server are
the same version. Is SQL Server in the correct compatibility mode? You
might have problems changing table properties like that through a 2000 GUI
(which can issue ALTER TABLE calls under the covers) if the underlying
database is actually in 6.5 mode, for example.
Open Query Analyzer, connect to the server, and run:
EXEC sp_dbcmptlevel 'database_name'
This should be in 70 or 80, or else you won't be able to issue many commands
resembling ALTER TABLE.
What operating system are you on locally? What type of authentication are
you using (SQL or Windows)? See http://support.microsoft.com/?id=243292
Also, if you are on a Win9x platform and are using Windows authentication,
you might consider using SQL authentication instead.
Was this database created by upsizing from Access?
Might also see http://support.microsoft.com/?id=821334
Also, most of these operations can and should be handled in Query Analyzer
(ALTER TABLE, EXEC sp_rename, etc). That way you can save your scripts,
check them into source safe, but more meaningful errors when they break,
etc.
Adding the IDENTITY property to an existing column is quite a bit of work...
the database has to make a duplicate table, copy all the rows, drop the old
table, and rename the new one, and keep the table off-limits to all other
processes throughout the task. I suggest if you want IDENTITY you decide so
up front, before the table has data in it...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"REB" <russ@.bjorgaard.com> wrote in message
news:#76622R5DHA.564@.TK2MSFTNGP10.phx.gbl...
> The table has 5 columns:
> Drug_AlcoholID int 4
> DriverID int 4 Allow Nulls
> PullDate smalldatetime 4
> Pulltype int 4 Allow Nulls
> Results char 20 Allow Nulls
> I just created the table and there is no data in it.
> I get the error if I try to uncheck allow nulls, change the first field to
> an identity field, delete a column, or try to add a new column.
> I am using Enterprise Manager so I do not see the ALTER TABLE statement.
>|||Thanks for the response. Using Query analyzer to make the table changes
works just fine (I just had to learn the syntax). Enterprise Manager was
installed with my SQL Server 2000 Standard edition on my test server, which
is running windows server 2003. I have SP3a installed.
Is there a seperate download to update the Enterprise Manager? When I look
under help it says my version of Enterprise Manager is 8.0|||I have been getting the same problem, too. Our scenario
Server
Windows Server 200
SQL 2000 SP3
MDAC 2.8 with KB832483 hotfix installe
Client 1
XP Pro SP1
MDAC 2.8 with KB832483 hotfix installe
Client 2
XP Home SP1
MDAC 2.8 (no hotfix installed
(Also tried Enterprise Manager from server console
When trying to add column, delete column, or uncheck allow nulls through Enterprise Manager, I get the same "Invalid Cursor State" message. This happens when performing these operations on any table in any database on the server. However, executing a script through Query Analyzer to perform these functions causes not problems
On the XP Pro machine, I have SQL Server 2000 Developer and DO NOT get any of these problems
Regarding the 2003 server, I have verified the following
Compatibility level of all databases is 8
Connecting to server using SQL or Windows Authentication makes no differenc
Connecting using sa or a variety of db admin users makes no differenc
No databases were upsized from Access (in fact, all DBs and objects were created within the last two months from either EM or QA
My last successful attempt to perform the above operations was Jan 27. The only configuation change to the server was Jan 29 to install the KB832483 hotfix. The next attempt to perform these operations was today and had the problems indicated above
I also
1) Reinstalled MDAC 2.8 on Server, attempted operation from Client 1 and got erro
2) Reinstalled MDAC 2.8 on Client 1, attempted operation from Client 1 and got erro
3) Reinstalled KB832483 hotfix on Client 1, attempted operation from Client 1 and got erro
4) Reinstalled KB832483 hotfix on Server, attempted operation from Client 1 and got erro
This is a development server, so I[m open to trying something radical if needed. Any other suggestions
John|||What Version of SQL Server do you have? 8.00.760(SP3)?
"JSD" <anonymous@.discussions.microsoft.com> wrote in message
news:815C57B0-77A9-4102-A772-D3849A599DD0@.microsoft.com...
> I have been getting the same problem, too. Our scenario:
> Server:
> Windows Server 2003
> SQL 2000 SP3a
> MDAC 2.8 with KB832483 hotfix installed
> Client 1:
> XP Pro SP1a
> MDAC 2.8 with KB832483 hotfix installed
> Client 2:
> XP Home SP1a
> MDAC 2.8 (no hotfix installed)
> (Also tried Enterprise Manager from server console)
>
> When trying to add column, delete column, or uncheck allow nulls through
Enterprise Manager, I get the same "Invalid Cursor State" message. This
happens when performing these operations on any table in any database on the
server. However, executing a script through Query Analyzer to perform these
functions causes not problems.
> On the XP Pro machine, I have SQL Server 2000 Developer and DO NOT get any
of these problems.
> Regarding the 2003 server, I have verified the following:
> Compatibility level of all databases is 80
> Connecting to server using SQL or Windows Authentication makes no
difference
> Connecting using sa or a variety of db admin users makes no difference
> No databases were upsized from Access (in fact, all DBs and objects were
created within the last two months from either EM or QA)
>
> My last successful attempt to perform the above operations was Jan 27.
The only configuation change to the server was Jan 29 to install the
KB832483 hotfix. The next attempt to perform these operations was today and
had the problems indicated above.
> I also:
> 1) Reinstalled MDAC 2.8 on Server, attempted operation from Client 1 and
got error
> 2) Reinstalled MDAC 2.8 on Client 1, attempted operation from Client 1 and
got error
> 3) Reinstalled KB832483 hotfix on Client 1, attempted operation from
Client 1 and got error
> 4) Reinstalled KB832483 hotfix on Server, attempted operation from Client
1 and got error
>
> This is a development server, so I[m open to trying something radical if
needed. Any other suggestions?
> John|||What is the level of SQL Server on the clients (they should be at the same
SP level as the server)? Why have you not updated client 2 with the MDAC
hotfix?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"JSD" <anonymous@.discussions.microsoft.com> wrote in message
news:815C57B0-77A9-4102-A772-D3849A599DD0@.microsoft.com...
> I have been getting the same problem, too. Our scenario:
> Server:
> Windows Server 2003
> SQL 2000 SP3a
> MDAC 2.8 with KB832483 hotfix installed
> Client 1:
> XP Pro SP1a
> MDAC 2.8 with KB832483 hotfix installed
> Client 2:
> XP Home SP1a
> MDAC 2.8 (no hotfix installed)
> (Also tried Enterprise Manager from server console)
>
> When trying to add column, delete column, or uncheck allow nulls through
Enterprise Manager, I get the same "Invalid Cursor State" message. This
happens when performing these operations on any table in any database on the
server. However, executing a script through Query Analyzer to perform these
functions causes not problems.
> On the XP Pro machine, I have SQL Server 2000 Developer and DO NOT get any
of these problems.
> Regarding the 2003 server, I have verified the following:
> Compatibility level of all databases is 80
> Connecting to server using SQL or Windows Authentication makes no
difference
> Connecting using sa or a variety of db admin users makes no difference
> No databases were upsized from Access (in fact, all DBs and objects were
created within the last two months from either EM or QA)
>
> My last successful attempt to perform the above operations was Jan 27.
The only configuation change to the server was Jan 29 to install the
KB832483 hotfix. The next attempt to perform these operations was today and
had the problems indicated above.
> I also:
> 1) Reinstalled MDAC 2.8 on Server, attempted operation from Client 1 and
got error
> 2) Reinstalled MDAC 2.8 on Client 1, attempted operation from Client 1 and
got error
> 3) Reinstalled KB832483 hotfix on Client 1, attempted operation from
Client 1 and got error
> 4) Reinstalled KB832483 hotfix on Server, attempted operation from Client
1 and got error
>
> This is a development server, so I[m open to trying something radical if
needed. Any other suggestions?
> John|||SP3 (8.00.859) is installed on the 2003 server, SP3 (8.00.760) on the SQL Server Developer on Client 1. Both Client 1 and Client 2 have SP3a applied to the client tools, also
Client 2 belongs to a user who does not regularly keep up with MDAC updates; therefore, the hotfix was not installed. I wanted to attempt the operations on Client 2 to help rule out wheter the hotfix may have caused the problem on Client 1
I forgot to mention that we also installed Reporting Services on the 2003 server on the 29th. I e-mailed this question to MS and they didn't think that installation caused this problem. I can't find a cross-reference to determine how the 2003 server got 8.00.859, so I suspect this is due to installing Reporting Services. I'm doubting whether uninstalling Reporting Services would get that server back to 8.00.760
Any ideas?|||>> I can't find a cross-reference to determine how the 2003 server got
8.00.859, so I suspect this is due to installing Reporting Services.
No, this has nothing to do with RS. 859 is the most recent *publicly*
available hotfix that I'm aware of. See
http://support.microsoft.com/?kbid=821334
You can trace specific version #s here:
http://www.aspfaq.com/2160
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks for the info. I'll bookmark that page at aspfaq.com for future reference
Any other suggestions on the source of the "invalid cursor state" error?|||No, my post earlier summarized all of the reasons I'm aware of.
How about, instead of using Enterprise Manager to make DDL changes, you use
Query Analyzer?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"JSD" <anonymous@.discussions.microsoft.com> wrote in message
news:78B9B85C-061F-40C4-96D1-77BCEB249BC0@.microsoft.com...
> Thanks for the info. I'll bookmark that page at aspfaq.com for future
reference.
> Any other suggestions on the source of the "invalid cursor state" error?|||To me, Query Analyzer is an acceptable alternative. The use of this server will involve some users who need to learn the fundamentals of SQL Server. While doing DDL changes in Query Analyzer is no problem for me, the EM GUI sure would make their learning easier
I think we'll end up creating a support incident for this. Any other resources you would suggest I try before taking the plunge? I spent quite a while researching this on the Web myself and this newsgroup was the only place I found any help
Aaron, thanks again for all your assistance!|||I spoke with MS support over the last couple of days. They gave me instructions on how to get hotfix 876. It is not available for public release yet, so you'll need to call them and ask for a "grace incident" so they don't charge you for the support
Anyway, they'll send a link and a password to get your own copy of the fix
I installed the hotfix and it cleared up all the problems that I reported
Good luck!|||Thanks JSD, made a note in http://www.aspfaq.com/2515
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"JSD" <anonymous@.discussions.microsoft.com> wrote in message
news:72E54CEF-E2F2-40F5-9D96-72BF84A04191@.microsoft.com...
> I spoke with MS support over the last couple of days. They gave me
instructions on how to get hotfix 876. It is not available for public
release yet, so you'll need to call them and ask for a "grace incident" so
they don't charge you for the support.
> Anyway, they'll send a link and a password to get your own copy of the
fix.
> I installed the hotfix and it cleared up all the problems that I reported.
> Good luck!|||I am experiencing the same behavior. Any idea when a hotfix will be
available for this issue? If not, can someone please point me toward the
telephone number I need to use for free information on where to get the
hotfix? It is really aggrevating not being able to modify any tables ...
Thank you,
Michael Carr
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:OG4cuVp7DHA.2168@.TK2MSFTNGP12.phx.gbl...
> Thanks JSD, made a note in http://www.aspfaq.com/2515|||You can ask for fix 878.
http://support.microsoft.com/?kbid=831950
http://support.microsoft.com/?scid=fh;en-us;Prodoffer41a
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Michael Carr" <mcarr@.umich.edu> wrote in message
news:OtHSTcC$DHA.392@.TK2MSFTNGP12.phx.gbl...
> I am experiencing the same behavior. Any idea when a hotfix will be
> available for this issue? If not, can someone please point me toward the
> telephone number I need to use for free information on where to get the
> hotfix? It is really aggrevating not being able to modify any tables ...
> Thank you,
> Michael Carr
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:OG4cuVp7DHA.2168@.TK2MSFTNGP12.phx.gbl...
> > Thanks JSD, made a note in http://www.aspfaq.com/2515
>|||Hey thanks, that fixed it right up!
Michael Carr
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:#8zODhH$DHA.2592@.TK2MSFTNGP10.phx.gbl...
> You can ask for fix 878.
> http://support.microsoft.com/?kbid=831950
> http://support.microsoft.com/?scid=fh;en-us;Prodoffer41a