0

I have a .bat file that iterates through it's residing director and takes the file name and creates a series of insert into select openrowset statements that is written to another file for later use.

I am trying to execute this .bat file in SQL Server using the below statement:

EXEC master..xp_cmdshell 'cmd /c filepath\FileTransfers\incoming\pdfs\gensql.bat'

overall the command works, if I replace gensql.bat with helloworld.bat I get the expected output.

The problem: When I attempt to execute the command using the gensql.bat I get null output and the file is not written to.

  • If I run the bat file from the command prompt it works as expected.
  • If I manually run the bat file from it's folder it works as expected.
  • If I attempt it to run it via PowerShell I get access denied as expected.

I have tried to run it using the SQL Server job agent and the job runs successfully but there the expected output to the file is not there.

The account I am logged into and running the bat file has the permissions to the file location because I load other files from the same file path.

Command I am executing in SQL Server

EXEC master..xp_cmdshell 'cmd /c filepath\FileTransfers\incoming\pdfs\gensql.bat'

Contents of .bat file -- all this bat file is doing is writing statements to a file

@echo off
echo. > loadFiles.sql
for /r %%i in (*.pdf) do (
    echo INSERT INTO dbo.user_attachments(content, extension, recipient_id^)
    echo SELECT bulkcolumn, '.pdf', '%%~ni'
    echo FROM OPENROWSET(BULK '%%i', SINGLE_BLOB^) AS t;
) >> loadFiles.sql

Any help I can get with this is very much appreciated.

Dale K
  • 25,246
  • 15
  • 42
  • 71
user3072822
  • 1
  • Consider using File Tables, which will expose a SQL Server table as an SMB share. https://learn.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server?view=sql-server-ver15 – David Browne - Microsoft Jan 12 '22 at 00:39
  • What is your SQL Server version? – Yitzhak Khabinsky Jan 12 '22 at 01:56
  • 1
    Can I gently suggest that you are using the wrong tool for the job. T-SQL is not the place to be firing off `cmd` batches, especially as SQL Server's service account has quite limited permissions (or should have). I suggest you use Powershell for this. You can even run these SQL commands from Powershell, passing in the data as parameters – Charlieface Jan 12 '22 at 02:33
  • Have you searched other folders for a `loadFiles.sql` file? I'm not seeing anything changing the current directory there so it could be trying to enumerate .pdf files and write to wherever the default command prompt folder is. – AlwaysLearning Jan 12 '22 at 02:56
  • First off, thank you all for the responses. Very helpful information for me to start looking into. – user3072822 Jan 12 '22 at 13:02
  • @KJ - I will do just as you have suggested and make those changes so I can better determine what's going wrong with the file. Thank you – user3072822 Jan 12 '22 at 13:13
  • @David Browne - I will definitely look into this and do some additional research to determine exactly how to apply this method. Thank you – user3072822 Jan 12 '22 at 13:14
  • @Charlieface - Yes and after some additional research I agree. I have actually attempted to use PowerShell as an alternative but ran into an Access denied error. This is option is still on the table and more favorable. Just need to work through this access denied issue. Thank you! – user3072822 Jan 12 '22 at 13:16
  • @Yitzhak Khabinsky Management Studio Version is 2017 - 17.8.1 but the version on our database server is 16, I believe. I waiting on confirmation about this. Thank you – user3072822 Jan 12 '22 at 13:18
  • @AlwayLearning - No, I have not and I have not considered this. Great suggestion. I will look into this immediately. Thank you! – user3072822 Jan 12 '22 at 13:18

0 Answers0