How to save FOR XML results into TEXT column?

Help! It appears that the FOR XML clause doesn't 100% get done what I
need to accommplish.
Problem: With Orders table (no Order Details) as:
OrderID int
OrderData ntext
OrderDate smalldatetime
OrderProcessed bit
I have another query that does a select on an Order Details and
Customer table which wnds with FOR XML.
I need to get the results of that query stored in the OrderData column
from above in XML format.
How do I accomplish ths using SQL server tools?
"CD" <doober@.family.us> wrote in message
news:s535f0pk4c6sro2puv3h783t7femfj1gms@.4ax.com...
[snip]
> I need to get the results of that query stored in the OrderData column
> from above in XML format.
> How do I accomplish ths using SQL server tools?
You can't without a lot of effort. If you're storing the XML for caching
purposes you're better off using something like the caching in ASP.Net.
Bryant
|||
>You can't without a lot of effort. If you're storing the
XML for caching
>purposes you're better off using something like the
caching in ASP.Net.
>
No, I need to use it to post orders to another downsteam
system that requires the order data in XML in a single
field, one record for each order.
|||Wait for Yukon.
Read How XML is treated in Yukon
[vbcol=seagreen]
>--Original Message--
the
>XML for caching
>caching in ASP.Net.
>No, I need to use it to post orders to another downsteam
>system that requires the order data in XML in a single
>field, one record for each order.
>.
>
|||To add some more information: In SQL Server 2005, you can nest FOR XML
expressions and thus could write an expression that provides XML data in XML
in a field, one per row.
Best regards
Michael
"Nitin" <anonymous@.discussions.microsoft.com> wrote in message
news:2b75901c46894$9b9861c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Wait for Yukon.
> Read How XML is treated in Yukon
>
> the

How to run a script against all databases

I want to run a script to get the file details on each database.
Script:
Use DBname
Select * from sysfiles
I want to cycle through the databases using dbname as variable. I tried a
bunch of different ways with no luck. Any help or pointers would be great.
What I'm looking to do is "inventory" my sql servers. I need to come up
with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
I would like to come up with a script (or scripts) that will give me list of
databases and details on databases for that server. I would like to dump
into table and eventually use that to keep track of my servers. Any
thoughts ?
thanks in advance
rob
Try this:
sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
"Rob" wrote:

> I want to run a script to get the file details on each database.
> Script:
> Use DBname
> Select * from sysfiles
> I want to cycle through the databases using dbname as variable. I tried a
> bunch of different ways with no luck. Any help or pointers would be great.
> What I'm looking to do is "inventory" my sql servers. I need to come up
> with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
> I would like to come up with a script (or scripts) that will give me list of
> databases and details on databases for that server. I would like to dump
> into table and eventually use that to keep track of my servers. Any
> thoughts ?
> thanks in advance
> rob
|||This works great... yet another undocumented MS trick.
One question. One of my tables has a name that starts with a number, 3.
This seems to choke the script. Any ideas on how to resolve.
thanks again for great tip.
rob
"CLM" wrote:
[vbcol=seagreen]
> Try this:
> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
> "Rob" wrote:
|||Surround the question mark in square brackets [?]
A number is not a valid starting character for a regular identifier.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:BC0CA6B4-0A30-4D45-89F7-EC46DCB1B15A@.microsoft.com...[vbcol=seagreen]
> This works great... yet another undocumented MS trick.
> One question. One of my tables has a name that starts with a number, 3.
> This seems to choke the script. Any ideas on how to resolve.
> thanks again for great tip.
> rob
>
> "CLM" wrote:

How to run a script against all databases

I want to run a script to get the file details on each database.
Script:
Use DBname
Select * from sysfiles
I want to cycle through the databases using dbname as variable. I tried a
bunch of different ways with no luck. Any help or pointers would be great.
What I'm looking to do is "inventory" my sql servers. I need to come up
with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
I would like to come up with a script (or scripts) that will give me list of
databases and details on databases for that server. I would like to dump
into table and eventually use that to keep track of my servers. Any
thoughts '
thanks in advance
robTry this:
sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
"Rob" wrote:
> I want to run a script to get the file details on each database.
> Script:
> Use DBname
> Select * from sysfiles
> I want to cycle through the databases using dbname as variable. I tried a
> bunch of different ways with no luck. Any help or pointers would be great.
> What I'm looking to do is "inventory" my sql servers. I need to come up
> with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
> I would like to come up with a script (or scripts) that will give me list of
> databases and details on databases for that server. I would like to dump
> into table and eventually use that to keep track of my servers. Any
> thoughts '
> thanks in advance
> rob|||This works great... yet another undocumented MS trick.
One question. One of my tables has a name that starts with a number, 3.
This seems to choke the script. Any ideas on how to resolve.
thanks again for great tip.
rob
"CLM" wrote:
> Try this:
> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
> "Rob" wrote:
> > I want to run a script to get the file details on each database.
> > Script:
> >
> > Use DBname
> > Select * from sysfiles
> >
> > I want to cycle through the databases using dbname as variable. I tried a
> > bunch of different ways with no luck. Any help or pointers would be great.
> > What I'm looking to do is "inventory" my sql servers. I need to come up
> > with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
> > I would like to come up with a script (or scripts) that will give me list of
> > databases and details on databases for that server. I would like to dump
> > into table and eventually use that to keep track of my servers. Any
> > thoughts '
> >
> > thanks in advance
> > rob|||Surround the question mark in square brackets [?]
A number is not a valid starting character for a regular identifier.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:BC0CA6B4-0A30-4D45-89F7-EC46DCB1B15A@.microsoft.com...
> This works great... yet another undocumented MS trick.
> One question. One of my tables has a name that starts with a number, 3.
> This seems to choke the script. Any ideas on how to resolve.
> thanks again for great tip.
> rob
>
> "CLM" wrote:
>> Try this:
>> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
>> "Rob" wrote:
>> > I want to run a script to get the file details on each database.
>> > Script:
>> >
>> > Use DBname
>> > Select * from sysfiles
>> >
>> > I want to cycle through the databases using dbname as variable. I
>> > tried a
>> > bunch of different ways with no luck. Any help or pointers would be
>> > great.
>> > What I'm looking to do is "inventory" my sql servers. I need to come
>> > up
>> > with a Disaster Recovery Plan and I have 10 + sql servers I need to
>> > document.
>> > I would like to come up with a script (or scripts) that will give me
>> > list of
>> > databases and details on databases for that server. I would like to
>> > dump
>> > into table and eventually use that to keep track of my servers. Any
>> > thoughts '
>> >
>> > thanks in advance
>> > rob

How to run a script against all databases

I want to run a script to get the file details on each database.
Script:
Use DBname
Select * from sysfiles
I want to cycle through the databases using dbname as variable. I tried a
bunch of different ways with no luck. Any help or pointers would be great.
What I'm looking to do is "inventory" my sql servers. I need to come up
with a Disaster Recovery Plan and I have 10 + sql servers I need to document
.
I would like to come up with a script (or scripts) that will give me list of
databases and details on databases for that server. I would like to dump
into table and eventually use that to keep track of my servers. Any
thoughts '
thanks in advance
robTry this:
sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
"Rob" wrote:

> I want to run a script to get the file details on each database.
> Script:
> Use DBname
> Select * from sysfiles
> I want to cycle through the databases using dbname as variable. I tried a
> bunch of different ways with no luck. Any help or pointers would be great
.
> What I'm looking to do is "inventory" my sql servers. I need to come up
> with a Disaster Recovery Plan and I have 10 + sql servers I need to docume
nt.
> I would like to come up with a script (or scripts) that will give me list
of
> databases and details on databases for that server. I would like to dump
> into table and eventually use that to keep track of my servers. Any
> thoughts '
> thanks in advance
> rob|||This works great... yet another undocumented MS trick.
One question. One of my tables has a name that starts with a number, 3.
This seems to choke the script. Any ideas on how to resolve.
thanks again for great tip.
rob
"CLM" wrote:
[vbcol=seagreen]
> Try this:
> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
> "Rob" wrote:
>|||Surround the question mark in square brackets [?]
A number is not a valid starting character for a regular identifier.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:BC0CA6B4-0A30-4D45-89F7-EC46DCB1B15A@.microsoft.com...[vbcol=seagreen]
> This works great... yet another undocumented MS trick.
> One question. One of my tables has a name that starts with a number, 3.
> This seems to choke the script. Any ideas on how to resolve.
> thanks again for great tip.
> rob
>
> "CLM" wrote:
>

How to rollback a transaction in Data Access Layer

Hi,

I am having a application in which from the front end i am saving details of three different things

i.Enquiry Details

ii.Parts Details

iii.Machine details

i am saving the Enquiry detail in a data table,Parts Details in a data table and machine detail in a data table and finally i am adding the three data tables into a single data set and passing the data set to data access layer there i have three insert command one for each data table in my case the enquiry data table will be saved first and then the next two details will be saved and i am saving the details in three different tables in the database, my problem is some times the enquiry details will save to the database and while saving the Parts details there may be some exception and i will throw an exception in that case the enquiry details will be saved and the remaining two details are not saved(Which are also part of the same Transaction).I wanted to know about how to call the transaction function in case of Data Access Layer.

Do you want to store the data independtly. Even the tail 2 tables failed you want to successfully commit the first insert?

Pls explain the last statement?

|||

You can use a user transaction which can be opend through Sqlconnection.begintransaction()

See the MSDN for more details:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.begintransaction.aspx

The transaction is tied to the connection but can be commited separately.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for Reply,

The answers for your questions are below

1.Do you want to store the data independtly?

yes i am storing the data in three different tables.

2.Even the tail 2 tables failed you want to successfully commit the first insert?

No i want to roll back the entire transaction.

how to return event description from stored proceedure?

I am using C# to insert the form details and passing event id (numeric) to the same stored procedure in my eror handler and need to retrieve the description from event_db to display in MessageBox..

can the stored proceedure send the text?

Could you describe this abit in detail and send your DDL over ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de