Opening password protected Excel files

I have a stored procedure in which I am importing data from a password protected Excel file to a SQL table. The file comes from our client and the password cannot be removed before sending, per his company policy.

I would like to create a DTS package to open the file and import the data. Is there a way to pass the password to the DTS package?

Thanks!May refer to this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q211378).|||I can do this if I am using Excel, but I need to do it from a stored procedure. Is this possible using TSQL?

Thanks!|||Honestly I never tried, you may search under SQL DTS (http://www.sqldts.com) for further information.|||I got it!!! One of my coworkers (who was out when I needed the answer yesterday) had the solution!

dtsrun /Sservername /Uuid /Ppwd /Npackagename /AFileName:8= excelfilename /APassword:8=excelpwd

:-)|||Good keep it up, your reply help others.

OpenDataSource with Microsoft.Jet.OLEDB.4 Excel from stored proced

I have a stored procedure which has the following line:
select * from FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\MachineName\shared\account.xls";...Excel 8.0...
This stored procedure was called from a VB application. It's odd that
ONLY when ADO connection user need SA database role
AND \\MachineName\shared\account.xls was physical located in the same
machine of DATABASE
the stored procedure would work.
Otherwise it returned error like:
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not give an
y
information about the error.]. 0 7300 -2147217900 Microsoft OLE DB Provider
for SQL Server 01000OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. The provider did not give any information about the error. 0 7399
-2147217900 Microsoft OLE DB Provider for SQL Server 42000
Database is SQL Server 2000 SP4. Registry DisallowAdhocAccess is 0.
Any suggestion?Because the security reson, I don't want ADO connection user has the SA role
,
and Excel file can located in any shared folder in the LAN.
"david" wrote:

> I have a stored procedure which has the following line:
> select * from FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\MachineName\shared\account.xls";...Excel 8.0...
> This stored procedure was called from a VB application. It's odd that
> ONLY when ADO connection user need SA database role
> AND \\MachineName\shared\account.xls was physical located in the same
> machine of DATABASE
> the stored procedure would work.
> Otherwise it returned error like:
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
any
> information about the error.]. 0 7300 -2147217900 Microsoft OLE DB Provide
r
> for SQL Server 01000OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error. The provider did not give any information about the error. 0 7399
> -2147217900 Microsoft OLE DB Provider for SQL Server 42000
> Database is SQL Server 2000 SP4. Registry DisallowAdhocAccess is 0.
> Any suggestion?
>sql

OpenDataSource with Microsoft.Jet.OLEDB.4 Excel from stored proced

I have a stored procedure which has the following line:
select * from FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\MachineName\shared\account.xls";...Excel 8.0...
This stored procedure was called from a VB application. It's odd that
ONLY when ADO connection user need SA database role
AND \\MachineName\shared\account.xls was physical located in the same
machine of DATABASE
the stored procedure would work.
Otherwise it returned error like:
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not give any
information about the error.]. 0 7300 -2147217900 Microsoft OLE DB Provider
for SQL Server 01000OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. The provider did not give any information about the error. 0 7399
-2147217900 Microsoft OLE DB Provider for SQL Server 42000
Database is SQL Server 2000 SP4. Registry DisallowAdhocAccess is 0.
Any suggestion?Because the security reson, I don't want ADO connection user has the SA role,
and Excel file can located in any shared folder in the LAN.
"david" wrote:
> I have a stored procedure which has the following line:
> select * from FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\MachineName\shared\account.xls";...Excel 8.0...
> This stored procedure was called from a VB application. It's odd that
> ONLY when ADO connection user need SA database role
> AND \\MachineName\shared\account.xls was physical located in the same
> machine of DATABASE
> the stored procedure would work.
> Otherwise it returned error like:
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give any
> information about the error.]. 0 7300 -2147217900 Microsoft OLE DB Provider
> for SQL Server 01000OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error. The provider did not give any information about the error. 0 7399
> -2147217900 Microsoft OLE DB Provider for SQL Server 42000
> Database is SQL Server 2000 SP4. Registry DisallowAdhocAccess is 0.
> Any suggestion?
>

OpenDataSource to Excel

OpenDataSource does not work:
SELECT * FROM
OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\temp\TEST.XLS;Extended properties=Excel 5.0')...[Tabelle1$]
The error message is:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
On our newest installation with Windows2003Server SP2 running with
SQLServer2005 SP2 the select-statement fails.
(On WindowsXP SP2 with SQLServer2005 SP2 its still working fine)
SQLServer-Service is running as a local-user that is an administrator
The user calling the select-statement is the same local-user
The Excel-File is on a local drive
Georg
Yes, I have seen this error many times and could not be able to solve the
problem. It worked just fine on SS2000 and does not on SS2005.
What if you create SSIS package to move the data?
"Georg" <Georg@.discussions.microsoft.com> wrote in message
news:63193B94-1355-44C4-A38E-A645A3F343E1@.microsoft.com...
> OpenDataSource does not work:
> SELECT * FROM
> OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\temp\TEST.XLS;Extended properties=Excel
> 5.0')...[Tabelle1$]
> The error message is:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> On our newest installation with Windows2003Server SP2 running with
> SQLServer2005 SP2 the select-statement fails.
> (On WindowsXP SP2 with SQLServer2005 SP2 its still working fine)
> SQLServer-Service is running as a local-user that is an administrator
> The user calling the select-statement is the same local-user
> The Excel-File is on a local drive
>
>
|||Thank you for quick reply !
I'm afraid SSIS package is no solution for me...
"Uri Dimant" wrote:

> Georg
> Yes, I have seen this error many times and could not be able to solve the
> problem. It worked just fine on SS2000 and does not on SS2005.
> What if you create SSIS package to move the data?
>
> "Georg" <Georg@.discussions.microsoft.com> wrote in message
> news:63193B94-1355-44C4-A38E-A645A3F343E1@.microsoft.com...
>
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:OT1SVhsnIHA.5944@.TK2MSFTNGP03.phx.gbl:
[vbcol=seagreen]
> Georg
> Yes, I have seen this error many times and could not be able to solve
> the problem. It worked just fine on SS2000 and does not on SS2005.
> What if you create SSIS package to move the data?
>
> "Georg" <Georg@.discussions.microsoft.com> wrote in message
> news:63193B94-1355-44C4-A38E-A645A3F343E1@.microsoft.com...
FWIW, the analogous
SELECT * FROM
OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\Documents and Settings\Chris\My Documents\RSJ.xls;Extended
properties=Excel 5.0')...[Sheet1$]
works fine for me with SQL Server 2005 Express.
|||We could not reproduce the problem on a Test-Server with W2003Server/SP2 +
SS2005/SP2 - here it works fine also. Currently we try to find further
difference(s) between Test- and Production-Server
"Chris.Cheney" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:OT1SVhsnIHA.5944@.TK2MSFTNGP03.phx.gbl:
>
> FWIW, the analogous
> SELECT * FROM
> OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\Documents and Settings\Chris\My Documents\RSJ.xls;Extended
> properties=Excel 5.0')...[Sheet1$]
> works fine for me with SQL Server 2005 Express.
>

OpenDataSource to Excel

OpenDataSource does not work:
SELECT * FROM
OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\temp\TEST.XLS;Extended properties=Excel 5.0')...[Tabelle1$]
The error message is:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
On our newest installation with Windows2003Server SP2 running with
SQLServer2005 SP2 the select-statement fails.
(On WindowsXP SP2 with SQLServer2005 SP2 its still working fine)
SQLServer-Service is running as a local-user that is an administrator
The user calling the select-statement is the same local-user
The Excel-File is on a local driveGeorg
Yes, I have seen this error many times and could not be able to solve the
problem. It worked just fine on SS2000 and does not on SS2005.
What if you create SSIS package to move the data?
"Georg" <Georg@.discussions.microsoft.com> wrote in message
news:63193B94-1355-44C4-A38E-A645A3F343E1@.microsoft.com...
> OpenDataSource does not work:
> SELECT * FROM
> OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\temp\TEST.XLS;Extended properties=Excel
> 5.0')...[Tabelle1$]
> The error message is:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> On our newest installation with Windows2003Server SP2 running with
> SQLServer2005 SP2 the select-statement fails.
> (On WindowsXP SP2 with SQLServer2005 SP2 its still working fine)
> SQLServer-Service is running as a local-user that is an administrator
> The user calling the select-statement is the same local-user
> The Excel-File is on a local drive
>
>|||Thank you for quick reply !
I'm afraid SSIS package is no solution for me...
"Uri Dimant" wrote:
> Georg
> Yes, I have seen this error many times and could not be able to solve the
> problem. It worked just fine on SS2000 and does not on SS2005.
> What if you create SSIS package to move the data?
>
> "Georg" <Georg@.discussions.microsoft.com> wrote in message
> news:63193B94-1355-44C4-A38E-A645A3F343E1@.microsoft.com...
> > OpenDataSource does not work:
> >
> > SELECT * FROM
> > OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> > 'Data Source=c:\temp\TEST.XLS;Extended properties=Excel
> > 5.0')...[Tabelle1$]
> >
> > The error message is:
> > OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> > returned message "Unspecified error".
> > Msg 7303, Level 16, State 1, Line 1
> > Cannot initialize the data source object of OLE DB provider
> > "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> >
> > On our newest installation with Windows2003Server SP2 running with
> > SQLServer2005 SP2 the select-statement fails.
> > (On WindowsXP SP2 with SQLServer2005 SP2 its still working fine)
> >
> > SQLServer-Service is running as a local-user that is an administrator
> > The user calling the select-statement is the same local-user
> > The Excel-File is on a local drive
> >
> >
> >
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:OT1SVhsnIHA.5944@.TK2MSFTNGP03.phx.gbl:
> Georg
> Yes, I have seen this error many times and could not be able to solve
> the problem. It worked just fine on SS2000 and does not on SS2005.
> What if you create SSIS package to move the data?
>
> "Georg" <Georg@.discussions.microsoft.com> wrote in message
> news:63193B94-1355-44C4-A38E-A645A3F343E1@.microsoft.com...
>> OpenDataSource does not work:
>> SELECT * FROM
>> OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
>> 'Data Source=c:\temp\TEST.XLS;Extended properties=Excel
>> 5.0')...[Tabelle1$]
FWIW, the analogous
SELECT * FROM
OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\Documents and Settings\Chris\My Documents\RSJ.xls;Extended
properties=Excel 5.0')...[Sheet1$]
works fine for me with SQL Server 2005 Express.|||We could not reproduce the problem on a Test-Server with W2003Server/SP2 +
SS2005/SP2 - here it works fine also. Currently we try to find further
difference(s) between Test- and Production-Server
"Chris.Cheney" wrote:
> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:OT1SVhsnIHA.5944@.TK2MSFTNGP03.phx.gbl:
> > Georg
> > Yes, I have seen this error many times and could not be able to solve
> > the problem. It worked just fine on SS2000 and does not on SS2005.
> > What if you create SSIS package to move the data?
> >
> >
> > "Georg" <Georg@.discussions.microsoft.com> wrote in message
> > news:63193B94-1355-44C4-A38E-A645A3F343E1@.microsoft.com...
> >> OpenDataSource does not work:
> >>
> >> SELECT * FROM
> >> OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> >> 'Data Source=c:\temp\TEST.XLS;Extended properties=Excel
> >> 5.0')...[Tabelle1$]
> FWIW, the analogous
> SELECT * FROM
> OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\Documents and Settings\Chris\My Documents\RSJ.xls;Extended
> properties=Excel 5.0')...[Sheet1$]
> works fine for me with SQL Server 2005 Express.
>

OpenDataSource "Unspecified Error" connecting to Excel file

OpenDataSource gives "Unspecified Error" when connecting to local Excel
spreadsheet file with account not in local administrators group, on
Microsoft Windows 2003 / Microsoft SQL Server 2000 sp3a.
I am having a permissions problem using the OpenDataSource command to open
an Excel spreadsheet located on our Sql Server. The query is as follows:
SELECT * FROM opendatasource('Microsoft.Jet.OLEDB.4.0', 'Data
Source="C:\myPath\tempExcel.xls";User ID=Admin;Password=;Extended
properties=Excel 8.0')...['US Mail$'] tblImport
As you can see, the file is on the local Sql Server. All domain users have
full control to the myPath directory. The Sql Server service (and Sql
Server agent service) is running as the domain administrator account. We
are using integrated security to authenticate with the Sql Server. I even
tried setting up the proxy account as the domain administrator thinking that
maybe the opendatasource would use that.
The error I get when running this query on a remote machine as a domain user
through Query Analyzer is "Unspecified Error" in the Jet Initialization.
Even if I add domain users to the server administrator role, I still get the
error. However, if I add that domain users account to the local
administrators group on the Sql Server, the query works. I know this is not
an NTFS permission issue, as I have auditing turned on and there are no
object security failures in the event logs.
So my question is: what specific permissions are required for a domain user
to run an OpenDataSource query like this without having to add them to the
local administrators group on the Sql Server? Having to add the domain
users that need to run this query to the Sql Servers local administrators
group is simply unacceptable...
Thanks,
Frank Jones
Hi Frank,
From your descriptions, I understood that your non-admin users are not able
to execute OpenDataSource with the error message "Unspecified Error" while
only those who have system admin permissions could do it. Have I understood
you? Correct me if I was wrong.
First of all, please make sure your domain users have the access permission
to the following Registry Key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance
Name>\Providers\Microsoft.Jet.OLEDB.4.0
value must not exist or be 1.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Providers\Microsoft.Jet.OL
EDB.4.0
DisallowAdhocAccess = 0 should be added to allow access.
NOTE that you *must* Reboot the server for these registry changes to take
effect.
Secondly, please make sure your users have access permission to
C:\WINDOWS\system32\msjet40.dll and its folder
If all above does not work, I would like to suggest you using Regmon to
monitor which registry key was checked and denied for your domain users
with the following steps
1. Select the correct version for your Regmon and then download it from the
web site below
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Install Regmon
3. Login as member of Local Administrator,
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file1
4. Login as member of non-Administrator
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file2
Compare the difference between file1 and file2, give the permissions to
those ACCESSDENY keys to non-admin users.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

OpenDataSource "Unspecified Error" connecting to Excel file

OpenDataSource gives "Unspecified Error" when connecting to local Excel
spreadsheet file with account not in local administrators group, on
Microsoft Windows 2003 / Microsoft SQL Server 2000 sp3a.
I am having a permissions problem using the OpenDataSource command to open
an Excel spreadsheet located on our Sql Server. The query is as follows:
SELECT * FROM opendatasource('Microsoft.Jet.OLEDB.4.0', 'Data
Source="C:\myPath\tempExcel.xls";User ID=Admin;Password=;Extended
properties=Excel 8.0')...['US Mail$'] tblImport
As you can see, the file is on the local Sql Server. All domain users have
full control to the myPath directory. The Sql Server service (and Sql
Server agent service) is running as the domain administrator account. We
are using integrated security to authenticate with the Sql Server. I even
tried setting up the proxy account as the domain administrator thinking that
maybe the opendatasource would use that.
The error I get when running this query on a remote machine as a domain user
through Query Analyzer is "Unspecified Error" in the Jet Initialization.
Even if I add domain users to the server administrator role, I still get the
error. However, if I add that domain users account to the local
administrators group on the Sql Server, the query works. I know this is not
an NTFS permission issue, as I have auditing turned on and there are no
object security failures in the event logs.
So my question is: what specific permissions are required for a domain user
to run an OpenDataSource query like this without having to add them to the
local administrators group on the Sql Server? Having to add the domain
users that need to run this query to the Sql Servers local administrators
group is simply unacceptable...
Thanks,
Frank JonesHi Frank,
From your descriptions, I understood that your non-admin users are not able
to execute OpenDataSource with the error message "Unspecified Error" while
only those who have system admin permissions could do it. Have I understood
you? Correct me if I was wrong.
First of all, please make sure your domain users have the access permission
to the following Registry Key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\<Instance
Name>\Providers\Microsoft.Jet.OLEDB.4.0
value must not exist or be 1.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Providers\Microsoft.Jet.OL
EDB.4.0
DisallowAdhocAccess = 0 should be added to allow access.
NOTE that you *must* Reboot the server for these registry changes to take
effect.
Secondly, please make sure your users have access permission to
C:\WINDOWS\system32\msjet40.dll and its folder
If all above does not work, I would like to suggest you using Regmon to
monitor which registry key was checked and denied for your domain users
with the following steps
1. Select the correct version for your Regmon and then download it from the
web site below
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Install Regmon
3. Login as member of Local Administrator,
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file1
4. Login as member of non-Administrator
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file2
Compare the difference between file1 and file2, give the permissions to
those ACCESSDENY keys to non-admin users.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Open Report in MS Word.

I created Report in SQLREporting services. I need open in in Worl, Is it
possible , using "Format=" ; I can open it in Excel by giving "Format=Excel".
plz help me .its an urgent..Not directly.
You could format it as MHTML or Excel, and open it in
Word.
Word can contain Excel spreadsheets as objects, so you could may be do
something fancy in office vba.
But I guess you want to export directly to Word, this is not possible
with RS out of the box.
Chris
pramod wrote:
> I created Report in SQLREporting services. I need open in in Worl, Is
> it possible , using "Format=" ; I can open it in Excel by giving
> "Format=Excel". plz help me .its an urgent..

Open Excel File

hey
I am just trying a simple query of an Excel file.

Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\MyFile.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

and i am getting this error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

I know for a fact that the path is correct and that it is the path of the server. So what is the problem with this??

tibordoes someone have the spreadsheet open?|||nope not at all|||oh ok i found out the issue. i was using vb with excel and was terminating the debug program without closing out excel so i had several in memory still