check fields in select statement

hi,

I am getting some fields back from a select statement, how do you check one of the fields and display a string depending on what it is? Is there something like an if statement you can use? for example

select

field1,
field2 /*how do you check to see what it is here and display something depending on what it is*/

from
record

I am trying to see if field2 is a '' or empty string character

thxYou use a CASE statement.

SELECT field1, CASE field2 WHEN '' THEN 'BLANK' ELSE field2 END as Field2 FROM Table

If rather than '' the value might be null, you can use ISNULL

SELECT field1, ISNULL(field2,'BLANK') as field2 FROM Table

|||thank you for the help

Charging back

Hi,
Is there any automated method to measure reporting services usage (report
duration) per report in order to be used for charging back report owners in
an enterprise and shared reporting services environment?
We have SQL Server RS 2000 SP2 on an shared report/ SQL server and need to
find a method to charge back our customers.
ThanksAlex,
All your reporting services report processing statistics are stored in the
Report Server database in the ExecutionLog table. It details execution
times, user ID, etc for each report run. You will have to tie the report ID
to the ItemID in the Catalogue to get you report names to see which reports
are being run by who.
Reporting services cleans the executionlog table out every couple of months,
so if you wish to keep a permanent record of all reports run then you will
have to periodically export the table contents to another table/database.
Hoep this helps
Ray
"Alex" wrote:
> Hi,
> Is there any automated method to measure reporting services usage (report
> duration) per report in order to be used for charging back report owners in
> an enterprise and shared reporting services environment?
> We have SQL Server RS 2000 SP2 on an shared report/ SQL server and need to
> find a method to charge back our customers.
> Thanks
>
>|||Hi Ray,
Thanks for the info. It works great.
Alex
"Ray Seppala" <Ray.Seppala@.defence.gov.au.(Donotspam)> wrote in message
news:81AD2BFF-FDED-4B78-BA08-FFCC11C4A3F1@.microsoft.com...
> Alex,
> All your reporting services report processing statistics are stored in the
> Report Server database in the ExecutionLog table. It details execution
> times, user ID, etc for each report run. You will have to tie the report
> ID
> to the ItemID in the Catalogue to get you report names to see which
> reports
> are being run by who.
> Reporting services cleans the executionlog table out every couple of
> months,
> so if you wish to keep a permanent record of all reports run then you will
> have to periodically export the table contents to another table/database.
> Hoep this helps
> Ray
> "Alex" wrote:
>> Hi,
>> Is there any automated method to measure reporting services usage (report
>> duration) per report in order to be used for charging back report owners
>> in
>> an enterprise and shared reporting services environment?
>> We have SQL Server RS 2000 SP2 on an shared report/ SQL server and need
>> to
>> find a method to charge back our customers.
>> Thanks
>>|||Ray,
I beleive the start and end time are from when user clicks on run button
until they actually see the report. This time will include the time while
Reporting server was waiting for data from database server. This will not be
fair to include the wait time in charge back and charge customers for it.
Is there anyway to get the actual time Reporting server was busy processing
the report only?
Thanks
"Ray Seppala" <Ray.Seppala@.defence.gov.au.(Donotspam)> wrote in message
news:81AD2BFF-FDED-4B78-BA08-FFCC11C4A3F1@.microsoft.com...
> Alex,
> All your reporting services report processing statistics are stored in the
> ver
> Report Server database in the ExecutionLog table. It details execution
> times, user ID, etc for each report run. You will have to tie the report
> ID
> to the ItemID in the Catalogue to get you report names to see which
> reports
> are being run by who.
> Reporting services cleans the executionlog table out every couple of
> months,
> so if you wish to keep a permanent record of all reports run then you will
> have to periodically export the table contents to another table/database.
> Hoep this helps
> Ray
> "Alex" wrote:
>> Hi,
>> Is there any automated method to measure reporting services usage (report
>> duration) per report in order to be used for charging back report owners
>> in
>> an enterprise and shared reporting services environment?
>> We have SQL Server RS 2000 SP2 on an shared report/ SQL server and need
>> to
>> find a method to charge back our customers.
>> Thanks
>>|||Alex,
There is a bit in BOL about the execution data you can get from RS. Try
this link;
ms-help://MS.RSBOL80.1033/rsadmin/htm/arp_rslogfiles_v1_88gy.htm
Chris
Ray Seppala wrote:
> Alex,
> All your reporting services report processing statistics are stored
> in the Report Server database in the ExecutionLog table. It details
> execution times, user ID, etc for each report run. You will have to
> tie the report ID to the ItemID in the Catalogue to get you report
> names to see which reports are being run by who.
> Reporting services cleans the executionlog table out every couple of
> months, so if you wish to keep a permanent record of all reports run
> then you will have to periodically export the table contents to
> another table/database.
> Hoep this helps
> Ray
> "Alex" wrote:
> > Hi,
> >
> > Is there any automated method to measure reporting services usage
> > (report duration) per report in order to be used for charging back
> > report owners in an enterprise and shared reporting services
> > environment?
> >
> > We have SQL Server RS 2000 SP2 on an shared report/ SQL server and
> > need to find a method to charge back our customers.
> >
> > Thanks
> >
> >
> >

Character String Query Doesnt Fill Dataset

I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though.

I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:

set @.FullQuery = 'Select ' + @.FieldsinOrder + ' from tblExample'
exec (@.FullQuery)

This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application.

If I change the query to be a normal select it works. For example:

select * from tblEmample

That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?

try to build your command in ASP and pass it to SQL command command object

like:

Dim

yourCommandAs SqlClient.SqlCommand =New SqlClient.SqlCommand(" 'Select '" + FieldsinOrder + '" from tblExample"', yourconnection)
yourCommand.CommandType = CommandType.Text

Maybe it will work.

When your use Exec to execute query inside query is possible that result from this exec is not visible to ASP.NET code as valid result.

Thanks