2013年6月 的存档
2013六月27

质量保证方式

1,测试
2,监控
3,预案建立,防止问题出现时思维混乱
出现问题时想彻底解决方案!杜绝再次发生。
对于技术,如网站与系统,关键就是做好三点,速度、稳定性、安全性
安全性:可能发生的一定会发生
1,学好攻,做检测
2,做好防,服务器限制IP访问,策略,权限,默认端口,账号更改(sa,administrator)
3,做好预警,异常系统登陆日志,异常数据库连接,服务器新文件添加

关于监控,对于功能来说,核心功能在上线后要做好数据统计,分析是否有异常数据产生。
如一次上线积分产品,功能完成后,基本数据未修改,造成本不是积分产品的产品当成积分产品下,造成损失!

上线阶段:
1,开发:代码审查与TDD测试
2,上线:eventvwr分析与IIS日志
3,上线后:总结

对于新的项目流程
1,确定流程图
2,画出原型图
3,开发,代码审核与TDD
4,上线
5, 定期召集开会,看现有什么问题,改进【监控】

2013六月23

正能量 评论关闭

整个时代都建立在爱因斯坦的理论之上,知识改变世界。
爱因斯坦记录片

2013六月23

短网址实现

ASP.NET 评论关闭

算法原理

1)将长网址md5生成32位签名串,分为4段, 每段8个字节;

2)对这四段循环处理, 取8个字节, 将他看成16进制串与0x3fffffff(30位1)与操作, 即超过30位的忽略处理;

3)这30位分成6段, 每5位的数字作为字母表的索引取得特定字符, 依次进行获得6位字符串;

4)总的md5串可以获得4个6位串; 取里面的任意一个就可作为这个长url的短url地址;

 算法描述:使用6个字符来表示短链接,我们使用ASCII字符中的'a'-'z','0'-'5',共计32个字符做为集合。每个字符有32种状态,六个字符就可以表示32^6(1073741824),那么如何得到这六个字符,描述如下:

对传入的长URL进行Md5,得到一个32位的字符串,这个字符串变化很多,是16的32次方,基本上可以保证唯一性。将这32位分成四份,每一份8个字符,这时机率变成了16的8次方,是4294967296,这个数字碰撞的机率也比较小啦,关键是后面的一次处理。我们将这个8位的字符认为是16进制整数,也就是1*('0x'.$val),然后取0-30位,每5个一组,算出他的整数值,然后映射到我们准备的32个字符中,最后就能够得到一个6位的短链接地址。
 

 

 

参考:

 

短链接算法收集与分析

关于微博内容中的短地址ShortURL

各大微博短网址(ShortUrl)的算法 C#

2013六月20

67岁老母亲徒步11天探监 称怕活不到儿子出狱

正能量 评论关闭

67岁老母亲徒步11天探监 称怕活不到儿子出狱

怕活不到儿子出狱那天 从河南确山背着两笼馍馍出发

67岁老母亲徒步11天来汉探监

2013六月20

VS2010测试

ASP.NET 评论关闭
普通单元测试:测试类方法,如值是否相等,逻辑判断是否正确等
顺序单元测试:按指定顺序执行测试
UI界面测试:可以录制操作步骤测试
数据库测试:测试数据库表数据合法
压力测试:测试性能
Generic测试:与其他测试工具混合使用
2013六月19

修改SQLServer默认端口

sql server 评论关闭
修改SQLServer默认端口
1,在TCP/IP协议属性的全部侦听为“是”时,修改IPALL的端口即可
2,在TCP/IP协议属性的全部侦听为“否”时,单独设置每个IP的端口
 
默认情况下:
TCP/IP协议属性的全部侦听值为“是”
意为:在IP地址的配置中,由IPALL统管一切,
IP地址中的TCP端口、活动、启用都是无效的,只要配置上IP,都会启用的,端口是IPALL配置的端口
即:IP地址中只配置IP地址。
 
当将TCP/IP协议属性的全部侦听值为“否”时
在IP地址的配置中,自己配置自己的,决定自己的端口,是否启用等。
 
注:SQL Server 数据库引擎可以侦听同一 IP 地址的多个端口,端口以逗号分隔的格式列出:1433,1500,1501。本字段最多允许 2047 个字符。
若要配置单个 IP 地址以侦听多个端口,还必须将“TCP/IP 属性”对话框的“协议”选项卡上的“全部侦听”参数设置为“否”。
 
静态端口与动态端口
SQL Server 的默认实例侦听端口 1433 的传入连接。可以出于安全性原因或根据客户端应用程序的请求来更改该端口。
默认情况下,命名实例(包含 SQL Server Express)被配置为侦听动态端口。若要配置静态端口,请将“TCP 动态端口”框保留为空,并在“TCP 端口”框中提供一个可用的端口号。
 
动态端口

如果未启用动态端口,则为空。若要使用动态端口,请设置为 0。
对于“IPAll”,将显示所用动态端口的端口号。
如果某个 SQL Server 实例已配置为侦听动态端口,则在启动时,该实例将检查操作系统中的可用端口,并为该端口打开一个端点。传入连接必须指定要连接的端口号。
由于每次启动 SQL Server 时端口号都可能会改变,因此 SQL Server 提供 SQL Server Browser 服务监视端口,
并将传入连接指向该实例的当前端口。使用动态端口会增加通过防火墙连接 SQL Server 的复杂性,因为重新启动 SQL Server 时端口号可能会改变,从而需要更改防火墙设置。
若要避免通过防火墙连接的问题,请将 SQL Server 配置为使用静态端口。

 
 
2013六月14

Service Broker入门

sql server 评论关闭
Service Broker:多服务器单信息传递,大小写敏感
组成:
Message:信内容,为便于区分保证唯一性,信息的前缀用域名好一些。
Contracts:信头,组合message及message由哪边发送,发起者或者接收者
Queue:信封,收到的信息存储的位置

建立方式:
1,定义 SERVICE:将上面三者组合
2,信息发送:
BEGIN TRY
 BEGIN TRANSACTION;
  DECLARE @ch UNIQUEIDENTIFIER
  DECLARE @msg NVARCHAR(MAX);

  BEGIN DIALOG CONVERSATION @ch
   FROM SERVICE [InitiatorService]
   TO SERVICE 'TargetService'
   ON CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
   WITH ENCRYPTION = OFF;

  SET @msg =
   '<HelloWorldRequest>
     Klaus Aschenbrenner
   </HelloWorldRequest>';

  SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/RequestMessage] (@msg);
 COMMIT
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH
GO
3,接收信息
--***********************************************************************************
--*  Retrieve the sent message from the queue "TargetQueue" with a WAITFOR statement
--***********************************************************************************
DECLARE @cg UNIQUEIDENTIFIER
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML;

BEGIN TRY
 BEGIN TRANSACTION;

  WAITFOR (
   RECEIVE TOP (1)
    @cg = conversation_group_id,
    @ch = conversation_handle,
    @messagetypename = message_type_name,
    @messagebody = CAST(message_body AS XML)
   FROM TargetQueue
  ), TIMEOUT 60000

  IF (@@ROWCOUNT > 0)
  BEGIN
   PRINT 'Conversation group: ' + CAST(@cg AS NVARCHAR(MAX))
   PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX))
   PRINT 'Message type: ' + @messagetypename
   PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX))
  END

 COMMIT
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH
GO
4,监控对列
CREATE EVENT NOTIFICATION PoisonMessageNotification ON QUEUE TargetQueue
FOR Broker_Queue_Disabled
TO SERVICE 'PoisonMessageNotifyService', 'current database'
GO

---------------------------明细
USE master;

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Chapter3_HelloWorldSvc')
BEGIN
	PRINT 'Dropping database ''Chapter3_HelloWorldSvc''';
	DROP DATABASE Chapter3_HelloWorldSvc;
END
GO

CREATE DATABASE Chapter3_HelloWorldSvc
GO

USE Chapter3_HelloWorldSvc
GO

--*********************************************
--*  Create the message type "RequestMessage"
--*********************************************
CREATE MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
VALIDATION = NONE
GO

--*********************************************
--*  Create the message type "ResponseMessage"
--*********************************************
CREATE MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
VALIDATION = NONE
GO

--*********************************************
--*  Show the created message types
--*********************************************
SELECT * FROM sys.service_message_types
GO

--************************************************
--*  Changing the validation of the message types
--************************************************
ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
VALIDATION = WELL_FORMED_XML
GO

ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
VALIDATION = WELL_FORMED_XML
GO

--************************************************
--*  Create the contract "HelloWorldContract"
--************************************************
CREATE CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
(
	[http://ssb.csharp.at/SSB_Book/c03/RequestMessage] SENT BY INITIATOR,
	[http://ssb.csharp.at/SSB_Book/c03/ResponseMessage] SENT BY TARGET
)
GO

--*************************************************************
--*  Getting some information about the newly created contract
--*************************************************************
SELECT
	sc.name AS 'Contract',
	mt.name AS 'Message type',
	cm.is_sent_by_initiator,
	cm.is_sent_by_target,
	mt.validation
FROM sys.service_contract_message_usages cm
	INNER JOIN sys.service_message_types mt ON cm.message_type_id = mt.message_type_id
	INNER JOIN sys.service_contracts sc ON sc.service_contract_id = cm.service_contract_id
GO

--********************************************************
--*  Create the queues "InitiatorQueue" and "TargetQueue"
--********************************************************
CREATE QUEUE InitiatorQueue
WITH STATUS = ON
GO

CREATE QUEUE TargetQueue
WITH STATUS = ON
GO

--*************************************************************
--*  Getting some information about the newly created queues
--*************************************************************
SELECT * FROM sys.service_queues
GO

--************************************************************
--*  Create the queues "InitiatorService" and "TargetService"
--************************************************************
CREATE SERVICE InitiatorService
ON QUEUE InitiatorQueue
(
	[http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
)
GO

CREATE SERVICE TargetService
ON QUEUE TargetQueue
(
	[http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
)
GO

--*************************************************************
--*  Getting some information about the newly created services
--*************************************************************
SELECT
	sv.name AS 'Service',
	sc.name AS 'Contract'
FROM sys.services sv
	INNER JOIN sys.service_contract_usages scu ON scu.service_id = sv.service_id
	INNER JOIN sys.service_contracts sc ON sc.service_contract_id = scu.service_contract_id
GO

--********************************************************************
--*  Sending a message from the InitiatorService to the TargetService
--********************************************************************
BEGIN TRY
	BEGIN TRANSACTION;
		DECLARE @ch UNIQUEIDENTIFIER
		DECLARE @msg NVARCHAR(MAX);

		BEGIN DIALOG CONVERSATION @ch
			FROM SERVICE [InitiatorService]
			TO SERVICE 'TargetService'
			ON CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
			WITH ENCRYPTION = OFF;

		SET @msg =
			'<HelloWorldRequest>
					Klaus Aschenbrenner
			</HelloWorldRequest>';

		SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/RequestMessage] (@msg);
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

--********************************************************************
--*  View the sent message on the queue "TargetQueue"
--********************************************************************
SELECT * FROM TargetQueue
GO

--********************************************************************
--*  View the created conversation endpoints
--********************************************************************
SELECT * FROM sys.conversation_endpoints
GO

--********************************************************************
--*  Retrieve the sent message from the queue "TargetQueue"
--********************************************************************
DECLARE @cg UNIQUEIDENTIFIER
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML;

BEGIN TRY
	BEGIN TRANSACTION;

		RECEIVE TOP(1)
			@cg = conversation_group_id,
			@ch = conversation_handle,
			@messagetypename = message_type_name,
			@messagebody = CAST(message_body AS XML)
		FROM TargetQueue

		PRINT 'Conversation group: ' + CAST(@cg AS NVARCHAR(MAX))
		PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX))
		PRINT 'Message type: ' + @messagetypename
		PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX))

	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

--***********************************************************************************
--*  Retrieve the sent message from the queue "TargetQueue" with a WAITFOR statement
--***********************************************************************************
DECLARE @cg UNIQUEIDENTIFIER
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML;

BEGIN TRY
	BEGIN TRANSACTION;

		WAITFOR (
			RECEIVE TOP (1)
				@cg = conversation_group_id,
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM TargetQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT > 0)
		BEGIN
			PRINT 'Conversation group: ' + CAST(@cg AS NVARCHAR(MAX))
			PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX))
			PRINT 'Message type: ' + @messagetypename
			PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX))
		END

	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

--**************************************************
--*  Create a table to store the processed messages
--**************************************************
CREATE TABLE ProcessedMessages
(
	ID UNIQUEIDENTIFIER NOT NULL,
	MessageBody XML NOT NULL,
	ServiceName NVARCHAR(MAX) NOT NULL
)
GO

--*******************************************************************
--*  Send a response message back to the service "InitiatorService"
--*******************************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML
DECLARE @responsemessage XML;

BEGIN TRY
	BEGIN TRANSACTION
		WAITFOR (
			RECEIVE TOP (1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM TargetQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT > 0)
		BEGIN
			IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
			BEGIN
				-- Store the received request message in a table
				INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'TargetService')

				-- Construct the response message
				SET @responsemessage = '<HelloWorldResponse>' + @messagebody.value('/HelloWorldRequest[1]', 'NVARCHAR(MAX)') + '</HelloWorldResponse>';

				-- Send the response message back to the initiating service
				SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage] (@responsemessage);

				-- End the conversation on the target's side
				END CONVERSATION @ch;
			END
		END
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

--********************************************************************
--*  View the sent message on the queue "InitiatorQueue"
--********************************************************************
SELECT * FROM InitiatorQueue
GO

--********************************************************************
--*  View the processed message in the table "ProcessedMessages"
--********************************************************************
SELECT * FROM ProcessedMessages
GO

--*******************************************************************
--*  Service program for the service "InitiatorService"
--*******************************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML;

BEGIN TRY
	BEGIN TRANSACTION
		WAITFOR (
			RECEIVE TOP (1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM InitiatorQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT > 0)
		BEGIN
			IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/ResponseMessage')
			BEGIN
				-- Store the received response) message in a table
				INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'InitiatorService')
			END

			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
			BEGIN
				-- End the conversation on the initiator's side
				END CONVERSATION @ch;
			END
		END
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

--***********************************************************************************
--*  Endless receive loop for processing incoming messages on the initiator's queue
--***********************************************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML;

WHILE (1=1)
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION

		WAITFOR (
			RECEIVE TOP (1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM InitiatorQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT = 0)
		BEGIN
			ROLLBACK TRANSACTION
			BREAK
		END

		IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/ResponseMessage')
		BEGIN
			-- Store the received response) message in a table
			INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'InitiatorService')
		END

		IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
		BEGIN
			-- End the conversation on the initiator's side
			END CONVERSATION @ch;
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO

--********************************************************************************
--*  Endless receive loop for processing incoming messages on the target's queue
--********************************************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML
DECLARE @responsemessage XML;

WHILE (1=1)
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION

		WAITFOR (
			RECEIVE TOP (1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM TargetQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT = 0)
		BEGIN
			ROLLBACK TRANSACTION
			BREAK
		END

		IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
		BEGIN
			-- Store the received request message in a table
			INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'TargetService')

			-- Construct the response message
			SET @responsemessage = '<HelloWorldResponse>' + @messagebody.value('/HelloWorldRequest[1]', 'nvarchar(max)') + '</HelloWorldResponse>';

			-- Send the response message back to the initiating service
			SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage] (@responsemessage);

			-- End the conversation on the target's side
			END CONVERSATION @ch;
		END

		IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
		BEGIN
			-- End the conversation
			END CONVERSATION @ch;
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO

--**********************************************************
--*  Error handling with a savepoint on the target's queue
--**********************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML
DECLARE @responsemessage XML;

WHILE (1=1)
BEGIN
	BEGIN TRANSACTION

	WAITFOR (
		RECEIVE TOP (1)
			@ch = conversation_handle,
			@messagetypename = message_type_name,
			@messagebody = CAST(message_body AS XML)
		FROM TargetQueue
	), TIMEOUT 60000

	IF (@@ROWCOUNT = 0)
	BEGIN
		ROLLBACK TRANSACTION
		BREAK
	END

	SAVE TRANSACTION MessageReceivedSavepoint

	IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
	BEGIN
		BEGIN TRY
			-- Store the received request message in a table
			INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'TargetService')

			-- Construct the response message
			SET @responsemessage = '<HelloWorldResponse>' + @messagebody.value('/HelloWorldRequest[1]', 'nvarchar(max)') + '</HelloWorldResponse>';

			-- Send the response message back to the initiating service
			SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage] (@responsemessage);

			-- End the conversation on the target's side
			END CONVERSATION @ch;
		END TRY
		BEGIN CATCH
			IF (ERROR_NUMBER() = 1205)
			BEGIN
				-- A deadlock occurred.
				-- We can try it again...
				ROLLBACK TRANSACTION
				CONTINUE
			END
			ELSE
			BEGIN
				-- A other error occurred.
				-- The message can't be processed successfully, because it's a poison message
				ROLLBACK TRANSACTION MessageReceivedSavepoint
				PRINT 'Error occured: ' + CAST(@messagebody AS NVARCHAR(MAX))
			END
		END CATCH
	END

	IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
	BEGIN
		-- End the conversation
		END CONVERSATION @ch;
	END

	COMMIT TRANSACTION
END
GO

--**************************************
--*  Poison messages in Service Broker
--**************************************
-- Reactivating the queue
ALTER QUEUE TargetQueue WITH STATUS = ON
GO

-- Service program for handling the poison message
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML

WHILE (1=1)
BEGIN
	BEGIN TRANSACTION

	WAITFOR (
		RECEIVE TOP (1)
			@ch = conversation_handle,
			@messagetypename = message_type_name,
			@messagebody = CAST(message_body AS XML)
		FROM TargetQueue
	), TIMEOUT 60000

	IF (@@ROWCOUNT = 0)
	BEGIN
		ROLLBACK TRANSACTION
		BREAK
	END

	-- Rollling back the current transaction
	PRINT 'Rollback the current transaction - simulating a poison message...'
	ROLLBACK TRANSACTION
END
GO

--**********************************************************
--*  Setting up the event notification for poison messages
--**********************************************************
-- Create the queue which stores the event notification messages
CREATE QUEUE PoisonMessageNotifyQueue
GO

-- Create the service that accepts the event notification messages
CREATE SERVICE PoisonMessageNotifyService ON QUEUE PoisonMessageNotifyQueue
(
	[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO

-- Create the event notification itself
CREATE EVENT NOTIFICATION PoisonMessageNotification ON QUEUE TargetQueue
FOR Broker_Queue_Disabled
TO SERVICE 'PoisonMessageNotifyService', 'current database'
GO

-- Select the received event notification message
SELECT * FROM PoisonMessageNotifyQueue
GO

--**********************************************************
--*  End an conversation with an error
--**********************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML

BEGIN TRY
	BEGIN TRANSACTION
		WAITFOR (
			RECEIVE TOP (1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM TargetQueue
		), TIMEOUT 60000

		IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
		BEGIN
			-- End the conversation with an error
			END CONVERSATION @ch WITH ERROR = 4242 DESCRIPTION = 'My custom error message'
		END
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

-- Table that stores error information
CREATE TABLE ErrorLog
(
	ID UNIQUEIDENTIFIER NOT NULL,
	ErrorCode INT NOT NULL,
	ErrorMessage NVARCHAR(MAX) NOT NULL
)
GO

-- Service program for the InitiatorService, that handles also error message types
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML
DECLARE @errorcode INT
DECLARE @errormessage NVARCHAR(3000);

BEGIN TRY
	BEGIN TRANSACTION
		WAITFOR (
			RECEIVE TOP(1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM InitiatorQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT > 0)
		BEGIN
			IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/ResponseMessage')
			BEGIN
				-- Store the received response) message in a table
				INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'InitiatorService')
			END

			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
			BEGIN
				-- End the conversation on the initiator's side
				END CONVERSATION @ch;
			END

			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
			BEGIN
				-- Extract the error information from the sent message
				SET @errorcode = (SELECT @messagebody.value(
					N'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
					(/brokerns:Error/brokerns:Code)[1]', 'int'));
				SET @errormessage = (SELECT @messagebody.value(
					'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
					(/brokerns:Error/brokerns:Description)[1]', 'nvarchar(3000)'));

				-- Log the error
				INSERT INTO ErrorLog(ID, ErrorCode, ErrorMessage)
				VALUES (NEWID(), @errorcode, @errormessage)

				-- End the conversation on the initiator's side
				END CONVERSATION @ch;
			END
		END
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO
2013六月13

sql登录账号密码比对

sql server 评论关闭
--find SQL login with blank passwords
select name,type_desc,create_date from sys.sql_logins where pwdcompare('', password_hash) = 1

--find SQL login with password same as name
select name,type_desc,create_date from sys.sql_logins where pwdcompare(name, password_hash) = 1

--比对sa的密码是否为123abc
SELECT TOP 50 *
FROM sys.sql_logins sl
WHERE name = 'sa' AND pwdcompare('123abc', password_hash) = 1
2013六月2

nolock的替代方案-提交读快照隔离[行版本控制]

sql server 评论关闭
with(nolock)并意味着没有锁,实际上在查询一张表时,还是有锁,会对对象增加架构锁,
防止表会修改,会对数据库增加共享锁。若使用drop index,则要等到架构锁释放。

sql server2005提供了快照隔离和读取已提交快照这两种新的不加锁、无阻塞的事务隔离级别,可使用
快照:每次从数据进行修改时,会在teampdb上存储上一版本
好处:
select不要求锁,会大大降低整个库的锁负载量
nolock会读取到未提交事务时修改的数据,而读快照读取的是修改之前的数据,故nolock易读取到脏数据
读快照与nolock相同的地方在于都不加共享锁,但都会加对象架构锁与数据库的共享锁,区别在于,nolock需要
在每个sql语句后加,而读快照不用,并用读快照不会读到未提交事务的数据。
行版本控制:在任何一个修改之前,先对修改前的版本做一个复制 ,后续的一切读操作都会去读这个复制的版本,修改将创建一个新的版本。在这种处理方式下,读、写操作不会相互阻塞。使用这种行版本控制机制的好处,是程序的并发性比较高,但是缺点是用户读到的虽然不是一个脏数据,但是可能是个正在被修改马上就要过期的数据值
【注:加上行版本控制后,会最大限度降低死锁,但不是没有死锁】

相关存储过程:select * from sys.dm_tran_version_store

把SQL Server数据库事务隔离级别设置为已提交读快照隔离:
如果直接运行下面的语句:
ALTER Database [mydbname] SET READ_COMMITTED_SNAPSHOT ON
会可能被阻塞很长时间。
你可以选择运行下面的语句:
if(charindex('Microsoft SQL Server 2005',@@version) > 0)
begin
declare @sql varchar(8000)
select @sql = '
ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;'
Exec(@sql)
end
通过查询 sys.databases的 is_read_committed_snapshot_on字段
select is_read_committed_snapshot_on from sys.databases where name = DB_Name()
is_read_committed_snapshot_on如果为1表示目前为已提交读快照隔离级别

引用:
7 Things Developers Should Know About SQL Server
Using Read-Committed Snapshot Isolation - Mini-Lab
SQL Server已提交读快照隔离级别的设置
SQL Server 2005使用基于行版本控制的隔离级别初探