SqlCmd command execution stops

Question

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?

score 0 · Answer 1 · answered Jul 25 '20 at 23:01

Instead of:

FullBackup=("""$fullbackup""") TransactionLog=("""$transactionLog""")

try:

FullBackup="""$fullbackup""" TransactionLog="""$transactionLog"""

If you use (), the grouping operator, its output is passed as a separate argument, which is not what you want.

Do note, however, that even the solution above relies on PowerShell's fundamentally broken argument-passing to external programs, as of v7.0 - see this answer.

If sqlcmd is implemented properly (I don't know if it is), the right way to pass the arguments is:

FullBackup=$fullbackup TransactionLog=$transactionLog

That way, you would rely on PowerShell's on-demand, behind-the-scenes re-quoting of arguments, where if $fullbackup or $translactionLog contained spaces, the arguments would be passed as, for instance, "FullBackup=c:\path\to\backup 1" and "TransactionLog=c:\path\to\log 1"

score 0 · Accepted Answer · answered Jul 26 '20 at 17:51

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.

SqlCmd command execution stops

2 Answers2