[SOLVED] SqlCmd command execution stops

SqlCmd command execution stops

Iam new to sqlcmd and i'm trying to execute this sql cmd code:

:Connect SERVERNAME
!!if exist $(FullBackup) del $(FullBackup)
GO
!!if exist $(TransactionLog) del $(TransactionLog)
GO

I am passing variables $(FullBackup) and $(TransactionLog) through a powershell script:

& $app -i $syncFileLocal -E -b -v FullBackup=("""$fullbackup""") TransactionLog=("""$transactionLog""");

where syncFileLocal contains the above sqlcmd command.

Somehow the execution stops after the second :Connect PROD-SQLMASTER

UPDATE:

When i use harcorded values for $(FullBackup) and $(TransactionLog) the script seems to work. Is there anyway i could do it by passing variables through powershell?

Solution

I found a solution. I recommend using this with appropriate validations

:Connect $(ServerMaster)
DECLARE @resultBkp INT
EXEC master.dbo.xp_fileexist N'$(FullBackup)', @resultBkp OUTPUT
IF (@resultBkp = 1)
BEGIN
    DECLARE @resultDeleteBkp INT
    EXECUTE master.sys.xp_cmdshell '$(FullBackup)'
    EXEC master.dbo.xp_fileexist N'$(FullBackup)', @resultDeleteBkp OUTPUT
    IF (@resultDeleteBkp = 0)
    BEGIN
        PRINT 'Backup Deleted'
    END
    ELSE
    BEGIN
        SELECT ERROR_NUMBER(), ERROR_MESSAGE();
        RETURN;
    END
END
ELSE
BEGIN
    PRINT 'Backup file not found'
END

I used the master.dbo.xp_fileexist to check whether the file exists and then used master.sys.xp_cmdshell command to delete the file.

To enable master.sys.xp_cmdshell for the database server please use this solution: Enable 'xp_cmdshell' SQL Server

I have tested it and it works fine when i pass the arguments via powershell.