outer join similar to Access

Ok fellas, I need your help. I have a crosstab query that I wrote in Access
that lists all my marketers regardless of their referrals. The outer joins
selects all rows from marketer table. Now in SSRS I recreated the same query,
setup the matrix and populated it correctly. Problem is, that if the marketer
had no referrals, their name does not show up. I assumed that the outer join
would take care of that. Do I need to alter the query in some way to treat
nulls as zeros? Its a very simple query but I cannot determine where to put
the conditioning. Many thanks.isnull(numofreferralscolumn, 0) as numofreferralscolumn
in the select would be the easiest way.
--
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:D5DBC2BE-55FA-413E-97A9-99A262C48EAC@.microsoft.com...
> Ok fellas, I need your help. I have a crosstab query that I wrote in
> Access
> that lists all my marketers regardless of their referrals. The outer joins
> selects all rows from marketer table. Now in SSRS I recreated the same
> query,
> setup the matrix and populated it correctly. Problem is, that if the
> marketer
> had no referrals, their name does not show up. I assumed that the outer
> join
> would take care of that. Do I need to alter the query in some way to treat
> nulls as zeros? Its a very simple query but I cannot determine where to
> put
> the conditioning. Many thanks.|||or you might need to do some more hocus-pocus.. i have to do things
like this to get 0 members showing up in some cubes for example
say you got a table that lists OrderTotals.. right?
Select CustomerID, OrderID, OrderTotalA, OrderTotalB From OrderTotals
UNION ALL
Select CustomerID, 0,0,0 From Customers
Michael Abair wrote:
> isnull(numofreferralscolumn, 0) as numofreferralscolumn
> in the select would be the easiest way.
> --
> Michael Abair
> Programmer / Analyst
> Chicos FAS Inc.
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:D5DBC2BE-55FA-413E-97A9-99A262C48EAC@.microsoft.com...
> > Ok fellas, I need your help. I have a crosstab query that I wrote in
> > Access
> > that lists all my marketers regardless of their referrals. The outer joins
> > selects all rows from marketer table. Now in SSRS I recreated the same
> > query,
> > setup the matrix and populated it correctly. Problem is, that if the
> > marketer
> > had no referrals, their name does not show up. I assumed that the outer
> > join
> > would take care of that. Do I need to alter the query in some way to treat
> > nulls as zeros? Its a very simple query but I cannot determine where to
> > put
> > the conditioning. Many thanks.

Outer Join qry help!

The first query returns 46 rows (correct result set) while the second one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullArul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
http://groups.google.nl/groups?selm=eAtctS7DBHA.968%40tkmsftngp07&output=gplain
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is nullsql

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 Misc
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.

--MaxOn 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 Misc
>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.

> >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?

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:
>> 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.

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:
>> 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.

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)
>> 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.)

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 Misc
>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 MVPsql

Other databases stopped working

I have a similar problem but not with the sample databases. All the sample databases work perfectly fine but all my other databases that were hosted appear to have similar error
"The database cannot be opened because it is version 607. This server supports version 603 and earlier. A downgrade is not supported"
I'm using the SQL Server that came with Visual Studio 2005 Beta 2. Version 9.01116
Any ideas?

The error message indicates that your other databases were created in a version of SQL Server 2005 newer than the version of SQL Server included in the VS Beta2. Databases are not backward compatible, so that's why you're getting the error. Did you have another version of SQL Server installed before you installed the VS Beta (perhaps the June CTP or the September CTP) ?

Assuming that's what happened, here are a couple of possible solutions.

1. Install the September CTP version of SQL Server 2005 from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/ctp.mspx and try to attach these databases there. (This leaves the older version of SQL Server in place).
or
2. Uninstall the current version and install the September CTP. The September CTP bits are compatible with VS Beta 2, so this shouldn't create any problems.

These solutions might or might not work depending on which versions the September CTP supports. If this doesn't work, you'll need to install an older version like the June CTP and try to attach the databases there. For the June CTP Express Edition, see http://www.microsoft.com/downloads/details.aspx?FamilyId=1A722B0F-6CCA-4E8B-B6EA-12D9C450ED92&displaylang=en . For the June CTP Developer Edition, see http://www.microsoft.com/downloads/details.aspx?FamilyId=B414B00F-E2CC-4CAB-A147-EACA26740F19&displaylang=en.

Hope that helps.
Regards,

OT: "Profiler" for Access?

Guys,
Is there a tool that I can see what queries are running against an Access
database? The one is similar to Profiler for SQL.
Thanks.Hi
AFAIK three is no such tool, if you are using ODBC then you may want to try
using ODBC tracing, but this would need to be enabled on each client and
would severely impact performance. Another alternative would be to make the
m
linked tables to a SQL Server database and to actually use profiler.
You may want to ask this in an Access News Group.
John
"ME" wrote:

> Guys,
> Is there a tool that I can see what queries are running against an Access
> database? The one is similar to Profiler for SQL.
> Thanks.
>
>|||thanks anyway.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0CC54221-14C3-42D9-A296-46C75C5B1C29@.microsoft.com...[vbcol=seagreen]
> Hi
> AFAIK three is no such tool, if you are using ODBC then you may want to
> try
> using ODBC tracing, but this would need to be enabled on each client and
> would severely impact performance. Another alternative would be to make
> them
> linked tables to a SQL Server database and to actually use profiler.
> You may want to ask this in an Access News Group.
> John
> "ME" wrote:
>

OT: "Profiler" for Access?

Guys,
Is there a tool that I can see what queries are running against an Access
database? The one is similar to Profiler for SQL.
Thanks.Hi
AFAIK three is no such tool, if you are using ODBC then you may want to try
using ODBC tracing, but this would need to be enabled on each client and
would severely impact performance. Another alternative would be to make them
linked tables to a SQL Server database and to actually use profiler.
You may want to ask this in an Access News Group.
John
"ME" wrote:
> Guys,
> Is there a tool that I can see what queries are running against an Access
> database? The one is similar to Profiler for SQL.
> Thanks.
>
>|||thanks anyway.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0CC54221-14C3-42D9-A296-46C75C5B1C29@.microsoft.com...
> Hi
> AFAIK three is no such tool, if you are using ODBC then you may want to
> try
> using ODBC tracing, but this would need to be enabled on each client and
> would severely impact performance. Another alternative would be to make
> them
> linked tables to a SQL Server database and to actually use profiler.
> You may want to ask this in an Access News Group.
> John
> "ME" wrote:
>> Guys,
>> Is there a tool that I can see what queries are running against an Access
>> database? The one is similar to Profiler for SQL.
>> Thanks.
>>

OSQL restore Database

Hi

I need to do a restore similar to the Restore Database in SQL Enterprise Manager using OSQL. I created an app to do the OSQL Run script command and it works fine. Is this the best way to create a setup to restore the database? Any ideas please!!

Best Regards
PhilipI'd use a VBS script. Easier then a full application...|||Thanks