Combine command line and T-SQL - SQL Server

Question

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?

Answer 1

You need to use single % in your for loop as you are not executing the code in a batch file (that requires %%), see this post for some further clarification. So your for loop should be:

FOR %R IN (X:\Main Folder\Data\'+@databaseName+'_'+@tableName+'.dat) DO SET size=%~zR 

Answer 2

I can't speak to your DOS command. I can however suggest using Ole Automation Procedures to get the file size. That way you would not have to rely on running batch commands.

First you need to enable Ole Automation Procedures on your SQL Server instance, as follows:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

You only need to do this once.

---

Next is a script that gets the file size. The example assumes that there's a file called C:\Temp\testfile.txt. The script selects the size if the file exists, or selects 0 if it doesn't. You can take this script as an example to do what you want based on the size.

Here goes:

DECLARE @hr INT;
DECLARE @size INT;
DECLARE @obj_file INT;
DECLARE @obj_file_system INT;
DECLARE @file_name VARCHAR(100)='C:\Temp\testfile.txt';

-- Create a FileSystemObject. Create this once for all subsequent file manipulation. Don't forget to destroy this object once you're done with file manipulation (cf cleanup)
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @obj_file_system OUT;
IF @hr<>0 GOTO __cleanup;

-- Get a handle for the file. Don't forget to release the handle for each file you get a handle for (see cleanup). The return will be different from 0 if the file doesn't exist
EXEC @hr = sp_OAMethod @obj_file_system, 'GetFile', @obj_file out, @file_name;
IF @hr<>0 GOTO __print_file_size;

-- Retrieve the file size.
EXEC sp_OAGetProperty @obj_file, 'size', @size OUT;

__print_file_size:
SELECT ISNULL(@size,0) AS file_size;

__cleanup:
EXEC sp_OADestroy @obj_file_system;
EXEC sp_OADestroy @obj_file;

Answer 3

I think the problem is that you're not using quotes around your filenames. That top level directory has a space in it.

Jaco's answer looks correct and I'm sure it was part of the problem. You should probably initialize size just to be safe too:

SET @myVariable = '
SETLOCAL
SET size=0
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

Without the quotes the for loop is going to treat that its "set" (the terminology used in for /?) as two items separated by the space. If your current directory is X:\Main Folder\Data\ it would still work though since it sees the last one as a relative path to the .dat file and then still sets the right value on the last pass.

Answer 4

Not sure if this is an option for you but since you are on SQL 2008 you should be able to use powershell command instead:

DECLARE @File varchar(100), @outputFile varchar(100)
DECLARE @cmd varchar(1000)
SELECT @File = 'path_to_file'
SELECT @outputFile = 'path_to_output_file'

SELECT @cmd = 'powershell.exe -command "if((Get-Item '''+@File+''').length -gt 0) {&sqlcmd.exe -E -S SERVERNAME -Q ''SELECT name FROM master.sys.databases ;'' -o '+@outputFile+'}"'


SELECT @cmd

exec master..xp_cmdshell @cmd

I've checked and it seems to be working depending on the file size.

Answer 5

Why would you go 'down' to the command line at all? (there are reasons why xp_cmdshell is disabled by default)

Could you not simply loop over your tables (sys.tables WHERE name LIKE ...) and then

• create a shadow-copy of the table (SELECT INTO)
• BULK INSERT from the (expected) file into shadow-table (in a TRY..CATCH to handle situations where the file does not exist, or is empty, or is corrupt, ..
• if there is data in the shadow-table, then DELETE the actual table records and move the data over
• if there is no data in the shadow table then DELETE the actual table records (or leave them in if you assume a missing or empty bcp file means it will arrive later on and you're stuck with the current version for now)
• DROP the shadow table again

Answer 6

You are using X:\ in your code. But the code is running under the service account for SQL Server. That account may not have x: available.

I would suggest using a UNC instead of a mapped drive. Also, make sure that your service is running under a domain account, and that the domain account has all required permissions to the UNC.

Answer 7

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

Answer 8

You seems to know the names of the databases and tables already, so you can use the following, which basically does a DIR for the file you're looking for and checks if it's '0 bytes', if so it then does whatever you want. Things to note:

• STRING TEMPLATES -- When building strings, I like to build a 'template' and then replace within the string. This is a good way to make sure you have the right number of quotes, parenthesis, etc. I did it twice here, once to build the DIR command and then again to build the TRUNCATE command.

• TRUNCATE -- although not part if your question, you may want to use a TRUNCATE instead of DELETE FROM. If you had a million rows in your table, DELETE FROM may take 2 min to run, where as TRUNCATE will always take 0-seconds to run.

Your answer:

SET NOCOUNT ON;

DECLARE @DatabaseName VARCHAR(50) = 'database1'
DECLARE @TableName VARCHAR(50) = 'table1'

DECLARE @PathTemplate VARCHAR(50) = 'dir c:\temp\{@DatabaseName}_{@TableName}.txt'

SET @PathTemplate = REPLACE(@PathTemplate, '{@DatabaseName}', @DatabaseName);
SET @PathTemplate = REPLACE(@PathTemplate, '{@TableName}', @TableName);

DECLARE @FileNames AS TABLE (FileNames VARCHAR(100))

INSERT @FileNames (FileNames)
exec xp_cmdshell @PathTemplate

IF EXISTS ( SELECT 1 FROM @FileNames WHERE FileNames LIKE '%0 bytes')
BEGIN
    PRINT 'No Content/Missing File'
END 
ELSE BEGIN

    DECLARE @SqlExc VARCHAR(500) = 'TRUNCATE TABLE [{@DatabaseName}].[dbo].[{@TableName}]'
    SET @SqlExc = REPLACE(@SqlExc, '{@DatabaseName}', @DatabaseName);
    SET @SqlExc = REPLACE(@SqlExc, '{@TableName}', @TableName);

    PRINT @SqlExc
    -- sp_executesql @SqlExc  <-- Do this in production

END