ADP/2003 sends wrong query to SQL server

M

Marcus

Hope someone can help.

Access 2003 + latest patches, .adp, SQL server 2000.

When trying to use a sample published by microsoft

http://support.microsoft.com/kb/281811/en-us/

to build a PivotChart in a subform, whose contents change when the current
record changes in the main form, I get an error:

"The column prefix 'dbo.I Stabili' does not match with a table name or alias
name used in the query"

So I have started the profiler on the SQL Server and traced the query, and
here's the output:
******* BEGIN TRACE OUTPUT *****************
SQL:BatchCompleted SET ROWCOUNT 10000 Microsoft Office 2003 DomusUser 0 0
0 0 5552 55 2008-07-02 5:50:16.867

SQL:BatchCompleted SELECT * FROM "dbo"."I - Appartamenti" Microsoft Office
2003 DomusUser 0 120 0 16 5552 55 2008-07-02 15:50:16.867

SQL:BatchCompleted SET ROWCOUNT 0 Microsoft Office 2003 DomusUser 0 0 0 0
5552 55 2008-07-02 15:50:16.883

SQL:BatchCompleted SET FMTONLY ON select "ID Appartamenti" from (SELECT
dbo.[I - Appartamenti].[ID Appartamenti], dbo.[I - Appartamenti].Interno,
dbo.[I - Stabili].Sigla, dbo.[I - Pagamenti].Importo, dbo.[I - Stabili].[Id
Stabile], YEAR(dbo.[I - Pagamenti].Data) AS Anno, MONTH(dbo.[I -
Pagamenti].Data) AS Mese, dbo.[I - Periodi].Canone FROM dbo.[I -
Appartamenti] INNER JOIN dbo.[I - Stabili] ON dbo.[I - Appartamenti].Stabile
= dbo.[I - Stabili].[Id Stabile] INNER JOIN dbo.[I - Contratti] ON dbo.[I -
Appartamenti].[ID Appartamenti] = dbo.[I - Contratti].Appartamento INNER
JOIN dbo.[I - Periodi] ON dbo.[I - Contratti].[ID Contratto] = dbo.[I -
Periodi].[ID Contratto] INNER JOIN dbo.[I - Pagamenti] ON dbo.[I -
Periodi].[ID Periodo] = dbo.[I - Pagamenti].[ID periodo] ) AS DRVD_TBL WHERE
1=2 SET FMTONLY OFF Microsoft Office 2003 DomusUser 0 215 0 0 5552 55
2008-07-02 15:50:16.900

RPC:Completed declare @P1 int

set @P1=NULL

exec sp_prepare @P1 output, N'@P1 int', N'SELECT * FROM (SELECT dbo.[I -
Appartamenti].[ID Appartamenti], dbo.[I - Appartamenti].Interno, dbo.[I -
Stabili].Sigla, dbo.[I - Pagamenti].Importo, dbo.[I - Stabili].[Id Stabile],
YEAR(dbo.[I - Pagamenti].Data) AS Anno, MONTH(dbo.[I - Pagamenti].Data) AS
Mese, dbo.[I - Periodi].Canone FROM dbo.[I - Appartamenti] INNER JOIN
dbo.[I - Stabili] ON dbo.[I - Appartamenti].Stabile = dbo.[I - Stabili].[Id
Stabile] INNER JOIN dbo.[I - Contratti] ON dbo.[I - Appartamenti].[ID
Appartamenti] = dbo.[I - Contratti].Appartamento INNER JOIN dbo.[I -
Periodi] ON dbo.[I - Contratti].[ID Contratto] = dbo.[I - Periodi].[ID
Contratto] INNER JOIN dbo.[I - Pagamenti] ON dbo.[I - Periodi].[ID Periodo]
= dbo.[I - Pagamenti].[ID periodo] ) AS DRVD_TBL WHERE ((@P1 = "ID
Appartamenti")) ORDER BY dbo.[I - Stabili].[Id Stabile], dbo.[I -
Appartamenti].[ID Appartamenti]', 1

select @P1 Microsoft Office 2003 DomusUser 0 57 0 0 5552 55 2008-07-02
15:50:16.913

SQL:BatchCompleted SELECT N'Testing Connection...' SQLAgent - Alert Engine
Administrator STUDIONOLI\Administrator 16 0 0 16 2908 52 2008-07-02
15:50:16.913

SQL:BatchCompleted EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters SQLAgent -
Alert Engine Administrator STUDIONOLI\Administrator 15 99 0 16 2908 52
2008-07-02 15:50:16.930

TraceStop
******* END TRACE OUTPUT *****************

What's wrong?
 
A

a a r o n . k e m p f

uh, do you have a space in your table name?

I'd get rid of that for starters.

-Aaron


Hope someone can help.

Access 2003 + latest patches, .adp, SQL server 2000.

When trying to use a sample published by microsoft

http://support.microsoft.com/kb/281811/en-us/

to build a PivotChart in a subform, whose contents change when the current
record changes in the main form, I get an error:

"The column prefix 'dbo.I Stabili' does not match with a table name or alias
name used in the query"

So I have started the profiler on the SQL Server and traced the query, and
here's the output:
******* BEGIN TRACE OUTPUT *****************
SQL:BatchCompleted SET ROWCOUNT 10000  Microsoft Office 2003  DomusUser 0 0
0 0 5552 55 2008-07-02 5:50:16.867

SQL:BatchCompleted  SELECT * FROM "dbo"."I - Appartamenti"  MicrosoftOffice
2003  DomusUser 0 120 0 16 5552 55 2008-07-02 15:50:16.867

SQL:BatchCompleted SET ROWCOUNT 0  Microsoft Office 2003  DomusUser 00 0 0
5552 55 2008-07-02 15:50:16.883

SQL:BatchCompleted SET FMTONLY ON select "ID Appartamenti" from  (SELECT
dbo.[I - Appartamenti].[ID Appartamenti], dbo.[I - Appartamenti].Interno,
dbo.[I - Stabili].Sigla, dbo.[I - Pagamenti].Importo, dbo.[I - Stabili].[Id
Stabile], YEAR(dbo.[I - Pagamenti].Data) AS Anno, MONTH(dbo.[I -
Pagamenti].Data) AS Mese, dbo.[I - Periodi].Canone FROM dbo.[I -
Appartamenti] INNER JOIN dbo.[I - Stabili] ON dbo.[I - Appartamenti].Stabile
= dbo.[I - Stabili].[Id Stabile] INNER JOIN dbo.[I - Contratti] ON dbo.[I -
Appartamenti].[ID Appartamenti] = dbo.[I - Contratti].Appartamento INNER
JOIN dbo.[I - Periodi] ON dbo.[I - Contratti].[ID Contratto] = dbo.[I -
Periodi].[ID Contratto] INNER JOIN dbo.[I - Pagamenti] ON dbo.[I -
Periodi].[ID Periodo] = dbo.[I - Pagamenti].[ID periodo] ) AS DRVD_TBL WHERE
1=2  SET FMTONLY OFF Microsoft Office 2003  DomusUser 0 215 0 0 5552 55
2008-07-02 15:50:16.900

RPC:Completed declare @P1 int

set @P1=NULL

exec sp_prepare @P1 output, N'@P1 int', N'SELECT  *  FROM (SELECT dbo..[I -
Appartamenti].[ID Appartamenti], dbo.[I - Appartamenti].Interno, dbo.[I -
Stabili].Sigla, dbo.[I - Pagamenti].Importo, dbo.[I - Stabili].[Id Stabile],
YEAR(dbo.[I - Pagamenti].Data) AS Anno, MONTH(dbo.[I - Pagamenti].Data) AS
Mese, dbo.[I - Periodi].Canone FROM dbo.[I - Appartamenti] INNER JOIN
dbo.[I - Stabili] ON dbo.[I - Appartamenti].Stabile = dbo.[I - Stabili]..[Id
Stabile] INNER JOIN dbo.[I - Contratti] ON dbo.[I - Appartamenti].[ID
Appartamenti] = dbo.[I - Contratti].Appartamento INNER JOIN dbo.[I -
Periodi] ON dbo.[I - Contratti].[ID Contratto] = dbo.[I - Periodi].[ID
Contratto] INNER JOIN dbo.[I - Pagamenti] ON dbo.[I - Periodi].[ID Periodo]
= dbo.[I - Pagamenti].[ID periodo] ) AS DRVD_TBL WHERE ((@P1 = "ID
Appartamenti")) ORDER BY dbo.[I - Stabili].[Id Stabile], dbo.[I -
Appartamenti].[ID Appartamenti]', 1

select @P1 Microsoft Office 2003  DomusUser 0 57 0 0 5552 55 2008-07-02
15:50:16.913

SQL:BatchCompleted SELECT N'Testing Connection...' SQLAgent - Alert Engine
Administrator STUDIONOLI\Administrator 16 0 0 16 2908 52 2008-07-02
15:50:16.913

SQL:BatchCompleted EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters SQLAgent -
Alert Engine Administrator STUDIONOLI\Administrator 15 99 0 16 2908 52
2008-07-02 15:50:16.930

TraceStop
******* END TRACE OUTPUT *****************

What's wrong?
Click to expand...
 
M

Marcus

a a r o n . k e m p f @ g m a i l . c o m said:
in message
uh, do you have a space in your table name?

I'd get rid of that for starters.
Click to expand...

All the tables in the DB have spaces in theyr name, like

[C - Contabilità]

.........................
 
A

a a r o n . k e m p f

yah.. well you shouldn't have spaces in your names

JET isn't robust enough to parse the query text if it has spaces and
special characters.
JET isn't a real database.

Maybe SQL Server would work better for you.





uh, do you have a space in your table name?
Click to expand...
I'd get rid of that for starters.
Click to expand...

All the tables in the DB have spaces in theyr name, like

[C - Contabilità]

........................
Click to expand...
 
G

Graham R Seach

I thought Marcus said he was using ADP/SQL Server. But I agree about having
spaces in names.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

message
yah.. well you shouldn't have spaces in your names

JET isn't robust enough to parse the query text if it has spaces and
special characters.
JET isn't a real database.

Maybe SQL Server would work better for you.





uh, do you have a space in your table name?
Click to expand...
I'd get rid of that for starters.
Click to expand...

All the tables in the DB have spaces in theyr name, like

[C - Contabilità]

........................
Click to expand...
 
G

Graham R Seach

Marcus,

Just enclose the object names in square brackets.
SELECT [field name] FROM [table name]

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Marcus said:
uh, do you have a space in your table name?

I'd get rid of that for starters.
Click to expand...

All the tables in the DB have spaces in theyr name, like

[C - Contabilità]

........................
Click to expand...
 
G

Graham R Seach

Marcus,

Do you want to be right in assuming its a bug, or do you want to resolve the
problem and move on?

Have you actually tried putting the object names in square brackets? If it
doesn't work, then you've lost 5 minutes of your life. If it does, then you
can keep working while you wait for Microsoft to get back to you.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Marcus said:
in message
yah.. well you shouldn't have spaces in your names

JET isn't robust enough to parse the query text if it has spaces and
special characters.
JET isn't a real database.

Maybe SQL Server would work better for you.





"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]>
wrote
in message
uh, do you have a space in your table name?
Click to expand...
I'd get rid of that for starters.
Click to expand...

All the tables in the DB have spaces in theyr name, like

[C - Contabilità]

........................
Click to expand...



Guys I am using ADP & SQL server. And I'm not coding the SELECT
statement... Access is doing it.
Access is issueing the wrong query, not me. And about the table names, I
have dozen forms and reports using them with no problem... stored
procedures, you name it.

So there must be a bug in Access when putting a Pivotable in a subform....

I've opened a request of support @ Microsoft and they're following the
case...

Will post follow up if any from them...

Thanks
Click to expand...
 
M

Marcus

Graham R Seach said:
Marcus,

Do you want to be right in assuming its a bug, or do you want to resolve
the problem and move on?

Have you actually tried putting the object names in square brackets? If it
doesn't work, then you've lost 5 minutes of your life. If it does, then
you can keep working while you wait for Microsoft to get back to you.
Click to expand...

Sorry but I don't understand... if it is Acces that builds the underlying
query to the SQl server, where and what should I enclose in brackets?

The only place I could do that is in the Link Child/Master properties in the
subform.

If I do so I get an error "Invalid column name [xxxxxxxx]"

Could you clarify?
 
G

Guest

"The column prefix 'dbo.I Stabili' does not match with a table name or
alias
name used in the query"
Click to expand...

That is not a SQL error: that is a PivotChart error. There is no column
prefix 'dbo.I Stabili', so the PivotChart can't build the chart. The column
prefix shown in your SQL is 'dbo.I - Stabili'.

Is this a transcription error when you were writing the message,
or have you constructed the PivotChart incorrectly?

(david)


Marcus said:
Hope someone can help.

Access 2003 + latest patches, .adp, SQL server 2000.

When trying to use a sample published by microsoft

http://support.microsoft.com/kb/281811/en-us/

to build a PivotChart in a subform, whose contents change when the current
record changes in the main form, I get an error:

"The column prefix 'dbo.I Stabili' does not match with a table name or alias
name used in the query"

So I have started the profiler on the SQL Server and traced the query, and
here's the output:
******* BEGIN TRACE OUTPUT *****************
SQL:BatchCompleted SET ROWCOUNT 10000 Microsoft Office 2003 DomusUser 0 0
0 0 5552 55 2008-07-02 5:50:16.867

SQL:BatchCompleted SELECT * FROM "dbo"."I - Appartamenti" Microsoft Office
2003 DomusUser 0 120 0 16 5552 55 2008-07-02 15:50:16.867

SQL:BatchCompleted SET ROWCOUNT 0 Microsoft Office 2003 DomusUser 0 0 0 0
5552 55 2008-07-02 15:50:16.883

SQL:BatchCompleted SET FMTONLY ON select "ID Appartamenti" from (SELECT
dbo.[I - Appartamenti].[ID Appartamenti], dbo.[I - Appartamenti].Interno,
dbo.[I - Stabili].Sigla, dbo.[I - Pagamenti].Importo, dbo.[I - Stabili].[Id
Stabile], YEAR(dbo.[I - Pagamenti].Data) AS Anno, MONTH(dbo.[I -
Pagamenti].Data) AS Mese, dbo.[I - Periodi].Canone FROM dbo.[I -
Appartamenti] INNER JOIN dbo.[I - Stabili] ON dbo.[I - Appartamenti].Stabile
= dbo.[I - Stabili].[Id Stabile] INNER JOIN dbo.[I - Contratti] ON dbo.[I -
Appartamenti].[ID Appartamenti] = dbo.[I - Contratti].Appartamento INNER
JOIN dbo.[I - Periodi] ON dbo.[I - Contratti].[ID Contratto] = dbo.[I -
Periodi].[ID Contratto] INNER JOIN dbo.[I - Pagamenti] ON dbo.[I -
Periodi].[ID Periodo] = dbo.[I - Pagamenti].[ID periodo] ) AS DRVD_TBL WHERE
1=2 SET FMTONLY OFF Microsoft Office 2003 DomusUser 0 215 0 0 5552 55
2008-07-02 15:50:16.900

RPC:Completed declare @P1 int

set @P1=NULL

exec sp_prepare @P1 output, N'@P1 int', N'SELECT * FROM (SELECT dbo.[I -
Appartamenti].[ID Appartamenti], dbo.[I - Appartamenti].Interno, dbo.[I -
Stabili].Sigla, dbo.[I - Pagamenti].Importo, dbo.[I - Stabili].[Id Stabile],
YEAR(dbo.[I - Pagamenti].Data) AS Anno, MONTH(dbo.[I - Pagamenti].Data) AS
Mese, dbo.[I - Periodi].Canone FROM dbo.[I - Appartamenti] INNER JOIN
dbo.[I - Stabili] ON dbo.[I - Appartamenti].Stabile = dbo.[I - Stabili].[Id
Stabile] INNER JOIN dbo.[I - Contratti] ON dbo.[I - Appartamenti].[ID
Appartamenti] = dbo.[I - Contratti].Appartamento INNER JOIN dbo.[I -
Periodi] ON dbo.[I - Contratti].[ID Contratto] = dbo.[I - Periodi].[ID
Contratto] INNER JOIN dbo.[I - Pagamenti] ON dbo.[I - Periodi].[ID Periodo]
= dbo.[I - Pagamenti].[ID periodo] ) AS DRVD_TBL WHERE ((@P1 = "ID
Appartamenti")) ORDER BY dbo.[I - Stabili].[Id Stabile], dbo.[I -
Appartamenti].[ID Appartamenti]', 1

select @P1 Microsoft Office 2003 DomusUser 0 57 0 0 5552 55 2008-07-02
15:50:16.913

SQL:BatchCompleted SELECT N'Testing Connection...' SQLAgent - Alert Engine
Administrator STUDIONOLI\Administrator 16 0 0 16 2908 52 2008-07-02
15:50:16.913

SQL:BatchCompleted EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters SQLAgent -
Alert Engine Administrator STUDIONOLI\Administrator 15 99 0 16 2908 52
2008-07-02 15:50:16.930

TraceStop
******* END TRACE OUTPUT *****************

What's wrong?
Click to expand...
 
G

Graham R Seach

Hi Marcus,

Sorry, my bad. Try creating a view in SQL Server, which aliases the
fieldnames so they have no spaces. Then base the pivot on that.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Marcus said:
Graham R Seach said:
Marcus,

Do you want to be right in assuming its a bug, or do you want to resolve
the problem and move on?

Have you actually tried putting the object names in square brackets? If
it doesn't work, then you've lost 5 minutes of your life. If it does,
then you can keep working while you wait for Microsoft to get back to
you.
Click to expand...

Sorry but I don't understand... if it is Acces that builds the underlying
query to the SQl server, where and what should I enclose in brackets?

The only place I could do that is in the Link Child/Master properties in
the subform.

If I do so I get an error "Invalid column name [xxxxxxxx]"

Could you clarify?
Click to expand...
 
M

Marcus

Marcus said:
Hope someone can help.

Access 2003 + latest patches, .adp, SQL server 2000.

When trying to use a sample published by microsoft

http://support.microsoft.com/kb/281811/en-us/

to build a PivotChart in a subform, whose contents change when the current
record changes in the main form, I get an error:

"The column prefix 'dbo.I Stabili' does not match with a table name or
alias name used in the query"

So I have started the profiler on the SQL Server and traced the query, and
here's the output:
******* BEGIN TRACE OUTPUT *****************
SQL:BatchCompleted SET ROWCOUNT 10000 Microsoft Office 2003 DomusUser 0
0 0 0 5552 55 2008-07-02 5:50:16.867

SQL:BatchCompleted SELECT * FROM "dbo"."I - Appartamenti" Microsoft
Office 2003 DomusUser 0 120 0 16 5552 55 2008-07-02 15:50:16.867

SQL:BatchCompleted SET ROWCOUNT 0 Microsoft Office 2003 DomusUser 0 0 0
0 5552 55 2008-07-02 15:50:16.883

SQL:BatchCompleted SET FMTONLY ON select "ID Appartamenti" from (SELECT
dbo.[I - Appartamenti].[ID Appartamenti], dbo.[I - Appartamenti].Interno,
dbo.[I - Stabili].Sigla, dbo.[I - Pagamenti].Importo, dbo.[I -
Stabili].[Id Stabile], YEAR(dbo.[I - Pagamenti].Data) AS Anno,
MONTH(dbo.[I - Pagamenti].Data) AS Mese, dbo.[I - Periodi].Canone FROM
dbo.[I - Appartamenti] INNER JOIN dbo.[I - Stabili] ON dbo.[I -
Appartamenti].Stabile = dbo.[I - Stabili].[Id Stabile] INNER JOIN dbo.[I -
Contratti] ON dbo.[I - Appartamenti].[ID Appartamenti] = dbo.[I -
Contratti].Appartamento INNER JOIN dbo.[I - Periodi] ON dbo.[I -
Contratti].[ID Contratto] = dbo.[I - Periodi].[ID Contratto] INNER JOIN
dbo.[I - Pagamenti] ON dbo.[I - Periodi].[ID Periodo] = dbo.[I -
Pagamenti].[ID periodo] ) AS DRVD_TBL WHERE 1=2 SET FMTONLY OFF Microsoft
Office 2003 DomusUser 0 215 0 0 5552 55 2008-07-02 15:50:16.900

RPC:Completed declare @P1 int

set @P1=NULL

exec sp_prepare @P1 output, N'@P1 int', N'SELECT * FROM (SELECT dbo.[I -
Appartamenti].[ID Appartamenti], dbo.[I - Appartamenti].Interno, dbo.[I -
Stabili].Sigla, dbo.[I - Pagamenti].Importo, dbo.[I - Stabili].[Id
Stabile], YEAR(dbo.[I - Pagamenti].Data) AS Anno, MONTH(dbo.[I -
Pagamenti].Data) AS Mese, dbo.[I - Periodi].Canone FROM dbo.[I -
Appartamenti] INNER JOIN dbo.[I - Stabili] ON dbo.[I -
Appartamenti].Stabile = dbo.[I - Stabili].[Id Stabile] INNER JOIN dbo.[I -
Contratti] ON dbo.[I - Appartamenti].[ID Appartamenti] = dbo.[I -
Contratti].Appartamento INNER JOIN dbo.[I - Periodi] ON dbo.[I -
Contratti].[ID Contratto] = dbo.[I - Periodi].[ID Contratto] INNER JOIN
dbo.[I - Pagamenti] ON dbo.[I - Periodi].[ID Periodo] = dbo.[I -
Pagamenti].[ID periodo] ) AS DRVD_TBL WHERE ((@P1 = "ID Appartamenti"))
ORDER BY dbo.[I - Stabili].[Id Stabile], dbo.[I - Appartamenti].[ID
Appartamenti]', 1

select @P1 Microsoft Office 2003 DomusUser 0 57 0 0 5552 55 2008-07-02
15:50:16.913

SQL:BatchCompleted SELECT N'Testing Connection...' SQLAgent - Alert Engine
Administrator STUDIONOLI\Administrator 16 0 0 16 2908 52 2008-07-02
15:50:16.913

SQL:BatchCompleted EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
SQLAgent - Alert Engine Administrator STUDIONOLI\Administrator 15 99 0 16
2908 52 2008-07-02 15:50:16.930

TraceStop
******* END TRACE OUTPUT *****************

What's wrong?
Click to expand...

Ok, it looks like there's a bug in the underlying query. Microsoft support
is working on it. It looks like the problem is in the ORDER BY statement
that issues a second query that shouldn't be issued at all. I've been told
to place current SELECT statment in a named query (view) and use the view as
the recordsource, and it works!

Thanks everyone! I'll post updates about the fix, if any.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query not Updateable 1
SQL query to select rows with highest vales 2
ACCESS 2003 CRASH EXAMPLES 9
query to return counts of all categories per day 3
QUERY TO SORT BY MORE THAN ONE COLUMN 1
Can't add drawing toolbar to Word 2008 in Mac 0
Crash on startup of Excel from Office 2008 - Clean install 5
Outlook connector crashes outlook 0

Top