double quotes on a sql server code inside powershell - in visual code - how to make it work?
4
votes
2
answers
369
views
double quotes are used in sql server, and their behaviour can be defined by the option SET QUOTED_IDENTIFIER ON \ OFF
double quotes are also used in powershell .
SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.
this is a partial view of a stored procedure that I want to deploy to multiple servers,
however, because of the quoted-identifier, or double quotes this character - " - it is not working in powershell, in the visual code that I use to deploy.
The Powershell code:
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;
this is the error message:
"' 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

$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;
"
Asked by Marcello Miorelli
(17274 rep)
Jan 29, 2025, 03:21 PM
Last activity: Feb 12, 2025, 04:21 PM
Last activity: Feb 12, 2025, 04:21 PM