Long text

Hi,
I have written a UDF that calls itself and nests its operation. The output
is a varchar value in XML format. But sometimes the XML is more than 8000
characters and varchar is small for that. It is not possible to use text
data type. What can I do?
Any help would be greatly appreciated,
LeilaCan you form the XML using FOR XML instead of your own recursive UDF?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Leila" <leilas@.hotpop.com> wrote in message
news:%23XJncCx5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have written a UDF that calls itself and nests its operation. The output
> is a varchar value in XML format. But sometimes the XML is more than 8000
> characters and varchar is small for that. It is not possible to use text
> data type. What can I do?
> Any help would be greatly appreciated,
> Leila
>|||Actually I tried, maybe it's because lack of my skills in writing queries
that I couldn't use FOR XML, hence used UDF. Could you please have a look to
my post with subject: Retrieving Data in XML Format
Thanks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:us1MORx5EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Can you form the XML using FOR XML instead of your own recursive UDF?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23XJncCx5EHA.2624@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> I have written a UDF that calls itself and nests its operation. The
>> output
>> is a varchar value in XML format. But sometimes the XML is more than 8000
>> characters and varchar is small for that. It is not possible to use text
>> data type. What can I do?
>> Any help would be greatly appreciated,
>> Leila
>>
>|||Start reading:
www.sqlxml.org
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Leila" <leilas@.hotpop.com> wrote in message
news:uNJZHFy5EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Actually I tried, maybe it's because lack of my skills in writing queries
> that I couldn't use FOR XML, hence used UDF. Could you please have a look
to
> my post with subject: Retrieving Data in XML Format
> Thanks
>
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:us1MORx5EHA.2316@.TK2MSFTNGP15.phx.gbl...
> > Can you form the XML using FOR XML instead of your own recursive UDF?
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:%23XJncCx5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> >> Hi,
> >> I have written a UDF that calls itself and nests its operation. The
> >> output
> >> is a varchar value in XML format. But sometimes the XML is more than
8000
> >> characters and varchar is small for that. It is not possible to use
text
> >> data type. What can I do?
> >> Any help would be greatly appreciated,
> >> Leila
> >>
> >>
> >
> >
>

Long text

Hi,
I have written a UDF that calls itself and nests its operation. The output
is a varchar value in XML format. But sometimes the XML is more than 8000
characters and varchar is small for that. It is not possible to use text
data type. What can I do?
Any help would be greatly appreciated,
Leila
Can you form the XML using FOR XML instead of your own recursive UDF?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Leila" <leilas@.hotpop.com> wrote in message
news:%23XJncCx5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have written a UDF that calls itself and nests its operation. The output
> is a varchar value in XML format. But sometimes the XML is more than 8000
> characters and varchar is small for that. It is not possible to use text
> data type. What can I do?
> Any help would be greatly appreciated,
> Leila
>
|||Actually I tried, maybe it's because lack of my skills in writing queries
that I couldn't use FOR XML, hence used UDF. Could you please have a look to
my post with subject: Retrieving Data in XML Format
Thanks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:us1MORx5EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Can you form the XML using FOR XML instead of your own recursive UDF?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23XJncCx5EHA.2624@.TK2MSFTNGP11.phx.gbl...
>
|||Start reading:
www.sqlxml.org
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Leila" <leilas@.hotpop.com> wrote in message
news:uNJZHFy5EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Actually I tried, maybe it's because lack of my skills in writing queries
> that I couldn't use FOR XML, hence used UDF. Could you please have a look
to[vbcol=seagreen]
> my post with subject: Retrieving Data in XML Format
> Thanks
>
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:us1MORx5EHA.2316@.TK2MSFTNGP15.phx.gbl...
8000[vbcol=seagreen]
text
>

Long Running OPENXML Query

Hi,
I have a long running OPENXML query in a SP that runs for around 7 mins, it
runs 6 queries doing inserts and updates from the XML to the DB. The XML is
around 2.5MB in size.
On my test server which has 1 physical XEON CPU, SQL Server uses 50% of both
logical processors and all works fine.
However my live server has 2 physical CPUs but we only have a single proc
license. The SQL Server is configured to use 2 logical CPUs but just the 1
physical CPU. When the long running query begins the query runs on just 1
logical processor and all other connections to the SQL server timeout.
Does anyone know how to get the query to use both logical processors at 50%
and thus allow other connections to the server?
Thanks
Jason
Hi Jason,
Thanks for your posting!
From your descriptions, I understood you can make two CPU balance the
workload in test environment but failed to do so in live machine. If I have
misunderstood your concern, please feel free to point it out.
Based on my knowlegde, please check SQL Server Properties to see whether
you are using that two logic processor in SQL Server and selected Use.
However, we do not have a way to compose the query to use both logical
processor.
Sincerely yours,
Michael Cheng
Microsoft Online Partner 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.
|||What version of SQL Server 2000 are you running? Have you upgraded to SP4
(which should give you some improvement on the OpenXML front).
Best regards
Michael
"Jason" <techno@.noemail.nospam> wrote in message
news:%23JxasEJiFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a long running OPENXML query in a SP that runs for around 7 mins,
> it
> runs 6 queries doing inserts and updates from the XML to the DB. The XML
> is
> around 2.5MB in size.
> On my test server which has 1 physical XEON CPU, SQL Server uses 50% of
> both
> logical processors and all works fine.
> However my live server has 2 physical CPUs but we only have a single proc
> license. The SQL Server is configured to use 2 logical CPUs but just the 1
> physical CPU. When the long running query begins the query runs on just 1
> logical processor and all other connections to the SQL server timeout.
> Does anyone know how to get the query to use both logical processors at
> 50%
> and thus allow other connections to the server?
> Thanks
> Jason
>
|||Hi
Thanks for your replies.
We are running SP4. I have rewritten the query which has improved the speed
and also used NOLOCK to avoid locks being taken out for the duration of the
transaction.
The problem I still have is that SQL Server is only using 1 logical
processor for everything that it does. On the processor tab of the SQL
Properties window I have the following settings,
CPU 0 and 1 are checked, 2 and 3 are not.=20
Max worker threads is 255
Boost SQL server priority is checked
Parallism
Use all available processors is checked
Min query plan threshold is 5
When any SQL activity occurs the Task Manager performance window shows 25%
CPU usage on the left and out of the 4 available logical processors only 1
spikes.
Any ideas why its not using 50%?
Thanks
Jason
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23KJXNUdiFHA.576@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> What version of SQL Server 2000 are you running? Have you upgraded to SP4
> (which should give you some improvement on the OpenXML front).
> Best regards
> Michael
> "Jason" <techno@.noemail.nospam> wrote in message
> news:%23JxasEJiFHA.3960@.TK2MSFTNGP12.phx.gbl...
proc[vbcol=seagreen]
1[vbcol=seagreen]
1
>
|||OpenXML is implemented as a remote provider, so I believe parallelism is
disabled for OpenXML. I'm hardly an expert on the way SQL schedules, though.
Best regards
Michael
"Jason" <techno@.noemail.nospam> wrote in message
news:O$m92M3iFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks for your replies.
> We are running SP4. I have rewritten the query which has improved the
> speed
> and also used NOLOCK to avoid locks being taken out for the duration of
> the
> transaction.
> The problem I still have is that SQL Server is only using 1 logical
> processor for everything that it does. On the processor tab of the SQL
> Properties window I have the following settings,
> CPU 0 and 1 are checked, 2 and 3 are not.=20
> Max worker threads is 255
> Boost SQL server priority is checked
> Parallism
> Use all available processors is checked
> Min query plan threshold is 5
> When any SQL activity occurs the Task Manager performance window shows 25%
> CPU usage on the left and out of the 4 available logical processors only 1
> spikes.
> Any ideas why its not using 50%?
> Thanks
> Jason
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23KJXNUdiFHA.576@.tk2msftngp13.phx.gbl...
> proc
> 1
> 1
>