quering an excel limked server

hi all,
I've configured an excel file as a linked server to my sql 2000 server (win
2003)
I used kb 814398 to do that, and now if I run a query againt the excel file
from the server I do get the results.
the problem occures when I try to run the same query from the query analyzer
on a computer in the network. ofcourse, I connect and query againt my sql
server.
I get the following massage:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'microsoft.jet.oledb.4.0' reported an error.
[OLE/DB provider returned message: 'I:\prices\opelfrontera9698.xls' is not a
valid path. Make sure that the path name is spelled correctly and that you
are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'microsoft.jet.oledb.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
again, if I run the same query from the query analyzer on the server, there
is no error massage.
thanks,
elad.
Hi
I assume you followed the KB abd restarted the server?
If this is on a mapped network drive, you may want to try specifying a UNC
name instead.
John
"???? ????" wrote:

> hi all,
> I've configured an excel file as a linked server to my sql 2000 server (win
> 2003)
> I used kb 814398 to do that, and now if I run a query againt the excel file
> from the server I do get the results.
> the problem occures when I try to run the same query from the query analyzer
> on a computer in the network. ofcourse, I connect and query againt my sql
> server.
> I get the following massage:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'microsoft.jet.oledb.4.0' reported an error.
> [OLE/DB provider returned message: 'I:\prices\opelfrontera9698.xls' is not a
> valid path. Make sure that the path name is spelled correctly and that you
> are connected to the server on which the file resides.]
> OLE DB error trace [OLE/DB Provider 'microsoft.jet.oledb.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> again, if I run the same query from the query analyzer on the server, there
> is no error massage.
> thanks,
> elad.

Queries will be the death of me

Hello! My query skills are in need of some help. I've simplified my tables
in order to explain what it is that I'm trying to do. I need this to create
dynamic checkboxes on an asp.net page and to have the checkbox values set as
the tables are drawn dynamically. Anyway, here goes:
Table A
ItemID <bigint>
Title <string>
Table B
PurchaseID <bitint>
ItemID <smallint>
I have two tables (A and B). The two tables are linked on field ItemID. I
need to make a query that will return ALL of the values from table A along
with a boolean value stating whether or not table B contains an item - but
only for a specific purchaseID.
For example:
Table A (ItemID, Title)
1 VCR
2 DVD
3 Television
4 Phone
Table B (PurchaseID, ItemID)
1 1 (VCR)
1 2 (DVD)
1 3 (Tel)
2 2 (DVD)
2 1 (...)
2 4
3 4
3 1
For the above data I need a list of the Items from tableA and a boolean
value showing whether the item exists in Table B for a specific PurchaseID,
in this case I used PurchaseID = 2.
Result Table (ItemID, Title, Boolean)
1 VCR 1
2 DVD 1
3 Television 0
4 Phone 1
The result shows that there exists a VCR, DVD and Phone for purchaseID, but
no Television.
Grateful for any help...
Hello ChrisN,
I think this is what you're looking for (with tables and data included):
-- DROP TABLE A - Items
CREATE TABLE A (
ItemID int NOT NULL, -- PK
Title varchar (50) NOT NULL)
go
ALTER TABLE A WITH NOCHECK ADD PRIMARY KEY CLUSTERED (ItemID)
go
-- DROP TABLE B - Purchases
CREATE TABLE B (
PurchaseID int NOT NULL, -- PK
ItemID int NOT NULL) -- PK
go
ALTER TABLE B WITH NOCHECK ADD PRIMARY KEY CLUSTERED (PurchaseID, ItemID)
go
-- Insert test sample data into table A:
insert into A values (1, 'VCR')
insert into A values (2, 'DVD')
insert into A values (3, 'Television')
insert into A values (4, 'Phone')
go
-- Insert test sample data into table B:
insert into B values (1, 1) -- VCR
insert into B values (1, 2) -- DVD
insert into B values (1, 3) -- Television
insert into B values (2, 2) -- DVD
insert into B values (2, 1) -- VCR
insert into B values (2, 4) -- Phone
insert into B values (3, 4) -- Phone
insert into B values (3, 1) -- VCR
go
SELECT TableA.ItemID, TableA.Title
FROM A TableA
INNER JOIN B TableB ON (TableA.ItemID = TableB.ItemID)
WHERE PurchaseID = 2
go
However, are you also interested in a FTS query based upon the above INNER
JOIN between tables A & B since you posted this question in the fulltext
newsgroup? If so, could you post an example of what you're looking for?
Thanks,
John
"ChrisN" <cnewald@.hotmail.com> wrote in message
news:uJV$f$b1EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello! My query skills are in need of some help. I've simplified my
tables
> in order to explain what it is that I'm trying to do. I need this to
create
> dynamic checkboxes on an asp.net page and to have the checkbox values set
as
> the tables are drawn dynamically. Anyway, here goes:
> Table A
> ItemID <bigint>
> Title <string>
> Table B
> PurchaseID <bitint>
> ItemID <smallint>
> I have two tables (A and B). The two tables are linked on field ItemID.
I
> need to make a query that will return ALL of the values from table A along
> with a boolean value stating whether or not table B contains an item - but
> only for a specific purchaseID.
> For example:
> Table A (ItemID, Title)
> 1 VCR
> 2 DVD
> 3 Television
> 4 Phone
> Table B (PurchaseID, ItemID)
> 1 1 (VCR)
> 1 2 (DVD)
> 1 3 (Tel)
> 2 2 (DVD)
> 2 1 (...)
> 2 4
> 3 4
> 3 1
> For the above data I need a list of the Items from tableA and a boolean
> value showing whether the item exists in Table B for a specific
PurchaseID,
> in this case I used PurchaseID = 2.
> Result Table (ItemID, Title, Boolean)
> 1 VCR 1
> 2 DVD 1
> 3 Television 0
> 4 Phone 1
> The result shows that there exists a VCR, DVD and Phone for purchaseID,
but
> no Television.
> Grateful for any help...
>
|||Thank you John for your response. Being a dough head I did post to the
wrong newsgroup and realized after it sent. I'll give your solution a try
in a little while.
Thanks again,
Chris.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OlzoyOf1EHA.1300@.TK2MSFTNGP14.phx.gbl...
> Hello ChrisN,
> I think this is what you're looking for (with tables and data included):
> -- DROP TABLE A - Items
> CREATE TABLE A (
> ItemID int NOT NULL, -- PK
> Title varchar (50) NOT NULL)
> go
> ALTER TABLE A WITH NOCHECK ADD PRIMARY KEY CLUSTERED (ItemID)
> go
> -- DROP TABLE B - Purchases
> CREATE TABLE B (
> PurchaseID int NOT NULL, -- PK
> ItemID int NOT NULL) -- PK
> go
> ALTER TABLE B WITH NOCHECK ADD PRIMARY KEY CLUSTERED (PurchaseID, ItemID)
> go
> -- Insert test sample data into table A:
> insert into A values (1, 'VCR')
> insert into A values (2, 'DVD')
> insert into A values (3, 'Television')
> insert into A values (4, 'Phone')
> go
> -- Insert test sample data into table B:
> insert into B values (1, 1) -- VCR
> insert into B values (1, 2) -- DVD
> insert into B values (1, 3) -- Television
> insert into B values (2, 2) -- DVD
> insert into B values (2, 1) -- VCR
> insert into B values (2, 4) -- Phone
> insert into B values (3, 4) -- Phone
> insert into B values (3, 1) -- VCR
> go
> SELECT TableA.ItemID, TableA.Title
> FROM A TableA
> INNER JOIN B TableB ON (TableA.ItemID = TableB.ItemID)
> WHERE PurchaseID = 2
> go
> However, are you also interested in a FTS query based upon the above INNER
> JOIN between tables A & B since you posted this question in the fulltext
> newsgroup? If so, could you post an example of what you're looking for?
> Thanks,
> John
>
>
> "ChrisN" <cnewald@.hotmail.com> wrote in message
> news:uJV$f$b1EHA.3416@.TK2MSFTNGP09.phx.gbl...
> tables
> create
> as
> I
> PurchaseID,
> but
>
sql