SQL Agent Job reports that is succeeded. If I manually run the Stored Procedure, it works every time but it will not create the file when ran via the SQL Agent Job.
My user has rights to the folder as does the user used for SQL Agent Service and SQL Server Service.
Has anyone ran into this issue? Any ideas?
Welcome. where is the destination file location in relation to the sql server?
A hard drive on the server in its own folder.
SELECT @sBCP = 'bcp "SELECT * FROM SF_XXXXXXX_AAAAAAA.dbo.tmpHROnBoardingExtract ORDER BY [Last Name], [First Name], [Middle Name], [Employee ID]" queryout H:\AAAAAAA\HROnboard_Nightly_Extract.csv -T ';I also tried including a User Name and Password and the Server Name but that didn't help either.
does this letter mapping H exist on that server?
H:\AAAAAAA\
Yes, it does exist on the server. When I run the Stored Procedure manually the file is written out. It seems to be a difference in permissions between my user running the SP and the Agent running it. the following is what is in the Step for the Agent Job. It works manually as well.
IF (DB_NAME() LIKE 'SF_XXXXXXX_AAAAAAA')
BEGIN
IF OBJECT_ID(N'dbo.sysCustomExtractForAAAAAAAHROnBoard_SP', N'P')IS NOT NULL
BEGIN
EXECUTE dbo.sysCustomExtractForAAAAAAAHROnBoard_SP;
END
END
oh. I am not sure. Wonder if it an issue of delegation and hopping? What if in the SQL Job rather than call the stored procedure, embed the bcp stuff in the SQL job step itself?
By doing the bcp in a batch file it works. So, I will just move on with that theory.
Thank you very much for helping!