如果您对SQLServerHostingToolkit感兴趣，那么这篇文章一定是您不可错过的。我们将详细讲解SQLServerHostingToolkit的各种细节，此外还有关于asp.net–版本1
如果您对SQL Server Hosting Toolkit感兴趣，那么这篇文章一定是您不可错过的。我们将详细讲解SQL Server Hosting Toolkit的各种细节，此外还有关于asp.net – 版本16.1.1.0中不提供AjaxControlToolkit.ToolkitScriptManager、Auditing SQL Server SQL Statements、Centos6 下 Mysql启动提示 Starting MySQL. ERROR! The server quit without updating PID file、create sqlserver login user and add it to certain db user (sqlserver authentication)的实用技巧。
本文目录一览：- SQL Server Hosting Toolkit
- asp.net – 版本16.1.1.0中不提供AjaxControlToolkit.ToolkitScriptManager
- Auditing SQL Server SQL Statements
- Centos6 下 Mysql启动提示 Starting MySQL. ERROR! The server quit without updating PID file
- create sqlserver login user and add it to certain db user (sqlserver authentication)
SQL Server Hosting Toolkit
The Database Publishing Wizard enables the deployment of sql Server 2005 databases (both schema and data) into a shared hosting environment on either a sql Server 2000 or 2005 server.
The tool supports two modes of deployment:
- It generates a single sql script file which can be used to recreate a database when the only connectivity to a server is through a web-based control panel with a script execution window.
- It connects to a web service provided by your hoster and directly creates objects on a specified hosted database
The Database Publishing Wizard provide both a graphical and a command-line interface. In addition, it can integrate directly into Visual Studio 2005 or Visual Web Developer 2005.
http://www.cnblogs.com/MichaelPeng/archive/2009/04/25/1443282.html
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard
asp.net – 版本16.1.1.0中不提供AjaxControlToolkit.ToolkitScriptManager
我在使用MaskedEditExtender时遇到了退格按钮的问题,这就是我将AjaxControlToolkit从v 4.1.60919.0升级到最新版本n 16.1.1.0的原因.但是现在我面临一个新的错误,我们正在实现toolkitscriptmanager但是现在我找不到它,即使在解决方案中也没有ToolkitScriptManager也没有调用ScriptManager.所以我该怎么做？
要理解我的意思：
要理解我的意思：
在web.config中我将AjaxControlToolkit组装到cc1
<add tagPrefix="cc1" assembly="AjaxControlToolkit" namespace="AjaxControlToolkit"/>
每当我在.aspx页面中使用cc1标签时,元素ToolkitScriptManager在新版本中无效.我该如何解决这个问题？
解决方法
这是有意的.
ToolkitScriptManager was removed in v15.1.请按照
the upgrade guide解决此问题.
Auditing SQL Server SQL Statements
“谁把我的表给删拉”，”谁删了整个表阿”…碰到这种棘手的情况，你如果没有预先做好准备，真的是头都要急炸了。那怎么能抓出这个”凶手”呢？SQL Trace, SQL Profile，SQL Trigger，Extended Events等着伺候你呢，更别说CLR，Service Broker等重量级武器了
“谁把我的表给删拉”，”谁删了整个表阿”…碰到这种棘手的情况，你如果没有预先做好准备，真的是头都要急炸了。那怎么能抓出这个”凶手”呢？sql trace, sql profile，sql trigger，extended events等着伺候你呢，更别说clr，service broker等重量级武器了，别急！
工具虽好，但是也得用得贴合场景才能发挥作用，要不然跟你的SQL Server抢IO，那就得不偿失了。比如高频的OLTP场合，你还用TRIGGER把大量的DML语句都写到当前数据库去，那不是给数据库增加一倍工作量么。当然控制好权限，做好测试也能保证你的数据安全。这不妨碍我们讨论auditing SQL。
SQL Profiler是基于SQL Trace的，而SQL Trace是会被更高版本的SQL Server给逐渐摒弃的，所以我们就只讨论一个就可以了。一开始我知道 SQL Profiler是可以可视化监控即时的SQL Server活动的，但是缺点是不能保存或者自动执行，需要人工干预。经过研究，它是可以自动保存截取结果的。
让我们认识下“源”：想要抓“破坏分子”，首先要知道“破环分子”的标示有哪些，漏抓，错抓，都是失手的表现。访问SQL SERVER的方式有很多种，有ADO.NET, JDBC, SQL SERVER Management Studio, ODBC等各种方法，也有 Ad-hoc SQL, Stored Procedure等表现形式，是否每一种的方式都有各自不同的格式呢？最好的方法就是针对每种方式都来做个例子验证下。
首先最简单的SSMS方式，我们将SQL Profiler限制到某一个数据库，lenistest4。 在SSMS里面输入：
<span>use</span> lenistest4 go
登录后复制
SQL Profiler显示的是SQL:BatchCompleted。
这里与有没有go无关。当你选中一块SQL区域并执行，其中如果有go那就有关系了，比如：
<span><span>select</span> top <span>10</span> * <span>from</span> sys.tables <span>go</span> <span>select</span> top <span>10</span> * <span>from</span> dbo.region <span>go</span></span>
登录后复制
这里一个go分割了一个batch。最后一个go没有意义，我们提交了一段代码，这段代码相当于是一个大batch，如果中间有go，那这个go 就代表了一个子batch。
那么用动态语句，会有什么Trace格式呢：
<span>declare</span> <span>@<span>sqlstatement</span> <span>nvarchar(max)</span> = N<span>''select top 10 * from dbo.region''</span> </span>; <span>exec</span> <span>sp_executesql</span> <span>@<span>sqlstatement</span></span>
登录后复制
这里有SQL:Batch*, SQL:Stmt*,SP:Stmt， SQL:Stmt*代表的是一系列的SQL命令，比如declare, set等；SP:Stmt*代表了stored procedure的范围内语句。
<span>declare</span> @sqlstatement nvarchar(max) = N<span>''</span> <span>declare</span> @regionName varchar(<span>20</span>) = <span>''''China''''</span> <span>select</span> top <span>10</span> * <span>from</span> dbo.region <span>where</span> regionName = @regionName<span>'' ;</span> exec sp_executesql @sqlstatement
登录后复制
上面这段SQL，验证了SQL命令select和declare是不是都被看作是SP:Stmt* ? 其实在一个batch里面，SQL命令里面DML语句也是被当作Statement来处理的。
如果在动态SQL里面有go是不是也会有SP:Batch*? 我们接着往下看：
<span>declare</span> @sqlstatement nvarchar(max) = N<span>''</span> <span>declare</span> @regionName varchar(<span>20</span>) = <span>''''China''''</span> <span>select</span> top <span>10</span> * <span>from</span> dbo.region <span>where</span> regionName = @regionName; go <span>declare</span> @regionName2 varchar(<span>20</span>) = <span>''''England''''</span> <span>select</span> top <span>10</span> * <span>from</span> dbo.region <span>where</span> regionName = @regionName2 <span>'' ;</span> exec sp_executesql @sqlstatement
登录后复制
Msg 102, Level 15, State 1, Line 12 Incorrect syntax near ‘go’.
可以看到go是不能用在动态语句里面的。
为了验证SP:Stmt*是不是指的是存储过程里面的语句，我们先创建一个stored procedure,然后再执行它：
<span><span>create</span> <span>procedure</span> dbo.getRegionName <span>as</span> <span>begin</span> <span>declare</span> @regionName <span>varchar</span>(<span>20</span>) = <span>''China''</span> <span>select</span> top <span>10</span> * <span>from</span> dbo.region <span>where</span> regionName = @regionName <span>declare</span> @regionNamex <span>varchar</span>(<span>20</span>) = <span>''England''</span> <span>select</span> top <span>10</span> * <span>from</span> dbo.region <span>where</span> regionName = @regionNamex <span>end</span> <span>exec</span> dbo.getRegionName</span>
登录后复制
正是如此 ！综上所述， SQL:Batch* ， 这里的batch相当于是个scope，一个大的执行空间，里面的所有 SQL 语句都是statement，包括DML，DDL等一系列 T-SQL语句 ；而SP:Stmt*又是存储过程的执行空间，里面所有的 T-SQL语句都是statement。
下面看段c#调用这个stored procedure，看看trace是如何识别的：
<span>using</span> System;
<span>using</span> System.Collections.Generic;
<span>using</span> System.Linq;
<span>using</span> System.Text;
<span>using</span> System.Threading.Tasks;
<span>using</span> System.Data.SqlClient;
<span>using</span> System.Data.SqlTypes;
<span>using</span> System.Data;
<span><span>namespace</span> <span>AccessLenistest4DB</span>
{</span>
<span><span>class</span> <span>Program</span>
{</span>
<span>static</span> <span>void</span> Main(<span>string</span>[] args)
{
SqlConnection<span> ICONN </span>= <span>new</span> SqlConnection(@<span>"data source = (localhost);initial catalog = lenistest4 ;user id = sa; password = sas;"</span>);
SqlCommand icmd = <span>new</span> SqlCommand();
icmd.Connection = ICONN;
icmd.CommandType = System.Data.CommandType.StoredProcedure;
icmd.CommandText = <span>"dbo.getRegionName"</span>;
SqlDataAdapter ida = <span>new</span> SqlDataAdapter(icmd);
DataSet localds = <span>new</span> DataSet();
<span>try</span>
{
ICONN.Open();
ida.Fill(localds);
}
<span>catch</span>(SqlException se)
{
Console.Write(se.ToString());
}
}
}
}登录后复制
这儿多了个RPC， remote procedure call。其他都一样，所以RPC可以看作是一种命名空间，用阿里区别访问协议。这里要区别的是我们调用的是stored procedure，所以会RPC。所以如果我们是用纯SQL来访问数据库，那会不会有 RPC标示呢：
<span>using</span> System;
<span>using</span> System.Collections.Generic;
<span>using</span> System.Linq;
<span>using</span> System.Text;
<span>using</span> System.Threading.Tasks;
<span>using</span> System.Data.SqlClient;
<span>using</span> System.Data.SqlTypes;
<span>using</span> System.Data;
<span><span>namespace</span> <span>AccessLenistest4DB</span>
{</span>
<span><span>class</span> <span>Program</span>
{</span>
<span>static</span> <span>void</span> Main(<span>string</span>[] args)
{
SqlConnection<span> ICONN </span>= <span>new</span> SqlConnection(@<span>"data source = (localhost);initial catalog = lenistest4 ;user id = sa; password = sas;"</span>);
SqlCommand icmd = <span>new</span> SqlCommand();
icmd.Connection = ICONN;
icmd.CommandType = System.Data.CommandType.Text;
icmd.CommandText = <span>"select * from dbo.region"</span>;
SqlDataAdapter ida = <span>new</span> SqlDataAdapter(icmd);
DataSet localds = <span>new</span> DataSet();
<span>try</span>
{
ICONN.Open();
ida.Fill(localds);
}
<span>catch</span>(SqlException se)
{
Console.Write(se.ToString());
}
}
}
}登录后复制
并没有RPC。 事实证明 RPC只出现在客户端语言调用存储过程的例子。
Trace的手段 ：“破坏分子”的特征被识别了，接下来就是怎么去抓捕的手段问题了。可以有即时的GUI工具，比如SQL Profiler，Extended Event(SSMS自带)，也可以用脚本去抓，并放在特定存储里面供稍后分析使用。
SQL Profiler 是即时的GUI工具很好用，可以保存结果，也可以自定义模板，缺点在于你必须开一个额外的窗口去跟踪，有时候数据量太大，还会影响传输，对多太SQL Server做监控就不怎么容易了，一个一个手工去开窗口，是不是很麻烦 ?
这里有Extended Events可以帮我们用脚本的形式去捕捉这些T-SQL语句，这里有个简单的例子：
- 通过 Extended Events ，我们可以监控一段时间内的的 SQL Completed 情况，简要介绍下：
1.1 Extended Events 概念：由一系列自动触发的 event 产生性能数据，经过 event engine 的收集，存放到指定的输出文件，以供后续的分析。
1.2 XE 涉及到的动态管理试图 : sys.dm_xe_packages; sys.dm_xe_objects;sys.dm_xe_sessions
1.3 基本用法:
2.3.1 创建一个 Event Session
<span><span>create</span> event <span>session</span> capture_sql_events <span>on</span> server <span>add</span> event sqlserver.sql_statement_completed ( <span>action</span>(sqlserver.sql_text) ) <span>add</span> target package0.event_file ( <span>set</span> filename = <span>''E:\data_bu\capture_sql_events.xel''</span>, metadatafile = <span>''E:\data_bu\capture_sql_event.xem''</span> ) <span>go</span></span>
登录后复制
2.3.2 启用这个 Event session 来收集数据
<span><span>alter</span> event <span>session</span> capture_sql_events <span>on</span> server STATE = <span>start</span> <span>go</span></span>
登录后复制
2.3.3 停用这个 Event session
<span><span>alter</span> event <span>session</span> capture_sql_events <span>on</span> server state = stop <span>go</span></span>
登录后复制
2.3.4 修改一个 session 来增加或者删除对 Event 的监控
<span><span>alter</span> event <span>session</span> capture_sql_events <span>on</span> server <span>add</span> event sqlserver.sql_batch_completed (<span>action</span>(sqlserver.sql_text)) <span>go</span></span>
登录后复制
2.3.5 查看正在运行的 Event Session
<span>select</span> * <span>from</span> sys.dm_xe_sessions <span>select</span> * <span>from</span> sys.dm_xe_session_events
登录后复制
2.3.6 查看收集到的统计数据
<span><span>select</span> top <span>10</span> * <span>from</span> dbo.region <span>go</span> <span>select</span> * , <span>cast</span>(event_data <span>as</span> xml) <span>as</span> event_data_xml <span>from</span> sys.fn_xe_file_target_read_file(<span>''E:\data_bu\capture_sql_events*.xel''</span>,<span>null</span>,<span>null</span>,<span>null</span>) <span>where</span> event_data <span>like</span> N<span>''%region%''</span></span>
登录后复制
从结果集我们可以看到，sql_statement_completed这个Event抓到的结果中，包含了Action中我们指定的内容，还包含了其他的一些统计信息：
<span><<span>event</span> <span>name</span>=<span>"sql_statement_completed"</span> <span>package</span>=<span>"sqlserver"</span> <span>timestamp</span>=<span>"2016-05-06T03:29:48.868Z"</span>></span> <span><<span>data</span> <span>name</span>=<span>"duration"</span>></span> <span><<span>value</span>></span>0<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"cpu_time"</span>></span> <span><<span>value</span>></span>0<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"physical_reads"</span>></span> <span><<span>value</span>></span>0<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"logical_reads"</span>></span> <span><<span>value</span>></span>2<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"writes"</span>></span> <span><<span>value</span>></span>0<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"row_count"</span>></span> <span><<span>value</span>></span>6<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"last_row_count"</span>></span> <span><<span>value</span>></span>6<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"line_number"</span>></span> <span><<span>value</span>></span>1<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"offset"</span>></span> <span><<span>value</span>></span>0<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"offset_end"</span>></span> <span><<span>value</span>></span>62<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"statement"</span>></span> <span><<span>value</span>></span>select top 10 * from dbo.region<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"parameterized_plan_handle"</span>></span> <span><<span>value</span> /></span> <span></<span>data</span>></span> <span><<span>action</span> <span>name</span>=<span>"sql_text"</span> <span>package</span>=<span>"sqlserver"</span>></span> <span><<span>value</span>></span>select top 10 * from dbo.region <span></<span>value</span>></span> <span></<span>action</span>></span> <span></<span>event</span>></span>
登录后复制
我们在Action里面加入对database_name,和plan_handle的捕捉，可以从结果看到又多出来两个元素：
<span><span>create</span> event <span>session</span> capture_sql_events <span>on</span> server <span>add</span> event sqlserver.sql_statement_completed ( <span>action</span>(sqlserver.sql_text,sqlserver.database_name,sqlserver.plan_handle) ) <span>add</span> target package0.event_file ( <span>set</span> filename = <span>''E:\data_bu\capture_sql_events.xel''</span>, metadatafile = <span>''E:\data_bu\capture_sql_event.xem''</span> )</span>
登录后复制
<span><<span>event</span> <span>name</span>=<span>"sql_statement_completed"</span> <span>package</span>=<span>"sqlserver"</span> <span>timestamp</span>=<span>"2016-05-06T03:52:02.524Z"</span>></span> <span><<span>data</span> <span>name</span>=<span>"duration"</span>></span> <span><<span>value</span>></span>0<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"cpu_time"</span>></span> <span><<span>value</span>></span>0<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"physical_reads"</span>></span> <span><<span>value</span>></span>0<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"logical_reads"</span>></span> <span><<span>value</span>></span>2<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"writes"</span>></span> <span><<span>value</span>></span>0<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"row_count"</span>></span> <span><<span>value</span>></span>6<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"last_row_count"</span>></span> <span><<span>value</span>></span>6<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"line_number"</span>></span> <span><<span>value</span>></span>1<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"offset"</span>></span> <span><<span>value</span>></span>0<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"offset_end"</span>></span> <span><<span>value</span>></span>62<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"statement"</span>></span> <span><<span>value</span>></span>select top 10 * from dbo.region<span></<span>value</span>></span> <span></<span>data</span>></span> <span><<span>data</span> <span>name</span>=<span>"parameterized_plan_handle"</span>></span> <span><<span>value</span> /></span> <span></<span>data</span>></span> <span><<span>action</span> <span>name</span>=<span>"plan_handle"</span> <span>package</span>=<span>"sqlserver"</span>></span> <span><<span>value</span>></span>06002000448a700f00d62b7a0300000001000000000000000000000000000000000000000000000000000000<span></<span>value</span>></span> <span></<span>action</span>></span> <span><<span>action</span> <span>name</span>=<span>"database_name"</span> <span>package</span>=<span>"sqlserver"</span>></span> <span><<span>value</span>></span>lenistest4<span></<span>value</span>></span> <span></<span>action</span>></span> <span><<span>action</span> <span>name</span>=<span>"sql_text"</span> <span>package</span>=<span>"sqlserver"</span>></span> <span><<span>value</span>></span>select top 10 * from dbo.region <span></<span>value</span>></span> <span></<span>action</span>></span> <span></<span>event</span>></span>
登录后复制
Centos6 下 Mysql启动提示 Starting MySQL. ERROR! The server quit without updating PID file
环境说明
Centos6.5
MysqL 5.5.37
问题描述
查看MysqL服务，提示MysqL没有启动，手动启动后，提示如下：
[root@LNMP ~]# /etc/init.d/MysqL start Starting MysqL. ERROR! The server quit without updating PID file (/usr/local/MysqL/var/LNMP.pid).
查看错误日志
root@LNMP var]# tail -f /usr/local/MysqL/var/LNMP.err /usr/local/MysqL/bin/MysqLd: Can't create/write to file '/tmp/ibJb9vcs' (Errcode: 13) 161201 8:45:41 InnoDB: Error: unable to create temporary file; errno: 13 161201 8:45:41 [ERROR] Plugin 'InnoDB' init function returned error. 161201 8:45:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE Failed. 161201 8:45:41 [ERROR] UnkNown/unsupported storage engine: InnoDB 161201 8:45:41 [ERROR] Aborting 161201 8:45:41 [Note] /usr/local/MysqL/bin/MysqLd: Shutdown complete 161201 08:45:41 MysqLd_safe MysqLd from pid file /usr/local/MysqL/var/LNMP.pid ended
解决办法
给临时文件加777权限
root@LNMP var]# chmod 777 /tmp
再次启动MysqL，MysqL正常启动！
[root@LNMP var]# /etc/init.d/MysqL restart ERROR! MysqL server PID file Could not be found! Starting MysqL.. SUCCESS! [root@LNMP var]# ps -ef | grep MysqL root 5305 1 0 08:49 pts/0 00:00:00 /bin/sh /usr/local/MysqL/bin/MysqLd_safe --datadir=/usr/local/MysqL/var --pid-file=/usr/local/MysqL/var/LNMP.pid MysqL 5690 5305 1 08:49 pts/0 00:00:00 /usr/local/MysqL/bin/MysqLd --basedir=/usr/local/MysqL --datadir=/usr/local/MysqL/var --plugin-dir=/usr/local/MysqL/lib/plugin --user=MysqL --log-error=/usr/local/MysqL/var/LNMP.err --pid-file=/usr/local/MysqL/var/LNMP.pid --socket=/tmp/MysqL.sock --port=3306 root 5712 1969 0 08:49 pts/0 00:00:00 grep MysqL
create sqlserver login user and add it to certain db user (sqlserver authentication)
- create a user in sqlserver
- server -> security - > right click logins -> new login
- in General page,
- fill login name
- sql server authentication (radio button)
- uncheck Enforce pass policy
- drop down list
- default DB -> select your DB (Could leave it)
- default language -> English
- Add the new user to be your DB user (create a user to a certain DB,this user is not real DB owner,but Could set permission as owner. Set the real DB owner see bottom,if set a real owner,Could skip this step )
- your DB -> security -> right click user -> new user
- in General page
- fill the User name
- put your new created user to Login name
- don't select schema
- in DB Role membership -> check the DB owner
- in General page
- your DB -> security -> right click user -> new user
- set the server authentication type
- right click server -> property -> security in left Panel
- select the sql server and Windows Authentication mode (radio button)
set up your program configuration file
- connection string
- user,role provider
- create the membership user role table (createuserWizard in web app)
- use ASP.net adminstrator configuration tool (add,edit,delete user and role)
Set the DB Owner (normally leave as the default) select the certain DB,right click property,in left page,click Files,browse for a owner. then OK. (this Could replace step 2)
今天关于SQL Server Hosting Toolkit的介绍到此结束，谢谢您的阅读，有关asp.net – 版本16.1.1.0中不提供AjaxControlToolkit.ToolkitScriptManager、Auditing SQL Server SQL Statements、Centos6 下 Mysql启动提示 Starting MySQL. ERROR! The server quit without updating PID file、create sqlserver login user and add it to certain db user (sqlserver authentication)等更多相关知识的信息可以在本站进行查询。
本文标签：
