|
|
|
date: Wed, 2 Jul 2008 16:05:14 +0200,
group: microsoft.public.access.adp.sqlserver
back
ADP/2003 sends wrong query to SQL server
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?
date: Wed, 2 Jul 2008 16:05:14 +0200
author: Marcus
Re: ADP/2003 sends wrong query to SQL server
uh, do you have a space in your table name?
I'd get rid of that for starters.
-Aaron
On Jul 2, 7:05 am, "Marcus" wrote:
> 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?
date: Sat, 5 Jul 2008 21:18:02 -0700 (PDT)
author: a a r o n . k e m p f @ g m a i l . c o m
Re: ADP/2003 sends wrong query to SQL server
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" wrote in message
news:ONSdnTU3qccQPu7VnZ2dnUVZ_sWdnZ2d@supernews.com...
>
> "a a r o n . k e m p f @ g m a i l . c o m" wrote
> in message
> news:ca9bf279-52f5-437b-b45e-2ec8cfacfd0e@m45g2000hsb.googlegroups.com...
> 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.
>
>
>
>
>
> On Jul 7, 10:23 am, "Marcus" wrote:
>> >"a a r o n . k e m p f @ g m a i l . c o m"
>> >wrote
>> >in message
>> > >news:e912a8e9-91bd-44e0-9d6e-5f115abf6a82@d1g2000hsg.googlegroups.com...
>> >uh, do you have a space in your table name?
>>
>> >I'd get rid of that for starters.
>>
>> All the tables in the DB have spaces in theyr name, like
>>
>> [C - Contabilità]
>>
>> ........................
>
>
>
> 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
date: Wed, 9 Jul 2008 08:26:25 +1000
author: Graham R Seach
Re: ADP/2003 sends wrong query to SQL server
> "The column prefix 'dbo.I Stabili' does not match with a table name or
alias
> name used in the query"
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" wrote in message
news:Vfadnf72A8yBFvbVnZ2dnUVZ_v_inZ2d@supernews.com...
> 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?
>
date: Thu, 10 Jul 2008 08:20:50 +1000
author: david@epsomdotcomdotau
Re: ADP/2003 sends wrong query to SQL server
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" wrote in message
news:pvWdnbAnI40cAenVnZ2dnUVZ_tDinZ2d@supernews.com...
>
> "Graham R Seach" wrote in message
> news:eDg$pmU4IHA.1952@TK2MSFTNGP03.phx.gbl...
>> 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.
>>
>
> 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?
date: Thu, 10 Jul 2008 09:24:32 +1000
author: Graham R Seach
Re: ADP/2003 sends wrong query to SQL server
"Marcus" wrote in message
news:Vfadnf72A8yBFvbVnZ2dnUVZ_v_inZ2d@supernews.com...
> 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?
>
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.
date: Thu, 10 Jul 2008 22:47:10 +0200
author: Marcus
|
|
