I am trying to execute some sqlcmd through T-SQL on SQL Server 2008. There is a part of my code where I am checking a data file size and if that data file size does not equal to 0, then start deleting the specific table so I can BCP in new data.
Below is my code that is not being executed:
SET @myVariable = '
SETLOCAL
FOR %%R IN (X:\Main Folder\Data\'+@databaseName+'_'+@tableName+'.dat) DO SET size=%%~zR
IF %size% NEQ 0 (
SQLCMD -E -S my-server-name -Q "DELETE FROM '+@databaseName+'.'+@schemaName+'.'+@tableName+';" >> X:\Main Folder\Log\Log.txt
)'
EXEC master..xp_cmdshell @myVariable
For some reason when I execute my stored procedure, the code above seems to be skipped because it does not shoot back any error messages.
EDIT: After re-adjusting the spacing and my code, @myVariable, gets executed now. However, it still does not work in regards that it still deletes the table even though the data file size = 0. However, when I hard code it within a batch file, it works perfectly fine. Any ideas?
I realized that I can check the table count instead of a data file size by using this method:
SET @sqlCheck = 'SQLCMD -E -S ServerA -Q "IF (SELECT COUNT(*) FROM '+@databaseName+'.'+@schemaName+'.'+@tableName+') > 0 BEGIN DELETE FROM ServerB.'+@databaseName+'.'+@schemaName+'.'+@tableName+' END;"'
EXEC MASTER..xp_cmdshell @sqlcheck