0

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
1
  • Please don't vandalize the question that has an accepted and upvoted answer.
    – mustaccio
    Sep 17 at 16:40
3

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;
5
  • I have tried exec, execute, exec xp_cmdshell and execute xp_cmdshell and different results. Oct 20 '16 at 20:08
  • Right above it you do INSERT ... EXEC sys.xp_cmdshell @command and that works, right? What are "different results"? Oct 20 '16 at 20:08
  • Yes, here are the errors I get with the different attempts. I had to remove the @ signs in my code below. --EXEC sys.sp_executesql SqlCmd -- Incorrect Syntax near / --EXEC sqlCmd -- is not a valid identifier. --EXECUTE sqlCmd --is not a valid identifier. --EXEC sys.xp_cmdshell sqlCmd --'C:\Program' is not recognized as an internal or external command, --EXECUTE sys.xp_cmdshell sqlCmd --'C:\Program' is not recognized as an internal or external command, Oct 20 '16 at 20:09
  • You need additional double quotes. See update. Oct 20 '16 at 20:22
  • That was it, thank you so much. These double, triple, quotes are killers. Oct 21 '16 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.