Error reading a linked Excel spreadsheet

This problem just started happening (though the process had been working
without a problem for several months):
SQL to create link:
--if the link exists drop it
IF EXISTS (SELECT srvname FROM master.dbo.sysservers srv WHERE srv.srvid !=
0 AND srv.srvname = N'Excel')
EXEC master.dbo.sp_dropserver @.server=N'Excel', @.droplogins='droplogins'
--create the link
EXEC sp_addlinkedserver 'Excel', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
@.FileName, NULL, 'Excel 5.0'
--add the login
EXEC sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
--SQL to read linked data (which throws below error):
EXEC sp_tables_ex Excel
--(this also throws same error)
select *
INTO ExcelData
from Excel...[' + @.SheetName + ']
The link to the excel spreadsheet can be made but when you try to read the
data you get:
[OLE/DB provider returned message: Could not find installable ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I have tried the following which are suggestions from searching the internet:
1. Making sure the registry entries are there.
2. Renaming Msexcl40.dll and the opening Access and running Detect and
Repair, which placed a new Msexcl40.dll in the system32 directory
3. Restarting the SQL Server Service and restarting the server
4. I tried different excel files
I have linked to the same files used above from my local SQL server and had
no problem reading the data.
I don’t know what else to do. Can someone help me with this problem?
Harolds
Hello Harolds,
From the error message, it seems jet driver has issues. You may want to
reinstall Jet SP8 to test:
829558.KB.EN-US Information About Jet 4.0 Service Pack 8
http://support.microsoft.com/default...B;EN-US;829558
239114: How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
http://support.microsoft.com/default...b;en-us;239114
If the issue persists, please try to reinstall MDAC to see if it helps:
1. Find the file C:\windows\inf\mdac.inf (%SYSTEMROOT%\inf\mdac.inf)
(the INF folder is hidden so you will have to make in viewable:
click on Tools, Folder options..., View, click Show hidden files and
folders).
2. Right click on the file and choose install.
3. When prompted to get file, on the Locate File dialog box that results,
click Browse. You may want to direct to Win2003 SP1 setup CD or folders
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.

Error reading a linked Excel spreadsheet

This problem just started happening (though the process had been working
without a problem for several months):
SQL to create link:
--if the link exists drop it
IF EXISTS (SELECT srvname FROM master.dbo.sysservers srv WHERE srv.srvid !=
0 AND srv.srvname = N'Excel')
EXEC master.dbo.sp_dropserver @.server=N'Excel', @.droplogins='droplogins'
--create the link
EXEC sp_addlinkedserver 'Excel', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
@.FileName, NULL, 'Excel 5.0'
--add the login
EXEC sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
--SQL to read linked data (which throws below error):
EXEC sp_tables_ex Excel
--(this also throws same error)
select *
INTO ExcelData
from Excel...[' + @.SheetName + ']
The link to the excel spreadsheet can be made but when you try to read the
data you get:
[OLE/DB provider returned message: Could not find installable ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I have tried the following which are suggestions from searching the internet
:
1. Making sure the registry entries are there.
2. Renaming Msexcl40.dll and the opening Access and running Detect and
Repair, which placed a new Msexcl40.dll in the system32 directory
3. Restarting the SQL Server Service and restarting the server
4. I tried different excel files
I have linked to the same files used above from my local SQL server and had
no problem reading the data.
I don’t know what else to do. Can someone help me with this problem?
HaroldsHello Harolds,
From the error message, it seems jet driver has issues. You may want to
reinstall Jet SP8 to test:
829558.KB.EN-US Information About Jet 4.0 Service Pack 8
http://support.microsoft.com/defaul...KB;EN-US;829558
239114: How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
http://support.microsoft.com/defaul...kb;en-us;239114
If the issue persists, please try to reinstall MDAC to see if it helps:
1. Find the file C:\windows\inf\mdac.inf (%SYSTEMROOT%\inf\mdac.inf)
(the INF folder is hidden so you will have to make in viewable:
click on Tools, Folder options..., View, click Show hidden files and
folders).
2. Right click on the file and choose install.
3. When prompted to get file, on the Locate File dialog box that results,
click Browse. You may want to direct to Win2003 SP1 setup CD or folders
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.|||Hi guys !!!
I have the same problem, I'm using the same version of SQL, Excel & MDAC in
my local pc and the production server, however, the issue is only in the ser
ver.
Harolds: Please keep me posted about the way you solve this.
My Best Regards.|||I never got it fixed, I moved the process to another server where the error
does not occur.
--
Harolds
"velort" wrote:

> Hi guys !!!
> I have the same problem, I'm using the same version of SQL, Excel &
> MDAC in my local pc and the production server, however, the issue is
> only in the server.
> Harolds: Please keep me posted about the way you solve this.
> My Best Regards.
>
> --
> velort
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1450885.html
>

Error pulling unicode data from DB2 Linked Server

Greetings,

We have been Selecting data from A DB2 AS400 via Linked Servers in SQL 7.

We began to pull data from new intstance/tables that contain some fields with foreign characters. (double byte, unicode) Some data will pull fine, then we will get an error:

Unexpected NULL value returned for column '[MYLINKEDSERVER].[DB2NAME1].[DB2NAME2].[MYTABLENAME].DESC2' from the OLE DB provider 'MSDASQL'. This column cannot be NULL.

The DBA from the other country tells me that the field is never null.

KB article Q239458 seemed related but doesn't help. We are running SP4, and will be trying on SP3 soon.

Is there any way to turn off this checking for NULL? My guess is that it interprets the unicode as a NULL incorrectly. I've tried to explicitly cast into varchar(30.)

Sometimes this fields is readable in English and it contains data that is critical when we can see it.

Thanks In Advance,
MikeyrowThe Work-Around developed was to use a pass-thru query to the DB2. I used a NULLIF function to replace any nulls, and that prevented the OLE-DB layer from erroring and failing.

Ideally we would want the DBA in the other country to fix this themselves, but sometimes time won't allow the cogs to churn enough to get the ideal solution developed.

Mike Rowland

Error open/accessing a MSSQL Linked Server

Hi!

Im having the fallowing error when I try to open/access a linked server
Error:
Error 7302: Could not create an instance of OLE DB provider 'MSOLAP.2'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSOLAP.2 returned 0x80004005].

I have already tried to create the server using SEM and also with SQL syntax in ISQLW.
Code:
EXEC sp_addlinkedserver
@.server='LINKED_OLAP',
@.srvproduct='',
@.provider='MSOLAP.2',
@.datasrc='MyServerName.MyDomain,
@.catalog='MyDataBase'

Im allowed to create the linked server but I cant use it.

Information:
OS: Windows 2003EE
SQL: MSSQL 2000 + SP3a
MDAC: MDAC2.8 rtm

Thanks,
Paulowhen referring to objects defined in a linked server, you should use the convention:
servername.databasename.userid.object|||That it's not the problem for sure.|||I'd see if any of Microsoft's (http://search.microsoft.com/search/results.aspx?view=msdn&st=b&na=82&qu=0x80004005+OLAP&s=1) ideas help.

-PatP|||Thanks, but none of the links apply to.

Paulo

error message in view to a linked server

arno,
Here is someone about a year ago getting a very similar error:
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1245831&SiteID=1
It seems that datatype mapping is at the root of the problem, so perhaps
this article (and its references) will help.
http://msdn2.microsoft.com/en-us/library/ms175970.aspx
But I have never seen this error myself.
RLF
"arno" <nospam@.invalid> wrote in message
news:ey2LLfeUIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a firebird 1.5 application. In my sql-server 2000 I created a
> linked server to firebird using the firebird odbc driver. I cannot query
> one table because I get the following error:
> " [Non-interface error: Column 'ERHALTENABSCHLAG' (compile-time ordinal
> 35) of object 'LVVERW' was reported to have a DBTYPE of 5 at compile time
> and 131 at run time]. (Source: Microsoft OLE DB Provider for SQL Server)
> (SQL State: 01000) (NativeError: 7300)Error -2147217900 "
> How can I solve tis problem?
> I cannot find any corrupt data in that column. I can query the table w/o
> problems from an Access Database with odbc-links to the table. I can
> Import the table into an sql-server database using the odbc-driver w/o a
> problem, it is just impossible to run a query on the table of the linked
> server.
> regards
> arno
>
arno,
I don't want to post the whole thread, but these are some of the comments.
#1 - IgorB
I found that the problem is usually in the data type of the columns defined
in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but
if you convert the integer columns in a NUMBER the error will disappear. The
same with the Oracle VARCHAR2.
#2 - Hans G
It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005).It only occurs on
Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby
only; if the oracle-table has fields of type "number". When I alter the
oracle-number-fields to a more precise type of e.g. number(10), the problem
is solved.
#3 - Kev dock
It appears, from reading forums, that a field defined as "TEST NUMBER" is
typeless whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or
OLEDB drivers get confused as they dont know what to convert these things
to.
RLF
"arno" <nospam@.invalid> wrote in message
news:uiVC3iCVIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hello Russel,
>
> I cannot follow the link, and cannot google the postID. Can cut/paste the
> thread?
>
> I think it does not say what to do in a case of error.
> arno
>