Dynamic SQL gives "Incorrect Syntax Near '/'

Question

I am looking for some assistance with this code. This code builds the proper command line syntax and executes fine. However, when running via TSQL I get Incorrect Syntax near '/'.

*/
/*********************************************************************************************************************************************/
/* 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

Answer 1

Well, did you mean to use xp_cmdshell there instead of sys.sp_executesql? The latter expects a T-SQL command of some kind, not "C:\Program Files ..."

You might want to use a different variable name than @sqlCmd to prevent that confusion.

I was able to get your command to work (at least to the point where it told me that the file didn't exist) by wrapping the command in an additional set of double quotes:

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

EXEC sys.xp_cmdshell @SqlCmd;