C
Chris
Hi! Sorry in advance for the length of this post! I have
an Access 2000 project (back end is SQL Server - I think
7.0) that I am administering but did not create. I would
appreciate help with a view that is to query an Excel
spreadsheet that is a linked server.
Although I 'appear' to have no problem linking to the
spreadsheet, when I try to run my query I keep getting an
ADO error ([OLE/DB provider returned message: Neither the
isolation level nor a strengthening of it is supported.]
Could not start a transaction for OLE DB
provider 'Microsoft.Jet.OLEDB.4.0.'). I tried following
the instructions in Knowledge base article 306397 but to
no avail.
This problem does not exist when the same spreadsheet is
first linked to an external Access database, which is
subsequently identified as the linked server.
The working stored procedure to link to the Access
database is as follows:
Alter Procedure procLinkINMAST
As
exec sp_addlinkedserver
@server = 'INMASTServer',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@catalog = 'INMastCatalog',
@datasrc = '\\macdiddy\shared\ItemMaster_sep10.mdb'
exec procLinkINMASTLogin
return
The SQL for the view that works based on the above linked
server is:
SELECT fpartno AS PNum, fdescript AS PDescription,
f2totcost AS PCost, frev AS Prev FROM
INMASTServer.. .INMAST WHERE (f2totcost > 0)
I changed the stored procedure to the following:
Alter Procedure procLinkINMAST
As
exec sp_addlinkedserver
@server = 'INMASTServer',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@provstr = 'Excel 5.0',
@datasrc = '\\macdiddy\shared\Itemmaster_sep9.xls'
exec procLinkINMASTLogin
return
I changed the view to:
SELECT fpartno AS PNum, fdescript AS PDescription,
f2totcost AS PCost, frev AS Prev FROM
INMASTServer.. .Sheet1$ WHERE (f2totcost > 0)
What am I missing??
Thanks for any and all assistance!
an Access 2000 project (back end is SQL Server - I think
7.0) that I am administering but did not create. I would
appreciate help with a view that is to query an Excel
spreadsheet that is a linked server.
Although I 'appear' to have no problem linking to the
spreadsheet, when I try to run my query I keep getting an
ADO error ([OLE/DB provider returned message: Neither the
isolation level nor a strengthening of it is supported.]
Could not start a transaction for OLE DB
provider 'Microsoft.Jet.OLEDB.4.0.'). I tried following
the instructions in Knowledge base article 306397 but to
no avail.
This problem does not exist when the same spreadsheet is
first linked to an external Access database, which is
subsequently identified as the linked server.
The working stored procedure to link to the Access
database is as follows:
Alter Procedure procLinkINMAST
As
exec sp_addlinkedserver
@server = 'INMASTServer',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@catalog = 'INMastCatalog',
@datasrc = '\\macdiddy\shared\ItemMaster_sep10.mdb'
exec procLinkINMASTLogin
return
The SQL for the view that works based on the above linked
server is:
SELECT fpartno AS PNum, fdescript AS PDescription,
f2totcost AS PCost, frev AS Prev FROM
INMASTServer.. .INMAST WHERE (f2totcost > 0)
I changed the stored procedure to the following:
Alter Procedure procLinkINMAST
As
exec sp_addlinkedserver
@server = 'INMASTServer',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@provstr = 'Excel 5.0',
@datasrc = '\\macdiddy\shared\Itemmaster_sep9.xls'
exec procLinkINMASTLogin
return
I changed the view to:
SELECT fpartno AS PNum, fdescript AS PDescription,
f2totcost AS PCost, frev AS Prev FROM
INMASTServer.. .Sheet1$ WHERE (f2totcost > 0)
What am I missing??
Thanks for any and all assistance!