Sql Server Alignment problem: sp3a

Showing posts with label sp3a. Show all posts

Thursday, March 22, 2012

ALTER TABLE question

Hi,
Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
defaults/observe any constraints defined on a table?
I use SQL Server 2000 SP3a.
--
Many thanks,
OskHi,
Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
defaults/observe any constraints defined on a table?
I want to change the nullability of some columns and add a
primary key constraint to a table. When I monitor what the
ALTER TABLE really does (using Profiler), I see strange
inserts and updates like "insert [dbo].[table] select *
from [dbo].[table]" or "update [dbo].[table] set [column] =[column]". What are they for, and will these inserts and
updates activate any triggers/defaults/constraints defined
for a table?
I use SQL Server 2000 SP3a.
--
Many thanks,
Osk|||Are you doing this in Enterprise Manager or Query Analyzer.
You really want to be using Query Analyzer to make these
kind of changes.
-Sue
On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
>defaults/observe any constraints defined on a table?
>I want to change the nullability of some columns and add a
>primary key constraint to a table. When I monitor what the
>ALTER TABLE really does (using Profiler), I see strange
>inserts and updates like "insert [dbo].[table] select *
>from [dbo].[table]" or "update [dbo].[table] set [column] =>[column]". What are they for, and will these inserts and
>updates activate any triggers/defaults/constraints defined
>for a table?
>I use SQL Server 2000 SP3a.|||I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
ALTER TABLE command as far as I know. But what about
triggers/constraint - will they bey fired/observed?
--
Thanks,
Osk
>--Original Message--
>Are you doing this in Enterprise Manager or Query Analyzer.
>You really want to be using Query Analyzer to make these
>kind of changes.
>-Sue
>On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
><anonymous@.discussions.microsoft.com> wrote:
>>Hi,
>>Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
>>defaults/observe any constraints defined on a table?
>>I want to change the nullability of some columns and add a
>>primary key constraint to a table. When I monitor what the
>>ALTER TABLE really does (using Profiler), I see strange
>>inserts and updates like "insert [dbo].[table] select *
>>from [dbo].[table]" or "update [dbo].[table] set [column] =>>[column]". What are they for, and will these inserts and
>>updates activate any triggers/defaults/constraints defined
>>for a table?
>>I use SQL Server 2000 SP3a.
>.
>|||On Thu, 20 Jan 2005 22:39:58 -0800, Osk wrote:
>I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
>ALTER TABLE command as far as I know. But what about
>triggers/constraint - will they bey fired/observed?
Hi Osk,
Easy to test, isn't it?
CREATE TABLE Test (Col1 int NULL,
Col2 int NULL)
go
CREATE TRIGGER TestTrig ON Test
AFTER INSERT, UPDATE, DELETE
AS
PRINT 'I''m fired!'
go
ALTER TABLE Test
ALTER COLUMN Col1 int NOT NULL
go
ALTER TABLE Test
ADD CONSTRAINT pk_Test PRIMARY KEY (Col1)
go
DROP TABLE Test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Tuesday, March 20, 2012

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

Monday, February 13, 2012

All users can start and stop SQL Server?

I work for a training center and we have the following scenario:
SQL Server 2000 SP3A is installed on 10 computers in our classroom, under
Windows 2000 SP4 Professional.
The students log on with their own user name.
They are member of the local Administrators group (we trust them on their
own machine).
They are also member of the sysadmin role on their own SQL Server.
We removed the BUILTIN/Administrators login on every SQL Server.
The students cannot access any database on the other machines, which is OK.
But by playing around, they discovered that they are still able to start and
stop any of the other servers.
Is this normal?
Did I overlook something?
What should I do to prevent this?
RikiSounds normal. Removing the role prevented them from accessing the data
within the SQL server. SQL runs as a service and any local administrator
can stop and start any service. Treat it as a learning opportunity.
Learning to be careful when you are a local administrator on a SQL server
host computer is a very important skill.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Riki" <riki@.bounce.com> wrote in message
news:ueJ$t9zGFHA.1528@.TK2MSFTNGP09.phx.gbl...
> I work for a training center and we have the following scenario:
> SQL Server 2000 SP3A is installed on 10 computers in our classroom, under
> Windows 2000 SP4 Professional.
> The students log on with their own user name.
> They are member of the local Administrators group (we trust them on their
> own machine).
> They are also member of the sysadmin role on their own SQL Server.
> We removed the BUILTIN/Administrators login on every SQL Server.
> The students cannot access any database on the other machines, which is
OK.
> But by playing around, they discovered that they are still able to start
and
> stop any of the other servers.
> Is this normal?
> Did I overlook something?
> What should I do to prevent this?
> Riki
>|||Thanks for your response, Geoff.
I wasn't aware that starting and stopping a SQL Server
doesn't have anything to do with SQL Server Permissions.
Riki
Geoff N. Hiten wrote:[vbcol=seagreen]
> Sounds normal. Removing the role prevented them from accessing the
> data within the SQL server. SQL runs as a service and any local
> administrator can stop and start any service. Treat it as a learning
> opportunity. Learning to be careful when you are a local
> administrator on a SQL server host computer is a very important skill.
>
> "Riki" <riki@.bounce.com> wrote in message
> news:ueJ$t9zGFHA.1528@.TK2MSFTNGP09.phx.gbl...
Riki|||Hi Geoff,
This doesn't seem quite right to me, but I might be missing something.
Riki's problem as I see it is that the local Admin on Machine B can stop and
start services on Machine A. But the local Admin is just that - local - and
so should not be able to affect any other machine.
So while a local Admin can start and stop the local MSSQLServer service
irrespective of SQL Server rights, they shouldn't be able to affect another
machine's services.
So, have I missed something?
Simon.
"Geoff N. Hiten" wrote:

> Sounds normal. Removing the role prevented them from accessing the data
> within the SQL server. SQL runs as a service and any local administrator
> can stop and start any service. Treat it as a learning opportunity.
> Learning to be careful when you are a local administrator on a SQL server
> host computer is a very important skill.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Riki" <riki@.bounce.com> wrote in message
> news:ueJ$t9zGFHA.1528@.TK2MSFTNGP09.phx.gbl...
> OK.
> and
>
>

Sunday, February 12, 2012

All Tasks->Restore Database....hangs forever

We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary to
two secondary servers. I performed a role change from the primary to the
secondary running script "sp_change_secondary_role" on secondary server.
Everything went fine. I later removed log shipping from all servers.
Now, I try to Restore a backup of the database on the ex-primary server by
right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted the
db, created an empty instance and tried to restore again. It hangs. I
restored using t-sql, but something is obviously wrong.
For what reason could sql exhibit this behavior?
Thank you for your help
JCOn Tue, 7 Jun 2005 06:47:04 -0700, John C <John
C@.discussions.microsoft.com> wrote:
>We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary to
>two secondary servers. I performed a role change from the primary to the
>secondary running script "sp_change_secondary_role" on secondary server.
>Everything went fine. I later removed log shipping from all servers.
>Now, I try to Restore a backup of the database on the ex-primary server by
>right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted the
>db, created an empty instance and tried to restore again. It hangs. I
>restored using t-sql, but something is obviously wrong.
>For what reason could sql exhibit this behavior?
>Thank you for your help
Sorry not a solution but just to confirm I have also seen this
behaviour of restore hanging enterprise manager (although these
servers were not log shipping) - using the SQL command equivalents in
Query Analyzer to restore provide a workaround.|||John,
Do you ever cleanup the backup entries in tables in MSDB? Your request is
looking at all the entries in MSDB about this database. Probably should not
have the results that you see though.
Chris Wood
"John C" <John C@.discussions.microsoft.com> wrote in message
news:38E1E36C-CF4F-494E-978C-05DFC0E2D72C@.microsoft.com...
> We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary
> to
> two secondary servers. I performed a role change from the primary to the
> secondary running script "sp_change_secondary_role" on secondary server.
> Everything went fine. I later removed log shipping from all servers.
> Now, I try to Restore a backup of the database on the ex-primary server by
> right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted
> the
> db, created an empty instance and tried to restore again. It hangs. I
> restored using t-sql, but something is obviously wrong.
> For what reason could sql exhibit this behavior?
> Thank you for your help
> JC|||Chris, this might be the problem. I just remembered that I had the same
problem when I tried to Delete the database by right-clicking. When I
selected "Delete backup history" the system hung. When I tried again and
unchecked this selection it did go hrough.
What tables do I have to clean though? Where in msdb is the backup history
stored?
Thank yu for your prompt reply
John C
"Chris Wood" wrote:
> John,
> Do you ever cleanup the backup entries in tables in MSDB? Your request is
> looking at all the entries in MSDB about this database. Probably should not
> have the results that you see though.
> Chris Wood
> "John C" <John C@.discussions.microsoft.com> wrote in message
> news:38E1E36C-CF4F-494E-978C-05DFC0E2D72C@.microsoft.com...
> > We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary
> > to
> > two secondary servers. I performed a role change from the primary to the
> > secondary running script "sp_change_secondary_role" on secondary server.
> > Everything went fine. I later removed log shipping from all servers.
> >
> > Now, I try to Restore a backup of the database on the ex-primary server by
> > right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted
> > the
> > db, created an empty instance and tried to restore again. It hangs. I
> > restored using t-sql, but something is obviously wrong.
> >
> > For what reason could sql exhibit this behavior?
> >
> > Thank you for your help
> > JC
>
>|||There are 4-6 tables that need to be cleaned up. Backupfile, backupmediaset,
backupmediafamily, backupset. Maybe a couple more, you will see when the
FKeys get in the way.
"John C" wrote:
> Chris, this might be the problem. I just remembered that I had the same
> problem when I tried to Delete the database by right-clicking. When I
> selected "Delete backup history" the system hung. When I tried again and
> unchecked this selection it did go hrough.
> What tables do I have to clean though? Where in msdb is the backup history
> stored?
> Thank yu for your prompt reply
> John C
> "Chris Wood" wrote:
> > John,
> >
> > Do you ever cleanup the backup entries in tables in MSDB? Your request is
> > looking at all the entries in MSDB about this database. Probably should not
> > have the results that you see though.
> >
> > Chris Wood
> >
> > "John C" <John C@.discussions.microsoft.com> wrote in message
> > news:38E1E36C-CF4F-494E-978C-05DFC0E2D72C@.microsoft.com...
> > > We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary
> > > to
> > > two secondary servers. I performed a role change from the primary to the
> > > secondary running script "sp_change_secondary_role" on secondary server.
> > > Everything went fine. I later removed log shipping from all servers.
> > >
> > > Now, I try to Restore a backup of the database on the ex-primary server by
> > > right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted
> > > the
> > > db, created an empty instance and tried to restore again. It hangs. I
> > > restored using t-sql, but something is obviously wrong.
> > >
> > > For what reason could sql exhibit this behavior?
> > >
> > > Thank you for your help
> > > JC
> >
> >
> >

Thursday, March 22, 2012

ALTER TABLE question

Tuesday, March 20, 2012

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

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

Monday, February 13, 2012

All users can start and stop SQL Server?

Sunday, February 12, 2012

All Tasks->Restore Database....hangs forever

All Tasks->Restore Database....hangs forever

All Tasks->Restore Database....hangs forever

Sql Server Alignment problem

Blog Archive

About Me