Solved

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

Posted on 2008-06-13
9
1,152 Views
Last Modified: 2011-10-19
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
Comment
Question by:MaryvilleCollege
  • 4
  • 3
9 Comments
 
LVL 6

Expert Comment

by:DocCan11
ID: 21781477
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
 

Author Comment

by:MaryvilleCollege
ID: 21781541
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
 

Author Comment

by:MaryvilleCollege
ID: 21781590
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 6

Expert Comment

by:DocCan11
ID: 21781687
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
 

Author Comment

by:MaryvilleCollege
ID: 21781884
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
 
LVL 6

Expert Comment

by:DocCan11
ID: 21781909
ok.. thanks.. let me look into this some more.. I will try and post before I leave work today
0
 
LVL 6

Accepted Solution

by:
DocCan11 earned 500 total points
ID: 21796441
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now