Okay, I've been battling with this problem for almost a week now and I cannot for the life of me figure out what the issue is.
Problem: BCP utility creates txt file but nothing happens after. The file just sits there and is blank. BCP pretty much hangs. I have to End Process to get stop it. The BCP command is inside a stored procedure that is inside a transaction that is inside a job step. If I take the sproc itself and run it in management studio, the file is created without a problem. If I create a SQL job and put JUST the sproc that runs the BCP command, it ALSO works.
This is the job step:
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.DataManipulation1
EXEC dbo.DataManipulation2
EXEC dbo.DataManipulation3
EXEC dbo.DataManipulation4
EXEC dbo.DataManipulation5
EXEC dbo.spCreateFiles 0
EXEC dbo.spSendEmail 'PASS'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
EXEC dbo.spGetDatabaseErrorInfo
EXEC dbo.spCreateFiles 1
EXEC dbo.spSendEmail 'FAIL'
END CATCH
Here's the spCreateFiles sproc. The high level overview: Sproc generate system folders then does a queryout to a txt file. That's it. If the parameter being passed to the sproc is 0, it'll generate files based on the execution of a sproc, if the parameter is NOT 0, then it'll generate blank files. It's a total of 4 files. The username and password for the BCP command is removed for obvious reasons. From some of my reading online, it might be because something is locking up the file... maybe the sproc or the SQL job or even the transation and then when the BCP utility tries to use it, nothing happens.
ALTER PROCEDURE [dbo].[spCreateFiles] @errorCode BIT
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON;
DECLARE @year CHAR(4)
DECLARE @month CHAR(2)
DECLARE @day CHAR(2)
DECLARE @rootDir VARCHAR(200)
DECLARE @yearDir VARCHAR(200)
DECLARE @monthDir VARCHAR(200)
DECLARE @dayDir VARCHAR(200)
DECLARE @dirsTable TABLE (directory VARCHAR(200))
DECLARE @baseFileName VARCHAR(8)
DECLARE @detailFile VARCHAR(500)
DECLARE @detailNydFile VARCHAR(500)
DECLARE @summaryFile VARCHAR(500)
DECLARE @summaryNydFile VARCHAR(500)
DECLARE @cmdQueryout VARCHAR(2000)
SET @rootDir = 'C:\Test\'
SET @year = DATEPART(YEAR, GETDATE())
SET @month = RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(MONTH, GETDATE())), 2)
SET @day = RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(DAY, GETDATE())), 2)
SET @yearDir = @rootDir + @year + '\'
SET @monthDir = @rootDir + @year + '\' + @year + @month + '\'
SET @dayDir = @rootDir + @year + '\' + @year + @month + '\' + @year + @month + @day + '\'
SET @baseFileName = @year + @month + @day
PRINT @rootDir
PRINT @year
PRINT @month
PRINT @day
PRINT @yearDir
PRINT @monthDir
PRINT @dayDir
PRINT @baseFileName
INSERT INTO @dirsTable
EXEC master.dbo.xp_subdirs
@rootDir
IF NOT EXISTS ( SELECT directory
FROM @dirsTable
WHERE directory = @year )
EXEC master.sys.xp_create_subdir
@yearDir
DELETE FROM @dirsTable
INSERT INTO @dirsTable
EXEC master.dbo.xp_subdirs
@yearDir
IF NOT EXISTS ( SELECT directory
FROM @dirsTable
WHERE directory = @month )
EXEC master.sys.xp_create_subdir
@monthDir
DELETE FROM @dirsTable
INSERT INTO @dirsTable
EXEC master.dbo.xp_subdirs
@monthDir
IF NOT EXISTS ( SELECT directory
FROM @dirsTable
WHERE directory = @day )
EXEC master.sys.xp_create_subdir
@dayDir
DELETE FROM @dirsTable
SET @detailFile = @dayDir + @baseFileName + ' Detail.txt'
SET @detailNydFile = @dayDir + @baseFileName + ' Detail_NYD.txt'
SET @summaryFile = @dayDir + @baseFileName + ' Summary.txt'
SET @summaryNydFile = @dayDir + @baseFileName + ' Summary_NYD.txt'
PRINT @detailFile
PRINT @detailNydFile
PRINT @summaryFile
PRINT @summaryNydFile
IF @errorCode = 0
BEGIN
PRINT 'Error Code: ' + CAST(@errorCode AS CHAR(1))
SET @cmdQueryout = 'bcp "EXEC DB_NAME.dbo.spGetDetailRecords" queryout "' + @detailFile + '" -c -Uusername -Ppassword'
PRINT @cmdQueryout
EXEC master..xp_cmdshell
@cmdQueryout
SET @cmdQueryout = 'bcp "EXEC DB_NAME.dbo.spGetDetailNYDRecords" queryout "' + @detailNydFile + '" -c -Uusername -Ppassword'
PRINT @cmdQueryout
EXEC master..xp_cmdshell
@cmdQueryout
SET @cmdQueryout = 'bcp "EXEC DB_NAME.dbo.spGetSummaryRecords" queryout "' + @summaryFile + '" -c -Uusername -Ppassword'
PRINT @cmdQueryout
EXEC master..xp_cmdshell
@cmdQueryout
SET @cmdQueryout = 'bcp "EXEC DB_NAME.dbo.spGetSummaryNYDRecords" queryout "' + @summaryNydFile + '" -c -Uusername -Ppassword'
PRINT @cmdQueryout
EXEC master..xp_cmdshell
@cmdQueryout
END
ELSE
BEGIN
SET @cmdQueryout = 'bcp "SELECT NULL" queryout "' + @detailFile + '" -c -Uusername -Ppassword'
EXEC master..xp_cmdshell
@cmdQueryout
SET @cmdQueryout = 'bcp "SELECT NULL" queryout "' + @detailNydFile + '" -c -Uusername -Ppassword'
EXEC master..xp_cmdshell
@cmdQueryout
SET @cmdQueryout = 'bcp "SELECT NULL" queryout "' + @summaryFile + '" -c -Uusername -Ppassword'
EXEC master..xp_cmdshell
@cmdQueryout
SET @cmdQueryout = 'bcp "SELECT NULL" queryout "' + @summaryNydFile + '" -c -Uusername -Ppassword'
EXEC master..xp_cmdshell
@cmdQueryout
END
END TRY
BEGIN CATCH
EXEC dbo.spGetDatabaseErrorInfo
END CATCH
END
I figured out the issue.
I cannot use the BCP utility while a transaction is open. I must commit the transaction and then use BCP.