Outer Join problem

I know this is not an Access group, but there doesn't seem to be one,
and I'm guessing MS SQLs are all quite similar - at least in relatively
simple cases like this one.

I'm using this SQL statement,

"SELECT [Cape Town 25/04].IDNUMBER, [Cape Town 25/04].SURNAME,
[Cape Town 25/04].NAMES, Misc.PHONE_NUMBER,
Misc.VOTE                           FROM [Cape Town 25/04] LEFT JOIN Mis
c
ON [Cape Town 25/04].IDNUMBER = Misc.IDNUMBER
WHERE STREETNAME='FIRTH ROAD' AND STREETNO='3FI' AND BLDNGNAME IS NULL
AND BLDNGNO IS NULL"

and getting this error message: "[Microsoft][ODBC Microsoft Access
Driver] Too few parameters. Expected 2. in EXEC"

Where is the missing parameter? I think it's something to do with the
LEFT JOIN, but I'm not sure - I'm a SQL newbie.

I'm accessing an MS Access database from Python via the PythonWin obdc
interface, if it makes a difference.

--Max

On Tue, 24 Jan 2006 20:13:49 +0200, Max <rabkin@mweb[DOT]co[DOT]za>
wrote:

>I know this is not an Access group, but there doesn't seem to be one,

Hi Max,

There's comp.databases.ms-access. And over 20 groups in the
microsoft.public.access hierarchy. There are also access groups in many
international hierarchies, or in international sub-hierarchies of the
microsoft.public hierarchy.

>and I'm guessing MS SQLs are all quite similar - at least in relatively
>simple cases like this one.

Don't count on it - there are many major differences between Jet SQL
(used in Access) and Trasact SQL (used in SQL Server). T-SQL tends to be
a lot closer to the ANSI-defined SQL standards.

>
>I'm using this SQL statement,
>
>"SELECT [Cape Town 25/04].IDNUMBER, [Cape Town 25/04].SURNAME,
>                     [Cape Town 25/04].NAMES, Misc.PHONE_NUMBER,
>Misc.VOTE                           FROM [Cape Town 25/04] LEFT JOIN Mi
sc
>ON [Cape Town 25/04].IDNUMBER = Misc.IDNUMBER
>WHERE STREETNAME='FIRTH ROAD' AND STREETNO='3FI' AND BLDNGNAME IS NULL
>AND BLDNGNO IS NULL"
>
>and getting this error message: "[Microsoft][ODBC Microsoft Access
>Driver] Too few parameters. Expected 2. in EXEC"
>
>Where is the missing parameter? I think it's something to do with the
>LEFT JOIN, but I'm not sure - I'm a SQL newbie.

According to the error message, two parameters where expected in EXEC.
The code you posted does not contain the word "EXEC". Maybe you should
double-check if the error is not produced by another part of your code?

Anyway, the query you posted passes the syntax check of SQL Server 2000
without problems.

>
>I'm accessing an MS Access database from Python via the PythonWin obdc
>interface, if it makes a difference.
>
>--Max


--
Hugo Kornelis, SQL Server MVP

Hugo Kornelis  wrote:
> There's comp.databases.ms-access. And over 20 groups in the
> microsoft.public.access hierarchy. There are also access groups in many
> international hierarchies, or in international sub-hierarchies of the
> microsoft.public hierarchy.

Thank you. Sorry my ISP seems not to offer them.

> Don't count on it - there are many major differences between Jet SQL
> (used in Access) and Trasact SQL (used in SQL Server). T-SQL tends to be
> a lot closer to the ANSI-defined SQL standards.
>

Good to know.
 
>
> According to the error message, two parameters where expected in EXEC.
> The code you posted does not contain the word "EXEC". Maybe you should
> double-check if the error is not produced by another part of your code?

Nope, all my error messages end with a period and "in EXEC". I'm pretty
sure it's that line - although I'm getting the same problem elsewhere

> Anyway, the query you posted passes the syntax check of SQL Server 2000
> without problems.
>

Thanks for checking.

> --
> Hugo Kornelis, SQL Server MVP

--Max

I'd check the spelling of the column names as this is often where
Access throws this sort of error. If you spelt something wrong, it may
give this error thinking that you are going to pass a parameter into
the query. SQL would show something different (and probably more close
to the actual error).

BTW, have a look at the table naming you use as it's terrible.

Ryan

The slashes in the table name might be causing you grief.


Max  wrote:
>"SELECT [Cape Town 25/04].IDNUMBER, [Cape Town 25/04].SURNAME,
>                      [Cape Town 25/04].NAMES, Misc.PHONE_NUMBER,

On 24 Jan 2006 23:32:21 -0800, rabkin@mweb.co.za wrote:

>Hugo Kornelis wrote: 
>
>Thank you. Sorry my ISP seems not to offer them.

Hi Max,

If you need help from an Access group often, consider switching ISP or
buying a pay server subscription. For a one-time issue, you could use
Google groups.

http://groups.google.com/group/comp...ms-access/about

--
Hugo Kornelis, SQL Server MVP

(rabkin@mweb.co.za)  writes:
> Hugo Kornelis wrote: 
>
> Thank you. Sorry my ISP seems not to offer them.

You can access Microsoft's newsgroups at msnews.microsoft.com.

> Nope, all my error messages end with a period and "in EXEC". I'm pretty
> sure it's that line - although I'm getting the same problem elsewhere

My news provider has had a hiatus, so I have seen all of the thread, but
did you ever post the full error message. Is it possible that the message
comes from Python? (I know neither Python nor Access.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

On Wed, 25 Jan 2006 22:51:23 +0000 (UTC), Erland Sommarskog wrote:

 (snip) 
>
>My news provider has had a hiatus, so I have seen all of the thread, but
>did you ever post the full error message. Is it possible that the message
>comes from Python? (I know neither Python nor Access.)

Hi Erland,

Max did, in his first post. Here's what he posted:

>I know this is not an Access group, but there doesn't seem to be one,
>and I'm guessing MS SQLs are all quite similar - at least in relatively
>simple cases like this one.
>
>I'm using this SQL statement,
>
>"SELECT [Cape Town 25/04].IDNUMBER, [Cape Town 25/04].SURNAME,
>                     [Cape Town 25/04].NAMES, Misc.PHONE_NUMBER,
>Misc.VOTE                           FROM [Cape Town 25/04] LEFT JOIN Mi
sc
>ON [Cape Town 25/04].IDNUMBER = Misc.IDNUMBER
>WHERE STREETNAME='FIRTH ROAD' AND STREETNO='3FI' AND BLDNGNAME IS NULL
>AND BLDNGNO IS NULL"
>
>and getting this error message: "[Microsoft][ODBC Microsoft Access
>Driver] Too few parameters. Expected 2. in EXEC"
>
>Where is the missing parameter? I think it's something to do with the
>LEFT JOIN, but I'm not sure - I'm a SQL newbie.
>
>I'm accessing an MS Access database from Python via the PythonWin obdc
>interface, if it makes a difference.
>
>--Max


--
Hugo Kornelis, SQL Server MVP