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?
AntoninWhat 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...
>>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
>|||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...
>> 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
>>
>|||> 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...
>> 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
>|||"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...
>> 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
>
--
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('InstanceName')),'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...
>>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...
>> 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
>>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
Showing posts with label disappeared. Show all posts
Showing posts with label disappeared. Show all posts
Monday, March 12, 2012
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
>
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
>
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?
AntoninWhat we used to call system tables are not implemented as views. Read in Boo
ks 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 t
ables 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 li
ke these. Sysxlogins
wasn't documented.
The old system tables (what was documented) are now called compatibility vie
ws. 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 catalo
g views.
So, you need to go through your code and find what code uses old undocumente
d 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.gb
l...
> 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 i
n 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/SQLSer...=27224
5|||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 mess
age
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/SQLSer...=272
245
>|||"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('
InstanceName')),'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
>
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?
AntoninWhat we used to call system tables are not implemented as views. Read in Boo
ks 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 t
ables 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 li
ke these. Sysxlogins
wasn't documented.
The old system tables (what was documented) are now called compatibility vie
ws. 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 catalo
g views.
So, you need to go through your code and find what code uses old undocumente
d 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.gb
l...
> 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 i
n 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/SQLSer...=27224
5|||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 mess
age
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/SQLSer...=272
245
>|||"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('
InstanceName')),'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
>
Wednesday, March 7, 2012
Missing ODBC drivers - please help!!
Hi all
For some reason, my Excel / Access drivers have disappeared from the list of available data sources when running the Import wizard or when trying to create a dts package. They are installed on the machine as I can see them through the ODBC Administrator. I have googled extensively, found some posts relating to TimeSlips (not relevant as not installed), re-installing MDAC.
Having done all the relevant stuff (updating to latest MDAC), still not coming up. Installed SQL 2005 Management Studio thinking that may help. Still nothing.Can someone please give me some advice, APART FROM RE-INSTALLING OS.
How does Import wizard pickup the available drivers, any registry settings...? Currently running XP SP2 with all the latest updates.
Need to get data uploaded to customer site from Excel spreadsheet. So any help / advice would be greatly appreciated!
TIA
Regan
Might try http://www.datadirect.com/download/index.ssp here to download.
HTH
|||Hi Satya
In the hope of NOT making things worse, I don't want to install third party stuff just yet. I will give it a go if no other advice is submitted. Thanks
Regan
|||Are there any recent chanages such as rollout of hotfix or service pack on this installation, as you say previously it used to be there.|||It was definitely working on the 13Nov. That was the last time I did an import into a sql db.
Automatic updates has been running and yes there have been a few. Tried a system restore, but for some reason the wizard only goes back to the 20th. Not sure why THAT is.
Anyway, did a restore to the 20th and the problem ist still there. My question is HOW does the import wizard enumerate the list for the data source drop down box. I am leading towards the following strategy:
1. A XP repair attempt which I am assuming will restore everything to installation default, but still keep my program files ...I hope!?
2. If no luck there then a complete re-install. This is an absolute last resort as it will involve installing and setting up my development environment, all vpn access connections and everything else and will be a complete pain not to mention the 1 to 2 days downtime (which I REALLY can't afford).
Maybe using the data direct driver is still an option. but that still doesn't fix my machine.
So ANY help would be greatly appreciated.
Thanks
Regan
|||Appreciate your feedback in this case to understand the problem and workaround
.I believe it looks like some sort of MDAC tools mismatch causing this issue and relevant ODBC drivers .DLL might be missing, may try with DataDirect and they can be trusted as no issues it works me always.
I suggest you may try opening a case with MS PSS if this is causing a major downgrade to your production system, if not live with third party tool until you can get another machine to fix the problem.
Sorry, nothing much help but few options you might try out.
Good luck.|||
Did you ever get this fixed? I have a staff member that has the same problem and is running XP SP2 with no ODBC Drivers at all.
|||Have you referred to the link above to get the relevant drivers?Missing ODBC Drivers - please help!!
Hi all
For some reason, my Excel / Access drivers have disappeared from the list of available data sources when running the Import wizard or when trying to create a dts package. They are installed on the machine as I can see them through the ODBC Administrator. I have googled extensively, found some posts relating to TimeSlips (not relevant as not installed), re-installing MDAC.
Having done all the relevant stuff (updating to latest MDAC), still not coming up. Installed SQL 2005 Management Studio thinking that may help. Still nothing.Can someone please give me some advice, APART FROM RE-INSTALLING OS.
How does Import wizard pickup the available drivers, any registry settings...? Currently running XP SP2 with all the latest updates.
Need to get data uploaded to customer site from Excel spreadsheet. So any help / advice would be greatly appreciated!
TIA
Regan
Missing ODBC drivers - please help!!
Hi all
For some reason, my Excel / Access drivers have disappeared from the list of available data sources when running the Import wizard or when trying to create a dts package. They are installed on the machine as I can see them through the ODBC Administrator. I have googled extensively, found some posts relating to TimeSlips (not relevant as not installed), re-installing MDAC.
Having done all the relevant stuff (updating to latest MDAC), still not coming up. Installed SQL 2005 Management Studio thinking that may help. Still nothing.Can someone please give me some advice, APART FROM RE-INSTALLING OS.
How does Import wizard pickup the available drivers, any registry settings...? Currently running XP SP2 with all the latest updates.
Need to get data uploaded to customer site from Excel spreadsheet. So any help / advice would be greatly appreciated!
TIA
Regan
Might try http://www.datadirect.com/download/index.ssp here to download.
HTH
|||Hi Satya
In the hope of NOT making things worse, I don't want to install third party stuff just yet. I will give it a go if no other advice is submitted. Thanks
Regan
|||Are there any recent chanages such as rollout of hotfix or service pack on this installation, as you say previously it used to be there.|||It was definitely working on the 13Nov. That was the last time I did an import into a sql db.
Automatic updates has been running and yes there have been a few. Tried a system restore, but for some reason the wizard only goes back to the 20th. Not sure why THAT is.
Anyway, did a restore to the 20th and the problem ist still there. My question is HOW does the import wizard enumerate the list for the data source drop down box. I am leading towards the following strategy:
1. A XP repair attempt which I am assuming will restore everything to installation default, but still keep my program files ...I hope!?
2. If no luck there then a complete re-install. This is an absolute last resort as it will involve installing and setting up my development environment, all vpn access connections and everything else and will be a complete pain not to mention the 1 to 2 days downtime (which I REALLY can't afford).
Maybe using the data direct driver is still an option. but that still doesn't fix my machine.
So ANY help would be greatly appreciated.
Thanks
Regan
|||Appreciate your feedback in this case to understand the problem and workaround.I believe it looks like some sort of MDAC tools mismatch causing this issue and relevant ODBC drivers .DLL might be missing, may try with DataDirect and they can be trusted as no issues it works me always.
I suggest you may try opening a case with MS PSS if this is causing a major downgrade to your production system, if not live with third party tool until you can get another machine to fix the problem.
Sorry, nothing much help but few options you might try out.
Good luck.|||
Did you ever get this fixed? I have a staff member that has the same problem and is running XP SP2 with no ODBC Drivers at all.
|||Have you referred to the link above to get the relevant drivers?Missing ODBC drivers - please help!!
Hi all
For some reason, my Excel / Access drivers have disappeared from the list of available data sources when running the Import wizard or when trying to create a dts package. They are installed on the machine as I can see them through the ODBC Administrator. I have googled extensively, found some posts relating to TimeSlips (not relevant as not installed), re-installing MDAC.
Having done all the relevant stuff (updating to latest MDAC), still not coming up. Installed SQL 2005 Management Studio thinking that may help. Still nothing.Can someone please give me some advice, APART FROM RE-INSTALLING OS.
How does Import wizard pickup the available drivers, any registry settings...? Currently running XP SP2 with all the latest updates.
Need to get data uploaded to customer site from Excel spreadsheet. So any help / advice would be greatly appreciated!
TIA
Regan
Might try http://www.datadirect.com/download/index.ssp here to download.
HTH
|||Hi Satya
In the hope of NOT making things worse, I don't want to install third party stuff just yet. I will give it a go if no other advice is submitted. Thanks
Regan
|||Are there any recent chanages such as rollout of hotfix or service pack on this installation, as you say previously it used to be there.|||It was definitely working on the 13Nov. That was the last time I did an import into a sql db.
Automatic updates has been running and yes there have been a few. Tried a system restore, but for some reason the wizard only goes back to the 20th. Not sure why THAT is.
Anyway, did a restore to the 20th and the problem ist still there. My question is HOW does the import wizard enumerate the list for the data source drop down box. I am leading towards the following strategy:
1. A XP repair attempt which I am assuming will restore everything to installation default, but still keep my program files ...I hope!?
2. If no luck there then a complete re-install. This is an absolute last resort as it will involve installing and setting up my development environment, all vpn access connections and everything else and will be a complete pain not to mention the 1 to 2 days downtime (which I REALLY can't afford).
Maybe using the data direct driver is still an option. but that still doesn't fix my machine.
So ANY help would be greatly appreciated.
Thanks
Regan
|||Appreciate your feedback in this case to understand the problem and workaround.I believe it looks like some sort of MDAC tools mismatch causing this issue and relevant ODBC drivers .DLL might be missing, may try with DataDirect and they can be trusted as no issues it works me always.
I suggest you may try opening a case with MS PSS if this is causing a major downgrade to your production system, if not live with third party tool until you can get another machine to fix the problem.
Sorry, nothing much help but few options you might try out.
Good luck.
Missing ODBC drivers - please help!
Hi all
For some reason, my Excel / Access drivers have disappeared from the list of available data sources when running the Import wizard or when trying to create a dts package. They are installed on the machine as I can see them through the ODBC Administrator. I have googled extensively, found some posts relating to TimeSlips (not relevant as not installed), re-installing MDAC.
Having done all the relevant stuff (updating to latest MDAC), still not coming up. Installed SQL 2005 Management Studio thinking that may help. Still nothing.Can someone please give me some advice, APART FROM RE-INSTALLING OS.
How does Import wizard pickup the available drivers, any registry settings...? Currently running XP SP2 with all the latest updates.
Need to get data uploaded to customer site from Excel spreadsheet. So any help / advice would be greatly appreciated!
TIA
Regan
Might try http://www.datadirect.com/download/index.ssp here to download.
HTH
|||Hi Satya
In the hope of NOT making things worse, I don't want to install third party stuff just yet. I will give it a go if no other advice is submitted. Thanks
Regan
|||Are there any recent chanages such as rollout of hotfix or service pack on this installation, as you say previously it used to be there.|||It was definitely working on the 13Nov. That was the last time I did an import into a sql db.
Automatic updates has been running and yes there have been a few. Tried a system restore, but for some reason the wizard only goes back to the 20th. Not sure why THAT is.
Anyway, did a restore to the 20th and the problem ist still there. My question is HOW does the import wizard enumerate the list for the data source drop down box. I am leading towards the following strategy:
1. A XP repair attempt which I am assuming will restore everything to installation default, but still keep my program files ...I hope!?
2. If no luck there then a complete re-install. This is an absolute last resort as it will involve installing and setting up my development environment, all vpn access connections and everything else and will be a complete pain not to mention the 1 to 2 days downtime (which I REALLY can't afford).
Maybe using the data direct driver is still an option. but that still doesn't fix my machine.
So ANY help would be greatly appreciated.
Thanks
Regan
|||Appreciate your feedback in this case to understand the problem and workaround.I believe it looks like some sort of MDAC tools mismatch causing this issue and relevant ODBC drivers .DLL might be missing, may try with DataDirect and they can be trusted as no issues it works me always.
I suggest you may try opening a case with MS PSS if this is causing a major downgrade to your production system, if not live with third party tool until you can get another machine to fix the problem.
Sorry, nothing much help but few options you might try out.
Good luck.
Subscribe to:
Posts (Atom)