Query is not runnig

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello Reader!

I have designed a query with two tables. One is linked (with Sql server
2000) table and othe is Local table, I have defned relationship between One
field (both are text fields). When I try to execute this query, execution
starts, but the query does not display any records even after 1 hour or more.

Could you help me to resolve this problem?
 
Not without more information we can't.

Start with the SQL of the Query and the DDL of the tables/views/
indexes used therein.

Cheers,
Jason Lepack
 
Hello Reader!

I have designed a query with two tables. One is linked (with Sql server
2000) table and othe is Local table, I have defned relationship between One
field (both are text fields). When I try to execute this query, execution
starts, but the query does not display any records even after 1 hour or more.

Could you help me to resolve this problem?
Click to expand...

One issue is that you cannot enforce referential integrity between a remote
table and a local table. Any relationship you define is purely for
documentation purposes - it won't affect the query, and it won't create any
indexes (as will happen if the tables are both local).

Are there appropriate indexes on these fields in the two tables? How big are
the tables? Could you post the SQL view of the query, or at least the JOIN
clause?


John W. Vinson [MVP]
 
John W. Vinson said:
One issue is that you cannot enforce referential integrity between a remote
table and a local table. Any relationship you define is purely for
documentation purposes - it won't affect the query, and it won't create any
indexes (as will happen if the tables are both local).

Are there appropriate indexes on these fields in the two tables? How big are
the tables? Could you post the SQL view of the query, or at least the JOIN
clause?


John W. Vinson [MVP]
Click to expand...
 
John W. Vinson said:
One issue is that you cannot enforce referential integrity between a remote
table and a local table. Any relationship you define is purely for
documentation purposes - it won't affect the query, and it won't create any
indexes (as will happen if the tables are both local).

Are there appropriate indexes on these fields in the two tables? How big are
the tables? Could you post the SQL view of the query, or at least the JOIN
clause?


John W. Vinson [MVP]
Click to expand...
Thanks for reply Vinson,

SELECT dbo_RUNINGORDERS_ADM.RORDATE AS [Date], dbo_UNITS.AUCODE AS UCode,
dbo_UNITS.SUNAME AS Unit, dbo_UNITS.REGIONCODE AS RegCode, dbo_UNITS.REGION
AS Region, dbo_UNITS.UTYPECODE AS UTypeCode, dbo_UNITS.UTYPE AS UnitType,
RetSF([RORRSFCODE]) AS Shift, RetOcc([ROROCCCODE]) AS Segment, ([RORSEQ]) AS
[Order No], [RORTOTAMT]*1 AS Amount, [RORTOTDISCOUNT]*1 AS Discount
FROM dbo_UNITS INNER JOIN dbo_RUNINGORDERS_ADM ON dbo_UNITS.SUCODE =
dbo_RUNINGORDERS_ADM.RORPRJCODE;

Both of joined fields property is 'NO'
dbo_RUNINGORDERS_ADM has 750000 (approx) records
and the dbo_UNITS has 31 records.
 
Both of joined fields property is 'NO'
dbo_RUNINGORDERS_ADM has 750000 (approx) records
and the dbo_UNITS has 31 records.
Click to expand...


The indexed property is No? Then it's IMPOSSIBLE for SQL or JET to process the
query efficiently. Since there is no index on the field, Access must do a full
table scan *thirty-one times*.

If you have design rights to dbo_RUNINGORDERS_ADM, create a nonunique Index on
RORPRJCODE. That will make a *dramatic* difference. An index (unique or not, I
can't tell from the data you provide) on dbo_UNITS.SUCODE will help too,
though probably far less impressively.

If you don't have design access... talk to your DBA and explain what you're
trying to do.

John W. Vinson [MVP]
 
Hello,

One more thing Mr. Vinson,

The sql database has his own dbo_Units table, when I import this table (as a
linked table) and replace the local dbo_Units with linked dbo_Units then
query works fine.
What is this ??
Actually I want to use a local (dbo_Units) table to change few values in
differnt fields, occassionally, while I dont have writing rights to the
Linked tables.

JB
 
The sql database has his own dbo_Units table, when I import this table (as a
linked table) and replace the local dbo_Units with linked dbo_Units then
query works fine.
What is this ??
Actually I want to use a local (dbo_Units) table to change few values in
differnt fields, occassionally, while I dont have writing rights to the
Linked tables.
Click to expand...

I suspect - but am not certain - that you will lose a lot of the advantage of
indexing if the two tables in your query are in different databases (dbo_Units
local and the other table remote), since neither the local JET database engine
nor the remote SQL engine has access to both tables.

I'm not sure what you mean by "when I import this table (as a linked table)".
It's either an imported table, or a linked table - it can't be both!

John W. Vinson [MVP]
 
Back
Top