• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1201
  • Last Modified:

How do I fix Import and Export MSSQL Server Agent Jobs that have started failing?

On 6/9/2208 all of the stored import and export SQL Server Agent jobs started failing.  Two of the imports had be working as of 6/4/2008.  The third job was being tested to make sure that we had our fixed file format correct before scheduling it to run.  I have started getting the following error message:
Message
Date            6/13/2008 1:33:07 PM
Log  - Job History (ApplicationDB-Std. Prospect Short Form 8.0.b-fc2d778c-a0fd-417b-b9b7-2c01f6c1b1f6)
Step ID:1  Server: Server  Job Name:ApplicationDB-Std. Prospect Short Form 8.0.b-fc2d778c-a0fd-417b-b9b7-2c01f6c1b1f6.  Step Name: Report Start     Duration:      00:00:00     Sql Severity: 11
Sql Message ID: 50000
Executed as user: NT AUTHORITY\SYSTEM. The Client Base Folders are not available on the SQL Server's network. [SQLSTATE 42000] (Error 50000).  The step failed.

This is the code that the step that fails is running:
DECLARE @r int
EXECUTE @r = [Server].[master].[dbo].xp_cmdshell 'dir "\\Server\SQL Client Base Folder\ApplicationDB\"', no_output
IF @r <> 0
BEGIN
RAISERROR ('The Client Base Folders are not available on the SQL Server''s network.', 11, 1)  WITH SETERROR
RETURN
END
DECLARE @d datetime
SELECT @d = getdate()
EXECUTE [Server].[ApplicationDB].[dbo].pcUpd_PJOB_Status  @JobGUID = 'fc2d778c-a0fd-417b-b9b7-2c01f6c1b1f6' ,  @Status = 'Running' , @EndDate = @d

I've checked permissions on the folder. I've checked permissions on the job owner.  I've checked the permissions of the owner of the SQL Server Agent service.  I've done a dbcc checkdb on the application database that the imports and exports are running to.  Any thoughts on what else I can do to fix this?
0
MaryvilleCollege
Asked:
MaryvilleCollege
  • 4
  • 3
1 Solution
 
DocCan11Commented:
from the error message it looks like it is executing as the system account which of course would have no permissions on another computer. Since you are using a share I assume the files you are checking for do not exist on the SQL server that is running the job.. You will need to create a user account that has permisison to the files, then configure a credential in SQL server 2005 that maps to this user account, now configure the jobs to run in the context of the credential.... also make sure you give the system the right to access the credential..
0
 
MaryvilleCollegeAuthor Commented:
Actually the share is on the same server as the Database.  I've tried running the job as both the System account and as the Domain Administrator with the same results.
0
 
MaryvilleCollegeAuthor Commented:
The folder's permissions are currently set:
Share Permissions Allow full control -  Administrator, Administrators, Domain Admins, Everyone
Security Permissions are set: Full control: (Local Machine) Administrator, (Local Machine) Administrators, Domain Admins, System, and (Local Machine) Users
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
DocCan11Commented:
could you do me one favour to help diagnose this change the following line

EXECUTE @r = [Server].[master].[dbo].xp_cmdshell 'dir "\\Server\SQL Client Base Folder\ApplicationDB\"',

to

EXECUTE @r = [Server].[master].[dbo].xp_cmdshell 'dir "d:\SQL Client Base Folder\ApplicationDB\"',


let me know the result of it running.. if you still get the same error then it will rule out a problem with ports and network transport
0
 
MaryvilleCollegeAuthor Commented:
I set the job to run as the domain admin who also has the same permissions as the sa account in SQL. I made the change to the EXECUTE line and re-ran the job I am still getting the error:
Message
Executed as user: Domain\DomainAdmin. The Client Base Folders are not available on the SQL Server's network. [SQLSTATE 42000] (Error 50000).  The step failed.
On Step: Report Start
The Job that I changed:
DECLARE @r int
EXECUTE @r = [Server].[master].[dbo].xp_cmdshell 'dir "E:\SQL Client Base Folder\ApplicationDB\"', no_output
IF @r <> 0
BEGIN
RAISERROR ('The Client Base Folders are not available on the SQL Server''s network.', 11, 1)  WITH SETERROR
RETURN
END
DECLARE @d datetime
SELECT @d = getdate()
EXECUTE [Server].[ApplicationDB].[dbo].pcUpd_PJOB_Status  @JobGUID = 'fc2d778c-a0fd-417b-b9b7-2c01f6c1b1f6' ,  @Status = 'Running' , @EndDate = @d

The directory path of the folder:
E:\SQL Client Base Folder\ApplicationDB\
0
 
DocCan11Commented:
ok.. thanks.. let me look into this some more.. I will try and post before I leave work today
0
 
DocCan11Commented:
I am sorry I am not ignoring you .. I was traveling all day yesterday.. Ok... I was not able to reproduce your error.. I created the a job and you can see the steps I used.. I tried it with and without files in the directory and the only time I produced the error you have is when the directory I was using did not exist (typo in my case).. can you look over the screen and see any differences that could help me to guide you further?
sql.jpg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now