Dataset Query using Parameters

OK. I've got a tough one here. I am attempting to create a parameter .aspx
page that will pass in start date, end date and multiple storeIDs to the
report. A section of my query in the report dataset looks like this:
WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
@.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
The problem is at the end with the @.paramStore. It works if you pass it
just one StoreID. The syntax becomes ...StoreID IN ('1')
When you try to pass it more than one storeid, it blows up. The syntax
becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
convert '1,2' to datatype int. Is there a way to take these leading and
trailing apostrophes off or can you think of a workaround? Thanks.You have to do a dynamically generated SQL statement, like this:
= "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
@.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
(" & @.paramStore & "))"
It builds the SQL statement on the fly, so you won't be able to use the
query designer after this. I may not have put the quotes in properly, but I
hope you get the idea.
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> OK. I've got a tough one here. I am attempting to create a parameter
> .aspx
> page that will pass in start date, end date and multiple storeIDs to the
> report. A section of my query in the report dataset looks like this:
> WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> The problem is at the end with the @.paramStore. It works if you pass it
> just one StoreID. The syntax becomes ...StoreID IN ('1')
> When you try to pass it more than one storeid, it blows up. The syntax
> becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> convert '1,2' to datatype int. Is there a way to take these leading and
> trailing apostrophes off or can you think of a workaround? Thanks.|||Thanks Jeff but I can't seem to get this to work. It changes my quotation
marks to brackets and comes up with error "Identifier expected."
"Jeff A. Stucker" wrote:
> You have to do a dynamically generated SQL statement, like this:
> = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
> (" & @.paramStore & "))"
> It builds the SQL statement on the fly, so you won't be able to use the
> query designer after this. I may not have put the quotes in properly, but I
> hope you get the idea.
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > OK. I've got a tough one here. I am attempting to create a parameter
> > .aspx
> > page that will pass in start date, end date and multiple storeIDs to the
> > report. A section of my query in the report dataset looks like this:
> >
> > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> >
> > The problem is at the end with the @.paramStore. It works if you pass it
> > just one StoreID. The syntax becomes ...StoreID IN ('1')
> >
> > When you try to pass it more than one storeid, it blows up. The syntax
> > becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> > convert '1,2' to datatype int. Is there a way to take these leading and
> > trailing apostrophes off or can you think of a workaround? Thanks.
>
>|||My advice in this situation is to back up and make sure you can create the
appropriate string.
Create a report that has the report parameters and a textbox and nothing
else. In the textbox put in the expression. Now you should be able to copy
and paste the result into query analyzer and it should work. Sometimes just
seeing the result will let you know what you are doing wrong.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:7236F404-0AB1-44C6-97A1-A601A1E2B738@.microsoft.com...
> Thanks Jeff but I can't seem to get this to work. It changes my quotation
> marks to brackets and comes up with error "Identifier expected."
> "Jeff A. Stucker" wrote:
> > You have to do a dynamically generated SQL statement, like this:
> >
> > = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> > @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID
IN
> > (" & @.paramStore & "))"
> >
> > It builds the SQL statement on the fly, so you won't be able to use the
> > query designer after this. I may not have put the quotes in properly,
but I
> > hope you get the idea.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> > news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > > OK. I've got a tough one here. I am attempting to create a parameter
> > > .aspx
> > > page that will pass in start date, end date and multiple storeIDs to
the
> > > report. A section of my query in the report dataset looks like this:
> > >
> > > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> > >
> > > The problem is at the end with the @.paramStore. It works if you pass
it
> > > just one StoreID. The syntax becomes ...StoreID IN ('1')
> > >
> > > When you try to pass it more than one storeid, it blows up. The
syntax
> > > becomes ...StoreID IN ('1,2') and an error comes up saying that it
cannot
> > > convert '1,2' to datatype int. Is there a way to take these leading
and
> > > trailing apostrophes off or can you think of a workaround? Thanks.
> >
> >
> >

Data-Driven Subscription - Empty Field List on Step 4

I am attempting to create a data-driven subscription. I step through the
wizard, paste in the query, validate and click next. When I get to the screen
to map the fields from the database table to the fields in the subscription
(to, cc, reply-to, etc).
There are no fields in the drop-down. I have tried numerous different
queries, that I know work when I run them in a report, but don't seem to want
to populate the field list. The source database is Teradata, if that makes a
difference.
There is no error, and I can't seem to find any log entries, so I am not
sure where to turn. Any Ideas?
SteveUPDATED: I have confirmed that, in fact the data-driven subscription will
pull fields from SQL Server, so the issue seems to be with the Teradata
connection.
Any help?
"Steve Muise" wrote:
> I am attempting to create a data-driven subscription. I step through the
> wizard, paste in the query, validate and click next. When I get to the screen
> to map the fields from the database table to the fields in the subscription
> (to, cc, reply-to, etc).
> There are no fields in the drop-down. I have tried numerous different
> queries, that I know work when I run them in a report, but don't seem to want
> to populate the field list. The source database is Teradata, if that makes a
> difference.
> There is no error, and I can't seem to find any log entries, so I am not
> sure where to turn. Any Ideas?
> Steve

Databse in Use

I am attempting to schedule a Transaction log update to our reports server.
the transaction log completes fine as long as no one is in the DB. How can
I restart SQL or drop the users before I run the log restore so I do not get
the Database in use error?How about executing ALTER DATABASE and set it in single user with the
ROLLBACK IMMEDIATE option?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Will Westfall" <willwjr@.nospamyahoo.com> wrote in message
news:%23o6jcT8pDHA.1948@.TK2MSFTNGP12.phx.gbl...
> I am attempting to schedule a Transaction log update to our reports
server.
> the transaction log completes fine as long as no one is in the DB. How
can
> I restart SQL or drop the users before I run the log restore so I do not
get
> the Database in use error?
>|||Thanks for the quick response. I will give that a shot.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:exy65V8pDHA.372@.TK2MSFTNGP11.phx.gbl...
> How about executing ALTER DATABASE and set it in single user with the
> ROLLBACK IMMEDIATE option?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Will Westfall" <willwjr@.nospamyahoo.com> wrote in message
> news:%23o6jcT8pDHA.1948@.TK2MSFTNGP12.phx.gbl...
> > I am attempting to schedule a Transaction log update to our reports
> server.
> > the transaction log completes fine as long as no one is in the DB. How
> can
> > I restart SQL or drop the users before I run the log restore so I do not
> get
> > the Database in use error?
> >
> >
>