SQL2000 备份的数据库文件恢复到sql2005中，创建broker ,结果发送消息后，队列中发现没有消息。

SQL2000 备份的数据库文件恢复到sql2005中，创建broker ,结果发送消息后，队列中发现没有消息。
发送消息前，我已经修改数据库的运行兼容级别为sql2005 模式。
希望专家告诉原因。
这是我的脚本

USE master

ALTER DATABASE test SET ENABLE_BROKER
go



-------------------------------------------------------------------------------
-- 1 生成队列
create queue dbo.statQueue

go




--------------------------------------------------------------------------------
--5 创建消息 和服务端口
CREATE MESSAGE TYPE MSGT_counter_trigger
VALIDATION = WELL_FORMED_XML
GO

 -- 创建约定，只需要发送消息
 CREATE CONTRACT CNT_counter_trigger(

    MSGT_counter_trigger SENT BY INITIATOR)
GO
--- 创建服务端口

CREATE SERVICE SRV_counter_trigger

    ON QUEUE dbo.statQueue(

        CNT_counter_trigger)

GO

--------------------------------------------------------------------
-- 6 添加 发送消息代理存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[async_SendCountMsg] 
@message [nvarchar](max)
AS
BEGIN

SET NOCOUNT ON;
    DECLARE   @handle uniqueidentifier
begin tran

BEGIN DIALOG CONVERSATION @handle

FROM SERVICE [SRV_counter_trigger]

TO SERVICE N'SRV_counter_trigger'

ON CONTRACT CNT_counter_trigger

WITH

ENCRYPTION = OFF;

SEND

ON CONVERSATION @handle

MESSAGE TYPE MSGT_counter_trigger(@message);

-- 消息发出即可, 不需要回复, 因此发出后即可结束会话

END CONVERSATION @handle

commit tran
END

10 个解决方案

#1

你查看下sys.transmission_queue里有没记录，如果有
transmission_status列显示的是什么，或者你把记录贴出来。

#2

刚用你的代码测试了，是OK的，你是不是传递的消息内容不符合XML格式
导致验证失败

#3

这是 select * from sys.transmission_queue 的内容
conversation_handle                  to_service_name                                                                                                                                                                                                                                                  to_broker_instance                                                                                                               from_service_name                                                                                                                                                                                                                                                service_contract_name                                                                                                                                                                                                                                            enqueue_time            message_sequence_number message_type_name                                                                                                                                                                                                                                                is_conversation_error is_end_of_dialog message_body                                                                                                                                                                                                                                                     transmission_status
------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
F0DF456E-8D9B-DD11-909C-00142272F0C5 SRV_counter_trigger                                                                                                                                                                                                                                                                                                                                                                               SRV_counter_trigger                                                                                                                                                                                                                                              CNT_counter_trigger                                                                                                                                                                                                                                              2008-10-16 14:19:24.467 0                       MSGT_counter_trigger                                                                                                                                                                                                                                             0                     0                0x3C0061003E003C0062002F003E003C0061002F003E00                                                                                                                                                                                                                   An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

#4

同时发现 应用日志有如下错误：Service Broker 消息调度程序出错: 错误: 15517 状态: 1

#5

use test
go

exec sp_change_users_login 'report'
go

exec sp_helpdb 'test'
go

结果是什么，估计楼主没有修复孤立用户，以及数据库所有者不是sa

#6

exec sp_change_users_login 'report'
的结果是：

UserName                                                                                                                         UserSID
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
No4user                                                                                                                          0x074E43E705AE5E42BDA6E08FCC6359F2

#7

SQL还原数据库后孤立用户问题处理

所谓孤立帐户，就是某个数据库的帐户只有用户名而没有登录名，这样的用户在用户库的sysusers系统表中存在，而在master数据库的syslogins中却没有对应的记录

孤立帐户的产生一般是一下两种：
1.将备份的数据库在其它机器上还原；
2.重装系统或SQL SERVER之后只还原了用户库

解决方法是使用sp_change_users_login来修复

sp_change_users_login的用法有三种

用法1：
exec sp_change_users_login 'REPORT'

列出当前数据库的孤立用户

用法2：
exec sp_change_users_login 'AUTO_FIX','用户名'

可以自动将用户名所对应的同名登录添加到syslogins中

用法3：
exec sp_change_users_login 'UPDATE_ONE','用户名','登录名'
将用户名映射为指定的登录名 
----------------------------------------------------------------------------------------------------------------
看看是否有用


SQL孤立用户解决方案  


症状

当您将数据库备份恢复到另一台服务器时，可能会遇到孤立用户的问题SQL Server 联机丛书中的孤立用户疑难解答主题中没有讲述解决此问题的具体步骤

本文介绍了如何解决孤立用户问题

状态

Microsoft 已经确认这是在本文开头列出的 Microsoft 产品中存在的问题

更多信息

虽然术语登录和用户经常交换使用，但它们之间有很大的不同登录用于用户身份验证，而数据库用户帐户用于数据库访问和权限验证登录通过安全识别符 (SID) 与用户关联访问 SQL Server 服务器需要登录验证特定登录是否有效的过程称为身份验证登录必须与 SQL Server 数据库用户相关联您使用用户帐户控制数据库中执行的活动如果数据库中不存在针对特定登录的用户帐户，使用该登录的用户即使能够连接到 SQL Server 服务器，也无法访问数据库但是，该情形的唯一例外是当数据库包含guest用户帐户时与用户帐户不关联的登录将被映射到 guest 用户相反，如果存在数据库用户，但没有与其关联的登录，则该用户将无法登录到 SQL Server 服务器中

将数据库恢复到其他服务器时，数据库中包含一组用户和权限，但可能没有相应的登录或者登录所关联的用户可能不是相同的用户这种情况被称为存在孤立用户

孤立用户疑难解答

当您将数据库备份恢复到另一台服务器时，可能会遇到孤立用户的问题以下情形说明了该问题并阐述如何加以解决

1. 向主数据库添加一个登录，并将默认数据库指定为 Northwind： Use master go sp_addlogin 'test', 'password', 'Northwind'

2. 向刚创建的用户授予访问权限： Use Northwind go sp_grantdbaccess 'test'

3. 备份数据库 BACKUP DATABASE Northwind
TO DISK = 'C:MSSQLBACKUPNorthwind.bak'

4. 将数据库恢复到其他 SQL Server 服务器： RESTORE DATABASE Northwind
FROM DISK = 'C:MSSQLBACKUPNorthwind.bak'
      
恢复的数据库包含名为test的用户，但没有相应的登录，这就导致test成为孤立用户 
5. 现在，为了检测孤立用户，请运行此代码： Use Northwind go sp_change_users_login 'report'
      
输出中列出了所有登录，其中包含 Northwind 数据库的 sysusers 系统表和主数据库的 sysxlogins 系统表中不匹配的条目

解决孤立用户问题的步骤

1. 为前一步中的孤立用户运行以下命令：

Use Northwind
go
sp_change_users_login 'update_one', 'test', 'test'
      
这样，就将服务器登录test与 Northwind 数据库用户test重新连接起来

sp_change_users_login 存储过程还可以使用auto_fix参数对所有孤立用户执行更新，但不推荐这样做，因为 SQL Server 会尝试按名称匹配登录和用户大多数情况下这都是可行的；但是，如果用户与错误登录关联，该用户可能拥有错误的权限

2. 在上一步中运行代码后，用户就可以访问数据库了然后用户可以使用 sp_password 存储过程更改密码： Use master
go
sp_password NULL, 'ok', 'test'
      
此存储过程不能用于 Microsoft Windows NT 安全帐户通过 Windows NT 网络帐户连接到 SQL Server 服务器的用户是由 Windows NT 授权的；因此，这些用户只能在 Windows NT 中更改密码

只有 sysadmin 角色的成员可以更改其他用户的登录密码 
----------------------------------------------------------------------------------------------------------------

SQL2005删除用户的时候，产生数据库主体在该数据库中拥有架构，无法删除的解决办法

--执行如下SQL语句

ALTER   AUTHORIZATION   ON   SCHEMA::db_owner   TO   dbo; 
--然后手动删除就可以了


----------------------------------------------------------------------------------------------------------------
[导入]sql2000备份的数据库还原到sql2005后，选择数据库关系图提示：此数据库没有有效所有者，因此无法安装数据库关系图支持对象"的解决方法 
sql2000备份的数据库还原到sql2005后，选择数据库关系图提示：此数据库没有有效所有者，因此无法安装数据库关系图支持对象若要继续，请首先使用数据库属性对话框的文件页或 ALTER AUTHORIZATION 语句将数据库所有者设置为有效登录名，然后再添加数据库关系图支持对象 
     
解决方法如下： 
1设置兼容级别为90（2005为90） 
USE [master] 
GO 
EXEC dbo.sp_dbcmptlevel @dbname='数据库名', @new_cmptlevel=90 
GO  

或是选责你还原的数据库，点右键，选属性->选项->兼容级别，选择sqlserver2005(90) 然后确定，

      这时,你在该数据库下展开数据库关系图节点时会有个提示，"此数据库缺少一个或多个使用数据库关系图所需的支持对象，是否创建"，选择是即可

2通过以上的方法操作，如果问题依然存在的话，按下列方法继续

选择你的数据库，然后选择"安全性"->"用户",选择dbo,打开属性页，如登录名为空的话，新建查询，然后

use [你的数据库名]
EXEC   sp_changedbowner   'sa' 

执行成功后，你再选择"数据库关系图"节点，时提示 此数据库缺少一个或多个使用数据库关系图所需的支持对象，是否创建"，选择是即可 就可以看到原先建的关系图了


----------------------------------------------------------------------------------------------------------------

从服务器上作导入导出至本地机上，数据库中的表都在，可是表名前段的架构身份不是dbo了，而是服务器上数据库的库名这样架构身份不同了，程序运行就出问题了试过单个修改表，在sql2005的属性窗口可以更改架构者，可是N多表哪儿能手动改得过来呀！还请高手指点批量更改的方法在此谢过 
SQL   Server2005可以使用系统存储过程sp_changeobjectowner更改数据库对象的所有者   
    
sp_changeobjectowner   '对象名(包括架构名)','新架构名'   
    
批量修改请用：   
    
方法一：使用游标   
    
declare   @name   sysname   
declare   csr1   cursor   
for     
      select   TABLE_NAME   from   INFORMATION_SCHEMA.TABLES   
open   csr1   
    
FETCH   NEXT   FROM   csr1   INTO   @name   
while   (@@FETCH_STATUS=0)   
      BEGIN   
SET   @name='原架构名.'+@name   
                  EXEC   SP_ChangeObjectOwner   @name,   '新架构名'   
    fetch   next   from   csr1   into   @name   
      END   
CLOSE   csr1   
DEALLOCATE   csr1   
    
方法二：使用系统存储过程sp_MSforeachtable   
EXEC   sp_MSforeachtable   @command1="EXEC   SP_ChangeObjectOwner   '?','新架构名'"
----------------------------------------------------------------------------------------------------------------
在sql server 2005数据库中更改数据架构 
在数据库testDB中存在架构A及用户A，现将testDB数据库所属的用户由A改为B，同时删除用户A；架构也由A改为B，删除架构A，操作如下：

1创建用户B，再创建架构B；
2将架构A的权限赋给用户B，取消用户A拥有架构A的权限，删除用户A；
3将数据库的所有属于架构A的对象改为架构B，代码如下：
ALTER SCHEMA [新架构名] TRANSFER 旧架构名.[数据库中的对象表或视图或存储过程]

ALTER SCHEMA [B] TRANSFER A.[对象1]
ALTER SCHEMA [B] TRANSFER A.[对象2]
ALTER SCHEMA [B] TRANSFER A.[对象n]
4删除架构A


 

#8

引用 6 楼 winter_leaf_wei 的回复:

exec sp_change_users_login 'report' 
的结果是： 

UserName                                                                                                                        UserSID 
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------…

EXEC sp_change_users_login 'Auto_Fix', 'No4user', NULL, '这里输入密码';
GO
--修复这个孤立用户，建立一个同名的登录

--完了之后，再发送消息试试，应该是没问题了。

--另外还有 sp_helpdb 'test'的结果呢

#9

如果还是不行，你再执行语句：

use test
go
exec sp_changedbowner 'sa'
go

应该就可以了，其实在恢复数据库做SERVICE BROKER时，这两点都是很重要的。

#10

非常感谢，我先试试看。 很快会结贴