ajeh's questions

运行所有这些命令的登录名是sysadminSQL 2012 Developer 实例上的服务器角色的成员。它是要部署到的数据库的所有者。EXTERNAL ACCESS ASSEMBLY已授予此数据库中的登录权限。还尝试了以下所有sa登录方式均无济于事。

alter database test set trustworthy on
go
create assembly [icsharpcode.sharpziplib]
from 'C:\Workspace\111\icsharpcode-SharpZipLib-4f2d664\bin\Release\ICSharpCode.SharpZipLib.dll'
with permission_set = UNSAFE --< This works!
go
create assembly OutOfRowCompression
from 'C:\Workspace\Sandbox\OutOfRowCompression\OutOfRowCompression\bin\Debug\OutOfRowCompression.dll'
with permission_set = UNSAFE --< This fails!
go

最后一个命令失败：

消息 10327，级别 14，状态 1，第 1 行为程序集“OutOfRowCompression”创建程序集失败，因为程序集“OutOfRowCompression”未获得 PERMISSION_SET = UNSAFE 的授权。当以下任一情况为真时，程序集被授权：数据库所有者 (DBO) 具有 UNSAFE ASSEMBLY 权限并且数据库具有 TRUSTWORTHY 数据库属性；或者程序集使用证书或非对称密钥签名，该密钥具有相应的登录名并具有 UNSAFE ASSEMBLY 权限。

收到错误后，我使用新的非对称密钥对程序集进行了签名并将key1.pfx其添加到主数据库：

CREATE ASYMMETRIC KEY key1
FROM executable
FILE = 'C:\Workspace\Sandbox\OutOfRowCompression\OutOfRowCompression\bin\Debug\OutOfRowCompression.dll'

验证密钥是否存在：

从此密钥创建登录并授予上帝模式：

create login clr_key1 from asymmetric key key1
go
GRANT EXTERNAL ACCESS ASSEMBLY TO clr_key1
go

来自程序集创建命令的错误仍然相同。

尝试OutOfRowCompression在 Visual Studio 2012 中使用 SAFE 和 UNSAFE 访问构建程序集 - 没有区别。

我觉得我已经满足了错误消息提出的所有要求。它应该工作。我错过了什么？

SQL 2012 Enterprise DB 中有一个现有表，其大小约为 120MB，包含约 2000 条记录。它最大的字段是nvarchar(max)包含海绵状的 XML（范围从 20 到 300 kB），它本身在 7Zip 或 RAR 中压缩了大约 50-75%。

我尝试了以下立即返回的命令，即使只是从表中查询 * 需要几秒钟：

alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=ROW)
alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=PAGE)
alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=NONE)

但是在它们中的任何一个之后大小都没有改变，即使表的属性正确地显示了压缩类型。

服务器实际上是否对行/页面应用和删除压缩？它似乎没有时间在执行每个命令的瞬间完成。

几周或几个月一次，但总是在同一工作日，每日批处理作业中使用的存储过程中的一个查询可能会明显卡住并运行大约 200 分钟，直到应用程序终止其连接。这是一个非常简单的教科书查询，它连接 5 个表并计算sum()一个字段以分配给一个变量，因此在这里提供它没有意义。求和的记录总数一般为1-2打。这个查询没有参数，所以参数嗅探（幸运的是）是不可能的。

我看过很多东西：

• 数据库中没有在线用户。它纯粹用作数据转储。
• 没有同时运行的作业。数据库上的最后一个活动是大约提前 10 分钟完成的 tranlog 备份。下一个活动在大约 2 小时后开始，它是一个完整的备份，几分钟后就可以正常完成（虽然这个查询仍然很疯狂）。

• 此查询运行时有以下锁，均处于 GRANT 状态：

  • OBJECTSch-S锁定模式下请求的表之一
  • HOBT.BULK_OPERATIONS在模式下锁定另一个表
  • OBJECTIX锁定模式下请求的另一个表

该查询(NOLOCK)对所有连接表都有提示。它处于RUNNABLE状态，根据sp_whoisactive，它在服务器上每 15 分钟运行一次，相关查询的 CPU 计数不断增加。CPU 增长到独立查询执行的 1000 倍左右。来自的查询的 IO 与sp_whoisactive我独立运行它时大致相同，但读取计数非常大，是独立执行的 10000 倍。感觉就像查询处于无限循环中。但是，当我早上进入办公室时，作业会在几秒钟内重新运行。

• 报告的计划sp_whoisactive与独立计划相同。当我运行它时，有一个创建索引的优化器提示，但由于查询独立时间约为 200 毫秒，我并不关心这个提示。实施它似乎又减少了 10 毫秒。

• DBCC CHECKTABLE在任何涉及的表或上都没有报告错误DBCC CHECKDB。

我对该计划的唯一怀疑是它有两个平行图标。但是添加option (maxdop 1)只会将其执行时间从 200 毫秒增加到 400 毫秒。

涉及到5张表，大小不一，最大的大概有1*10^6条记录，不算大。我们每天凌晨 4 点刷新统计数据和索引，作业在午夜运行，然后再导入任何大量数据。作业本身只添加 1-10 条记录。

强制为挂起的查询存储执行计划会导致相同的结果：查询卡住、巨大的 CPU 和 IO。让优化器发挥它的魔力，或者强制我获得独立的计划（它们是相同的）它在瞬间完成。

...

长话短说：我被允许在此作业的“正常”执行时间内在生产中运行每晚计划的跟踪，以尝试解决此问题。我们不知道下一次发生的时间，只希望它会遵循我们观察到的模式——每隔几周。

我的百万美元问题是我应该追踪哪些事件？我只是一名 SQL 开发人员，没有典型的 DBA 技能，所以我不知道应该通过跟踪暴露 SQL Server 的哪些内部结构以抓住罪魁祸首。

当我在会话 #1 中创建测试用例时：

create table ##t (a int)
begin tran
insert into ##t select 1

在另一个会话 #2 中运行

select * from ##t

在另一个会话中运行

sp_who2 active

那么在阻塞的会话#2 上没有 CPU 和 IO 计数器增加。这是有道理的，但是阻塞语句总是这样吗？

我想知道是否可以使用over (partition by...)子句以避免在下面的示例中使用子选择：

declare @t table (
     id         int
    ,code       char(2)
    ,descriptor int
)

insert into @t
      select 1, 'a1', 10
union select 1, 'a1', 20
union select 1, 'a1', 30
union select 2, 'b1', 10
union select 2, 'b1', 20
union select 2, 'b1', 30
union select 2, 'b2', 10
union select 2, 'b2', 20
union select 2, 'b2', 30
union select 3, 'c4', 10
union select 3, 'c4', 20
union select 3, 'c4', 30
union select 3, 'c7', 10
union select 3, 'c7', 20
union select 3, 'c7', 30


select *
from @t as t
where code = (select min(code) from @t where id = t.id)

我只想查看code等于a1和b1的记录c4。可以over (partition by...)将 1 分配给所有这些，将 2 分配给代码b2等c7，这样最后我可以说where row_number = 1而不是使用子查询？

根据这篇 MSDN 文章http://msdn.microsoft.com/en-us/library/ms190328.aspx

指定临时表名时，数据库名必须在临时表名之前，除非当前数据库是 tempdb。例如：SELECT OBJECT_ID('tempdb..#mytemptable')。

但是，当我实际上在数据库中创建临时表时tempdb，函数未按指定工作：

use beta1
create table #t(id int)
select object_name(object_id('tempdb..#t')) as from_beta1
use tempdb
select object_name(object_id('tempdb..#t')) as from_tempdb
drop table #t

这就是我所看到的：

from_beta1
----------
NULL

from_tempdb
---------------
#t_________________...______0000000000B9

我错过了什么或做错了什么？

我想弄清楚是否可以避免使用游标来迭代 PL/SQL 中的表行，并尝试了以下脚本：

set serveroutput on;

declare
    v1 tbl_plant.plant_id%type := 0; -- number(10,0)
    v2 tbl_plant.name%type := ''; -- varchar2(50)
begin
    while true
    loop
        select plant_id, name
        into v1, v2
        from tbl_plant
        where rownum = 1
            and plant_id > v1
        order by plant_id;

        exit when sql%rownum = 0;

        dbms_output.put_line(v1 || v2);
    end loop;
end;

所以我认为编译器在while使用循环时将其优化为隐式游标，并试图规避：

set serveroutput on;

declare
    v1 tbl_plant.plant_id%type := 0;
    v2 tbl_plant.name%type := '';
begin
<<divein>>
        select plant_id, name
        into v1, v2
        from tbl_plant
        where rownum = 1
            and plant_id > v1
        order by plant_id;

        if sql%rownum = 0 then
          goto bailout;
        end if;

        dbms_output.put_line(v1 || v2);
        goto divein;
<<bailout>>
    null;
end;

但在这两种情况下，我都得到了完全相同的错误：

PLS-00207: identifier 'ROWNUM', applied to implicit cursor SQL, is not a legal cursor attribute

但是，如果我注释掉whileorgoto和标签，消除任一类型的循环，相同的选择工作正常，并按tbl_plant植物 ID 返回第一个 ID/名称。

是否可以使用游标进行迭代？

前一段时间（大约 2004 年），我在一个团队中担任后端开发人员，每个人都使用 where 子句连接。作为使用内部/外部连接的支持者，我向他们展示了一个 where 子句连接示例，说明它如何根据数据产生不明确的结果，以及外部连接如何避免该问题。

我必须做同样的演示，但再也找不到那个片段了。它在我的文件存档中的某个地方，不幸的是我无权访问。有没有人有一个方便的例子？

问 1：

每当 Oracle SP 想要返回错误代码/消息时，它都无法返回以下异常：

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DB.GET_COST", line 244
ORA-01403: no data found
ORA-06512: at line 1".

我通过链接服务器从 MS SQL 调用它，如下所示：

declare
     @id1   int
    ,@id2   varchar(8)
    ,@code  varchar(10)
    ,@curr  varchar(4)
    ,@ed    datetime
    ,@return_status int
    ,@error_code    varchar(10)
    ,@error_msg     varchar
    ,@landed_cost   numeric(14,5)

begin try
    exec ('BEGIN db.get_cost(?,?,?,?,?,?,?,?,?); END;'
         ,@code
         ,@id1
         ,@id2
         ,@curr
         ,@ed
         ,@return_status    output
         ,@error_code       output
         ,@error_msg        output
         ,@cost             output
    ) AT ORASRV
end try
begin catch
    select
          @return_status    as return_status
         ,@error_code       as error_code
         ,@error_msg        as error_msg
end catch

输入参数似乎工作正常，因为它们的值使得当 SP 返回正确的结果时，一切正常。当抛出上述错误时，只有 IN 参数值可能导致 SP 返回错误代码。

Oracle端的声明如下：

PROCEDURE get_cost (
     code   IN  VARCHAR2(10 BYTE),
     id1    IN  NUMBER(7,0),
     id2    IN  VARCHAR2(8 BYTE),
     curr   IN  VARCHAR2(4 BYTE),
     ed     IN  DATE,
     return_status  OUT NUMBER
     error_code     OUT VARCHAR2(10 BYTE)
     error_msg      OUT VARCHAR2,
     cost   OUT NUMBER(14,5)
)

问 2：

雪上加霜的是，在抛出上述异常 3 次并正常工作一次后，该脚本随后终止并显示以下 MS SQL 错误消息：

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

即使我排除了导致原始错误的第 3 个调用并且只成功调用了一次 SP，它也会死掉。

使用以下参数类型调用 Oracle 函数时

NUMBER(7,0)
VARCHAR2(8 BYTE)
VARCHAR2(4 BYTE)
DATE

并通过链接的 Oracle 服务器从 SQL Server 2008R2 返回类型NUMBER(14,5)，我在 SQL Server 端声明参数变量如下：

numeric(7,0) -- also tried INT, BIGINT
varchar(8)
varchar(4)
datetime -- also tried DATE

并返回变量类型numeric(14,5)

exec ( 'BEGIN ? := db.pkg.getPrice(?,?,?,?); END;', @price, @id1, @id2, @curr, @ed ) AT ORASRV

调用失败并出现以下错误：

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1".

我究竟做错了什么？

为了测试该功能，我使用了以下 PL/SQL 脚本，它运行良好（打印0出的参数结果正确）：

declare
  c varchar2(4);
  ed date;
  res number(14,5);
begin
  c := 'USD';
  ed := to_date('22-MAY-14', 'DD-Mon-YYYY');
  res := pkg.getPrice(214360, '129746', c, ed);
  DBMS_OUTPUT.PUT_LINE(res);
end;

还创建了一个仅用于测试的函数：

create or replace FUNCTION getTest ( 
    a IN VARCHAR2 -- also tried INTEGER
)
RETURN  VARCHAR2 -- also tried INTEGER
IS
  res VARCHAR2(1); -- also tried INTEGER
BEGIN
  res := a;
  RETURN res;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;    
END;

当我按如下方式从 SQL Server 调用它时

declare @a varchar(1),@b varchar(1) -- also tried INT
select @b = 'a'
exec ( 'BEGIN ? := db.getTest(?); END;', @a, @b) AT ORASRV

我收到以下错误：

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1".
Msg 7215, Level 17, State 1, Line 172
Could not execute statement on remote server 'ORASRV'.

对于 Oracle 端的 INTEGER 和 SQL Server 端的 INT，消息只是"ORA-06502: PL/SQL: numeric or value error

在 SQLDeveloper 4 中，一些 10g PL/SQL 包有一个常规的 crate，而另一些有一个绿点。

那些有绿点的人不会扩大他们的身体（可能只是一个巧合）。带绿点和不带绿点的包装有什么区别？为什么有些包装不膨胀它们的身体？

如果 DB2 for IBM i 7.1 中已经有一个全局临时表，那么测试的语法是什么？

我试过这个之后DECLARE GLOBAL TEMPORARY TABLE：

SELECT * FROM QSYS2.SYSTABLES WHERE SYSTEM_TABLE_SCHEMA = 'QTEMP';

它返回一个空的结果集。永久表似乎包含在该视图中，而不是临时表。

最终，我需要有条件地删除临时表或截断/删除数据（如果存在）。

在阅读了 DB2 UDB for iSeries SQL Programming Concepts V5R2 的“定义 SQL 过程”部分和 i5/OS 信息中心第 5 版第 4 版的“从存储过程返回结果集”部分后，我创建了以下存储过程：

CREATE PROCEDURE TESTDAT.REPORT (IN DATESTART DATE, IN DATEEND DATE)
LANGUAGE SQL READS SQL DATA
BEGIN
    DECLARE CSR1 CURSOR WITH RETURN FOR
    SELECT *
    FROM  SYSIBM.SYSDUMMY1;

    OPEN CSR1;
    RETURN;
END

接下来，我阅读了同一 i5/OS 信息中心第 5 版第 4 版的“示例 1：调用返回单个结果集的存储过程”部分，并尝试从 SqlDbx 客户端（通过 ODBC）或 SSRS 报告（通过IBMDASQL 提供程序）使用以下语句：

CALL TESTDAT.REPORT (DATE('11/01/2013'), DATE('11/03/2013'));

它执行成功，但没有返回结果集。通过 iSeries 导航器成功执行此操作返回 2 个语句 - 选择和声明游标。打电话

SELECT * FROM  SYSIBM.SYSDUMMY1;

返回具有 1 行“Y”的 IBMREQD 列。我从上面的文件中遗漏了什么？

更新：

此过程生成的 SQL 包括除了已经讨论过的其他选项：

SPECIFIC TESTDAT.REPORT 
NOT DETERMINISTIC 
READS SQL DATA 
CALLED ON NULL INPUT 
SET OPTION  ALWBLK = *ALLREAD , 
ALWCPYDTA = *OPTIMIZE , 
COMMIT = *NONE , 
DECRESULT = (31, 31, 00) , 
DFTRDBCOL = *NONE , 
DYNDFTCOL = *NO , 
DYNUSRPRF = *USER , 
SRTSEQ = *HEX   

它们中的任何一个都会影响将结果集返回到 SSRS 吗？