resource file 'msmdsrv.rll' for locale '1049' could not be loaded

Hi forum,

does anyone know the cause of this error message?

23-Mar-2007 16:03:50

Computer: <ComputerName>

Source: MSSQLServerOLAPService

Category: None

Type: Error

Event ID: 25

Description: The resource file 'msmdsrv.rll' for locale '1049' could not be loaded. Switching to US English (1033) if available.

Tia,

/Gert

There is no localized version for locale 1049, or it was not installed.|||

Hello I'm with the same problem but from spanish(1034):

How can I solve it?

|||You need to install a version that includes the specific resource files for your locale. I'm not sure if one is available or not.

Resolving an attribute member from a member property

What will be the fastest way to resolve an atttribute member from one of its member properties, e.g. the key value from the source database assuming that the attribute key uses the dimension surrogate key for its KeyColumn?

You can use the member properties Key0, Key1, ... KeyN. Each of theses represents one of the columns in the attributes key columns.

E.g.

select [Time By Day].[Years].Members dimension properties key0, key1 on 0
from budget

|||

This is not what I was asking. Consider the following key attribute and member properties

Product (attribute key)

- ProductOriginal Key (member property and/or attribute)

The question was how can I resolve the Product attribute by knowing the ProductOriginalKey member property?

|||

Since there is many-to-one relationship between related attributes, there are actually multiple products with the same ProductOrdinal. You can easily get the set of all Product attribute members which have certain ProductOrdinal value, by using

Exists(Product.Product.Product.MEMBERS, Product.ProductOrdinal.&[key_of_ordinal])

HTH,

Mosha (http://www.mosha.com/msolap)

|||Thank you. Unfortunately, Exists doesn't seem to work as an allowed set security filter with parent-child dimensions as I reported on connect with CTP2 SP2. Neither does crossjoining works (*). A bug perhaps? Meanwhile, any idea what works to crossjoin two sets to create an allowed set?|||Sorry - I didn't see you mentioning before that it was Parent-Child. Can you please give more concrete description of the structure of dimension, which attributes you are securing etc. Example from Adventure Works would be most appreciated.|||

The link in my previous post demonstrates how this can be reproduced with AW. Assuming the AdventureWorks sample cube:

1. Create a new role Reviewer
2. Create an allowed set on the Employee dimension (cube level) as follows:
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997])
3. Browse the cube under Reviewer by Employee. Notice that all employees are returned. In comparison, the following query returns only two employees and their supervisors (as it should).
select [Measures].[Reseller Sales Amount] on 0,
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997]) on 1
from [Adventure Works]

4. Trying [Employee].[Employees].Members * [Employee].[Hire Year].&[1997] throws an exception, as well as
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

|||

Well, since you are defining dimension security - there is no need to ever write Exists as expression of any allowed set. Dimension security will do Exists itself. So all you need to do is to define security on Hire Year attribute instead of trying to do it on Employees attribute. Simply specify [Employee].[Hire Year].&[1997] as expression for the Allowed Set on Hire Year attribute - and everything will work as you need.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

This works! Thank you so much for your help. I'd appreciate it if you could answer one more question for me. Let's say that when the user slices by the Sales Territory Region attribute hierarchy of the Sales Territory dimension, I want the user to see only the regions serviced by the allowed employees only. Is the following allowed set expression the most efficient way to do so?

Exists([Sales Territory].[Sales Territory Region].[Sales Territory Region].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

I understand that I cannot use [Employee].[Hire Year].Members since security polices are not applied yet so essentially I have no other choice but to carry the Employee filter to the other dimensions. I am just concerned that with large dimensions this may incur significant performance penalty.

|||Yes, I beleive this is the best way to do it.

Resolving an attribute member from a member property

What will be the fastest way to resolve an atttribute member from one of its member properties, e.g. the key value from the source database assuming that the attribute key uses the dimension surrogate key for its KeyColumn?

You can use the member properties Key0, Key1, ... KeyN. Each of theses represents one of the columns in the attributes key columns.

E.g.

select [Time By Day].[Years].Members dimension properties key0, key1 on 0
from budget

|||

This is not what I was asking. Consider the following key attribute and member properties

Product (attribute key)

- ProductOriginal Key (member property and/or attribute)

The question was how can I resolve the Product attribute by knowing the ProductOriginalKey member property?

|||

Since there is many-to-one relationship between related attributes, there are actually multiple products with the same ProductOrdinal. You can easily get the set of all Product attribute members which have certain ProductOrdinal value, by using

Exists(Product.Product.Product.MEMBERS, Product.ProductOrdinal.&[key_of_ordinal])

HTH,

Mosha (http://www.mosha.com/msolap)

|||Thank you. Unfortunately, Exists doesn't seem to work as an allowed set security filter with parent-child dimensions as I reported on connect with CTP2 SP2. Neither does crossjoining works (*). A bug perhaps? Meanwhile, any idea what works to crossjoin two sets to create an allowed set?|||Sorry - I didn't see you mentioning before that it was Parent-Child. Can you please give more concrete description of the structure of dimension, which attributes you are securing etc. Example from Adventure Works would be most appreciated.|||

The link in my previous post demonstrates how this can be reproduced with AW. Assuming the AdventureWorks sample cube:

1. Create a new role Reviewer
2. Create an allowed set on the Employee dimension (cube level) as follows:
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997])
3. Browse the cube under Reviewer by Employee. Notice that all employees are returned. In comparison, the following query returns only two employees and their supervisors (as it should).
select [Measures].[Reseller Sales Amount] on 0,
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997]) on 1
from [Adventure Works]

4. Trying [Employee].[Employees].Members * [Employee].[Hire Year].&[1997] throws an exception, as well as
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

|||

Well, since you are defining dimension security - there is no need to ever write Exists as expression of any allowed set. Dimension security will do Exists itself. So all you need to do is to define security on Hire Year attribute instead of trying to do it on Employees attribute. Simply specify [Employee].[Hire Year].&[1997] as expression for the Allowed Set on Hire Year attribute - and everything will work as you need.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

This works! Thank you so much for your help. I'd appreciate it if you could answer one more question for me. Let's say that when the user slices by the Sales Territory Region attribute hierarchy of the Sales Territory dimension, I want the user to see only the regions serviced by the allowed employees only. Is the following allowed set expression the most efficient way to do so?

Exists([Sales Territory].[Sales Territory Region].[Sales Territory Region].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

I understand that I cannot use [Employee].[Hire Year].Members since security polices are not applied yet so essentially I have no other choice but to carry the Employee filter to the other dimensions. I am just concerned that with large dimensions this may incur significant performance penalty.

|||Yes, I beleive this is the best way to do it.

Request Length Exceeded

I am working in Visual Studio 2005. I have a Report Model project with a
data source, a data source view which contains all of the tables in my
database (SQL Server 2005), and a Report Model that contains the items that I
want the users to be able to work with.
When I deploy the project, I get the following error:
Error 2 System.Web.Services.Protocols.SoapException: There was an exception
running the extensions specified in the config file. -->
System.Web.HttpException: Maximum request length exceeded. at
System.Web.HttpRequest.GetEntireRawContent() at
System.Web.HttpRequest.get_InputStream() at
System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
inner exception stack trace -- at
System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
HttpContext context, HttpRequest request, HttpResponse response, Boolean&
abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
How do I fix it?
Thank you,
Mark LauserHi, Mark
It sounds like your report model file is kinda large so when you
upload/deploy it to the server, the asp.net http request times out before the
file is fully uploaded (it uses the web service).
You are gonna have to change the value in the maxRequestLength attribute of
the httpRuntime element in the web.config file of your ReportServer and
Report Manager so it doesn't time out. The default size is 4096 KB (4 MB),
but I bet your file is much larger, so change it to something like:
<httpRuntime ... maxRequestLength="151200" .../>
Hope this helps you!
--
Regards,
Thiago Silva
"Mark Lauser" wrote:
> I am working in Visual Studio 2005. I have a Report Model project with a
> data source, a data source view which contains all of the tables in my
> database (SQL Server 2005), and a Report Model that contains the items that I
> want the users to be able to work with.
> When I deploy the project, I get the following error:
> Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> running the extensions specified in the config file. -->
> System.Web.HttpException: Maximum request length exceeded. at
> System.Web.HttpRequest.GetEntireRawContent() at
> System.Web.HttpRequest.get_InputStream() at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> inner exception stack trace -- at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> How do I fix it?
> Thank you,
> Mark Lauser|||Thanks, that worked. My problem was that my web.config file didn't have a
maxRequestLength attribute for the httpRuntime element, so my 'Find' didn't
find anything. Now that I know where it is supposed to be, I added the
attribute and it worked fine.
There must be a default that is used when the attribute is not present.
Thanks again!
Mark Lauser
"HC" wrote:
> Hi, Mark
> It sounds like your report model file is kinda large so when you
> upload/deploy it to the server, the asp.net http request times out before the
> file is fully uploaded (it uses the web service).
> You are gonna have to change the value in the maxRequestLength attribute of
> the httpRuntime element in the web.config file of your ReportServer and
> Report Manager so it doesn't time out. The default size is 4096 KB (4 MB),
> but I bet your file is much larger, so change it to something like:
> <httpRuntime ... maxRequestLength="151200" .../>
> Hope this helps you!
> --
> Regards,
> Thiago Silva
> "Mark Lauser" wrote:
> > I am working in Visual Studio 2005. I have a Report Model project with a
> > data source, a data source view which contains all of the tables in my
> > database (SQL Server 2005), and a Report Model that contains the items that I
> > want the users to be able to work with.
> >
> > When I deploy the project, I get the following error:
> >
> > Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> > running the extensions specified in the config file. -->
> > System.Web.HttpException: Maximum request length exceeded. at
> > System.Web.HttpRequest.GetEntireRawContent() at
> > System.Web.HttpRequest.get_InputStream() at
> > System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> > inner exception stack trace -- at
> > System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> > System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> > HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> > abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> >
> > How do I fix it?
> >
> > Thank you,
> > Mark Lauser|||Yeah, the default is in the machine.config file
(C:\WINDOWS\Microsoft.NET\Framework\[VERSION_NUMBER]\CONFIG).
The aspnet engine follows a hierarchy of config files. If it doesn't
find a setting in the inner-most config, it will keep searching up the
hierarchy until it finds it.
Most settings are defined with a default in the machine.config file.
So if you don't override a setting in your application folder's
web.config, it will use whatever is defined in the machine.config file.
Regards,
Thiago Silva|||Thanks, that makes sense.
My problem was further complicated by the fact that there was no
maxRequestLength in my machine.config for the version of the .net framework
that I am working in. It must have defaulted all the way up the hierarchy.
I noticed that the notes in the file say to only include a setting if you
want to override the default (for better performance).
Best Regards,
Mark Lauser
"tafs7" wrote:
> Yeah, the default is in the machine.config file
> (C:\WINDOWS\Microsoft.NET\Framework\[VERSION_NUMBER]\CONFIG).
> The aspnet engine follows a hierarchy of config files. If it doesn't
> find a setting in the inner-most config, it will keep searching up the
> hierarchy until it finds it.
> Most settings are defined with a default in the machine.config file.
> So if you don't override a setting in your application folder's
> web.config, it will use whatever is defined in the machine.config file.
> Regards,
> Thiago Silva
>

Repost + Alternative

Hi,
Haven't found an answer yet but I'm still hoping. When we try to import data
using the Other (ODBC Data Source) and select the DSN created for the Excel
spreadsheet then no data is listed. However, if we choose "Microsoft Excel
97-2000" as the Data Source and select the appropriate excel file when
importing data then the Excel data is listed. What underlying technique is
used when selecting "Microsoft Excel 97-2000" and can we use it in Transact
SQL?
Thanks,
Alain
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server
tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> 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.].
>
> 2) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling
that
> some setting (registry) is missing or is incorrect or that a file is
missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> Cortex
Alan,
this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
Not sure if that error is generic but I get that error when I leave the File
open in Excel
Also see KB below on possible issue with version of Jet DLL
http://support.microsoft.com/default...b;en-us;818182
Andy Ball
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Haven't found an answer yet but I'm still hoping. When we try to import
data
> using the Other (ODBC Data Source) and select the DSN created for the
Excel
> spreadsheet then no data is listed. However, if we choose "Microsoft Excel
> 97-2000" as the Data Source and select the appropriate excel file when
> importing data then the Excel data is listed. What underlying technique is
> used when selecting "Microsoft Excel 97-2000" and can we use it in
Transact[vbcol=seagreen]
> SQL?
> Thanks,
> Alain
>
> "Alain Sienaert" wrote:
> tables.
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
provider[vbcol=seagreen]
give[vbcol=seagreen]
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
application[vbcol=seagreen]
run[vbcol=seagreen]
data.
> that
> missing
>
|||Alain,
Thanks for your suggestions however they do not solve the problem.
1) I checked the MSJET40.dll and this is the latest one available. I also
created a Test.udl file with the following properties:
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
When pressing Test Connection the program replies with Test connection
succeeded.
2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
pressing Test Connection the program also replies with Test connection
succeeded.
3) When we include one of our sql statements that fail when run from the
Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
Package without any problems.
Provider:
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Alan,
> this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
> Not sure if that error is generic but I get that error when I leave the
File[vbcol=seagreen]
> open in Excel
> Also see KB below on possible issue with version of Jet DLL
> http://support.microsoft.com/default...b;en-us;818182
>
> Andy Ball
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> data
> Excel
Excel[vbcol=seagreen]
is[vbcol=seagreen]
> Transact
MS[vbcol=seagreen]
> (Windows
> Excel
> provider
> give
MS[vbcol=seagreen]
> (Windows
> Excel
Cannot[vbcol=seagreen]
> application
IDBInitialize::Initialize[vbcol=seagreen]
> run
> data.
files).
>
|||Andy,
You can tell that it is getting late (about 12:45 AM). I call you Alain and
forget to close my message with a greeting.
Kind regards,
Alain
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Alain,
> Thanks for your suggestions however they do not solve the problem.
> 1) I checked the MSJET40.dll and this is the latest one available. I also
> created a Test.udl file with the following properties:
> - Provider: Microsoft OLE DB Provider for ODBC Drivers
> - Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
> When pressing Test Connection the program replies with Test connection
> succeeded.
>
> 2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
> pressing Test Connection the program also replies with Test connection
> succeeded.
>
> 3) When we include one of our sql statements that fail when run from the
> Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
> Package without any problems.
>
>
> Provider:
> "Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
> news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
covers.[vbcol=seagreen]
> File
import[vbcol=seagreen]
> Excel
technique[vbcol=seagreen]
> is
> MS
> MS
> Cannot
> IDBInitialize::Initialize
and[vbcol=seagreen]
to[vbcol=seagreen]
feeling
> files).
>
|||you can call me betty ..:-)
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
What happens if you run the following from QA :
EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
cheers,
Andy.
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> Andy,
> You can tell that it is getting late (about 12:45 AM). I call you Alain
and[vbcol=seagreen]
> forget to close my message with a greeting.
> Kind regards,
> Alain
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
also[vbcol=seagreen]
when[vbcol=seagreen]
the[vbcol=seagreen]
> covers.
the[vbcol=seagreen]
> import
the[vbcol=seagreen]
when[vbcol=seagreen]
> technique
server[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
not[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
> and
Excel[vbcol=seagreen]
> to
the[vbcol=seagreen]
> feeling
is
>
|||Dear Betty ,
Thanks for your assistance so far...
Please find my answers below.
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
Yes, I did run the DTS from a Client workstation. Running it in Design Mode
directly on server does not work. Same error as before:
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
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
No, running the command on the workstation does work. It fails when running
it on the server. Please note that the client workstation account has less
access rights than the account used when running the tests on the server
(administrator account). I guess the client workstation is using its own
configuration (OLE DB objects) to access the appropriate file?
What happens if you run the following from QA : EXEC master.dbo.xp_cmdshell
'dir \\server\file.xls'
It returns the expected information such as Volume, Volume Serial Number,
Date and Timestamp, etc...
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
The SQL Server runs under a domain account with a password that never
expires (functional account). This account has administrator rights on the
sql server and is used when running the tests on the server. Please note
that the Excel file is on the same server.
I have the feeling that something is wrong in the registry or so, since I am
able to access Excel 4.0 files.
Cheers,
Alain
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ux30zW4XEHA.3972@.TK2MSFTNGP12.phx.gbl...
> you can call me betty ..:-)
> 1. So when u run in DTS u are in Design Mode on a Client workstation ?
When[vbcol=seagreen]
> happens if you schedule same package under SQLAgent - does it still work ?
> 2. When you run from SQL Server QA and it fails you are also running from
> the workstation right ?
> What happens if you run the following from QA :
> EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
> 3. What account does SQL Server Service start under ? System or a domain
> account ? When using QA it will used the Service Account credentials to
> access the spreadsheet (presuming you are SA equivalent)
> cheers,
> Andy.
>
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> and
> also
> when
the[vbcol=seagreen]
> the
> the
> the
"Microsoft[vbcol=seagreen]
> when
> server
> SP6a -
is[vbcol=seagreen]
> an
> not
> SP6a -
Driver[vbcol=seagreen]
is[vbcol=seagreen]
> an
Driver][vbcol=seagreen]
format[vbcol=seagreen]
> Excel
seem[vbcol=seagreen]
> the
file
> is
>

Repost + Alternative

Hi,
Haven't found an answer yet but I'm still hoping. When we try to import data
using the Other (ODBC Data Source) and select the DSN created for the Excel
spreadsheet then no data is listed. However, if we choose "Microsoft Excel
97-2000" as the Data Source and select the appropriate excel file when
importing data then the Excel data is listed. What underlying technique is
used when selecting "Microsoft Excel 97-2000" and can we use it in Transact
SQL?
Thanks,
Alain
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server
tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> 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.].
>
> 2) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driv
er] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initializ
e
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling
that
> some setting (registry) is missing or is incorrect or that a file is
missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> CortexAlan,
this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
Not sure if that error is generic but I get that error when I leave the File
open in Excel
Also see KB below on possible issue with version of Jet DLL
http://support.microsoft.com/defaul...kb;en-us;818182
Andy Ball
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Haven't found an answer yet but I'm still hoping. When we try to import
data
> using the Other (ODBC Data Source) and select the DSN created for the
Excel
> spreadsheet then no data is listed. However, if we choose "Microsoft Excel
> 97-2000" as the Data Source and select the appropriate excel file when
> importing data then the Excel data is listed. What underlying technique is
> used when selecting "Microsoft Excel 97-2000" and can we use it in
Transact
> SQL?
> Thanks,
> Alain
>
> "Alain Sienaert" wrote:
>
> tables.
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
provider[vbcol=seagreen]
give[vbcol=seagreen]
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
application[vbcol=seagreen]
run[vbcol=seagreen]
data.[vbcol=seagreen]
> that
> missing
>|||Alain,
Thanks for your suggestions however they do not solve the problem.
1) I checked the MSJET40.dll and this is the latest one available. I also
created a Test.udl file with the following properties:
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
When pressing Test Connection the program replies with Test connection
succeeded.
2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
pressing Test Connection the program also replies with Test connection
succeeded.
3) When we include one of our sql statements that fail when run from the
Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
Package without any problems.
Provider:
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Alan,
> this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
> Not sure if that error is generic but I get that error when I leave the
File
> open in Excel
> Also see KB below on possible issue with version of Jet DLL
> http://support.microsoft.com/defaul...kb;en-us;818182
>
> Andy Ball
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> data
> Excel
Excel[vbcol=seagreen]
is[vbcol=seagreen]
> Transact
MS[vbcol=seagreen]
> (Windows
> Excel
> provider
> give
MS[vbcol=seagreen]
> (Windows
> Excel
Cannot[vbcol=seagreen]
> application
IDBInitialize::Initialize[vbcol=seagreen
]
> run
> data.
files).[vbcol=seagreen]
>|||Andy,
You can tell that it is getting late (about 12:45 AM). I call you Alain and
forget to close my message with a greeting.
Kind regards,
Alain
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
> Alain,
> Thanks for your suggestions however they do not solve the problem.
> 1) I checked the MSJET40.dll and this is the latest one available. I also
> created a Test.udl file with the following properties:
> - Provider: Microsoft OLE DB Provider for ODBC Drivers
> - Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
> When pressing Test Connection the program replies with Test connection
> succeeded.
>
> 2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
> pressing Test Connection the program also replies with Test connection
> succeeded.
>
> 3) When we include one of our sql statements that fail when run from the
> Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
> Package without any problems.
>
>
> Provider:
> "Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
> news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
covers.[vbcol=seagreen]
> File
import[vbcol=seagreen]
> Excel
technique[vbcol=seagreen]
> is
> MS
> MS
> Cannot
> IDBInitialize::Initialize
and[vbcol=seagreen]
to[vbcol=seagreen]
feeling[vbcol=seagreen]
> files).
>|||you can call me betty ..:-)
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
What happens if you run the following from QA :
EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
cheers,
Andy.
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> Andy,
> You can tell that it is getting late (about 12:45 AM). I call you Alain
and
> forget to close my message with a greeting.
> Kind regards,
> Alain
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
also[vbcol=seagreen]
when[vbcol=seagreen]
the[vbcol=seagreen]
> covers.
the[vbcol=seagreen]
> import
the[vbcol=seagreen]
when[vbcol=seagreen]
> technique
server[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
not[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
> and
Excel[vbcol=seagreen]
> to
the[vbcol=seagreen]
> feeling
is[vbcol=seagreen]
>|||Dear Betty ,
Thanks for your assistance so far...
Please find my answers below.
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
Yes, I did run the DTS from a Client workstation. Running it in Design Mode
directly on server does not work. Same error as before:
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
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
No, running the command on the workstation does work. It fails when running
it on the server. Please note that the client workstation account has less
access rights than the account used when running the tests on the server
(administrator account). I guess the client workstation is using its own
configuration (OLE DB objects) to access the appropriate file?
What happens if you run the following from QA : EXEC master.dbo.xp_cmdshell
'dir \\server\file.xls'
It returns the expected information such as Volume, Volume Serial Number,
Date and Timestamp, etc...
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
The SQL Server runs under a domain account with a password that never
expires (functional account). This account has administrator rights on the
sql server and is used when running the tests on the server. Please note
that the Excel file is on the same server.
I have the feeling that something is wrong in the registry or so, since I am
able to access Excel 4.0 files.
Cheers,
Alain
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ux30zW4XEHA.3972@.TK2MSFTNGP12.phx.gbl...
> you can call me betty ..:-)
> 1. So when u run in DTS u are in Design Mode on a Client workstation ?
When
> happens if you schedule same package under SQLAgent - does it still work ?
> 2. When you run from SQL Server QA and it fails you are also running from
> the workstation right ?
> What happens if you run the following from QA :
> EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
> 3. What account does SQL Server Service start under ? System or a domain
> account ? When using QA it will used the Service Account credentials to
> access the spreadsheet (presuming you are SA equivalent)
> cheers,
> Andy.
>
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> and
> also
> when
the[vbcol=seagreen]
> the
> the
> the
"Microsoft[vbcol=seagreen]
> when
> server
> SP6a -
is[vbcol=seagreen]
> an
> not
> SP6a -
Driver[vbcol=seagreen]
is[vbcol=seagreen]
> an
Driver][vbcol=seagreen]
format[vbcol=seagreen]
> Excel
seem[vbcol=seagreen]
> the
file[vbcol=seagreen]
> is
>