Steve_Malcolm's questions

这个让我快打败了。以下是存储过程中的代码，如果重复运行，将继续 1) 无法识别匹配行和 2) 插入新的相同行。源表和目标表中 SERV_LINE_ID 和 TYPE_OF_SERVICE 的值和数据类型是相同的。同样，这种行为是可重复的；即使在删除有问题的行之后。其他删除的行被替换，但在第一次运行后被识别。

INSERT INTO etl.TargetTable
            (CUSTOMER_NUMBER,
             PCN,
             VISN,
             VA_FACILITY,
             AUTHORIZATION_NUMBER,
             VAMC,
             CLINXX,
             BEGIN_DOS,
             BILLED_AMOUNT,
             DATE_SUBMITTED,
             VA_FORM_TYPE,
             VETERAN_LAST_NAME,
             VETERAN_FIRST_NAME,
             VETERAN_SS,
             TYPE_OF_SERVICE,
             ENDING_DOS,
             CATEGORY_CARE,
             AUTH_TYPE,
             SERVICE_LINE_ID,
             SERVICE_LINE_CHARGE_AMOUNT,
             LAST_EXPORT,
             SYSISEXPORTED,
             SYSISDELETED,
             SYSCHECKSUM,
             SERV_LINE_ID)
SELECT s.CUSTOMER_NUMBER,
       s.PCN,
       s.VISN,
       s.VA_FACILITY,
       s.AUTHORIZATION_NUMBER,
       s.VAMC,
       s.CLINXX,
       s.BEGIN_DOS,
       s.BILLED_AMOUNT,
       s.DATE_SUBMITTED,
       s.VA_FORM_TYPE,
       s.VETERAN_LAST_NAME,
       s.VETERAN_FIRST_NAME,
       s.VETERAN_SS,
       s.TYPE_OF_SERVICE,
       s.ENDING_DOS,
       s.CATEGORY_CARE,
       s.AUTH_TYPE,
       s.SERVICE_LINE_ID,
       s.SERVICE_LINE_CHARGE_AMOUNT,
       NULL,
       0,
       0,
       s.SYSCHECKSUM,
       s.SERV_LINE_ID
FROM   ##Source s
       LEFT OUTER JOIN etl.TargetTable t
                    ON s.SERV_LINE_ID = t.SERV_LINE_ID
                       AND s.TYPE_OF_SERVICE = t.TYPE_OF_SERVICE
                       AND s.DATE_SUBMITTED <= '2018-10-10'
WHERE  t.SERV_LINE_ID IS NULL 

我觉得一个人可能离这个人站得太近了。我继承了一个SSIS package有两个数据连接对象的。在OLE DB连接（执行工作）和ADO.NET连接（到不同的服务器）上进行日志记录。

ADO.NET 连接远端的服务器可能不稳定，我需要使日志记录容错。日志记录任务已经设置为在失败时不会使包失败，也不会使父包失败。然而，这个过程还没有那么远。包失败是因为连接失败，而不是因为任务失败。

这个包的逻辑路径是 Log Start > Do Stuff > Log Completion。在这个特定的例子中，Log Start 成功了，Do Stuff 也成功了。日志完成失败，因为服务器突然不可用。

SSIS 报告级别的错误消息是

日志状态完成错误：无法获取连接“Logging ADO NET”。连接可能配置不正确，或者您可能没有正确的权限来管理此连接。

欢迎所有建议。（谢谢，圣诞快乐）

我有一个包含多个 (10) 模式的数据库。我需要一个用户可以访问其中两个。如果我授予对数据库的访问权限，然后将 GRANT SELECT 授予 2 个模式，是否暗示对其他模式的访问被拒绝？或者我必须向这些模式授予权限，然后拒绝对其余模式进行选择吗？

将 SQL Server 2012 中的参数化选项从简单更改为强制清除缓存；还是应该在更改后运行 DBCC FREEPROCCACHE？

升级还是新建？有什么建议么？有没有人有任何关于这个过程的陷阱或战争故事要分享？

我们在 Prod 中有一个 2012 实例，它同时具有 OLTP 和 OLAP 数据库。我们正在创建两个实例，一个专门用于这些任务。OLAP 实例是一个新版本，但是为了最大限度地减少最终用户的停机时间，我们正在考虑对将容纳 OLAP 数据库的实例进行升级。

我正在寻找超出我在 Microsoft 文档中阅读的内容的一般性建议。

我的执行计划发布在 PasteThePlan.com 上。第二个嵌套循环警告没有连接谓词。我还是新手，所以我很困惑。那是因为我使用 CTE 来确定其中两个属性的值吗？此演示表用于更多的报告中，我不想为这个非常独特的报告创建第二个。

这是查询：

WITH Accumulators AS
(
 SELECT DISTINCT AreaNum, SuperintendentNum, SuperName
   FROM FI.SLAnalysis
  WHERE SuperName LIKE '%ACCUM%'
)
SELECT
    slard.[IO]
  , slard.PhaseName             AS PhaseName
  , slard.JobNum              AS JobNum
--  , slard.JobName              AS JobName
  , CASE
   WHEN SUBSTRING(JobNum, 5, 2) IN ('05', '06')
    THEN ac.SuperintendentNum
   ELSE slard.SuperNum
     END                AS SuperNum
  , CASE
   WHEN SUBSTRING(JobNum, 5, 2) IN ('05', '06')
    THEN ac.SuperName
   ELSE slard.SuperName
     END                AS SuperName
  , slard.AreaNum              AS AreaNum
  , slard.AreaName             AS AreaName
  , fp.WeekEndDate             AS WeekEndDate

  -- WTD Metrics
  , slard.WeeklyRevenue            AS WTDRevenue
  , slard.WeeklyCost             AS WTDCost  
  , slard.WeeklyRevenue - slard.WeeklyCost       AS WTDGP
  , CASE
   WHEN slard.WeeklyRevenue = .01 THEN 0
   ELSE slard.WeeklyRevenue
     END                AS WTDRatio
  , slard.Weekly_PER             AS WTDGPPer
  , slard.WeeklyStandOvrheadAdmin          AS WTDOverAdmin
  , slard.WeeklyStandOvrheadEquip          AS WTDOverEquip
  , (slard.WeeklyRevenue
   -slard.WeeklyCost
   -slard.WeeklyStandOvrheadAdmin
   -slard.WeeklyStandOvrheadEquip)         AS WTDOHGP

  --MTD Metrics      
  , slard.MTDRevenue             AS MTDRevenue
  , slard.MTDCost              AS MTDCost
  , slard.MTDRevenue - MTDCost          AS MTD_GP
  , CASE 
   WHEN slard.MTDRevenue <= .05 AND slard.MTDRevenue > 0 THEN 0
   ELSE slard.MTDRevenue
     END                AS MTDRatio
  , CASE
   WHEN slard.MTDRevenue <> 0
    THEN CAST(slard.MTDCost / slard.MTDRevenue AS DECIMAL(18, 2))
   ELSE CAST(0.00 AS DECIMAL(18, 2))
     END                AS MTDGPPer
  , slard.MonthlyStandOvrheadAdmin         AS MTDOverAdmin
  , slard.MonthlyStandOvrheadEquip         AS MTDOverEquip
  , (slard.MTDRevenue
   -slard.MTDCost
   -slard.MonthlyStandOvrheadAdmin
   -slard.MonthlyStandOvrheadEquip)        AS MTDOHGP
  -- YTD Metrics
  , slard.YTDRevenue             AS YTDRevenue
  , slard.YTDCost              AS YTDCost
  , slard.YTDRevenue - YTDCost          AS YTD_GP
  , CASE
   WHEN slard.YTDRevenue <= .53 AND slard.YTDRevenue > 0 THEN 0
   ELSE slard.YTDRevenue
     END                 AS YTDRatio
  , slard.YTDStandOvrheadAdmin          AS YTDOverAdmin
  , slard.YTDStandOvrheadEquip          AS YTDOverEquip
  , (slard.YTDRevenue
   -slard.YTDCost
   -slard.YTDStandOvrheadAdmin
   -slard.YTDStandOvrheadEquip)         AS YTDOHGP
  FROM [DW].[FI].[SLAnalysisReportData] slard
 INNER JOIN Accumulators ac
   ON slard.AreaNum = ac.AreaNum
 INNER JOIN CORP.FiscalPeriod fp
  ON slard.FY = fp.FY
   AND slard.[Period] = fp.[Period]
WHERE slard.FY = 2018
 AND slard.[Period]  = 3
    AND slard.AreaNum IN ('40')
   AND NOT (    WeeklyRevenue = 0
            AND WeeklyCost = 0
            AND WeeklyStandOvrheadAdmin = 0
            AND WeeklyStandOvrheadEquip = 0
            AND MTDRevenue = 0
            AND MTDCost = 0
            AND MonthlyStandOvrheadAdmin = 0
            AND MonthlyStandOvrheadEquip = 0
            AND YTDRevenue = 0
            AND YTDCost = 0
            AND YTDStandOvrheadAdmin = 0
            AND YTDStandOvrheadEquip = 0
           )