Does SQL Server 2005 Support the List Statement?

We have a comprehensive windows based application which uses a database othe
r
than SQL Server. We have been considering moving our application to SQL
server but have been stymied by limitations in the ANS SQL implementation as
well has hidden restrictions in Decalarative Referential integrity.
One statement we use a lot in reporting is the ANS List statment (a scalar
aggregate function for strings which can return a set of results in a
appropriate delimited string).
Does anyone know if the List statement is implemented fully in SQL Server
2005.
If not, we would be interested in what techniques others use to get around
this limitation.
Thanks> One statement we use a lot in reporting is the ANS List statment (a scalar
> aggregate function for strings which can return a set of results in a
> appropriate delimited string).
> Does anyone know if the List statement is implemented fully in SQL Server
> 2005.
NO, it is not.

> If not, we would be interested in what techniques others use to get around
> this limitation.
http://www.aspfaq.com/2529|||"Glenn" wrote:

> We have a comprehensive windows based application which uses a database ot
her
> than SQL Server. We have been considering moving our application to SQL
> server but have been stymied by limitations in the ANS SQL implementation
as
> well has hidden restrictions in Decalarative Referential integrity.
> One statement we use a lot in reporting is the ANS List statment (a scalar
> aggregate function for strings which can return a set of results in a
> appropriate delimited string).
> Does anyone know if the List statement is implemented fully in SQL Server
> 2005.
> If not, we would be interested in what techniques others use to get around
> this limitation.
> Thanks
When you say "ANS SQL" do you mean ANSI SQL? If so, I think you are
mistaken. The LIST "aggregate" has never been part of standard SQL. It is a
proprietary feature in DB2 I believe (and possibly others).
There are a number of logical and practical problems with the concept of a
"string concatenation aggregate". The problems are to do with the fact that
concatentation implies order, which implies sorting. Furthermore, determinis
m
requires unique sorting. This means that "LIST" is A) potentially expensive
on performance B) difficult to ensure reliable results in queries C) totally
contrary to the way other SQL queries and aggregates work.
The ANSI response to "ordered" functions is the Windowed functions and these
*are* supported by SQL Server 2005. Theoretically you can kludge your own
string aggregate using standard SQL, provided you can set some reasdonable
upper limit to the number of items to be concatenated. Allternatively there
are non-standard workarounds in TSQL as there apparently are in your current
database.
Rather than try to support such a potential kludge in the database I suggest
you look at doing this client-side. In ADO you have the GetString method to
serve that purpose.
David Portas
SQL Server MVP
--|||Thanks very much for your response. This is a bit disappointing. We had so
hoped they would address this item as it is so commonly used in other SQL
implementations.
I took a look at the faq sample - and though helpful - and it looks like it
might add quite a lot of complexity to the SQL in many of our queries. I
think it is unfortunate that Microsoft continues to add many new features to
its SQL implementation, yet seems to have long ignored the ANS standard SQL.
It makes it difficult for developers and ISV's to move their applications
from other vendors to the M/S platform.
Glenn
"Aaron Bertrand [SQL Server MVP]" wrote:

> NO, it is not.
>
> http://www.aspfaq.com/2529
>
>|||> yet seems to have long ignored the ANS standard SQL.
Why do you think LIST is ANSI standard?
A|||Hi, this is pretty helpful, although it was my understanding that the SELECT
LIST command was ANSI standard although there were various vendor extensions
to the feature added for control of concatenation, separators, and order.
We are a bit spoiled by the iAnywhere implementation which works very
efficiently for our application and allows for ordering of the result (an
obvious Vendor extention). We have also tried doing this on the client side
,
and it was much-much slower.
Thanks for your help, looks like we are going to need to engage a SQL port
specialist.
BTW - We utilize quite a few Scalar Subqueries in our application, do you
feel that using these on SQL Server 2005 will introduce any special
performance or implementation problems.
Glenn
"David Portas" wrote:

> "Glenn" wrote:
>
> When you say "ANS SQL" do you mean ANSI SQL? If so, I think you are
> mistaken. The LIST "aggregate" has never been part of standard SQL. It is
a
> proprietary feature in DB2 I believe (and possibly others).
> There are a number of logical and practical problems with the concept of a
> "string concatenation aggregate". The problems are to do with the fact tha
t
> concatentation implies order, which implies sorting. Furthermore, determin
ism
> requires unique sorting. This means that "LIST" is A) potentially expensiv
e
> on performance B) difficult to ensure reliable results in queries C) total
ly
> contrary to the way other SQL queries and aggregates work.
> The ANSI response to "ordered" functions is the Windowed functions and the
se
> *are* supported by SQL Server 2005. Theoretically you can kludge your own
> string aggregate using standard SQL, provided you can set some reasdonable
> upper limit to the number of items to be concatenated. Allternatively ther
e
> are non-standard workarounds in TSQL as there apparently are in your curre
nt
> database.
> Rather than try to support such a potential kludge in the database I sugge
st
> you look at doing this client-side. In ADO you have the GetString method t
o
> serve that purpose.
> --
> David Portas
> SQL Server MVP
> --
>|||In our database documentation it was descibed as ANS 92/99 with optional
vendor extensions.
--
Glenn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why do you think LIST is ANSI standard?
> A
>
>|||LIST isn't ANSI Standard and never has been - I just checked the
standards docs. The reason is probably related to some of the logical
problems I mentioned. It just doesn't make sense as an "aggregate"
function.
I think SQL Server is actually reasonably good on standard SQL
compliance, and it goes further with 2005 - the SQL1999 OLAP functions
for example -although it certainly could do better.
David Portas
SQL Server MVP
--|||I do not wish to offend anyone, but this is - after all - the 21st century:
consider using XML (e.g. in SQL 2005) to replace the less useful LIST
function, if you really need to move sets of data about on the server.
ML|||Can you give me link to the ANSI SQL specification documents?
Thanks
--
Glenn
"David Portas" wrote:

> LIST isn't ANSI Standard and never has been - I just checked the
> standards docs. The reason is probably related to some of the logical
> problems I mentioned. It just doesn't make sense as an "aggregate"
> function.
> I think SQL Server is actually reasonably good on standard SQL
> compliance, and it goes further with 2005 - the SQL1999 OLAP functions
> for example -although it certainly could do better.
> --
> David Portas
> SQL Server MVP
> --
>

Does Computed Column Update on Select General Question

Okay, newb question alert!!!

I created a computer column that is based on the difference between the column start_date and getdate().

Does the computed column only update when you update the column or does it change when you select it also?

Nevermind... Sorry to crowd the forums with stupid questions... I gave it another two seconds worth of thinking and realized that if I changed it to seconds just for the sake of testing that I could see it updating (It was at months before and I didn't want to wait a month to find out)

Sorry again about the wasted post!

Does anyone know of a web based SQL Query tool?

Hi

I want to be able t give my users access to our SQL server database via a eb application published o our web server. I have found one freeware tool but it only supports SQL Server 2000 and the developer doesn't returns any emails.

Has anyone come across a web ui SQL query tool either freeware or one that can be purhcase

thanks for your help

Marcus

Do you just want to have a tool sending out a query from a textbox and getting the results back in a grid or something ? Or do you want tome graphical designer for your users ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

search for

SQL Server Web Data Administrator

on microsoft website

|||

Here is the link to SQL Server Web Data Administrator

http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en

Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

|||ASP Enterprise Manager http://sourceforge.net/projects/asp-ent-man/

Does anyone know of a tool that allows a SQL Server database to be

Does anyone know of a web based tool that allows a SQL Server database
to be
adminstered from the Internet? (in php)Does anyone know of a web based tool that allows a SQL Server database

Quote:

Originally Posted by

to be adminstered from the Internet? (in php)

It's not in PHP but you can have a look at
myLittleAdmin for SQL Server 2005
and myLittleAdmin for SQL Server 2000
on http://www.mylittletools.net

Both have a free lite edition

Best regards

|||Not sure if it's sufficient for your purpose but have a look at MS SQL
Server Web Data Administrator
http://www.microsoft.com/downloads/...&displaylang=en

Markus

Does any version supprt embedding EMF (vector based) images into Reports?

Hi all,
Does any version of Reporting Services support embedding vector based images
(such as EMF's, or WMF's) into Reports?
RobHello Robert,
Based on my scope, SQL 2000/2005 reporting services do not support embed
EMF files in a report though you could render a report to EMF type files by
using Image Rendering Extension.
You may want to use image converter to convert EMF/WMF files to JPG etc so
that you could embed them into a report. Also, you could send your feedback
by clicking Send feddback button in toolbar in SQL 2005 Books Online.
All product or service suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Robert Hooker" <rhooker@.newsgroup.nospam>
>Subject: Does any version supprt embedding EMF (vector based) images into
Reports?
>Date: Tue, 13 Dec 2005 10:22:35 -0700
>Lines: 8
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
>Message-ID: <eqaLQnAAGHA.344@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: mail.optiframe.com 12.155.152.130
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:64952
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hi all,
>Does any version of Reporting Services support embedding vector based
images
>(such as EMF's, or WMF's) into Reports?
>Rob
>
>

Do we really need query based optimisation?

Hello,

I was playing with the Query based optimisation in SQL2005.

- it is not available by default, you have to enable the query log in the server properties

- it generates 1 out of 10 queries log entry

- 1 out of 10 query is still a large number of log entries if you have many users and many cubes, 1 out of 10 does not filter any redundant queries.

- I also tried to use this mechanism to create an usage audit log, Log table becomes bloated very quickly.

- tried to optimise the partitions based on this mechanism and did not see any performance boost.

I wonder in which cases this option really makes a difference.

I went through all the dimensions design best practices I could find and I finally came to the conclusion that my front-end tool of predilection (Excel 2003) is the problem.

Not sure if anyone did a test on speed boost of aggregations optimization when using the new Excel 2007 as a front end but even without such optimization, Excel 2007 executes in miliseconds what takes ages in Excel 2003 no matter what optimization.

What is the real advantage of using query based optimisations in ssas 2005?

Since this option is disabled by default, I doubt that it would go any farther than an academic type of optimisation. With the right client, 20 ms un-optimized vs 12 ms optimized would certainly not make a difference in the eyes of an end user.

Also, you tend to loose the optimizations each time you change your cube structure and then have to wait a few weeks untill you have a sample of queries large enough.

Any thoughts?

Philippe

I agree with your doubts regarding query based optimization. In AS2000 I have seen that it delivers improvements. Some MS people in this newsgroup have hinted that we will see improvements in SP2 for SSAS2005.

Regards

Thomas Ivarsson

|||

Dont agree with you guys a bit. Usage based optimization is very useful. Especially in AS 2005.

First to answer some questions:

>>- it is not available by default, you have to enable the query log in the server properties

Yes it is not avaliable by default. Here is whitepaper explaning query log setup and options controlling it: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/config_ssas_querylog.mspx

>>- it generates 1 out of 10 queries log entry

Take a look at the whitepaper and you'll see the QueryLogSampling property that defiles the frequency of sampling

>>- 1 out of 10 query is still a large number of log entries if you have many users and many cubes, 1 out of 10 does not filter any redundant queries.

The way you should think of Usage Based Optimization is; You should let it run for awhile and then used it to design aggregatins. After that if you satisfied with UBO, you can stop it.

>>- I also tried to use this mechanism to create an usage audit log, Log table becomes bloated very quickly.

Not sure what you refer to here

>>- tried to optimise the partitions based on this mechanism and did not see any performance boost.

This could be the indication that you dont really need usage based optimization. If your query perofrmance problem could be solved by moving to use another tool, this is good indication, you probably dont need it.

With lots of attributes and poorly designed attribute relationships your aggregations are going to be of little help. Put decent size of data into your cube and see perofrmance going down. Collecting stats and than later designig aggregations for these queries is probably the only way to go in this situation.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thank you Edward for the clarification.

By Usage audit I meant I did set the log sampling to 1 out of 1 so I can query the OlapQueryLog table to report on cube usage.

That is a management requirement to see who uses which cube and how frequently.

The log mechanism is not designed to do this however it is the only way I have found to provide this usage information.

If there is a better way, I would be glad to use it. All I need is UserID, database name, Cube name, Date. If I could have the query itself also, that could be a nice thing. I do not need the subcube code.

These days of SOX audits makes it very interesting to be able to tell who is actually using your systems beyond simply providing a list of authorized users.

Regards,
Philippe|||

For audit purposes you can create a server-side trace. Try to see how in SQL Profiler you can create a trace that writes to a file. This way you should get UserID and all other properties logged.

You only need few events selected for this trace for instance: Query Begin, Command Begin, Audit Login, Audit Logout

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.