Edit a SQL Server 2005 XML column in ASP.NET

Hi. I've been trying to figure this out for a couple ws now and have =
posted various pleas for help, but so far I've not been able to find the =
magic I need to make this happen. This can't be too uncommon of a =
scenario. Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps =
actually working that they can post for me to examine/review?
--=20
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )On 5 23 , 7 40 , "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> Hi. I've been trying to figure this out for a couple ws now and have posted var
ious pleas for help, but so far I've not been able to find the magic I need to make
this happen. This can't be too uncommon of a scenario. Certainly someone has done th
is
before.
> What I'm trying to do is the following:
> 1. Pull XML from an MS SQL Server 2005 XML column.
> 2. Load it into a DataSet (unless there's a better way).
> 3. Bind it to some ASP.NET control(s) for editing.
> 4. Allow user to editing of the data in the web page.
> 5. Get the updated data from the control(s) as XML
> 6. Post it back to the SQL XML column.
> I can do 1-4 above... but for the life of me can't get step 5 to work.
> Does someone have a simple bare-bones example of the above 6 steps actuall
y working that they can post for me to examine/review?
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
4. Allow user to editing of the data in the web page.
Here i think we can use javascript DOM to modify xml document
according to user's edit.
5. Get the updated data from the control(s) as XML
And now we can get the xml document of step 4.
6. Post it back to the SQL XML column.
:) here have nothing to consider.
Hope this can help.|||On 5 23 , 7 40 , "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> Hi. I've been trying to figure this out for a couple ws now and have posted var
ious pleas for help, but so far I've not been able to find the magic I need to make
this happen. This can't be too uncommon of a scenario. Certainly someone has done th
is
before.
> What I'm trying to do is the following:
> 1. Pull XML from an MS SQL Server 2005 XML column.
> 2. Load it into a DataSet (unless there's a better way).
> 3. Bind it to some ASP.NET control(s) for editing.
> 4. Allow user to editing of the data in the web page.
> 5. Get the updated data from the control(s) as XML
> 6. Post it back to the SQL XML column.
> I can do 1-4 above... but for the life of me can't get step 5 to work.
> Does someone have a simple bare-bones example of the above 6 steps actuall
y working that they can post for me to examine/review?
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
4. Allow user to editing of the data in the web page.
Here i think we can use javascript DOM to modify xml document
according to user's edit.
5. Get the updated data from the control(s) as XML
And now we can get the xml document of step 4.
6. Post it back to the SQL XML column.
:) here have nothing to consider.
Hope this can help.|||On 5 23 , 7 40 , "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> Hi. I've been trying to figure this out for a couple ws now and have posted var
ious pleas for help, but so far I've not been able to find the magic I need to make
this happen. This can't be too uncommon of a scenario. Certainly someone has done th
is
before.
> What I'm trying to do is the following:
> 1. Pull XML from an MS SQL Server 2005 XML column.
> 2. Load it into a DataSet (unless there's a better way).
> 3. Bind it to some ASP.NET control(s) for editing.
> 4. Allow user to editing of the data in the web page.
> 5. Get the updated data from the control(s) as XML
> 6. Post it back to the SQL XML column.
> I can do 1-4 above... but for the life of me can't get step 5 to work.
> Does someone have a simple bare-bones example of the above 6 steps actuall
y working that they can post for me to examine/review?
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
4. Allow user to editing of the data in the web page.
Here i think we can use javascript DOM to modify xml document
according to user's edit.
5. Get the updated data from the control(s) as XML
And now we can get the xml document of step 4.
6. Post it back to the SQL XML column.
:) here have nothing to consider.
Hope this can help.|||Greg Collins [Microsoft MVP] wrote:
> Hi. I've been trying to figure this out for a couple ws now and have posted var
ious pleas for help, but so far I've not been able to find the magic I need to make
this happen. This can't be too uncommon of a scenario. Certainly someone has done th
is
before.
> What I'm trying to do is the following:
> 1. Pull XML from an MS SQL Server 2005 XML column.
> 2. Load it into a DataSet (unless there's a better way).
> 3. Bind it to some ASP.NET control(s) for editing.
> 4. Allow user to editing of the data in the web page.
> 5. Get the updated data from the control(s) as XML
> 6. Post it back to the SQL XML column.
> I can do 1-4 above... but for the life of me can't get step 5 to work.
> Does someone have a simple bare-bones example of the above 6 steps actuall
y working that they can post for me to examine/review?
>
Hi, Greg
check out this:
http://msdn2.microsoft.com/en-us/library/58a18dwa.aspx
What's exactly your case - you need to serialize the whole dataset,
or a given column ?
Regards,
Petar Atanasov
http://a-wake.net|||Use a table for the values on the form instead of XML? :-))
TheSQLGuru
President
Indicium Resources, Inc.
"Greg Collins [Microsoft MVP]" <gcollins_AT_msn_DOT_com> wrote in message
news:ussV38SnHHA.4316@.TK2MSFTNGP06.phx.gbl...
Hi. I've been trying to figure this out for a couple ws now and have
posted various pleas for help, but so far I've not been able to find the
magic I need to make this happen. This can't be too uncommon of a scenario.
Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps actually
working that they can post for me to examine/review?
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )|||What about using the WriteXml method of the DataSet after the changes are
made to it? Does that do anything useful?
"Greg Collins [Microsoft MVP]" <gcollins_AT_msn_DOT_com> wrote in message
news:ussV38SnHHA.4316@.TK2MSFTNGP06.phx.gbl...
Hi. I've been trying to figure this out for a couple ws now and have
posted various pleas for help, but so far I've not been able to find the
magic I need to make this happen. This can't be too uncommon of a scenario.
Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps actually
working that they can post for me to examine/review?
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )

Edit a SQL Server 2005 XML column in ASP.NET

Hi. I've been trying to figure this out for a couple weeks now and have posted various pleas for help, but so far I've not been able to find the magic I need to make this happen. This can't be too uncommon of a scenario. Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps actually working that they can post for me to examine/review?
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )
Greg Collins [Microsoft MVP] wrote:
> Hi. I've been trying to figure this out for a couple weeks now and have posted various pleas for help, but so far I've not been able to find the magic I need to make this happen. This can't be too uncommon of a scenario. Certainly someone has done this before.
> What I'm trying to do is the following:
> 1. Pull XML from an MS SQL Server 2005 XML column.
> 2. Load it into a DataSet (unless there's a better way).
> 3. Bind it to some ASP.NET control(s) for editing.
> 4. Allow user to editing of the data in the web page.
> 5. Get the updated data from the control(s) as XML
> 6. Post it back to the SQL XML column.
> I can do 1-4 above... but for the life of me can't get step 5 to work.
> Does someone have a simple bare-bones example of the above 6 steps actually working that they can post for me to examine/review?
>
Hi, Greg
check out this:
http://msdn2.microsoft.com/en-us/library/58a18dwa.aspx
What's exactly your case - you need to serialize the whole dataset,
or a given column ?
Regards,
Petar Atanasov
http://a-wake.net
|||Use a table for the values on the form instead of XML? :-))
TheSQLGuru
President
Indicium Resources, Inc.
"Greg Collins [Microsoft MVP]" <gcollins_AT_msn_DOT_com> wrote in message
news:ussV38SnHHA.4316@.TK2MSFTNGP06.phx.gbl...
Hi. I've been trying to figure this out for a couple weeks now and have
posted various pleas for help, but so far I've not been able to find the
magic I need to make this happen. This can't be too uncommon of a scenario.
Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps actually
working that they can post for me to examine/review?
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )
|||What about using the WriteXml method of the DataSet after the changes are
made to it? Does that do anything useful?
"Greg Collins [Microsoft MVP]" <gcollins_AT_msn_DOT_com> wrote in message
news:ussV38SnHHA.4316@.TK2MSFTNGP06.phx.gbl...
Hi. I've been trying to figure this out for a couple weeks now and have
posted various pleas for help, but so far I've not been able to find the
magic I need to make this happen. This can't be too uncommon of a scenario.
Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps actually
working that they can post for me to examine/review?
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )

EASY! Trim string?

I have values in a matrix takin too much space. Since they are just used as the matrix column header, cant i trim it or something. To display just the first 3 letters of the the attribute, and not the whole string? Whats the code for that?

=Fields!Collision_Type.Value thats my field right now.

Please help! thanks!

=LEFT(Fields!Collision_Type.Value,1,3) I believe. Or some variant of that syntax.|||

I believe it should be Mid(Fields!Collision_Type.Value,1,3)

|||

Yes, you can change it to this to always show the first 3 characters:

=Left(Fields!Collision_Type.Value, 3)

Jarret

|||Thanks for the quick response, this worked great!|||This one worked as well, hum i wonder what the difference is? But they both seem to display the same. Thanks!|||

There is no difference really, the Mid function allows you to give it a starting position and how many characters to return.

Jarret

Easy way to remove international alphabet from all rows?

Is there an easy way to loop through all rows and remove all
international alphabet characters from a column in a table, for example
remove German umlauts "" and convert them to a simple "u".
Thanks,
lqSee the following thread:
http://groups-beta.google.com/group...b45abaa5cf410b8

Razvan|||OK< thanks on that. How about keeping out these characters in the first
place?|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Is there an easy way to loop through all rows and remove all
> international alphabet characters from a column in a table, for example
> remove German umlauts "" and convert them to a simple "u".

Here is a much smarter version than the one in the linkn Razvan foun

create table T (
s nvarchar(20)
)
insert into T values ('cre')
insert into T values ('pt de fois gras')
insert into T values ('HLNE MELANON')

update T
set s = convert(varchar(20), s) COLLATE Cyrillic_General_CS_AS
go
select * from T
go
drop table T

This assumes that your data only has Latin characters.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> OK< thanks on that. How about keeping out these characters in the first
> place?

I don't know, nuke the parts of the world where we insist on using other
languages than English?

Seriously, I think it would be a very bad idea. If you want to send
mail to these persons, you do want to spell their names properly, don't
you?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's only one of the problems! I want to store the data with the
international characters but then I need to strip out the international
characters for certain output, such as in e-mail addresses...
lq|||Technically, international characters are permitted in email addresses
and domain names so stripping them out is still a bad idea.

You'll have to build your own table to translate characters that you
deem to be unacceptable. Google for some conversion tables but there
isn't really a single answer because it depends on which characters you
need to support (don't forget the two-byte character sets used in the
Far East and elsewhere for example).

--
David Portas
SQL Server MVP
--|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> That's only one of the problems! I want to store the data with the
> international characters but then I need to strip out the international
> characters for certain output, such as in e-mail addresses...

I would recomment that you store the e-mail address. While the e-mail
address often can be formed as firstname.lastname@.domain.xxx, this is
not a requirement.

And the local transformation may not be the one you expect. in German
after often replaced with ae, oe and ue. (Whether this is actually common
in mail addresses, I don't know.)

If you are thinking of the comment part of the e-mail address:

John Smith <johnsmith@.example.com
(John Smith is the comment here) You should either encode the comment
according to MIME as per RFC2047, or send as-is. But don't misspell
people's names.

(By the way, permit me to point out that the term "international characters"
is a misnomer. The internationalest characters I can think of is the
English alphabet A-Z. Non-ASCII characters is a better term, and
technically precise.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Thanks for that. You're right, I do mean non-ASCII characters...
This gets tricky because many city names and people names contain
non-ASCII characters and I want to display then properly, however, some
systems do not support non-ASCII characters and they need to be
cleansed for output to those systems, so as you point out, there is not
an always correct translation into ASCII characters.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Thanks for that. You're right, I do mean non-ASCII characters...
> This gets tricky because many city names and people names contain
> non-ASCII characters and I want to display then properly, however, some
> systems do not support non-ASCII characters and they need to be
> cleansed for output to those systems, so as you point out, there is not
> an always correct translation into ASCII characters.

Systems in 2005 that is not capable to handle non-ASCII characters? So,
not all programs are capable to handle Unicode, but one would at least
imagine that they should be able handle 8-bit charset. If I were you,
I would consider to attempt to kill these systems by starvation and
not feed them any data. :-)

On a more serious point, there are one letter that the snippet I
posted does not handle and that is . This gets converted into a ?.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Windows file names is an example on one such system...|||Which Windows are you referring to? FAT32? NTFS does support non-ASCII
characters in files names, at least it does under Win 2003 so I don't
know why it wouldn't under other versions.

--
David Portas
SQL Server MVP
--|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Windows file names is an example on one such system...

The 8.3 names, yes, but you rarely have to see them. Even less rarely
have to form them. As long as you are on NTFS you can use the whole range
of Unicode, and use letters from any script. The FAT files systems may
not support this, but I'm pretty sure that they support the full range
of the current ANSI code page.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Easy way to index a column with URLs

We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?If you need to enforce uniqueness, or you are frequently searching the table
by the URL column, then an index is appropriate. Bear in mind that an index
key can be no more than 900 bytes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Kevin" <none@.none.com> wrote in message
news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?|||I'm afraid that some of the urls possibly could get over that size after
encoding (the URL column is nvarchar).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
> If you need to enforce uniqueness, or you are frequently searching the
> table
> by the URL column, then an index is appropriate. Bear in mind that an
> index
> key can be no more than 900 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Kevin" <none@.none.com> wrote in message
> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
> We have a column that stores URL (varchar), which ultimately are unique
> within the table (though an identity is the actual primary key). Is it
> good
> practice to put an index on such a wide column? Are there are other
> alternatives, possibly different data type to use?
>|||"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>> If you need to enforce uniqueness, or you are frequently searching the
>> table
>> by the URL column, then an index is appropriate. Bear in mind that an
>> index
>> key can be no more than 900 bytes.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Kevin" <none@.none.com> wrote in message
>> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
>> We have a column that stores URL (varchar), which ultimately are unique
>> within the table (though an identity is the actual primary key). Is it
>> good
>> practice to put an index on such a wide column? Are there are other
>> alternatives, possibly different data type to use?
>|||Kevin,
if you badly want this column be indexed (of those values that do not excede
the 900 bites limit), you can do some more work:
create an indexed view that have a pk column and the URL column, with the
URL records no longer than 900 bites, index on the URL column;
create an indexed view that have a pk column and the URL column, with the
URL records longer than 900 bites, index on the pk column;
chances are that you may have a handful of records in the second view so
that you do not care to index scan it. And you have index seek on the
shorter URL values. Then join the views with your base table.
HTH
Quentin
"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>> If you need to enforce uniqueness, or you are frequently searching the
>> table
>> by the URL column, then an index is appropriate. Bear in mind that an
>> index
>> key can be no more than 900 bytes.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Kevin" <none@.none.com> wrote in message
>> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
>> We have a column that stores URL (varchar), which ultimately are unique
>> within the table (though an identity is the actual primary key). Is it
>> good
>> practice to put an index on such a wide column? Are there are other
>> alternatives, possibly different data type to use?
>sql

Easy way to index a column with URLs

We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?
If you need to enforce uniqueness, or you are frequently searching the table
by the URL column, then an index is appropriate. Bear in mind that an index
key can be no more than 900 bytes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Kevin" <none@.none.com> wrote in message
news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?
|||I'm afraid that some of the urls possibly could get over that size after
encoding (the URL column is nvarchar).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
> If you need to enforce uniqueness, or you are frequently searching the
> table
> by the URL column, then an index is appropriate. Bear in mind that an
> index
> key can be no more than 900 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Kevin" <none@.none.com> wrote in message
> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
> We have a column that stores URL (varchar), which ultimately are unique
> within the table (though an identity is the actual primary key). Is it
> good
> practice to put an index on such a wide column? Are there are other
> alternatives, possibly different data type to use?
>
|||"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>
|||Kevin,
if you badly want this column be indexed (of those values that do not excede
the 900 bites limit), you can do some more work:
create an indexed view that have a pk column and the URL column, with the
URL records no longer than 900 bites, index on the URL column;
create an indexed view that have a pk column and the URL column, with the
URL records longer than 900 bites, index on the pk column;
chances are that you may have a handful of records in the second view so
that you do not care to index scan it. And you have index seek on the
shorter URL values. Then join the views with your base table.
HTH
Quentin
"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>

Easy way to index a column with URLs

We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?If you need to enforce uniqueness, or you are frequently searching the table
by the URL column, then an index is appropriate. Bear in mind that an index
key can be no more than 900 bytes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Kevin" <none@.none.com> wrote in message
news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?|||I'm afraid that some of the urls possibly could get over that size after
encoding (the URL column is nvarchar).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
> If you need to enforce uniqueness, or you are frequently searching the
> table
> by the URL column, then an index is appropriate. Bear in mind that an
> index
> key can be no more than 900 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Kevin" <none@.none.com> wrote in message
> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
> We have a column that stores URL (varchar), which ultimately are unique
> within the table (though an identity is the actual primary key). Is it
> good
> practice to put an index on such a wide column? Are there are other
> alternatives, possibly different data type to use?
>|||"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>|||Kevin,
if you badly want this column be indexed (of those values that do not excede
the 900 bites limit), you can do some more work:
create an indexed view that have a pk column and the URL column, with the
URL records no longer than 900 bites, index on the URL column;
create an indexed view that have a pk column and the URL column, with the
URL records longer than 900 bites, index on the pk column;
chances are that you may have a handful of records in the second view so
that you do not care to index scan it. And you have index seek on the
shorter URL values. Then join the views with your base table.
HTH
Quentin
"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>

Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID = (SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe GeretzCheck this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scripts/article.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID => (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID => (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||Like most people you need proper database change control
then these issues would disappear. It seems that people
are all to quick to think that their processes are OK
until the inevitable happens and they are in the _hit!
Smart people use DB Ghost.
regards,
Mark Baekdal
www.dbghost.com
Living and breathing database change management for SQL
Server
>--Original Message--
>MS SQL Server 2000 does not allow columns with Default
value constraints to
>be dropped. We are looking for an easy way to drop such
columns. The problem
>occurs because the DEFAULT clause actually creates a
constraint on the
>table. This name of this constraint is chosen by SQL
Server and will differ
>from database to database.
>The seemingly obvious solution:
>exec sp_unbindefault 'table.column'
>does not work but gives us the following error:
>Server: Msg 15049, Level 11, State 1, Procedure
sp_unbindefault, Line 98
>Cannot unbind from 'table.column'. Use ALTER TABLE DROP
CONSTRAINT
>OK, ALTER TABLE DROP CONSTRAINT would certainly work,
but we don't know the
>name of the contraint! Makes it kind of hard for us to
script this to work
>for multiple databases.
>The only working solution we have seems very cumbersome.
(I guess we could
>convert it to a stored procedure.)
>DECLARE @.STR VARCHAR(100)
>SET @.STR = (
>SELECT NAME
>FROM SYSOBJECTS SO
>JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
>WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
>AND SO.XTYPE = 'D' AND SC.COLID =>(SELECT COLID FROM SYSCOLUMNS
>WHERE ID = OBJECT_ID('<table>')
>AND NAME = '<column>')
>)
>SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR
EXEC (@.STR)
>The other solution would be to stop using the DEFAULT
clause on the column
>definition, but rather to create a named constraint. But
completely
>eschewing the DEFAULT clause will drive the complexity
of our database
>definition scripts way up. We use DEFAULT values rather
heavily and I don't
>want to have to specify an ALTER TABLE ADD CONSTRAINT
for every column with
>a default value! I don't like this option.
>There's got to be a better way (I hope). I can't believe
it has to be this
>difficult to drop a column simply because it has a
default value specified
>for it. Is there an easy way to deal with this?
>Thanks for your help!
>Joe Geretz
>
>.
>sql

Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID =
(SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe Geretz
Check this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scrip...le.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>
|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>

Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID =
(SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe GeretzCheck this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scri...cle.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>

Easy query question

This one should be easy, but for some reason I can't get it.
I have a table with an XML column, containing the following:
<xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
<xs:Caption />
<xs:Description />
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1" />
<xs:Rateplan Caption="TN1|H" Description="" />
<xs:Roomtype Caption="A1A" Description="" />
<xs:Mealplan Caption="None" Description="" />
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9" />
<xs:Target Sell="107.9" Net="107.9" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5" />
<xs:Target Sell="124.5" Net="124.5" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8" />
<xs:Target Sell="132.8" Net="132.8" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1" />
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87" />
<xs:Target Sell="32.87" Net="32.87" />
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z"
PickUp="0001-01-01T00:00:00Z" DropOff="0001-01-02T00:00:00Z"
Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z" Nights="1"
Days="2" Weeks="1" />
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description />
<xs:UnitRate>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07" />
<xs:Target Sell="398.07" Net="398.07" />
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0" />
<xs:PickUpFee />
<xs:DropOffFee />
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None" />
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:Costs>
</xs:ExtraLimit>
<xs:Information />
</xs:DistanceLimit>
</xs:ReservationChargeItem>
What I want is a select statement that gives me the values for net and sell
from the TotalSum/Source. Seems like it should be simple, but I'm missing
something somewhere. Thanks for any help you can give me.
Hello PK,
How's this:
use scratch
go
declare @.x xml
set @.x = '<?xml version="1.0" encoding="UTF-8"?>
<xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
<xs:Caption/>
<xs:Description/>
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1"/>
<xs:Rateplan Caption="TN1|H" Description=""/>
<xs:Roomtype Caption="A1A" Description=""/>
<xs:Mealplan Caption="None" Description=""/>
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9"/>
<xs:Target Sell="107.9" Net="107.9"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5"/>
<xs:Target Sell="124.5" Net="124.5"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8"/>
<xs:Target Sell="132.8" Net="132.8"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1"/>
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87"/>
<xs:Target Sell="32.87" Net="32.87"/>
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" PickUp="0001-01-01T00:00:00Z"
DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z"
Nights="1" Days="2" Weeks="1"/>
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description/>
<xs:UnitRate>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07"/>
<xs:Target Sell="398.07" Net="398.07"/>
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0"/>
<xs:PickUpFee/>
<xs:DropOffFee/>
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None"/>
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:Costs>
</xs:ExtraLimit>
<xs:Information/>
</xs:DistanceLimit>
</xs:ReservationChargeItem>
'
selectt.c.value('data(@.Sell)','money') as Sell
, t.c.value('data(@.Net)','money') as Net
from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSchema";
//xs:TotalSum/xs:Source') as t(c);
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Fantastic! Thanks!
"Kent Tegels" wrote:

> Hello PK,
> How's this:
> use scratch
> go
> declare @.x xml
> set @.x = '<?xml version="1.0" encoding="UTF-8"?>
> <xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
> DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
> <xs:Caption/>
> <xs:Description/>
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" Weeks="1"/>
> <xs:Rateplan Caption="TN1|H" Description=""/>
> <xs:Roomtype Caption="A1A" Description=""/>
> <xs:Mealplan Caption="None" Description=""/>
> <xs:GuestIds>
> <xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
> <xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
> </xs:GuestIds>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="107.9" Net="107.9"/>
> <xs:Target Sell="107.9" Net="107.9"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="124.5" Net="124.5"/>
> <xs:Target Sell="124.5" Net="124.5"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="132.8" Net="132.8"/>
> <xs:Target Sell="132.8" Net="132.8"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
> ApplicableForCancellation="True">
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" Weeks="1"/>
> <xs:Caption>32.87</xs:Caption>
> <xs:TotalSum>
> <xs:Source Sell="32.87" Net="32.87"/>
> <xs:Target Sell="32.87" Net="32.87"/>
> </xs:TotalSum>
> </xs:Taxes>
> <xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
> TN1" NumberOfUnits="0">
> <xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" PickUp="0001-01-01T00:00:00Z"
> DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z"
> Nights="1" Days="2" Weeks="1"/>
> <xs:Caption>A1A - TN1</xs:Caption>
> <xs:Description/>
> <xs:UnitRate>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:UnitRate>
> </xs:Products>
> <xs:TotalSum>
> <xs:Source Sell="398.07" Net="398.07"/>
> <xs:Target Sell="398.07" Net="398.07"/>
> </xs:TotalSum>
> <xs:MultipleDriversFee ChargeType="0"/>
> <xs:PickUpFee/>
> <xs:DropOffFee/>
> <xs:DistanceLimit DistanceUnit="0">
> <xs:FreeLimit Type="None"/>
> <xs:ExtraLimit Type="PerUnit" Distance="0">
> <xs:Costs>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:Costs>
> </xs:ExtraLimit>
> <xs:Information/>
> </xs:DistanceLimit>
> </xs:ReservationChargeItem>
> '
> selectt.c.value('data(@.Sell)','money') as Sell
> , t.c.value('data(@.Net)','money') as Net
> from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSchema";
> //xs:TotalSum/xs:Source') as t(c);
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>

Easy query question

This one should be easy, but for some reason I can't get it.
I have a table with an XML column, containing the following:
<xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
<xs:Caption />
<xs:Description />
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Ws="1" />
<xs:Rateplan Caption="TN1|H" Description="" />
<xs:Roomtype Caption="A1A" Description="" />
<xs:Mealplan Caption="None" Description="" />
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9" />
<xs:Target Sell="107.9" Net="107.9" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5" />
<xs:Target Sell="124.5" Net="124.5" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8" />
<xs:Target Sell="132.8" Net="132.8" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Ws="1" />
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87" />
<xs:Target Sell="32.87" Net="32.87" />
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z"
PickUp="0001-01-01T00:00:00Z" DropOff="0001-01-02T00:00:00Z"
Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z" Nights="1"
Days="2" Ws="1" />
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description />
<xs:UnitRate>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07" />
<xs:Target Sell="398.07" Net="398.07" />
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0" />
<xs:PickUpFee />
<xs:DropOffFee />
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None" />
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:Costs>
</xs:ExtraLimit>
<xs:Information />
</xs:DistanceLimit>
</xs:ReservationChargeItem>
What I want is a select statement that gives me the values for net and sell
from the TotalSum/Source. Seems like it should be simple, but I'm missing
something somewhere. Thanks for any help you can give me.Hello PK,
How's this:
use scratch
go
declare @.x xml
set @.x = '<?xml version="1.0" encoding="UTF-8"?>
<xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
<xs:Caption/>
<xs:Description/>
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Ws="1"/>
<xs:Rateplan Caption="TN1|H" Description=""/>
<xs:Roomtype Caption="A1A" Description=""/>
<xs:Mealplan Caption="None" Description=""/>
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9"/>
<xs:Target Sell="107.9" Net="107.9"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5"/>
<xs:Target Sell="124.5" Net="124.5"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8"/>
<xs:Target Sell="132.8" Net="132.8"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Ws="1"/>
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87"/>
<xs:Target Sell="32.87" Net="32.87"/>
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" PickUp="
0001-01-01T00:00:00Z"
DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="000
1-01-02T00:00:00Z"
Nights="1" Days="2" Ws="1"/>
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description/>
<xs:UnitRate>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07"/>
<xs:Target Sell="398.07" Net="398.07"/>
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0"/>
<xs:PickUpFee/>
<xs:DropOffFee/>
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None"/>
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:Costs>
</xs:ExtraLimit>
<xs:Information/>
</xs:DistanceLimit>
</xs:ReservationChargeItem>
'
select t.c.value('data(@.Sell)','money') as Sell
, t.c.value('data(@.Net)','money') as Net
from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSche
ma";
//xs:TotalSum/xs:Source') as t(c);
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Fantastic! Thanks!
"Kent Tegels" wrote:

> Hello PK,
> How's this:
> use scratch
> go
> declare @.x xml
> set @.x = '<?xml version="1.0" encoding="UTF-8"?>
> <xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema
"
> DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
> <xs:Caption/>
> <xs:Description/>
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" Ws="1"/>
> <xs:Rateplan Caption="TN1|H" Description=""/>
> <xs:Roomtype Caption="A1A" Description=""/>
> <xs:Mealplan Caption="None" Description=""/>
> <xs:GuestIds>
> <xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
> <xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
> </xs:GuestIds>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="107.9" Net="107.9"/>
> <xs:Target Sell="107.9" Net="107.9"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="124.5" Net="124.5"/>
> <xs:Target Sell="124.5" Net="124.5"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="132.8" Net="132.8"/>
> <xs:Target Sell="132.8" Net="132.8"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
> ApplicableForCancellation="True">
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" Ws="1"/>
> <xs:Caption>32.87</xs:Caption>
> <xs:TotalSum>
> <xs:Source Sell="32.87" Net="32.87"/>
> <xs:Target Sell="32.87" Net="32.87"/>
> </xs:TotalSum>
> </xs:Taxes>
> <xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
> TN1" NumberOfUnits="0">
> <xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" Pick
Up="0001-01-01T00:00:00Z"
> DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="0
001-01-02T00:00:00Z"
> Nights="1" Days="2" Ws="1"/>
> <xs:Caption>A1A - TN1</xs:Caption>
> <xs:Description/>
> <xs:UnitRate>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:UnitRate>
> </xs:Products>
> <xs:TotalSum>
> <xs:Source Sell="398.07" Net="398.07"/>
> <xs:Target Sell="398.07" Net="398.07"/>
> </xs:TotalSum>
> <xs:MultipleDriversFee ChargeType="0"/>
> <xs:PickUpFee/>
> <xs:DropOffFee/>
> <xs:DistanceLimit DistanceUnit="0">
> <xs:FreeLimit Type="None"/>
> <xs:ExtraLimit Type="PerUnit" Distance="0">
> <xs:Costs>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:Costs>
> </xs:ExtraLimit>
> <xs:Information/>
> </xs:DistanceLimit>
> </xs:ReservationChargeItem>
> '
> select t.c.value('data(@.Sell)','money') as Sell
> , t.c.value('data(@.Net)','money') as Net
> from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSc
hema";
> //xs:TotalSum/xs:Source') as t(c);
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>

easy parameter Q

I think this is probably easy but haven't found the setting yet and am
running out of time.
My report has a column I want to use as a parameter(parameter query). The
value of the field repeats several times and I want to limit the drop down
list to the first occurance of each value.
Hope this is an easy one.Have you tried select Distinct(column) ?
"HollyylloH" wrote:
> I think this is probably easy but haven't found the setting yet and am
> running out of time.
> My report has a column I want to use as a parameter(parameter query). The
> value of the field repeats several times and I want to limit the drop down
> list to the first occurance of each value.
> Hope this is an easy one.|||Darwin,
Thanks for your reply. I am using report parameters and am not sure how to
use a distinct() within the confines of the the parameter options. If you can
help I would much appriciate it.
I don't want to affect the report query but rather the parameter drop-down
menu options.
"darwin" wrote:
> Have you tried select Distinct(column) ?
> "HollyylloH" wrote:
> > I think this is probably easy but haven't found the setting yet and am
> > running out of time.
> >
> > My report has a column I want to use as a parameter(parameter query). The
> > value of the field repeats several times and I want to limit the drop down
> > list to the first occurance of each value.
> >
> > Hope this is an easy one.|||create a new dataset to use to populate the parameter. You can create
multiple datasets to populate your parameters.
then change your parameter properties to use the new data set. select the
parameter you want to change, then click the From Query radio button, select
the new dataset name under dataset, select the Value Field value and the
label field. This is generally an Id and description.
hope that helps.. there should be something thats helps in the help files
"HollyylloH" wrote:
> Darwin,
> Thanks for your reply. I am using report parameters and am not sure how to
> use a distinct() within the confines of the the parameter options. If you can
> help I would much appriciate it.
> I don't want to affect the report query but rather the parameter drop-down
> menu options.
> "darwin" wrote:
> > Have you tried select Distinct(column) ?
> >
> > "HollyylloH" wrote:
> >
> > > I think this is probably easy but haven't found the setting yet and am
> > > running out of time.
> > >
> > > My report has a column I want to use as a parameter(parameter query). The
> > > value of the field repeats several times and I want to limit the drop down
> > > list to the first occurance of each value.
> > >
> > > Hope this is an easy one.|||Thanks a million! That did it for me!
"darwin" wrote:
> create a new dataset to use to populate the parameter. You can create
> multiple datasets to populate your parameters.
> then change your parameter properties to use the new data set. select the
> parameter you want to change, then click the From Query radio button, select
> the new dataset name under dataset, select the Value Field value and the
> label field. This is generally an Id and description.
> hope that helps.. there should be something thats helps in the help files
>
> "HollyylloH" wrote:
> > Darwin,
> >
> > Thanks for your reply. I am using report parameters and am not sure how to
> > use a distinct() within the confines of the the parameter options. If you can
> > help I would much appriciate it.
> >
> > I don't want to affect the report query but rather the parameter drop-down
> > menu options.
> >
> > "darwin" wrote:
> >
> > > Have you tried select Distinct(column) ?
> > >
> > > "HollyylloH" wrote:
> > >
> > > > I think this is probably easy but haven't found the setting yet and am
> > > > running out of time.
> > > >
> > > > My report has a column I want to use as a parameter(parameter query). The
> > > > value of the field repeats several times and I want to limit the drop down
> > > > list to the first occurance of each value.
> > > >
> > > > Hope this is an easy one.

Easy Newb Q? Cannot find data type xml?

I'm simply trying to create a column of datatype xml but I can't. I'm in Microsoft SQL Server Management Studio 2005. I have all other datatypes except XML... I thought it was supposed to be "built in".
CREATE TABLE T1(Col1 int primary key, Col2 xml)
gives me
Column or parameter #2: Cannot find data type xml.
I copied that straight out of the help. I must be missing something really simple here such as an "Enable XML" in a properties window somewhere? Thanks!
You are in SQL Management Studio 2005 but are you sure you are connected to a SQL Server 2005 instance ? Perhaps you have a SQL 2000 Server instance on your machine and connected to it ?|||

I have the same problem.

This machine has never had any other version of SQL other than SQL 2005.

Created database in Management Studio.

|||

Greetings,

I have encountered the same problem "Cannot find data type xml.".

I see that you have encountered the same problem but I have not been able to find the answer.

In my case I have a newly loaded Windows 2000 Server with all of the service packs and the trail version of SQL 2005 Exterprise.

When I try to create the table from the example in the book, which is the same create table example as above I get the message "Cannot find data type xml"

If I go into object explorer, tables, new tables and try and create the table from their xml is not listed as one of the data types.

Thanks for your help.

Easy Newb Q? Cannot find data type xml?

I'm simply trying to create a column of datatype xml but I can't. I'm

in Microsoft SQL Server Management Studio 2005. I have all other

datatypes except XML... I thought it was supposed to be "built in".

CREATE TABLE T1(Col1 int primary key, Col2 xml)

gives me

Column or parameter #2: Cannot find data type xml.

I copied that straight out of the help. I must be missing something

really simple here such as an "Enable XML" in a properties window

somewhere? Thanks! You are in SQL Management Studio 2005 but are you sure you are connected to a SQL Server 2005 instance ? Perhaps you have a SQL 2000 Server instance on your machine and connected to it ?|||

I have the same problem.

This machine has never had any other version of SQL other than SQL 2005.

Created database in Management Studio.

|||

Greetings,

I have encountered the same problem "Cannot find data type xml.".

I see that you have encountered the same problem but I have not been able to find the answer.

In my case I have a newly loaded Windows 2000 Server with all of the service packs and the trail version of SQL 2005 Exterprise.

When I try to create the table from the example in the book, which is the same create table example as above I get the message "Cannot find data type xml"

If I go into object explorer, tables, new tables and try and create the table from their xml is not listed as one of the data types.

Thanks for your help.

Easy Newb Q? Cannot find data type xml?

I'm simply trying to create a column of datatype xml but I can't. I'm in Microsoft SQL Server Management Studio 2005. I have all other datatypes except XML... I thought it was supposed to be "built in".
CREATE TABLE T1(Col1 int primary key, Col2 xml)
gives me
Column or parameter #2: Cannot find data type xml.
I copied that straight out of the help. I must be missing something really simple here such as an "Enable XML" in a properties window somewhere? Thanks!
You are in SQL Management Studio 2005 but are you sure you are connected to a SQL Server 2005 instance ? Perhaps you have a SQL 2000 Server instance on your machine and connected to it ?|||

I have the same problem.

This machine has never had any other version of SQL other than SQL 2005.

Created database in Management Studio.

|||

Greetings,

I have encountered the same problem "Cannot find data type xml.".

I see that you have encountered the same problem but I have not been able to find the answer.

In my case I have a newly loaded Windows 2000 Server with all of the service packs and the trail version of SQL 2005 Exterprise.

When I try to create the table from the example in the book, which is the same create table example as above I get the message "Cannot find data type xml"

If I go into object explorer, tables, new tables and try and create the table from their xml is not listed as one of the data types.

Thanks for your help.

sql

Easy Convert Question

Will the following SQL allow me to see the results of the computed column in
decimal format even if the Columns used in the calculation are defind as int
:
Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
I have used it but I am not getting any decimal places. It is showing the
decimals as 0. If this is not the problem could you please make some
suggestions.
Thanks
AdamTry multiplying the arguments by 1.0, or explicitly converting the arguments
to decimal.
By arguments, I mean the individual pieces, not the result of the
calculation.
A
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:836A42B5-D2D1-480B-82A5-A167007B0123@.microsoft.com...
> Will the following SQL allow me to see the results of the computed column
> in
> decimal format even if the Columns used in the calculation are defind as
> int:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards]))
> 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||You have to change the formula instead.
((#Operator2.[Count] * 1000.00)/#Closed2.[Dyed Yards])
AMB
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||Can you try this:
Convert(Dec(10,5), ((#Operator2.[Count]*1000.0)/#Closed2.[Dyed Yards]))
'Count'
Perayu
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||How about something like this
select price/cast(code as money) from xyz
i.e. the denominator gets converted to 'money' type :)
Cheers,
JP (Just a programmer:))
--
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O515emXqFHA.2652@.tk2msftngp13.phx.gbl...
> Try multiplying the arguments by 1.0, or explicitly converting the
> arguments to decimal.
> By arguments, I mean the individual pieces, not the result of the
> calculation.
> A
>
>
> "A.B." <AB@.discussions.microsoft.com> wrote in message
> news:836A42B5-D2D1-480B-82A5-A167007B0123@.microsoft.com...
>|||Thanks guys, it worked
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||OR
select price/cast(code as decimal(10,4)) from xyz
Cheers,
JP (Just a Programmer;))
--
"JP" <someone@.somewhere.com> wrote in message
news:ucbhzwXqFHA.3104@.TK2MSFTNGP12.phx.gbl...
> How about something like this
> select price/cast(code as money) from xyz
> i.e. the denominator gets converted to 'money' type :)
> Cheers,
> JP (Just a programmer:))
> --
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:O515emXqFHA.2652@.tk2msftngp13.phx.gbl...
>|||hi AB
you can try it as:
#Operator2.[Count]*1000.0)/#Closed2.[Dyed Yards] as [count]
hope this will help u
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam

dynamically sort columns?

Hi,
Can you set up a report so that you can dynamically sort columns.
ie.
When viewing a report you click on a header of a column and sort by
this column.
If so can you point me to an example
Thanks in AdvanceHi Paul,
You can rightclick on the columnheaders textbox to display the
properties of this, select the tab "Interactive Sort", and select "Add
an interactive sort action to this textbox".
Below you can specify how you want the data to be sorted, ie.
selecting a field from your dataset.
Regards,
Rune Brahe Bjerregaard
On 30 Jul., 06:03, paulhux...@.hotmail.com wrote:
> Hi,
> Can you set up a report so that you can dynamically sort columns.
> ie.
> When viewing a report you click on a header of a column and sort by
> this column.
> If so can you point me to an example
> Thanks in Advance|||On Jul 30, 12:03 am, paulhux...@.hotmail.com wrote:
> Hi,
> Can you set up a report so that you can dynamically sort columns.
> ie.
> When viewing a report you click on a header of a column and sort by
> this column.
> If so can you point me to an example
> Thanks in Advance
Right Click on the title of the column which you wish to use an
Interactive sort with. On the drop down menu select properties. When
the properties widow pops up go to the tab labeled "Interactive
Sort". Click the check box at the top and under the drop down
selection box pick the column that you are trying to sort by. For
example, you selected the column with the datafield "Order Number" so
select that in your interactive sort drop down... otherwise it will
sort according to another column when you click on it. Preview the
report to make sure it works.|||Thanks