Combine command line and T-SQL - SQL Server

Question

I am trying to execute some sqlcmd through T-SQL on SQL Server 2008. The part of my code where I check the data file size and if the data file size is not 0, then start the deletion so that I can use BCP in the new data.

Below is my code that is not executing:

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 above code is skipped because it does not throw away any error messages.

EDIT: After reconfiguring the interval and my code, @myVariable is now executing. However, it still does not work with respect to the fact that it still deletes the table, even if the data file size = 0. However, when I hard-code it in a batch file, it works fine. Any ideas?

Answer 1

I realized that I can check the number of tables instead of the data file size 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 2

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

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

Answer 3

I can not speak with your DOS team. However, I can suggest using Ole Automation Procedures to get the file size. This way, you don’t have to rely on running team commands.

First you need to enable Ole Automation Procedures on your instance of SQL Server 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 up is the script that gets the file size. The example assumes that there is a file named C:\Temp\testfile.txt . The script selects the size if the file exists, or selects 0 if it is not. You can take this script as an example to do what you want based on size.

Here:

 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 4

I think the problem is that you are not using quotation marks around your file names. There is a space in this top-level directory.

Jaco's answer looks right, and I'm sure that was part of the problem. You should probably initialize size to be safe:

 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 will consider its "set" (the terminology used in for /? ) As two elements separated by a space. If your current directory is X:\Main Folder\Data\ , it will still work, since it sees the latter as the relative path to the .dat file, and then set correct value on the last pass.

Answer 5

Not sure if this is the option for you, but since you're on SQL 2008, you can use the powershell command:

 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 checked and it seems to work depending on the file size.

Answer 6

Why do you even "go down" to the command line? (there are reasons why xp_cmdshell disabled by default)

Could you just sys.tables WHERE name LIKE ... 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 to the shadow table (in TRY..CATCH to handle situations when the file does not exist or is empty or damaged, ..
• if there is data in the shadow table, then DELETE actual table entries and moving the data over
• If there is no data in the shadow table, then DELETE actual table entries (or leave them if you assume that it is missing or an empty bcp file means that it will be sent later and you are stuck with the current version for now)
• DROP shadow table again

Answer 7

You are using X:\ in your code. But the code runs under a service account for SQL Server. This account may have x:

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

Answer 8

It seems you already know the names of the databases and tables, so you can use the following, which basically performs the DIR for the file you are looking for, and checks if it is '0 bytes' , if that is what you want. What should be noted:

• TEMPLATES STRING. When building strings, I like to create a “pattern” and then replace inside the string. This is a good way to make sure you have the correct number of quotes, brackets, etc. I did this twice here, once, to create a DIR command, and then create a TRUNCATE command again.

• TRUNCATE - although not part of your question, you can use TRUNCATE instead of DELETE FROM . If your table had a million rows, DELETE FROM can take 2 minutes, and as TRUNCATE 0 seconds will always be executed.

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