non-matching recrods 2 tables (Newbie)

Hi,

I am new to SQL and these forums, and have a quick simple question.

I am trying to view the records that do not match in 2 tables. I have tried a few different ways but keep getting results in the hundreds of thousands and the table only has 36 thousand reocrds.

Here is an example of what the two tables contain.

Table 1
customer_no eaddress_no name address (36000 records)

Table2
customer_no eaddress_no email (17000 records)

I need to find out which customers are not in table 2 by linking the eaddress_no numbers.

This is one of the scripts i ran that gives me the large results with a ton of dups:

SELECT *
FROM T_EADDRESS inner JOIN
T_CUST_LOGIN ON T_EADDRESS.eaddress_no
!= T_CUST_LOGIN.eaddress_no

Thanks in advance for any help with this.Try this:

SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.eaddress_no <> Table2.eaddress_no|||I got the same results:

Alot of dups.

I did just find this at SQLTeam after i posted this message. I am just getting ready to try it now.

SELECT A.No,A.Date
FROM A LEFT OUTER JOIN B ON A.No = B.No AND A.Date = B.Date WHERE B.No IS NOT NULL|||If your query does not work, try this:

SELECT *
FROM Table1
WHERE eaddress_no NOT IN
(SELECT eaddress_no FROM Table2)|||gyuan that worked.
yours looked a little easier for me to follow so i just used that one.
Thanks a million.|||Using a left join is generally preferable to NOT EXISTS.

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null

This can also be easily be modified to show records that are missing from either table:

SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||Thanks Blind Man,

Those make sense to me now . I helps to see the table names in the query.|||Blindman,

you have an extra ) where would the opening one go?|||Just remove it...you don't need it...|||Sorry. No Parenthesis is necessary. It was a copy/paste error.|||I tried that and it returns no records.|||If you run this

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null

and no records are returned, then there are no eaddress_no values in Table1 that are not also in Table2.

Run the second one with the FULL OUTER JOIN clause and see what you get:

SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||OOPS!
WHERE clause was looking at wrong table! We want to see if Table2 is null...

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table2.eaddress_no is null|||Yep that did it. And it took about 2 seconds few to run.

Thanks blindman, now i see.

No XML export

...Newbie alert...
I am picking up a project that makes significant use of SQL2005 reporting
services. The app, as it is currently, triggers the report by launching a new
window with a URL pointing at the report server and including the parameters
rs:Command=Render&rs:clearsession=true&rs:format=HTML4.0
The new window shows the "Report is being generated" message and eventually
it is displayed. I want to get the XML version of the report, but the EXPORT
drop down only offers Excel and PDF.
This seems to be consistent with a .net report viewer control in "local
mode" (whatever that is), but I don't think that this is what's going on.
If I go to the reportserver virtual directory, there is a
ReportServer.config file. It contains, in part the following:
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
<RenderingExtension>RGDI</RenderingExtension>
</ExcludedRenderFormats>
under <Delivery> for various <Extension Name="..."> tags
It also shows
<Render>
<Extension Name="XML"
Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering"/>
...
So it doesn't seem to actively exclude xml rendering and it does seem to
provide a renderer for the file type.
I've done this using the client's service provider and also on my own
out-of-the-box SQL2005 installation and have the same result on each.
I'm obviously missing something silly, but I can't figure out what it is.
Thx
MarcOn Sep 17, 3:08 pm, MarcG <JoePt...@.newsgroup.nospam> wrote:
> ...Newbie alert...
> I am picking up a project that makes significant use of SQL2005 reporting
> services. The app, as it is currently, triggers the report by launching a new
> window with a URL pointing at the report server and including the parameters
> rs:Command=Render&rs:clearsession=true&rs:format=HTML4.0
> The new window shows the "Report is being generated" message and eventually
> it is displayed. I want to get the XML version of the report, but the EXPORT
> drop down only offers Excel and PDF.
> This seems to be consistent with a .net report viewer control in "local
> mode" (whatever that is), but I don't think that this is what's going on.
> If I go to the reportserver virtual directory, there is a
> ReportServer.config file. It contains, in part the following:
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> <RenderingExtension>RGDI</RenderingExtension>
> </ExcludedRenderFormats>
> under <Delivery> for various <Extension Name="..."> tags
> It also shows
> <Render>
> <Extension Name="XML"
> Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering"/>
> ...
> So it doesn't seem to actively exclude xml rendering and it does seem to
> provide a renderer for the file type.
> I've done this using the client's service provider and also on my own
> out-of-the-box SQL2005 installation and have the same result on each.
> I'm obviously missing something silly, but I can't figure out what it is.
> Thx
> Marc
If you are using SQL Server 2005 Express Edition w/Advanced Services,
according to MS's documentation, you will not be able to export to XML
(refer to: http://technet.microsoft.com/en-us/library/ms157153.aspx );
however, I'm using that version w/SP2 and can export to Tiff and XML.
If you don't have SP2 installed, you may want to do that. Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
I'm not sure how to find the equivalent of "Help|About" to get the version
info, but the SQL Server Configuration Manager at the hosted site shows
Version-9.2.3042.00 and File Version 2005.090.3054.00. I can't find the
equivalent information for the Report Server.
Marc|||Actually, I didn't report this properly...
Both the hosting service and my test machine have near identical
reReportServer.config files.
They differ only in <dsn>, IInstallationID>,<UrlRoot>, and
WebServiceAccount> tags.
On the hosting service, Export offers only Excel and PDF. On my test host, I
get Excel, PDF, XML, csv, TIFF, and Web Archive, as expected.
So it appears that the answer is not in the config file. Is there a way that
I can confirm that the required renderers are installed on the hosting
service?
Thx|||Hello Joe,
I would like to know this issue more clearly.
Do you mean that you want to develop a windows form application to access
the Report via web access and you want to Export it via XML?
If so, please let me know whether you could Export the report to XML
directly in Report Server.
The Render Extension for the Reporting Services is specified in the
ReportServer.config file
Also, you could try to use the command parameter like this to export the
report to XML directly.
rs:Command=Render&rs:clearsession=true&rs:format=XML
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I want to have the report viewer that is used by default in IE. I DO NOT
want to use the .NET report viewer control. I want to leave the initial
display in HTML for viewing but allow for exporting and saving in XML. As I
said in the previous post, the rsreportserver.config files are effectively
identical on the two machines and I get the XML export optioin on my local XP
system but not on the hosted one.
I am a little confused about the installation that I am seeing on the
hosting service. Using the IIS Manager, I see the following...
Local Computer
Application Pools
AppPoolforRS
RManager (a "gear" icon, path is <ICCS>/RManager)
RServer (a "gear" icon, path is <ICCS>/RServer)
DefaultAppPool
Default Application (empty)
Default Application (empty)
Reports (empty)
ReportServer (empty)
MSSharePointAppPool
Root (empty)
Default Application (empty)
Web Sites
Default Web Site (stopped)
Microsoft SHarePoint Administration
ICCS
Reports ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportManager)
ReportServer ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportServer)
RManager ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportManager)
RServer ("gear" icon, path is C:\Program Files\Microsoft SQL
Server\MSSQL.2\Reporting Services\ReportServer)
In the ReportServer directory, I have (with other files),
rsreportserver.config
rsreportserversp2update.config
rssrvrpolicy.config
web.config
These files match (by name) the files I have in the same directory on my
local WinXP test machine. There is no file named "reportserver.config" on
either machine.
By way of an experiment, on the hosted system I made copies of
rsreportserver.config and rsreportserversp2update.config and renamed them
reportserver.config and reportserversp2update.config. I then stopped and
restarted the ICCS web site and stopped and restarted the AppPoolforRS. This
made no difference.|||Hell Joe,
Sorry for mistyping. It should be rsreportserver.config.
The <Render> part in my side is like this:
<Render>
<Extension Name="XML"
Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Mi
crosoft.ReportingServices.XmlRendering"/>
<Extension Name="NULL"
Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsof
t.ReportingServices.NullRendering" Visible="false"/>
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.
ReportingServices.CsvRendering"/>
<Extension Name="IMAGE"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport,Micros
oft.ReportingServices.ImageRendering"/>
<Extension Name="PDF"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsof
t.ReportingServices.ImageRendering"/>
<Extension Name="RGDI"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.RemoteGdiReport,Mi
crosoft.ReportingServices.ImageRendering" Visible="false"
LogAllExecutionRequests="false"/>
<Extension Name="HTML4.0"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExte
nsion,Microsoft.ReportingServices.HtmlRendering" Visible="false"
LogAllExecutionRequests="false"/>
<Extension Name="HTML3.2"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html32RenderingExte
nsion,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>
<Extension Name="MHTML"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExten
sion,Microsoft.ReportingServices.HtmlRendering"/>
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
osoft.ReportingServices.ExcelRendering"/>
</Render>
You need to check whether you have this
Microsoft.ReportingServices.XmlRendering.dll under the folder:
C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting
Services\ReportServer\bin
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
Unfortunately, my config matches what you sent me exactly, and
Microsoft.ReportingServices.XmlRendering.dll is in C:\Program Files\Microsoft
SQL Server\MSSQL.2\Reporting Services\ReportServer\bin
File version is 9.0.3042.0
'
Marc|||Hello Marc,
I would like to get the screenshot of your issue.
Also, have you applied the latest service pack of SQL 2005?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
I'm not sure what you want for a screen shot. I am looking at an IE5 browser
window with the HTML version of the report displayed. There is a drop down
box that says "Select a Format" and when you click it, it offers only
"Acrobat (PDF) File", and "Excel" If you send me an email address, I'll send
you the screen shot.
As noted earlier, I don't know how to ask SQL2005 what its SP level is. I
provided you with the file version numbers for the relevant DLL's
Marc|||Hello Marc,
You may send the email to me. Please remove the ONLINE in my display email
address. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.