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

[eggy168]

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.

 

[AlexCuse]

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

 

[eggy168]

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.

 

[RiverGuy]

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.

 

[eggy168]

So, I will need to change this part?

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

Thanks.

 

[RiverGuy]

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

 

[eggy168]

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

 

[eggy168]

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.