Query problem splitting field into two comumns

Hi I'm new to reporting services. I'm desinging a query for a report.
I have one filed "ship_yw" that contains year and week numbers, for example,
200701, 200702. I want to display the year in one column and the week in
another. I'm connected to a Progress database via ODBC. I have accomplished
this with no problem in Access using the following code:
SELECT LEFT([Ship_yw],4), RIGHT([Ship_yw],2)
This will not work in Query Builder or if I manually type it in the string
window.
I have also tried:
SELECT
LEFT(Ship__yw,4),
RIGHT(RTRIM(Ship__yw),2)
FROM
oe_head
SELECT
SUBSTRING(Ship__yw,1,4),
SUBSTRING(Ship__yw,5,2)
FROM
oe_head
Query Builder puts unwanted ' around the field name and causes the query to
return "ship" in every row in one column and "yw" in every row of the second.
Any suggestions?Try giving names for the fildes , Use AS
"Twaterman" wrote:
> Hi I'm new to reporting services. I'm desinging a query for a report.
> I have one filed "ship_yw" that contains year and week numbers, for example,
> 200701, 200702. I want to display the year in one column and the week in
> another. I'm connected to a Progress database via ODBC. I have accomplished
> this with no problem in Access using the following code:
> SELECT LEFT([Ship_yw],4), RIGHT([Ship_yw],2)
> This will not work in Query Builder or if I manually type it in the string
> window.
> I have also tried:
> SELECT
> LEFT(Ship__yw,4),
> RIGHT(RTRIM(Ship__yw),2)
> FROM
> oe_head
> SELECT
> SUBSTRING(Ship__yw,1,4),
> SUBSTRING(Ship__yw,5,2)
> FROM
> oe_head
> Query Builder puts unwanted ' around the field name and causes the query to
> return "ship" in every row in one column and "yw" in every row of the second.
> Any suggestions?
>
>

Query problem

Hi,

I've got a problem with the following query, i'd like to write this in SQL for reporting services.

if ($par == "Ok"){
$query .= ' and b.date is not null '
}

if ($par == "NOK"{
$query = 'and b.date is null'
}

How should I do this? If my parameter is OK, the date should be filled in. Otherwise...

THX

You can do something like this

select .... where @.par = 'Ok' AND b.date is not null

union

select .... where @.par = 'NOK' AND b.date is null

I haven't tested it but it should work. The idea being that only one side of the union can have a result because only one side's where clause will evaluate to true.

Query Performance from Reporting Services

Hi,
Has anyone had any problems with running queries from Reporting Services
that perform 300x slower than from other sources? Any Ideas of what may
cause this?
I have a query that uses an Indexed View and runs in 8 seconds if I run it
from QA, but RS takes 30 minutes. All indications from running Profiler
shows that RS is indeed using the indexed view.
Any thoughts of what to check would be appreciated.
ThanksCan you use a stored procedure? There have been instances where the query
used in Reporting Services did not use the same query plan as would be used
if the query was within query analyzer. One query plan used a particular
index and the other did not. Hence the differences in performance. If you
use a stored procedure you would be guaranteed that regardless of where
invoked (query analyzer or RS) they would use the same query plan.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jace" <Jace@.discussions.microsoft.com> wrote in message
news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
> Hi,
> Has anyone had any problems with running queries from Reporting Services
> that perform 300x slower than from other sources? Any Ideas of what may
> cause this?
> I have a query that uses an Indexed View and runs in 8 seconds if I run it
> from QA, but RS takes 30 minutes. All indications from running Profiler
> shows that RS is indeed using the indexed view.
> Any thoughts of what to check would be appreciated.
> Thanks
>|||Yes, I tried using a stored Proc and that didnt improve things.
Interestingly with a stored proc it seems to not even bother caching the
results, so that if you repreview it takes another 30 minutes to see the 8sec
query. I did notice it was passing the stored proc to another stored proc
(sp_procedure_params_rowset) to get the rowset values, but I didn't see any
parameters passed. I'm not sure if this could be causing the huge delay or
not.
But then again, even when I was using a text query straight from RS, I gave
it a direct index hint and if I previewed the data in the Data tab it ran in
8 secs, but if I previewed the report, it took 30 minutes.
Thanks
"Bruce L-C [MVP]" wrote:
> Can you use a stored procedure? There have been instances where the query
> used in Reporting Services did not use the same query plan as would be used
> if the query was within query analyzer. One query plan used a particular
> index and the other did not. Hence the differences in performance. If you
> use a stored procedure you would be guaranteed that regardless of where
> invoked (query analyzer or RS) they would use the same query plan.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jace" <Jace@.discussions.microsoft.com> wrote in message
> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
> > Hi,
> >
> > Has anyone had any problems with running queries from Reporting Services
> > that perform 300x slower than from other sources? Any Ideas of what may
> > cause this?
> >
> > I have a query that uses an Indexed View and runs in 8 seconds if I run it
> > from QA, but RS takes 30 minutes. All indications from running Profiler
> > shows that RS is indeed using the indexed view.
> >
> > Any thoughts of what to check would be appreciated.
> >
> > Thanks
> >
>
>|||How many rows are being returned? Are you using any filters?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jace" <Jace@.discussions.microsoft.com> wrote in message
news:ED4E90BD-7079-4E2A-8DB2-A783AE0BCC47@.microsoft.com...
> Yes, I tried using a stored Proc and that didnt improve things.
> Interestingly with a stored proc it seems to not even bother caching the
> results, so that if you repreview it takes another 30 minutes to see the
> 8sec
> query. I did notice it was passing the stored proc to another stored proc
> (sp_procedure_params_rowset) to get the rowset values, but I didn't see
> any
> parameters passed. I'm not sure if this could be causing the huge delay
> or
> not.
> But then again, even when I was using a text query straight from RS, I
> gave
> it a direct index hint and if I previewed the data in the Data tab it ran
> in
> 8 secs, but if I previewed the report, it took 30 minutes.
> Thanks
>
> "Bruce L-C [MVP]" wrote:
>> Can you use a stored procedure? There have been instances where the query
>> used in Reporting Services did not use the same query plan as would be
>> used
>> if the query was within query analyzer. One query plan used a particular
>> index and the other did not. Hence the differences in performance. If you
>> use a stored procedure you would be guaranteed that regardless of where
>> invoked (query analyzer or RS) they would use the same query plan.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Jace" <Jace@.discussions.microsoft.com> wrote in message
>> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
>> > Hi,
>> >
>> > Has anyone had any problems with running queries from Reporting
>> > Services
>> > that perform 300x slower than from other sources? Any Ideas of what
>> > may
>> > cause this?
>> >
>> > I have a query that uses an Indexed View and runs in 8 seconds if I run
>> > it
>> > from QA, but RS takes 30 minutes. All indications from running
>> > Profiler
>> > shows that RS is indeed using the indexed view.
>> >
>> > Any thoughts of what to check would be appreciated.
>> >
>> > Thanks
>> >
>>|||The row count is 18, but it is summarizing about 100 million records using an
indexed view. I am using report parameters to pass to the query to filter
the results from within the Query statement. Basically a date range
selection, along with a history type. I did try hard coding some values
within the query statement to see if it was faster, but the results were the
same.
"Bruce L-C [MVP]" wrote:
> How many rows are being returned? Are you using any filters?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jace" <Jace@.discussions.microsoft.com> wrote in message
> news:ED4E90BD-7079-4E2A-8DB2-A783AE0BCC47@.microsoft.com...
> > Yes, I tried using a stored Proc and that didnt improve things.
> > Interestingly with a stored proc it seems to not even bother caching the
> > results, so that if you repreview it takes another 30 minutes to see the
> > 8sec
> > query. I did notice it was passing the stored proc to another stored proc
> > (sp_procedure_params_rowset) to get the rowset values, but I didn't see
> > any
> > parameters passed. I'm not sure if this could be causing the huge delay
> > or
> > not.
> >
> > But then again, even when I was using a text query straight from RS, I
> > gave
> > it a direct index hint and if I previewed the data in the Data tab it ran
> > in
> > 8 secs, but if I previewed the report, it took 30 minutes.
> >
> > Thanks
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Can you use a stored procedure? There have been instances where the query
> >> used in Reporting Services did not use the same query plan as would be
> >> used
> >> if the query was within query analyzer. One query plan used a particular
> >> index and the other did not. Hence the differences in performance. If you
> >> use a stored procedure you would be guaranteed that regardless of where
> >> invoked (query analyzer or RS) they would use the same query plan.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Jace" <Jace@.discussions.microsoft.com> wrote in message
> >> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
> >> > Hi,
> >> >
> >> > Has anyone had any problems with running queries from Reporting
> >> > Services
> >> > that perform 300x slower than from other sources? Any Ideas of what
> >> > may
> >> > cause this?
> >> >
> >> > I have a query that uses an Indexed View and runs in 8 seconds if I run
> >> > it
> >> > from QA, but RS takes 30 minutes. All indications from running
> >> > Profiler
> >> > shows that RS is indeed using the indexed view.
> >> >
> >> > Any thoughts of what to check would be appreciated.
> >> >
> >> > Thanks
> >> >
> >>
> >>
> >>
>
>|||Very odd. How many fields are being returned? Usually with RS being slow it
is because the resultset is large. This is not the case for you. In this
case I would expect the performance to be the same as with the data tab ( or
at most a second or two longer).
This is a long shot but just in case you are seeing an issue with the
development environment, try deploying it and seeing how long it takes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jace" <Jace@.discussions.microsoft.com> wrote in message
news:D8B81128-ABF4-4C50-A639-E6C86CD16BBD@.microsoft.com...
> The row count is 18, but it is summarizing about 100 million records using
> an
> indexed view. I am using report parameters to pass to the query to filter
> the results from within the Query statement. Basically a date range
> selection, along with a history type. I did try hard coding some values
> within the query statement to see if it was faster, but the results were
> the
> same.
>
> "Bruce L-C [MVP]" wrote:
>> How many rows are being returned? Are you using any filters?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Jace" <Jace@.discussions.microsoft.com> wrote in message
>> news:ED4E90BD-7079-4E2A-8DB2-A783AE0BCC47@.microsoft.com...
>> > Yes, I tried using a stored Proc and that didnt improve things.
>> > Interestingly with a stored proc it seems to not even bother caching
>> > the
>> > results, so that if you repreview it takes another 30 minutes to see
>> > the
>> > 8sec
>> > query. I did notice it was passing the stored proc to another stored
>> > proc
>> > (sp_procedure_params_rowset) to get the rowset values, but I didn't see
>> > any
>> > parameters passed. I'm not sure if this could be causing the huge
>> > delay
>> > or
>> > not.
>> >
>> > But then again, even when I was using a text query straight from RS, I
>> > gave
>> > it a direct index hint and if I previewed the data in the Data tab it
>> > ran
>> > in
>> > 8 secs, but if I previewed the report, it took 30 minutes.
>> >
>> > Thanks
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Can you use a stored procedure? There have been instances where the
>> >> query
>> >> used in Reporting Services did not use the same query plan as would be
>> >> used
>> >> if the query was within query analyzer. One query plan used a
>> >> particular
>> >> index and the other did not. Hence the differences in performance. If
>> >> you
>> >> use a stored procedure you would be guaranteed that regardless of
>> >> where
>> >> invoked (query analyzer or RS) they would use the same query plan.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >> "Jace" <Jace@.discussions.microsoft.com> wrote in message
>> >> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > Has anyone had any problems with running queries from Reporting
>> >> > Services
>> >> > that perform 300x slower than from other sources? Any Ideas of what
>> >> > may
>> >> > cause this?
>> >> >
>> >> > I have a query that uses an Indexed View and runs in 8 seconds if I
>> >> > run
>> >> > it
>> >> > from QA, but RS takes 30 minutes. All indications from running
>> >> > Profiler
>> >> > shows that RS is indeed using the indexed view.
>> >> >
>> >> > Any thoughts of what to check would be appreciated.
>> >> >
>> >> > Thanks
>> >> >
>> >>
>> >>
>> >>
>>

Query Notifications/ Service Broker problem

Hey , I encountered a problem using SqlDependency and notification services.

I’ve created a database with tools from c# Express 2005 and attached it to the sql server express 2005. And it turned out that ‘dependency_OnChange’ in my application is triggered each time I execute the command.ExecuteReader(); and it shouldn’t, because the command is just the select statement of columns and rows for which I wait to be modified.

SqlDependency dependency = new SqlDependency(command);

dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

command.ExecuteReader();//when the program gets here dependency_OnChange is called

So I Used the example from here :

http://msdn2.microsoft.com/en-us/library/a52dhwx7.aspx

and with AdventureWorks it behaves as it should , but when I change the connection string and select statement in the example to work with MyDatabase, the problem appears again.

So there is something wrong with MyDatabase ?

The select statement I used was “select Name, Pass from Users where Name like ‘L%’”;

So theoretically this should work but it does not and I end up in an infinite loop with

dependency_OnChange called cyclically …

I’d be grateful for any advice !

What are the dependency_OnChange paramater values passed in when the callback is notified?

I suspect the SqlNotificationEventArgs argument has a value of 'Subscribe' for the notification type, indicating an error in setting up the notification. If the statement you included is the exact statement you'd tried, then the problem is that your query is not respecting the Query Notifications restrictions. The query select Name, Pass from Users where Name like ‘L%’ is not properly schema bound, the Users table name should be bound with a schema name (e.g. dbo.Users).

HTH,
~ Remus