processing order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

processing order

Author

Topic

lfmn
Posting Yak Master

141 Posts

Posted - 2001-05-16 : 14:24:00

I know that this is a lot of code to look at, but if anyone has the time, I would be interested in another opinion. This has me (and the other people I work with) stumped. This code should be run from the master db and you need to have the pubs db installed on your server (or rename pubs to any other db). We've run this on two different computers with the same results 1)Windows NT workstation with SP 5, SQL server 7 with SP2 and 2) Window NT server SP 6a, SQL server 7 with no service pack. It appears that this query is being processed in the incorrect order. I'm getting the following error - Server: Msg 207, Level 16, State 1, Line 78
Invalid column name 'username'., but I'm trying to drop the first table named _jfktmp2 and create a new table with the same name. The second _jfktmp2 does have the column username. If I comment out different sections of code, I can get parts of it to work, but not the whole thing. We are currently using an UGLY solution - a go statement and declare the variables again, but now this has gotten personal. I can't be vexed by a stupid machine!! Any thoughts would be appreciated

set nocount on
declare @sysadmin sysname
declare @securityadmin sysname
declare @serveradmin sysname
declare @setupadmin sysname
declare @processadmin sysname
declare @diskadmin sysname
declare @dbcreator sysname
declare @username sysname

create table pubs.dbo._jfktmp2
(
ServerRole sysname null,
MemberName sysname null,
MemberSID varbinary(85) null
)

insert pubs.dbo._jfktmp2 exec sp_helpsrvrolemember

select MemberName, ServerRole
into pubs.dbo._jfktmp3
from pubs.dbo._jfktmp2
order by MemberName

drop table pubs.dbo._jfktmp2

select * from pubs.dbo._jfktmp3

create table pubs.dbo._jfktmp2
(
username sysname null,
sysadmin sysname null,
securityadmin sysname null,
serveradmin sysname null,
setupadmin sysname null,
processadmin sysname null,
diskadmin sysname null,
dbcreator sysname null
)

declare db_cursor2 cursor for
select distinct membername from pubs.dbo._jfktmp3

open db_cursor2
fetch next from db_cursor2 into @username
while @@fetch_status = 0
begin
set @sysadmin = 'NO'
set @securityadmin = 'NO'
set @serveradmin = 'NO'
set @setupadmin = 'NO'
set @processadmin = 'NO'
set @diskadmin = 'NO'
set @dbcreator = 'NO'
if (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'sysadmin')) is not null
set @sysadmin = 'SELECTED'
if (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'securityadmin')) is not null
set @securityadmin = 'SELECTED'
if (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'serveradmin')) is not null
set @serveradmin = 'SELECTED'
if (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'setupadmin')) is not null
set @setupadmin = 'SELECTED'
if (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'processadmin')) is not null
set @processadmin = 'SELECTED'
if (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'diskadmin')) is not null
set @diskadmin = 'SELECTED'
if (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'dbcreator')) is not null
set @dbcreator = 'SELECTED'
/*
set @securityadmin = (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'securityadmin'))
set @serveradmin = (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'serveradmin'))
set @setupadmin = (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'setupadmin'))
set @processadmin = (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'processadmin'))
set @diskadmin = (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'diskadmin'))
set @dbcreator = (select serverrole from pubs.dbo._jfktmp3 where (membername = @username) and (serverrole = 'dbcreator'))
*/
insert pubs.dbo._jfktmp2 (username,sysadmin,securityadmin,serveradmin,setupadmin,processadmin,diskadmin,dbcreator) values (@username,@sysadmin,@securityadmin,@serveradmin,@setupadmin,@processadmin,@diskadmin,@dbcreator)
fetch next from db_cursor2 into @username
end
close db_cursor2

select * from pubs.dbo._jfktmp2

deallocate db_cursor2
drop table pubs.dbo._jfktmp2
drop table pubs.dbo._jfktmp3

lfmn16

DBA/Developer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources