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...
quote:
> I am trying to make design changes to a SQL table i just created and I
keep
quote:
> 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
quote:|||The table has 5 columns:
> 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.
>
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...
quote:|||Thanks for the response. Using Query analyzer to make the table changes
> 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.
>
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 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 Ent
erprise Manager, I get the same "Invalid Cursor State" message. This happen
s when performing these operations on any table in any database on the serve
r. However, executing a sc
ript 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 o
f 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 cre
ated 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 ho
tfix. The next attempt to perform these operations was today and had the pr
oblems 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 g
ot 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 nee
ded. 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...
quote:
> 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.
quote:
> On the XP Pro machine, I have SQL Server 2000 Developer and DO NOT get any
of these problems.
quote:
> 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
quote:
> 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)
quote:
>
> 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.
quote:
> I also:
> 1) Reinstalled MDAC 2.8 on Server, attempted operation from Client 1 and
got error
quote:
> 2) Reinstalled MDAC 2.8 on Client 1, attempted operation from Client 1 and
got error
quote:
> 3) Reinstalled KB832483 hotfix on Client 1, attempted operation from
Client 1 and got error
quote:
> 4) Reinstalled KB832483 hotfix on Server, attempted operation from Client
1 and got error
quote:
>
> This is a development server, so I[m open to trying something radical if
needed. Any other suggestions?
quote:|||What is the level of SQL Server on the clients (they should be at the same
> John
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...
quote:
> 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.
quote:
> On the XP Pro machine, I have SQL Server 2000 Developer and DO NOT get any
of these problems.
quote:
> 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
quote:
> 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)
quote:
>
> 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.
quote:
> I also:
> 1) Reinstalled MDAC 2.8 on Server, attempted operation from Client 1 and
got error
quote:
> 2) Reinstalled MDAC 2.8 on Client 1, attempted operation from Client 1 and
got error
quote:
> 3) Reinstalled KB832483 hotfix on Client 1, attempted operation from
Client 1 and got error
quote:
> 4) Reinstalled KB832483 hotfix on Server, attempted operation from Client
1 and got error
quote:
>
> This is a development server, so I[m open to trying something radical if
needed. Any other suggestions?
quote:|||SP3 (8.00.859) is installed on the 2003 server, SP3 (8.00.760) on the SQL Se
> John
rver 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 operation
s 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 se
rver 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 determ
ine how the 2003 server got
8.00.859, so I suspect this is due to installing Reporting Services. I'm do
ubting 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/
No comments:
Post a Comment