October 8, 2009 at 12:53 am
I was working on enabling and disabling a job. I was directly able to manipulate the values in sysjobs instead of doing it through sp_alterjob. How was that posssible? Though it was possible for sysjobs, it was not possible for other those were inside sys. schema. The following information might be helpful.:-D
The option ‘allow updates’ which shows up when sp_configure is run is the property which enables/disables the permit to update any system tables. It is set to 0 by default thereby disallowing the users to not update any system tables.
This can however be updated to 1 (followed by RECONFIGURE WITH OVERRIDE) but should strictly be avoided J
Even after ‘allow updates’ is set to 1, changes cannot be made directly but can be made only through stored procedures
Might find more info here:
http://msdn.microsoft.com/en-us/library/aa196704%28SQL.80%29.aspx%5B/color%5D
October 8, 2009 at 2:18 am
dreamthebest (10/8/2009)
I was working on enabling and disabling a job. I was directly able to manipulate the values in sysjobs instead of doing it through sp_alterjob. How was that posssible? Though it was possible for sysjobs, it was not possible for other those were inside sys. schema.
The msdb job/backup tables aren't system tables and they're not in the sys schema. They are normal user tables and can be updated like any other user table
select s.name, t.name, ObjectProperty(object_id,'IsUserTable') AS IsUserTable
from msdb.sys.tables t inner join msdb.sys.schemas s on t.schema_id = s.schema_id
order by t.name
Allow updates does not work on SQL 2005. Enable it, try and update a real system table (syscolpars for example) and see the errors that you get. The link you provided is for SQL 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2009 at 3:59 am
I think allow update work for SQL Server 2005 as well.
But still are you sure that they are not system tables as they get created automatically.
October 8, 2009 at 5:29 am
dreamthebest (10/8/2009)
I think allow update work for SQL Server 2005 as well.
No it doesn't. The system tables are hidden in SQL 2005 and higher and Allow Update does not allow you to update them.
If you don't believe me, look in Books Online.
allow updates Option
This option is still present in the sp_configure stored procedure, although its functionality is unavailable in SQL Server. The setting has no effect. Starting with SQL Server 2005, direct updates to the system tables are not supported.
But still are you sure that they are not system tables as they get created automatically.
Did you run the query I posted? If you do you'll see they are classified as user tables and they're in the dbo schema. Automatically created <> system table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2009 at 6:47 am
hey thanks. Yup I got it.
thanks for the information.
October 8, 2009 at 7:27 am
oops.. got doubt again.
If this is the case why this tables are defined as system tables for sql server 2000.:hehe:
http://msdn.microsoft.com/en-us/library/aa260604(SQL.80).aspx
dbobackupfile NULL
dbobackupfilegroup NULL
dbobackupmediafamil NULL
dbobackupmediaset NULL
dbobackupset NULL
dboDTA_input NULL
.
.
.
.
.
.
dbosyscachedcredentialsNULL
dbosyscategoriesNULL
dbosysdbmaintplan_databases0
dbosysdbmaintplan_history0
dbosysdbmaintplan_jobs0
dbosysdbmaintplans0
dbosysdownloadlistNULL
dbosysdtscategoriesNULL
dbosysdtslog90NULL
dbosysdtspackagefolders90NULL
dbosysdtspackagelogNULL
dbosysdtspackagesNULL
dbosysdtspackages90NULL
dbosysdtssteplogNULL
dbosysdtstasklogNULL
dbosysjobactivityNULL
dbosysjobhistory1
dbosysjobsNULL
dbosysjobschedulesNULL
dbosysjobserversNULL
dbosysjobstepsNULL
dbosysjobstepslogsNULL
.
.
.
.
Most of the values are null eventhough i have access to them.
October 8, 2009 at 7:50 am
dreamthebest (10/8/2009)
If this is the case why this tables are defined as system tables for sql server 2000.:hehe:
Change in architecture. It was a major change SQL 2000 - SQL 2005
Most of the values are null eventhough i have access to them.
What query did you run? The one I wrote is for SQL 2005 and wouldn't have run on 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply