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 do a select from sp_helpdbs output ?

Hi friends,
I want to select only filename column of sp_helpdb's output.But I don't know how should I do this? Other way is doing a direct select from sysfiles or sysdatabases but as you know microsoft doesn't recommand this way. Also I can select that filed in my application level but it is strange for me: Of course sql server should has a proper way to do a select from output curosr of this procedure,Doesn't it?
-Thanks in advanceTry this:

create table #temp
(
name sysname
,db_size nvarchar(13)
,owner sysname
,dbid smallint
,created nvarchar(11)
,status nvarchar(600)
,compatibility_level tinyint
)
go
insert into #temp
exec sp_helpdb

select * from #temp

drop table #temp

Bill|||Thanks for your solution,
But think about a highly busy environment with many concurent users.Should I create a temp table for each session? I can append the session_id or something like that to achieve unique temp table names but is this the best way in your opinion?
I am not satisfied,maybe there is a better solution...maybe...
-Regards|||You don't need to worry about SessionId's for the Temp table because the table will only be visible to the connection/user who created it.

Good Tip Cascred with the

insert into #temp
exec sp_helpdb

I've wondered myself how to capture these outputs before and you've just given me the answer.

I know MSoft don't recommend selecting directly from sysfiles and i'm not sure why - probably because they reserve the right to change it's structure in future releases which may mean that any code may stop working.

Having said that I would think that theres a great deal more chance that the SP_helpDB output is more likely to change than the filename column in sysobjects.

Personnaly I'd go with the select filename from sysobjects option and move on
but if you still don't like that maybe another option would be to capture the output of XP_CmdShell something like
xp_cmdshell 'dir c:\Program Files\Microsoft SQL Server\Mssql\Data\*.*'

Good Luck|||Thanks,
you don't know the path of SQL Server in each system,so you can't use xp_cmdshell always: You can set an environment variable in each system to point to SQL Server path.Even in this case maybe you have created the datafile in othe place and the scenario goes on...
Second: I don't want all of those files,I need the filename for a specific database name,and these are not equal or even similar always.
I am agree with you about changing in output of sp_helpdb versus structure of sysfiles.
-Regards