主页 / dba / 问题 / 344991
Accepted
Marcello Miorelli
Asked: 2025-01-29 23:21:24 +0800 CST

powershell 中的 sql 服务器代码上的双引号-在可视化代码中-如何使其工作？

  • 772

双引号在 SQL Server 中使用，其行为可以通过选项SET QUOTED_IDENTIFIER ON \ OFF来定义

在powershell中也使用双引号。

调用 XML 数据类型方法时，SET QUOTED_IDENTIFIER 必须为 ON。

这是我想要部署到多个服务器的存储过程的部分视图，但是，由于引号标识符或双引号这个字符 - “ - 它在 powershell 中不起作用，在我用来部署的可视化代码中。

DECLARE @WebXConfigurationID INT,
                @stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
                @stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
                @cur_Properties XML;


            PRINT 'WebX Configuration: ' + CAST(@WebXConfigurationID AS VARCHAR(20));

            IF (ISNULL(@stored_URL, '') <> ISNULL(@cur_URL, ''))
            BEGIN
                PRINT ' - Base URL needs updating'
            END 
            ELSE
            BEGIN
                PRINT ' - Base URL does not need updating'
            END;

            IF (ISNULL(@stored_OpsServiceURL, '') <> ISNULL(@cur_OPSServiceURL, '') AND @cur_OPSServiceURL IS NOT NULL)
            BEGIN
                PRINT ' - OPS Service URL needs updating'
                SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")')
            END 
            ELSE
            BEGIN
                PRINT ' - OPS Service URL does not need updating'
            END;

这是错误信息：

"' in expression or statement.
At C:\sp_PosRestore.ps1:636 char:114
+ ... ServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")')
+                                                 ~~~~~~~~~~~~~~~~~~~~~
The splatting operator '@' cannot be used to reference variables in an expression. '@stored_OpsServiceURL' can be used only as an argument to a command. To reference variables in an expression use      
'$stored_OpsServiceURL'.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : UnexpectedToken

在此处输入图片描述

Powershell 代码：

$SQLQuery = 
"
DECLARE @WebXConfigurationID INT,
                @stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
                @stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
                @cur_Properties XML;


            PRINT 'WebX Configuration: ' + CAST(@WebXConfigurationID AS VARCHAR(20));

            IF (ISNULL(@stored_URL, '') <> ISNULL(@cur_URL, ''))
            BEGIN
                PRINT ' - Base URL needs updating'
            END 
            ELSE
            BEGIN
                PRINT ' - Base URL does not need updating'
            END;

            IF (ISNULL(@stored_OpsServiceURL, '') <> ISNULL(@cur_OPSServiceURL, '') AND @cur_OPSServiceURL IS NOT NULL)
            BEGIN
                PRINT ' - OPS Service URL needs updating'
                SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")')
            END 
            ELSE
            BEGIN
                PRINT ' - OPS Service URL does not need updating'
            END;
"
sql-server

2 个回答

  1. Best Answer

    双引号是插入字符串，单引号是非插入字符串。由于引号内有引号，因此需要对其进行转义。如果您@涉及，那么它将尝试插入。

    因此，您需要在字符串周围使用单引号，并且其中的任何单引号都需要加倍。有关更多信息，请参阅此处。

    $SQLQuery = '
DECLARE @WebXConfigurationID INT,
                @stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
                @stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
                @cur_Properties XML;


            PRINT ''WebX Configuration: '' + CAST(@WebXConfigurationID AS VARCHAR(20));

            IF (ISNULL(@stored_URL, '''') <> ISNULL(@cur_URL, ''''))
            BEGIN
                PRINT '' - Base URL needs updating'';
            END 
            ELSE
            BEGIN
                PRINT '' - Base URL does not need updating'';
            END;

            IF (ISNULL(@stored_OpsServiceURL, '''') <> ISNULL(@cur_OPSServiceURL, '''') AND @cur_OPSServiceURL IS NOT NULL)
            BEGIN
                PRINT '' - OPS Service URL needs updating'';
                SET @cur_Properties.modify(''replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")'');
            END 
            ELSE
            BEGIN
                PRINT '' - OPS Service URL does not need updating'';
            END;
';

    另一个选项是 Here-string，用@'和包围'@。

    $SQLQuery = @'
DECLARE @WebXConfigurationID INT,
                @stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
                @stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
                @cur_Properties XML;


            PRINT 'WebX Configuration: ' + CAST(@WebXConfigurationID AS VARCHAR(20));

            IF (ISNULL(@stored_URL, '') <> ISNULL(@cur_URL, ''))
            BEGIN
                PRINT ' - Base URL needs updating';
            END 
            ELSE
            BEGIN
                PRINT ' - Base URL does not need updating';
            END;

            IF (ISNULL(@stored_OpsServiceURL, '') <> ISNULL(@cur_OPSServiceURL, '') AND @cur_OPSServiceURL IS NOT NULL)
            BEGIN
                PRINT ' - OPS Service URL needs updating';
                SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")');
            END 
            ELSE
            BEGIN
                PRINT ' - OPS Service URL does not need updating';
            END;
'@;

    SET QUOTED_IDENTIFIER与这些都无关。严格来说，这是指使用""来分隔标识符，而不是字符串。而且ON无论如何都应该如此。它与您发布的任何代码都无关。

    • 5

  2. 我改变了这一行：

    SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")')

    对此（基本上我在双引号前添加了反引号字符（`）：

    SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable(`"@stored_OpsServiceURL`")')

    现在一切正常。反引号工作正常

    然而，对我来说最有效的是@'和charlieface 的回答'@一样

    在此处输入图片描述

    • 3

相关问题