http://www.sqlservercentral.com/Forums/Topic562482-149-2.aspx?Update=1

Ultimate security guide:

Test scenario:

1. Computer 1 - SQL server 32 bit edition running on Windows 2003

2. Computer 2 - Windows XP desktop.

On computer 2 create a new folder on c:\...

What sheets this file is containing? Does it contain Sheet1 etc. or does it contain 6322Missing?

I am assuming that you are still running it locally on your SQL server and...

Can you please try replacing this:

select * from opendatasource('Microsoft.Jet.OLEDB.4.0',

'DataSource=C:\PIDExcelTest\6322done.xls;

user id=Admin;password=;Extended properties=Excel 5.0')...[6322missing$]

with this:

sp_addlinkedserver EXCEL,

...

binduldo,

you have to understand a concept of security behind linked server.

Your linked server currently using "YourSQLLogin", not sa.

If you will enter "sa" in your linked server settings, it will work...

RD,

if the owner of sproc on one server is different from the remote user specified on the linked server and different from a SQL user defined on the second server...

Because DTS package is executed under Windows security and linked server under you SQL security..

Can you replace this statement with the following:

EXEC sp_addlinkedsrvlogin

@rmtsrvname = N'VIMachine',

@useself = N'false',

@rmtuser = N'Admin',

@rmtpassword =...

I was using this approach:

http://www.databasejournal.com/features/mssql/article.php/10894_3709441_1

That is a statement you are using:

select * from opendatasource('Microsoft.Jet.OLEDB.4.0',

'DataSource=C:\PIDExcelTest\6322done.xls;

user id=Admin;password=;Extended properties=Excel 5.0')...[6322missing$]

Data Source...

Download a free test release of Apex SQL Diff.

One of your previous postings saying that you have two servers?

Does it mean that your Excel file located on a server different from the server where SQL server is located?

Verify that the security context under which you are logging to the Console and security context of the account running your SQL Agent are the same.

Are you logging in to your SQL server using SQL server authentication or windows authentication?

Shouldn't it be Excel 8.0 for Excel 2002 in extended properties.

Also, MS recommends using OPENDATASOURCE for only infrequent access. Did you try linked server instead?

http://support.microsoft.com/kb/321686

TRUNCATE TABLE defaulted to a table owner.

You can use EXECUTE AS in SPROC.