SQL Server 数据格式修改时,没有保存按钮的情况解决
如果你使用的是 SQL Server 2008, 当你修改数据结构后,保存时会报下图情况:
这是 SQL Server 2008 的一种自我保护,当你的修改可能导致数据表被删除并重新创建时(dropped and re-created),就会有这个提醒。
导致表被重新创建可能是以下几种情况:
- 在表中间添加一个新列;
- 删除列
- 更改列为 Null 性
- 更改列的顺序
- 更改列的数据类型
若要不使用这个保护,需要在“工具”菜单中单击“选项”,展开“设计器”,然后单击“表设计器和数据库设计器”。 清除“阻止保存要求重新创建表的更改”复选框。
参考资料:
Save (Not Permitted) Dialog Box
http://msdn.microsoft.com/en-us/library/bb895146.aspx
前一篇博客 IP to Integer 提供的 SQL 函数是 IP 转换成 Integer 的方法。 Integer 的最大值为: 2147483647(2^31 – 1)。按照 IP 转换成整数的算法,会算出来大于这个值的,所以之前提供的 IP 转换成 Integer 的方法会算出负值。
下面是 IP to BigInt 的转换算法, 这时候运算就不会小于零。
CREATE FUNCTION dbo.ipStringToBigInt
(
@ip CHAR(15)
)
RETURNS bigint
AS
BEGIN
DECLARE @rv bigint,
@o1 bigint,
@o2 INT,
@o3 INT,
@o4 INT
SELECT
@o1 = CONVERT(INT, PARSENAME(@ip, 4)),
@o2 = CONVERT(INT, PARSENAME(@ip, 3)),
@o3 = CONVERT(INT, PARSENAME(@ip, 2)),
@o4 = CONVERT(INT, PARSENAME(@ip, 1))
IF (@o1 BETWEEN 0 AND 255)
AND (@o2 BETWEEN 0 AND 255)
AND (@o3 BETWEEN 0 AND 255)
AND (@o4 BETWEEN 0 AND 255)
BEGIN
SET @rv = (@o1 * 16777216) +
(@o2 * 65536) +
(@o3 * 256) +
(@o4)
END
ELSE
SET @rv = -1
RETURN @rv
END
go
CREATE FUNCTION dbo.ipBigIntToString
(
@ip bigint
)
RETURNS CHAR(15)
AS
BEGIN
DECLARE @o1 INT,
@o2 INT,
@o3 INT,
@o4 INT
IF @ip > 4294967295 RETURN '255.255.255.255'
IF @ip <= 0 RETURN '0.0.0.0'
SET @o1 = @ip / 16777216
SET @ip = @ip % 16777216
SET @o2 = @ip / 65536
SET @ip = @ip % 65536
SET @o3 = @ip / 256
SET @ip = @ip % 256
SET @o4 = @ip
RETURN
CONVERT(VARCHAR(4), @o1) + '.' +
CONVERT(VARCHAR(4), @o2) + '.' +
CONVERT(VARCHAR(4), @o3) + '.' +
CONVERT(VARCHAR(4), @o4)
END
go
-- 调用例子
select dbo.ipBigIntToString(3708279131)
select dbo.ipStringToBigInt('221.7.217.91')
有时候我们要判断某个IP是否在某个段内,如何实现呢? 一个简单办法就是把IP转换成Integer,然后判断整数是否在某个范围内就可以实现这个判断。
转换的算法如下:
比如我们要转换的IP为: 058.062.042.000
First Octet: 058
Second Octet: 062
Third Octet: 042
Fourth Octet: 000
计算公式如下:
(first octet * 256³) + (second octet * 256²) + (third octet * 256) + (fourth octet)
= (first octet * 16777216) + (second octet * 65536) + (third octet * 256) + (fourth octet)
= (058 * 16777216) + (062 * 65536) + (042 * 256) + (000)
= 977152512
网上有现成的服务,比如下面这个地址:
http://www.aboutmyip.com/AboutMyXApp/IP2Integer.jsp
SQL Server 的一个例子如下:
CREATE FUNCTION dbo.ipStringToInt
(
@ip CHAR(15)
)
RETURNS INT
AS
BEGIN
DECLARE @rv INT,
@o1 INT,
@o2 INT,
@o3 INT,
@o4 INT,
@base INT
SELECT
@o1 = CONVERT(INT, PARSENAME(@ip, 4)),
@o2 = CONVERT(INT, PARSENAME(@ip, 3)),
@o3 = CONVERT(INT, PARSENAME(@ip, 2)),
@o4 = CONVERT(INT, PARSENAME(@ip, 1))
IF (@o1 BETWEEN 0 AND 255)
AND (@o2 BETWEEN 0 AND 255)
AND (@o3 BETWEEN 0 AND 255)
AND (@o4 BETWEEN 0 AND 255)
BEGIN
SELECT @base = CASE
WHEN @o1 < 128 THEN
(@o1 * 16777216)
ELSE
-(256 - @o1) * 16777216
END
SET @rv = @base +
(@o2 * 65536) +
(@o3 * 256) +
(@o4)
END
ELSE
SET @rv = -1
RETURN @rv
END
go
调用范例:
select dbo.ipStringToInt('058.062.042.000')
整数转换如下:
CREATE FUNCTION dbo.ipIntToString
(
@ip INT
)
RETURNS CHAR(15)
AS
BEGIN
DECLARE @o1 INT,
@o2 INT,
@o3 INT,
@o4 INT
IF ABS(@ip) > 2147483647
RETURN '255.255.255.255'
SET @o1 = @ip / 16777216
IF @o1 = 0
SELECT @o1 = 255, @ip = @ip + 16777216
ELSE IF @o1 < 0
BEGIN
IF @ip % 16777216 = 0
SET @o1 = @o1 + 256
ELSE
BEGIN
SET @o1 = @o1 + 255
IF @o1 = 128
SET @ip = @ip + 2147483648
ELSE
SET @ip = @ip + (16777216 * (256 - @o1))
END
END
ELSE
BEGIN
SET @ip = @ip - (16777216 * @o1)
END
SET @ip = @ip % 16777216
SET @o2 = @ip / 65536
SET @ip = @ip % 65536
SET @o3 = @ip / 256
SET @ip = @ip % 256
SET @o4 = @ip
RETURN
CONVERT(VARCHAR(4), @o1) + '.' +
CONVERT(VARCHAR(4), @o2) + '.' +
CONVERT(VARCHAR(4), @o3) + '.' +
CONVERT(VARCHAR(4), @o4)
END
go
调用范例:
select dbo.ipIntToString(977152512)
C# 的例子如下:
using System;
using System.Net;
namespace ConsoleApplication1
{
class Program
{
static long ToInt(string addr)
{
return BitConverter.ToInt32(IPAddress.Parse(addr).GetAddressBytes(), 0);
}
static string ToAddr(long address)
{
return IPAddress.Parse(address.ToString()).ToString();
// This also works:
// return new IPAddress((uint) IPAddress.HostToNetworkOrder(
// (int) address)).ToString();
}
static void Main(string[] args)
{
Console.WriteLine(ToInt("64.233.187.99"));
Console.WriteLine(ToAddr(1089059683));
Console.ReadLine();
}
}
}
参考资料:
How should I store an IP address in SQL Server?
http://sqlserver2000.databases.aspfaq.com/how-should-i-store-an-ip-address-in-sql-server.html
How to convert an IPv4 address into a integer in C#?
http://stackoverflow.com/questions/461742/how-to-convert-an-ipv4-address-into-a-integer-in-c
今天在处理一个用户名数据库时,发现有些不正常的数据存在,按照逻辑,用户名只能是数字,字母,下划线和纯中文这样的字符组合存在,不应该有其他组合存在,但是发现数据库中由于各种历史原因,有些不正常的存在,如何找到这些异常数据,在CSDN的 SQL Server 版问了这样两个问题,如下:
http://topic.csdn.net/u/20100111/14/529a21a1-3ea8-4263-a0d9-34e83be79b1d.html
http://topic.csdn.net/u/20100111/15/c2a124c5-bc5b-4626-86ce-c5b862cf5cff.html
感谢CSDN的网友帮忙解决了这个问题,下面就是解决方法的汇总:
if object_id('[t1]') is not null
drop table [t1]
create table [t1]([c] nvarchar(20))
insert [t1]
select 'aaa' union all -- 此数据不应该被搜索到
select 'bcds' union all -- 此数据不应该被搜索到
select 'a1' union all -- 此数据不应该被搜索到
select '啊' union all -- 此数据不应该被搜索到
select '^%' union all -- 应该搜索到
select 'ew1' union all -- 此数据不应该被搜索到
select '344' union all -- 此数据不应该被搜索到
select '__' union all -- 此数据不应该被搜索到
select '213_21' union all -- 此数据不应该被搜索到
select 'a_2' union all -- 此数据不应该被搜索到
select 'd' union all -- 此数据不应该被搜索到
select 'ddd' union all -- 此数据不应该被搜索到
select '电风扇' union all -- 此数据不应该被搜索到
select '★思寒★' union all -- 应该搜索到
select 'Ω' union all -- 应该搜索到
select 'トントン' union all -- 应该搜索到
select '***' union all -- 应该搜索到
select '///' union all -- 应该搜索到
select '@-@' union all -- 应该搜索到
select '@小慧' union all -- 应该搜索到
select '~*晓菊*~' union all -- 应该搜索到
select '啊★洛' union all -- 应该搜索到
select '不思議の夜' union all -- 应该搜索到
select '(嘉宾)胡飞' union all -- 应该搜索到
select '--------------' -- 应该搜索到
select * from [t1] WHERE PATINDEX('%[0-9a-z_]%',c)=0
and PATINDEX('%[^吖-座]%',c) <> 0
SQL Server 的 ltrim 和 rtrim 函数只会取消 char(32) 的字符, char(9) 之类的字符则不会剔除。今天在处理论坛的一个bug时,发现数据多了空格,就是char(9)在作怪。
一个演示SQL代码:
declare @s nvarchar(50)
select @s = char(32)+char(9)+'*'
print('%'+@s+'%')
select @s = ltrim(rtrim(@s))
print('%'+@s+'%')
print (str(len(@s)))
而 C# 中则没有类似问题,它剔除了很多非 char(32) 的空格。
演示代码:
static void Main(string[] args)
{
string s = string.Format("{0}{1}*", (char)32, (char)9);
Console.WriteLine("%" + s + "%");
Console.WriteLine(s.Length);
s = s.Trim();
Console.WriteLine("%" + s + "%");
Console.WriteLine(s.Length);
Console.ReadLine();
}
MSDN上一些空格的资料:
下表列出了被 Trim 方法移除的空白字符。第一列列出了字符的 Unicode 名称,第二列列出了标识该字符的 Unicode 码位的十六进制表示法。
(请注意,尽管传递特定字符时静态 Char.IsWhiteSpace(Char) 方法返回了 true,但该字符不一定被 Trim 方法移除。(作者注:MSDN上这句话很让我困惑,下面2个表中,Char.IsWhiteSpace中的空格都出现在Trim 中的空格了呀?))
Unicode 名称 | Unicode 码位 |
CHARACTER TABULATION | U+0009 |
LINE FEED | U+000A |
LINE TABULATION | U+000B |
FORM FEED | U+000C |
CARRIAGE RETURN | U+000D |
SPACE | U+0020 |
NEXT LINE | U+0085 |
NO-BREAK SPACE | U+00A0 |
OGHAM SPACE MARK | U+1680 |
EN QUAD | U+2000 |
EM QUAD | U+2001 |
EN SPACE | U+2002 |
EM SPACE | U+2003 |
THREE-PER-EM SPACE | U+2004 |
FOUR-PER-EM SPACE | U+2005 |
SIX-PER-EM SPACE | U+2006 |
FIGURE SPACE | U+2007 |
PUNCTUATION SPACE | U+2008 |
THIN SPACE | U+2009 |
HAIR SPACE | U+200A |
ZERO WIDTH SPACE | U+200B |
LINE SEPARATOR | U+2028 |
PARAGRAPH SEPARATOR | U+2029 |
IDEOGRAPHIC SPACE | U+3000 |
ZERO WIDTH NO-BREAK SPACE | U+FEFF |
会被 Char.IsWhiteSpace( 认为是空白字符的包括以下Unicode 字符:
- SpaceSeparator 类别的成员,该类别包括 SPACE 字符 (U+0020)。
- LineSeparator 类别的成员,该类别只包括 LINE SEPARATOR 字符 (U+2028)。
- ParagraphSeparator 类别的成员,该类别只包括 PARAGRAPH SEPARATOR 字符 (U+2029)。
- 字符 CHARACTER TABULATION (U+0009)、LINE FEED (U+000A)、LINE TABULATION (U+000B)、FORM FEED (U+000C)、CARRIAGE RETURN (U+000D)、NEXT LINE (U+0085) 和 NO-BREAK SPACE (U+0000A0)。
System.Data.SqlClient 命名空间下有个类: SqlConnectionStringBuilder ,可以帮助我们维护链接字符串。 这个类对我们下面的需求场景很有帮助。
对于同一个数据库,我们可能有正常和比较耗时的统计工作两种需求,为何互相不干扰,我们一般可以配置2个数据库链接字符串,类似如下的配置:
Persist Security Info=False;Integrated Security=true;Initial Catalog=Northwind;server=(local);
Persist Security Info=False;Integrated Security=true;Initial Catalog=Northwind;server=(local);Connect Timeout=0;
注: 在没有设置 Connect Timeout 时, Connect Timeout 默认为15秒,如果 Connect Timeout 设置为0,则表示永远不超时。这个设置是以秒为单位的。
其实我们完全可以只配置一个数据库链接字符串,然后使用 SqlConnectionStringBuilder 类来帮助我们实现不同 Connect Timeout 的需求。
演示代码如下:
// connString 原汁原味的配置的链接字符串
SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder(connString);
// 不论之前是否设置了Connect Timeout,这里都强制设置为用不超时
connStr.ConnectTimeout = 0;
// 使用新组合后的链接字符串
SqlConnection conn = new SqlConnection(connStr.ConnectionString);
参考资料:
SqlConnectionStringBuilder 类
http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlconnectionstringbuilder(VS.80).aspx
设置具体数据库启动Service Broker服务,如下图:
我这里试例数据库的名字为“ghj_Demo”,修改 Broker Enabled 属性为 true。
你也可以用SQL 语句来修改,修改的SQL语句如下:
ALTER DATABASE ghj_Demo SET ENABLE_BROKER
确保你将使用的数据库帐户具有必需的权限
你在后面连接这个数据库的帐户,要确保对这个数据库具有 SUBSCRIBE、 QUERY 、NOTIFICATIONS 的权限。
下面就是一个简单的代码例子,来演示查询通知。这里用到一个我自己建立的表:
这个表结构如下:
CREATE TABLE [dbo].[User]( [UserName] [nvarchar](20) NOT NULL, [Email] [nvarchar](50) NULL ) ON [PRIMARY] GO
演示的控制台代码如下:
using System;
using System.Data;
using System.Data.SqlClient;
namespace Demo
{
class Program
{
public static string connectionstring = "Data Source=.;Initial Catalog=ghj_Demo;Integrated Security=True";
public void DoDependency()
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
// sql is a local procedure that returns
// a paramaterized SQL string. You might want
// to use a stored procedure in your application.
string sql = "select [UserName] ,[Email] from dbo.[User]";
SqlCommand cmd = new SqlCommand(sql, conn);
// Ensure the command object does not have a notification object.
cmd.Notification = null;
//Notification specific code
SqlDependency dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(TestEvent);
conn.Open();
SqlDataReader r = cmd.ExecuteReader();
//Read the data here and close the reader
r.Close();
conn.Close();
Console.WriteLine("DataReader Read...");
}
}
void TestEvent(Object o, SqlNotificationEventArgs args)
{
// 注意:
// 如果Server端在很短的时间内发生了大量的改动(比如用了一个循环Update了好多行),
// OnChanged必须能迅速处理事件,否则它只会被触发一次。这个不是缺陷,
// 因为一般OnChanged事件处理函数内都要执行类似刷新缓存的操作,它只触发一次,
// 不会影响程序逻辑,却能提高程序性能。
Console.WriteLine("======================");
Console.WriteLine("Event Recd");
Console.WriteLine("Info:" + args.Info);
Console.WriteLine("Source:" + args.Source);
Console.WriteLine("Type:" + args.Type);
}
static void Main(string[] args)
{
// In order to use the callback feature of the
// SqlDependency, the application must have
// the SqlClientPermission permission.
try
{
SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
perm.Demand();
}
catch
{
throw new ApplicationException("No permission");
}
try
{
SqlDependency.Stop(connectionstring);
//Start the listener infrastructure on the client
SqlDependency.Start(connectionstring);
Program q = new Program();
q.DoDependency();
Console.WriteLine("Wait for Notification Event...");
Console.Read();
}
finally
{
//Optional step to clean up dependency else it will fallback to automatic cleanup
SqlDependency.Stop(connectionstring);
}
}
}
}
一些注意事项:摘自MSDN文档
使用查询通知功能的应用程序需要考虑下列特殊注意事项。
注意事项 | 说明 |
SQL Server 的服务帐户 | 对于使用本地系统帐户作为服务帐户的 SQL Server 实例,应用程序不会从其接收通知。 |
接收通知 | 无法在运行 Windows 95 或 Windows 98 的计算机上接收通知。 |
查询通知和事务 | 如果在某一事务内进行了多项影响具有已注册通知请求的一组数据的修改,则仅会发送单个通知事件。 |
快速更新和查询通知 | 使用查询通知的应用程序必须考虑到立即出现通知的情况。服务器上的数据更改时,通知消息将发送到相应的服务中介程序队列。 应用程序需要注册才能接收其他通知。 因此,如果多个应用程序快速更新某个数据集,应用程序在缓存刷新后,立即可以接收通知,检索数据,然后获取另一个更新通知。 编写使用查询通知的应用程序时必须考虑到此情况。 如果应用程序使用不断更新的数据,则可能更适合使用另一种数据缓存策略。 |
设置选项设置 | 在通知请求下执行 SELECT 语句时,提交请求的连接必须设置以下选项: · ANSI_NULLS ON · ANSI_PADDING ON · ANSI_WARNINGS ON · CONCAT_NULL_YIELDS_NULL ON · QUOTED_IDENTIFIER ON · NUMERIC_ROUNDABORT OFF · ARITHABORT ON |
编写通知查询语句的约束
您可以为 SELECT 和 EXECUTE 语句设置通知。 使用 EXECUTE 语句时,SQL Server 会为执行的命令而不是 EXECUTE 语句本身注册通知。 该命令必须满足 SELECT 语句的要求和限制。 当注册通知的命令包含多个语句时,数据库引擎会为批处理中的每个语句创建一个通知。
对满足以下要求的 SELECT 语句支持查询通知:
-
SELECT 语句中的提取的列必须显式声明,且表名称必须用由两部分组成的名称进行限定。 请注意,这意味着语句中引用的所有表都必须位于同一个数据库中。
-
语句不能使用星号 (*) 或 table_name.* 语法来指定列。
-
语句不能使用未命名的列或重复的列名称。
-
语句必须引用一个基表。
-
SELECT 语句中的提取的列不能包含聚合表达式,除非该语句使用 GROUP BY 表达式。 在提供 GROUP BY 表达式的情况下,选择列表可以包含聚合函数 COUNT_BIG() 或 SUM()。 不过,不能为可以为 null 的列指定 SUM()。 语句不能指定 HAVING、CUBE 或 ROLLUP。
-
SELECT 语句中的提取的列用作简单表达式时不能出现多次。
-
语句不能包含 PIVOT 或 UNPIVOT 运算符。
-
语句不能包含 INTERSECT 或 EXCEPT 运算符。
-
语句不能引用视图。
-
语句不能包含以下任一项: DISTINCT、COMPUTE、COMPUTE BY 或 INTO。
-
语句不能引用服务器全局变量 (@@variable_name)。
-
语句不能引用派生表、临时表或表变量。
-
语句不能引用其他数据库或服务器中的表或视图。
-
语句不能包含子查询、外部联接或自联接。
-
语句不能引用大型对象类型: text、ntext 和 image。
-
语句不能使用 CONTAINS 或 FREETEXT 全文谓词。
-
语句不能使用行集合函数,包括 OPENQUERY 和 OPENROWSET。
-
语句不能使用以下任一集合函数: AVG、COUNT(*)、MAX、MIN、STDEV、STDEVP、VAR 或 VARP。
-
语句不能使用任何不确定性函数,包括排名和开窗函数。
-
语句不能包含用户定义的聚合。
-
语句不能引用系统表或视图,包括目录视图和动态管理视图。
-
语句不能包含 FOR BROWSE 信息。
-
语句不能引用队列。
-
语句不能包含无法更改或无法返回结果的条件语句(例如 WHERE 1=0)。
sunmast 对查询通知的注意事情也有很多有价值的整理:
使用SQL Server 2005 Query Notification的几个注意事项
http://blog.joycode.com/sunmast/archive/2006/08/11/sql_2005_query_notification_comments_79814.aspx
参考资料:
剖析SQL Server 2005查询通知之基础
http://www.allwiki.com/wiki/%E5%89%96%E6%9E%90SQL_Server_2005%E6%9F%A5%E8%AF%A2%E9%80%9A%E7%9F%A5%E4%B9%8B%E5%9F%BA%E7%A1%80
Using SqlDependency for data change events
http://www.codeproject.com/KB/database/chatter.aspx
SQL Server 2005 Service Broker 初探
http://msdn.microsoft.com/zh-cn/library/ms345108.aspx
SQL Server 2005数据库开发详解
http://book.csdn.net/bookfiles/24/10024713.shtml
C# Windows 应用程序中实现 SQL Server 2005 查询通知
http://support.microsoft.com/kb/555893/zh-cn
SqlDependency changes for RTM [Sushil Chordia]
http://blogs.msdn.com/dataaccess/archive/2005/09/27/474447.aspx
.NET 2.0 SqlDependency快速上手指南
http://www.cnblogs.com/Xrinehart/archive/2006/07/27/461106.html
在 Windows 应用程序中使用 SqlDependency
http://msdn.microsoft.com/zh-cn/library/a52dhwx7(VS.80).aspx
Using SqlDependency in an ASP.NET Application
http://msdn.microsoft.com/en-us/library/9dz445ks(VS.80).aspx
Minimum Database Permissions Required for SqlDependency
http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx
使用SQL Server 2005 Query Notification的几个注意事项
http://blog.joycode.com/sunmast/archive/2006/08/11/sql_2005_query_notification_comments_79814.aspx
information_schema.routines与sysobjects
在建立存储过程前,我习惯于先检查存储过程是否存在,如果存在就建立,然后再创建。
这个检查的过程,现在有2种习惯写法,如下:
if exists (
select * from information_schema.routines where specific_name = 'WorkOrdersForBlade' and specific_schema = 'dbo')
begin
drop procedure dbo.workordersforblade
end
go
或者
if exists (
select * from sysobjects where type = 'p' and name = 'WorkOrdersForBlade')
begin
drop procedure dbo.workordersforblade
end
go
information_schema.routines 是SQL Server 2000开始新加的系统视图,它是以 sysobjects 和 syscolumns 系统表为基础建立的系统视图。它的字段更具备可读性。
用上面那个写法都没有问题。 在SQL Server 2005 以及 2008 的默认模板中,使用的是第一种写法。
显然,我们最好用经过整合后,更具备可读性的视图 information_schema.routines 。
参考资料:
SQL Server 2008 联机丛书 对routines 视图的介绍
http://msdn.microsoft.com/zh-cn/library/ms188757.aspx
http://msdn.microsoft.com/en-us/library/ms188757.aspx
数据库中User和Schema的关系
http://blog.csdn.net/yanjiangbo/archive/2007/09/12/1782576.aspx
ROUTINES
http://www.yesky.com/imagesnew/software/tsql/ts_ia-iz_3kq1.htm