How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
Sren
Hi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-28ac4e595d75.htm
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>
|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
Hope this helps.
Dan Guzman
SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>
|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
HTH
Kalen Delaney, SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>

How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
SørenHi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-28ac4e595d75.htm
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
--
HTH
Kalen Delaney, SQL Server MVP
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>sql

How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
SrenHi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-
28ac4e595d75.htm
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
Hope this helps.
Dan Guzman
SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
HTH
Kalen Delaney, SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>

How to edit this script to make it work right?

After run this command:
select 'sp_spaceused ' + name + 'go' from sysobjects where
type = 'U' order by name
I got results like:
sp_spaceused PSPRCSRQSTFILEgo
sp_spaceused PSPRCSRQSTMETAgo
sp_spaceused PSPRCSRQSTSTRNGgo
How to edit the script to seperate go at the line end and
make it like:
sp_spaceused PSPRCSRQSTFILE
go
sp_spaceused PSPRCSRQSTMETA
go
sp_spaceused PSPRCSRQSTSTRNG
go
Lookup REPLACE() function in SQL Server Books Online.
Anith
|||Instead you can use :
SELECT 'EXEC sp_spaceused ' + name + CHAR(13) + 'GO'
FROM sysobjects
WHERE type = 'U'
ORDER BY name ;
Anith

How to edit this script to make it work right?

After run this command:
select 'sp_spaceused ' + name + 'go' from sysobjects where
type = 'U' order by name
I got results like:
sp_spaceused PSPRCSRQSTFILEgo
sp_spaceused PSPRCSRQSTMETAgo
sp_spaceused PSPRCSRQSTSTRNGgo
How to edit the script to seperate go at the line end and
make it like:
sp_spaceused PSPRCSRQSTFILE
go
sp_spaceused PSPRCSRQSTMETA
go
sp_spaceused PSPRCSRQSTSTRNG
goLookup REPLACE() function in SQL Server Books Online.
--
Anith|||Instead you can use :
SELECT 'EXEC sp_spaceused ' + name + CHAR(13) + 'GO'
FROM sysobjects
WHERE type = 'U'
ORDER BY name ;
--
Anith

how to Drop an orphan view

Hi...
I have this in SQL Server 2000:
select * from sysobjects where name = 'xView' -- returns a row
-- but if I exec this script:
select * from xView
-- return to me
Mens. 208, Levl 16...
Object 'xView' is not valid.
The problem is I need to drop the login xLogin who is the xView owner.
Hw i do? why occurs this....thanks in advance.
--
ChevyHi Chevy,
Does
select * from xLogin.xView
works?
If that works then you can drop the view or change its ownership using
sp_changeobjectowner. Then drop the login.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Chevy" wrote:
> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy|||Chevy,
There probably is an object named 'xView', but it is not a view. If you try
the following, you will get the same error.
use master
select * from sp_who
If you query the system table you should do:
select * from sysobjects where name = 'xView' and type = 'V'
Or use the INFORMATION_SCHEMA views.
RLF
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:B6935BD1-F6A2-4980-9521-A9E064CAABFA@.microsoft.com...
> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy|||Yes Ben, you are rigth...I was really blind...thanks
___________
Chevy
"Ben Nevarez" wrote:
> Hi Chevy,
> Does
> select * from xLogin.xView
> works?
> If that works then you can drop the view or change its ownership using
> sp_changeobjectowner. Then drop the login.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Chevy" wrote:
> > Hi...
> >
> > I have this in SQL Server 2000:
> >
> > select * from sysobjects where name = 'xView' -- returns a row
> >
> > -- but if I exec this script:
> >
> > select * from xView
> >
> > -- return to me
> >
> > Mens. 208, Levl 16...
> > Object 'xView' is not valid.
> >
> > The problem is I need to drop the login xLogin who is the xView owner.
> >
> > Hw i do? why occurs this....thanks in advance.
> >
> > --
> > Chevy

how to Drop an orphan view

Hi...
I have this in SQL Server 2000:
select * from sysobjects where name = 'xView' -- returns a row
-- but if I exec this script:
select * from xView
-- return to me
Mens. 208, Levl 16...
Object 'xView' is not valid.
The problem is I need to drop the login xLogin who is the xView owner.
Hw i do? why occurs this....thanks in advance.
Chevy
Hi Chevy,
Does
select * from xLogin.xView
works?
If that works then you can drop the view or change its ownership using
sp_changeobjectowner. Then drop the login.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Chevy" wrote:

> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy
|||Chevy,
There probably is an object named 'xView', but it is not a view. If you try
the following, you will get the same error.
use master
select * from sp_who
If you query the system table you should do:
select * from sysobjects where name = 'xView' and type = 'V'
Or use the INFORMATION_SCHEMA views.
RLF
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:B6935BD1-F6A2-4980-9521-A9E064CAABFA@.microsoft.com...
> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy
|||Yes Ben, you are rigth...I was really blind...thanks
___________
Chevy
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Chevy,
> Does
> select * from xLogin.xView
> works?
> If that works then you can drop the view or change its ownership using
> sp_changeobjectowner. Then drop the login.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Chevy" wrote:

how to Drop an orphan view

Hi...
I have this in SQL Server 2000:
select * from sysobjects where name = 'xView' -- returns a row
-- but if I exec this script:
select * from xView
-- return to me
Mens. 208, Levl 16...
Object 'xView' is not valid.
The problem is I need to drop the login xLogin who is the xView owner.
Hw i do? why occurs this....thanks in advance.
ChevyHi Chevy,
Does
select * from xLogin.xView
works?
If that works then you can drop the view or change its ownership using
sp_changeobjectowner. Then drop the login.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Chevy" wrote:

> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy|||Chevy,
There probably is an object named 'xView', but it is not a view. If you try
the following, you will get the same error.
use master
select * from sp_who
If you query the system table you should do:
select * from sysobjects where name = 'xView' and type = 'V'
Or use the INFORMATION_SCHEMA views.
RLF
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:B6935BD1-F6A2-4980-9521-A9E064CAABFA@.microsoft.com...
> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy|||Yes Ben, you are rigth...I was really blind...thanks
___________
Chevy
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Chevy,
> Does
> select * from xLogin.xView
> works?
> If that works then you can drop the view or change its ownership using
> sp_changeobjectowner. Then drop the login.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Chevy" wrote:
>