Showing posts with label design. Show all posts
Showing posts with label design. Show all posts

Sunday, March 11, 2012

Convert text to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.

Thursday, March 8, 2012

convert sql tables with parent-child keys relating a nested structure into a nested x

I have 5 tables in SQL Server. Each with the following design and a sample
chain of the relationships from the root (WRL - World)
UUS is the 'Code' of the first table and it is the 'Parent' value of the
second table, etc.
Parent varchar 3
Name varchar 60
Code varchar 3
WRL United States UUS <- UUS California UCA <- UCA North Coast UNC <- UNC
Sonoma County USO <- USO Russian River Valley URR
If I have 6 tables with these relationships from root to these vineyard
appellation locations, how could I convert this nested, hiearchical data
from SQL Server tables into a nested XML file? I will be using the XML
file as input for a Winforms Treeview display of this data.
Thank you! -gregDepending on the complexity of the relationships and the XML you want, look
either into FOR XML AUTO (simple but limited) or FOR XML EXPLICIT (more
complex but powerful). If you have problems, there are people here that will
be able to help you for a good bottle :-).
Also, SQL Server 2005 will have a new FOR XML PATH mode that gives you
simplicity and power at the same time...
Best regards
Michael
"Hazz" <hazz@.nospameroosonic.net> wrote in message
news:eVYdakYMFHA.2580@.TK2MSFTNGP09.phx.gbl...
>I have 5 tables in SQL Server. Each with the following design and a sample
>chain of the relationships from the root (WRL - World)
> UUS is the 'Code' of the first table and it is the 'Parent' value of the
> second table, etc.
> Parent varchar 3
> Name varchar 60
> Code varchar 3
> WRL United States UUS <- UUS California UCA <- UCA North Coast UNC <-
> UNC Sonoma County USO <- USO Russian River Valley URR
> If I have 6 tables with these relationships from root to these vineyard
> appellation locations, how could I convert this nested, hiearchical data
> from SQL Server tables into a nested XML file? I will be using the XML
> file as input for a Winforms Treeview display of this data.
> Thank you! -greg
>|||Michael,
From what I have read since I posted this about FOR XML EXPLICIT, I think it
is time for me to step into the comfort of SQL Server 2005 and give it a
test ride.Until then what I did was this. I built the Treeview after
creating one table as per
http://www.wwwcoder.com/main/parent...68/default.aspx
then converted that recursively produced treeview node structure into the
necessary nested xml per Derek Harmon's excellent C# methods which I
massaged into vb.net for this app.
I do like this idea of the bottle bartering process!! Thank you Michael for
validating what I was finding about SQL possibilities. -greg
Here is Derek's code since I can't send a link to it;
using System;
using System.IO;
using System.Windows.Forms;
using System.Xml;
// . . .
private void SaveNode( XmlElement eNodes, TreeNodeCollection tnc)
{
foreach( TreeNode n in tnc)
this.SaveNode( eNodes, n);
}
private void SaveNode( XmlElement eNodes, TreeNode n )
{
XmlDocument nodeFactory = eNodes.OwnerDocument;
XmlElement child = nodeFactory.CreateElement( "Node");
XmlAttribute text = nodeFactory.CreateAttribute( "Text");
text.Value = n.Text;
child.Attributes.Append( text);
if ( null != n.Nodes && n.Nodes.Count > 0 )
{
XmlElement grandchildren = nodeFactory.CreateElement( "Nodes");
child.AppendChild( grandchildren);
this.SaveNode( grandchildren, n.Nodes);
}
eNodes.AppendChild( child);
}
private void btnSave_Click(object sender, System.EventArgs e)
{
XmlDocument doc = new XmlDocument( );
XmlElement eDoc = doc.CreateElement( "TreeView");
if ( null != this.treeView1.Nodes && this.treeView1.Nodes.Count > 0 )
{
XmlElement eNodes = doc.CreateElement( "Nodes");
this.SaveNode( eNodes, this.treeView1.Nodes);
eDoc.AppendChild( eNodes);
}
doc.AppendChild( eDoc);
XmlTextWriter sink = new XmlTextWriter( "../../tree.xml",
System.Text.Encoding.UTF8);
try
{
doc.WriteTo( sink);
sink.Flush( );
}
finally
{
sink.Close( );
}
}
****************************************
*********************
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umgl7SdMFHA.904@.tk2msftngp13.phx.gbl...
> Depending on the complexity of the relationships and the XML you want,
> look either into FOR XML AUTO (simple but limited) or FOR XML EXPLICIT
> (more complex but powerful). If you have problems, there are people here
> that will be able to help you for a good bottle :-).
> Also, SQL Server 2005 will have a new FOR XML PATH mode that gives you
> simplicity and power at the same time...
> Best regards
> Michael
> "Hazz" <hazz@.nospameroosonic.net> wrote in message
> news:eVYdakYMFHA.2580@.TK2MSFTNGP09.phx.gbl...
>