?
Solved

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

Posted on 2008-06-13
9
Medium Priority
?
1,173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 2000 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

764 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