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 Only Cursor

I have a sproc that has been running for over 2 years. We then alter a tabl
e structure
increasince the size of 3 fields, and correspondingly alter an Insert statem
ent for this
table. Now an 'update tablename where current of mycur' much later in the c
ode, issues an error of
'The cursor is Read Only' and fails.
The cursor syntax were :
SELECT ld_employee_no,
adjusted_hours
FROM Labrdet
WHERE ld_employee_no = @.dIFf_cur_empno
AND ld_prod_id + ld_prod_category + ld_prod_activity <> '02263'
ORDER BY ld_employee_no, adjusted_hours desc
I changed last line to the following and it works.
SELECT ld_employee_no,
adjusted_hours
FROM Labrdet
WHERE ld_employee_no = @.dIFf_cur_empno
AND ld_prod_id + ld_prod_category + ld_prod_activity <> '02263'
FOR UPDATE OF adjusted_hours
Does anyone have any idea, or should I post more info? We'd really like to
know
why the DDL change and the Insert change would affect a cursor update.
TIA,
Marc MillerWithout seeing the full repro I'm guessing that you changed something
that caused an implicit conversion to a static cursor. Always specify
cursor options explicitly to avoid this.
Could you explain why you are using a cursor at all? Again just
guessing by your code fragment this looks very like a straight data mod
that ought to be possible in an UPDATE with no cursor at all. If this
is a 2 year code legacy then maybe now would be a good time to review
and replace it.
David Portas
SQL Server MVP
--|||David,
I have a table of salaried employee time entires. Reporting requires,
however, that I only
show 40 hours per employee, even though they report overtime hours. Their
time is reported
in quarter hours increments and I need to loop and decrement/increment the
line items by the amount of
the overtime until I can best adjust each line 'evenly' (sort of an
allocation type basis.) to a total of 40 hours
for each person.
I have no idea in the world how I would use an UPDATE to accomplish this.
Thanks,
Marc Miller
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128538402.789619.48450@.z14g2000cwz.googlegroups.com...
> Without seeing the full repro I'm guessing that you changed something
> that caused an implicit conversion to a static cursor. Always specify
> cursor options explicitly to avoid this.
> Could you explain why you are using a cursor at all? Again just
> guessing by your code fragment this looks very like a straight data mod
> that ought to be possible in an UPDATE with no cursor at all. If this
> is a 2 year code legacy then maybe now would be a good time to review
> and replace it.
> --
> David Portas
> SQL Server MVP
> --
>|||> Reporting requires,
> however, that I only
> show 40 hours per employee
If that's just a reporting requirement why do you need to update the
table? Wouldn't it suffice to put the calc in a SELECT statement?

> I have no idea in the world how I would use an UPDATE to accomplish this.
If you want help with that please post DDL, sample data and required
results as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

read cvs in c# and insert into sql server

Hi,

Anybody has an example of reading a csv comma delimited file and insert the fields into a datatable?

Thanks

You have serveral options: bcp utility, DTS (SSIS in SQL 2005), Import/Export Wizard, or bulk insert command. DTS is much easier than others, you can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_des_07xh.asp

Or you can use the Import/Export Wizard:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_wiz_8vsj.asp

|||

Sorry, I must've posted to the wrong forum. I thought I posted into the LogParser one. I'll repost there. Yeah, I was asking if anybody knows how to use LogParser to read a custom cvs log file into the db. Thanks though.

Re : Sql Server - table - formula

I am trying to use SQL Server table formula field. I have
two fields 'Field1','Field2' and I am using the formula
Field2/Field1. The problem is if I have 0 values in
Field1, I am getting errors. Is there a documentation as
what functions are supported in the formula field and how
to handle the above mentioned error.What do you want the value to be if there is a '0' in field1. You can
use a case statement...
Vijay wrote:
> I am trying to use SQL Server table formula field. I have
> two fields 'Field1','Field2' and I am using the formula
> Field2/Field1. The problem is if I have 0 values in
> Field1, I am getting errors. Is there a documentation as
> what functions are supported in the formula field and how
> to handle the above mentioned error.|||You can use a CASE expression or even NULLIF like:
CREATE TABLE tbl (
...
col1 INT,
col2 INT,
calc_col AS col2/NULLIF(col1, 0),
...);
--
- Anith
( Please reply to newsgroups only )

RDA Push Command Failing ...

Hi,

I am using the Pull command to pull two fields, on is the primary ID (int) non identity and the other is Description which comes down as an ntext type. This works fine but if I change the description and use the push command I get the following error:-

The Query processor could not produce a query from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

I am really stuck with this one so if anyone can shed some light on it I would be much appreciated.

Cheers,

Jiggy!

Hi All,

I have found the problem. Basically I am intergrating with another desktop / web based application making a PDA version so the SQL Server Database is already in place. I have found that each table has a cluster index on the primary key for performance issues. Can someone please advice if there is a fix for this or is the Push command not compatible with Clustered Index's?

Cheers very much,

Jiggy!

|||Just so you do not feel alone in the world... and MAYBE point you in a useful direction...

This has to do with the parameters being set by the connection, most likely. The way to test this is to run the same SQL statement in Query Analyzer and see if it works, if it does, then you need to attempt to set different paramters on your connection and/or command.

Hope this helps a little, if you find the solution, and/or paramters to pass, I'd love to hear about it.

-Joshua

RDA Push Command Failing ...

Hi,

I am using the Pull command to pull two fields, on is the primary ID (int) non identity and the other is Description which comes down as an ntext type. This works fine but if I change the description and use the push command I get the following error:-

The Query processor could not produce a query from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

I am really stuck with this one so if anyone can shed some light on it I would be much appreciated.

Cheers,

Jiggy!

Hi All,

I have found the problem. Basically I am intergrating with another desktop / web based application making a PDA version so the SQL Server Database is already in place. I have found that each table has a cluster index on the primary key for performance issues. Can someone please advice if there is a fix for this or is the Push command not compatible with Clustered Index's?

Cheers very much,

Jiggy!

|||Just so you do not feel alone in the world... and MAYBE point you in a useful direction...

This has to do with the parameters being set by the connection, most likely. The way to test this is to run the same SQL statement in Query Analyzer and see if it works, if it does, then you need to attempt to set different paramters on your connection and/or command.

Hope this helps a little, if you find the solution, and/or paramters to pass, I'd love to hear about it.

-Joshua

rc:linktarget does not work

I have a webform with 2 frames. The top frame contains the main report and clicking on one of the fields generates another report. I want this report to open in the bottom frame and not take over the whole window. I am using this in the 'jump to url' section of the text field:
="http:/ServerName/ReportServer?/My Reports/Test Report&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false&rc:LinkTarget=charts&rc:Parameters=False&StartMonth=1&EndMonth=8&reportYear=2004"
where 'charts' is the frame name.
It does not work. I am new to reporting services and have spent a lot of time trying to figure this out. Please help me. it's urget.
Thanks!!
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.You need the rc:LinkTarget parameter in the URL of the initial report. It
still needs to be in the Jump to URL, or else subsequent clicks break out of
the frame.
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%23EN34Xj0EHA.2824@.TK2MSFTNGP09.phx.gbl...
>I have a webform with 2 frames. The top frame contains the main report and
>clicking on one of the fields generates another report. I want this report
>to open in the bottom frame and not take over the whole window. I am using
>this in the 'jump to url' section of the text field:
> ="http:/ServerName/ReportServer?/My Reports/Test
> Report&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false&rc:LinkTarget=charts&rc:Parameters=False&StartMonth=1&EndMonth=8&reportYear=2004"
> where 'charts' is the frame name.
> It does not work. I am new to reporting services and have spent a lot of
> time trying to figure this out. Please help me. it's urget.
> Thanks!!
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.