Can I change the default CSV extract to reporting services

I have been searching and found the answer to my problems in the XML string
sent from the reporting services engine on scheduled jobs. I know I need to
add a Noheader command and also change to ASCII.
I wondering how do I do this to scheduled jobs with in the reporting
services environment and schedule it to run each day with the extra device
parameters. The default I want for this report will be the same default for
all csv files so can I change something in the report engine or the CSV
extract defaults?
Current address I use is
http://report01/ReportServer?%2fDevelopment%2fEIS%2fEIS02+BSMPurchaseOrderExtract&rs:Format=CSV&rs:Command=Render&rc:Extension=txt&rc:NoHeader=true&rc:FieldDelimiter=&rc:Encoding=asciiI would love to know the answer to this because I have exactly the same
problem.
"John230873" wrote:
> I have been searching and found the answer to my problems in the XML string
> sent from the reporting services engine on scheduled jobs. I know I need to
> add a Noheader command and also change to ASCII.
> I wondering how do I do this to scheduled jobs with in the reporting
> services environment and schedule it to run each day with the extra device
> parameters. The default I want for this report will be the same default for
> all csv files so can I change something in the report engine or the CSV
> extract defaults?
> Current address I use is
> http://report01/ReportServer?%2fDevelopment%2fEIS%2fEIS02+BSMPurchaseOrderExtract&rs:Format=CSV&rs:Command=Render&rc:Extension=txt&rc:NoHeader=true&rc:FieldDelimiter=&rc:Encoding=ascii
>

Can CONTAINSTABLE be made to match unconditionally?

Say that users searching a Books database are able to search by Author,
Title, and Publisher using three separate text boxes. They can choose to
leave any or all text boxes empty if they do not care to filter by that
particular item.
Say I have a query that looks similar to this:
b.* FROM Books b
INNER JOIN CONTAINSTABLE( Books, Author, @.author) authorRank ON b.BookId =
authorRank.[KEY]
INNER JOIN CONTAINSTABLE( Books, Title, @.title) titleRank ON ( b.BookId =
titleRank.[KEY] )
INNER JOIN CONTAINSTABLE( Books, Publisher, @.publisher) publisherRank ON (
b.BookId = publisherRank.[KEY] )
This works great if the user chooses to enter something in each of the three
text boxes. However, CONTAINSTABLE does not accept an empty string for the
search condition (nor does it accept *, %, or other wildcards except when
searching for a prefix). How can I get the query to unconditionally match
title and publisher if they leave title and publisher blank but type
something for author?
If there is no way to allow any title and publisher to be returned when the
user leaves those text boxes blank, I will need to write a query for if they
type all three text boxes, a query for if they type in none of the text
boxes, a query if they type in just the publisher but not the title or
author, a query... etc. Not a good solution.
How can I achieve the results I want without writing multiple queries?
Thank you.Hi, Greg
Try something like this (untested):
SELECT * FROM Books
WHERE (@.author IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Author, @.author)
)) AND (@.title IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Title, @.title)
)) AND (@.publisher IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Publisher, @.publisher)
))
Razvan|||That doesn't let me use any of the ranking information provided by
CONTAINSTABLE. It may be workable. I may have found some sort of
sp_configure setting that will change the behavior of noise words. I'm not
sure about it yet.
"Razvan Socol" wrote:

> Hi, Greg
> Try something like this (untested):
> SELECT * FROM Books
> WHERE (@.author IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Author, @.author)
> )) AND (@.title IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Title, @.title)
> )) AND (@.publisher IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Publisher, @.publisher)
> ))
> Razvan
>|||> That doesn't let me use any of the ranking information provided by
> CONTAINSTABLE.
In this case, you might want to use something like this (also
untested):
SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookID=x.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookID=y.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
b.BookID=z.[KEY]
WHERE (@.author IS NULL OR x.[KEY] IS NOT NULL)
AND (@.title IS NULL OR y.[KEY] IS NOT NULL)
AND (@.publisher IS NULL OR z.[KEY] IS NOT NULL)
Razvan|||If @.author is NULL (or blank), then CONTAINSTABLE will throw an error, which
is pretty much the entire problem here.
"Razvan Socol" wrote:

> In this case, you might want to use something like this (also
> untested):
> SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
> LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookID=x.[KEY]
> LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookID=y.[KEY]
> LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
> b.BookID=z.[KEY]
> WHERE (@.author IS NULL OR x.[KEY] IS NOT NULL)
> AND (@.title IS NULL OR y.[KEY] IS NOT NULL)
> AND (@.publisher IS NULL OR z.[KEY] IS NOT NULL)
> Razvan
>|||> If @.author is NULL (or blank), then CONTAINSTABLE will throw an error [...]
Aha! I told you it was untested... :) I assumed that CONTAINSTABLE will
return an empty resultset when given a NULL search condition.
Obviously, I was wrong.
In this case, we can use a non-existent word instead of NULL, like
this:
SET @.title=ISNULL(@.title,'NotSpecified')
SET @.author=ISNULL(@.author,'NotSpecified')
SET @.publisher=ISNULL(@.publisher,'NotSpecifi
ed')
SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookId=x.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookId=y.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
b.BookId=z.[KEY]
WHERE (@.author='NotSpecified' OR x.[KEY] IS NOT NULL)
AND (@.title='NotSpecified' OR y.[KEY] IS NOT NULL)
AND (@.publisher='NotSpecified' OR z.[KEY] IS NOT NULL)
If you think the word "NotSpecified" may appear in a title of a book,
you can change it with another inexisting word.
Razvan

Can a multi-column index be used for single-column searching?

I know that in the case of some database engines a two-column index can
also be used to speed searches on the first of the two indexed columns
when searched singly, thereby making it unnecessary to create a
separate index on the first column.
For example, a two-column index in (lastname,firstname) will also be
used by those searches that search only on the 'lastname' field, so
that a separate one-column index on 'lastname' is not necessary.
Is this the case with SQL-Server?It is. Statistics are kept for the first column in a composite index.
ML
http://milambda.blogspot.com/|||Yes.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<quizling@.biztekinc.net> wrote in message
news:1136758766.471031.129740@.g14g2000cwa.googlegroups.com...
>I know that in the case of some database engines a two-column index can
> also be used to speed searches on the first of the two indexed columns
> when searched singly, thereby making it unnecessary to create a
> separate index on the first column.
> For example, a two-column index in (lastname,firstname) will also be
> used by those searches that search only on the 'lastname' field, so
> that a separate one-column index on 'lastname' is not necessary.
> Is this the case with SQL-Server?
>