主页 / user-48295

SQL Hammer's questions

Martin Hope
SQL Hammer
Asked: 2017-06-14 05:34:01 +0800 CST
  • 6

请帮我解决这个安装问题。

我正在尝试在两个 Windows Server 2012 R2 VM 上安装 SQL Server 2016（SP1 集成）故障转移群集实例 (FCI)。

我有 3 个集群磁盘。

  • 数据驱动器：E:\
  • 日志挂载点：E:\MSSQL\Log\L1
  • Tempdb 挂载点：E:\MSSQL\Data\Tempdb1

我首先尝试直接安装到这些根目录。失败后，我创建了要安装到的子目录。服务帐户和我自己是虚拟机的本地管理员，我明确授予对每个子目录的完全控制权。最后，我暂时忽略了挂载点，只安装到 E: 上的子目录。所有尝试都失败并出现以下错误。

详细结果：功能：数据库引擎服务状态：失败：查看日志了解详细信息失败原因：功能设置过程中发生错误。下一步：使用以下信息解决错误，卸载此功能，然后再次运行设置过程。组件名称：
SQL Server 数据库引擎服务实例功能组件错误代码：0x84CF0004 错误描述：更新文件夹“E:\MSSQL\Data\TempDB1\System Volume Information”的权限设置时，文件“E:\MSSQL\Data\”的权限设置更新失败TempDB1\系统卷信息\ResumeKeyFilter.Store'。文件夹权限设置应该设置为 'D:P(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A; OICI;FA;;;S-1-5-80-419818685-2113908795-3893829424-1849583840-1690709397)'。错误帮助链接： http:
//go.microsoft.com/fwlink ?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=13.0.4001.0&EvtType=0x88792597%400xBB814387&EvtType=0x88792597%400xBB8143

下面是用于安装的配置文件的匿名版本。

;SQL Server 2016 Configuration File
[OPTIONS]

ACTION="InstallFailoverCluster"
SUPPRESSPRIVACYSTATEMENTNOTICE="False"
IACCEPTROPENLICENSETERMS="False"
IAcceptSQLServerLicenseTerms="True"
ENU="True"
QUIET="False"
QUIETSIMPLE="True"
UpdateEnabled="True"
USEMICROSOFTUPDATE="False"
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,CONN,BC,SDK,SNAC_SDK
UpdateSource="MU"
HELP="False"
INDICATEPROGRESS="True"
X86="False"
INSTANCENAME="InstName"
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCEID="InstName"
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
FAILOVERCLUSTERDISKS="Cluster Disk 7"
FAILOVERCLUSTERGROUP="SQL Server (InstName)"
FAILOVERCLUSTERIPADDRESSES="IPv4;10.10.10.17;Cluster Network 2;255.255.255.0"
FAILOVERCLUSTERNETWORKNAME="abc-123-IN"
AGTSVCACCOUNT="DOMAIN\sqlsvc-abc-123"
COMMFABRICPORT="0"
COMMFABRICNETWORKLEVEL="0"
COMMFABRICENCRYPTION="0"
MATRIXCMBRICKCOMMPORT="0"
FILESTREAMLEVEL="0"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCACCOUNT="DOMAIN\sqlsvc-abc-123"
SQLSVCINSTANTFILEINIT="True"
SQLSYSADMINACCOUNTS="DOMAIN\AdminGroup"
SECURITYMODE="SQL"
SQLTEMPDBFILECOUNT="8"
SQLTEMPDBFILESIZE="8"
SQLTEMPDBFILEGROWTH="64"
SQLTEMPDBLOGFILESIZE="1024"
SQLTEMPDBLOGFILEGROWTH="128"
INSTALLSQLDATADIR="E:"
SQLBACKUPDIR="E:\MSSQL\Backup"
SQLUSERDBDIR="E:\MSSQL\Data"
SQLUSERDBLOGDIR="E:\MSSQL\Log"
SQLTEMPDBDIR="E:\MSSQL\Data"
SQLTEMPDBLOGDIR="E:\MSSQL\Data"
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$InstName"
sql-server sql-server-2016
Martin Hope
SQL Hammer
Asked: 2017-02-18 06:23:00 +0800 CST
  • 2

我在 Windows Sever 2012 R2 上运行 SQL Server 2016 SP1 (13.0.4001)。我有下表。

CREATE TABLE [SubEx].[Ingredients](
    [IngredientId] [int] IDENTITY(1,2) NOT NULL,
    [IngredientName] [nvarchar](328) NOT NULL,
    [IngredientShortDescription] [nvarchar](328) NOT NULL,
    [PortionUOMTypeId] [smallint] NULL,
    [WISRTypeId] [smallint] NOT NULL,
    [WISRSubTypeId] [smallint] NULL,
    [FlavorTypeId] [smallint] NULL,
    [StatusTypeId] [smallint] NOT NULL,
    [IsMandatory] [bit] NOT NULL,
    [IsDownloaded] [bit] NOT NULL,
    [CreatedDT] [datetime] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [LastUpdateDT] [datetime] NULL,
    [LastUpdateBy] [int] NULL,
    [Deleted] [bit] NOT NULL,
    [DeletedDT] [datetime] NULL,
    [DeletedBy] [int] NULL,
    [CreatedUserName] [nvarchar](136) NOT NULL,
    [UpdatedUserName] [nvarchar](136) NULL,
    [DeletedUserName] [nvarchar](136) NULL,
    [ValidFrom] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_Ingredients_IngredientId] PRIMARY KEY CLUSTERED 
(
    [IngredientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [PRIMARY]

GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_Ingredients_IsMandatory]  DEFAULT ((0)) FOR [IsMandatory]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_Ingredients_IsDownloaded]  DEFAULT ((0)) FOR [IsDownloaded]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_Ingredients_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_ValidFrom]  DEFAULT (dateadd(second,(-1),sysutcdatetime())) FOR [ValidFrom]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_ValidTo]  DEFAULT ('9999.12.31 23:59:59.99') FOR [ValidTo]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpFlavorTypes_FlavorTypeId] FOREIGN KEY([FlavorTypeId])
REFERENCES [SubEx].[TlkpFlavorTypes] ([FlavorTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpFlavorTypes_FlavorTypeId]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpStatusTypes_StatusTypeId] FOREIGN KEY([StatusTypeId])
REFERENCES [SubEx].[TlkpStatusTypes] ([StatusTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpStatusTypes_StatusTypeId]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpWISRCategoryTypes_WISRTypeId] FOREIGN KEY([WISRTypeId])
REFERENCES [SubEx].[TlkpWISRCategoryTypes] ([WISRTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpWISRCategoryTypes_WISRTypeId]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpWISRSubCategoryTypes_WISRSubTypeId] FOREIGN KEY([WISRSubTypeId])
REFERENCES [SubEx].[TlkpWISRSubCategoryTypes] ([WISRSubTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpWISRSubCategoryTypes_WISRSubTypeId]
GO

当我运行以下脚本为我的临时表启用 SYSTEM_VERSIONING 时，我收到以下错误消息和堆栈转储。

ALTER TABLE SubEx.Ingredients
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = SubEx.IngredientsHistory));

消息 596，级别 21，状态 1，第 8 行无法继续执行，因为会话处于终止状态。

消息 0，级别 20，状态 0，第 8 行当前命令发生严重错误。结果，如果有的话，应该被丢弃。

我是在做错什么还是需要向 Microsoft 提交错误？

sql-server sql-server-2016
Martin Hope
SQL Hammer
Asked: 2016-09-20 07:32:52 +0800 CST
  • 7

我有一个位于以下 AD 组中的 AD 登录名。

  • 域\全局组 1
  • 域\全局组 2
  • 域\全局组 3
  • 域\通用组 1

DOMAIN\UniversalGroup1 已被授予查看此 SQL Server 实例 (10.50.6000.34) 上的任何定义。用户无法查看任何数据库的定义。

DOMAIN\UniversalGroup1 的其他成员似乎能够查看定义。

EXEC xp_logininfo 'DOMAIN\user','all'

仅显示 3 个 GlobalGroups。这是xp_logininfo 过程的记录限制。

EXEC xp_logininfo 'DOMAIN\UniversalGroup1','members'

确认用户对组的成员资格。

我该如何解决这个问题？这个问题是否与全局组和通用组之间的区别有任何关系？

sql-server sql-server-2008-r2
Martin Hope
SQL Hammer
Asked: 2016-06-25 15:22:16 +0800 CST
  • 7

我有一个包含 5,000,000 条记录的表，其 DateOfBirth 列分布在 1950 年到 2007 年之间。索引的统计直方图只有两个 RANGE_HI_KEY。考虑到记录的数量和字段的选择性，我觉得直方图应该有更多的桶。

谁能向我解释为什么 SQL Server 只使用两个 RANGE_HI_KEY？

注意：我在 2014 年和 2016 年得到了相同的统计数据细分

桌子

CREATE TABLE [dbo].[Person](
    [BusinessEntityID] [INT] NOT NULL,
    [PersonType] [NCHAR](2) NOT NULL,
    [NameStyle] [dbo].[NameStyle] NOT NULL,
    [Title] [NVARCHAR](8) NULL,
    [FirstName] [dbo].[Name] NOT NULL,
    [MiddleName] [dbo].[Name] NULL,
    [LastName] [dbo].[Name] NOT NULL,
    [Suffix] [NVARCHAR](10) NULL,
    [EmailPromotion] [INT] NOT NULL,
    [rowguid] [UNIQUEIDENTIFIER] NOT NULL,
    [ModifiedDate] [DATETIME] NOT NULL,
    [DateOfBirth] [DATE] NOT NULL
)

指数

CREATE NONCLUSTERED INDEX [IX_dbo_Person_DateOfBirth] 
ON [dbo].[Person] ( [DateOfBirth] ASC )

直方图

Statistics for INDEX 'IX_dbo_Person_DateOfBirth'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IX_dbo_Person_DateOfBirth       Jun 24 2016  7:16PM             5000000                         5000000                         2                               0.004236792                     3                               NO                                                              5000000                         

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4.720544E-05                    3                               DateOfBirth                     

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1/1/1950 12:00:00 AM            0                               255                             0                               1                               
12/31/2007 12:00:00 AM          4999537                         208                             21182                           236.0276
sql-server sql-server-2014
Martin Hope
SQL Hammer
Asked: 2016-04-27 04:32:51 +0800 CST
  • 2

我有一个每天生成的文本文件。它是一种固定宽度的格式，但有一个令人沮丧的例外。第三列 (NAME) 的宽度可变。该列的长度始终等于数据集中最长的名称 + 1 个空格。这使得每天的文件都有不同的列起点和终点。

有没有人有聪明的方法来每天处理导入这个文件？更改源程序不是一种选择。

我愿意在导入前修改文件。

我愿意使用几乎任何工具，包括但不限于 SSIS、C#、PowerShell。

我正在寻找具有高可靠性的最不“混乱”的解决方案。

例子

A/R                                      Patient                                  PICASI, LINDA  00691685             90409843             Inpatient                                


Revenue                                  Patient                                  SIMPSON, RHASIRA B 00000000             00000000             Emergency
sql-server sql-server-2012
Martin Hope
SQL Hammer
Asked: 2016-03-31 16:40:58 +0800 CST
  • 0

我对 Oracle 非常陌生，不得不在 NOARCHIVELOG 模式下的 11g 数据库上进行备份。下面是输出。它声称备份失败，但我有一个适当大小的文件，我希望在其中创建备份。

有人可以帮我解释这个日志吗？

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 30 20:30:27 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 connected to target database: HS (DBID=3540555849)


RMAN> RUN {

2> 

3> CONFIGURE COMPRESSION ALGORITHM 'HIGH';

4> 

5> show all;

6> 

7> shutdown immediate;

8> 

9> startup mount;

10> 

11> backup database;

12> 

13> alter database open;

14> 

15> }

16> 

17> LIST BACKUP SUMMARY ;

18> 

19> EXIT 

using target database control file instead of recovery catalog

new RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters are successfully stored


RMAN configuration parameters for database with db_unique_name HS are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFHS.ORA'; # default
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area    4275781632 bytes
Fixed Size                     2262048 bytes
Variable Size               2818575328 bytes
Database Buffers            1442840576 bytes
Redo Buffers                  12103680 bytes
Starting backup at 30-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=395 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=D:\APP\ORACLE\ORADATA\HS\HS_F0_SVMC.CID
input datafile file number=00003 name=D:\APP\ORACLE\ORADATA\HS\UNDOTBS01.DBF
input datafile file number=00006 name=D:\APP\ORACLE\ORADATA\HS\AUDIT_DATA.DBF
input datafile file number=00002 name=D:\APP\ORACLE\ORADATA\HS\SYSAUX01.DBF
input datafile file number=00001 name=D:\APP\ORACLE\ORADATA\HS\SYSTEM01.DBF
input datafile file number=00007 name=D:\APP\ORACLE\ORADATA\HS\AUDIT_IDX.DBF
input datafile file number=00004 name=D:\APP\ORACLE\ORADATA\HS\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 30-MAR-16
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/30/2016 20:32:43
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 67108864 bytes disk space from 4322230272 limit
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 30-MAR-16
channel ORA_DISK_1: finished piece 1 at 30-MAR-16
piece handle=D:\APP\ORACLE\FAST_RECOVERY_AREA\HS\BACKUPSET\2016_03_30\O1_MF_NCSNF_TAG20160330T203107_CHRW1F69_.BKP tag=TAG20160330T203107 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================



RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/30/2016 20:32:43

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 67108864 bytes disk space from 4322230272 limit



Recovery Manager complete.
oracle backup
Martin Hope
SQL Hammer
Asked: 2015-11-11 06:39:12 +0800 CST
  • 0

我在 2012 实例 (11.0.2100) 上配置了本机 SSRS。我有两个带有图像的报告，它们用作在图表报告和详细报告之间切换的按钮。我想将操作 URL 配置为在报告之间切换时动态传递参数，以便在切换视图时不使用默认值（从查询派生），手动输入的参数将保持不变。

我已经尝试了以下版本的 URL，并且在所有情况下，默认（查询驱动）参数都用于运行报告，而不是传入的参数。

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&rs:Command=Render&rs:StartDate=2015-10-01&rs:EndDate= 2015-10-31

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&rs:Command=Render&StartDate=2015-10-01&EndDate=2015-10- 31

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&StartDate=2015-10-01&EndDate=2015-10-31

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&StartDate=10/1/2015&EndDate=10/31/2015

关于我在哪里错误地格式化我的 URL 有什么想法吗？

ssrs ssrs-2012
Martin Hope
SQL Hammer
Asked: 2015-09-03 07:12:49 +0800 CST
  • 1

我在 v4.0 中开发了一个 PowerShell 脚本，然后了解到 v2.0 不支持 -notin 运算符（这是我需要在其中运行它的版本）。v2.0 的 -notin 运算符的最佳替代方案是什么？如果可能的话，我宁愿尽量减少代码重构。

这是我使用运算符的代码片段：

$NoBackups += $BackupList | 
            Where-Object { $_.DatabaseName -notin ($NoBackups.DatabaseName) } |
            Where-Object { ($_.RecoveryModel.ToString().ToUpper() -ne "SIMPLE") -and ($_.LastLogBackupDate -lt $CompareDate.AddHours($LOGCompareHours)) } |
            SELECT InstanceName, DatabaseName, LastBackupDate, LastDifferentialBackupDate, LastLogBackupDate, `
                @{Name='Message';Expression={"Transaction Log Backup is more than $([Math]::Abs($LOGCompareHours)) hours old"}}
powershell
Martin Hope
SQL Hammer
Asked: 2015-03-06 10:17:42 +0800 CST
  • 1

我遇到了以下死锁情况。请参阅随附的对象定义和执行计划。这些计划是从确实发生此死锁的系统的缓存中提取出来的。

我了解所有这些数据告诉我的内容，但令我感到困惑的是为什么 spUpdateUserAccount 存储过程对 IX_Person_TaxPreparer_rowCreated 使用 S 锁。此锁定似乎是不必要的，并且在 spUpdateUserAccount 的执行计划中没有对 IX_Person_TaxPreparer_rowCreated 的引用。

...

完整的问题，带附件，张贴在 SQLPerformance

sql-server locking
Martin Hope
SQL Hammer
Asked: 2014-09-28 18:17:17 +0800 CST
  • 3

我在使用 PowerShell SQLSERVER 提供程序和 SQLRegistration\Central Management Server Group 目录时遇到了奇怪的行为。以下是行为。我想知道 SQLSERVER 提供程序如何知道注册了哪些 CMS，以便我可以理解为什么它在 SSMS 2014 中完成时似乎没有注意到注册（有关详细行为，请参见下文）。另外，当我知道实例在线时，为什么我的连接失败。

安装
机

  • 本地主机

实例

  • 本地主机\SQL2012
  • 本地主机\SQL2014
  • 本地主机\SQL2014_1

行为 1
使用 SSMS 2014，我将 localhost\SQL2012 注册为 CMS。执行此代码不会返回任何项目。 

PS SQLSERVER:\SQLRegistration\Central Management Server Group> dir

打开和关闭 PowerShell 控制台，SSMS 不会改变结果。如果我打开 SSMS 2012 并将 localhost\SQL2012 注册为 CMS 并重新运行上述命令，我会看到 localhost\SQL2012 按预期注册。

行为 2
让服务器成功列出后...

目录：Microsoft.SqlServer.Management.PSProvider\SqlServer::SQLSERVER:\SQLRegistration\Central Management Server Group

模式名称

  • 本地主机\SQL2012

并且运行以下命令以尝试导航到我注册的服务器组失败并出现以下错误，即使实例在线且可用。 

PS SQLSERVER:\SQLRegistration\Central Management Server Group> Set-Location "localhost\SQL2012\"

设置位置：找不到路径“SQLSERVER:\SQLRegistration\Central Management Server Group\localhost\SQL2012\”，因为它不存在。在 line:1 char:1 + Set-Location "localhost\SQL2012\" + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ + CategoryInfo : ObjectNotFound: (SQLSERVER:\SQLR...alhost\SQL2012:String) [Set-Location], ItemNotFoundE xception + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand

更新 1
SMO 似乎工作正常。以下代码成功返回我的服务器组。 

#Load SMO assemblies
$CentralManagementServer = "localhost\sql2012"
$MS='Microsoft.SQLServer'
@('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') |
     foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}}

$connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

$CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].ServerGroups
sql-server powershell