0

I have a Spring batch which invokes a SQL Procedure.

SQL Procedure is to extract a file (using BCP command). Internally code of SQL proc fetches the data from few tables and creates a string which it inserts in one Temp table and then there is BCP command to just extract the data from Temp table.

SQL Proc runs perfectly fine when we execute it in SQL Server Management Studio, i.e. a file is extracted with the data, but when the SQL proc is initiated from Java Spring batch, system generates Empty file.

On doing debugging I found that system does not find data in Temp table hence empty file is getting extracted. If I keep some data filled in that Temp table, then that gets extracted in file.

I feel the issue is that when Transaction is initiated from Java, the Insert which is written in SQL Proc (to insert data in Temp table) is not getting Commit, hence when BCP command works, it finds empty temp table.

Requirement - I tried by writing - BEGIN TRANSACTION before Insert and COMMIT after the Insert, but still the empty file is getting generated.

Is there a way to Force the Commit post the INSERT, so that when the BCP command is getting executed it finds the data in Temp table.

Or is there any other solution which you guys can suggest that I should try?

Appreciate your help!

EDIT ------------- Sample code for Data Query from the Proc

          SET @DATAQuery ='SELECT 1'           
          BEGIN TRANSACTION;
               SET @DATAQuery = 'INSERT INTO EXTRACTRESULTS ' + @DATAQuery  
               PRINT 'Data Query:'+@DATAQuery  
               EXEC (@DATAQuery) 
           COMMIT TRANSACTION

           SET @FINALQuery = 'SELECT RESULTS FROM '+@DBNAME+'.dbo.'+'EXTRACTRESULTS'

           SET @ExtractBatchStringData = 'bcp "'+@FINALQuery+'" queryout "'+@FOLDERPATH+@filename +'"  -c -T -t -S""';      
           PRINT 'ExtractBatchStringData: '+@ExtractBatchStringData      
           EXEC @STATUS = xp_cmdshell @ExtractBatchStringData
vnkotak
  • 129
  • 4
  • 14
  • Please post your code. – Dale K Sep 05 '19 at 06:25
  • @DaleBurrell.. Sample code updated in the post. As seen in the code that I have kept Begin and Commit, but still when the file is generated it is Empty, however same SQL procedure when executed directly in SSMS works perfectly fine. – vnkotak Sep 05 '19 at 06:34

0 Answers0