Showing posts with label particular. Show all posts
Showing posts with label particular. Show all posts

Thursday, March 22, 2012

Digital Dashboard Question

I’m not sure if this is the right topic for this particular forum but here goes anyway…

Okay, admittedly, I am not really familiar with MS SQL Server (I’m Oracle, actually) but I am investigating a possible implementation on a relatively small scale.I am sure that MS SQL Server will handle all the database functions I need and will leave plenty of room for future growth.That really isn’t the issue.What I am trying to figure out is how the Digital Dashboard I’ve read about works.Of particular concern is the apparent connection between the Dashboard and Share Point.My first question is this:Is Share Point required to use the Digital Dashboard?

My next question is actually a bit easier… Is there a sample site I can access that shows some of the capabilities of a Digital Dashboard implementation?I’d like to make sure that the kinds of displays I am trying to build are actually available using this feature…

Thanks in advance for your assistance!

Try searching around on the MS newsgroups. From what I read on there Digital Dashboard has long been superceeded by WSS.

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?query=%22digital+dashboard%22&dg=&cat=en_US_8BC69AC3-3D05-041E-84C8-501EAB19564E&lang=en&cr=US&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us

|||

Probably we should start with some understanding about what you are reading. Searching on "digital dashboard" reveals that it's been announced several years ago and a lot of things have changed since

Next, tell us a bit more about your high-level requirements. I guess the most important question is does your dashboard have to be web-based? If so, SharePoint v. 3.0 will definately help since you will get so much "dashboard" plumbing essentially for free, e.g. page personalization. In addtion, since you are posting your question to this forum, SQL Server 2005 SP2 will allow you integrate SSRS reports seamlessly with SharePoint. You will be able to bring in other report views, e.g. Excel spreadsheets, KPIs from SSAS. etc. Yet, you don't have to use SharePoint if for some reason you don't want to. Alternatively, you can whip out a custom ASP.NET-based app.

About your second question, we are still waiting for the AdventureWorks dashboard demo. The Office newsgroups will be a better vanue to ask this question.

|||

Okay...

High Level Requirements - We are a large Army Hospital. The types of data that I need to display include financial, productivity, manpower, and HR data. We currently have several MS Access databases that are handeling the analysis and reporting of this information but because of the large amounts of data, they are begining to stretch the file size limitations of Access. The specific reports that I will want to display are various bar charts, line charts, spreadsheet-type reports and possibly other forms based on the requirements determination that we are still putting together. I will need to use drill down for two reasons: First, each user will need to see primarily that data which pertains to their specific work center. Second, some users will need to move up and down the data chain to gather and analyze information across the enterprise.

Web-based - Well, I think this would work best. Idealy, I'd like to be able to create a mobile version that key staff members can access via their Blackberry devices. This will allow them to take certain data with them to meetings, while traveling, etc... I would be reluctant to put together something that required a client installation on the local PC. There are a lot of people who would need the information and installing and maintaining each one would be difficult.

Share Point - Well, my only problem with this is that we do not currently have Share Point implemented (I know... wake up and smell the technology).

Custom App - Well, the obvious drawback here is development and maintenance. I really don't have the resources to commit to a complex development and maintaining it would be equaly problematic. This is why I think a pre-designed thin client of some kind would work best for my situation. I am not familiar with ASP.NET so I don't really know the level of complexity involved.

I am not necessarily committed to MS SQL Server, if there is another option out there, I'd be happy to hear about it...

Again, thanks in advance for your assistance.

|||

1. Start by implementing the SSRS reports you need.

2. If you need to display reports side-by-side consider installing SQL Server 2005 SP2 to surface the SSRS reports to SharePoint If you don't need side-by-side reports and web parts, you may find that the Report Manager (a web application that ships with SSRS) is all you need. The Report Manager will allow your users to access reports on the web.

Drillthrough reports are supported by SSRS.

Wednesday, March 21, 2012

Differntial Backup

Question
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.

> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
|||This is exactly what I was looking for.
Thanks
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:

> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>
|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>
|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
[vbcol=seagreen]
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:

Differntial Backup

Question
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.Hi
have you seen this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||This is exactly what I was looking for.
Thanks
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> > Question
> >
> > How exactly does the technology of a differntial backup work. I have read
> > msdn articles, etc... But nothing states in particular how the technology
> > itself operates. It doesn't appear to use the log file.
> >
> > Since it is a single file (No secondaries used) how can it create a
> > differential file of updates that have occured since the last full backup?
> >
> > --
> > Paul Bergson
> > MVP - Directory Services
> > MCT, MCSE, MCSA, Security+, BS CSci
> > 2003, 2000 (Early Achiever), NT
> >
> > http://www.pbbergs.com
> >
> > This posting is provided "AS IS" with no warranties or rights.
>|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
--
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>> Question
>> How exactly does the technology of a differntial backup work. I have
>> read
>> msdn articles, etc... But nothing states in particular how the technology
>> itself operates. It doesn't appear to use the log file.
>> Since it is a single file (No secondaries used) how can it create a
>> differential file of updates that have occured since the last full
>> backup?
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> This posting is provided "AS IS" with no warranties or rights.
>|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:
> > The GAM (global allocation map) keeps track of which Extents have been
> > changed since the last full backup. Those are the ones written by a
> > differential backup.
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> >
> > "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> > news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> > > Question
> > >
> > > How exactly does the technology of a differntial backup work. I have read
> > > msdn articles, etc... But nothing states in particular how the technology
> > > itself operates. It doesn't appear to use the log file.
> > >
> > > Since it is a single file (No secondaries used) how can it create a
> > > differential file of updates that have occured since the last full backup?
> > >
> > > --
> > > Paul Bergson
> > > MVP - Directory Services
> > > MCT, MCSE, MCSA, Security+, BS CSci
> > > 2003, 2000 (Early Achiever), NT
> > >
> > > http://www.pbbergs.com
> > >
> > > This posting is provided "AS IS" with no warranties or rights.
> >
> >

Differntial Backup

Question
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.Hi
have you seen this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.

> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||This is exactly what I was looking for.
Thanks
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:

> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in
a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
[vbcol=seagreen]
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:
>

Sunday, March 11, 2012

Differential Backup Script

In SQL 2000 how can you script a differential backup to backup to a
particular named file and to replace the existing file contents?Here's an example:
BACKUP DATABASE pubs TO DISK='G:\Pubs.BAK' WITH INIT, DIFFERENTIAL
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<robin9876@.hotmail.com> wrote in message
news:1122993046.668119.116470@.o13g2000cwo.googlegroups.com...
In SQL 2000 how can you script a differential backup to backup to a
particular named file and to replace the existing file contents?

Friday, February 24, 2012

Different images based on condition...

I was wondering if there is a way to display a particular image into a table
record based on a condition. For example, I have a list of questions that
come in 3 different types, each type has a different image associated with
it, so it would look like this:
<Image1.bmp> First Question (type is 1)
<Image3.bmp> Second Question (type is 3)
<Image3.bmp> Third Question (type is 3)
<Image2.bmp> Forth Question (type is 2)
<Image1.bmp> Fifth Question (type is 1)
...and so forth.
So basically what I am wanting to do is (at the record level) display the
different images based on what type of question I want. But, I can't seem
to figure out how to insert three different images into one cell and supress
two of them based on the 'question_type'. Which was how I accomplished it
in crystal reports.
I think I could probably do this by storing the images in a column in the
database, then call that with my stored procedure. Although, I am afraid
that this would take up tons of space in the database and take forever for
the report to run.
Any thoughts? Thanks!
LisaThe sample report at the end of this posting shows how to conditionally
display an image.
This report conditionally displays an image based on a detail row value. In
this case Germany = Green, Purple = USA, and Canada = Red. The technique is
to place a rectangle in the table detail row and place all images at the
same location in the rectangle. The images should be the same size. Next is
to use an expression to set the image visibility:
=iif(Fields!<FieldName>.Value = "SomeValue", false, true). False = Not
hidden and True = Hidden.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:O4L73T9eEHA.3916@.TK2MSFTNGP11.phx.gbl...
> I was wondering if there is a way to display a particular image into a
table
> record based on a condition. For example, I have a list of questions that
> come in 3 different types, each type has a different image associated with
> it, so it would look like this:
> <Image1.bmp> First Question (type is 1)
> <Image3.bmp> Second Question (type is 3)
> <Image3.bmp> Third Question (type is 3)
> <Image2.bmp> Forth Question (type is 2)
> <Image1.bmp> Fifth Question (type is 1)
> ...and so forth.
> So basically what I am wanting to do is (at the record level) display the
> different images based on what type of question I want. But, I can't seem
> to figure out how to insert three different images into one cell and
supress
> two of them based on the 'question_type'. Which was how I accomplished it
> in crystal reports.
> I think I could probably do this by storing the images in a column in the
> database, then call that with my stored procedure. Although, I am afraid
> that this would take up tons of space in the database and take forever for
> the report to run.
> Any thoughts? Thanks!
> Lisa
>
>
ConditionallyDisplayAnImage.RDL
----
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<Height>0.625in</Height>
<Width>6.375in</Width>
<CanGrow>true</CanGrow>
<Value>This report conditionally displays an image based on a detail
row value. In this case Germany = Green, USA = Purple, and Canada = Red. The
technique is to all images in a rectangle at the using the same origin and
size. Next is to place the rectangle in the table detail row cell. Finally,
is to set each images initial visibility using an expression:
=iif(Fields!<FieldName>.Value = "SomeValue", false, true).</Value>
</Textbox>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>country</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Image Name="image1">
<ZIndex>2</ZIndex>
<Height>0.15625in</Height>
<Visibility>
<Hidden>=iif(Fields!country.Value = "Germany",
false, true)</Hidden>
</Visibility>
<Source>Embedded</Source>
<Style />
<Value>greenbullet</Value>
<Sizing>AutoSize</Sizing>
</Image>
<Image Name="image2">
<ZIndex>1</ZIndex>
<Height>0.15625in</Height>
<Visibility>
<Hidden>=iif(Fields!country.Value = "USA",
false, true)</Hidden>
</Visibility>
<Source>Embedded</Source>
<Style />
<Value>greybullet</Value>
<Sizing>AutoSize</Sizing>
</Image>
<Image Name="image3">
<Height>0.15625in</Height>
<Visibility>
<Hidden>=iif(Fields!country.Value = "Canada",
false, true)</Hidden>
</Visibility>
<Source>Embedded</Source>
<Style />
<Value>redbullet</Value>
<Sizing>AutoSize</Sizing>
</Image>
</ReportItems>
<Style />
</Rectangle>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Top>0.75in</Top>
<Width>2.375in</Width>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>0.20833in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.625in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>69fa60f9-f434-44ed-a70d-2fc41b5d0cb3</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.50001in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Country
FROM Customers
WHERE (Country = N'Germany') OR
(Country = N'USA') OR
(Country = N'Canada')</CommandText>
</Query>
</DataSet>
</DataSets>
<EmbeddedImages>
<EmbeddedImage Name="greenbullet">
<MIMEType>image/png</MIMEType>
<ImageData>iVBORw0KGgoAAAANSUhEUgAAABQAAAAPCAMAAADTRh9nAAAAAXNSR0IArs4c6QAAA
ARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRP
AAAAwBQTFRFISoIKDMKLjoLLzUbMT0MMzscNUMNO0oPO0UePUIvRFURU101TWAVU2gVW3EYYHcdZ
XskT1NDYGNYcnlcdXhtbogeaoIidY0te5UufIZfgZ0shZ82h6cojawuh6Mzi6c4lLUyiJpSlK5En
r1EkqFlmKN2nqp4pcc+psJTrMlVsc5YuNtPvt9ZwONYyetfyOZv0O9y1vR12vd33vt05P19hoiBj
5ODoKKYpamZrrSbrK2rs7Wstrmt9v+U//+Z////AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAadF2SAAAAMFJREFUKFNNj9cagjAUgxFQtLZQlSWIRVCR4QQXMt7/
rURo1XOTk//LRcLV9J6r7SanP9dpQRzXWWr40boOPhdBvI8DoqHXFxYkOGRZuvctDO4suXaStKrK
c0h0OGPQcJOsgZeI6GhM4Ut3o0tZZsfQxlBkScXyk9P5GHmGDHoMYm3ph+HOMzGS+gzOZc20bcvA
CPDqtydACtaxDIEofFZ15W8D2ByQeOH6W1TnQ3Eg8tyoW0+313WuTqZt7B9S38ob8DI2JkkuGrgA
AAAASUVORK5CYII=</ImageData>
</EmbeddedImage>
<EmbeddedImage Name="greybullet">
<MIMEType>image/png</MIMEType>
<ImageData>iVBORw0KGgoAAAANSUhEUgAAABQAAAAPCAMAAADTRh9nAAAAAXNSR0IArs4c6QAAA
ARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRP
AAAAwBQTFRFOC04XkxeemV6pYmlyazJ9tn2//X/////AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAABATwmQAAAHRJREFUKFNNz1kSwCAIA1CWqPe/cROgrflR3yCinQky
E7O3XkWZEc2NSGABGV5aSFtMab7IbmvvvYTxYgC0Rh9EXJXqVz2JasrC8B+lfIa37cLoMd3te+i4
h0IzDdrDp7liZhpz8IBn5f6mfsXLVfZXzmmWB4hABapTVcWWAAAAAElFTkSuQmCC</ImageData>
</EmbeddedImage>
<EmbeddedImage Name="redbullet">
<MIMEType>image/png</MIMEType>
<ImageData>iVBORw0KGgoAAAANSUhEUgAAABQAAAAPCAMAAADTRh9nAAAAAXNSR0IArs4c6QAAA
ARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRP
AAAAwBQTFRFAAAAKgAIMwAKOgALPQAMNRUbNhUcOxUcAEBAQQANRgAOSgAPTAAPRRUeUwESVQARW
QESXQMVQiovXSo1YQIVYgUXZQIVZAQXaQEWaAYZcQIYdwYdcQgdeAIZfAQcegwiew4kfQsifg0jf
A0kdxYpexYqQABAQEAAQEBAU0BDY1VYeVVceGptAAD/AP8AAICAAMDAAP//gQMciAQeggoihwoji
gYhjRcunw8skBYulhQunBEtkBgwlhs0mR01nhw2ogwqpwgorAsrrA8uoxczpBs3px05qBMxsRc2t
RIytRY2oCA5pS9GripEvSVEuChFhlVfmkBSoVZlo2t2qmx4xxs+/wAAyR5AxSVFxDdTwjhTzDVTy
ThVyTtYzjtY2yxP0TFR2DBR3DBS3zdZ4zZY6ztfzkBd3EBf5EFh5lJv6FJw71Ny9VV28ll49lt6+
lF0/FJ2/Vt9/lx/gACAwADA/wD//2qJ/2yN/26S/3OU/3SV/3aZ/3+hgIAAwMAA//8AgICAiICBk
4CDopWYqZWZrpWatJWbraqrtaqsuaqtvKuu////////wMDA///D////////wMDA////AAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAeTG9CwAAAM1JREFUKFNj6IaCtsCQ4FYomwFCd/k4eTo7GKu2gHkQ
wTbH0PiUxAh3M4kOuGCna3hqQVF+hp+1HH8zTGWQc1J+RXV5TrSbiYg2TNDGK7mwuqYqL87NREwY
Kthh5JGQU15ZlhVpryjCAlMpa+GXnp2bmeZrKSPABBNUMXPxj4mN8rVVFudhhwkGSJla2XvbmStI
8jHrwN3JLyGtqCQvI8rHwtYOF2ziEhAREeTnZmZtRPiou52XhZOTmVEIpA7mTSCjXUdTC6wMWRDK
B1MA/Td3eObvA7wAAAAASUVORK5CYII=</ImageData>
</EmbeddedImage>
</EmbeddedImages>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>e66e2b26-0660-4802-920d-211083d62c86</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Different execution plan between stored proc and ad-hoc query

We have just encountered a problem with a particular stored procedure which I would appreciate anyone's thoughts on.

The proc in question contains a single select statement which simply joins four tables and filters based on two parameters. It normally runs in well under a second but this morning we came in to find it was running in anything up to a minute.

After investigation we found that it was running a clustered index scan on one of the larger tables (approx 10.5m rows) rather than using the appropriate index. However, when we took the select statement out and ran it as an ad-hoc query it was utilising the index and performing correctly.

We tried recompiling the proc and also ran sp_updatestats on the database but we were still getting the different execution plans being generated. It was only after we ran UPDATE STATISTICS WITH FULLSCAN on the table in question that the proc went back to performing normally.

Can anyone shed any light on why the proc and query were using different execution plans, even after recompiling the proc? The table is heavily utilised in all areas of the system so I could understand if the stats got out of date (although autostats is on and sp_updatestats does get run on the database 2 or 3 times a day through an automated process).

Also, this is the second week in a row where this behaviour has occured so we need to try and mitigate the risks next week. Obviously we could schedule a stats update with fullscan early Monday morning but we would like to understand what is causing this and try and fix it "properly".

cheers

James

If the system is heavily updated, the stats could get stale. Consider running [sp_updatestats 'resample'] or explicitly forcing an index (the latter is not really recommended unless you fully understand the consequences).

e.g.

select * from tb with (index(myindex))

|||

It's very quite possible to get two different plans. Take for example these two TSQL statements:

a)

declare @.x int

set @.x = 99

select * from t1 where col1 = @.x

b)

select * from t1 where col1=99

In a), the optimizer evalutes the whole batch, and cannot evalute the literal value of 99 since it's a parameter value set inside a batch. So it makes a guesstimate and optimizes for a given value which it thinks may be the most correct/optimal. Often times it is, often times it is not. In b), the statement is evaluated and immediately it knows the value of 99, so it can get a very accurate estimate, thus producing a more reliable plan.

The same holds true for stored procedures. If you create a stored procedure and pass in a parameter value used in your WHERE clause, the optimizer will most likely produce a far better estimate than if you created a procedure which declared variables, set them, and then used them in your WHERE clause. It's always beneficial in a proc to try not to declare variables, set them and then use them in a query. If you can pass them as a parameter, the query has a better chance of producing an optimal plan based on accurate estimates.

Where you can also get into trouble is if the plan is cached with a value that gave good estimates/performance at the time it was created, but then as time passes and changes occur to the tables, yes the stats can get out of date, and you'll need to either update stats or clear the cache to remove the stale stats/plan.

If you got lost with what i was trying to say above, forgive me, I'm sure it's documented in some whitepaper somewhere, but I know this is an issue as merge replication procs and triggers hit this kind of problem often.

|||

Thank you both for your comments. Greg, that makes sense and does explain why we may have been seeing the different plan.

This problem actually appeared again yesterday, although this time the ad-hoc query was using the same (flawed) plan as the proc. We have decided in this instance to use an index hint as there are really no circumstances where it should be doing a full scan. As OJ says, I know these are not really recommended but I think I'm happy for this to be one of the rare expections!

cheers

James

|||Which version of SQL Server are you using? If 2005 then you might opt for the "Optimize For" hint or "Option Recompile". When using an index hint, you run the risk of accidentally breaking code later if that index is dropped or changed to include different columns.

Friday, February 17, 2012

Differences IN AS 2000 and AS 2005

Hi,

I just wanted to know if I can replicate a particular thing that I was able to do in AS 2000 very easily in AS 2005.

Basically I used to define a time dimension in AS 2000 which was basically a single column and was able to split it into 3 levels Year,Quarter and Month using the functinality provided by AS 2000.

Can I do something similar in AS 2005 because the options that I see in order to define a time hierarchy needs me to pick 3 different columns for each time level or hierarchy.

Thanks

There is a new "Server Time Dimension" option that allows you to build a time dimension simply by entering a begin and end date. If you want to control the exact format of how the level members work you can always create a "named query" in the data source view using the date from your fact table and T-SQL date functions to format the level members. The feature you are referring to is no longer available in 2005. Here is an example of a query using a table from the "AdventureWorks" database that demonstrates how you could do this using a named query:

USE [AdventureWorks]

GO

SELECT DISTINCT

OrderDate,

DatePart(year,OrderDate) AS [Year],

'Q' + DateName(quarter,OrderDate) + '-' + Right(DatePart(year,OrderDate),2) AS [Quarter],

Left(DateName(month,OrderDate),3) + '-' + Right(DatePart(year,OrderDate),2) AS [Month],

Right('0' + DateName(day,OrderDate),2) + '-' + Left(DateName(month,OrderDate),3) + '-' + Right(DatePart(year,OrderDate),2) AS [Day]

FROM

Sales.SalesOrderHeader

ORDER BY 1

GO

HTH,

Steve