|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Database Id prb !Hi,
I was profiling the Audit Schema Object Access Event in SQL Server 2005, and when I executed the system stored procedure "sys.xp_msver" I found that the database id of the this procedure is shown as "32767" which doesn't exists in the sys.databases view ? Any idea to what database it belongs to ? Does the above belong to the "mssqlsystemresource" or "distmdl" databases ? I also wanted information as to how do I query the system tables, it gives me an error saying the object doesnt exists Experts pls help TIA Thanks P [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
any database. - Breaking changes to database engine features in SQL Server 2005 http://msdn2.microsoft.com/en-us/library/ms143179.aspx [2] To query a system table, you will need to prefix the table with the appropriate database that you are going to query. E.g., SELECT * FROM msdb..backupfile - Querying the SQL Server System Catalog http://msdn2.microsoft.com/en-us/library/ms189082.aspx - Querying the SQL Server System Catalog FAQ http://msdn2.microsoft.com/en-us/library/ms345522.aspx -- Show quoteMartin Poon Microsoft MVP - SQL Server -------------------------------------------------- - SQL Server 2005 books online http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx - SQL Server 2000 books online http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx ================================================== "Prasad" <ekke_nik***@yahoo.co.uk> ¦b¶l¥ó news:e1BoWNa1GHA.3656@TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g... > Hi, > I was profiling the Audit Schema Object Access Event in SQL Server 2005, > and when I executed the system stored procedure "sys.xp_msver" I found that > the database id of the this procedure is shown as "32767" which doesn't > exists in the sys.databases view ? Any idea to what database it belongs to ? > Does the above belong to the "mssqlsystemresource" or "distmdl" > databases ? > I also wanted information as to how do I query the system tables, it > gives me an error saying the object doesnt exists > > Experts pls help > > TIA > > Thanks > P > > Hi,
Thnx for your information, but I have a question, this must be a naive for you. But if I query from a database "db" context shouldnt the profiler show that the database name is "db" and database id is the id the database "db" ? Same thing for the tables also ? The sys.all_objects lists that these are system tables and system stored procedures but then why it is not showing that it comes from this database in the profiler? I want to say to the profiler that I want to monitor all the "Audit Schema Object Access Event" in this particular database But then since these SP's and tables have the database id different then what is configured it is ignored even when these SP's are fired in the database context of what is configured Any suggestions ? Thanks P Show quote "Martin Poon [MVP]" <martinpoon@graduate(.)hku(.)hk> wrote in message news:u40Vtxa1GHA.4448@TK2MSFTNGP04.phx.gbl... > [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to > any database. > > - Breaking changes to database engine features in SQL Server 2005 > http://msdn2.microsoft.com/en-us/library/ms143179.aspx > > [2] To query a system table, you will need to prefix the table with the > appropriate database that you are going to query. E.g., > SELECT * FROM msdb..backupfile > > - Querying the SQL Server System Catalog > http://msdn2.microsoft.com/en-us/library/ms189082.aspx > - Querying the SQL Server System Catalog FAQ > http://msdn2.microsoft.com/en-us/library/ms345522.aspx > > -- > Martin Poon > Microsoft MVP - SQL Server > -------------------------------------------------- > - SQL Server 2005 books online > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > - SQL Server 2000 books online > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > ================================================== > "Prasad" <ekke_nik***@yahoo.co.uk> ¦b¶l¥ó > news:e1BoWNa1GHA.3656@TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g... >> Hi, >> I was profiling the Audit Schema Object Access Event in SQL Server > 2005, >> and when I executed the system stored procedure "sys.xp_msver" I found > that >> the database id of the this procedure is shown as "32767" which doesn't >> exists in the sys.databases view ? Any idea to what database it belongs >> to > ? >> Does the above belong to the "mssqlsystemresource" or "distmdl" >> databases ? >> I also wanted information as to how do I query the system tables, it >> gives me an error saying the object doesnt exists >> >> Experts pls help >> >> TIA >> >> Thanks >> P >> >> > > Hi,
Regarding your suggestion for the system table I tried the same thing as told by you "select * from msdb..backupfile" But in the profiler it still returns that as a User table instead of a System table. Any ideas ? Thanks P Show quote "Martin Poon [MVP]" <martinpoon@graduate(.)hku(.)hk> wrote in message news:u40Vtxa1GHA.4448@TK2MSFTNGP04.phx.gbl... > [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to > any database. > > - Breaking changes to database engine features in SQL Server 2005 > http://msdn2.microsoft.com/en-us/library/ms143179.aspx > > [2] To query a system table, you will need to prefix the table with the > appropriate database that you are going to query. E.g., > SELECT * FROM msdb..backupfile > > - Querying the SQL Server System Catalog > http://msdn2.microsoft.com/en-us/library/ms189082.aspx > - Querying the SQL Server System Catalog FAQ > http://msdn2.microsoft.com/en-us/library/ms345522.aspx > > -- > Martin Poon > Microsoft MVP - SQL Server > -------------------------------------------------- > - SQL Server 2005 books online > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > - SQL Server 2000 books online > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > ================================================== > "Prasad" <ekke_nik***@yahoo.co.uk> ¦b¶l¥ó > news:e1BoWNa1GHA.3656@TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g... >> Hi, >> I was profiling the Audit Schema Object Access Event in SQL Server > 2005, >> and when I executed the system stored procedure "sys.xp_msver" I found > that >> the database id of the this procedure is shown as "32767" which doesn't >> exists in the sys.databases view ? Any idea to what database it belongs >> to > ? >> Does the above belong to the "mssqlsystemresource" or "distmdl" >> databases ? >> I also wanted information as to how do I query the system tables, it >> gives me an error saying the object doesnt exists >> >> Experts pls help >> >> TIA >> >> Thanks >> P >> >> > > Some tables are flagged as user tables even though they are shipped with the product. The backup
history tables are such examples. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Prasad" <ekke_nik***@yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@TK2MSFTNGP03.phx.gbl... > Hi, > Regarding your suggestion for the system table I tried the same thing as told by you "select * > from msdb..backupfile" > But in the profiler it still returns that as a User table instead of a System table. > > Any ideas ? > > Thanks > P > > "Martin Poon [MVP]" <martinpoon@graduate(.)hku(.)hk> wrote in message > news:u40Vtxa1GHA.4448@TK2MSFTNGP04.phx.gbl... >> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to >> any database. >> >> - Breaking changes to database engine features in SQL Server 2005 >> http://msdn2.microsoft.com/en-us/library/ms143179.aspx >> >> [2] To query a system table, you will need to prefix the table with the >> appropriate database that you are going to query. E.g., >> SELECT * FROM msdb..backupfile >> >> - Querying the SQL Server System Catalog >> http://msdn2.microsoft.com/en-us/library/ms189082.aspx >> - Querying the SQL Server System Catalog FAQ >> http://msdn2.microsoft.com/en-us/library/ms345522.aspx >> >> -- >> Martin Poon >> Microsoft MVP - SQL Server >> -------------------------------------------------- >> - SQL Server 2005 books online >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> - SQL Server 2000 books online >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> ================================================== >> "Prasad" <ekke_nik***@yahoo.co.uk> ¦b¶l¥ó >> news:e1BoWNa1GHA.3656@TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g... >>> Hi, >>> I was profiling the Audit Schema Object Access Event in SQL Server >> 2005, >>> and when I executed the system stored procedure "sys.xp_msver" I found >> that >>> the database id of the this procedure is shown as "32767" which doesn't >>> exists in the sys.databases view ? Any idea to what database it belongs to >> ? >>> Does the above belong to the "mssqlsystemresource" or "distmdl" >>> databases ? >>> I also wanted information as to how do I query the system tables, it >>> gives me an error saying the object doesnt exists >>> >>> Experts pls help >>> >>> TIA >>> >>> Thanks >>> P >>> >>> >> >> > > Yes, sys.xp_msver lives in the system resource database. You can see this by connecting through the
dedicated administrator connection (DAC), looking at the source code from the view sys.databases and then do a select directly against the physical system table that the view sys.databases uses. I don't know what you want to achieve in the end, but best bet is probably to disregard objects that lives in the database with id 32767. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Prasad" <ekke_nik***@yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@TK2MSFTNGP04.phx.gbl... > Hi, > I was profiling the Audit Schema Object Access Event in SQL Server 2005, and when I executed > the system stored procedure "sys.xp_msver" I found that the database id of the this procedure is > shown as "32767" which doesn't exists in the sys.databases view ? Any idea to what database it > belongs to ? > Does the above belong to the "mssqlsystemresource" or "distmdl" databases ? > I also wanted information as to how do I query the system tables, it gives me an error saying > the object doesnt exists > > Experts pls help > > TIA > > Thanks > P >  |
|||||||||||||||||||||||
Other interesting topics