Can MSDE or SQL Server send an active message to the client?

when a new record is inserted into one of the table?
Or, we should maintain the messaging by ourselves?
Thanks.
hi,
zhaounknown wrote:
> when a new record is inserted into one of the table?
> Or, we should maintain the messaging by ourselves?
> Thanks.
actually this is a very bad practice as you'd overhelm the server
activities..
what kind of message would you like to send? over the whole network?
you can perhaps implement some NET SEND in insert/update/delete trigger, but
again, this is very poor..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||In addition to what Andrea mentioned, take a look at the SqlDependency class
in VS.NET2005/SQL Server 2005. You can use that with SQL Express (the MSDE
replacement). I suspect it's just what you're looking for.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3b036cF6fh560U1@.individual.net...
> hi,
> zhaounknown wrote:
> actually this is a very bad practice as you'd overhelm the server
> activities..
> what kind of message would you like to send? over the whole network?
> you can perhaps implement some NET SEND in insert/update/delete trigger,
> but
> again, this is very poor..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

can I use the value of field in last record?

I handle a output by a function with three parameters

the first is a value of Field [a1] in current record,

the second is value of Field [a1] in last record,

the third is value of Field [a1] in next record,

is it possible in RS2005?

Yes you can use them in a given scope (table/list):

Fields!a1.Value

Last(Fields!a1.Value, "Dataset1")

Fields!a1.Value(RowNumber("Dataset1")+1)

Shyam

Can I query XML data in SQL Server 2005 and get aggregate information? If so, how?

Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
say, an XML order per record (unshredded to extract the original data )
and then querying the XML to get aggregate information on individual
orders (e.g. sum of cost of all items in order) and then further
aggregating the information to get, say, quarterly results (e.g. sum of
cost of all items ordered from January 2005 to March 2005)? If the
answer to this is yes, and I don't expect it to be, how is this done
(i.e. can someone point me to information on how I could implement such
a thing)? Believe it or not, we can only get this information in XML
format. We would have to pay someone to design a relational database to
take this information, write a script to unshred the data from the XML
and populate the database tables, and then write queries to extract
aggregate data from the database.
You can store the data as XML by using the new xml data type. This data
type has various methods that you can use to query the data. The methods
include some XQuery based functions to extract information from an xml
column or variable (the "query", "value", and "exist" methods), which might
help if your aggergated data also needs to be in XML format. Alternatively,
theres a "nodes" method that you can use to extract a rowset from an xml
value (or you could use OPENXML). You can even create indexes on the xml
columns to improve XQuery performance.
All of this is described in SQL Server Books Online, and
http://msdn.microsoft.com/sql/learn/...l/default.aspx is a good place to
start.
-
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132716530.406874.269410@.f14g2000cwb.googlegr oups.com...
> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data )
> and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order) and then further
> aggregating the information to get, say, quarterly results (e.g. sum of
> cost of all items ordered from January 2005 to March 2005)? If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement such
> a thing)? Believe it or not, we can only get this information in XML
> format. We would have to pay someone to design a relational database to
> take this information, write a script to unshred the data from the XML
> and populate the database tables, and then write queries to extract
> aggregate data from the database.
>
|||"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in
news:1132716530.406874.269410@.f14g2000cwb.googlegr oups.com:

> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data
> ) and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order)
If the XML you store in a column in a row have order items nodes, then
sure no prob. You use the built-in xquery functionality on the xml
datatype to do these aggregations.

>and then further
> aggregating the information to get, say, quarterly results (e.g. sum
> of cost of all items ordered from January 2005 to March 2005)?
I assume you with this means you want to do querying/aggregation over
several rows? If so, this is not supported "out of the box", SQL Server
does not support composition on the xml data-type. However it can be
done by selecting in all the data into an xml variable and then do the
query/aggregation against that.

>If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement
> such a thing)?
Read Books Online about the xml data type and XQuery.
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
|||Niels and Graeme, I sincerely thank you for the trouble you have gone
to in addressing my issue and pointing me in the right direction.
|||To clarify, you can do the both aggregations with methods on XML data type.
To calculate prices of all orders when an order is an XML instance with
LineItem-s as XML elements you can write something like:
SELECT
sum(
xml_order.value('sum(/Order/LineItem/@.Price)','FLOAT')
)
FROM Orders
The inner XQuery "sum" would aggregate all price attribute values in an XML
instance and the outer T-SQL "sum" will aggregate across multiple XML
instances.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132796074.018648.122550@.o13g2000cwo.googlegr oups.com...
> Niels and Graeme, I sincerely thank you for the trouble you have gone
> to in addressing my issue and pointing me in the right direction.
>
|||Hi Eugene,
Sorry to take so long to get back to you (I had to finalise and finally
finish a VBA for Excel project I've been working on for some months and
that's done now). This is my next project.
Thank you for feedback on the nested sums in the Select statement. I
expect my next project will be to develop a(n) SQL Server 2005 database
for storing XML orders which can be queried to produce a known set of
management reports. Consequently, I expect to be a regular visitor to
this group with my newbie questions.
Thank you again for your help.
Terry R.

Can I query XML data in SQL Server 2005 and get aggregate information? If so, how?

Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
say, an XML order per record (unshredded to extract the original data )
and then querying the XML to get aggregate information on individual
orders (e.g. sum of cost of all items in order) and then further
aggregating the information to get, say, quarterly results (e.g. sum of
cost of all items ordered from January 2005 to March 2005)? If the
answer to this is yes, and I don't expect it to be, how is this done
(i.e. can someone point me to information on how I could implement such
a thing)? Believe it or not, we can only get this information in XML
format. We would have to pay someone to design a relational database to
take this information, write a script to unshred the data from the XML
and populate the database tables, and then write queries to extract
aggregate data from the database.You can store the data as XML by using the new xml data type. This data
type has various methods that you can use to query the data. The methods
include some XQuery based functions to extract information from an xml
column or variable (the "query", "value", and "exist" methods), which might
help if your aggergated data also needs to be in XML format. Alternatively,
theres a "nodes" method that you can use to extract a rowset from an xml
value (or you could use OPENXML). You can even create indexes on the xml
columns to improve XQuery performance.
All of this is described in SQL Server Books Online, and
http://msdn.microsoft.com/sql/learn...ml/default.aspx is a good place to
start.
-
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132716530.406874.269410@.f14g2000cwb.googlegroups.com...
> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data )
> and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order) and then further
> aggregating the information to get, say, quarterly results (e.g. sum of
> cost of all items ordered from January 2005 to March 2005)? If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement such
> a thing)? Believe it or not, we can only get this information in XML
> format. We would have to pay someone to design a relational database to
> take this information, write a script to unshred the data from the XML
> and populate the database tables, and then write queries to extract
> aggregate data from the database.
>|||"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in
news:1132716530.406874.269410@.f14g2000cwb.googlegroups.com:

> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data
> ) and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order)
If the XML you store in a column in a row have order items nodes, then
sure no prob. You use the built-in xquery functionality on the xml
datatype to do these aggregations.

>and then further
> aggregating the information to get, say, quarterly results (e.g. sum
> of cost of all items ordered from January 2005 to March 2005)?
I assume you with this means you want to do querying/aggregation over
several rows? If so, this is not supported "out of the box", SQL Server
does not support composition on the xml data-type. However it can be
done by selecting in all the data into an xml variable and then do the
query/aggregation against that.

>If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement
> such a thing)?
Read Books Online about the xml data type and XQuery.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels and Graeme, I sincerely thank you for the trouble you have gone
to in addressing my issue and pointing me in the right direction.|||To clarify, you can do the both aggregations with methods on XML data type.
To calculate prices of all orders when an order is an XML instance with
LineItem-s as XML elements you can write something like:
SELECT
sum(
xml_order.value('sum(/Order/LineItem/@.Price)','FLOAT')
)
FROM Orders
The inner XQuery "sum" would aggregate all price attribute values in an XML
instance and the outer T-SQL "sum" will aggregate across multiple XML
instances.
Best regards,
Eugene
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132796074.018648.122550@.o13g2000cwo.googlegroups.com...
> Niels and Graeme, I sincerely thank you for the trouble you have gone
> to in addressing my issue and pointing me in the right direction.
>|||Hi Eugene,
Sorry to take so long to get back to you (I had to finalise and finally
finish a VBA for Excel project I've been working on for some months and
that's done now). This is my next project.
Thank you for feedback on the nested sums in the Select statement. I
expect my next project will be to develop a(n) SQL Server 2005 database
for storing XML orders which can be queried to produce a known set of
management reports. Consequently, I expect to be a regular visitor to
this group with my newbie questions.
Thank you again for your help.
Terry R.