dbTalk Databases Forums  

URGENT - Please help - newbie question

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts


Discuss URGENT - Please help - newbie question in the microsoft.public.sqlserver.dts forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Ron Mann
 
Posts: n/a

Default URGENT - Please help - newbie question - 12-02-2003 , 05:00 PM






Hi,

We have an Access97 database, that lives on a file server running NT40SP6a,
and not running SQL.

We are trying to build a DTS package on an NT40SP6a/SQL7SP4 server that will
open the Access database and populate a SQL database from it.

Everything works fine when we execute the DTS package directly from
Enterprise Manager, because it runs in the logged-in user's security
context. When we try to schedule the package, it fails with the following
result -

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147008507 (80074005)
Error string: Unspecified error
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts.hlp
Help context: 1100
Error Detail Records:
Error: -2147008507 (80074005); Provider Error: 0 (0)
Error string: Unspecified error
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts.hlp
Help context: 1100
Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8)
Error string: The Microsoft Jet database engine cannot open the file
'\\<server_name>\<share_name>\db.mdb'. It is already opened exclusively
by another user, or you need permission to view its data.
Error. The step failed.

The DTS package can't run under the Local System account, because it runs on
the SQL server, which has a different Local System account than the file
server, so we can't pass through the authentication on the Share. Similarly,
we can't map a drive to the file server and have it work, because the drive
map is associated with the logged-in user, and doesn't carry over to the
Local System account.

Does anyone have any idea how to make the scheduled package run in a domain
user's context without reconfiguring the SQLServerAgent to run in a domain
account?

Any help is much appreciated.

Thanks.



Reply With Quote
  #2  
Old   
Rufin
 
Posts: n/a

Default RE: URGENT - Please help - newbie question - 12-02-2003 , 05:41 PM






Copy your Mdb file to same drive as MSSQL and it will be OK.
I had the same problem with mdb file in Novell maped drive.
SQL doesn't see other drives

Rufin from Poland.

Reply With Quote
  #3  
Old   
Rufin
 
Posts: n/a

Default RE: URGENT - Please help - newbie question - 12-02-2003 , 05:46 PM



Copy mdb file to the same drive as MSSQL and everything will be OK.
I had the same problem with mdb file on Novell mapped drive.
MSSQL (server service) does'nt see other (user) drives.

Rufin from poland.

Reply With Quote
  #4  
Old   
Ron Mann
 
Posts: n/a

Default Re: URGENT - Please help - newbie question - 12-02-2003 , 05:47 PM



Hi,

We tried that. It works, but doesn't solve the problem. We need to keep
the Access db where it currently resides. The task of moving it would play
havoc with our users.

Thanks anyway.

"Rufin" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:
Copy your Mdb file to same drive as MSSQL and it will be OK.
I had the same problem with mdb file in Novell maped drive.
SQL doesn't see other drives.

Rufin from Poland.



Reply With Quote
  #5  
Old   
Rufin
 
Posts: n/a

Default Re: URGENT - Please help - newbie question - 12-02-2003 , 06:01 PM



So. Try "net use" commands to map drives before DTS execute.
Try to include command "dir drive: > c:\test.txt" before DTS execute. It will help you to understand Does MSSQL (server serivice) see proper drive.

Rufin from poland.


Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.