解决方案 »
- 求助啊,ACCESS中同一个数据库里,讲一个标的一列更新到另一个表的一列中
- SQL Server 2005识别的CPU型号错误
- 麻烦大家解答下几个面试题
- mssql或access 怎么设置默认固定10位随机正整数 ,一个字段,一千万条
- 请问:如何在同一块表中查询相同的记录?
- SQL基本语句
- 帮忙分析下这个游标
- SQL 怎么连接两个 查询结果??????
- 新手求老师:如果一个存贮过程中有多个批,这些批这间如何共享参数呢?(即共享接收过来的参数)
- 用sql将文件存入字段。(MS SQL SERVER)
- 我使用SQL SERVER2000的时候,使用事务有许多的地方有时总爱忘关闭事务,有没有方法可以查看是那台机器的事务没关?
- 求一个算法!!在线等... ...
不知道我这个对你有没有帮助
看了一下,能再解释下吗?好像没有update_one
我是说你的那段SP没有用到EXEC sp_change_users_login 'Update_One',只有auto_fix?
你参考这个吧
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT
FROM GetOrphanUsers
INTO @username
END他这个是建在单独数据库下的,我这个你建在任一个库下都行,稍微改改就可以
就不会有此问题了,那才是真正的高可用.
DECLARE @databasename as varchar(200)
declare @strSQL as nvarchar (4000)
DECLARE Curse CURSOR local fast_forward
FOR
SELECT
name
FROM
master.dbo.sysdatabases OPEN Curse FETCH next FROM Curse INTO @databasename
WHILE @@fetch_status = 0
BEGIN
SET @strSQL = 'USE '+ @databasename + '
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
IF exists(select * FROM master..syslogins WHERE [name] = @usrname)
select @command='' sp_change_users_login auto_fix, '' + @usrname
ELSE
EXEC sp_change_users_login ''update_one'', @usrname, @usrname
FETCH NEXT
FROM Crs
INTO @usrname
print @command
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs'
EXEC dbo.sp_executesql @strSQL
fetch next from Curse into @databasename
end
close Curse
deallocate Curse
--Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
--Terminating this procedure. The User name 'MS_DataCollectorInternalUser' is absent or invalid.它一定是在update之前发现是孤立用户。能不能再帮忙改进让它自动建立对应的登录名?
exec sp_change_users_login auto_fix, top
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'top'.
top 是我加进去DB级的用户了。
select @command='' sp_change_users_login auto_fix, '' + @usrname这是源代码,上面也有,@usrname不可能加引号吧,我刚才试了另外一个用户名没问题。怎么回事
DECLARE @databasename as varchar(200)
declare @strSQL as nvarchar (4000)
DECLARE Curse CURSOR local fast_forward
FOR
SELECT
name
FROM
master.dbo.sysdatabases
OPEN Curse FETCH next FROM Curse INTO @databasename
WHILE @@fetch_status = 0
BEGIN
SET @strSQL = 'USE '+ @databasename + '
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
IF not exists(select * FROM master..syslogins WHERE [name] = @usrname)
select @command='' sp_change_users_login report ''
else IF exists(select * FROM master..syslogins WHERE [name] = @usrname)
select @command='' sp_change_users_login auto_fix, '' + @usrname
ELSE
EXEC sp_change_users_login ''update_one'', @usrname, @usrname
FETCH NEXT
FROM Crs
INTO @usrname
print @command
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs'
EXEC dbo.sp_executesql @strSQL
fetch next from Curse into @databasename
end
close Curse
deallocate Curse 我想不同条件的执行不同语句,但是现在的写法逻辑好像不对,如果没有找到DB级用户,执行report列出,但是其他存在的却不执行auto_fix和update。 这块怎么改?
帮忙看看啊~~~