Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
eggy168

eggy168

Programmer
Joined
Mar 6, 2002
Messages
220
Location
US
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.
 
Sort by date Sort by votes
This is typically an issue regarding permissions for the SQL Server Agent account.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
So, I will need to change this part?

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

Thanks.
 
Upvote 0 Downvote
Yes, try using the format of \\YourServerName\ShareName\database\databaseName
 
Upvote 0 Downvote
Thank you very much. I will try to run the DTS tonight.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

tekpr00
  • Locked
  • Question Question
Help with concatenation of BIG integers
Replies
0
Views
692
tekpr00
tekpr00
ItHurtsWhenIThink
  • Locked
  • Question Question
Using Distinct and count in same query???
Replies
4
Views
840
gk53
gk53
Fsoumia
  • Locked
  • Question Question
DTS Logging
Replies
2
Views
773
Fsoumia
Fsoumia
Imakeoil
  • Locked
  • Question Question
Used DTS Migration Wizard but can't find the DTS Packs
Replies
1
Views
399
drlex
drlex
WordTechinc
  • Locked
  • Question Question
DTS disable Daily Run
Replies
1
Views
422
drlex
drlex

Part and Inventory Search

Sponsor

Back
Top