主页 / user-31969

jhowe's questions

Martin Hope
jhowe
Asked: 2016-03-31 02:41:12 +0800 CST
  • 0

我们有一个有时会挂起的 DWH 加载作业。我们无法确定为什么会发生这种情况，它似乎是随机的并且与网络/硬件相关（我们无法控制）。我们正在订购新的硬件来隔离我们的流程，但这还需要几周的时间。因此，当我们的工作挂起时，我们希望指定一个超时选项，如果任何步骤达到一定时间并自动重新启动。如何才能做到这一点？谢谢！

sql-server sql-server-2012
Martin Hope
jhowe
Asked: 2016-02-01 06:10:32 +0800 CST
  • 5

我有一张包含近 600 万条记录的表。标识唯一行的业务键非常大。自从我为新多维数据集添加这个新表以来，我们的更新处理现在花费了更长的时间。我目前在更新的连接列上没有索引。SQL Server 估计执行计划说我应该在业务键上创建这个索引：

/*
Missing Index Details from Server.db
The Query Processor estimates that implementing the following index
could improve the query cost by 86.9178%.
*/

/*
USE [db]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [schema].[Production] 
(
    [ProdId],[PriceCalc],[CalcTypeId],[OprId],[CostGroupId],[Resource],
    [BOM],[ResourceDepartment],[OprNum],[DateWIP],[DataAreaId],[Partition]
)
INCLUDE 
(
    [ProdOrderStatus],[ManufacturedItemId],[CalcType],[CalculationLevel],
    [CostAnalysisOrderType],[CostGrouping],[UnitId],[WorkCenter],[Name],
    [RealisedConsumption],[RealisedCostAmount],[RealisedCostAdjustment],
    [EstimatedConsumption],[EstimatedCostAmount],[LotSizeVariance],
    [StandardQty],[StandardCost],[ItemStandardQty],[HasSubstitutionVariance],
    [R2],[R3],[StandardQtyByRAFQty],[StandardCostByRAFQty],[ProductionOrderType],
    [RealisedAllocation],[CostVariance],[QuantityVariance],[SubstitutionVariance],
    [TotalVariance],[ComponentItemId],[InventoryUOM],[InventConsumptionTransUOM],
    [BomConsumptionTranUOM],[TransactionUOM],[TransUOMToInvUOMConversionRate],
    [InventConsumptionInvUOM],[BomConsumptionInvUOM],[TotalNetWeightPerUnitInvUOM],
    [InventoryNetWeightUOM],[ReportingNetWeightUOM],
    [NetWeight_InvUOMToReportingUOMConversionRate],
    [InventConsumptionTotalNetWeightInvUOM],[BomConsumptionTotalNetWeightInvUOM],
    [InventConsumptionTotalNetWeightReportingUOM],
    [BomConsumptionTotalNetWeightReportingUOM],
    [FinancialProductId],[FinancialDepartmentId],[FinancialMarketId],[FinancialCodeId],
    [FinancialTypeId],[FinancialSiteId],[ProdPoolId],[Company_SK],[ComponentItem_SK],
    [EndedDate_SK],[DateWIP_SK],[FinancialCode_SK],[FinancialDepartment_SK],
    [FinancialMarket_SK],[FinancialProduct_SK],[FinancialSite_SK],[FinancialType_SK],
    [InventoryNetWeightUOM_SK],[InventoryUOM_SK],[ManufacturedItem_SK],
    [ProductionOrder_SK],
    [ReportingNetWeightUOM_SK],[TransactionUOM_SK],[BatchRunId],[ValidInd],[ScrapVar],
    [QuantityPO],[ExpectedConsumption],[FibreScrapFactor],[QtyAndSubVariance],
    [EndedDate],[RealisedAllocationCost],[FilmScrapFactor])
GO
*/

它想在键列上创建一个索引，但是它想包含很多列。我应该听还是只包含关键列？谢谢你的帮助。这是一个生产问题，所以我不能去测试不同的东西等。

我们正在考虑按照以下思路引入哈希解决方案：

使用 Hashbytes 跟踪和存储 SQL Server 数据的历史变化

短期来看，下周是重要的金融收尾期，系统需要良好运行，加指数似乎是个好主意。所以我想尽快做任何我能做的事。您会推荐用于长期/新实施的哈希解决方案吗？

sql-server sql-server-2012
Martin Hope
jhowe
Asked: 2015-10-30 09:27:54 +0800 CST
  • 0

我有一些来自多维数据集的数据。

在此处输入图像描述

我在这个订单中有一行作为替代方差输入，但我需要将其放入数量方差桶中。决定是否替换的代码位于最低级别（这是在一个大型 CTE 中）（该多维数据集背后的代码非常庞大并且存在于多个级别）

CASE 
   WHEN ISNULL(PC.ItemDimCostPrice, SC.ItemDimCostPrice) = 1
        AND (PC.ConfigId <> SC.ConfigId
             OR PC.InventColorId <> SC.InventColorId
             OR PC.InventSizeId <> SC.InventSizeId
             OR PC.InventStyleId <> SC.InventStyleId) 
      THEN 1
   --New code here ... THEN 0
   WHEN PC.[Resource] IS NULL
        OR SC.[Resource] IS NULL 
     THEN 1
   ELSE 0
END AS [HasSubstitutionVariance] ,

他们给我的逻辑基本上是如果成本分组很重要（我知道如何计算）并且存在另一个具有相同 costgroupid 和 operationid 的记录（在生产订单中）那么这将永远不会是替代方差。我正在考虑在 costgroupid 和 operationid 上使用 rownumber 和分区，但据我所知你不能在 case 语句中使用窗口函数？任何人有任何想法我将如何做到这一点？谢谢！

sql-server sql-server-2012
Martin Hope
jhowe
Asked: 2015-10-26 10:54:13 +0800 CST
  • 0

我有一个等式

((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0) 
    / NULLIF(1000000, 0))) - 1) * 100 AS FibreScrapFactor

这适用于我正在查看的示例订单。但是，如果我取出 where 子句（所以我有所有订单），我会得到除以零的错误。

如果我将 / 1000000 限制为 10，那么我就有

((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0) 
    / NULLIF(10, 0))) - 1) * 100 AS FibreScrapFactor

这适用于所有订单，但不正确我需要除以一百万。我怎样才能让它工作？列是numeric(32, 16)。

sql-server sql-server-2012
Martin Hope
jhowe
Asked: 2015-09-25 06:32:47 +0800 CST
  • 3

大家好，我有 2 个事实表，每个都有一个度量组，生产和生产订单。

生产具有较低粒度的生产信息（在组件级别）productionorders具有较高级别的信息（带有标题数量的订单级别等）。

我在 上的两个表之间创建了代理键链接productionorderid。一旦我将产品 ID（来自productiondetailsdim）添加到数据透视表，它就会显示实际数量（来自产品订单度量组）并且我无法将两个度量组中的数量组合起来......我如何设计正确的关系二？请看我模糊的用法图。

Production Details 是链接两个事实表的 dim……目前DimProductionDetails与 Production 处于事实关系中。

我不确定与生产订单的关系应该是什么（目前是多对多）感谢您的帮助！如果我将行级别数量与标题级别结合起来，我不希望为每一行（重复）求和的标题数量我只希望它在订单（标题级别）和行数量的旁边求和...

SSAS关系

请查看两个表之间的示例数据 在此处输入图像描述

我必须能够在下面复制这种行为......

在此处输入图像描述

ssas
Martin Hope
jhowe
Asked: 2015-02-26 06:06:57 +0800 CST
  • 1

简单的问题，不是那么简单的答案！我有一堆日期...如何删除中间数字（不正确）以便我只有第 3 周、2011/2012 等？谢谢！

Reporting_Week_Name
Week 52, 2010/2011/2012
Week 52, 2010/2011/2012
Week 1, 2011/2011/2012
Week 1, 2011/2011/2012
Week 1, 2011/2011/2012
Week 1, 2011/2011/2012
Week 1, 2011/2011/2012
Week 1, 2011/2011/2012
Week 1, 2011/2011/2012
Week 2, 2011/2011/2012
Week 2, 2011/2011/2012
Week 2, 2011/2011/2012
Week 2, 2011/2011/2012
Week 2, 2011/2011/2012
Week 2, 2011/2011/2012
Week 2, 2011/2011/2012
Week 3, 2011/2011/2012
Week 3, 2011/2011/2012
Week 3, 2011/2011/2012
Week 3, 2011/2011/2012
Week 3, 2011/2011/2012
Week 3, 2011/2011/2012
Week 3, 2011/2011/2012
Week 4, 2011/2011/2012
Week 4, 2011/2011/2012
Week 4, 2011/2011/2012
Week 4, 2011/2011/2012
Week 4, 2011/2011/2012
Week 4, 2011/2011/2012
Week 4, 2011/2011/2012
Week 5, 2011/2011/2012
Week 5, 2011/2011/2012
Week 5, 2011/2011/2012
Week 5, 2011/2011/2012
Week 5, 2011/2011/2012
Week 5, 2011/2011/2012
Week 5, 2011/2011/2012
Week 6, 2011/2011/2012
Week 6, 2011/2011/2012
Week 6, 2011/2011/2012
Week 6, 2011/2011/2012
Week 6, 2011/2011/2012
Week 6, 2011/2011/2012
Week 6, 2011/2011/2012
Week 7, 2011/2011/2012
Week 7, 2011/2011/2012
Week 7, 2011/2011/2012
Week 7, 2011/2011/2012
Week 7, 2011/2011/2012
Week 7, 2011/2011/2012
Week 7, 2011/2011/2012
Week 8, 2011/2011/2012
Week 8, 2011/2011/2012
Week 8, 2011/2011/2012
Week 8, 2011/2011/2012
Week 8, 2011/2011/2012
Week 8, 2011/2011/2012
Week 8, 2011/2011/2012
Week 9, 2011/2011/2012
Week 9, 2011/2011/2012
Week 9, 2011/2011/2012
Week 9, 2011/2011/2012
Week 9, 2011/2011/2012
Week 9, 2011/2011/2012
Week 9, 2011/2011/2012
Week 10, 2011/2011/2012
Week 10, 2011/2011/2012
Week 10, 2011/2011/2012
Week 10, 2011/2011/2012
Week 10, 2011/2011/2012
Week 10, 2011/2011/2012
Week 10, 2011/2011/2012
Week 11, 2011/2011/2012
Week 11, 2011/2011/2012
Week 11, 2011/2011/2012
Week 11, 2011/2011/2012
Week 11, 2011/2011/2012
Week 11, 2011/2011/2012
Week 11, 2011/2011/2012
Week 12, 2011/2011/2012
Week 12, 2011/2011/2012
Week 12, 2011/2011/2012
Week 12, 2011/2011/2012
Week 12, 2011/2011/2012
Week 12, 2011/2011/2012
Week 12, 2011/2011/2012
Week 13, 2011/2011/2012
Week 13, 2011/2011/2012
Week 13, 2011/2011/2012
Week 13, 2011/2011/2012
Week 13, 2011/2011/2012
Week 13, 2011/2011/2012
Week 13, 2011/2011/2012
Week 14, 2011/2011/2012
Week 14, 2011/2011/2012
Week 14, 2011/2011/2012
Week 14, 2011/2011/2012
Week 14, 2011/2011/2012
Week 14, 2011/2011/2012
Week 14, 2011/2011/2012
Week 15, 2011/2011/2012
Week 15, 2011/2011/2012
Week 15, 2011/2011/2012
Week 15, 2011/2011/2012
Week 15, 2011/2011/2012
Week 15, 2011/2011/2012
Week 15, 2011/2011/2012
Week 16, 2011/2011/2012
Week 16, 2011/2011/2012
Week 16, 2011/2011/2012
Week 16, 2011/2011/2012
Week 16, 2011/2011/2012
Week 16, 2011/2011/2012
Week 16, 2011/2011/2012
Week 17, 2011/2011/2012
Week 17, 2011/2011/2012
Week 17, 2011/2011/2012
Week 17, 2011/2011/2012
Week 17, 2011/2011/2012
Week 17, 2011/2011/2012
Week 17, 2011/2011/2012
Week 18, 2011/2011/2012
Week 18, 2011/2011/2012
Week 18, 2011/2011/2012
Week 18, 2011/2011/2012
Week 18, 2011/2011/2012
Week 18, 2011/2011/2012
Week 18, 2011/2011/2012
Week 19, 2011/2011/2012
Week 19, 2011/2011/2012
Week 19, 2011/2011/2012
Week 19, 2011/2011/2012
Week 19, 2011/2011/2012
Week 19, 2011/2011/2012
Week 19, 2011/2011/2012
Week 20, 2011/2011/2012
Week 20, 2011/2011/2012
Week 20, 2011/2011/2012
Week 20, 2011/2011/2012
Week 20, 2011/2011/2012
Week 20, 2011/2011/2012
Week 20, 2011/2011/2012
Week 21, 2011/2011/2012
Week 21, 2011/2011/2012
Week 21, 2011/2011/2012
Week 21, 2011/2011/2012
Week 21, 2011/2011/2012
Week 21, 2011/2011/2012
Week 21, 2011/2011/2012
Week 22, 2011/2011/2012
Week 22, 2011/2011/2012
Week 22, 2011/2011/2012
Week 22, 2011/2011/2012
Week 22, 2011/2011/2012
Week 22, 2011/2011/2012
Week 22, 2011/2011/2012
Week 23, 2011/2011/2012
Week 23, 2011/2011/2012
Week 23, 2011/2011/2012
Week 23, 2011/2011/2012
Week 23, 2011/2011/2012
Week 23, 2011/2011/2012
Week 23, 2011/2011/2012
Week 24, 2011/2011/2012
Week 24, 2011/2011/2012
Week 24, 2011/2011/2012
Week 24, 2011/2011/2012
Week 24, 2011/2011/2012
Week 24, 2011/2011/2012
Week 24, 2011/2011/2012
Week 25, 2011/2011/2012
Week 25, 2011/2011/2012
Week 25, 2011/2011/2012
Week 25, 2011/2011/2012
Week 25, 2011/2011/2012
Week 25, 2011/2011/2012
Week 25, 2011/2011/2012
Week 26, 2011/2011/2012
Week 26, 2011/2011/2012
Week 26, 2011/2011/2012
Week 26, 2011/2011/2012
Week 26, 2011/2011/2012
Week 26, 2011/2011/2012
Week 26, 2011/2011/2012
Week 27, 2011/2011/2012
Week 27, 2011/2011/2012
Week 27, 2011/2011/2012
Week 27, 2011/2011/2012
Week 27, 2011/2011/2012
Week 27, 2011/2011/2012
Week 27, 2011/2011/2012
Week 28, 2011/2011/2012
Week 28, 2011/2011/2012
Week 28, 2011/2011/2012
Week 28, 2011/2011/2012
Week 28, 2011/2011/2012
Week 28, 2011/2011/2012
Week 28, 2011/2011/2012
Week 29, 2011/2011/2012
Week 29, 2011/2011/2012
Week 29, 2011/2011/2012
Week 29, 2011/2011/2012
Week 29, 2011/2011/2012
Week 29, 2011/2011/2012
Week 29, 2011/2011/2012
Week 30, 2011/2011/2012
Week 30, 2011/2011/2012
Week 30, 2011/2011/2012
Week 30, 2011/2011/2012
Week 30, 2011/2011/2012
Week 30, 2011/2011/2012
Week 30, 2011/2011/2012
Week 31, 2011/2011/2012
Week 31, 2011/2011/2012
Week 31, 2011/2011/2012
Week 31, 2011/2011/2012
Week 31, 2011/2011/2012
Week 31, 2011/2011/2012
Week 31, 2011/2011/2012
Week 32, 2011/2011/2012
Week 32, 2011/2011/2012
Week 32, 2011/2011/2012
Week 32, 2011/2011/2012
Week 32, 2011/2011/2012
Week 32, 2011/2011/2012
Week 32, 2011/2011/2012
Week 33, 2011/2011/2012
Week 33, 2011/2011/2012
Week 33, 2011/2011/2012
Week 33, 2011/2011/2012
Week 33, 2011/2011/2012
Week 33, 2011/2011/2012
Week 33, 2011/2011/2012
Week 34, 2011/2011/2012
Week 34, 2011/2011/2012
Week 34, 2011/2011/2012
Week 34, 2011/2011/2012
Week 34, 2011/2011/2012
Week 34, 2011/2011/2012
Week 34, 2011/2011/2012
Week 35, 2011/2011/2012
Week 35, 2011/2011/2012
Week 35, 2011/2011/2012
Week 35, 2011/2011/2012
Week 35, 2011/2011/2012
Week 35, 2011/2011/2012
Week 35, 2011/2011/2012
Week 36, 2011/2011/2012
Week 36, 2011/2011/2012
Week 36, 2011/2011/2012
Week 36, 2011/2011/2012
Week 36, 2011/2011/2012
Week 36, 2011/2011/2012
Week 36, 2011/2011/2012
Week 37, 2011/2011/2012
Week 37, 2011/2011/2012
Week 37, 2011/2011/2012
Week 37, 2011/2011/2012
Week 37, 2011/2011/2012
Week 37, 2011/2011/2012
Week 37, 2011/2011/2012
Week 38, 2011/2011/2012
Week 38, 2011/2011/2012
Week 38, 2011/2011/2012
Week 38, 2011/2011/2012
Week 38, 2011/2011/2012
Week 38, 2011/2011/2012
Week 38, 2011/2011/2012
Week 39, 2011/2011/2012
Week 39, 2011/2011/2012
Week 39, 2011/2011/2012
Week 39, 2011/2011/2012
Week 39, 2011/2011/2012
Week 39, 2011/2011/2012
Week 39, 2011/2011/2012
Week 40, 2011/2011/2012
Week 40, 2011/2011/2012
Week 40, 2011/2011/2012
Week 40, 2011/2011/2012
Week 40, 2011/2011/2012
Week 40, 2011/2011/2012
Week 40, 2011/2011/2012
Week 41, 2011/2011/2012
Week 41, 2011/2011/2012
Week 41, 2011/2011/2012
Week 41, 2011/2011/2012
Week 41, 2011/2011/2012
Week 41, 2011/2011/2012
Week 41, 2011/2011/2012
Week 42, 2011/2011/2012
Week 42, 2011/2011/2012
Week 42, 2011/2011/2012
Week 42, 2011/2011/2012
Week 42, 2011/2011/2012
Week 42, 2011/2011/2012
Week 42, 2011/2011/2012
Week 43, 2011/2011/2012
Week 43, 2011/2011/2012
Week 43, 2011/2011/2012
Week 43, 2011/2011/2012
Week 43, 2011/2011/2012
Week 43, 2011/2011/2012
Week 43, 2011/2011/2012
Week 44, 2011/2011/2012
Week 44, 2011/2011/2012
Week 44, 2011/2011/2012
Week 44, 2011/2011/2012
Week 44, 2011/2011/2012
Week 44, 2011/2011/2012
Week 44, 2011/2011/2012
Week 45, 2011/2011/2012
Week 45, 2011/2011/2012
Week 45, 2011/2011/2012
Week 45, 2011/2011/2012
Week 45, 2011/2011/2012
Week 45, 2011/2011/2012
Week 45, 2011/2011/2012
Week 46, 2011/2011/2012
Week 46, 2011/2011/2012
Week 46, 2011/2011/2012
Week 46, 2011/2011/2012
Week 46, 2011/2011/2012
Week 46, 2011/2011/2012
Week 46, 2011/2011/2012
Week 47, 2011/2011/2012
Week 47, 2011/2011/2012
Week 47, 2011/2011/2012
Week 47, 2011/2011/2012
Week 47, 2011/2011/2012
Week 47, 2011/2011/2012
Week 47, 2011/2011/2012
Week 48, 2011/2011/2012
Week 48, 2011/2011/2012
Week 48, 2011/2011/2012
Week 48, 2011/2011/2012
Week 48, 2011/2011/2012
Week 48, 2011/2011/2012
Week 48, 2011/2011/2012
Week 49, 2011/2011/2012
Week 49, 2011/2011/2012
Week 49, 2011/2011/2012
Week 49, 2011/2011/2012
Week 49, 2011/2011/2012
Week 49, 2011/2011/2012
Week 49, 2011/2011/2012
Week 50, 2011/2011/2012
Week 50, 2011/2011/2012
Week 50, 2011/2011/2012
Week 50, 2011/2011/2012
Week 50, 2011/2011/2012
Week 50, 2011/2011/2012
Week 50, 2011/2011/2012
Week 51, 2011/2011/2012
Week 51, 2011/2011/2012
Week 51, 2011/2011/2012
Week 51, 2011/2011/2012
Week 51, 2011/2011/2012
Week 51, 2011/2011/2012
Week 51, 2011/2011/2012
Week 52, 2011/2011/2012
Week 52, 2011/2011/2012
Week 52, 2011/2011/2012
Week 52, 2011/2011/2012
Week 52, 2011/2011/2012
Week 52, 2011/2011/2012
Week 52, 2011/2012/2013
Week 1, 2012/2012/2013
sql-server-2008-r2
Martin Hope
jhowe
Asked: 2015-01-30 08:59:49 +0800 CST
  • 0

大家好，我在表格中有两列：

+--------------------+----------------+
| Units/Pk           | PackWeight/Vol |
+--------------------+----------------+
| 1.0000000000000000 | 150g           |
+--------------------+----------------+
| 1.0000000000000000 | 150g           |
+--------------------+----------------+
| 1.0000000000000000 | 114g           |
+--------------------+----------------+
| 1.0000000000000000 | 500g           |
+--------------------+----------------+
| 1.0000000000000000 | 200g           |
+--------------------+----------------+
| 1.0000000000000000 | 500g           |
+--------------------+----------------+
| 1.0000000000000000 | 500g           |
+--------------------+----------------+
| 1.0000000000000000 | 200g           |
+--------------------+----------------+
| 1.0000000000000000 | 200g           |
+--------------------+----------------+
| 4.0000000000000000 | 100g           |
+--------------------+----------------+
| 4.0000000000000000 | 125g           |
+--------------------+----------------+
| 4.0000000000000000 | 125g           |
+--------------------+----------------+
| 1.0000000000000000 | 500g           |
+--------------------+----------------+

我需要将它们相乘以获得结果以替换 PackWeight/Vol ie Units/Pk * PackWeight/Vol AS PackWeight/Vol 中的值。我该怎么做呢？即单位为4的结果分别为400g、500g、500g。其他结果将保持不变，因为单位为 1。单位/Pk 是数字，包装重量是 nvarchar。谢谢！

sql-server t-sql
Martin Hope
jhowe
Asked: 2015-01-21 05:55:45 +0800 CST
  • 0

我已经搜索过问题，但似乎不适用于我的场景。请参阅下面的附加代码。我正在尝试连接此数据的两个子集，但得到重复项/太多行，请参阅主要查询和主要结果下方的子集（我希望您可以通过提供没有表定义的数据/示例来理解，因为表很大）（请请原谅格式化，我不知道如何排列这里的所有内容）：

IF OBJECT_ID(N'tempdb..#TRANS', N'U') IS NOT NULL
    BEGIN
        DROP TABLE #TRANS;
    END

DECLARE @Item NVARCHAR(6) = 'AAS682' ,
    @ExpiryDate AS DATETIME = '2015-01-10 00:00:00.000'

SELECT  ITO.REFERENCEID ,
        WP.PRODID ,
        WP.WMSPALLETID ,
        ITR.ITEMID ,
        ITR.QTY ,
        ITR.STATUSISSUE ,
        ITR.STATUSRECEIPT ,
        ITO.REFERENCECATEGORY ,
        PT.USEBYDATE
INTO    #TRANS
FROM    dbo.INVENTTRANS AS ITR
        JOIN INVENTDIM AS ID ON ID.INVENTDIMID = ITR.INVENTDIMID
        JOIN dbo.WMSPALLET AS WP ON WP.WMSPALLETID = ID.WMSPALLETID
        JOIN dbo.PRODTABLE AS PT ON PT.PRODID = WP.PRODID
        JOIN dbo.INVENTTRANSORIGIN AS ITO ON ITR.INVENTTRANSORIGIN = ITO.RECID
WHERE   ITO.REFERENCECATEGORY IN ( 0, 2, 8 )
        AND PT.USEBYDATE = @ExpiryDate;

SELECT * FROM #TRANS

这给了我：

REFERENCEID PRODID  WMSPALLETID ITEMID  QTY STATUSISSUE STATUSRECEIPT   REFERENCECATEGORY   USEBYDATE
M0000042    M0000042    1288390 BAS242  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000042    M0000042    1288391 BAS242  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000042    M0000042    1288392 BAS242  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000042    M0000042    1288393 BAS242  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000042    M0000042    1288394 BAS242  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000043    M0000043    1288395 BAS243  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000043    M0000043    1288396 BAS243  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000043    M0000043    1288397 BAS243  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000043    M0000043    1288398 BAS243  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000043    M0000043    1288399 BAS243  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000044    1288400 AAS682  168.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000044    1288401 AAS682  168.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000044    1288402 AAS682  168.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000044    1288403 AAS682  168.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000044    1288404 AAS682  168.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000042    1288390 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288391 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288392 BAS242  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288395 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288396 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288397 BAS243  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    2   0   0   2015-01-10 00:00:00.000

我想加入这两个查询结果集

WITH    ACODE
          AS ( SELECT   *
               FROM     #TRANS
               WHERE    ITEMID = @Item
                        AND STATUSISSUE IN ( 1, 2 )
             )
    SELECT  *
    FROM    ACODE
SELECT  *
FROM    #TRANS
WHERE   REFERENCECATEGORY = 8

REFERENCEID PRODID  WMSPALLETID ITEMID  QTY STATUSISSUE STATUSRECEIPT   REFERENCECATEGORY   USEBYDATE
0013949 M0000044    1288400 AAS682  -32.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    2   0   0   2015-01-10 00:00:00.000

有了这个

REFERENCEID PRODID  WMSPALLETID ITEMID  QTY STATUSISSUE STATUSRECEIPT   REFERENCECATEGORY   USEBYDATE
M0000044    M0000042    1288390 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288391 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288392 BAS242  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288395 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288396 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288397 BAS243  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000

我试过了

SELECT  ACODE.REFERENCEID ,
            ACODE.PRODID ,
            ACODE.WMSPALLETID ,
            ACODE.ITEMID ,
            ACODE.QTY ,
            TRANS.REFERENCEID ,
            TRANS.PRODID ,
            TRANS.WMSPALLETID ,
            TRANS.ITEMID ,
            TRANS.QTY
    FROM    ACODE
            CROSS APPLY ( SELECT    REFERENCEID ,
                                    PRODID ,
                                    WMSPALLETID ,
                                    ITEMID ,
                                    QTY
                          FROM      #TRANS
                          WHERE     ACODE.PRODID != #TRANS.PRODID
                                    AND REFERENCECATEGORY = 8
                        ) TRANS

REFERENCEID PRODID  WMSPALLETID ITEMID  QTY REFERENCEID PRODID  WMSPALLETID ITEMID  QTY
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000043    1288397 BAS243  -50.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000043    1288397 BAS243  -50.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000043    1288397 BAS243  -50.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000043    1288397 BAS243  -50.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000043    1288397 BAS243  -50.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000043    1288397 BAS243  -50.0000000000000000

但这给了我 6 * 6 36 行...我只想将正确的查询连接到左侧查询的末尾。完全像一个工会

SELECT  *
                        FROM    ACODE
                        UNION
                        ( SELECT    *
                          FROM      #TRANS
                          WHERE     REFERENCECATEGORY = 8
                        )

REFERENCEID PRODID  WMSPALLETID ITEMID  QTY STATUSISSUE STATUSRECEIPT   REFERENCECATEGORY   USEBYDATE
0013949 M0000044    1288400 AAS682  -32.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    2   0   0   2015-01-10 00:00:00.000
M0000044    M0000042    1288390 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288391 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288392 BAS242  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288395 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288396 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288397 BAS243  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000

除了附加到查询右侧的底部 6 行...基本上，“B”项构成了“A”项。这是这份报告的目的，但所有数据都存在于同一张表中，这正是我真正苦苦挣扎的……谢谢！

sql-server-2008-r2
Martin Hope
jhowe
Asked: 2014-06-05 04:35:21 +0800 CST
  • 0

我正在构建一个教育数据仓库。我有各种维度，我想跟踪对即 DimStudent、DimClass、DimCollege、DimExamPaper 等的更改。我的解决方案目前在加载暂存表之前删除键、截断表并重新创建键，然后继续加载维度表和事实表.

因此，我的问题是我需要进行的最小物理更改是什么——> 在加载之前截断暂存表，跟踪暂存表、昏暗表和事实表之间的变化，以将行增量加载到昏暗表和事实表？我是否需要添加一个或多个表来跟踪每个维度或事实表的更改？我看过示例，但似乎只需要一张桌子就可以完成大量工作。我有 11 个维度、4 个事实表和 22 个登台表。

sql-server sql-server-2012
Martin Hope
jhowe
Asked: 2014-05-16 05:02:46 +0800 CST
  • 1

我目前正在构建一个教育数据仓库。

由于我没有衡量标准（我没有汇总任何内容），我有三个针对不同成绩集的无事实事实表，这些成绩彼此完全无关，因此它们位于不同的表中。我只是按成绩事实表对学生维度进行切片。

在 Excel 中，当我将一个学生拖到一行上，然后在列中评分时，我希望看到该学生的成绩。但是，我只看到所有学生/所有成绩。即使我可以清楚地看到表格图视图中的关系，这些关系似乎也不起作用。

关于为什么会这样的任何想法？

如下所示，SSAS 已导入关系；但是，当我在 Excel 中浏览时，数据之间似乎没有任何关系。如果我按一行筛选学生并选择成绩，我只会得到所有成绩，而不仅仅是与该学生相关的成绩。

在此处输入图像描述

在此处输入图像描述 在此处输入图像描述

如果我将学生键放在行部分中并将成绩放在列部分中，它将只显示全部/全部。如果我更改学生键过滤器以显示一些记录，似乎没有任何改变，所以 SSAS 目前对我来说相当无用。

在此处输入图像描述

sql-server sql-server-2012
Martin Hope
jhowe
Asked: 2014-04-15 12:34:17 +0800 CST
  • 2

我在数据仓库的设计阶段苦苦挣扎。这不是您在 MSDN 或所有书籍等上找到的传统销售示例。我的任务是创建一个教育数据仓库，用于跟踪学生随时间的进步。Kimball 的书中也没有与之相匹配的内容。到目前为止，这是我想出的。我正在遵循最佳实践，即在每个表上创建主身份密钥并包括业务密钥，我可能会尝试根据数据质量创建唯一密钥（是否可以保持参照完整性）

昏学生

  • 学生DwKey
  • 学生商务钥匙

昏暗的主题

  • SubjectDwKey
  • SubjectBizKey

昏暗的时间或日期

  • 日期键

不同格式的日期、季度等。

昏暗学院

  • 大学密钥
  • 大学商务钥匙

DimTeacher 或导师

  • 老师DwKey
  • 教师商务钥匙

事实成绩

  • FK_StudentDwKey
  • FK_SubjectDwKey
  • FK_CollegeDwKey
  • FK_TeacherDwKey
  • 等级

（显然维度表中的字段多了很多，只是为了简单起见）

Student 和 Subject 都具有相同的业务密钥。现在我真的在为事实表苦苦挣扎。由于某种原因，考试成绩存储在 Subject 业务表中。我创建的 ETL 将成绩从主题表中拆分出来，因此 DimSubject 仅包含主题，而 FactGrades 仅包含带有来自其他表的外键的成绩。我遇到的问题是关系以及我是否需要创建多个事实表。还有其他他们想要分析的成绩存储在不同的地方，例如预测成绩或入学考试成绩。那么我应该为每个事实表创建一个事实表，还是尝试将它们全部合并到一个事实表中？此外，如果关系由业务键定义，我不应该在业务键而不是代理键之间创建外键关系吗？简而言之。

  • 我是通过新创建的键还是业务键来定义关系？
  • 如果它们位于业务数据库的不同部分，我是为每种类型的成绩创建多个事实表，还是将它们全部放在一个表中。
  • 我可以将一个维度链接到多个事实表吗，或者这会导致重复问题吗？
  • 我如何定义与日期维度的关系，这种关系应该在事实表和我加入的对象之间吗？我有一个考试开始日期和结束日期，但其他年级类型会有不同的日期。
sql-server database-design
Martin Hope
jhowe
Asked: 2014-01-17 06:15:13 +0800 CST
  • 3

大家好，正在尝试转换

2014 年 1 月 16 日 --> 2014 年 1 月 16 日。有任何想法吗？？？我试图避免可怕的表达？简：1？FEB : 2 等等等等。

SSMS 会很乐意执行 SELECT MONTH('16JAN2014') 来获取月数，但是 MONTH("16JAN2014") 在 SSIS 中不起作用。

嗯...尝试了代码，但下面不断出现错误...

在此处输入图像描述

ssis ssis-expressions
Martin Hope
jhowe
Asked: 2014-01-09 06:07:17 +0800 CST
  • 2

我正在尝试创建一个强大的脚本来运行备份、备份当前证书、创建新证书、备份新证书并使用新证书重新生成数据库加密密钥。显然，要完成这一切，您所谈论的是一个相当复杂的脚本！我试图让它尽可能健壮，但是在运行脚本时，数据库卡在加密状态 4 中。（这之前发生过，这就是为什么我要测试它以破坏它。）现在在我删除和重新创建之前这些数据库有什么办法可以强制它们退出状态 4？它不允许您关闭加密，您会收到以下错误：加密、解密或密钥更改扫描正在进行时无法禁用数据库加密。

我不确定发生了什么让他们进入这种状态，但我想不惜一切代价阻止它。

请看我的脚本。您应该能够通过创建几个数据库来轻松测试这一点。

任何改进都将不胜感激，这对 TDE 环境中的任何人都非常有用。

-- *** CREATE CERTIFICATE FROM CERTIFICATE BACKUPS ON LOCAL MACHINE IF REQUIRED ***
------------------------------------------------------------------------------------------------------------------------------------------------------
--USE master;
--  CREATE CERTIFICATE PCI_Compliance_Certificate_201301032240 
--      FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PCI_Compliance_Certificate_201301032240.cer' 
--      WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PCI_Compliance_Certificate_201301032240.pvk', 
--      DECRYPTION BY PASSWORD = '*** SERVER MASTER KEY ***');
--  GO
------------------------------------------------------------------------------------------------------------------------------------------------------

 --This script will create new certificates, back them up, back up your old certificates and rotate 
 --the encryption using your new key. This is ideal in PCI DSS and other regulated environments 
 --where regular key rotation is neccessary. The script will determine which databases are encrypted
 --on your server instance and only re-generate the encryption for them. 

--Backup databases on server

 USE master

 DECLARE @Name NVARCHAR(50) , -- Database Name
    @Path NVARCHAR(100) , -- Path for backup files
    @FileName NVARCHAR(256) , -- Filename for backup
    @FileDate NVARCHAR(20) , -- Used for file name
    @BackupSetName NVARCHAR(50) ,
    @SQLScript NVARCHAR(MAX) ,
    @CurrentCertificateName AS NVARCHAR(100) ,
    @CertificateBackupFile AS NVARCHAR(256) ,
    @KeyBackup AS NVARCHAR(256) ,
    @KeyStore AS NVARCHAR(256) = 'E:\SQL Data\Local Backups\' ,
    @SecurePass AS NVARCHAR(MAX) = '*** PASSWORD ***' ,
    @Live AS NCHAR(3) = 'No'
 -- *** MAKE SURE YOU CHECK THIS BEFORE RUNNING ***


--   specify database backup directory
 SET @Path = 'E:\SQL Data\Local Backups\'

--   specify filename format
 SET @FileDate = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 120),
                                         ':', ''), '-', ''), ' ', '')

 IF CURSOR_STATUS('global', 'db_cursor') >= -1 
    DEALLOCATE db_cursor
 DECLARE db_cursor CURSOR
 FOR
    SELECT  Name
    FROM    sys.databases
    WHERE   Name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
            AND is_encrypted = 1

 OPEN db_cursor   
 FETCH NEXT FROM db_cursor INTO @Name   

 WHILE @@FETCH_STATUS = 0 
    BEGIN TRY

        SET @FileName = @Path + @Name + '_' + @FileDate + '.bak'  
        SET @SQLScript = 'BACKUP DATABASE ' + @Name + ' TO DISK = '''
            + @FileName + ''' WITH NOFORMAT, INIT, SKIP, STATS = 10
        RESTORE VERIFYONLY FROM  DISK = ''' + @FileName
            + ''' IF (SELECT recovery_model_desc FROM sys.databases WHERE Name = '
            + CHAR(39) + @Name + CHAR(39) + ') = ''FULL'' BACKUP LOG ' + @Name
            + ' TO DISK = ''' + @Path + @Name + '_log.ldf'''
        PRINT '*** STEP ONE Backing up Databases ***'
        PRINT @SQLScript

        IF @Live = 'Yes' 
            EXEC (@SQLScript)
        FETCH NEXT FROM db_cursor INTO @Name

    END TRY

    BEGIN CATCH 
        PRINT 'Error Completing Backups' 
        SELECT  ERROR_NUMBER() AS ErrorNumber ,
                ERROR_SEVERITY() AS ErrorSeverity ,
                ERROR_STATE() AS ErrorState ,
                ERROR_PROCEDURE() AS ErrorProcedure ,
                ERROR_LINE() AS ErrorLine ,
                ERROR_MESSAGE() AS ErrorMessage;
        RETURN
    END CATCH

 CLOSE db_cursor
 DEALLOCATE db_cursor  

 PRINT '*** STEP ONE Complete ***'

-- Get current certificate statuses
 SELECT DB_NAME(database_id) AS DatabaseName ,
        Name AS CertificateName ,
        CASE encryption_state
          WHEN 0 THEN 'No database encryption key present, no encryption'
          WHEN 1 THEN 'Unencrypted'
          WHEN 2 THEN 'Encryption in progress'
          WHEN 3 THEN 'Encrypted'
          WHEN 4 THEN 'Key change in progress'
          WHEN 5 THEN 'Decryption in progress'
        END AS encryption_state_desc ,
        create_date ,
        regenerate_date ,
        modify_date ,
        set_date ,
        opened_date ,
        key_algorithm ,
        key_length ,
        encryptor_thumbprint ,
        percent_complete ,
        certificate_id ,
        principal_id ,
        pvt_key_encryption_type ,
        pvt_key_encryption_type_desc ,
        issuer_name ,
        cert_serial_number ,
        subject ,
        expiry_date ,
        start_date ,
        thumbprint ,
        pvt_key_last_backup_date
 FROM   sys.dm_database_encryption_keys AS e
        LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint

         -- TDE cannot be started while backup is running
 WHILE EXISTS ( SELECT  *
                FROM    master.dbo.sysprocesses
                WHERE   dbid IN ( DB_ID('*** DATABASE ***'))
                        AND cmd LIKE 'BACKUP%' )  
    BEGIN
        PRINT 'Waiting for backups to complete'
        WAITFOR DELAY '00:01:00'
    END

 --Code for backing up certificate and generating new certificate

-- Get current certificate name
 SELECT @CurrentCertificateName = c.name
 FROM   sys.dm_database_encryption_keys AS e
        LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint
 WHERE  DB_NAME(e.database_id) = @Name

-- backup the current certificate
 SET @CertificateBackupFile = @KeyStore + @CurrentCertificateName + '.cer'
 SET @KeyBackup = @KeyStore + @CurrentCertificateName + '.pvk'

 SET @SQLScript = 'BACKUP CERTIFICATE ' + @CurrentCertificateName
    + +' TO FILE = ''' + @CertificateBackupFile + ''' WITH PRIVATE KEY'
    + ' (FILE = ''' + @KeyBackup + ''',' + ' ENCRYPTION BY PASSWORD = '''
    + @SecurePass + ''')'

 PRINT '*** STEP TWO Backing up current certificate: ' + @SQLScript + ' ***'

 IF @Live = 'Yes' 
    BEGIN TRY

        EXEC ( @SQLScript )

    END TRY
    BEGIN CATCH
        PRINT 'Could not back up existing Certificate. Job Cancelled'
        SELECT  ERROR_NUMBER() AS ErrorNumber ,
                ERROR_SEVERITY() AS ErrorSeverity ,
                ERROR_STATE() AS ErrorState ,
                ERROR_PROCEDURE() AS ErrorProcedure ,
                ERROR_LINE() AS ErrorLine ,
                ERROR_MESSAGE() AS ErrorMessage;
        RETURN
    END CATCH

 PRINT '*** STEP TWO Complete ***'

-- Generate the new certificate.
 DECLARE @Now AS NVARCHAR(12) = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 120),
                                                        ':', ''), '-', ''),
                                        ' ', '')
 DECLARE @NewCertificateName AS NVARCHAR(50) = 'PCI_Compliance_Certificate_'
    + @Now

 -- Manually set certificate name
 --SELECT @NewCertificateName = 'PCI_Compliance_Certificate_201312231546'

-- Generate a new certificate
 DECLARE @NewCertificateDescription AS NVARCHAR(100) = 'PCI DSS Compliance Certificate for 2014'

 SET @SQLScript = 'CREATE CERTIFICATE ' + @NewCertificateName
    + ' WITH SUBJECT = ''' + @NewCertificateDescription + ''''

 PRINT '*** STEP THREE Creating New Certificate: ' + @SQLScript + ' ***'

 IF @Live = 'Yes' 
    BEGIN TRY
        EXEC ( @SQLScript
        )
    END TRY 
    BEGIN CATCH

        PRINT 'Could not create the new Certificate. Job Cancelled'
        SELECT  ERROR_NUMBER() AS ErrorNumber ,
                ERROR_SEVERITY() AS ErrorSeverity ,
                ERROR_STATE() AS ErrorState ,
                ERROR_PROCEDURE() AS ErrorProcedure ,
                ERROR_LINE() AS ErrorLine ,
                ERROR_MESSAGE() AS ErrorMessage;
        RETURN      
    END CATCH

 PRINT '*** STEP THREE Complete ***'

--  Back up the new certificate
 SET @CertificateBackupFile = @KeyStore + @NewCertificateName + '.cer'
 SET @KeyBackup = @KeyStore + @NewCertificateName + '.pvk'

 SET @SQLScript = 'BACKUP CERTIFICATE ' + @NewCertificateName
    + +' TO FILE = ''' + @CertificateBackupFile + '''' + ' WITH PRIVATE KEY'
    + ' (FILE = ''' + @KeyBackup + ''',' + ' ENCRYPTION BY PASSWORD = '''
    + @SecurePass + ''')'

 PRINT '*** STEP FOUR Backing up New Certificate: ' + @SQLScript + ' ***'

 IF @Live = 'Yes' 
    BEGIN TRY
        EXEC ( @SQLScript
        )
    END TRY
    BEGIN CATCH  
        PRINT 'Error: Could not back up New Certificate.'
        SELECT  ERROR_NUMBER() AS ErrorNumber ,
                ERROR_SEVERITY() AS ErrorSeverity ,
                ERROR_STATE() AS ErrorState ,
                ERROR_PROCEDURE() AS ErrorProcedure ,
                ERROR_LINE() AS ErrorLine ,
                ERROR_MESSAGE() AS ErrorMessage;
        RETURN
    END CATCH

 PRINT '*** STEP FOUR Complete ***'

--Encrypt database with new certificate

 WHILE EXISTS ( SELECT  *
                FROM    master.dbo.sysprocesses
                WHERE   dbid IN ( DB_ID('*** DATABASE ***'))
                        AND cmd LIKE 'BACKUP%' ) 
    BEGIN
        PRINT 'Waiting for backups to complete'
        WAITFOR DELAY '00:01:00'
    END

 DECLARE db_cursor CURSOR
 FOR
    SELECT  Name
    FROM    sys.databases
    WHERE   Name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
            AND is_encrypted = 1

 OPEN db_cursor   
 FETCH NEXT FROM db_cursor INTO @Name   

 WHILE @@FETCH_STATUS = 0 
    BEGIN TRY
        SET @SQLScript = 'USE ' + @Name
            + ' ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE '
            + 'PCI_Compliance_Certificate_' + @Now
        PRINT '*** STEP FIVE Encrypting Databases ***'
        PRINT @SQLScript

        IF @Live = 'Yes' 
            EXEC (@SQLScript)
        FETCH NEXT FROM db_cursor INTO @Name

    END TRY

    BEGIN CATCH 
        PRINT 'Error Encrypting Databases' 
        SELECT  ERROR_NUMBER() AS ErrorNumber ,
                ERROR_SEVERITY() AS ErrorSeverity ,
                ERROR_STATE() AS ErrorState ,
                ERROR_PROCEDURE() AS ErrorProcedure ,
                ERROR_LINE() AS ErrorLine ,
                ERROR_MESSAGE() AS ErrorMessage;
        RETURN
    END CATCH

 CLOSE db_cursor
 DEALLOCATE db_cursor  

 PRINT '*** STEP FIVE Complete ***'

 -- Inspect the new state of the databases
 SELECT DB_NAME(e.database_id) AS DatabaseName ,
        e.database_id ,
        e.encryption_state ,
        CASE e.encryption_state
          WHEN 0 THEN 'No database encryption key present, no encryption'
          WHEN 1 THEN 'Unencrypted'
          WHEN 2 THEN 'Encryption in progress'
          WHEN 3 THEN 'Encrypted'
          WHEN 4 THEN 'Key change in progress'
          WHEN 5 THEN 'Decryption in progress'
        END AS encryption_state_desc ,
        c.name ,
        e.percent_complete
 FROM   sys.dm_database_encryption_keys AS e
        LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint
sql-server t-sql