missing tables

I upgraded SQL Server 2000 with 2005 Developer. Now, I have only 6 system
tables in the master database. All the others disappeared. I need them for
running my old stored procedures. Do I have to reinstall 2000 to get back
the functionality?
Antonin
What we used to call system tables are not implemented as views. Read in Books Online about
"compatibility views".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Antonin" <antonin@.comphub.com> wrote in message news:uoQXFfwgHHA.1244@.TK2MSFTNGP04.phx.gbl...
>I upgraded SQL Server 2000 with 2005 Developer. Now, I have only 6 system tables in the master
>database. All the others disappeared. I need them for running my old stored procedures. Do I have
>to reinstall 2000 to get back the functionality?
> Antonin
>
|||Thanks Tibor,
I can not find any how to fix this:
'Invalid object name 'master.dbo.sysxlogins'
Antonin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uZKmhnxgHHA.4892@.TK2MSFTNGP03.phx.gbl...
> What we used to call system tables are not implemented as views. Read in
> Books Online about "compatibility views".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Antonin" <antonin@.comphub.com> wrote in message
> news:uoQXFfwgHHA.1244@.TK2MSFTNGP04.phx.gbl...
>
|||When you use undocumented features in a product you need to expect things like these. Sysxlogins
wasn't documented.
The old system tables (what was documented) are now called compatibility views. The new views has a
high degree of backwards compatibility, but again for what was documented in the first plans. These
are meant for backwards compatibility and the replacement are the new catalog views.
So, you need to go through your code and find what code uses old undocumented stuff, and fix it
before it will run (well) on 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Antonin" <antonin@.comphub.com> wrote in message news:%23cXL6rygHHA.4992@.TK2MSFTNGP06.phx.gbl...
> Thanks Tibor,
> I can not find any how to fix this:
> 'Invalid object name 'master.dbo.sysxlogins'
> Antonin
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uZKmhnxgHHA.4892@.TK2MSFTNGP03.phx.gbl...
>
|||> When you use undocumented features in a product you need to expect things
> like these. Sysxlogins wasn't documented.
Sadly, though, Microsoft does talk about it in their documentation. In SQL
Server 2000 Books Online, look at these topics:
Resolving Permission Conflicts
SQL Profiler Data Columns
And in 2005, there is a topic (Breaking Changes to Database Engine Features
in SQL Server 2005, ironically!) that says:
<snip>
You can identify dormant SQL Server 6.5 logins by using the following query:
SELECT * FROM sysxlogins WHERE (xstatus & 2048) = 2048;
</snip>
They corrected a similar issue I brought up on connect a while back, where
sp_who2 was mentioned in a single Books Online article. I just entered a
new one for this omission, which will at least correct the 2005 issue.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272245
|||I found somewhere that I should use syslogins view in 2005 instead of
sysxlogins. Now I get when running this
SELECT 'ServerRole' = A.name, 'MemberName' = B.name
FROM master.dbo.spt_values A, master.dbo.syslogins B
WHERE A.low = 0
AND A.type = 'SRV'
AND B.srvid IS NULL
AND A.number & B.xstatus = A.number
Server: Msg 207, Level 16, State 1, Line 6
Invalid column name 'srvid'.
Server: Msg 207, Level 16, State 1, Line 7
Invalid column name 'xstatus'.
Where can I find the mappings between sysxlogins and syslogins columns?
Antonin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%239yaPC1gHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Sadly, though, Microsoft does talk about it in their documentation. In
> SQL Server 2000 Books Online, look at these topics:
> Resolving Permission Conflicts
> SQL Profiler Data Columns
> And in 2005, there is a topic (Breaking Changes to Database Engine
> Features in SQL Server 2005, ironically!) that says:
> <snip>
> You can identify dormant SQL Server 6.5 logins by using the following
> query:
> SELECT * FROM sysxlogins WHERE (xstatus & 2048) = 2048;
> </snip>
> They corrected a similar issue I brought up on connect a while back, where
> sp_who2 was mentioned in a single Books Online article. I just entered a
> new one for this omission, which will at least correct the 2005 issue.
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272245
>
|||"Antonin" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
news:%23Sm1vyVhHHA.1240@.TK2MSFTNGP04.phx.gbl...
>I found somewhere that I should use syslogins view in 2005 instead of
>sysxlogins. Now I get when running this
> SELECT 'ServerRole' = A.name, 'MemberName' = B.name
> FROM master.dbo.spt_values A, master.dbo.syslogins B
> WHERE A.low = 0
> AND A.type = 'SRV'
> AND B.srvid IS NULL
> AND A.number & B.xstatus = A.number
> Server: Msg 207, Level 16, State 1, Line 6
> Invalid column name 'srvid'.
> Server: Msg 207, Level 16, State 1, Line 7
> Invalid column name 'xstatus'.
> Where can I find the mappings between sysxlogins and syslogins columns?
Perhaps you can tell us what you're trying to do and someone can suggest a
better way to do that in SQL 2005.
In general, any time you mess with system tables, you risk running into
issues like this.

> Antonin
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%239yaPC1gHHA.4872@.TK2MSFTNGP03.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Below is the whole code. I inherited this. It runs a security report every
morning at six o'clock. I tried to work out what it is doing by running it.
However, I was not able to do it. I thing that there are some much smarter
guys than I am in this group and for them it would be obvious. I know,
everybody has other things to do than debugging someone else's code.
Nevertheless I would appreciate help. Thank you.
Antonin
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.usp_GenerateSecurityReport
AS
SET NOCOUNT ON
PRINT 'SERVER: ' + CAST(SERVERPROPERTY('servername') AS Varchar) + '\' +
ISNULL(CONVERT(char(20),SERVERPROPERTY('InstanceNa me')),'DEFAULT')
PRINT ''
PRINT 'Server Role members'
PRINT ''
SELECT 'ServerRole' = A.name, 'MemberName' = B.name
FROM master.dbo.spt_values A, master.dbo.sysxlogins B
WHERE A.low = 0
AND A.type = 'SRV'
AND B.srvid IS NULL
AND A.number & B.xstatus = A.number
PRINT ''
PRINT 'Database Role members'
PRINT ''
DECLARE curSecurity CURSOR FAST_FORWARD FOR
SELECT [name] FROM master..sysdatabases
DECLARE @.vchName varchar(50)
DECLARE @.vchSQL varchar(3000)
OPEN curSecurity
FETCH NEXT FROM curSecurity INTO @.vchName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Database Name: ' + @.vchName
PRINT ''
SET @.vchSQL = 'select substring(r.[name], 1, 30) AS RoleName,
SUBSTRING(u.[name], 1, 50) AS DB_User
from ' + @.vchName + '.dbo.sysusers r
inner join ' + @.vchName + '.dbo.sysmembers m on m.groupuid = r.uid
inner join ' + @.vchName + '.dbo.sysusers u on u.uid = m.memberuid
where r.issqlrole=1
order by 1, 2'
--print @.vchSQL
exec(@.vchSQL)
PRINT ''
FETCH NEXT FROM curSecurity INTO @.vchName
END
CLOSE curSecurity
DEALLOCATE curSecurity
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Ooy303VhHHA.4300@.TK2MSFTNGP05.phx.gbl...
> "Antonin" <Antonin.Koudelka@.fmc.sa.gov.au> wrote in message
> news:%23Sm1vyVhHHA.1240@.TK2MSFTNGP04.phx.gbl...
> Perhaps you can tell us what you're trying to do and someone can suggest a
> better way to do that in SQL 2005.
> In general, any time you mess with system tables, you risk running into
> issues like this.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>