How to simplify XML Query? (SQL2000)

Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
schema that I want to produce is quite large (with a reasonable amount of
repitition) this seems unnecessarily complicated. A simplified version of the
schema I want to produce is:
<ArrayOfEntity>
<Entity Key=123>
<Name>...</Name>
<Prop1>...</Prop1>
<Prop2>...</Prop2>
<Prop3>...</Prop3>
<Node1 Key=32>text</Node1>
<Node2 Key=43>text</Node2>
<Node3 Key=54>text</Node3>
<Node4 Key=65>text</Node4>
</Entity>
<Entity Key=234>
...
</Entity>
</ArrayOfEntity>
The thing is, there are quite a few of these child "Node"s, which will only
contain a key and text value. There are also more complicated child nodes
(not shown here).
I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
because I could then write a function to generate the child node. But this
does not seem to allow me to specify the Node Key's!! :(
Is there any way to write a reasonably simple query to get the above schema'
Thanks
GregHi Greg
You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
data in this format. A different approach you be to apply a transform after
the data has been extracted. You could use XSLT to do this, possibly use the
document()
function to include and export the data in fragments.
http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&
John
"Greg Bacchus" wrote:
> Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
> just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
> schema that I want to produce is quite large (with a reasonable amount of
> repitition) this seems unnecessarily complicated. A simplified version of the
> schema I want to produce is:
> <ArrayOfEntity>
> <Entity Key=123>
> <Name>...</Name>
> <Prop1>...</Prop1>
> <Prop2>...</Prop2>
> <Prop3>...</Prop3>
> <Node1 Key=32>text</Node1>
> <Node2 Key=43>text</Node2>
> <Node3 Key=54>text</Node3>
> <Node4 Key=65>text</Node4>
> </Entity>
> <Entity Key=234>
> ...
> </Entity>
> </ArrayOfEntity>
> The thing is, there are quite a few of these child "Node"s, which will only
> contain a key and text value. There are also more complicated child nodes
> (not shown here).
> I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
> because I could then write a function to generate the child node. But this
> does not seem to allow me to specify the Node Key's!! :(
> Is there any way to write a reasonably simple query to get the above schema'
> Thanks
> Greg|||Thanks for your reply John.
Can you do XSL Transforms in SQL 2000?
Greg
"John Bell" wrote:
> Hi Greg
> You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
> data in this format. A different approach you be to apply a transform after
> the data has been extracted. You could use XSLT to do this, possibly use the
> document()
> function to include and export the data in fragments.
> http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&
> John
> "Greg Bacchus" wrote:
> > Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
> > just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
> > schema that I want to produce is quite large (with a reasonable amount of
> > repitition) this seems unnecessarily complicated. A simplified version of the
> > schema I want to produce is:
> >
> > <ArrayOfEntity>
> > <Entity Key=123>
> > <Name>...</Name>
> > <Prop1>...</Prop1>
> > <Prop2>...</Prop2>
> > <Prop3>...</Prop3>
> > <Node1 Key=32>text</Node1>
> > <Node2 Key=43>text</Node2>
> > <Node3 Key=54>text</Node3>
> > <Node4 Key=65>text</Node4>
> > </Entity>
> > <Entity Key=234>
> > ...
> > </Entity>
> > </ArrayOfEntity>
> >
> > The thing is, there are quite a few of these child "Node"s, which will only
> > contain a key and text value. There are also more complicated child nodes
> > (not shown here).
> >
> > I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
> > because I could then write a function to generate the child node. But this
> > does not seem to allow me to specify the Node Key's!! :(
> >
> > Is there any way to write a reasonably simple query to get the above schema'
> > Thanks
> > Greg|||Hi Greg
It should be possible to do this using the SP_OA... procedures but really I
would expect it to be done externally.
You may want to look at SQL2005 as this could all be done a lot easier using
.NET
John
"Greg Bacchus" wrote:
> Thanks for your reply John.
> Can you do XSL Transforms in SQL 2000?
> Greg
> "John Bell" wrote:
> > Hi Greg
> >
> > You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
> > data in this format. A different approach you be to apply a transform after
> > the data has been extracted. You could use XSLT to do this, possibly use the
> > document()
> > function to include and export the data in fragments.
> >
> > http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&
> >
> > John
> >
> > "Greg Bacchus" wrote:
> >
> > > Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
> > > just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
> > > schema that I want to produce is quite large (with a reasonable amount of
> > > repitition) this seems unnecessarily complicated. A simplified version of the
> > > schema I want to produce is:
> > >
> > > <ArrayOfEntity>
> > > <Entity Key=123>
> > > <Name>...</Name>
> > > <Prop1>...</Prop1>
> > > <Prop2>...</Prop2>
> > > <Prop3>...</Prop3>
> > > <Node1 Key=32>text</Node1>
> > > <Node2 Key=43>text</Node2>
> > > <Node3 Key=54>text</Node3>
> > > <Node4 Key=65>text</Node4>
> > > </Entity>
> > > <Entity Key=234>
> > > ...
> > > </Entity>
> > > </ArrayOfEntity>
> > >
> > > The thing is, there are quite a few of these child "Node"s, which will only
> > > contain a key and text value. There are also more complicated child nodes
> > > (not shown here).
> > >
> > > I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
> > > because I could then write a function to generate the child node. But this
> > > does not seem to allow me to specify the Node Key's!! :(
> > >
> > > Is there any way to write a reasonably simple query to get the above schema'
> > > Thanks
> > > Greg

How to simplify XML Query? (SQL2000)

Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
schema that I want to produce is quite large (with a reasonable amount of
repitition) this seems unnecessarily complicated. A simplified version of the
schema I want to produce is:
<ArrayOfEntity>
<Entity Key=123>
<Name>...</Name>
<Prop1>...</Prop1>
<Prop2>...</Prop2>
<Prop3>...</Prop3>
<Node1 Key=32>text</Node1>
<Node2 Key=43>text</Node2>
<Node3 Key=54>text</Node3>
<Node4 Key=65>text</Node4>
</Entity>
<Entity Key=234>
...
</Entity>
</ArrayOfEntity>
The thing is, there are quite a few of these child "Node"s, which will only
contain a key and text value. There are also more complicated child nodes
(not shown here).
I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
because I could then write a function to generate the child node. But this
does not seem to allow me to specify the Node Key's!!
Is there any way to write a reasonably simple query to get the above schema?
Thanks
Greg
Hi Greg
You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
data in this format. A different approach you be to apply a transform after
the data has been extracted. You could use XSLT to do this, possibly use the
document()
function to include and export the data in fragments.
http://groups.google.com/group/micro...f8fbde6?hl=en&
John
"Greg Bacchus" wrote:

> Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
> just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
> schema that I want to produce is quite large (with a reasonable amount of
> repitition) this seems unnecessarily complicated. A simplified version of the
> schema I want to produce is:
> <ArrayOfEntity>
> <Entity Key=123>
> <Name>...</Name>
> <Prop1>...</Prop1>
> <Prop2>...</Prop2>
> <Prop3>...</Prop3>
> <Node1 Key=32>text</Node1>
> <Node2 Key=43>text</Node2>
> <Node3 Key=54>text</Node3>
> <Node4 Key=65>text</Node4>
> </Entity>
> <Entity Key=234>
> ...
> </Entity>
> </ArrayOfEntity>
> The thing is, there are quite a few of these child "Node"s, which will only
> contain a key and text value. There are also more complicated child nodes
> (not shown here).
> I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
> because I could then write a function to generate the child node. But this
> does not seem to allow me to specify the Node Key's!!
> Is there any way to write a reasonably simple query to get the above schema?
> Thanks
> Greg
|||Thanks for your reply John.
Can you do XSL Transforms in SQL 2000?
Greg
"John Bell" wrote:
[vbcol=seagreen]
> Hi Greg
> You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
> data in this format. A different approach you be to apply a transform after
> the data has been extracted. You could use XSLT to do this, possibly use the
> document()
> function to include and export the data in fragments.
> http://groups.google.com/group/micro...f8fbde6?hl=en&
> John
> "Greg Bacchus" wrote:
|||Hi Greg
It should be possible to do this using the SP_OA... procedures but really I
would expect it to be done externally.
You may want to look at SQL2005 as this could all be done a lot easier using
..NET
John
"Greg Bacchus" wrote:
[vbcol=seagreen]
> Thanks for your reply John.
> Can you do XSL Transforms in SQL 2000?
> Greg
> "John Bell" wrote:

How to simplify XML Query? (SQL2000)

Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
schema that I want to produce is quite large (with a reasonable amount of
repitition) this seems unnecessarily complicated. A simplified version of th
e
schema I want to produce is:
<ArrayOfEntity>
<Entity Key=123>
<Name>...</Name>
<Prop1>...</Prop1>
<Prop2>...</Prop2>
<Prop3>...</Prop3>
<Node1 Key=32>text</Node1>
<Node2 Key=43>text</Node2>
<Node3 Key=54>text</Node3>
<Node4 Key=65>text</Node4>
</Entity>
<Entity Key=234>
..
</Entity>
</ArrayOfEntity>
The thing is, there are quite a few of these child "Node"s, which will only
contain a key and text value. There are also more complicated child nodes
(not shown here).
I was quite excited to find the [Element!Num!Attr!xml] explicit column n
ame,
because I could then write a function to generate the child node. But this
does not seem to allow me to specify the Node Key's!!
Is there any way to write a reasonably simple query to get the above schema?
?
Thanks
GregHi Greg
You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
data in this format. A different approach you be to apply a transform after
the data has been extracted. You could use XSLT to do this, possibly use the
document()
function to include and export the data in fragments.
[url]http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&[/u
rl]
John
"Greg Bacchus" wrote:

> Hi, I am trying to write a query in SQL 2000 to produce a slightly more th
an
> just basic xml schema. I can do the query using FOR XML EXPLICIT, but as t
he
> schema that I want to produce is quite large (with a reasonable amount of
> repitition) this seems unnecessarily complicated. A simplified version of
the
> schema I want to produce is:
> <ArrayOfEntity>
> <Entity Key=123>
> <Name>...</Name>
> <Prop1>...</Prop1>
> <Prop2>...</Prop2>
> <Prop3>...</Prop3>
> <Node1 Key=32>text</Node1>
> <Node2 Key=43>text</Node2>
> <Node3 Key=54>text</Node3>
> <Node4 Key=65>text</Node4>
> </Entity>
> <Entity Key=234>
> ...
> </Entity>
> </ArrayOfEntity>
> The thing is, there are quite a few of these child "Node"s, which will onl
y
> contain a key and text value. There are also more complicated child nodes
> (not shown here).
> I was quite excited to find the [Element!Num!Attr!xml] explicit column
name,
> because I could then write a function to generate the child node. But this
> does not seem to allow me to specify the Node Key's!!
> Is there any way to write a reasonably simple query to get the above schem
a'
> Thanks
> Greg|||Thanks for your reply John.
Can you do XSL Transforms in SQL 2000?
Greg
"John Bell" wrote:
[vbcol=seagreen]
> Hi Greg
> You will need to use the FOR XML EXPLICIT option if you wish to retrieve t
he
> data in this format. A different approach you be to apply a transform afte
r
> the data has been extracted. You could use XSLT to do this, possibly use t
he
> document()
> function to include and export the data in fragments.
> [url]http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&[
/url]
> John
> "Greg Bacchus" wrote:
>|||Hi Greg
It should be possible to do this using the SP_OA... procedures but really I
would expect it to be done externally.
You may want to look at SQL2005 as this could all be done a lot easier using
.NET
John
"Greg Bacchus" wrote:
[vbcol=seagreen]
> Thanks for your reply John.
> Can you do XSL Transforms in SQL 2000?
> Greg
> "John Bell" wrote:
>sql

How to setup reporting database

Hi!
Our client is using sql2000 sp3a OLTP database. They are also using a lot of
reports that generate high utilization on the server.
We would like to move that load to secondary server. What is the easiest way
to do that without modifying the current database or application?
First thing that comes to my mind is log shipping but as far as I know, in
this case we would get at least 1 min delay between primary and secondary
server.
I think that's great for reports but our clients don't think so...They
request seconds...
Alternative would be transactional replication. Can I replicate the entire
database with all views, stored procedures...?
I also found out that it requires that all the tables have at least one
primary key. Off course NOT all tables have them. How to solve this? Can
PK's be added easy or does it mean that they would have to modify the
database and application? The goal is not to modify the database and
application if posible.
Any sugestions?
tomLog Shipping wouldnt work anyways because the db is in a load state until
you apply the final TLog since you are constantly restoring. Transactional
Replication is pretty common for this. Not having a PK on a table is pretty
rare. Put an Identity column on the tables that don't have one.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>|||P.S.
Yes, you can replicate View, Procs, etc. but then you need a new snapshot
whenever they are modified. I prefer to just copy and paste them personally.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>|||"ChrisR" <chris@.noemail.com> wrote in message
news:ODv4F0JqEHA.376@.TK2MSFTNGP14.phx.gbl...
> Log Shipping wouldnt work anyways because the db is in a load state until
> you apply the final TLog since you are constantly restoring. Transactional
> Replication is pretty common for this. Not having a PK on a table is
pretty
> rare. Put an Identity column on the tables that don't have one.
Well, for reporting you can put the receiver into read-only mode, except
when the log is being applied.
Of course if you do this every minute, you spend most of your time NOT in
read-only mode. ;-)
And yes, this is a case where Joe Celko not withstanding an Identity key can
be a useful PK if you don't have one already.

How to setup reporting database

Hi!
Our client is using sql2000 sp3a OLTP database. They are also using a lot of
reports that generate high utilization on the server.
We would like to move that load to secondary server. What is the easiest way
to do that without modifying the current database or application?
First thing that comes to my mind is log shipping but as far as I know, in
this case we would get at least 1 min delay between primary and secondary
server.
I think that's great for reports but our clients don't think so...They
request seconds...
Alternative would be transactional replication. Can I replicate the entire
database with all views, stored procedures...?
I also found out that it requires that all the tables have at least one
primary key. Off course NOT all tables have them. How to solve this? Can
PK's be added easy or does it mean that they would have to modify the
database and application? The goal is not to modify the database and
application if posible.
Any sugestions?
tom
Log Shipping wouldnt work anyways because the db is in a load state until
you apply the final TLog since you are constantly restoring. Transactional
Replication is pretty common for this. Not having a PK on a table is pretty
rare. Put an Identity column on the tables that don't have one.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>
|||P.S.
Yes, you can replicate View, Procs, etc. but then you need a new snapshot
whenever they are modified. I prefer to just copy and paste them personally.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>
|||"ChrisR" <chris@.noemail.com> wrote in message
news:ODv4F0JqEHA.376@.TK2MSFTNGP14.phx.gbl...
> Log Shipping wouldnt work anyways because the db is in a load state until
> you apply the final TLog since you are constantly restoring. Transactional
> Replication is pretty common for this. Not having a PK on a table is
pretty
> rare. Put an Identity column on the tables that don't have one.
Well, for reporting you can put the receiver into read-only mode, except
when the log is being applied.
Of course if you do this every minute, you spend most of your time NOT in
read-only mode. ;-)
And yes, this is a case where Joe Celko not withstanding an Identity key can
be a useful PK if you don't have one already.

How to set replication

Dear Experts,
Would you please help me to set SQL 2000 replication ?
I need a link or article that show me how to set replication between SQL
2000 servers.
thank you
best regards.
Akkado,
have a look at
(a) this site for a graphical walkthrough:
http://www.mssqlcity.com/Articles/Replic/Replic.htm
(b) books on line (BOL) for an indepth description of replication.
(c) this recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
HTH,
Paul Ibison

How to send mail programmatically from SQL2000

I tried to use the stored procedure xp_sendmail to send mail
programmatically from MSSQL2000.
But it seems to be inexistent.
Is there any other means to do that?XP_sendmail lives in the master database, so you should use the command
master..XP_sendmail
HTH, Jens Suessmeyer.

How to send mail programmatically from SQL2000

I tried to use the stored procedure xp_sendmail to send mail
programmatically from MSSQL2000.
But it seems to be inexistent.
Is there any other means to do that?Did you qualify with the master database?
EXEC master..xp_sendmail ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoy@.bishoy.com> wrote in message news:%23apBGJDQGHA.516@.TK2MSFTNGP15.ph
x.gbl...
>I tried to use the stored procedure xp_sendmail to send mail
> programmatically from MSSQL2000.
> But it seems to be inexistent.
> Is there any other means to do that?
>|||I wrote now the following code:
exec [master].[dbo].[xp_sendmail]
@.recipients = 'bishoy@.bishoy.com',
@.message = 'Test',
@.subject = 'Test SQL Mail'
and received the following error:
Server: Msg 17985, Level 16, State 1, Line 0
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
Although when I added @.user parameter, I received the following error:
Server: Msg 17981, Level 16, State 1, Line 0
xp_sendmail: Invalid parameter '@.user'|||There are plenty of issues with xp_sendmail. I have seen this in newsgroups
reported before. You'll
probably find a resolution by Google and searching the newsgroup archives.Al
so consider using
xp_smtp_sendmail instead (www.sqldev.net).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoy@.bishoy.com> wrote in message news:%23ErGM4FQGHA.3896@.TK2MSFTNGP15.p
hx.gbl...
>I wrote now the following code:
> exec [master].[dbo].[xp_sendmail]
> @.recipients = 'bishoy@.bishoy.com',
> @.message = 'Test',
> @.subject = 'Test SQL Mail'
>
> and received the following error:
> Server: Msg 17985, Level 16, State 1, Line 0
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> Although when I added @.user parameter, I received the following error:
> Server: Msg 17981, Level 16, State 1, Line 0
> xp_sendmail: Invalid parameter '@.user'
>