Alter table does not work on SQL Server 2000 with SP3a and Hotfix KB810185

Greetings !
I am having problem with ALTER TABLE on any table when I install Hotfix
(KB810185-8.00.0859-ENU) required for SQL Server Reporting services.
I have installed SQL Server 2000 Enterprise Edition with SP3a on Windows
2003 Server (Standard Edition)
Hardware characteristics: Intel Pentum IV 2.40 GHz, 2 GB RAM, 5 disks etc.
We have tested alter table on two different computers and problem is the
same.
We have tried in Enterprise Manager and Query Analyser.
In Query Analyser sometimes is possible, but not all the time.
Without Hotfix (KB810185) everthing works fine.
Please help.
Is it possible to uninstall this hotfix?
It is not visible in ADD/REMOVE Programs ?
Regards Gjuro.What is the exact ALTER TABLE statement that is failing, and how do you
know it fails? Do you get an error, does it simply not change the
table, or something else?
SK
Dariviko wrote:
>Greetings !
>I am having problem with ALTER TABLE on any table when I install Hotfix
>(KB810185-8.00.0859-ENU) required for SQL Server Reporting services.
>I have installed SQL Server 2000 Enterprise Edition with SP3a on Windows
>2003 Server (Standard Edition)
>Hardware characteristics: Intel Pentum IV 2.40 GHz, 2 GB RAM, 5 disks etc.
>We have tested alter table on two different computers and problem is the
>same.
>We have tried in Enterprise Manager and Query Analyser.
>In Query Analyser sometimes is possible, but not all the time.
>Without Hotfix (KB810185) everthing works fine.
>
>Please help.
>Is it possible to uninstall this hotfix?
> It is not visible in ADD/REMOVE Programs ?
>Regards Gjuro.
>
>|||---
Test table creation: (Script from Enterprise Manager - New table)
----
/*
17. veljaca 2004 16:49:35
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
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.Table_TEST
(
id int NOT NULL,
col1 char(10) NULL,
col2 char(10) NULL
) ON [PRIMARY]
GO
COMMIT
--
Table is successfuly created.
When I move ALLOW NULLS from col2 in Enterprise Manager, I receive error in
Enterprise Manager.
/*
17. veljaca 2004 16:51:40
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
---
ALTER TABLE in Enterprise Manager
---
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_TABLE_TEST
(
id int NOT NULL,
col1 char(10) NULL,
col2 char(10) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.TABLE_TEST)
EXEC('INSERT INTO dbo.Tmp_TABLE_TEST (id, col1, col2)
SELECT id, col1, col2 FROM dbo.TABLE_TEST TABLOCKX')
GO
DROP TABLE dbo.TABLE_TEST
GO
EXECUTE sp_rename N'dbo.Tmp_TABLE_TEST', N'TABLE_TEST', 'OBJECT'
GO
COMMIT
--
This is an error I receive:
--
/*
17. veljaca 2004 16:52:07
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
'TABLE_TEST' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
Table TABLE_TEST is empty. New table
Windows 2003 Server has all updates.
I tried to remove hotfix KB810185 as it is described on Microsoft site, but
it does not work.
Regards
Gjuro MCDBA
"Steve Kass" <skass@.drew.edu> wrote in message
news:uS5Yl8V9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> What is the exact ALTER TABLE statement that is failing, and how do you
> know it fails? Do you get an error, does it simply not change the
> table, or something else?
> SK
> Dariviko wrote:
> >Greetings !
> >
> >I am having problem with ALTER TABLE on any table when I install Hotfix
> >(KB810185-8.00.0859-ENU) required for SQL Server Reporting services.
> >
> >I have installed SQL Server 2000 Enterprise Edition with SP3a on Windows
> >2003 Server (Standard Edition)
> >Hardware characteristics: Intel Pentum IV 2.40 GHz, 2 GB RAM, 5 disks
etc.
> >
> >We have tested alter table on two different computers and problem is the
> >same.
> >We have tried in Enterprise Manager and Query Analyser.
> >In Query Analyser sometimes is possible, but not all the time.
> >Without Hotfix (KB810185) everthing works fine.
> >
> >
> >Please help.
> >
> >Is it possible to uninstall this hotfix?
> > It is not visible in ADD/REMOVE Programs ?
> >
> >Regards Gjuro.
> >
> >
> >
> >
>|||I am having the same exact problem except when using Enterprise Manager.
I am trying to modify an existing table, and when I click the Save icon, I
get a message about Invalid Cursor State. This is only AFTER I applied
Hotfix 821334 in order to use SQL Reporting Services. As in Dariviko's case,
I am also running Server 2003 with SQL Server 2000 and SP3a. I installed
Reporting Services yesterday and the 821334 hotfix as a part of that
installation.
Does anybody else get this error?
Thanks,
Michael Carr
"Dariviko" <dariviko@.hotmail.com> wrote in message
news:c0te4m$pq4$1@.sunce.iskon.hr...
> --
> This is an error I receive:
> --
> /*
> 17. veljaca 2004 16:52:07
> User:
> Server: MSSERVER
> Database: MFIN_TEST
> Application: MS SQLEM - Data Tools
> */
> 'TABLE_TEST' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
>
> Table TABLE_TEST is empty. New table
> Windows 2003 Server has all updates.
> I tried to remove hotfix KB810185 as it is described on Microsoft site,
but
> it does not work.
> Regards
> Gjuro MCDBA
>
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uS5Yl8V9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> > What is the exact ALTER TABLE statement that is failing, and how do you
> > know it fails? Do you get an error, does it simply not change the
> > table, or something else?
> >
> > SK
> >
> > Dariviko wrote:
> >
> > >Greetings !
> > >
> > >I am having problem with ALTER TABLE on any table when I install Hotfix
> > >(KB810185-8.00.0859-ENU) required for SQL Server Reporting services.
> > >
> > >I have installed SQL Server 2000 Enterprise Edition with SP3a on
Windows
> > >2003 Server (Standard Edition)
> > >Hardware characteristics: Intel Pentum IV 2.40 GHz, 2 GB RAM, 5 disks
> etc.
> > >
> > >We have tested alter table on two different computers and problem is
the
> > >same.
> > >We have tried in Enterprise Manager and Query Analyser.
> > >In Query Analyser sometimes is possible, but not all the time.
> > >Without Hotfix (KB810185) everthing works fine.
> > >
> > >
> > >Please help.
> > >
> > >Is it possible to uninstall this hotfix?
> > > It is not visible in ADD/REMOVE Programs ?
> > >
> > >Regards Gjuro.
> > >
> > >
> > >
> > >
> >
>|||I have them same problem. See http://www.aspfaq.com/show.asp?id=2515. Now we just need a Microsoft tech to tell us where we can download hotfix 876 or greater.|||You call Microsoft Product Support Services, just like the KB articles say.
(You can find individual hotfixes at http://www.aspfaq.com/2160)
You won't be able to find hotfixes available at tucows or download.com.
They keep them from public consumption for a reason...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David Hubbell" <David.Hubbell@.sierras.com> wrote in message
news:D52EBDF9-A0DC-4723-A123-D1FE4B47747D@.microsoft.com...
>I have them same problem. See http://www.aspfaq.com/show.asp?id=2515. Now
>we just need a Microsoft tech to tell us where we can download hotfix 876
>or greater.

Alter table does not work on SQL Server 2000 with SP3a and Hotfix KB810185

Greetings !
I am having problem with ALTER TABLE on any table when I install Hotfix
(KB810185-8.00.0859-ENU) required for SQL Server Reporting services.
I have installed SQL Server 2000 Enterprise Edition with SP3a on Windows
2003 Server (Standard Edition)
Hardware characteristics: Intel Pentum IV 2.40 GHz, 2 GB RAM, 5 disks etc.
We have tested alter table on two different computers and problem is the
same.
We have tried in Enterprise Manager and Query Analyser.
In Query Analyser sometimes is possible, but not all the time.
Without Hotfix (KB810185) everthing works fine.
Please help.
Is it possible to uninstall this hotfix?
It is not visible in ADD/REMOVE Programs ?
Regards Gjuro.---
Test table creation: (Script from Enterprise Manager - New table)
----
/*
17. veljaca 2004 16:49:35
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
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.Table_TEST
(
id int NOT NULL,
col1 char(10) NULL,
col2 char(10) NULL
) ON [PRIMARY]
GO
COMMIT
Table is successfuly created.
When I move ALLOW NULLS from col2 in Enterprise Manager, I receive error in
Enterprise Manager.
/*
17. veljaca 2004 16:51:40
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
---
ALTER TABLE in Enterprise Manager
---
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_TABLE_TEST
(
id int NOT NULL,
col1 char(10) NULL,
col2 char(10) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.TABLE_TEST)
EXEC('INSERT INTO dbo.Tmp_TABLE_TEST (id, col1, col2)
SELECT id, col1, col2 FROM dbo.TABLE_TEST TABLOCKX')
GO
DROP TABLE dbo.TABLE_TEST
GO
EXECUTE sp_rename N'dbo.Tmp_TABLE_TEST', N'TABLE_TEST', 'OBJECT'
GO
COMMIT
--
This is an error I receive:
--
/*
17. veljaca 2004 16:52:07
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
'TABLE_TEST' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
Table TABLE_TEST is empty. New table
Windows 2003 Server has all updates.
I tried to remove hotfix KB810185 as it is described on Microsoft site, but
it does not work.
Regards
Gjuro MCDBA
"Steve Kass" <skass@.drew.edu> wrote in message
news:uS5Yl8V9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> What is the exact ALTER TABLE statement that is failing, and how do you
> know it fails? Do you get an error, does it simply not change the
> table, or something else?
> SK
> Dariviko wrote:
>
etc.
>|||I am having the same exact problem except when using Enterprise Manager.
I am trying to modify an existing table, and when I click the Save icon, I
get a message about Invalid Cursor State. This is only AFTER I applied
Hotfix 821334 in order to use SQL Reporting Services. As in Dariviko's case,
I am also running Server 2003 with SQL Server 2000 and SP3a. I installed
Reporting Services yesterday and the 821334 hotfix as a part of that
installation.
Does anybody else get this error?
Thanks,
Michael Carr
"Dariviko" <dariviko@.hotmail.com> wrote in message
news:c0te4m$pq4$1@.sunce.iskon.hr...
> --
> This is an error I receive:
> --
> /*
> 17. veljaca 2004 16:52:07
> User:
> Server: MSSERVER
> Database: MFIN_TEST
> Application: MS SQLEM - Data Tools
> */
> 'TABLE_TEST' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor stat
e
>
> Table TABLE_TEST is empty. New table
> Windows 2003 Server has all updates.
> I tried to remove hotfix KB810185 as it is described on Microsoft site,
but
> it does not work.
> Regards
> Gjuro MCDBA
>
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uS5Yl8V9DHA.2472@.TK2MSFTNGP10.phx.gbl...
Windows
> etc.
the
>|||I have them same problem. See http://www.aspfaq.com/show.asp?id=2515. Now we
just need a Microsoft tech to tell us where we can download hotfix 876 or g
reater.|||You call Microsoft Product Support Services, just like the KB articles say.
(You can find individual hotfixes at http://www.aspfaq.com/2160)
You won't be able to find hotfixes available at tucows or download.com.
They keep them from public consumption for a reason...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David Hubbell" <David.Hubbell@.sierras.com> wrote in message
news:D52EBDF9-A0DC-4723-A123-D1FE4B47747D@.microsoft.com...
>I have them same problem. See http://www.aspfaq.com/show.asp?id=2515. Now
>we just need a Microsoft tech to tell us where we can download hotfix 876
>or greater.sql

All Required Data Is Not Immediately Available - What to do?

What are some acceptable ways to deal with "required" data that is
unavailable at the time a row is added to a table?
Example: Lab tests are requested for a patient. Some results come in right
away and others come days later. All are eventually required in order for
the patient's lab records to be completed. It is unacceptable to the
customer to wait until ALL the values are available; they want to enter the
values AS they become available.
Obviously we cannot place a "NOT NULL" constraint on the columns in order to
allow for required data coming in late. I'd prefer to avoid having
application-level logic take care of this; but I currently don't see a way
around that.
Suggestions?
Thanks!"Fred Mertz" <A@.B.COM> wrote in message
news:%23DOJXeumGHA.856@.TK2MSFTNGP03.phx.gbl...
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter
> the values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
> to allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!
>
Like this for example:
CREATE TABLE Patients (PatientID INT NOT NULL PRIMARY KEY, PatientLastName
VARCHAR(35) NOT NULL /* ... etc */);
CREATE TABLE PatientTestResults (PatientID INT NOT NULL PRIMARY KEY
REFERENCES Patients (PatientID), Result VARCHAR(20) NOT NULL);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||So it sounds as if you want to enforce data validation before a status
can be changed or checked. If the process of changing status is manual
(i.e, a technician clicks a button that "signs off" on the data), you
could use a trigger to verify that data is complete before allowing
that status to be changed.
If the process is automatic (you just want to check if the status is
complete), you could run a check process in your stored procedure to
look for NULL tuples and report that status is Incomplete.
Tossing some ideas around.
Stu
Fred Mertz wrote:
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter th
e
> values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
to
> allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!|||RE:
<< Tossing some ideas around >>
Just what I was looking for. Your ideas are helpful.|||Just a guess here -REMOVE the NOT NULL constraint?
CREATE a 'holding' table that accepts the incomplete data, and move it to
the 'real' table when complete.
Alternatively, set a default value. If char/varchar datatype default to
'N/A' or whatever makes sense. If numeric datatype, and if zero has
significance, then default to a predetermined 'magic number' that signals
the data is still missing, e.g., 999.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Fred Mertz" <A@.B.COM> wrote in message
news:%23DOJXeumGHA.856@.TK2MSFTNGP03.phx.gbl...
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter
> the values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
> to allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!
>