结合命令行和 T-SQL - SQL Server [英] Combine command line and T-SQL - SQL Server

问题描述

我正在尝试通过 SQL Server 2008 上的 T-SQL 执行一些 sqlcmd.我的代码的一部分是检查数据文件大小，如果该数据文件大小不等于 0，则开始删除特定的表，以便我可以在新数据中进行 BCP.

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.

重新调整间距后，我的代码@myVariable 立即执行.但是，即使数据文件大小 = 0，它仍然无法删除表.但是，当我在批处理文件中对其进行硬编码时，它可以正常工作.有什么想法吗?

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

这篇关于结合命令行和 T-SQL - SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！