Newbie help with joins

I am learning vb.net and ms sql (2000 evaluation version); I can't get my joins to work in Tsql:

select m.lname,m.fname,m.idno,m.payorno,p.payorno.p.payor name
from dbtemp..d_mbrdata m join dbtemp..d_payors p
on (m.payorno = p.payorno)

inner join query returns no records at all. left outerjoin returns the mbrdata fields, but the payors fields show as null. payorno in both tables is type integer and both tables definitely have matching records. I created index for both tables on payorno.

Does anyone have an idea what i am doing wrong here? Thanks.There is nothing wrong with query. If left join returns some data - problem is in second table (matching records).

Just run this query (without join):

select *
from dbtemp..d_mbrdata
where payorno in (select distinct payorno from dbtemp..d_payors)|||Yeah, from the way u'r describing u'r results, it seems there is no m.payorno that is equal to p.payorno.

Maybe u can try debuggin further by inserting a new record into both this tables with a "confirm" identical payorno and do your select join statement again.
It should return 1 joined record.|||Try running this and see what key values are returned:
select m.payorno, p.payorno
from dbtemp..d_mbrdata m
full outer join dbtemp..d_payors p on (m.payorno = p.payorno)
where mpayrono is null or p.payorno is null

blindman

Newbie Alert !

Dear Folks,

First time here.

Have just installed a 120-day evaluation copy of MSSQL 2000.

In the 'blur' and confusion of installing this on my WinXP Laptop, I
forgot to record the password.

Finally, I got into the 'logins' tab on the Enterprise Manager, and
modified the 'sa' password to 'ivlem1', and I think it was recorded
properly.

I tried to test my installation with the following at the Command
Prompt:

osql /Usa /P ivlem1

It failed saying I wasn't 'Trusted'

I would appreciate any help offered !

TIA,

-Mel SmithDid you select "mixed" mode for authentication. I would test this
differently...fire up QA and use your SA account/passwd to account.
There are too many things that could go wrong with establishing a
connection using osql.

HTH

MJKulangara
http://sqladventures.blogspot.com|||Mel Smith (medsyntel@.aol.com) writes:
> Have just installed a 120-day evaluation copy of MSSQL 2000.
> In the 'blur' and confusion of installing this on my WinXP Laptop, I
> forgot to record the password.
> Finally, I got into the 'logins' tab on the Enterprise Manager, and
> modified the 'sa' password to 'ivlem1', and I think it was recorded
> properly.
> I tried to test my installation with the following at the Command
> Prompt:
> osql /Usa /P ivlem1
> It failed saying I wasn't 'Trusted'

SQL Server have to means of authentication: Windows authentication (a.k.a
Integrated Security or Trusted Connection) and SQL Server authentication.
Windows authentication is always there, SQL authentication is optional,
and usually referred to as "mixed mode".

Windows authentcation means that you log into SQL Server with your
Windows credentials. This is very simple, you just say "osql /E" and
you are on the air. No need for password, as you are already logged
into Windows.

By default, only Windows authentication is enabled. This is because
SQL Server authentication is less secure. However, many enable
SQL authentiation for various reasons. For instance, if you are a
developer you may work with Windows authentication as long as you
develop and have sysadmin rights. But when you need to test, you
want a low-priv user, and testing different users is much easier
with SQL authentication.

You can change authentication mode from Enterprise Manager. Right-
click the server, select Properties, and then use the Security tab.

--
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|||Erland and MJK,

Thank you both for your advice !

Erland:

I tried "osql /E" and it worked ! (i.e., I got correct correct results
for the two queries: select @.@.servername and select @.@.version)

(now I'm going to go blind reading all there is on Books Online to get
myself up to speed )

btw, I'm fairly expert in xBase databases (my job is tech support for a
structural steel company that uses my software, but is now moving to MSSQL)
and I have to show that I'm up to the task to help them make that move. In
fact, they have purchased AccPac for Windows, and have installed MSSQL 2000.
But I'm at a remote location from the headquarters of this company and must
get used to MSSQL all by myself in the 'sunny south'.

Thanks again.

-Mel Smith
Mesa, Arizona, USA

Newbie - Subscription problem

I know this has probably been asked many many times, but
can someone please help me.
Recently, I installed the evaluation version of RS (and
SP1). I have been trying to test out the subscription
feature, but can't seem to get it working. When I store
the credentials as a Windows login, I get an error
stating that the login failed. If I store the sa login
under the credentials I do not get a login failed error,
but nothing happens. The report is never delivered. Can
anyone tell me how to make subscriptions work?
I am running RS on a Window 2000 server and am trying to
send email through and Exchange 2000 server.
Thanks in advance for your help.When using sa after the subscription fires, what is in the log files? Does
the status of the subscription ever change?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim" <tim@.discussions.microsoft.com> wrote in message
news:2d93d01c46b4b$965ad170$a601280a@.phx.gbl...
> I know this has probably been asked many many times, but
> can someone please help me.
> Recently, I installed the evaluation version of RS (and
> SP1). I have been trying to test out the subscription
> feature, but can't seem to get it working. When I store
> the credentials as a Windows login, I get an error
> stating that the login failed. If I store the sa login
> under the credentials I do not get a login failed error,
> but nothing happens. The report is never delivered. Can
> anyone tell me how to make subscriptions work?
> I am running RS on a Window 2000 server and am trying to
> send email through and Exchange 2000 server.
> Thanks in advance for your help.
>