×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DTS is working, but it is not running in schedule?

DTS is working, but it is not running in schedule?

DTS is working, but it is not running in schedule?

(OP)
Hi,

I have a DTS in SQL Server 2000 that truncated the table in SQL 2000 and Select * from a table in Access then import into the table.  It works no problem in the DTS section.  However, when I schedule the DTS that run every night, it had an error and said it couldn't find the Access table.  When I checked the DTS itself, it works no problem.  Any ideas to solve this problem?

Thank you very much.

RE: DTS is working, but it is not running in schedule?

This is typically an issue regarding permissions for the SQL Server Agent account.

Hope this helps,

Alex

----signature below----
Majority rule don't work in mental institutions

My Crummy Web Page

RE: DTS is working, but it is not running in schedule?

(OP)
I am running it as a SA account, is it regarding permissions issue in SQL Agent Account?

I tried to write the store procedure instead of using the DTS.  I have an error on it and it keeps saying the connection erro.  Can anyone helps?

Create Procedure [dbo],[spXX] As
truncate table [DatabaseName].[dbo].[TableName]
insert into [DatabaseName].[dbo].[TableName]
Select
Convert (Char(10),GetDate(),101) as as_of,
Field1,
Field2,
Field3
From OpenDataSource
('SQLOLEDB','Provider=Microsoft.Jet.OLEDB.4.0;Data Source = S:\FolderName\database\databaseName')
Go

Thanks.

RE: DTS is working, but it is not running in schedule?

Your S: drive is probably a mapped drive, which your SQL Agent Account is unaware of.  You might try using the UNC path to the MDB.

RE: DTS is working, but it is not running in schedule?

(OP)
So, I will need to change this part?

From OpenDataSource
('SQLOLEDB','Provider=Microsoft.Jet.OLEDB.4.0;DataSource=UNCpath')

Thanks.

RE: DTS is working, but it is not running in schedule?

Yes, try using the format of \\YourServerName\ShareName\database\databaseName

RE: DTS is working, but it is not running in schedule?

(OP)
Thank you very much. I will try to run the DTS tonight.  

RE: DTS is working, but it is not running in schedule?

(OP)
I found the DTS is not having any problem, but somehow connecting to the Novell Network at night is causing the error.  

I tested it by copying the entire database from the Novell Network to the Local C Drive and schedule to run it again, it works great.  

I think my solution is either ask the Network guy to help me or create a step to copy the S drive Access.mdb to the C drive.  Before I bug the Network guy, I am wondering can I write a step to copy the S drive Access.mdb ot the C drive? Is it workable?  If so, can anyone guilde me how to do this?
Thanks a lot.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close