New System

I am setting up a web based survey for the personnel in my company and would
like to use SQL. Is this a practical applicaiton for SQL 2000?It doesn't hurt !
It depends upon the size and complexity of Survey, if you want to know if
you are really leveraging SQL 2K power and features.
HTH
Satish Balusa
Corillian Corp.
"Heimann" <gheimann@.heimannonline.com> wrote in message
news:238DE8DB-8F5C-46E0-8941-788B5A3FB8C1@.microsoft.com...

quote:

---

> I am setting up a web based survey for the personnel in my company and

---

would like to use SQL. Is this a practical applicaiton for SQL 2000?

New System

I am setting up a web based survey for the personnel in my company and would like to use SQL. Is this a practical applicaiton for SQL 2000?It doesn't hurt !
It depends upon the size and complexity of Survey, if you want to know if
you are really leveraging SQL 2K power and features.
--
HTH
Satish Balusa
Corillian Corp.
"Heimann" <gheimann@.heimannonline.com> wrote in message
news:238DE8DB-8F5C-46E0-8941-788B5A3FB8C1@.microsoft.com...
> I am setting up a web based survey for the personnel in my company and
would like to use SQL. Is this a practical applicaiton for SQL 2000?sql

New SP vs parameters

I have a table of 25-30 million properties, from which are retrieved
~150 centered on a point, based on the parameters -- coordinates,
property type and date of transaction. There's an SP (also implemented
as a function returning a table) to return the desired records.

This look-up takes the most time in the C# program that calls it, and
should be optimized. It was suggested that instead of having an SP on
the server, each time the program should create an SP that is the same,
however without any parameters -- with the values hard-coded. Then
execute it, and drop it. This way, the execution plan will be
customized for the specific parameters. I tried it and it turns out
the suggested method is noticeably faster, even compared to recompiling
an SP every time. I was wondering if there is a way to get equivalent
performance out of an SP or UDF that has parameters, or is this
approach necessarily going to be less optimized than hard-coded
non-parameters.

Thanks,
JimHi Jim,
Okay.
Does the SP takes a long time to run as stand alone (executing it on
the server without calling from C#)?
Is it too much of a trouble to post the whole code? Can you time the SP
and what is it?
As a quick solution maybe you can modify the SP to save the result into
a table and return some number instead (as success). Then the C# just
access that table and once done drop it at the end. You can have a
recompile option when creating the stored procedure but might worth as
well if you can increase performance by minor fixes.

jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

I have a table of 25-30 million properties, from which are retrieved
~150 centered on a point, based on the parameters -- coordinates,
property type and date of transaction. There's an SP (also implemented
as a function returning a table) to return the desired records.
>
This look-up takes the most time in the C# program that calls it, and
should be optimized. It was suggested that instead of having an SP on
the server, each time the program should create an SP that is the same,
however without any parameters -- with the values hard-coded. Then
execute it, and drop it. This way, the execution plan will be
customized for the specific parameters. I tried it and it turns out
the suggested method is noticeably faster, even compared to recompiling
an SP every time. I was wondering if there is a way to get equivalent
performance out of an SP or UDF that has parameters, or is this
approach necessarily going to be less optimized than hard-coded
non-parameters.
>
Thanks,
Jim

|||Your answer is: "it depends."

Instead of re-creating the stored proc, you can use the WITH RECOMPILE
option, which will probably do what you want:

CREATE PROC dbo.foo WITH RECOMPILE AS
select 'hello world';

http://msdn2.microsoft.com/en-us/li...59(SQL.80).aspx
-Dave

jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

I have a table of 25-30 million properties, from which are retrieved
~150 centered on a point, based on the parameters -- coordinates,
property type and date of transaction. There's an SP (also implemented
as a function returning a table) to return the desired records.
>
This look-up takes the most time in the C# program that calls it, and
should be optimized. It was suggested that instead of having an SP on
the server, each time the program should create an SP that is the same,
however without any parameters -- with the values hard-coded. Then
execute it, and drop it. This way, the execution plan will be
customized for the specific parameters. I tried it and it turns out
the suggested method is noticeably faster, even compared to recompiling
an SP every time. I was wondering if there is a way to get equivalent
performance out of an SP or UDF that has parameters, or is this
approach necessarily going to be less optimized than hard-coded
non-parameters.
>
Thanks,
Jim

New Reporting Control

Does the new report control for vs 2005 utilize SOAP or the URL method
to access the report server?
I need to utilize a pure SOAP based solution to get reports from the
report server. Just curious if the new solution is used that way or if
rolling my own is the only option.
ThanksThe new controls use soap (web services). They are a much more complete
solution that the old way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vivaldi" <ZachMattson@.gmail.com> wrote in message
news:1131634098.219113.321000@.g49g2000cwa.googlegroups.com...
> Does the new report control for vs 2005 utilize SOAP or the URL method
> to access the report server?
> I need to utilize a pure SOAP based solution to get reports from the
> report server. Just curious if the new solution is used that way or if
> rolling my own is the only option.
> Thanks
>

New query based on SOME fields returned in another query

I need to have two queries, the second query uses some information from the first query but the second query's results is not a subset of the first query.

this is my first query

select name, date, company, valid
from tbl1, tbl2, tbl3
where valid=0

Then I need to take the person's name to do another query, but I don't need to look in the first set of results to get what I need. It is a brand new query just with the name as the parameter.

select name
from tbl4
where name = @.name

Right now I have the second query getting it's parameter from the first query in RS. The two queries are two different data sets BUT my second query is limited by the results from the first query. How do I get the second query to be it's own query not based on the first but get a parameter from the first query.

I know this is all very confusing. I apologize. I am trying to explain fully.

Please help, I am new to RS and quite new in SQL

Well, I think I solved my problem. I needed to create a sub report.

New Measure Based on Attribute Values

I'd like to add a new measure to my cube which is based upon values of an attribute. For example I'd like the measures to always apply the filter [Geography].[Country].&[Australia] applied even if the Geography attribute isn't included in the SELECT.

I've tried:

CREATEMEMBERCURRENTCUBE.[measures].[Oz Internet]

AS

AGGREGATE

(

EXISTING

(

{

[Geography].[Country].&[Australia]

}

*

[Sales Channel].[Sales Channel].&[Internet]

)

),

but this doesn't return anything. Is there a way to do this?

Here is an example where I limit a specific measure by a dimension member:

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])

select

{[Measures].[Reseller Sales Amount],[Measures].[Reseller Sales Amount Bikes]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

Bryan|||Thanks for that. What if I needed it to work for more than one category eg Bikes and Accessories? I'm sure it's going to be simple but I can't get the syntax right.|||

One way to pull out the individual measure values and add them. That's seen in the first calculated member.

In the next calculated member, you can see I build a set of members, get the measure against this set and then aggregate. You can build that first set many different ways.

Good luck,
Bryan

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes+] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])+

([Measures].[Reseller Sales Amount],[Product].[Category].[Accessories])

member [Measures].[Reseller Sales Amount Bikes+ Too] as

AGGREGATE({[Product].[Category].[Bikes],[Product].[Category].[Accessories]},[Measures].[Reseller Sales Amount])

select

{[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes+],

[Measures].[Reseller Sales Amount Bikes+ Too]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

|||

That's very helpful. Thanks Bryan.

Mark

New Measure Based on Attribute Values

I'd like to add a new measure to my cube which is based upon values of an attribute. For example I'd like the measures to always apply the filter [Geography].[Country].&[Australia] applied even if the Geography attribute isn't included in the SELECT.

I've tried:

CREATEMEMBERCURRENTCUBE.[measures].[Oz Internet]

AS

AGGREGATE

(

EXISTING

(

{

[Geography].[Country].&[Australia]

}

*

[Sales Channel].[Sales Channel].&[Internet]

)

),

but this doesn't return anything. Is there a way to do this?

Here is an example where I limit a specific measure by a dimension member:

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])

select

{[Measures].[Reseller Sales Amount],[Measures].[Reseller Sales Amount Bikes]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

Bryan|||Thanks for that. What if I needed it to work for more than one category eg Bikes and Accessories? I'm sure it's going to be simple but I can't get the syntax right.|||

One way to pull out the individual measure values and add them. That's seen in the first calculated member.

In the next calculated member, you can see I build a set of members, get the measure against this set and then aggregate. You can build that first set many different ways.

Good luck,
Bryan

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes+] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])+

([Measures].[Reseller Sales Amount],[Product].[Category].[Accessories])

member [Measures].[Reseller Sales Amount Bikes+ Too] as

AGGREGATE({[Product].[Category].[Bikes],[Product].[Category].[Accessories]},[Measures].[Reseller Sales Amount])

select

{[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes+],

[Measures].[Reseller Sales Amount Bikes+ Too]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

|||

That's very helpful. Thanks Bryan.

Mark