動態 SQL 給出“’/’ 附近的語法不正確

我正在尋找有關此程式碼的幫助。此程式碼建構正確的命令行語法並執行良好。但是，當通過 TSQL 執行時，我在“/”附近得到不正確的語法。

*/
/*********************************************************************************************************************************************/
/* Declaration Section                                                                                                                       */
/*********************************************************************************************************************************************/
DECLARE @sqlPackageFilePath VARCHAR(250)
DECLARE @artifactLocation   VARCHAR(1000)
DECLARE @databaseName       VARCHAR(25)
DECLARE @targetServer       VARCHAR(100)
DECLARE @outputFileLocation VARCHAR(1000)
DECLARE @currentDate        VARCHAR(8)
DECLARE @sqlCmd             NVARCHAR(4000)
DECLARE @command            VARCHAR(4000)
DECLARE @reversePath        VARCHAR(4000)
DECLARE @extLength          INT
DECLARE @artifactPublish    VARCHAR(1000)
DECLARE @artifactDacPac     VARCHAR(1000)



/*********************************************************************************************************************************************/
/* Create temporary tables if they don't exist.                                                                                              */
/*********************************************************************************************************************************************/

IF OBJECT_ID('tempdb..#ArtifactsPublish') IS NOT NULL
   DROP TABLE #ArtifactsPublish

CREATE TABLE #ArtifactsPublish (
   ID              INT IDENTITY(1,1)
   , filePath      VARCHAR(1000)
   , databaseName  VARCHAR(25)
)

IF OBJECT_ID('tempdb..#ArtifactsDacPac') IS NOT NULL
   DROP TABLE #ArtifactsDacPac

CREATE TABLE #ArtifactsDacPac (
   ID              INT IDENTITY(1,1)
   , filePath      VARCHAR(1000)
   , databaseName  VARCHAR(25)
)

IF OBJECT_ID('tempdb..#TargetServers') IS NOT NULL
   DROP TABLE #TargetServers

CREATE TABLE #TargetServers
(
   ID                  INT IDENTITY(1,1)
   , databaseName      VARCHAR(25)
   , aliasName         VARCHAR(100)
   , productName       VARCHAR(25)
)


/*********************************************************************************************************************************************/
/* Coding Section                                                                                                                            */
/*********************************************************************************************************************************************/

SET @currentDate = REPLACE(CONVERT(CHAR(10), GETDATE(), 101), '/','')
SET @sqlPackageFilePath = 'C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe'
-- Path to the publish and dacpac
--SET @artifactLocation = 
--SET @artifactLocation = 
--SET @artifactLocation =
SET @artifactLocation = 
SET @databaseName =
SET @targetServer = 
SET @outputFileLocation = 

IF (@sqlPackageFilePath IS NULL OR @sqlPackageFilePath = '')
   OR (@artifactLocation IS NULL OR @artifactLocation = '')
   OR (@databaseName IS NULL OR @databaseName = '')
   OR (@targetServer IS NULL OR @targetServer = '')
   OR (@outputFileLocation IS NULL OR @outputFileLocation = '')
BEGIN
   PRINT 'Required Values are incorrect.  Please correct and rerun process.'
END
ELSE
   PRINT 'I am in and have the correct values.'


-- Get all DB Projects to generate files for
SET @command = 'dir ' + @artifactLocation + '\*.publish.xml /s /b'

INSERT INTO #ArtifactsPublish (filePath)
EXEC sys.xp_cmdshell @command

UPDATE #ArtifactsPublish
SET databaseName = REPLACE(REVERSE(LEFT(REVERSE(filePath),CHARINDEX('\', REVERSE(filePath), 1) - 1)), '.publish.xml', '')

SELECT * FROM #ArtifactsPublish

SELECT @artifactPublish = filePath FROM #ArtifactsPublish WHERE databaseName = @databaseName


-- Get all DacPac Files
SET @command = 'dir ' + @artifactLocation + '\*.dacpac /s /b'

INSERT INTO #ArtifactsDacPac (filePath)
EXEC sys.xp_cmdshell @command

UPDATE #ArtifactsDacPac
SET databaseName = REPLACE(REVERSE(LEFT(REVERSE(filePath),CHARINDEX('\', REVERSE(filePath), 1) - 1)), '.dacpac', '')

SELECT * FROM #ArtifactsDacPac

SELECT @artifactDacPac = filePath FROM #ArtifactsDacPac WHERE databaseName = @databaseName


SET @sqlCmd = '"' + @sqlPackageFilePath + '" /a:Script /pr:"' + @ArtifactPublish + '" /sf:"' + @ArtifactDacPac + '" /TargetServerName:"' + @targetServer + '" /TargetDatabaseName:"' + @databaseName + '" /p:DropIndexesNotInSource=False /op:"' + @outputFileLocation + @databaseName + '_' + @currentDate + '.sql"'

PRINT @sqlCmd


EXEC sys.sp_executesql @SqlCmd


/*********************************************************************************************************************************************/
/* Drop temporary tables.                                                                                                                    */
/*********************************************************************************************************************************************/

IF OBJECT_ID('tempdb..#ArtifactsPublish') IS NOT NULL
   DROP TABLE #ArtifactsPublish

IF OBJECT_ID('tempdb..#ArtifactsDacPac') IS NOT NULL
   DROP TABLE #ArtifactsDacPac

IF OBJECT_ID('tempdb..#TargetServers') IS NOT NULL
   DROP TABLE #TargetServers

好吧，你的意思是用xp_cmdshell那里而不是sys.sp_executesql？後者需要某種 T-SQL 命令，而不是"C:\Program Files ..."

您可能希望使用不同的變數名@sqlCmd來防止這種混淆。

通過將命令括在一組額外的雙引號中，我能夠使您的命令正常工作（至少到它告訴我該文件不存在的程度）：

SET @sqlCmd = '""' + @sqlPackageFilePath + '" ..."' + ... + @currentDate + '.sql""';
---- here -----^            ---- and here ------------------------------------------^

EXEC sys.xp_cmdshell @SqlCmd;

引用自：https://dba.stackexchange.com/questions/152913