SQL Server not recognizing aliased table

[Dale Fye]

I've recently upsized an Access b.e. to SQL Server 2000. Most of it is
running great, with very few application modifications. However, I am having
trouble with one report. The query that this report is based on is based on
two subqueries (both of which run fine from within Access). However, when I
try to run the reports query, I get an error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]
The correlation name 'tbl_Nodes_1' is specified multiple times in a FROM
clause. (#1011)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax near
dbo. (#170)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax near
'MS3'. (#170)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax near
the keyword 'NOT'. (#156)

Regarding the first line, I have two copies of the same table, one aliased
as tbl_Nodes_1 in one of the queries. But did not anticipate that this would
be a problem. I can post the queries, but they are rather long. If anyone
can provide some advise, I would greatly appreciate it.

Thanks.

 

[Gary Walter]

I've recently upsized an Access b.e. to SQL Server 2000. Most of it is
running great, with very few application modifications. However, I am
having
trouble with one report. The query that this report is based on is based
on
two subqueries (both of which run fine from within Access). However, when
I
try to run the reports query, I get an error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]
The correlation name 'tbl_Nodes_1' is specified multiple times in a FROM
clause. (#1011)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
dbo. (#170)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
'MS3'. (#170)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
the keyword 'NOT'. (#156)

Regarding the first line, I have two copies of the same table, one aliased
as tbl_Nodes_1 in one of the queries. But did not anticipate that this
would
be a problem. I can post the queries, but they are rather long. If
anyone
can provide some advise, I would greatly appreciate it.

Hi Dale,

I am not an expert, but I believe I fit category "anyone."

Almost all of our mdb's hit the SQL Server for reports.

In almost all cases, we use a "scratch" table(s) for reports
that we empty, then fill based on user-supplied parameters
via a report query.

It always seemed to me that with the complex reports
like we produce (subreports, group headers/footers,
running sums, percentage calcs, etc.)
that you have to "help" that "internal report query"
-- the behind-the-scenes mechinations that can
sometimes give rise to "Multi-level group not allowed"
for instance.

Access reports are so powerful (I love them and have
not moved away from Access for that very reason),
but sometimes they need some simplification help.

I don't know if that is your problem, but if I had
queries that "run fine within Access," I would try
developing the report scratch table(s) as one
strategy.

good luck,

gary

 

[Gary Walter]

Also (meaning no offense if this is obvious)...

Did you try creating a View(s) in Enterprise Manager
that does the work in SQL Server?

or stored procedure?

I forgot to mention that in almost all cases
we fill these scratch tables from Views or Stored Procs...

If you want to post the query(s) though, I will try to sort
through it (them) and see if these old eyes can spot something.

good luck,

gary

I've recently upsized an Access b.e. to SQL Server 2000. Most of it is
running great, with very few application modifications. However, I am
having trouble with one report. The query that this report is based on
is based on two subqueries (both of which run fine from within Access).
However, when I try to run the reports query, I get an error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]
The correlation name 'tbl_Nodes_1' is specified multiple times in a FROM
clause. (#1011)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
dbo. (#170)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
'MS3'. (#170)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
the keyword 'NOT'. (#156)

Regarding the first line, I have two copies of the same table, one
aliased
as tbl_Nodes_1 in one of the queries. But did not anticipate that this
would be a problem. I can post the queries, but they are rather long.
If anyone can provide some advise, I would greatly appreciate it.

Hi Dale,

I am not an expert, but I believe I fit category "anyone."

Almost all of our mdb's hit the SQL Server for reports.

In almost all cases, we use a "scratch" table(s) for reports
that we empty, then fill based on user-supplied parameters
via a report query.

It always seemed to me that with the complex reports
like we produce (subreports, group headers/footers,
running sums, percentage calcs, etc.)
that you have to "help" that "internal report query"
-- the behind-the-scenes mechinations that can
sometimes give rise to "Multi-level group not allowed"
for instance.

Access reports are so powerful (I love them and have
not moved away from Access for that very reason),
but sometimes they need some simplification help.

I don't know if that is your problem, but if I had
queries that "run fine within Access," I would try
developing the report scratch table(s) as one
strategy.

good luck,

gary

 

[Dale Fye]

Gary,

I liked your first recommendation.

This particular application has to be able to run against either Access or
SQL Server backends. So all my queries have to be Access queries against
linked tables. I'll give your first suggestion a try on Monday.

Thanks

Also (meaning no offense if this is obvious)...

Did you try creating a View(s) in Enterprise Manager
that does the work in SQL Server?

or stored procedure?

I forgot to mention that in almost all cases
we fill these scratch tables from Views or Stored Procs...

If you want to post the query(s) though, I will try to sort
through it (them) and see if these old eyes can spot something.

good luck,

gary

I've recently upsized an Access b.e. to SQL Server 2000. Most of it is
running great, with very few application modifications. However, I am
having trouble with one report. The query that this report is based on
is based on two subqueries (both of which run fine from within Access).
However, when I try to run the reports query, I get an error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]
The correlation name 'tbl_Nodes_1' is specified multiple times in a FROM
clause. (#1011)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
dbo. (#170)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
'MS3'. (#170)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect syntax
near
the keyword 'NOT'. (#156)

Regarding the first line, I have two copies of the same table, one
aliased
as tbl_Nodes_1 in one of the queries. But did not anticipate that this
would be a problem. I can post the queries, but they are rather long.
If anyone can provide some advise, I would greatly appreciate it.

Hi Dale,

I am not an expert, but I believe I fit category "anyone."

Almost all of our mdb's hit the SQL Server for reports.

In almost all cases, we use a "scratch" table(s) for reports
that we empty, then fill based on user-supplied parameters
via a report query.

It always seemed to me that with the complex reports
like we produce (subreports, group headers/footers,
running sums, percentage calcs, etc.)
that you have to "help" that "internal report query"
-- the behind-the-scenes mechinations that can
sometimes give rise to "Multi-level group not allowed"
for instance.

Access reports are so powerful (I love them and have
not moved away from Access for that very reason),
but sometimes they need some simplification help.

I don't know if that is your problem, but if I had
queries that "run fine within Access," I would try
developing the report scratch table(s) as one
strategy.

good luck,

gary