Change local variable inside query

/*Given*/

CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)

/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.

This doesn't compile of course, but is there
a way to accomplish the same thing?
*/

DECLARE @.ndx int

SET @.ndx = 1

SELECT

(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT

FROM _T1sub a

/*Output would look like this:*/

FKplusWT
----
11
22
33

/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/

thanks, Otto PorterOn Sat, 02 Oct 2004 12:20:48 -0600, Otto Porter wrote:

>I know, I can get this output just by adding
>FK+WT. This is not about that.
>This is about setting vars inside a query

Hi Otto,

It's not possible to change the value of a variable during the execution
of a SELECT statement. At least not the way you are trying to do it.

You can of course do
SELECT @.var = ..., @.var = ...
FROM table
WHERE ...
but I assume that this is not what you want. You can't mix this format of
the SELECT statement with a SELECT that outputs a result set.

The way I read your example, it would be very easy to have queries where
the result would be dependent on the order in which rows are processed by
SQL Server. Since SQL Server is entirely free in it's choice of processing
order, the results would be unexpected and might even vary from execution
to execution.

Check out the following link to find some good examples of the possible
effects of unexpected processing order on assignments with the SELECT
statement:
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 characters.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.
Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>
|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:

> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
>

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 characters.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:
> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> > SQL 2005
> >
> > I have a table with a number of nvarchar fields. One field in the midde
> > was
> > 1000 in length. I ran a query with:
> >
> > ALTER TableName
> > ALTER ColumnName nvarchar(2000).
> >
> > This apeared to increase the field size to 2000 (as seen in Server
> > Management Studio), but it will still not store any more than 1000
> > characters.
> >
> > Have I done it incorrectly.
> >
> > There appears to be still space in my record as I can add a field at the
> > end
> > 2000 long and that works correctly.
> >
> > Thanks,
> > Steve.
> >
>

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 character
s.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and
I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:

> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
>

Change Date Format from mm/dd/yyyy to dd/mm/yyyy

Hi,
I have a query to retrieve data from a table for the give two dates(from and
to) in a stored procedure.
select * from employees where (DateJoined BETWEEN @.FromDate AND @.ToDate)
now I have give the dates like this to get the results
@.FromDate =01/01/2005(mm/dd/yyyy)
@.ToDate=6/2/2005(mm/dd/yyyy)
but I would like to give the date in this format (dd/mm/yyyy) like
@.FromDate =01/01/2005(dd/mm/yyyy)
@.ToDate=2/6/2005(dd/mm/yyyy)
can someone help me with this(I know there is a way in which you can specify
in select query itself, but forgot :-) )
Thanks
KiranWhy do you need to express the date in a special format? Why not let the end
user use the format the
end user want (based on regional settings) and let the application send a fo
rmat to SQL server which
is language neutral?
See http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kiran" <kiran_nospam@.gmail.com> wrote in message news:Ot61f56ZFHA.3840@.tk2msftngp13.phx.gb
l...
> Hi,
> I have a query to retrieve data from a table for the give two dates(from a
nd to) in a stored
> procedure.
> select * from employees where (DateJoined BETWEEN @.FromDate AND @.ToDate)
> now I have give the dates like this to get the results
> @.FromDate =01/01/2005(mm/dd/yyyy)
> @.ToDate=6/2/2005(mm/dd/yyyy)
> but I would like to give the date in this format (dd/mm/yyyy) like
> @.FromDate =01/01/2005(dd/mm/yyyy)
> @.ToDate=2/6/2005(dd/mm/yyyy)
> can someone help me with this(I know there is a way in which you can speci
fy in select query
> itself, but forgot :-) )
> Thanks
> Kiran
>|||Kiran wrote:
> Hi,
> I have a query to retrieve data from a table for the give two
> dates(from and to) in a stored procedure.
> select * from employees where (DateJoined BETWEEN @.FromDate AND
> @.ToDate)
> now I have give the dates like this to get the results
> @.FromDate =01/01/2005(mm/dd/yyyy)
> @.ToDate=6/2/2005(mm/dd/yyyy)
> but I would like to give the date in this format (dd/mm/yyyy) like
> @.FromDate =01/01/2005(dd/mm/yyyy)
> @.ToDate=2/6/2005(dd/mm/yyyy)
> can someone help me with this(I know there is a way in which you can
> specify in select query itself, but forgot :-) )
> Thanks
> Kiran
Always use a portable date format in SQL... or else.
YYYYMMDD
or
YYYY-MM-DDThh:mm:ss.mmm
David Gugick
Quest Software
www.imceda.com
www.quest.com

change data table from system.data into sql query

i someone had teach me how to write a query in datatable. however i need to get the data out from my database rather than the data table. can someone teach me how should i do it?

esp at the first like... like DataTable dt = GetFilledTable()

since i already have set of data in my preset table i should be getting data from SqlDataSource1 right ( however i am writing this in my background code or within <script></script>

so can anyone help me?

protected void lnkRadius_Click(object sender, EventArgs e)

{

DataTable dt = GetFilledTable();



double radius = Convert.ToDouble(txtRadius.Text);

decimal checkX = (decimal)dt.Rows[0]["Latitude"];

decimal checkY = (decimal)dt.Rows[0]["Longitude"];



// expect dt[0] to pass - as this is our check point

// We use for rather than fopreach because the later does not allow DELETE during loop execution

for(int index=0; index < dt.Rows.Count; index++)

{

DataRow dr = dt.Rows[index];



decimal testX = (decimal)dr["Latitude"];

decimal testY = (decimal)dr["Longitude"];



double testXzeroed = Convert.ToDouble(testX -= checkX);

double testYzeroed = Convert.ToDouble(testY -= checkY);



double distance = Math.Sqrt((testXzeroed * testXzeroed) + (testYzeroed * testYzeroed));



// mark for delete (not allowed in a foreach - so we use "for")

if (distance > radius)

dr.Delete();

}



// accept deletes

dt.AcceptChanges();



GridView1.DataSource = dt.DefaultView;

GridView1.DataBind();

}

should start with

SqlDataSource1.SelectCommand = "SELECT [id], [title], [largeimage], [imageTakenAt], [imageLon], [imageLat], [notes] FROM [images]";

SqlDataSource1.DataBind;

or something like that?

|||

Hi,

I would suggest you start from learning ADO.NET. You will learn how to write a query with SqlCommand, and use SqlDataAdapter to fill a DataTable.

Please start from the following link:

http://msdn2.microsoft.com/en-us/data/aa937699.aspx

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

change connectionstring for dataset

hi,

I'm having this application using the express way to create the dategridview by having the query string builder. However, my computer recently crashed and I have no idea to change the connection string. so when I load the whole windows application, I am unable to view the information that are supposed to be in the datagridview. however, it returned an exception.

after finding out the main culprit, I realised that the database is using the old sql server's login. since it is using the old database's login, therefore it is unable to log the information into the datagrid view.

Please help! Thanks.

I'm sorry to hear that your computer crashed. Did you mean to change connection strings for typed DataSet which you created via "Data Source Configuration Wizard"? If so, you only need to change the corresponding Data Connection in the Server Explorer, as all Typed DataSet generated by the wizard use connectionstrings defined in Data Connections. If you want to configure connection string for individual TableAdapter, you need to open the typed DataSet in Design view, and right click on the TableAdapter->choose Configure...->press Previous button untill you rearch the "Choose Your Data Connection" step.

Hope this helps.

|||

thanks for the guide..I'll try and see if it works in other computer..

Cheers,

Joelle

Change connection with T-SQL?

Hi,

I know I can right-click in query editor window and choose "Connection->Change Connection", but is there any T-SQL code or system stored proc I can use to dynamically change server connection in the middle of a script?

Thanks,

Dave

The sqlcmd command prompt utility which can also be run in Query Editor in sqlcmd mode.

See SQL Server 2005 Books Online topic for script information

Using the sqlcmd Utility

http://msdn2.microsoft.com/en-us/library/ms180944.aspx

E. Using sqlcmd to execute code on multiple instances

The following code in a file shows a script that connects to two instances. Notice the GO before the connection to the second instance.

:CONNECT <server>\,<instance1>

EXEC dbo.SomeProcedure

GO

:CONNECT <server>\,<instance2>

EXEC dbo.SomeProcedure

GO

See SQL Server 2005 Books Onlinetopic

Editing SQLCMD Scripts with Query Editor

http://msdn2.microsoft.com/en-gb/library/ms174187.aspx

Change CommandText

I have a report created on the reporting services and i
need to change the query that the report uses to retrieve
data dynamically based on a criteria page.
All I need is to change part of the RDL of the report
that includes the command text but i can't seem to find
how to do that. Can anyone please HELP!!!.
Thanks...
Omniams-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Omnia" <anonymous@.discussions.microsoft.com> wrote in message
news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>I have a report created on the reporting services and i
> need to change the query that the report uses to retrieve
> data dynamically based on a criteria page.
> All I need is to change part of the RDL of the report
> that includes the command text but i can't seem to find
> how to do that. Can anyone please HELP!!!.
>
> Thanks...
> Omnia|||Thanks very much for the link...
But I still have the following issues:
- Primarily the walkthrough does not work as it insists
on generating errors being unable to parse the query and
I can't manage to make it work...
- The other issue is that this will provide either
an 'ALL' or single selection but not multiple selection
which is what I primarily wanted to achieve, nevertheless
I do appreciate if you can help me further with the
syntax to make the walkthrough work...
Thanks again !!!
Omnia
>--Original Message--
>ms-
help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure
_data_v1_41ir.htm
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>>I have a report created on the reporting services and i
>> need to change the query that the report uses to
retrieve
>> data dynamically based on a criteria page.
>> All I need is to change part of the RDL of the report
>> that includes the command text but i can't seem to find
>> how to do that. Can anyone please HELP!!!.
>>
>> Thanks...
>> Omnia
>
>.
>|||Could you post your query and exact error message?
Multiple selection feature is in our wishlist for future releases.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Omnia" <anonymous@.discussions.microsoft.com> wrote in message
news:80f101c495a4$c5efd1a0$a501280a@.phx.gbl...
> Thanks very much for the link...
> But I still have the following issues:
> - Primarily the walkthrough does not work as it insists
> on generating errors being unable to parse the query and
> I can't manage to make it work...
> - The other issue is that this will provide either
> an 'ALL' or single selection but not multiple selection
> which is what I primarily wanted to achieve, nevertheless
> I do appreciate if you can help me further with the
> syntax to make the walkthrough work...
>
> Thanks again !!!
> Omnia
>
>
>
>>--Original Message--
>>ms-
> help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure
> _data_v1_41ir.htm
>>--
>>This posting is provided "AS IS" with no warranties, and
> confers no rights.
>>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>>I have a report created on the reporting services and i
>> need to change the query that the report uses to
> retrieve
>> data dynamically based on a criteria page.
>> All I need is to change part of the RDL of the report
>> that includes the command text but i can't seem to find
>> how to do that. Can anyone please HELP!!!.
>>
>> Thanks...
>> Omnia
>>
>>.|||Thank You very much for your assistance, the walkthrough
worked fine, and I will try to apply that around here...
>--Original Message--
>Could you post your query and exact error message?
>Multiple selection feature is in our wishlist for future
releases.
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:80f101c495a4$c5efd1a0$a501280a@.phx.gbl...
>> Thanks very much for the link...
>> But I still have the following issues:
>> - Primarily the walkthrough does not work as it insists
>> on generating errors being unable to parse the query
and
>> I can't manage to make it work...
>> - The other issue is that this will provide either
>> an 'ALL' or single selection but not multiple selection
>> which is what I primarily wanted to achieve,
nevertheless
>> I do appreciate if you can help me further with the
>> syntax to make the walkthrough work...
>>
>> Thanks again !!!
>> Omnia
>>
>>
>>
>>--Original Message--
>>ms-
help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure
>> _data_v1_41ir.htm
>>--
>>This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>>I have a report created on the reporting services and
i
>> need to change the query that the report uses to
>> retrieve
>> data dynamically based on a criteria page.
>> All I need is to change part of the RDL of the report
>> that includes the command text but i can't seem to
find
>> how to do that. Can anyone please HELP!!!.
>>
>> Thanks...
>> Omnia
>>
>>.
>
>.
>|||What is ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
Is that a URL ? Or is it
http://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
I can access neither.
Please help !|||ms-help: link should work if you have books online installed.
MSDN page is here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_data_v1_41ir.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"mangaraju venuturupalli" <mangaraju@.yahoo.com> wrote in message
news:784bfe81.0409141309.18bae5e6@.posting.google.com...
> What is
> ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
> Is that a URL ? Or is it
> http://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
> I can access neither.
> Please help !

Change Column Names

Can someone help with a query to change the column names in a table
Thanks
ChrisEXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4388A639-BA52-481D-9B69-A64420E5B59E@.microsoft.com...
> Can someone help with a query to change the column names in a table
> Thanks
> Chris

change a value in a field

I have a table with several fields (like who doesn't)
I would like a quick and easy way to change a field value
If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
tableE where fieldD = '3122007' "
I get a ton of results
What I want to do is if fieldD = 3122007, I want to change that value to
<NULL>
How do I do that?
You can use UPDATE statement like this:
UPDATE tableE
SET fieldD = NULL
WHERE fieldD = 3122007
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||you want it to happen during the query or have the database itself
change the stored value when there's an insert with a specific number?
On Mar 14, 11:31 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
> How do I do that?
|||"Johnfli" <john@.ivhs.us> wrote in message
news:uZEjPCpZHHA.1580@.TK2MSFTNGP05.phx.gbl...
>I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
>
> How do I do that?
>
If you mean change the field in the table, what Plamen posted is the way.
If you mean in the returned result set, you may want to look at CASE.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||worked perfectly!!
thank you
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:O%23pVOsqZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> You can use UPDATE statement like this:
> UPDATE tableE
> SET fieldD = NULL
> WHERE fieldD = 3122007
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

change a value in a field

I have a table with several fields (like who doesn't)
I would like a quick and easy way to change a field value
If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
tableE where fieldD = '3122007' "
I get a ton of results
What I want to do is if fieldD = 3122007, I want to change that value to
<NULL>
How do I do that?You can use UPDATE statement like this:
UPDATE tableE
SET fieldD = NULL
WHERE fieldD = 3122007
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||you want it to happen during the query or have the database itself
change the stored value when there's an insert with a specific number?
On Mar 14, 11:31 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
> How do I do that?|||"Johnfli" <john@.ivhs.us> wrote in message
news:uZEjPCpZHHA.1580@.TK2MSFTNGP05.phx.gbl...
>I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
>
> How do I do that?
>
If you mean change the field in the table, what Plamen posted is the way.
If you mean in the returned result set, you may want to look at CASE.
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||worked perfectly!!
thank you
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:O%23pVOsqZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> You can use UPDATE statement like this:
> UPDATE tableE
> SET fieldD = NULL
> WHERE fieldD = 3122007
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

change a value in a field

I have a table with several fields (like who doesn't)
I would like a quick and easy way to change a field value
If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
tableE where fieldD = '3122007' "
I get a ton of results
What I want to do is if fieldD = 3122007, I want to change that value to
<NULL>
How do I do that?You can use UPDATE statement like this:
UPDATE tableE
SET fieldD = NULL
WHERE fieldD = 3122007
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||you want it to happen during the query or have the database itself
change the stored value when there's an insert with a specific number?
On Mar 14, 11:31 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
> How do I do that?|||"Johnfli" <john@.ivhs.us> wrote in message
news:uZEjPCpZHHA.1580@.TK2MSFTNGP05.phx.gbl...
>I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
>
> How do I do that?
>
If you mean change the field in the table, what Plamen posted is the way.
If you mean in the returned result set, you may want to look at CASE.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||worked perfectly!!
thank you
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:O%23pVOsqZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> You can use UPDATE statement like this:
> UPDATE tableE
> SET fieldD = NULL
> WHERE fieldD = 3122007
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

Challenging Query / View

Hi,
We have a tough situation when we migrated one of the view from SQL Server
2000 to SQL Server 2005, we have seen CPU hogging around 100% when we access
this particulat view. It works fine in SQL 2000 but has CPU hogging in SQL
Server 2005. This is the view definition

SELECT CASE WHEN ARC.ContactID IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.ContactID ELSE ARC.ContactID END As ContactID,
CASE WHEN ARC.ContactName IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.ContactName ELSE ARC.ContactName END As ContactName,
CASE WHEN ARC.IsValidEmail IS NULL OR MSC.DataSourceCode = 'USER' THEN
CASE MSC.IsValidEmail WHEN 'Y' THEN MSC.ContactEmail ELSE '' END ELSE
CASE ARC.IsValidEmail WHEN 'Y' THEN ARC.ContactEmail ELSE '' END END As
ContactEmail,
CASE WHEN ARC.ContactType IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.ContactType ELSE ARC.ContactType END As ContactType,
CASE WHEN ARC.DataSourceCode IS NULL OR MSC.DataSourceCode = 'USER'
THEN MSC.DataSourceCode ELSE ARC.DataSourceCode END As DataSourceCode,
CASE WHEN ARC.IsValidEmail IS NULL OR MSC.DataSourceCode = 'USER'
THEN MSC.IsValidEmail ELSE ARC.IsValidEmail END As IsValidEmail,
CASE WHEN ARC.IsAllowUpdate IS NULL OR MSC.DataSourceCode = 'USER'
THEN MSC.IsAllowUpdate ELSE ARC.IsAllowUpdate END As IsAllowUpdate,
CASE WHEN ARC.IsAllowElectronicCommunication IS NULL OR
MSC.DataSourceCode = 'USER' THEN MSC.IsAllowElectronicCommunication ELSE
ARC.IsAllowElectronicCommunication END As IsAllowElectronicCommunication,
CASE WHEN MSC.ContactAddress1 IS NULL THEN ARC.ContactAddress1 ELSE
MSC.ContactAddress1 END As ContactAddress1,
CASE WHEN MSC.ContactAddress2 IS NULL THEN ARC.ContactAddress2 ELSE
MSC.ContactAddress2 END As ContactAddress2,
CASE WHEN MSC.ContactAddress3 IS NULL THEN ARC.ContactAddress3 ELSE
MSC.ContactAddress3 END As ContactAddress3,
CASE WHEN MSC.ContactFaxNbr IS NULL THEN ARC.ContactFaxNbr ELSE
MSC.ContactFaxNbr END As ContactFaxNbr,
CASE WHEN MSC.PhoneNbr1 IS NULL THEN ARC.PhoneNbr1 ELSE MSC.PhoneNbr1
END As PhoneNbr1,
CASE WHEN MSC.PhoneNbr2 IS NULL THEN ARC.PhoneNbr2 ELSE MSC.PhoneNbr2
END As PhoneNbr2,
CASE WHEN MSC.PhoneNbr3 IS NULL THEN ARC.PhoneNbr3 ELSE MSC.PhoneNbr3
END As PhoneNbr3,
CASE WHEN MSC.PhoneNbr4 IS NULL THEN ARC.PhoneNbr4 ELSE MSC.PhoneNbr4
END As PhoneNbr4,
CASE WHEN MSC.Position IS NULL THEN ARC.Position ELSE MSC.Position
END As Position,
CASE WHEN MSC.ContactComments IS NULL THEN ARC.ContactComments ELSE
MSC.ContactComments END As ContactComments,
CASE WHEN MSC.IsActive IS NULL THEN ARC.IsActive ELSE MSC.IsActive
END As IsActive,
CASE WHEN ARC.CreatedBy IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.CreatedBy ELSE ARC.CreatedBy END As CreatedBy,
CASE WHEN ARC.CreatedDate IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.CreatedDate ELSE ARC.CreatedDate END As CreatedDate,
CASE WHEN MSC.UpdatedBy IS NULL THEN ARC.UpdatedBy ELSE MSC.UpdatedBy
END As UpdatedBy,
CASE WHEN MSC.UpdatedDate IS NULL THEN ARC.UpdatedDate ELSE
MSC.UpdatedDate END As UpdatedDate
FROM dbo.dat_Contact MSC
FULL OUTER JOIN ARCommon.dbo.dat_Contact ARC
ON MSC.ContactID = ARC.ContactID

dbo.dat_Contact- This has around 50000 rows and ARCommon.dbo.dat_Contact
has around 40000 rows.

I dont have an option of getting rid of this view as it has been referrend
in more than 50 procs. Is there anyway I can rewrite the logic in this view
so that CPU time comes down in SQL Server 2005. I figured this CASE logic is
the one that is causing CPU hoggging..

Please help me ASAP.

Regards,
Murali

Ses, you are right, the case is consuming the most cpu in here. Did you chance (even for readability) the CASE constructs (Where possible) to ISNULL(someColumn,SomeOtherColumn) ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

This construct:

CASE WHEN MSC.PhoneNbr2 IS NULL THEN ARC.PhoneNbr2 ELSE MSC.PhoneNbr2
END As PhoneNbr2,

can be replaced with this:

Code Snippet

PhoneNbr2= coalesce ( MSC.PhoneNbr2, ARC.PhoneNbr2 )

|||

CASE expression and scalar expression operators in general should not consume the most CPU during query execution. This may be the case if you have some complex UDF in the expression for example. Otherwise it is very unlikely. So the problem might be somewhere else - like a parallel execution plan, out of date statistics, missing indexes etc.

Start first by comparing the actual execution plan between SQL Server 2000 (SET STATISTICS PROFILE ON) and SQL Server 2005 (SET XML STATISTICS PROFILE ON). Ensure that the indexes are same between the servers and the statistics are updated. By looking at the execution plan, you can confirm if there is a plan change & if so that is your problem. Once you have determined the change in the plan then it is easy to suggest the workaround.

If the plans are the same then look for any other problem - hardware configuration, disk subsystem etc.

|||This will not help. COALESCE is just short-form for the above CASE expression. It will get resolved to the same form actually. It doesn't help in terms of performance only readability.

Challenging Join question

I have a query in which I have 2 tables, but I join one of the tables to twice. I use the result to populate a drop down list

here is the data in table 1 (BaselineControlPairings)

ID SITEID BaselineID ControlID Description

2 4495 2 1 Jones - Jun 07

here is the data in table 2 (BaselineLog)

TESTID SITEID StartDate EndDate DataSetID

4 4495 2007-05-30 2007-06-07 1

5 4495 2007-06-09 2007-06-15 2

I want the query to do the following.. select all records from

BaselineControlPairings where the site id = 4495,

than get the associated BaselineLog record Start and end date where

BaselineControlPairings.BaselineID = BaselineLog.DataSetID

than get the associated BaselineLog record Start and end date where

BaselineControlPairings.ControlID = BaselineLog.DataSetID

Now I string the result together to be displayed.

Here is my query code

Code Snippet

SELECT BaselineControlPairings.TestID

,CONVERT(varchar(12), BaselineLog.StartDate, 110)as BLStart

,CONVERT(varchar(12), BaselineLog.EndDate, 110)as BLEnd

,CONVERT(varchar(12), BaselineLog_1.StartDate, 110)as CTRLStart

,CONVERT(varchar(12), BaselineLog_1.EndDate, 110)as CTRLEnd

,BaselineControlPairings.Description +' Baseline: '+CONVERT(varchar(12)

, BaselineLog.StartDate, 110)+' - '+CONVERT(varchar(12)

, BaselineLog.EndDate, 110)+' -- '+'Control: '+CONVERT(varchar(12)

, BaselineLog_1.StartDate, 110)+' - '+CONVERT(varchar(12), BaselineLog_1.EndDate, 110)as dates

FROM BaselineControlPairings INNERJOIN

BaselineLog AS BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID

INNERJOIN

BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID

WHERE(BaselineControlPairings.siteid = 4495)

here is the result of my query, which we know is wrong because it should contain only 1 result

Jones Jun 07 Baseline: 05-08-2007 - 05-14-2007 -- Control: 05-18-2007 - 05-25-2007
Jones Jun 07 Baseline: 06-09-2007 - 06-15-2007 -- Control: 05-18-2007 - 05-25-2007
Jones Jun 07 Baseline: 05-08-2007 - 05-14-2007 -- Control: 05-30-2007 - 06-07-2007
Jones Jun 07 Baseline: 06-09-2007 - 06-15-2007 -- Control: 05-30-2007 - 06-07-2007

I should only have one record in the result because I should only have the number of results that are in the

BaselineControlPairings with a matching SiteID

I know the issue is occuring in the join, but I have no idea on how to resolve it and I have spent numerous hours on this. any suggestions?

hi, your sample data and your desired output doesn't seem to match.

anyway here's my hunch,

SELECT BaselineControlPairings.ID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd
, BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12)
, BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12)
, BaselineLog.EndDate, 110) + ' -- ' + 'Control: ' + CONVERT(varchar(12)
, BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates
FROM BaselineControlPairings INNER JOIN
BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog_1.SiteID INNER JOIN
BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog.SiteID
WHERE (BaselineControlPairings.siteid = 4495)|||Thanks for the reply. I tried your solution, and although better its still broken. I am still getting some duplications but not as many. Also here is my actual code and data:

SELECT BaselineControlPairings.TestID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd

,BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12), BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog.EndDate, 110) + ' -- '
+ 'Control: ' + CONVERT(varchar(12), BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates

FROM BaselineControlPairings INNER JOIN
BaselineLog AS BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID INNER JOIN
BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
WHERE (BaselineControlPairings.siteid = 4495)

The data I am using:

BaselineLog Table

WhoProvisTestID SiteID StartDate EndDate Results DataSetID BLorCTRL

NULL 55 4495 2007-05-27 2007-06-04 NULL 5 0

NULL 56 4495 2007-05-27 2007-06-04 NULL 2 0

NULL 57 4495 2007-05-27 2007-06-04 NULL 3 1

NULL 58 905 2007-05-28 2007-05-31 NULL 3 0

NULL 59 905 2007-05-28 2007-05-31 NULL 1 0

NULL 60 907 2007-05-27 2007-05-29 NULL 17 0

BaselineControlPairings Table

TestID SiteID BaselineID ControlID Description

-- -- -- -- -

25 905 3 1 hgc

26 4495 5 3 df

27 4495 2 5 df

against thes tables I should receive 1 result for the query using ID 905 and 2 for 4495, BUT
for 905 I receive 2 rows both for testID 25, yet there is only 1 test id.

here are the results for 905:
TestID BLStart BLEnd CTRLStart CTRLEnd dates
--
25 05-27-2007 06-04-2007 05-28-2007 05-31-2007 hgc Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-28-2007 - 05-31-2007
25 05-28-2007 05-31-2007 05-28-2007 05-31-2007 hgc Baseline: 05-28-2007 - 05-31-2007 -- Control: 05-28-2007 - 05-31-2007

and for 4495 I should get 2 rows, 1 for test id 26 and one for id 27. Yet I get (2) for 26 and one for 27.

ARGH ! ! !

Results for id 4495
TestID BLStart BLEnd CTRLStart CTRLEnd dates
--
26 05-27-2007 06-04-2007 05-27-2007 06-04-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-27-2007 - 06-04-2007
26 05-27-2007 06-04-2007 05-28-2007 05-31-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-28-2007 - 05-31-2007
27 05-27-2007 06-04-2007 05-27-2007 06-04-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-27-2007 - 06-04-2007

any help is greatly appreciated!|||hi

i'm not sure on what you've meant was still broken, but based on your test data and expected result, if you add BaselineControlPairings.SiteID = BaselineLog_1.SiteID and BaselineControlPairings.SiteID = BaselineLog.SiteID respectively on your joins would result to what you're expecting.

select *
into #BaseLog
from (
select NULL as WhoProvis
, 55 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 5 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 56 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 2 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 57 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 3 as DataSetID
, 1 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 58 as TestID
, 905 as SiteID
, '2007-05-28' as StartDate
, '2007-06-31' as EndDate
, NULL as Results
, 3 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 59 as TestID
, 905 as SiteID
, '2007-05-28' as StartDate
, '2007-06-31' as EndDate
, NULL as Results
, 1 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 60 as TestID
, 907 as SiteID
, '2007-05-27' as StartDate
, '2007-06-29' as EndDate
, NULL as Results
, 17 as DataSetID
, 0 as BLorCTRL
) BaseLog

select *
into #BaselineControlPairings
from (
select 25 as TestID
, 905 as SiteID
, 3 as BaselineID
, 1 as ControlID
, 'hgc' as Description
UNION ALL
select 26 as TestID
, 4495 as SiteID
, 5 as BaselineID
, 3 as ControlID
, 'df' as Description
UNION ALL
select 27 as TestID
, 4495 as SiteID
, 2 as BaselineID
, 5 as ControlID
, 'df' as Description
) BaselineControlPairings

SELECT BaselineControlPairings.TestID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd
, BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12), BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog.EndDate, 110) + ' -- '
+ 'Control: ' + CONVERT(varchar(12), BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates
FROM #BaselineControlPairings BaselineControlPairings INNER JOIN
#BaseLog BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog_1.SiteID INNER JOIN
#BaseLog BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog.SiteID
WHERE (BaselineControlPairings.siteid = 905)

SELECT BaselineControlPairings.TestID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd
, BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12), BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog.EndDate, 110) + ' -- '
+ 'Control: ' + CONVERT(varchar(12), BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates
FROM #BaselineControlPairings BaselineControlPairings INNER JOIN
#BaseLog BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog_1.SiteID INNER JOIN
#BaseLog BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog.SiteID
WHERE (BaselineControlPairings.siteid = 4495)

drop table #BaseLog
drop table #BaselineControlPairings|||Thanks, this fixed it... I think the mistake I was making was not writing to a temp file.|||hi, i'm sorry i don't think that writing to a temp file made the difference. maybe you might have misplaced the ON filter for the SiteID

Challenge: nested FOR XML EXPLICIT query with data not in target t

I am trying to create a nested FOR XML EXPLICIT query where the majority of the data used in the query does not come from the target table being referenced.
Actually, only a few fields will come from the target table and when those fields appear in the data I want the FOR XML EXPLICIT query to duplicate the entire document structure for each time this happens only changing those elements to which the data cor
responds to (since all of the other elements are static anyway).
What I want is this:
<docs>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comm
ent>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6283</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comm
ent>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
</docs>
However what I am getting is this:
<docs>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><standard><ITEM>6283</ITEM></standard><standard><ITE
M>6284</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
</docs>
The <standard> tag is repeating multiple times in a single document where I want only 1 <standard> tag to appear a document. Additional <standard> tags should trigger additional documents to be generated.
Here is the FOR XML EXPLICIT query I am currently using:
SET NOCOUNT ON
SELECT
1 AS Tag
,NULL AS Parent
,NULL AS [docs!1]
,NULL AS [doc!2!ordering!hide]
,NULL AS [doc!2]
,NULL AS [settings!3!ordering!hide]
,NULL AS [settings!3!doctype!element]
,NULL AS [settings!3!language!element]
,NULL AS [settings!3!type!element]
,NULL AS [settings!3!branch!element]
,NULL AS [settings!3!printer!element]
,NULL AS [standard!4!ordering!hide]
,NULL AS [standard!4!ITEM!element]
,NULL AS [multiused!5!ordering!hide]
,NULL AS [multiused!5]
,NULL AS [item!6!ordering!hide]
,NULL AS [item!6!AttachmentID!element]
,NULL AS [item!6!Comment!element]
,NULL AS [item!6!DocName!element]
,NULL AS [item!6!NbrOfPages!element]
UNION ALL
SELECT
2 AS Tag
,1 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
3 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,'Letter_w_Attachments'
,'ENGLISH'
,'CoverLetter'
,'Mailroom'
,'PRT1'
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
4 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,ID
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
6 AS Tag
,5 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,'?'
,'?'
,'?'
,'?'
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
ORDER BY
[doc!2!ordering!hide]
,[settings!3!ordering!hide]
,[standard!4!ordering!hide]
,[multiused!5!ordering!hide]
,[item!6!ordering!hide]
FOR XML EXPLICIT
Any assistance would be most helpful.
Thank you.
The solution can be found by envisioning how the relation that is being
aggregated needs to look like:
1 row for every element. Thus if you want as many documents as <standard>
elements, you need to generate more than one and use some common id to group
them with their children.
Try for example (I simplified the query a bit without loss of
functionality):
SELECT
1 AS Tag
,NULL AS Parent
,ID AS [docs!1!id!hide]
,NULL AS [doc!2!ordering!hide]
,NULL AS [settings!3!ordering!hide]
,NULL AS [settings!3!doctype!element]
,NULL AS [settings!3!language!element]
,NULL AS [settings!3!type!element]
,NULL AS [settings!3!branch!element]
,NULL AS [settings!3!printer!element]
,NULL AS [standard!4!ITEM!element]
,NULL AS [multiused!5!ordering!hide]
--,NULL AS [item!6!ordering!hide]
,NULL AS [item!6!AttachmentID!element]
,NULL AS [item!6!Comment!element]
,NULL AS [item!6!DocName!element]
,NULL AS [item!6!NbrOfPages!element]
FROM vConsolidationPrinting
UNION ALL
SELECT
2 AS Tag
,1 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
3 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,ID
--settings
,'Letter_w_Attachments'
,'ENGLISH'
,'CoverLetter'
,'Mailroom'
,'PRT1'
--standard
,NULL
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
4 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,ID
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,ID
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
6 AS Tag
,5 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,ID
--item
,'?'
,'?'
,'?'
,'?'
FROM vConsolidationPrinting
/*
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
*/
ORDER BY
[docs!1!id!hide],[doc!2!ordering!hide],Tag,[settings!3!ordering!hide]
,[standard!4!ITEM!element]
,[multiused!5!ordering!hide]
, [item!6!AttachmentID!element]
FOR XML EXPLICIT
HTH
Michael
PS: BTW, this can be done much easier in SQL Server 2005 with the new PATH
mode. Here it is:
SELECT
'Letter_w_Attachments' as "settings/doctype",
'ENGLISH' as "settings/language",
'CoverLetter' as "settings/type",
'Mailroom' as "settings/branch",
'PRT1' as "settings/printer",
ID as "standard/ITEM",
'?' as "multiused/item/AttachmentID",
'?' as "multiused/item/Comment",
'?' as "multiused/item/DocName",
'?' as "multiused/item/NbrOfPages"
FROM vConsolidationPrinting
FOR XML Path('doc'), ROOT('docs')
"JRutberg" <JRutberg@.discussions.microsoft.com> wrote in message
news:303784BC-7CEC-4AA2-A1AD-1CAF38C1CE1D@.microsoft.com...
>I am trying to create a nested FOR XML EXPLICIT query where the majority of
>the data used in the query does not come from the target table being
>referenced.
> Actually, only a few fields will come from the target table and when those
> fields appear in the data I want the FOR XML EXPLICIT query to duplicate
> the entire document structure for each time this happens only changing
> those elements to which the data corresponds to (since all of the other
> elements are static anyway).
> What I want is this:
> <docs>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Co
mment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6283</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Co
mment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> </docs>
> However what I am getting is this:
> <docs>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><standard><ITEM>6283</ITEM></standard><standard><I
TEM>6284</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> </docs>
> The <standard> tag is repeating multiple times in a single document where
> I want only 1 <standard> tag to appear a document. Additional <standard>
> tags should trigger additional documents to be generated.
> Here is the FOR XML EXPLICIT query I am currently using:
>
> SET NOCOUNT ON
> SELECT
> 1 AS Tag
> ,NULL AS Parent
> ,NULL AS [docs!1]
> ,NULL AS [doc!2!ordering!hide]
> ,NULL AS [doc!2]
> ,NULL AS [settings!3!ordering!hide]
> ,NULL AS [settings!3!doctype!element]
> ,NULL AS [settings!3!language!element]
> ,NULL AS [settings!3!type!element]
> ,NULL AS [settings!3!branch!element]
> ,NULL AS [settings!3!printer!element]
> ,NULL AS [standard!4!ordering!hide]
> ,NULL AS [standard!4!ITEM!element]
> ,NULL AS [multiused!5!ordering!hide]
> ,NULL AS [multiused!5]
> ,NULL AS [item!6!ordering!hide]
> ,NULL AS [item!6!AttachmentID!element]
> ,NULL AS [item!6!Comment!element]
> ,NULL AS [item!6!DocName!element]
> ,NULL AS [item!6!NbrOfPages!element]
> UNION ALL
> SELECT
> 2 AS Tag
> ,1 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 3 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,'Letter_w_Attachments'
> ,'ENGLISH'
> ,'CoverLetter'
> ,'Mailroom'
> ,'PRT1'
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 4 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,ID
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM vConsolidationPrinting
> UNION ALL
> SELECT
> 5 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 6 AS Tag
> ,5 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,'?'
> ,'?'
> ,'?'
> ,'?'
> UNION ALL
> SELECT
> 5 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ORDER BY
> [doc!2!ordering!hide]
> ,[settings!3!ordering!hide]
> ,[standard!4!ordering!hide]
> ,[multiused!5!ordering!hide]
> ,[item!6!ordering!hide]
> FOR XML EXPLICIT
>
> Any assistance would be most helpful.
> Thank you.
>

Centralizing Two Stored Procedure Queries (one using Cursor Ou

Alejandro,
Sorry, one more question: In your example, would there be any way to return
only a subset of the fields in the stored proc query. For example, if you
wanted a temporary table with only the orderid field. Like this:
create table #t (orderid int)
insert into #t(orderid)
exec dbo.p1 @.sd, @.ed
I'll need it like this because my store proc "spTrucker" has many, many
fields, and I only need about 4 or 5 of them in the cursor.
Thanks again,
John
"Alejandro Mesa" wrote:
> John,
> You do not need a cursor output parameter. You can grab the result of the
sp
> "spTrucker" in sp "spDistinctCodes". See "insert into ... exec ..." in BOL
.
> Example:
> use northwind
> go
> create procedure dbo.p1
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> select
> orderid, orderdate, customerid
> from
> dbo.orders
> where
> orderdate >= convert(char(8), @.sd, 112)
> and orderdate < convert(char(8), dateadd(day, 1, @.ed), 112)
> return @.@.error
> go
> create procedure dbo.p2
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> declare @.orderid int
> declare @.orderdate varchar(25)
> declare @.customerid nchar(5)
> create table #t (orderid int, orderdate datetime, customerid nchar(5))
> insert into #t(orderid, orderdate, customerid)
> exec dbo.p1 @.sd, @.ed
> declare my_cursor cursor local fast_forward
> for
> select orderid, orderdate, customerid
> from #t
> order by orderdate
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.orderid, @.orderdate, @.customerid
> if @.@.error != 0 or @.@.fetch_status != 0 break
> raiserror('%d %s %s', 10, 1, @.orderid, @.orderdate, @.customerid) with nowa
it
> end
> close my_cursor
> deallocate my_cursor
> go
> exec dbo.p2 '19970701', '19970731'
> go
> drop procedure p2, p1
> go
> How to share data between stored procedures
> http://www.sommarskog.se/share_data.html
>
> AMB
>
> "John Walker" wrote:
>John,
The ddl for the temporary table need to match all columns returned by the
sp. You can not grab just a subset.
AMB
"John Walker" wrote:
> Alejandro,
> Sorry, one more question: In your example, would there be any way to retu
rn
> only a subset of the fields in the stored proc query. For example, if you
> wanted a temporary table with only the orderid field. Like this:
> create table #t (orderid int)
> insert into #t(orderid)
> exec dbo.p1 @.sd, @.ed
> I'll need it like this because my store proc "spTrucker" has many, many
> fields, and I only need about 4 or 5 of them in the cursor.
> Thanks again,
> John
> "Alejandro Mesa" wrote:
>

Cellset.open hangs, query works fine in Management Studio?

Hi All,

We are experiencing a very strange Cellset behaviour.

Trying to open a cellset in VBA (same in VB6) in Excel 2003, using ADOMD.Catalog, accessing a AS2005SP2, the system 'sometimes' hangs, depending on the 'dynamic' MDX query string. While the query works fine in the SQL Management Studio.

For example, this MDX works fine:

select descendants {[Measures].[NumberOfA]} on columns, [Time].[Month].[200502] on rows

from [DWH]
where {([Client].[Hierarchy].[Group].&[G000879])}

This one causes Excel (and VB6) to freeze, but nicely returns a result in SQL Management Studio!!!?

select descendants {[Measures].[NumberOfA]} on columns, [Time].[Month].[200501] on rows

from [DWH]
where {([Client].[Hierarchy].[Group].&[G000879])}

There is nothing wrong with the data, it's more connection related (I expect...)?

Does somebody have any advice?

PS: we have msxml6,asoledb9 installed

It is a firewall issue. See also: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1558402&SiteID=1

Cheers,

Tom

CDC not tracking changes

Hi

I'm trying to get CDC going, it works however when i query the LSN using the functions i get no changes. The min and max LSN returns null. SQL agent is running, db is on full recovery model etc.

any ideas ?

thanks

CDC is a SQL Server 2008 feature. Not to mention nothing to do with SSIS. Can you refine your issue so that we can redirect your question appropriately? This is a SQL Server 2005 forum for the SQL Server Integration Services tool.

|||

sorry Phil, but when i search on the forums under "change data capture" - only SSIS forums come up. I'll ask the question internally - i'm in Microsoft. thanks for your troubles.

|||

Sqlgoof wrote:

sorry Phil, but when i search on the forums under "change data capture" - only SSIS forums come up. I'll ask the question internally - i'm in Microsoft. thanks for your troubles.

Try the SQL Server 2008 Data Warehousing forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1617&SiteID=1

CDATA & FOR XML PATH

Hi,
Using a nested FOR XML EXPLICIT statement within an FOR XML PATH query is it
possible to create a node creating cdata?
For example, the statement;
select
pt_description as 'description',
(select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
from property where pt_id = 8957627 for xml explicit, type)
from pt
where pt_id = 9999999
for xml path ('details'), root('info')
Produces;
<info>
<details>
<description>text...</description>
<description>text...</description>
</details>
</info>
Using the nested for xml explicit I was hoping to be able to display the
second description node as;
<description><![CDATA[text...]]></description>
When the 'type' directive is specified it ignores the fact it should contain
cdata.
Am I missing something?...Is this possible to do?
Thanks
Pete
Hi Pete
If you use a FOR XML expression with the TYPE directive, you get an XML
datatype. And the XML datatype (since it is based on the XQuery Datamodel)
does not preserve the CDATA section information.
So the only way to preserve CDATA is by using EXPLICIT mode without TYPE
directive at the top.
Now, I would like to better understand why you want to generate a CDATA
section in the first place. The only impact it has is to allow people to
author certain XML content without having to explicitly entitize characters
such as <, & etc.. Why would that be important during serializing a FOR XML
result?
Thanks
Michael
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:eRrOk2kdFHA.3452@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Using a nested FOR XML EXPLICIT statement within an FOR XML PATH query is
> it possible to create a node creating cdata?
> For example, the statement;
> select
> pt_description as 'description',
> (select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
> from property where pt_id = 8957627 for xml explicit, type)
> from pt
> where pt_id = 9999999
> for xml path ('details'), root('info')
> Produces;
> <info>
> <details>
> <description>text...</description>
> <description>text...</description>
> </details>
> </info>
> Using the nested for xml explicit I was hoping to be able to display the
> second description node as;
> <description><![CDATA[text...]]></description>
> When the 'type' directive is specified it ignores the fact it should
> contain cdata.
> Am I missing something?...Is this possible to do?
> Thanks
> Pete
>
|||Hi Michael,
Thanks for your reply. My belief is that the use of the cdata directive is
necessary as I have html content stored in the database and need to be able
to be rendered as html when producing an xslt transformation. is there an
alternative way that you know of to do this in SQL Server 2005.
When I try using the same statement;
select
pt_description as 'description',
(select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
from property where pt_id = 8957627 for xml explicit)
from pt
where pt_id = 9999999
for xml path ('details'), root('info')
without using the 'TYPE' directive the result displays as follows;
<info>
<details>
<description>text...</description>
<description><![CDATA[text...]]></description>
</details>
</info>
How can I ensure the both the html and the element containing the cdata are
rendered correctly (eg. <element> rather than '<element>...')?...Is
there any alternative way to do this that I'm missing?
Thanks
Pete
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uxdEMepdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi Pete
> If you use a FOR XML expression with the TYPE directive, you get an XML
> datatype. And the XML datatype (since it is based on the XQuery Datamodel)
> does not preserve the CDATA section information.
> So the only way to preserve CDATA is by using EXPLICIT mode without TYPE
> directive at the top.
> Now, I would like to better understand why you want to generate a CDATA
> section in the first place. The only impact it has is to allow people to
> author certain XML content without having to explicitly entitize
> characters such as <, & etc.. Why would that be important during
> serializing a FOR XML result?
> Thanks
> Michael
> "Pete Roberts" <peter.roberts@.vebra.com> wrote in message
> news:eRrOk2kdFHA.3452@.TK2MSFTNGP10.phx.gbl...
>
|||XSLT should not make a difference between the CDATA section and content that
just had been entitized.
Did you try to take your original output (with the TYPE directive) and pass
it through your XSLT style sheet?
Alternatively, if you need to preserve your CDATA section, you need to use a
top-level EXPLICIT mode query:
select 1 as Tag, NULL as Parent, pt_description as
[details!1!description!element],pt_description as
[details!1!description!cdata]
from property where pt_id = 8957627
for xml explicit, root('info')
Best regards
Michael
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:eHmIDS0dFHA.1612@.tk2msftngp13.phx.gbl...
> Hi Michael,
> Thanks for your reply. My belief is that the use of the cdata directive
> is necessary as I have html content stored in the database and need to be
> able to be rendered as html when producing an xslt transformation. is
> there an alternative way that you know of to do this in SQL Server 2005.
> When I try using the same statement;
> select
> pt_description as 'description',
> (select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
> from property where pt_id = 8957627 for xml explicit)
> from pt
> where pt_id = 9999999
> for xml path ('details'), root('info')
> without using the 'TYPE' directive the result displays as follows;
> <info>
> <details>
> <description>text...</description>
> <description><![CDATA[text...]]></description>
> </details>
> </info>
> How can I ensure the both the html and the element containing the cdata
> are rendered correctly (eg. <element> rather than
> '<element>...')?...Is there any alternative way to do this that I'm
> missing?
> Thanks
> Pete
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uxdEMepdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>