Solved

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

Posted on 2008-06-13
9
1,167 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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 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