Finding Missing Records

I have 18 tables that are all related by the primary key. When I join all
the fields together for reporting, it only shows the records that have all
the data filled in. How can I find which tables don't have a record?
Thanks,
DrewLEFT JOIN
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>I have 18 tables that are all related by the primary key. When I join all
>the fields together for reporting, it only shows the records that have all
>the data filled in. How can I find which tables don't have a record?
> Thanks,
> Drew
>|||Didn't think it would be that easy! Thought I would have to use an EXIST
query for this, and couldn't figure out how to incorporate all the tables
into 1 query...
Thanks,
Drew
"Michael C#" <howsa@.boutdat.com> wrote in message
news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
> LEFT JOIN
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>|||SELECT *
FROM Table1
LEFT JOIN Table2
WHERE Table2.Field1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||OOPS, left out the ON clause:
SELECT *
FROM Table1
LEFT JOIN Table2
ON Table1.Column1 = Table2.Column1
WHERE Table2.Column1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||>> When I join all the fields together for reporting, it only shows the
Depending on your requirements, you can use OUTER JOINs or correlated
subqueries to retrieve all rows in one table which does not have a matching
value for a joining column in another table.
Anith

Finding credientials to connect to RS database

First of all sorry that the message is so skatty. I have a lot on my plate
right now :)
When I installed our instance of Reporting Services on our report box I
cannot remember if I rsactivate-ed and rsconfig-ed the installation with a
windows user or a DB user. I need to know b/c we recently changed an
account that I believe the RS is running under (To impersonate) I think
it's happening because all my jobs have "Login failed" as the status.
Is there a was to decrypt the XML values in the config files using the keys
in the DB so I can tell what credentials everything is running with? Or is
there some way I can easily get the user that is being impersonated to
connect to the RS database?
Thanks
ScottIs this RS2000/2005?
Is this for a perticular report?
Just open up the Reporting Services Configuration and look at the DB
connection/server.
regards,
Stas K.|||For 2000
It's all encrypted
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1144694663.719163.201870@.e56g2000cwe.googlegroups.com...
> Is this RS2000/2005?
> Is this for a perticular report?
> Just open up the Reporting Services Configuration and look at the DB
> connection/server.
> regards,
> Stas K.
>|||Hi Scott,
Welcome to use MSDN managed newsgroup Support and thanks Stas's help.
In Reporting Services 2000, I don't think you can get the credential used
to connect to the RS database directly for the security issue.
I would like to know why you need to know this user. In the ReportServer
database, you may check what users are there in this database.
Also, you may post what the exactly error message you get when you run your
jobs.
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.|||We changed a password to a security account on our network and reports were
not being delivered through subscriptions. I started poking around and used
either rsactivate of rsconfiig to re-assign the user that unattended
operations ran under.
Thanks for everyone's help.
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:0do0ntSXGHA.5540@.TK2MSFTNGXA01.phx.gbl...
> Hi Scott,
> Welcome to use MSDN managed newsgroup Support and thanks Stas's help.
> In Reporting Services 2000, I don't think you can get the credential used
> to connect to the RS database directly for the security issue.
> I would like to know why you need to know this user. In the ReportServer
> database, you may check what users are there in this database.
> Also, you may post what the exactly error message you get when you run
> your
> jobs.
> 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 Scott,
Thanks for the update.
I think the root cause of the Login Fail is the credential you use to
connect to the datasource in the Report is not available. So you may try to
change this credential.
To change the credential stored in the data source, please do the following:
1. Find the datasource your report use in the Report Manager.
2. In the Properties Tab, General section, please change the Connect Using
Section.
Hope this will be helpful.
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.sql

Finding a query submitted by software

I have a retail software that has a reporting function for its sales. It has
a SQL Server 2000 database. The table links are extremely hard to define, and
I want to be able to write custom reports. If I can see the query that it
submits I can figure out how to make my own reports for it. Is there a way in
SQL Server to locate or capture a query that is submitted by a third party
software package?
Scott Ford
Information Services
Starlite Entertainment
Scott Ford wrote:
> I have a retail software that has a reporting function for its sales. It has
> a SQL Server 2000 database. The table links are extremely hard to define, and
> I want to be able to write custom reports. If I can see the query that it
> submits I can figure out how to make my own reports for it. Is there a way in
> SQL Server to locate or capture a query that is submitted by a third party
> software package?
I'd use SQL Profiler - it captures all queries.
Start profiler with recording the profiler data into a table. Use the
software then stop the profiler. Then analyze the profiler log.
|||Thanks Yuras .. I had just discovered the Profiler minutes before your post,
but it helps to have verification that this is the tool I need.
Scott Ford
Information Services
Starlite Entertainment
"Yuras" wrote:

> Scott Ford wrote:
> I'd use SQL Profiler - it captures all queries.
> Start profiler with recording the profiler data into a table. Use the
> software then stop the profiler. Then analyze the profiler log.
>

Finding a query submitted by software

I have a retail software that has a reporting function for its sales. It has
a SQL Server 2000 database. The table links are extremely hard to define, and
I want to be able to write custom reports. If I can see the query that it
submits I can figure out how to make my own reports for it. Is there a way in
SQL Server to locate or capture a query that is submitted by a third party
software package?
--
Scott Ford
Information Services
Starlite EntertainmentScott Ford wrote:
> I have a retail software that has a reporting function for its sales. It has
> a SQL Server 2000 database. The table links are extremely hard to define, and
> I want to be able to write custom reports. If I can see the query that it
> submits I can figure out how to make my own reports for it. Is there a way in
> SQL Server to locate or capture a query that is submitted by a third party
> software package?
I'd use SQL Profiler - it captures all queries.
Start profiler with recording the profiler data into a table. Use the
software then stop the profiler. Then analyze the profiler log.|||Thanks Yuras .. I had just discovered the Profiler minutes before your post,
but it helps to have verification that this is the tool I need.
--
Scott Ford
Information Services
Starlite Entertainment
"Yuras" wrote:
> Scott Ford wrote:
> > I have a retail software that has a reporting function for its sales. It has
> > a SQL Server 2000 database. The table links are extremely hard to define, and
> > I want to be able to write custom reports. If I can see the query that it
> > submits I can figure out how to make my own reports for it. Is there a way in
> > SQL Server to locate or capture a query that is submitted by a third party
> > software package?
> I'd use SQL Profiler - it captures all queries.
> Start profiler with recording the profiler data into a table. Use the
> software then stop the profiler. Then analyze the profiler log.
>

Finding a query submitted by software

I have a retail software that has a reporting function for its sales. It has
a SQL Server 2000 database. The table links are extremely hard to define, an
d
I want to be able to write custom reports. If I can see the query that it
submits I can figure out how to make my own reports for it. Is there a way i
n
SQL Server to locate or capture a query that is submitted by a third party
software package?
--
Scott Ford
Information Services
Starlite EntertainmentScott Ford wrote:
> I have a retail software that has a reporting function for its sales. It h
as
> a SQL Server 2000 database. The table links are extremely hard to define,
and
> I want to be able to write custom reports. If I can see the query that it
> submits I can figure out how to make my own reports for it. Is there a way
in
> SQL Server to locate or capture a query that is submitted by a third party
> software package?
I'd use SQL Profiler - it captures all queries.
Start profiler with recording the profiler data into a table. Use the
software then stop the profiler. Then analyze the profiler log.|||Thanks Yuras .. I had just discovered the Profiler minutes before your post,
but it helps to have verification that this is the tool I need.
--
Scott Ford
Information Services
Starlite Entertainment
"Yuras" wrote:

> Scott Ford wrote:
> I'd use SQL Profiler - it captures all queries.
> Start profiler with recording the profiler data into a table. Use the
> software then stop the profiler. Then analyze the profiler log.
>

Find servers runnign reporting services

How can I enumerate only those servers running reporting services in
c#.
I can get a list of all SQL Server instances on the local network
using:
SmoApplication.EnumAvailableSqlServers(false)
How can i filter this list to include only those instances where
Reporting Services is running?
Thanks in advance
Dave ArkleyOn Sep 14, 7:06 am, daveark...@.wildair.net wrote:
> How can I enumerate only those servers running reporting services in
> c#.
> I can get a list of all SQL Server instances on the local network
> using:
> SmoApplication.EnumAvailableSqlServers(false)
> How can i filter this list to include only those instances where
> Reporting Services is running?
> Thanks in advance
> Dave Arkley
You can loop through the list and run a query on each SQL Server
Instance similar to the following to determine if SSRS is installed
(at least for the most part):
SELECT COUNT(*) FROM SYS.DATABASES WHERE [NAME] IN
('REPORTSERVER','REPORTSERVERTEMPDB');
If the query comes back w/a result of 2, then Reporting Services is
installed. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Find first and last date of a month?

Afternoon all,

Just a quickie, is there an expression built into Reporting Services from which you can find the first date and last date of any given month? The first date isn't much of a problem as it isn't quite as dynamic as the last date.

I can do it in an IIf statement but wouldn't be able to make it recognise 29th February in a leap year very easily.

Thanks,

Paul

Hello Paul,

If you have the first day of the month, you can just add a month to it and subtract a day.

=DateAdd("d", -1, DateAdd("m", 1, FirstDayOfMonth))

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your reply, yes it helps a lot. Your solution is so simple it's brilliant.

Thanks,

Paul

|||

Phew,

Just thought I'd add this since I have proven that moving to SP2 9.0.3054 broke all my sub reports that passed a start and end date! (It blew up on the end date since the month and day were aways passed the wrong way round (i.e US when I'm en-GB)).

(No Language settings ANYWHERE fixed it, only code!)

Type in the "Code" section of report properties:

Public Function GetISODate(tmDate as DateTime)
return tmDate.Year & "-" & tmDate.Month & "-" & tmDate.Day
End Function

and use it on the navigation:
=Code.GetISODate(Parameters!<parameter>.Value)

It seems that the ISO formatted date (year-month-day) will be parsed the
same by every culture.

Credit goes to "Darren France", I just wished I'd assumed the DateTime object was in fact being passed as a date string!

final product

Hi all,

I have build a small asp.net websites where I want to generate some reports, but I have some questions?

Do you have to install the reporting services on the system where the final product will be hosted?

I have tried to use the report viewer to build a local delivery report, but I can not see the printer, any solution to that ?

Yes you have to install Microsoft Report Viewer.

But, if you create a kit for your web site=> properties on the kit =>Prerequisites=>chech ""Microsoft Reports for VS 2005" (something like this) and also

VERY IMPORTANT chech the second options from down "Telecharger les components requires a partir de l'amplacement de mon application" (sorry but i have VS in french)

Thease option will create the kit with all you nead to run reports after the instalation!!!

Popa Iulia

__________________________________

MCP.MCAD.MCSD

|||

iuliax:

Yes you have to install Microsoft Report Viewer.

Popa Iulia

__________________________________

MCP.MCAD.MCSD

I know that report viewer has to be installed? What about SQL services?

When using report viewer with asp.net local delivery the printer icon disappear?

any solution to that?

|||

Hi there,

I am not sure if could solve that problem of not seeing printer on the reportviewer.

If you have solve the problem that please let me know, as I am also having same problem here. The printer does not appear on the toolbar. Even though I switched the Showprinter properties of reportviewer true.

Thanks

MEmam

kms

|||

Hi there,

I am still waiting for the any answer.

Please if there anyone is welcome to help me to solve the problem of why I can't see printer option on my reportviewer.

Thanks in advance if there is anyone who could provide me the answer. It will be a great help.

regards,

Emam

Filters for dataset from Parameter - How to ignore if the parameter is NULL ?

Hi,

I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.

The idea is the create snapshot nightly and then display the report filtered based on parameters to users.

I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,

Right now I defined it this way :

Left =Fields!RegionCode.Value Operator = Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)

I do this for the 4 parameters that are passed, if they are null, make then equals to the field.

I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.

Thanks

One way would be to use this in your stored procedure or query, with coalesce...

eg. AND COALESCE(@.RegionCode,[RegionCode]) = [RegionCode]

That way you're not bringing the data into the report and then filtering it out, you're filtering at the source.

|||

No I don't want to do that,

The idea is the get ALL the data in a snapshot nightly then apply filters on the snapshot when we display to the users.

The result is that the DB will not get hammered everytime a report is run but only once at night.

So the coalesce will not work.

|||

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

|||

Andrew - T4G wrote:

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

Thanks, I'll try something like that.