Solved

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

Posted on 2008-06-13
9
1,163 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

839 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