Read XML > 8k in sql TEXT column?

Hi,
I have a SQL2k database that holds XML in a TEXT column because it is
greater than 8k, I need to extract the value from a couple of fields in the
XML. How can I do this? Examples would be great; I'm new to world of XML.
BTW I'm don't have control of the database design so I can't change the
structure of the db to hold the data in a more senisble way.
Thanks,
Paul.Hello Paul,
Thank you for posting.
Regarding on the read XML data from multiple columns in SQLServer database,
are you using ADO.NET components to access the database table? Based on my
understanding, if we can make sure the content order of those columns in
the database table, we can just use ADO.NET datareader or dataadapter to
query the records out, and then combine the text in those columns together
to construct a complete text stream.
Please let me know if you have any detailed question or concerns here.
Regards,
Steven Cheng
Microsoft Online Community 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.|||Hello Steven,
Thanks for the reply, I'm not using ADO.NET.
All the data is in a single SQL2k Text column but it is greater than 8k in
size. What I'm looking to do get data out of a couple of elements and write
them back to two sql columns using a stored procedure.
The basic issue is because I can't delclare a variable of type text in the
procedure. How can I use the sp_xml_preparedocument and OPENXML commands in
a
procedure with a large text column with more than 8k of XML.
Thanks,
Paul.
"Steven Cheng[MSFT]" wrote:

> Hello Paul,
> Thank you for posting.
> Regarding on the read XML data from multiple columns in SQLServer database
,
> are you using ADO.NET components to access the database table? Based on my
> understanding, if we can make sure the content order of those columns in
> the database table, we can just use ADO.NET datareader or dataadapter to
> query the records out, and then combine the text in those columns together
> to construct a complete text stream.
> Please let me know if you have any detailed question or concerns here.
> Regards,
>
> Steven Cheng
> Microsoft Online Community 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
.
>|||Thanks for your response Paul,
So you're going to done the multiple text column string concatenate at
server-side through T-SQL. Based on my research, I'm afraid this is not
supported in SQL 2000 since the datatype are limited to varchar or ntext
which has 8000 limitation. And we can not have local variable that have
larger value return from concatenate of such columns. So we may consider
done it at upstream level(in data access component, ADO or ADO.NET).
BTW, if it is possible to upgrate to SQL 2005, there has built-in sql xml
type and CLR code supported which may help resolve such issue.
Sorry for the inconvenience this brings you.
Regards,
Steven Cheng
Microsoft Online Community 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.

read trn log backup files

What product is out there that can read a Transaction Backup file?
A restore was done but they didn't apply a couple of transaction logs. I
need to see what data is in these backup logs. If there is a product that
can do this can it apply the data from the logs or dump it to a file?
Thanks.
I've listed some on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"AHartman" <Hoosbruin@.Kconline.com> wrote in message news:OS70VXD3HHA.212@.TK2MSFTNGP05.phx.gbl...
> What product is out there that can read a Transaction Backup file?
> A restore was done but they didn't apply a couple of transaction logs. I
> need to see what data is in these backup logs. If there is a product that
> can do this can it apply the data from the logs or dump it to a file?
>
> Thanks.
>

read trn log backup files

What product is out there that can read a Transaction Backup file?
A restore was done but they didn't apply a couple of transaction logs. I
need to see what data is in these backup logs. If there is a product that
can do this can it apply the data from the logs or dump it to a file?
Thanks.I've listed some on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"AHartman" <Hoosbruin@.Kconline.com> wrote in message news:OS70VXD3HHA.212@.TK2MSFTNGP05.phx.g
bl...
> What product is out there that can read a Transaction Backup file?
> A restore was done but they didn't apply a couple of transaction logs. I
> need to see what data is in these backup logs. If there is a product that
> can do this can it apply the data from the logs or dump it to a file?
>
> Thanks.
>

read trn log backup files

What product is out there that can read a Transaction Backup file?
A restore was done but they didn't apply a couple of transaction logs. I
need to see what data is in these backup logs. If there is a product that
can do this can it apply the data from the logs or dump it to a file?
Thanks.I've listed some on my links page:
http://www.karaszi.com/SQLServer/links.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"AHartman" <Hoosbruin@.Kconline.com> wrote in message news:OS70VXD3HHA.212@.TK2MSFTNGP05.phx.gbl...
> What product is out there that can read a Transaction Backup file?
> A restore was done but they didn't apply a couple of transaction logs. I
> need to see what data is in these backup logs. If there is a product that
> can do this can it apply the data from the logs or dump it to a file?
>
> Thanks.
>sql

Read Only MDF LDF files

I have a couple of SQL database files, MDF and LDF, that were distributed on
CD media (they came w/ a Microsoft Press book).
I can attach the database, but it is read only. However, if I try to set
the DB to not read-only, I get an error:
Error 5105: Device activation error. They physical file name "C:\program
files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
incorrect.
Could not restart database "contactsdb". Reverting to old status.
ALTER DATABASE statement failed
sp_dboption command failed.
If I set the files to NOT read-only (they were read only since they were
copied from a CD), the DB gets marked suspect and/or I get the error 9003:
The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
How am I supposed to attach this dataabase in a write-enabled mode?
--
Thanks,
Martin SchmidIt sounds like the files might be bad, and I don't think just marking them
readable in the file system would do that. You could try just copying the
MDF file to the local directory (leaving the LDF on the CD), changing the
file's readonly attribute, and then try attaching the mdf only (maybe with
sp_attach_single_file_db. It might be able to build a new log for you.
Good Luck
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
news:eA32Y5AeDHA.2304@.TK2MSFTNGP11.phx.gbl...
> I have a couple of SQL database files, MDF and LDF, that were distributed
on
> CD media (they came w/ a Microsoft Press book).
> I can attach the database, but it is read only. However, if I try to set
> the DB to not read-only, I get an error:
> Error 5105: Device activation error. They physical file name "C:\program
> files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
> incorrect.
> Could not restart database "contactsdb". Reverting to old status.
> ALTER DATABASE statement failed
> sp_dboption command failed.
> If I set the files to NOT read-only (they were read only since they were
> copied from a CD), the DB gets marked suspect and/or I get the error 9003:
> The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
> How am I supposed to attach this dataabase in a write-enabled mode?
> --
> Thanks,
> Martin Schmid
>|||I DON'T want the DB read only... the file is marked read only because it was
copied from READ ONLY media.
--
Thanks,
Martin Schmid, EIT, CCSA, MCDBA, MCSE
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:euGR0OCeDHA.2436@.TK2MSFTNGP11.phx.gbl...
> Hi Martin !
> Even you want the database to be read-only. you cannot achieve it by
setting
> the attribute to read-only, because during normal database checkpoints are
> written to the LOG-Files and the database. What you can do is to copy the
DB
> set the attribute to writeable and set the database (logically) to
readonly,
> so that only records can be read.
> HTH, Jens Süßmeyer.
>
> "Martin Schmid" <martinschmid@.sbcglobal.net.nospam> schrieb im Newsbeitrag
> news:eA32Y5AeDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > I have a couple of SQL database files, MDF and LDF, that were
distributed
> on
> > CD media (they came w/ a Microsoft Press book).
> >
> > I can attach the database, but it is read only. However, if I try to
set
> > the DB to not read-only, I get an error:
> > Error 5105: Device activation error. They physical file name
"C:\program
> > files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
> > incorrect.
> > Could not restart database "contactsdb". Reverting to old status.
> > ALTER DATABASE statement failed
> > sp_dboption command failed.
> >
> > If I set the files to NOT read-only (they were read only since they were
> > copied from a CD), the DB gets marked suspect and/or I get the error
9003:
> > The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
> >
> > How am I supposed to attach this dataabase in a write-enabled mode?
> >
> > --
> > Thanks,
> > Martin Schmid
> >
> >
>|||Perfect! Thanks.
--
Thanks,
Martin Schmid, EIT, CCSA, MCDBA, MCSE
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OgkrngBeDHA.1828@.TK2MSFTNGP10.phx.gbl...
> It sounds like the files might be bad, and I don't think just marking them
> readable in the file system would do that. You could try just copying the
> MDF file to the local directory (leaving the LDF on the CD), changing the
> file's readonly attribute, and then try attaching the mdf only (maybe with
> sp_attach_single_file_db. It might be able to build a new log for you.
> Good Luck
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
> news:eA32Y5AeDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > I have a couple of SQL database files, MDF and LDF, that were
distributed
> on
> > CD media (they came w/ a Microsoft Press book).
> >
> > I can attach the database, but it is read only. However, if I try to
set
> > the DB to not read-only, I get an error:
> > Error 5105: Device activation error. They physical file name
"C:\program
> > files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
> > incorrect.
> > Could not restart database "contactsdb". Reverting to old status.
> > ALTER DATABASE statement failed
> > sp_dboption command failed.
> >
> > If I set the files to NOT read-only (they were read only since they were
> > copied from a CD), the DB gets marked suspect and/or I get the error
9003:
> > The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
> >
> > How am I supposed to attach this dataabase in a write-enabled mode?
> >
> > --
> > Thanks,
> > Martin Schmid
> >
> >
>

RDA Question

Hi All

Wondering if someone can answer a couple of quick questions.

I have a vb 2005 winforms application that uses SQL CE database and uses RDA to pull data from a SQL 2005 database and also to push back the changes. I would like to be able to advise the user if the connection to the SQL 2005 database is available and make either the push button or the pull button available depending on the state of the local or remote database.

Any suggestions on how I can do this. I have tried the following which works sort of ok but the timeout when it can't open the connection is too long.

PrivateSub checkserver()

Dim connectionstatus AsNew SqlConnection(My.Settings.MyRemoteConnectionString)

Try

connectionstatus.Open()

label2.text = "Connected"

Catch ex As Exception

Label2.Text = "Disconnected"

Button5.Enabled = False

Button6.Enabled = False

Finally

connectionstatus.Close()

EndTry

EndSub

Also I'm not sure of a way to determine which database is the most recent to enable either the push or pull buttons.

Appreciate any assistance.

Regards

Ian

You could add "Connection Timeout=5" to MyRemoteConnectionString (5 seconds instead of 15)

|||

Thanks, much better.

Any ideas about how I can determine the last time either database was edited/updated?

Ian

|||If you are using RDA, you must implement a scheme to track these changes yourself, as no information about server state is available when using RDA. I you are tracking changes locally, you might be able to get some information from the replication system tables in the SQL CE database. Maybe you should consider Merge replication, which allows you to track changes both server side and client side.

RDA Question

Hi All

Wondering if someone can answer a couple of quick questions.

I have a vb 2005 winforms application that uses SQL CE database and uses RDA to pull data from a SQL 2005 database and also to push back the changes. I would like to be able to advise the user if the connection to the SQL 2005 database is available and make either the push button or the pull button available depending on the state of the local or remote database.

Any suggestions on how I can do this. I have tried the following which works sort of ok but the timeout when it can't open the connection is too long.

Private Sub checkserver()

Dim connectionstatus As New SqlConnection(My.Settings.MyRemoteConnectionString)

Try

connectionstatus.Open()

label2.text = "Connected"

Catch ex As Exception

Label2.Text = "Disconnected"

Button5.Enabled = False

Button6.Enabled = False

Finally

connectionstatus.Close()

End Try

End Sub

Also I'm not sure of a way to determine which database is the most recent to enable either the push or pull buttons.

Appreciate any assistance.

Regards

Ian

You could add "Connection Timeout=5" to MyRemoteConnectionString (5 seconds instead of 15)

|||

Thanks, much better.

Any ideas about how I can determine the last time either database was edited/updated?

Ian

|||If you are using RDA, you must implement a scheme to track these changes yourself, as no information about server state is available when using RDA. I you are tracking changes locally, you might be able to get some information from the replication system tables in the SQL CE database. Maybe you should consider Merge replication, which allows you to track changes both server side and client side.

Raw File reader utility?

Does anyone happen to know if there is a raw file reader utility available? One of my processes drops a couple of interim raw files and I'd like to be able to look at the data in them in a columnar format without having to run a downstream data flow with a reader inserted.If you need to do this, then temporarily redirect to a flat file, look at your data, and then put back the raw file destination. Or, temporarily throw a multicast in right before the raw file destination and add a flat file destination. That'd be your best option.|||

It's about time someone created a utility (Microsoft ?).

Not having one does provide a challenge for support, especially if you are heavily using raw files for staging data.

|||

Have a look at this one I've just finished.

http://sqlblogcasts.com/files/folders/ssis_tools/entry1528.aspx

Feedback please as this is hot off the presses

|||Simon,
The features look very good and pretty comprehensive.
I am going to try this this week.

Keep up the good work.|||

Simon,

Whats the best place to provide feedback?

|||

You should be able to add comments to the download page. You may have to register.

or you can contact me through my blog, see below

|||Just awesome, man. I don't have a lot of cash, but I'm kicking a few quid your way. Enjoy a pint on me!