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 Rollback SQL

Hello experts,

I made a huge mistake on a production server, ran an update without the WHERE clause.

UPDATE myTable
SET Field1 = 'SOmetext',
Field2 = 'sometext2'

Is there a way to rollback? I read something a while ago about transaction logs.

SOS!

Thanks

Ouch - but you're not the first!

There is a way to rollback as SQL statement but you would have needed to issue the BEGIN TRANSACTION command before issuing the update. Unless you're using a query tool that issues it behind the scenes (then you can issue ROLLBACK), - but if your running the commands directly then you can't rollback without having done so.

A quick note before I continue with a possible solution: it's a good habit (as you've found) to do updates/deletes like this:

BEGIN TRANSACTION

...<your update statement>

And then watching the "rows affected" to make sure it's what you expected. If yes, issue the COMMIT command otherwise ROLLBACK.

Anyway - here's the possible solution. Find out what the backup strategy is on the production database. For example, is it one full backup a night followed by hourly incremental backups - then you will be able to restore the full backup followed by the transaction log backups sequentially to the point before you issued the update statement. Of course, this will undo all other data changes up to the last transaction log restore.

|||

You cant perform a rollback, but hopefully you have your backup procedures in order so you can make a database restore. The transaction logs will be used in the restore procedure.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

|||

Thanks for the prompt responses.

I looked further and it show that there are Full backups done nightly, but when I do a restore I get an error"

Restore failed for Server '<ServerName>'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot open backup device '<GUID of the device>'. Operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)

I can see a list of backups on the restore window and I tried using a different backup but still get the error. The database is on a different drive than the SQL installation but on the same system.

I also tried to copy the files to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and detach and attach but same error. Any ideas on what I can do?

Thanks.

|||

I can see a list of backups on the restore window and I tried using a different backup but still get the error. The database is on a different drive than the SQL installation but on the same system.

I also tried to copy the files to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and detach and attach but same error. Any ideas on what I can do?

What type is your backup file? If it's *.bak then you can only open it in Restore Wizard. And if it's *.mdf/*.ldf, you can only attach it. It's different.

Also, the error message says sql server cannot open the backup file, are you sure the backup file/or the hard disk is not a damaged one?

Hope my suggestion helps