Unable to automate import of Access DBs

[VogonPoet]

I have setup a stored procedure to allow me to run any DTS package from Query Analyzer. When I run the stored procedure for a DTS package that moves data from one SQL DB to another it works. However, when I run the stored procedure for packages which transer data from MS Access to SQL, it does not work. Specifically, the log file for the package indicates the step failed and the run time is instantaneous. Also, the log file for the step is not created. If I run this same package from within Enterprise Manager everything works as expected. Also, the Access DBs live on a different computer.

I'm running SQL Server 7.
Any ideas?
The stored procedure is listed below:

CREATE Procedure usp_RunDTSPackage @PkgName as varchar(50)

AS

Declare @hr int
declare @oPkg int

--Create the DTS package object
exec @hr=sp_OACREATE 'DTS.Package', @oPKG OUT
If @hr<>0
Begin
Print '*** Create package object failed - ' + @PkgName
Return
End

--Load the package
Exec @hr=sp_oamethod @oPkg,'LoadFromSqlServer',Null, @ServerName='PMA2', @PackageName=@PkgName, @Flags=256
If @Hr<>0
Begin
Print '*** Load package failed - ' + @PkgName
Return
End

--Execute the package
Exec @hr=sp_oamethod @oPkg, 'Execute'
If @Hr<>0
Begin
Print '*** Execute failed - ' + @PkgName
Return
End

--Cleanup
Exec @hr = sp_oadestroy @oPkg
If @Hr<>0
Begin
Print '*** Destroy package failed - ' + @PkgName
Return
End

Thanks in advance,
Michael

 

[tlbroadbent]

There may be a permissions issue. When you execute the DTS package from EM, it runs on our PC with your login credentials. When you execute the DTS package from Query Analyzer, the package is run on the server with the SQL Server login credentials.

When run on teh server, the DTS package cannot access data on your local hard drives. It cannot use drives mapped in a Windows login on the server. If you provide a network location using the UNC, the SQL Server login must have permissions to the server\share\folder.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[VogonPoet]

Terry,

Thanks for the post, but I do not think permissions are the issue.

- SQL Server is residing on a work station, not a server.
- The network is just a peer-to-peer network with four computers.
- I am the only user of all these computers and I use the same login in all places (same login ID and password).
- I am already using the UNC naming conventions.

I hope this clarifies things.

Thanks again for your help.

Michael

 

[tlbroadbent]

Which version of SQL Server are you running? What is the OS of the workstation where SQL Server is running?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[VogonPoet]

SQL Server 7
SQL Server is running on Windows 2000 Pro
The other computer is running Windows XP

 

[tlbroadbent]

Are you running the developer edition or MSDE? Standard and Enterprise Editions require a Server OS to run. In my previous post, I assumed you were running SQL Server as a service on a server.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[VogonPoet]

I'm running the standard edition for the past three years.

 

[VogonPoet]

Terry,

You are absolutely right about the permissions issue. I created a test package and a test Access db on the local computer and it works. I created another test package referencing an identical db on another computer and it fails.

But I am rather confused. As I indicated, I am the only user on this peer-to-peer network and I use the same logins and passwords on all the computers.

Do you have any ideas? Also, I know almost nothing about networking. If MS didn't make it so easy to setup a peer-to-peer network I'd be in deep doo doo.

Michael

 

[tlbroadbent]

You indicate that you run Standard edition but you don't have a server OS on the PCs. If you do run SQL Server standard (I have my doubts.) then what is the Login used to start the SQL Server service? Is it the same login? Does the DTS package reference mapped drives?

I'm not familiar with peer-to-per networks and how permissions are granted. I probably can't be of any help in this situation.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm