Geoff Dawdy's questions

我有太多服务器要管理，无法在每台服务器上手动执行此操作，因此我想以某种方式使用 dbatools 模块和以下用于收集备份集信息的查询来自动执行此操作。

  WITH BackupsSize AS(
  SELECT TOP 1000
         rn = ROW_NUMBER() OVER (ORDER BY DATEPART(year,[backup_start_date]) ASC, DATEPART(month,[backup_start_date]) ASC)
    , [Year]  = DATEPART(year,[backup_start_date])
    , [Month] = DATEPART(month,[backup_start_date])
    , [Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([backup_size]/1024/1024/1024),4))
    , [Compressed Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([compressed_backup_size]/1024/1024/1024),4))
FROM 
    msdb.dbo.backupset
WHERE 
    [database_name] = N'A1000370D1'
AND [type] = 'D'
AND backup_start_date BETWEEN DATEADD(mm, - 13, GETDATE()) AND GETDATE()
GROUP BY 
    [database_name]
    , DATEPART(yyyy,[backup_start_date])
    , DATEPART(mm, [backup_start_date])
ORDER BY [Year],[Month]) 
--SECTION 1 END
 
--SECTION 2 BEGIN
SELECT 
   b.Year,
   b.Month,
   b.[Backup Size GB],
   0 AS deltaNormal,
   b.[Compressed Backup Size GB],
   0 AS deltaCompressed
FROM BackupsSize b
WHERE b.rn = 1
UNION
SELECT 
   b.Year,
   b.Month,
   b.[Backup Size GB],
   b.[Backup Size GB] - d.[Backup Size GB] AS deltaNormal,
   b.[Compressed Backup Size GB],
   b.[Compressed Backup Size GB] - d.[Compressed Backup Size GB] AS deltaCompressed
FROM BackupsSize b
CROSS APPLY (
   SELECT bs.[Backup Size GB],bs.[Compressed Backup Size GB]
   FROM BackupsSize bs
   WHERE bs.rn = b.rn - 1
) AS d

我有一个位于数据库表中的服务器列表，并计划使用 invoke-dbaquery 将一个 $sqlinstance 变量与我的实例列表一起加载。

我不确定并且需要帮助的是如何使用上述 SQL 脚本一次性收集所有服务器上所有数据库的历史备份数据。如何在遍历每个数据库的同时也遍历我的实例列表？

我正在尝试在我们的一个环境中收集所有 sql 实例的所有 dbfile 信息。这是一个相当大的环境，一次执行一个实例会很乏味。我认为使用 Powershell 会有一种简单的方法来做到这一点，但我似乎遇到了障碍。

我有一个存储在表/视图 [vSQLInstances] 中的实例列表。如果我将这些值传递给一个变量，然后将该变量用作 SQLInstance 参数，则会出现错误。

我正在使用的脚本...

$sqlinstance = Invoke-DbaQuery -SqlInstance dbasql01\sql2019 -Database DBAEstate -Query "select sqlinstance from vSQLInstance;"

Get-DbadbFile -SqlInstance $sqlinstance | Select-Object sqlinstance, database, logicalname, size 

我收到的错误：

Get-DbaDbFile : Cannot process argument transformation on parameter 'SqlInstance'. Cannot convert value 
"@{sqlinstance=PRODSQLD\D}" to type "Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter". Error: "Failed to interpret 
input as Instance: @{sqlinstance=PRODSQLD\D}"
At line:11 char:28
+ Get-DbaDbFile -SqlInstance $instancelist
+                            ~~~~~~~~~~~~~
+ CategoryInfo          : InvalidData: (:) [Get-DbaDbFile], ParameterBindingArgumentTransformationException
+ FullyQualifiedErrorId : ParameterArgumentTransformationError,Get-DbaDbFile

我有一个表，我试图只返回 rendition_type Interactive 的记录，而没有其他关联的 rendition_type。如果记录有多个 rendition_type，我需要将其从列表中排除。

DOC_ID    RENDITION_ID    RENDITION_TYPE
001       001             Interactive
001       002             Web PDF
002       003             Interactive
003       004             Interactive
003       005             Print PDF
004       006             Interactive
005       007             Interactive
005       008             Web PDF

我正在寻找一个查询，该查询将仅返回 document_ID 的 Rendition_type 类型仅为 1 且该类型为 Interactive 的行。

我有一个最近从 Oracle 迁移到 SQL Server 2017 的数据库。最初设置数据库的人将所有外键设置为系统生成的名称。换句话说，如果我查看表中的外键，它们被命名为 ( SYS_C005214, SYS_C005271, SYS_C005272)。我希望将这些都重命名为：

FK_ChildTable_childColumn_ParentTable_parentColumn

我想设置一个脚本，将所有外键更改为适当的名称。有没有办法做到这一点，而不必进入每个键来查看它的定义，然后手动重命名它？

注意：我现在意识到在运行 Aaron Bertrand 的脚本后，我也需要这个作为主键。

我在 Oracle db 上有一个列，其中包含带有嵌入图像的 HTML 数据，导致该列的某些记录的大小超过 2GB。我已经成功地将数据库的其余部分迁移到 SQL Server，除了这个 CLOB 列。由于 SQL Server varchar(max) 无法处理超过 2GB 的大小，我有什么选择？

我有一份报告，我需要从日期时间列中删除时间。我发现这个表达式可以工作，但它会将所有空值更改为 '1/1/0001'

=FormatDateTime(Fields!Temperature_Control_Inspection_Date.Value,DateFormat.ShortDate)

我已经尝试使用下面的表达式来查找空值，但它似乎不起作用。

=IIF(Fields!Temperature_Control_Inspection_Date is nothing, nothing, FormatDateTime(Fields!Temperature_Control_Inspection_Date.Value,DateFormat.ShortDate))

如何仅显示 DateTime 列中的日期，而不将所有空值都替换为“1/1/0001”？

我最近继承了一个从旧的 MS Access 数据库转换而来的 SQL Server 2016 数据库。我有一个表格，其中显示了相关序列号的各种维修日期和测试日期。我希望每个序列号有一行显示所有最近的日期。表格现在根据输入数据库的不同日期显示每个序列号的多行。

维修和配置日期表：

+------+-------------+-----------------+---------------+
| S/N  | REPAIR_DATE | INSPECTION_DATE | REPLACED_DATE |
+------+-------------+-----------------+---------------+
| 1001 | 2017-11-17  | 2017-11-17      | NULL          |
| 1002 | NULL        | NULL            | NULL          |
| 1002 | 2016-11-21  | 2016-11-21      | NULL          |
| 1004 | NULL        | NULL            | NULL          |
| 1004 | NULL        | 2017-03-28      | 2017-09-07    |
| 1004 | 2017-12-15  | NULL            | NULL          |
+------+-------------+-----------------+---------------+

期望的输出

+------+-------------+-----------------+---------------+
| S/N  | REPAIR_DATE | INSPECTION_DATE | REPLACED_DATE |
+------+-------------+-----------------+---------------+
| 1001 | 2017-11-17  | 2017-11-17      | NULL          |
| 1002 | 2016-11-21  | 2016-11-21      | NULL          |
| 1004 | 2017-12-15  | 2017-03-28      | 2017-09-07    |
+------+-------------+-----------------+---------------+

如何合并表格结果，以便只有一行序列号显示每列的最新日期？

我需要从此表中删除超过 30 天的记录。

我们在这个表中只有 2 列。“Printed_%”之后的第一个日期是我将用来确定是否应删除记录的日期。请注意，有些日期采用 MM/DD/YYYY 格式，而有些则以 M/D/YYYY 格式出现在字符串中。如何提取日期以便确定哪些行超过 30 天？

ID      Command
123232  Printed_9/6/2017 2:36:09 PM W;03;2K;8/6/2013;8/6/2015;ODSI3498
123233  Printed_9/6/2017 2:53:20 PM W;07;2K;8/6/2013;8/6/2015;PWEOIR123
123234  Printed_9/6/2017 2:54:44 PM W;15;2K;11/9/2011;11/18/2005;PWEOIR123
123235  Printed_10/21/2016 10:21:10 AM W;15;2K;11/9/2011;11/18/2005;ODSI3498
123236  Printed_11/15/2015 10:30:13 AM W;02;2K;8/7/2013;8/7/2014;ODSI3498
123237  Printed_12/7/2014 10:30:26 AM W;21;2K;7/30/2013;7/30/2014;ODSI3498

我正在尝试在 SQL 代理作业 (SQL Server 2016) 中设置 PowerShell 脚本。我正在使用第三方模块 DBATOOLS.psd1，这似乎是我遇到语法错误的地方。目前我的 SQL 作业有一行代码失败：

Import-Module e:\dbatools-master\dbatools-master\dbatools.psd1

为什么作业失败并出现以下错误？当我通过 PowerShell ISE 运行此命令时，它运行良好。

Error Message: Unable to start execution of step 1 (reason: line(5): Syntax error).  The step failed.

我需要从备份中恢复一个表。我的理解是将单个表返回到数据库中的唯一方法是将其复制到临时表中。然后删除旧表并将新表重命名为旧名称。由于主键列是身份列，我的问题出现在这个特定步骤中。该表还有多个外键约束。如何将备份数据库中的一张表复制到原始数据库中，并仍然保留主键标识列和所有外部约束？

这是我当前的脚本尝试：

CREATE TABLE dbo.Incidents_tmp
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[IncidentAction_ID] [int] NULL,
[IncidentCompromise_ID] [int] NULL,
[IncidentDamage_ID] [int] NULL,
[IncidentReportingEntity_ID] [int] NULL,
[IncidentStatus_ID] [int] NULL,
[IncidentType_ID] [int] NULL,
[IncidentID] [varchar](20) NULL,
[Version] [timestamp] NOT NULL,
 CONSTRAINT [PK_Incidents_tmp] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Incidents_tmp ON
GO

IF EXISTS ( SELECT * 
        FROM DB_old.dbo.Incidents )
INSERT INTO DB_new.dbo.Incidents_tmp 
        ( 
        ID ,
        IncidentAction_ID ,
        IncidentCompromise_ID ,
        IncidentDamage_ID ,
        IncidentReportingEntity_ID ,
        IncidentStatus_ID ,
        IncidentType_ID ,
        IncidentID 
        )
    SELECT 
    ID ,
    IncidentAction_ID ,
    IncidentCompromise_ID ,
    IncidentDamage_ID ,
    IncidentReportingEntity_ID ,
    IncidentStatus_ID ,
    IncidentType_ID ,
    IncidentID ,
    FROM DB_old.dbo.Incidents TABLOCKX
GO

SET IDENTITY_INSERT DB_new.dbo.Incidents_tmp OFF
GO

ALTER TABLE dbo.IncidentSubject DROP CONSTRAINT 
FK_IncidentSubject_Incidents;
ALTER TABLE dbo.IncidentsReportLog DROP CONSTRAINT 
FK_IncidentsReportLog_Incidents;
ALTER TABLE dbo.Incidents DROP CONSTRAINT PK_Incidents;

DROP TABLE DB_new.dbo.Incidents
GO

Exec sp_rename 'Incidents_tmp', 'Incidents'

Exec sp_rename 'PK_incidents_tmp', 'PK_Incidents'


ALTER TABLE [dbo].[IncidentSubject]  WITH CHECK ADD  CONSTRAINT 
[FK_IncidentSubject_Incidents] FOREIGN KEY([Incident_ID])
REFERENCES [dbo].[Incidents] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[IncidentSubject] CHECK CONSTRAINT 
[FK_IncidentSubject_Incidents]
GO

当我尝试重新添加约束时，我收到一条错误消息：

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_IncidentSubject_Incidents". The conflict occurred in database "DB_new", table "dbo.Incidents", column 'ID'.

编辑：一些额外的信息。该应用程序的用户不小心删除了 175 个事件。不幸的是，她直到两周后才告诉我们，直到将近一个月后才将其发送给我们。用户一直在访问应用程序并进行更改，因此不能选择完整的数据库还原。用户请求仅恢复已删除的事件。我查看了数据库并注意到一个事件表，其中包含当前数据库中缺失的数据以及备份数据库中存在的 175 条记录。希望这能提供一些更好的背景来说明我为什么要尝试只恢复一个事件表。它开始看起来需要恢复其他表。

我们正在尝试清理我们的 AlwaysOn 集群之一上的一些旧帐户。

这个特定的帐户拒绝玩得很好，并允许自己被删除。

USE [master]
GO
DROP LOGIN [PROD\dba007]
GO

错误：

Msg 15173, Level 16, State 1, Line 4
Server principal 'PROD\dba007' has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

它抱怨的权限是对 HADR_ENDPOINT 的连接权限

Select perm.* from sys.server_permissions  perm
INNER JOIN sys.server_principals prin ON perm.grantor_principal_id = prin.principal_id
where prin.name = N'PROD\dba007'

class   class_desc  type    permission_name state   state_desc
105     ENDPOINT    CO      CONNECT         G       GRANT

现在下一个合乎逻辑的事情是撤销连接权限。

USE master;
REVOKE CONNECT ON ENDPOINT ::HADR_endpoint from [PROD\dba007]

但这会产生消息

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

它所属的唯一服务器角色是 PUBLIC。

我怎样才能找出究竟是什么阻止我撤销这些权限，以便我可以删除用户？

谢谢。

我对 PowerShell 还是很陌生，所以我不确定我可以采取哪些故障排除步骤来解决这个问题。我想使用下面的命令来确定副本是否是主副本。

我相信我已经成功加载了提供者和 smo。Intellisense 似乎可以识别该命令。

如果我运行以下命令

Test-SqlAvailabilityReplica

该脚本将运行但永远不会完成。我在其中一个副本上运行它，所以我假设它应该只是抓住本地主机。如果我添加 -WhatIf 标志，它仍然会在没有完成的情况下运行。

我也试过 Test-SqlAvailabilityGroup 但它也从未完成。

我该怎么做才能使此命令起作用？我如何找出导致它锁定的原因？

编辑：看起来我的问题出在路径上......这个路径位置有效

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabilityGroups\Fo‌​oAg\AvailabilityRepl‌​icas\ 

但后来我尝试使用这个路径位置但它失败了。

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabilityGroups\Fo‌​oAg\AvailabilityRepl‌​icas\MainReplica

编辑2：

我们确定它是

<ServerName> 

在最后？我可以将我的位置设置为

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabiltyGroups\FooAg\AvailabilityReplicas\ 

正好。当我执行 DIR 以查看可用内容时，我看到我的两个副本列为

"FooNode1\FooInstance" 

和

"FooNode2\FooInstance" 

那么对于路径的“MainReplica”部分，我应该用 FooNode1 还是 FooNode1\FooInstance 替换它？

根据这篇 Technet 文章，我需要：

使用 .NET Framework 提供的自动生成功能生成验证密钥和解密密钥。

该步骤不解释如何生成验证密钥和解密密钥。

不幸的是，我有一些不良数据，需要解决它。我在电子表格中有一个包裹列，其中包含一些无意中转换为浮点数的包裹。我发布了一个问题来解决这个问题处理混合 varchar 和 float 数据类型的 sql 列 但是，现在当我尝试以下代码时出现错误：

SELECT CAST(CAST(CAST([Parcel Number 2(max)] AS FLOAT) AS DECIMAL) AS NVARCHAR(255))
FROM XLSIMPORT WHERE [Parcel Number 2(max)] = '1.4515020001e+012'

我得到的错误是：

Msg 8115, Level 16, State 6, Line 41
Arithmetic overflow error converting float to data type numeric.    

我有一个要从数据库中删除的证书。

如果我发出命令

DROP CERTIFICATE <FooCert>

我得到错误

The certificate cannot be dropped because one or more entities are either signed or encrypted using it

根据 Jason Strate 的说法，我应该能够找出证书签名的内容。

以下查询返回 0 行：

SELECT OBJECT_SCHEMA_NAME(co.major_id) + '.' + OBJECT_NAME(co.major_id)
FROM sys.certificates c 
INNER JOIN sys.crypt_properties co ON c.thumbprint = co.thumbprint
WHERE co.crypt_type_desc = 'SIGNATURE BY CERTIFICATE' 
AND c.name = 'FooCert'

我还尝试根据这个 SO question 将实体解耦。 https://stackoverflow.com/questions/52460/how-do-i-find-and-decouple-entities-from-a-certificate-when-upgrading-ms-sqlserv

如何删除此证书的依赖项以便删除它？

我有一个需要在 SQL Server 中更新的地址列表。我怎样才能将 Suite # 添加到缺少它的地址？

Address
1 Burton Hills Blvd, Suite 250
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Ford Pl
1 Ford Pl, Suite 5B
1 Kish Dr
1 Park Center Dr, Suite 11
1 Park Center Dr

我正在尝试解决使邮政编码正确显示的一些困难。

原始电子表格包含 5 位和 9 位混合格式的邮政编码。在导入过程之后，这些 9 位邮政编码报告的长度为 12 位。现在，当我尝试在 9 位邮政编码中添加连字符“-”时，由于长度错误和各种数据类型转换问题，我得到了异常结果和错误。

使用用于从电子表格导入数据的 openrowset 方法执行导入。

当我查询新导入的数据时，我看到邮政编码显示与电子表格中的相同，但长度错误。

SELECT ZIP,
    LEN(LTRIM(RTRIM(Zip))) AS ZIPLENGTH
  FROM XLS_IMPORT

ZIP         ZIPLENGTH
45750       5
432013256   12
441153221   12
44120       5
441351362   12

如果我选择数据的左 9 个字符，所有内容都会转换为浮点数，并且邮政编码现在不可读。

SELECT LEFT(ZIP,9) FROM XLS_IMPORT
WHERE LEN(LTRIM(RTRIM(ZIP))) = 12

ZIP
4.32013e+
4.42034e+
4.56637e+
4.41153e+
4.36045e+
4.41133e+

如何将这些邮政编码恢复为正确的 9 位数字？或者如何在报告长度为 12 的 9 位邮政编码中添加连字符？我的最终目标是简单地让 9 位邮政编码在中间有一个连字符。

该Zip列的数据类型是float.

我刚刚发现我的一些电子表格（例如 NJ 和 NY）在邮政编码的前导 0 之前有一个撇号。我将需要研究如何处理 '0xxxx 邮政编码以使其适用于我的一些电子表格导入。

我们有一些 Excel 电子表格，其中包含属性的宗地编号，这些属性应采用标准 varchar 格式，类似于：

[Parcel # 1]          [Building Address]
038-60-171-16-000     1750 W 4th St
038-60-238-09-000     2003-2027 W 4th St
01-15393              201 5th St NE
01-13504              101 5th St SE
25-20-00-03-008.501   1800 E 5th St
36-102-0700-000       2932 S 5th St
280615                2600-B 6th St SW
31-0101.000           1202 16th St
054-259620-00.000     843 N 21st St

不幸的是，我们有一些包裹编号已转换为科学计数法。这意味着它们的包裹编号格式错误，应转换回常规的十进制或数字格式。我在转换那些具有科学记数法的值时遇到了困难。该列是 nvarchar(255) 数据类型，但需要将这些值操作回数字或十进制数据类型。

Parcel Number 1(Min)    Building Address
3.4007000221e+015   247 S Burnett Rd
3.1844401004e+013   433 N Cleveland Ave
3.4007000222e+016   2415 Lexington Ave
E020-0010-006-0211  7271 N Main St
3.6360705006e+013   825 W Market St
3.4007000353e+015   148 W North St
1.40109139e+011     4453-4461 SR-159
16-B-029-E-00-004   7314-7352 Industrial Park Blvd

使用以下 SELECT 语句检索上表

SELECT [Parcel Number 1] 
,[Building Address]
FROM XLSimport
WHERE [Parcel Number 1] like '%e%'

我的理解是，当它采用科学计数法时，我应该能够将值转换为小数。但是，其他具有合法“E”的包裹号码需要保持不变。如何解释不同的“e”值，并且仍将科学计数法的包裹编号转换回其标准包裹编号格式？

我经常遇到将 nvarchar 转换为数字错误消息的错误，我想知道这是否是因为包裹号有 E 但不是科学格式。

例如我运行这个查询：

SELECT
CASE 
    WHEN [Parcel Number 1] like '%E+%' THEN CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL)
    ELSE [Parcel Number 1]
END
FROM XLSimport

然后我收到以下错误：

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

我的一位客户有一些非常具体的需求，需要通过我创建的一些 ETL 流程来运行电子表格。

我开发了一个 SSIS .dtsx 包，它将 Excel 电子表格导入 SQL Server，然后执行一些转换过程，然后将结果导出到另一个 Excel 电子表格。我想将该工具交给客户，以便他们可以临时启动 ETL 流程。为了让他们能够运行 DTSX 软件包，在他们的系统上运行该软件包所需的最低要求是什么？这可以用 SQL Express 完成，还是他们需要运行集成服务来执行包？

由于我无法控制的原因，我必须找到解决此问题的方法。简单地重新安装实例不是一种选择。在 sys.servers 中显示为 server_id 0 的服务器名称仍显示旧的服务器名称。

运行以下命令时出现错误：

sp_dropserver 'OLD_INSTANCE'
GO
sp_addserver 'NEW_INSTANCE', Local
GO 

错误信息：

消息 15190，级别 16，状态 1，过程 sp_dropserver，第 67 行
服务器“OLD_INSTANCE”仍有远程登录或链接登录。
消息 15028，级别 16，状态 1，过程 sp_addserver，第 87 行
服务器“NEW_INSTANCE”已经存在。

最奇怪的是，远程登录是“空”登录。

exec sp_dropremotelogin @remoteserver = 'OLD_INSTANCE'
go

错误信息：

消息 15185，级别 16，状态 1，过程 sp_dropremotelogin，第 70 行
没有从远程服务器 'OLD_INSTANCE' 映射到本地用户 '(null)' 的远程用户 '(null)'。

旧实例不存在登录名。

sp_helpremotelogin 'OLD_INSTANCE'

消息 15201，级别 16，状态 1，过程 sp_helpremotelogin，第 37 行
远程服务器“OLD_INSTANCE”没有远程登录。

如果我无法删除不存在的登录名，如何重命名此实例？有没有办法刷新登录名？