Column's default value in 2005

HI,
Dose column's default value can be set as 2 columns' "+, - , *, / " ?
I want ColumnC's default value as ColumnA/ColumnB.
Any idea? Thanks for any advice!
Angi
angi a crit :
> HI,
> Dose column's default value can be set as 2 columns' "+, - , *, / " ?
> I want ColumnC's default value as ColumnA/ColumnB.
> Any idea? Thanks for any advice!
> Angi
>
NO... Default and Not Null constraint can only be set on a single row.
To do that, you must use a trigger :
CREATE TRIGGER <trig_name> ON <table_name>
FOR INSERT
AS
UPDATE <table_name>
SET ColumnC = ColumnA/ColumnB
FROM <table_name> T
INNER JOIN inserted i
ON T.<key_col> = i.<key_col>
WHERE ColumnC IS NULL
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Column's default value in 2005

HI,
Dose column's default value can be set as 2 columns' "+, - , *, / " ?
I want ColumnC's default value as ColumnA/ColumnB.
Any idea? Thanks for any advice!
Angiangi a crit :
> HI,
> Dose column's default value can be set as 2 columns' "+, - , *, / " ?
> I want ColumnC's default value as ColumnA/ColumnB.
> Any idea? Thanks for any advice!
> Angi
>
NO... Default and Not Null constraint can only be set on a single row.
To do that, you must use a trigger :
CREATE TRIGGER <trig_name> ON <table_name>
FOR INSERT
AS
UPDATE <table_name>
SET ColumnC = ColumnA/ColumnB
FROM <table_name> T
INNER JOIN inserted i
ON T.<key_col> = i.<key_col>
WHERE ColumnC IS NULL
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Column's default value in 2005

HI,
Dose column's default value can be set as 2 columns' "+, - , *, / " ?
I want ColumnC's default value as ColumnA/ColumnB.
Any idea? Thanks for any advice!
Angiangi a écrit :
> HI,
> Dose column's default value can be set as 2 columns' "+, - , *, / " ?
> I want ColumnC's default value as ColumnA/ColumnB.
> Any idea? Thanks for any advice!
> Angi
>
NO... Default and Not Null constraint can only be set on a single row.
To do that, you must use a trigger :
CREATE TRIGGER <trig_name> ON <table_name>
FOR INSERT
AS
UPDATE <table_name>
SET ColumnC = ColumnA/ColumnB
FROM <table_name> T
INNER JOIN inserted i
ON T.<key_col> = i.<key_col>
WHERE ColumnC IS NULL
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

column-defulat-value as auto-increment?

Thank you very much for the reply!
well, there is no way to update an identity column,
but is there a way to define a default value to a column
that makes auto-increment?
i tried in the SQL-Server-Enterprize-Manager\Design-Table
to set the default value of a column to MAX(recid_)+1...
any more ideas?
Thanks again,
edo.> well, there is no way to update an identity column,
> but is there a way to define a default value to a column
> that makes auto-increment?
> i tried in the SQL-Server-Enterprize-Manager\Design-Table
> to set the default value of a column to MAX(recid_)+1...
> any more ideas?
You could do this in a trigger, but maybe it might make more sense to
explain why you need this behavior.|||i made this trigger:
CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
FOR INSERT
AS
declare @.i1 int
declare c1 cursor for select Max(recid) from jobs
open c1
fetch next from c1 into @.i1
close c1
deallocate c1
update T1 set recid_=@.i1+1 where recid is NULL
do you think it's the most effecient way?
i doubt because i wonder first, wether i must open a
cursor, and second wether there is no direct way to
update the current inserted record, ruther than
search "where recid is NULL"
thank,
edo.
>--Original Message--
>> well, there is no way to update an identity column,
>> but is there a way to define a default value to a
column
>> that makes auto-increment?
>> i tried in the SQL-Server-Enterprize-Manager\Design-
Table
>> to set the default value of a column to MAX(recid_)
+1...
>> any more ideas?
>You could do this in a trigger, but maybe it might make
more sense to
>explain why you need this behavior.
>
>.
>|||You don't need a cursor:
SET @.i = (SELECT MAX(recid) FROM jobs)
Also, you use the INSERTED table to get the modified row(s).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"edo" <ewilde@.nana.co.il> wrote in message news:0afe01c36d21$31eab6c0$a101280a@.phx.gbl...
> i made this trigger:
> CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
> FOR INSERT
> AS
> declare @.i1 int
> declare c1 cursor for select Max(recid) from jobs
> open c1
> fetch next from c1 into @.i1
> close c1
> deallocate c1
> update T1 set recid_=@.i1+1 where recid is NULL
>
> do you think it's the most effecient way?
> i doubt because i wonder first, wether i must open a
> cursor, and second wether there is no direct way to
> update the current inserted record, ruther than
> search "where recid is NULL"
> thank,
> edo.
> >--Original Message--
> >> well, there is no way to update an identity column,
> >> but is there a way to define a default value to a
> column
> >> that makes auto-increment?
> >>
> >> i tried in the SQL-Server-Enterprize-Manager\Design-
> Table
> >> to set the default value of a column to MAX(recid_)
> +1...
> >> any more ideas?
> >
> >You could do this in a trigger, but maybe it might make
> more sense to
> >explain why you need this behavior.
> >
> >
> >.
> >|||Thanks for your helped,
i implemented your suggestion about the
SET @.i = (SELECT MAX(recid) FROM T1)
but i tried somthing like:
update inserted set recid=1
and got an error:
"the logical tables INSERTED and DELETED can not be
updated."
?
thanks agian,
edo.
>--Original Message--
>You don't need a cursor:
>SET @.i = (SELECT MAX(recid) FROM jobs)
>Also, you use the INSERTED table to get the modified row
(s).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"edo" <ewilde@.nana.co.il> wrote in message
news:0afe01c36d21$31eab6c0$a101280a@.phx.gbl...
>> i made this trigger:
>> CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
>> FOR INSERT
>> AS
>> declare @.i1 int
>> declare c1 cursor for select Max(recid) from jobs
>> open c1
>> fetch next from c1 into @.i1
>> close c1
>> deallocate c1
>> update T1 set recid_=@.i1+1 where recid is NULL
>>
>> do you think it's the most effecient way?
>> i doubt because i wonder first, wether i must open a
>> cursor, and second wether there is no direct way to
>> update the current inserted record, ruther than
>> search "where recid is NULL"
>> thank,
>> edo.
>> >--Original Message--
>> >> well, there is no way to update an identity column,
>> >> but is there a way to define a default value to a
>> column
>> >> that makes auto-increment?
>> >>
>> >> i tried in the SQL-Server-Enterprize-Manager\Design-
>> Table
>> >> to set the default value of a column to MAX(recid_)
>> +1...
>> >> any more ideas?
>> >
>> >You could do this in a trigger, but maybe it might
make
>> more sense to
>> >explain why you need this behavior.
>> >
>> >
>> >.
>> >
>
>.
>|||> update inserted set recid=1
You can't update the inserted / deleted tables.
Maybe you could show your table structure, sample data, and the results you
are trying to achieve, rather than have us reverse engineer your existing
trigger code. It might be that a trigger isn't even necessary for this, or
it might be that you could approach the trigger in a completely different
way. Your narrative a few posts back is difficult to follow, but might be
easier to understand if you show us your actual schema design. There might
be a much more efficient approach to whatever it is you mean by "cloning"...

Column Properties Window Bug in Management Studio?

This issue deals with where to see the default value for a column in Sql Server 2005 Management Studio.

I have a default value of (1) in a column of type bit:

If you right click on the column in Management Studio object explorer and choose properties, the default value does not appear under "Default Binding" or "Default Schema" in the Column Properties window.

If you select the table and then choose View -> Summary, double click on the Columns folder, then right click -> properties on the column, again the default value does not appear under "Default Binding" or "Default Schema" in the Column Properties window.

If you right click on the table or column and choose "Modify", then select the column, the default value appears in "Default Value or Binding".

If you script the table out (right click -> Script Table as -> Create to), the default value for that column appears in the script.

I'm wondering if this is by design or a bug? Obviously right click-> properties is not a reliable way to view the properties on a column.

Hello Eric,

This issue seems to be a bug in Column Properties page. Could you please report this issue through Connect web site

https://connect.microsoft.com/SQLServer/Feedback

Click on Submit Feedback

Thanks

Sethu Srinivasan

SQL Server Manageability Dev Team

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
BkThe order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:
> There is no 'default' ordering of rows. Itzik sheds some interesting light
> on the subject:
> http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
> http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
> http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> > Can someone tell me what defines the order of SQL output columns when i
> > use a
> > default query on a table like "SELECT * from <Table> ".
> >
> > Is there a way to alter the default ORDER of these columns?
> >
> > I am aware of the ORDER BY but will not be able to use it for various
> > reasons.
> >
> > Regards
> > Bk
>|||As mentioned by Arnie, it is considered a bad practice to use "SELECT *"
in production code (with the exception of its use in an EXISTS clause).
When using SELECT * on a table, the columns in the resultset will match
the order in the table definition. There is no way to change this using
DML. The only way to change its order (apart from explicitely naming the
columns in the desired order) is to redefine the table.
HTH,
Gert-Jan
BK-Chicago wrote:
> Can someone tell me what defines the order of SQL output columns when i use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk

Column Order in Matrix

Using SRS 2000.
When creating a matrix report, can you order the columns? By default, they
are in alpha order. My columns are the months of the year. Using Order By
in the query doesn't work.
I want January, February..., not April, August...If you have an actual DateTime field in the dataset, you could just add a
sort expression on the matrix column grouping like
=Month(Fields!SalesDate.Value)
If the column groupings are just based on a string field that actually
contains the month names and there are no other fields that would provide
full datetime information, you could create some "faked" datetime object
based on your month string and then use a sorting expression like
=Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"del" <del@.discussions.microsoft.com> wrote in message
news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> Using SRS 2000.
> When creating a matrix report, can you order the columns? By default,
> they
> are in alpha order. My columns are the months of the year. Using Order
> By
> in the query doesn't work.
> I want January, February..., not April, August...|||Thanks for that. That converts the string to the month number, but it
doesn't sort them 1,2,3... It's still in the same order, but with numbers.
I thought I was being smart by converting in the query using datepart and
datename.
I will look through the info you gave.
"Robert Bruckner [MSFT]" wrote:
> If you have an actual DateTime field in the dataset, you could just add a
> sort expression on the matrix column grouping like
> =Month(Fields!SalesDate.Value)
> If the column groupings are just based on a string field that actually
> contains the month names and there are no other fields that would provide
> full datetime information, you could create some "faked" datetime object
> based on your month string and then use a sorting expression like
> =Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
> See also:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "del" <del@.discussions.microsoft.com> wrote in message
> news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> > Using SRS 2000.
> >
> > When creating a matrix report, can you order the columns? By default,
> > they
> > are in alpha order. My columns are the months of the year. Using Order
> > By
> > in the query doesn't work.
> >
> > I want January, February..., not April, August...
>
>|||Here's what I did:
In the query, I used date(mm,field) to convert the date in to a month. Then
In the matrix report, I sorted by order and used the monthname function. So
after it was sorted numerically, I used the expression,
=monthname(Field!...Value) and it returns the name of the month.
I would have never looked if you hadn't pointed me in the right direction.
Thanks!
"del" wrote:
> Thanks for that. That converts the string to the month number, but it
> doesn't sort them 1,2,3... It's still in the same order, but with numbers.
> I thought I was being smart by converting in the query using datepart and
> datename.
> I will look through the info you gave.
> "Robert Bruckner [MSFT]" wrote:
> > If you have an actual DateTime field in the dataset, you could just add a
> > sort expression on the matrix column grouping like
> > =Month(Fields!SalesDate.Value)
> >
> > If the column groupings are just based on a string field that actually
> > contains the month names and there are no other fields that would provide
> > full datetime information, you could create some "faked" datetime object
> > based on your month string and then use a sorting expression like
> > =Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
> > See also:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
> > http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "del" <del@.discussions.microsoft.com> wrote in message
> > news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> > > Using SRS 2000.
> > >
> > > When creating a matrix report, can you order the columns? By default,
> > > they
> > > are in alpha order. My columns are the months of the year. Using Order
> > > By
> > > in the query doesn't work.
> > >
> > > I want January, February..., not April, August...
> >
> >
> >

Column length changes back to default automatically

Why is it that when I create a varchar column in a table through the
SQL Server 2005 Management Studio and assign it a length of, say, 10,
when I tab to the next field, the length is changed back to 50? If I
return to the column type field and correct it, it works fine.Because there is a bug in Management Studio. It will be fixed at the latest
in SQL Server 2008; in the meantime, you will either have to live with it,
or use CREATE TABLE in a query window instead of the table designer (which
has about 50 other bugs, FWIW). Here are three issues relating to your
problem:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127135
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278074
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125878
The last one suggests the bug was being considered for SP2, but I don't
think it made the bar. One of the others suggests that it has been fixed in
an early Katmai CTP. Have you updated your client tools to SP2 or better?
What does Help|About... in Management Studio tell you (the first row under
the Versions column)?
A
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
> Why is it that when I create a varchar column in a table through the
> SQL Server 2005 Management Studio and assign it a length of, say, 10,
> when I tab to the next field, the length is changed back to 50? If I
> return to the column type field and correct it, it works fine.
>|||I'm sure that's it, Aaron. This is a new install of the client tools,
and I have yet to update them with SP2.
Thanks!
--
Richard Carpenter
On Mar 19, 7:42=A0pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Because there is a bug in Management Studio. =A0It will be fixed at the la=test
> in SQL Server 2008; in the meantime, you will either have to live with it,=
> or use CREATE TABLE in a query window instead of the table designer (which=
> has about 50 other bugs, FWIW). =A0Here are three issues relating to your
> problem:
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> The last one suggests the bug was being considered for SP2, but I don't
> think it made the bar. =A0One of the others suggests that it has been fixe=d in
> an early Katmai CTP. =A0Have you updated your client tools to SP2 or bette=r?
> What does Help|About... in Management Studio tell you (the first row under=
> the Versions column)?
> A
> "Richard Carpenter" <rumble...@.hotmail.com> wrote in message
> news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
>
> > Why is it that when I create a varchar column in a table through the
> > SQL Server 2005 Management Studio and assign it a length of, say, 10,
> > when I tab to the next field, the length is changed back to 50? If I
> > return to the column type field and correct it, it works fine.- Hide quo=ted text -
> - Show quoted text -|||Richard , I could not reproduce the bug , can you tell me what did you do
step by step?
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:7dd431ea-206f-40d1-bab4-34850956501f@.m44g2000hsc.googlegroups.com...
I'm sure that's it, Aaron. This is a new install of the client tools,
and I have yet to update them with SP2.
Thanks!
--
Richard Carpenter
On Mar 19, 7:42 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Because there is a bug in Management Studio. It will be fixed at the
> latest
> in SQL Server 2008; in the meantime, you will either have to live with it,
> or use CREATE TABLE in a query window instead of the table designer (which
> has about 50 other bugs, FWIW). Here are three issues relating to your
> problem:
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> The last one suggests the bug was being considered for SP2, but I don't
> think it made the bar. One of the others suggests that it has been fixed
> in
> an early Katmai CTP. Have you updated your client tools to SP2 or better?
> What does Help|About... in Management Studio tell you (the first row under
> the Versions column)?
> A
> "Richard Carpenter" <rumble...@.hotmail.com> wrote in message
> news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
>
> > Why is it that when I create a varchar column in a table through the
> > SQL Server 2005 Management Studio and assign it a length of, say, 10,
> > when I tab to the next field, the length is changed back to 50? If I
> > return to the column type field and correct it, it works fine.- Hide
> > quoted text -
> - Show quoted text -|||Uri, I can reproduce this at will quite easily in Microsoft SQL Server
Management Studio 9.00.3042.00.
In any database, right-click "Tables" in Object Explorer, and select "New
Table..."
In Column Name type "foo", then hit Tab and in Data Type type "varchar(10)"
and hit Tab. varchar(10) should change to varchar(50).
A
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1Xc5ZmiIHA.5780@.TK2MSFTNGP06.phx.gbl...
> Richard , I could not reproduce the bug , can you tell me what did you do
> step by step?|||Thank you Aaron, I was able reproduce it too. As I see it is happening only
for the first time ( also Click change to varchar(50)) I mean if you go back
and change it again to VARCHAR(10) then SQL Server DOES save it, am I
right?
PS. Microsoft SQL Server Management Studio 9.00.3042.00.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eVHhfqoiIHA.4436@.TK2MSFTNGP02.phx.gbl...
> Uri, I can reproduce this at will quite easily in Microsoft SQL Server
> Management Studio 9.00.3042.00.
> In any database, right-click "Tables" in Object Explorer, and select "New
> Table..."
> In Column Name type "foo", then hit Tab and in Data Type type
> "varchar(10)" and hit Tab. varchar(10) should change to varchar(50).
> A
>
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e1Xc5ZmiIHA.5780@.TK2MSFTNGP06.phx.gbl...
>> Richard , I could not reproduce the bug , can you tell me what did you do
>> step by step?
>|||> Thank you Aaron, I was able reproduce it too. As I see it is happening
> only for the first time ( also Click change to varchar(50)) I mean if you
> go back and change it again to VARCHAR(10) then SQL Server DOES save it,
> am I right?
Yes, that seems right. But the user shouldn't have to override (50) twice.|||> Yes, that seems right. But the user shouldn't have to override (50)
> twice.
100% , I hope to see they fixed it in SP3 , I so hope......
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:upTjzwoiIHA.2304@.TK2MSFTNGP05.phx.gbl...
>> Thank you Aaron, I was able reproduce it too. As I see it is happening
>> only for the first time ( also Click change to varchar(50)) I mean if you
>> go back and change it again to VARCHAR(10) then SQL Server DOES save it,
>> am I right?
> Yes, that seems right. But the user shouldn't have to override (50)
> twice.
>

Column length changes back to default automatically

Why is it that when I create a varchar column in a table through the
SQL Server 2005 Management Studio and assign it a length of, say, 10,
when I tab to the next field, the length is changed back to 50? If I
return to the column type field and correct it, it works fine.
Because there is a bug in Management Studio. It will be fixed at the latest
in SQL Server 2008; in the meantime, you will either have to live with it,
or use CREATE TABLE in a query window instead of the table designer (which
has about 50 other bugs, FWIW). Here are three issues relating to your
problem:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127135
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278074
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125878
The last one suggests the bug was being considered for SP2, but I don't
think it made the bar. One of the others suggests that it has been fixed in
an early Katmai CTP. Have you updated your client tools to SP2 or better?
What does Help|About... in Management Studio tell you (the first row under
the Versions column)?
A
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
> Why is it that when I create a varchar column in a table through the
> SQL Server 2005 Management Studio and assign it a length of, say, 10,
> when I tab to the next field, the length is changed back to 50? If I
> return to the column type field and correct it, it works fine.
>
|||I'm sure that's it, Aaron. This is a new install of the client tools,
and I have yet to update them with SP2.
Thanks!
Richard Carpenter
On Mar 19, 7:42Xpm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Because there is a bug in Management Studio. XIt will be fixed at the latest
> in SQL Server 2008; in the meantime, you will either have to live with it,
> or use CREATE TABLE in a query window instead of the table designer (which
> has about 50 other bugs, FWIW). XHere are three issues relating to your
> problem:
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> The last one suggests the bug was being considered for SP2, but I don't
> think it made the bar. XOne of the others suggests that it has been fixed in
> an early Katmai CTP. XHave you updated your client tools to SP2 or better?
> What does Help|About... in Management Studio tell you (the first row under
> the Versions column)?
> A
> "Richard Carpenter" <rumble...@.hotmail.com> wrote in message
> news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
>
>
> - Show quoted text -
|||Richard , I could not reproduce the bug , can you tell me what did you do
step by step?
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:7dd431ea-206f-40d1-bab4-34850956501f@.m44g2000hsc.googlegroups.com...
I'm sure that's it, Aaron. This is a new install of the client tools,
and I have yet to update them with SP2.
Thanks!
Richard Carpenter
On Mar 19, 7:42 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Because there is a bug in Management Studio. It will be fixed at the
> latest
> in SQL Server 2008; in the meantime, you will either have to live with it,
> or use CREATE TABLE in a query window instead of the table designer (which
> has about 50 other bugs, FWIW). Here are three issues relating to your
> problem:
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> The last one suggests the bug was being considered for SP2, but I don't
> think it made the bar. One of the others suggests that it has been fixed
> in
> an early Katmai CTP. Have you updated your client tools to SP2 or better?
> What does Help|About... in Management Studio tell you (the first row under
> the Versions column)?
> A
> "Richard Carpenter" <rumble...@.hotmail.com> wrote in message
> news:61b0eefa-15f3-475c-b173-76bf35e77c50@.p73g2000hsd.googlegroups.com...
>
>
> - Show quoted text -
|||Uri, I can reproduce this at will quite easily in Microsoft SQL Server
Management Studio 9.00.3042.00.
In any database, right-click "Tables" in Object Explorer, and select "New
Table..."
In Column Name type "foo", then hit Tab and in Data Type type "varchar(10)"
and hit Tab. varchar(10) should change to varchar(50).
A
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1Xc5ZmiIHA.5780@.TK2MSFTNGP06.phx.gbl...
> Richard , I could not reproduce the bug , can you tell me what did you do
> step by step?
|||Thank you Aaron, I was able reproduce it too. As I see it is happening only
for the first time ( also Click change to varchar(50)) I mean if you go back
and change it again to VARCHAR(10) then SQL Server DOES save it, am I
right?
PS. Microsoft SQL Server Management Studio 9.00.3042.00.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eVHhfqoiIHA.4436@.TK2MSFTNGP02.phx.gbl...
> Uri, I can reproduce this at will quite easily in Microsoft SQL Server
> Management Studio 9.00.3042.00.
> In any database, right-click "Tables" in Object Explorer, and select "New
> Table..."
> In Column Name type "foo", then hit Tab and in Data Type type
> "varchar(10)" and hit Tab. varchar(10) should change to varchar(50).
> A
>
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e1Xc5ZmiIHA.5780@.TK2MSFTNGP06.phx.gbl...
>
|||> Thank you Aaron, I was able reproduce it too. As I see it is happening
> only for the first time ( also Click change to varchar(50)) I mean if you
> go back and change it again to VARCHAR(10) then SQL Server DOES save it,
> am I right?
Yes, that seems right. But the user shouldn't have to override (50) twice.
|||> Yes, that seems right. But the user shouldn't have to override (50)
> twice.
100% , I hope to see they fixed it in SP3 , I so hope......
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:upTjzwoiIHA.2304@.TK2MSFTNGP05.phx.gbl...
> Yes, that seems right. But the user shouldn't have to override (50)
> twice.
>

Column Default Value

I want a table to include these columns:
UserID, int, IDENTITY
GroupID, int
I would like for the default value of the GroupID to be equal to the UserID.
The problem is the default only accepts a constant value. Any way to
accomplish this?
Thanks.

Quote:

Originally Posted by J

I want a table to include these columns:
UserID, int, IDENTITY
GroupID, int
I would like for the default value of the GroupID to be equal to the UserID.
The problem is the default only accepts a constant value. Any way to
accomplish this?
Thanks.

You have to create trigger

Column Default Value

I want a table to include these columns:
UserID, int, IDENTITY
GroupID, int
I would like for the default value of the GroupID to be equal to the UserID.
The problem is the default only accepts a constant value. Any way to
accomplish this?
Thanks.You can create a trigger to set the GroupID to the UserID value.
CREATE TABLE Foo (
UserID INT IDENTITY NOT NULL PRIMARY KEY,
GroupID INT,
datacol CHAR(1));
GO
CREATE TRIGGER SetGroupID
ON Foo
AFTER INSERT
AS
UPDATE Foo
SET GroupID = I.UserID
FROM Foo AS F
JOIN Inserted AS I
ON F.UserID = I.UserID
AND I.GroupID IS NULL;
GO
INSERT INTO Foo (datacol) VALUES('a');
INSERT INTO Foo (GroupID, datacol) VALUES(5, 'b');
SELECT UserID, GroupID, datacol
FROM Foo;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Column default value

Hi,
Anyone of you know how to change the default value for an existing column?
I have the script below but it might be a problem to run this script on
different server because the default constraint name might be different. Any
efficient way to solve this problem?
ALTER TABLE AcctSettings DROP CONSTRAINT DF_ShowOrganizationWidePopup
GO
ALTER TABLE [dbo].[AcctSettings] ADD
CONSTRAINT [DF_AcctSettings_ShowOrganization] DEFAULT( 0 ) FOR
[ShowOrganizationWidePopup]
GO
Thanks,
KennyKenny
See INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view to get the name of
contraint
"Kenny" <keejh@.hotmail.com> wrote in message
news:%23DcH9j%23%23FHA.3676@.tk2msftngp13.phx.gbl...
> Hi,
> Anyone of you know how to change the default value for an existing column?
> I have the script below but it might be a problem to run this script on
> different server because the default constraint name might be different.
> Any efficient way to solve this problem?
> ALTER TABLE AcctSettings DROP CONSTRAINT DF_ShowOrganizationWidePopup
> GO
> ALTER TABLE [dbo].[AcctSettings] ADD
> CONSTRAINT [DF_AcctSettings_ShowOrganization] DEFAULT( 0 ) FOR
> [ShowOrganizationWidePopup]
> GO
> Thanks,
> Kenny
>|||Before dropping an existing constraint you should know its name. If the name
was created automatically (if the constraint was "created by clicking, rathe
r
by typing" :)), you need to identify its name first either by looking at the
objects in QA or by inspecting the INFORMATION_SCEMA views.
ML
http://milambda.blogspot.com/|||Unfortunately, default names are not in the information_schema tables (at le
ast not in
CONSTRAINT_COLUMN_USAGE). This is because in ANSI SQL, a default is a column
attribute, not a
constraint.
So one would have to look up the name in sysobjects and syscolumns.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ML" <ML@.discussions.microsoft.com> wrote in message
news:02BFC401-F554-41C7-95FD-7B0D19071702@.microsoft.com...
> Before dropping an existing constraint you should know its name. If the na
me
> was created automatically (if the constraint was "created by clicking, rat
her
> by typing" :)), you need to identify its name first either by looking at t
he
> objects in QA or by inspecting the INFORMATION_SCEMA views.
>
> ML
> --
> http://milambda.blogspot.com/|||Tibor

> Unfortunately, default names are not in the information_schema tables (at
> least not in CONSTRAINT_COLUMN_USAGE). This is because in ANSI SQL, a
> default is a column attribute, not a constraint.
Yes, you are right, how could I forget about it.
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23svTrw%23%23FHA.4088@.TK2MSFTNGP09.phx.gbl...
> Unfortunately, default names are not in the information_schema tables (at
> least not in CONSTRAINT_COLUMN_USAGE). This is because in ANSI SQL, a
> default is a column attribute, not a constraint.
> So one would have to look up the name in sysobjects and syscolumns.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:02BFC401-F554-41C7-95FD-7B0D19071702@.microsoft.com...
>

Collations

Hi,
I'm starting to build a database that will contain English and Welsh
text. Up until now I have just used the default collation settings, and
everything has been fine - but I've only ever needed to use English.
There is also the possibility that, at some point in the future, the
database will have to handle several Arabic and Asian languages.
So, my question is what collation should I used to make the database as
future-proof as possible given the statements above? Does, for example,
the default collation actually support the Welsh alphabet? (Welsh has
four extra characters that I don't think appear in other European
languages - ?, ? and their upper case equivalents - possibly more, but I
don't actually know Welsh!) I notice that there isn't a bog-standard
"UTF-8" option in the collation list...
Lastly, does anyone have any general tips regarding multi-lingual databases?
Cheers,
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.Hi Dylan,
Basically you collation should be the local environment where it is. So if
its in the UK it should be english etc.
With regards to your text, what you need to do is have the appropriate
datatype's for your tables.
For storing text that you would want as not only english, but another
language, instead of using a datatype of say char(10) you would use
nchar(10) this N, ensures that the type is unicode and not ANSI. Text
conversions of char can have mistakes, yet nchar represents the characters
as they should. One important note, using nchar instead of char, will
consume twice as much space as char, so just something to bear in mind.
hth
Barry Andrew
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d0c87$0$761$bed64819@.news.gradwell.net...
> Hi,
> I'm starting to build a database that will contain English and Welsh
> text. Up until now I have just used the default collation settings, and
> everything has been fine - but I've only ever needed to use English.
> There is also the possibility that, at some point in the future, the
> database will have to handle several Arabic and Asian languages.
> So, my question is what collation should I used to make the database as
> future-proof as possible given the statements above? Does, for example,
> the default collation actually support the Welsh alphabet? (Welsh has
> four extra characters that I don't think appear in other European
> languages - ?, ? and their upper case equivalents - possibly more, but I
> don't actually know Welsh!) I notice that there isn't a bog-standard
> "UTF-8" option in the collation list...
> Lastly, does anyone have any general tips regarding multi-lingual
> databases?
> Cheers,
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.|||Barry Andrew Hall wrote:
> Hi Dylan,
> Basically you collation should be the local environment where it is.
> So if its in the UK it should be english etc.
Ah okay - so I should simply leave it "as is", right?

> With regards to your text, what you need to do is have the
> appropriate datatype's for your tables.
> For storing text that you would want as not only english, but another
> language, instead of using a datatype of say char(10) you would use
> nchar(10) this N, ensures that the type is unicode and not ANSI.
> Text conversions of char can have mistakes, yet nchar represents the
> characters as they should.
Right, I understand now. So instead of using "text" to store several
paragraphs of writing, I should use "ntext" - but only in places that
could contain Welsh text.

> One important note, using nchar instead of char, will consume twice
> as much space as char, so just something to bear in mind.
In much the same way as saving a text file in UTF-8 instead of
ISO-8859-1 will consume more space. That's not too much of an issue for
me, although I guess it could cause degradation in performance?
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.|||Your right on the performance. But to be fair, if its designed solidly you
shouldnt have a thing to worry about.
Also, as you said, anything you want potentially in another language, have a
datatype of nvarchar(max) thats probably your best bet.
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d1330$0$757$bed64819@.news.gradwell.net...
> Barry Andrew Hall wrote:
> Ah okay - so I should simply leave it "as is", right?
>
> Right, I understand now. So instead of using "text" to store several
> paragraphs of writing, I should use "ntext" - but only in places that
> could contain Welsh text.
>
> In much the same way as saving a text file in UTF-8 instead of
> ISO-8859-1 will consume more space. That's not too much of an issue for
> me, although I guess it could cause degradation in performance?
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.

Collations

Hi,
I'm starting to build a database that will contain English and Welsh
text. Up until now I have just used the default collation settings, and
everything has been fine - but I've only ever needed to use English.
There is also the possibility that, at some point in the future, the
database will have to handle several Arabic and Asian languages.
So, my question is what collation should I used to make the database as
future-proof as possible given the statements above? Does, for example,
the default collation actually support the Welsh alphabet? (Welsh has
four extra characters that I don't think appear in other European
languages - ŵ, ŷ and their upper case equivalents - possibly more, but I
don't actually know Welsh!) I notice that there isn't a bog-standard
"UTF-8" option in the collation list...
Lastly, does anyone have any general tips regarding multi-lingual databases?
Cheers,
--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.Hi Dylan,
Basically you collation should be the local environment where it is. So if
its in the UK it should be english etc.
With regards to your text, what you need to do is have the appropriate
datatype's for your tables.
For storing text that you would want as not only english, but another
language, instead of using a datatype of say char(10) you would use
nchar(10) this N, ensures that the type is unicode and not ANSI. Text
conversions of char can have mistakes, yet nchar represents the characters
as they should. One important note, using nchar instead of char, will
consume twice as much space as char, so just something to bear in mind.
hth
Barry Andrew
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d0c87$0$761$bed64819@.news.gradwell.net...
> Hi,
> I'm starting to build a database that will contain English and Welsh
> text. Up until now I have just used the default collation settings, and
> everything has been fine - but I've only ever needed to use English.
> There is also the possibility that, at some point in the future, the
> database will have to handle several Arabic and Asian languages.
> So, my question is what collation should I used to make the database as
> future-proof as possible given the statements above? Does, for example,
> the default collation actually support the Welsh alphabet? (Welsh has
> four extra characters that I don't think appear in other European
> languages - ŵ, ŷ and their upper case equivalents - possibly more, but I
> don't actually know Welsh!) I notice that there isn't a bog-standard
> "UTF-8" option in the collation list...
> Lastly, does anyone have any general tips regarding multi-lingual
> databases?
> Cheers,
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.|||Barry Andrew Hall wrote:
> Hi Dylan,
> Basically you collation should be the local environment where it is.
> So if its in the UK it should be english etc.
Ah okay - so I should simply leave it "as is", right?
> With regards to your text, what you need to do is have the
> appropriate datatype's for your tables.
> For storing text that you would want as not only english, but another
> language, instead of using a datatype of say char(10) you would use
> nchar(10) this N, ensures that the type is unicode and not ANSI.
> Text conversions of char can have mistakes, yet nchar represents the
> characters as they should.
Right, I understand now. So instead of using "text" to store several
paragraphs of writing, I should use "ntext" - but only in places that
could contain Welsh text.
> One important note, using nchar instead of char, will consume twice
> as much space as char, so just something to bear in mind.
In much the same way as saving a text file in UTF-8 instead of
ISO-8859-1 will consume more space. That's not too much of an issue for
me, although I guess it could cause degradation in performance?
--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.|||Your right on the performance. But to be fair, if its designed solidly you
shouldnt have a thing to worry about.
Also, as you said, anything you want potentially in another language, have a
datatype of nvarchar(max) thats probably your best bet.
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d1330$0$757$bed64819@.news.gradwell.net...
> Barry Andrew Hall wrote:
>> Hi Dylan,
>> Basically you collation should be the local environment where it is.
>> So if its in the UK it should be english etc.
> Ah okay - so I should simply leave it "as is", right?
>> With regards to your text, what you need to do is have the
>> appropriate datatype's for your tables.
>> For storing text that you would want as not only english, but another
>> language, instead of using a datatype of say char(10) you would use
>> nchar(10) this N, ensures that the type is unicode and not ANSI.
>> Text conversions of char can have mistakes, yet nchar represents the
>> characters as they should.
> Right, I understand now. So instead of using "text" to store several
> paragraphs of writing, I should use "ntext" - but only in places that
> could contain Welsh text.
>> One important note, using nchar instead of char, will consume twice
>> as much space as char, so just something to bear in mind.
> In much the same way as saving a text file in UTF-8 instead of
> ISO-8859-1 will consume more space. That's not too much of an issue for
> me, although I guess it could cause degradation in performance?
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.

Collation Sequence Incompatibilities in ODBC

We are getting the following errors when running our application on a new database server that has a default collation sequence different from the server we developed the application on.

We are using SQL_Latin1_General_CP1_Cl_AS on our dev box, but some production boxes may not have this collation sequence since some of our clients are international.

The database itself uses this sequence and show this sequence even on the other server. If we restore the database on a server with this sequence the problem disappears, but that is not always an option.

I want to figure out how to bypass it, by either altering the collation sequence of the database or something else. In short I have to figure out how to make it work in their environment. Any ideas are much appreciated. Thanks. -- Henry :-)

P.S. I checked Microsoft and know that the issue exists. The only article I could find had to do with SMS and their recommendation of installing on a server with a different collation sequence does not resolve my problem. I can't always do that.
Our application is written in Cold Fusion 5.0

============ Error Messages ============

Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve
collation conflict for equal to operation.

Error Occurred While Processing Request
Error Diagnostic Information

System Error &H80004005 (-2147467259).

===================================While this does not directly address the issue (My guess is both installs are 7.0?), perhaps your issue would be addressed by Sql Server 2k features. Sql Server 2k supports collation configurations somewhat differently from 7.0 i.e.(Sql Server 2K should support installing a server instance with a given collation and sucessfully hosting DBs with a variety of different collation configurations). It may be worth your while to test Sql Server 2k against your specific application(s) collation requirements.|||No. Both installs are SQL Server 2000. At least one is service pack 2.|||No. Both installs are SQL Server 2000. At least one is service pack 2.

S1 Then you must know about / have attempted using the tsql collation clause in queries (to force character string expressions which may include columns to apply a particular collation cast)? While one might have to add the clause to numerous queries and tsql statements in application code or withing stored procs, views, etc., the advantage of this approach is that the DB itself is not changed. You might want to isolate some problem code on a test restore configuration to get a better idea of how involved the approach may be.

S2 Another option may be (as you mentioned) to use Alter Table on the tables of the restored DB (on the target) to make it compatible with the applications in the locale where it is restored. Have you tested the results with this approach using a development test install / DB restore setup?

S3 I suggest keeping service packs and hotfixes the same accross installations (if for nothing else, to eliminate added vairables in addressing possible issues).

Collation Questions SQL Server 2005 SP2

A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.

The default collation for SQLS is apparently SQL_Latin1_General_CP1_CI_AS.

I wish to use a variation of this, SQL_Latin1_General_CP1_CS_AI
collation, but there is no such collation returned from
fn_helpcollations(). Also, if I try to use this collation in
a CREATE DATABASE stmt, SQLS yells about it.

I see that there is a Latin1_General_CS_AI. What effects are there
in using this collation? The SQL_* collations are SQL collations,
while non-SQL_* collations are Windows collations, yes? SQLS runs
only on Windows, so am I safe in using Latin1_General_CS_AI? What
does the CP1 in the SQL collation signify? Am I asking for trouble?
-----------
Assuming that I set Latin1_General_CS_AI (or any other case-sensitive
collation) at the database level, I believe my DDL/DML for that database
also becomes case-sensitive. How can I specify that I want ONLY my data
access to be case-sensitive, and not my DDL/DML? I don't want to have
to remember to type "select * from MyCamelCase" when "mycamelcase"
should work.

Any help appreciated.

A new SQLS DBA..

ajaj (ronald@.mcdonalds.com) writes:

Quote:

Originally Posted by

A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.
>
The default collation for SQLS is apparently SQL_Latin1_General_CP1_CI_AS.

The default collation when you install SQL Server depends on your regional
settings. SQL_Latin1_General_CP1_CI_AS is what you get when your regional
settings is English (US).

Quote:

Originally Posted by

I see that there is a Latin1_General_CS_AI. What effects are there
in using this collation? The SQL_* collations are SQL collations,
while non-SQL_* collations are Windows collations, yes? SQLS runs
only on Windows, so am I safe in using Latin1_General_CS_AI? What
does the CP1 in the SQL collation signify? Am I asking for trouble?

You should be fine. About everywhere else in the world when you install SQL
Server, the default collation is a Windows collation. For instance, in my
case it's Finnish_Swedish_CI_AS (but I always change it to
Finnish_Swedish_CS_AS.)

Windows collations are drawn from Windows and Unicode, and the sorting for
varchar and nvarchar data is the same (save that nvarchar includes far more
characters). SQL collations on the other hand are completely different
for varchar and nvarchar. For varchar they are just an 8-bit character
set, while for nvarchar they are Unicode. The flip side of this is since
they for varchar only have 255 charcters, operations with varchar are
quite a bit faster with SQL collations than with Windows collations (save
binary collations). However, there are also potential for performance
disasters with SQL collations if you join varchar and nvarchar that
are less likly to occur with Windows collations.

Quote:

Originally Posted by

Assuming that I set Latin1_General_CS_AI (or any other case-sensitive
collation) at the database level, I believe my DDL/DML for that database
also becomes case-sensitive. How can I specify that I want ONLY my data
access to be case-sensitive, and not my DDL/DML? I don't want to have
to remember to type "select * from MyCamelCase" when "mycamelcase"
should work.

You would have to set the database collation to be one that fits your
preference for identifiers and then explcitly set the collation for each
column to be case-sensitive.

My strong recommendation is that you should always develop on a
case-sensitive collation. If you develop on a case-insensitive collation,
and the customer then insists on case-sensitive, you may have a complete
mess to sort out.

Personally, I don't see the point of using MyCamelCase, if you don't
care to remember how you originally defined it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your response. This confirms what we suspected.

Do you have any idea why there is no SQL_Latin1_General_CP1_CS_AI
collation? Can I get it somewhere?

Erland Sommarskog wrote:

Quote:

Originally Posted by

aj (ronald@.mcdonalds.com) writes:

Quote:

Originally Posted by

>A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.
>>
>The default collation for SQLS is apparently SQL_Latin1_General_CP1_CI_AS.

>
The default collation when you install SQL Server depends on your regional
settings. SQL_Latin1_General_CP1_CI_AS is what you get when your regional
settings is English (US).
>

Quote:

Originally Posted by

>I see that there is a Latin1_General_CS_AI. What effects are there
>in using this collation? The SQL_* collations are SQL collations,
>while non-SQL_* collations are Windows collations, yes? SQLS runs
>only on Windows, so am I safe in using Latin1_General_CS_AI? What
>does the CP1 in the SQL collation signify? Am I asking for trouble?

>
You should be fine. About everywhere else in the world when you install SQL
Server, the default collation is a Windows collation. For instance, in my
case it's Finnish_Swedish_CI_AS (but I always change it to
Finnish_Swedish_CS_AS.)
>
Windows collations are drawn from Windows and Unicode, and the sorting for
varchar and nvarchar data is the same (save that nvarchar includes far more
characters). SQL collations on the other hand are completely different
for varchar and nvarchar. For varchar they are just an 8-bit character
set, while for nvarchar they are Unicode. The flip side of this is since
they for varchar only have 255 charcters, operations with varchar are
quite a bit faster with SQL collations than with Windows collations (save
binary collations). However, there are also potential for performance
disasters with SQL collations if you join varchar and nvarchar that
are less likly to occur with Windows collations.
>

Quote:

Originally Posted by

>Assuming that I set Latin1_General_CS_AI (or any other case-sensitive
>collation) at the database level, I believe my DDL/DML for that database
>also becomes case-sensitive. How can I specify that I want ONLY my data
>access to be case-sensitive, and not my DDL/DML? I don't want to have
>to remember to type "select * from MyCamelCase" when "mycamelcase"
>should work.

>
You would have to set the database collation to be one that fits your
preference for identifiers and then explcitly set the collation for each
column to be case-sensitive.
>
My strong recommendation is that you should always develop on a
case-sensitive collation. If you develop on a case-insensitive collation,
and the customer then insists on case-sensitive, you may have a complete
mess to sort out.
>
Personally, I don't see the point of using MyCamelCase, if you don't
care to remember how you originally defined it.
>
>

|||aj (ronald@.mcdonalds.com) writes:

Quote:

Originally Posted by

Do you have any idea why there is no SQL_Latin1_General_CP1_CS_AI
collation?

No. Maybe they never developed this combiniation for SQL collations, but got
it for free with Windows collation. fn_helpcollations tells me that there
are no CS_AI SQL collation at all.

Quote:

Originally Posted by

Can I get it somewhere?

No. There is support for adding collations.

If you feel strongly about it, you can submit a suggestion on
http://connect.microsoft.com/SqlServer/Feedback.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

collation problem

I have a database that has Korean_Wansung_CI_AS as the default collation. I
need to use this database to interact with another database (e.g. merge
tables and views) that has SQL_Latin1_General_CP1_CI_AS collation. The
first step is to change the default collation of the first databse from
Korean to Latin1. How do I do this?
Thanks, Dick
use ALTER DATABASE
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Dick Swager" <cswager.no.spam@.cox.net> wrote in message
news:e5PVqLpJGHA.964@.tk2msftngp13.phx.gbl...
> I have a database that has Korean_Wansung_CI_AS as the default collation.
I
> need to use this database to interact with another database (e.g. merge
> tables and views) that has SQL_Latin1_General_CP1_CI_AS collation. The
> first step is to change the default collation of the first databse from
> Korean to Latin1. How do I do this?
> Thanks, Dick
>
|||Thanks. I still have a problem though because the 'alter database' fails.
The failure is probably because many of the tables were created using the
Korean collation and so there are many columns that are dependent on it.
Does that make sense?
I intend to try to alter the tables get get rid of such a dependency and try
again, but there are a lot of tables. I don't think there are any Korean
characters used int eh database though so I should be able to do it.
Dick
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:droai9$2ct$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> use ALTER DATABASE
> --
> Jack Vamvas
> __________________________________________________ ________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Dick Swager" <cswager.no.spam@.cox.net> wrote in message
> news:e5PVqLpJGHA.964@.tk2msftngp13.phx.gbl...
> I
>
|||I have the simillar problem. Please follow this thread.
http://groups.google.com/group/comp...643198997e7b3f
|||Thanks Sai. This looks like just the thing I need. It has been put on the
back burner though so I won't have the chance to report any results for a
while.
Dick
"Sai" <sbillanuka@.gmail.com> wrote in message
news:1138784646.094858.34330@.o13g2000cwo.googlegro ups.com...
>I have the simillar problem. Please follow this thread.
> http://groups.google.com/group/comp...643198997e7b3f
>

collation problem

When I create a database it takes the default collation of my server. Okay
so far.
But if I later try to change the default collation of the database, this
does not work.
I use:
ALTER DATABASE [test2]
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
This command runs succesful (says the Query Analyzer), but when I look at
the properties of the database in the Enterprise Manager the collation is
empty, and if I generate a CREATE TABLE command with the Query Analyzer then
the server collation is used.
So it looks if nothing happened.
What's wrong?
Jan van Veldhuizen
What does this have to do with DTS ?
That said this works for me
CREATE DATABASE A
GO
EXEC sp_helpdb 'A'
--Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=539, Collation=Latin1_General_CI_AS, SQLSortOrder=0,
IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics
ALTER DATABASE [A]
COLLATE SQL_Latin1_General_CP1_CS_AS
EXEC sp_helpdb 'A'
--Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CS_AS,
SQLSortOrder=51, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"Jan van Veldhuizen" <jan@.van-veldhuizen.nl> wrote in message
news:u3Q3a$UhEHA.2416@.TK2MSFTNGP10.phx.gbl...
> When I create a database it takes the default collation of my server. Okay
> so far.
> But if I later try to change the default collation of the database, this
> does not work.
> I use:
> ALTER DATABASE [test2]
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> This command runs succesful (says the Query Analyzer), but when I look at
> the properties of the database in the Enterprise Manager the collation is
> empty, and if I generate a CREATE TABLE command with the Query Analyzer
then
> the server collation is used.
> So it looks if nothing happened.
> What's wrong?
> Jan van Veldhuizen
>
>

collation problem

I have a database that has Korean_Wansung_CI_AS as the default collation. I
need to use this database to interact with another database (e.g. merge
tables and views) that has SQL_Latin1_General_CP1_CI_AS collation. The
first step is to change the default collation of the first databse from
Korean to Latin1. How do I do this?
Thanks, Dickuse ALTER DATABASE
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Dick Swager" <cswager.no.spam@.cox.net> wrote in message
news:e5PVqLpJGHA.964@.tk2msftngp13.phx.gbl...
> I have a database that has Korean_Wansung_CI_AS as the default collation.
I
> need to use this database to interact with another database (e.g. merge
> tables and views) that has SQL_Latin1_General_CP1_CI_AS collation. The
> first step is to change the default collation of the first databse from
> Korean to Latin1. How do I do this?
> Thanks, Dick
>|||Thanks. I still have a problem though because the 'alter database' fails.
The failure is probably because many of the tables were created using the
Korean collation and so there are many columns that are dependent on it.
Does that make sense?
I intend to try to alter the tables get get rid of such a dependency and try
again, but there are a lot of tables. I don't think there are any Korean
characters used int eh database though so I should be able to do it.
Dick
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:droai9$2ct$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> use ALTER DATABASE
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Dick Swager" <cswager.no.spam@.cox.net> wrote in message
> news:e5PVqLpJGHA.964@.tk2msftngp13.phx.gbl...
> I
>|||I have the simillar problem. Please follow this thread.
6aac5b4737dc29/a4643198997e7b3f#a4643198997e7b3f" target="_blank">http://groups.google.com/group/comp...4643198997e7b3f|||Thanks Sai. This looks like just the thing I need. It has been put on the
back burner though so I won't have the chance to report any results for a
while.
Dick
"Sai" <sbillanuka@.gmail.com> wrote in message
news:1138784646.094858.34330@.o13g2000cwo.googlegroups.com...
>I have the simillar problem. Please follow this thread.
> 896aac5b4737dc29/a4643198997e7b3f#a4643198997e7b3f" target="_blank">http://groups.google.com/group/comp...4643198997e7b3f
>

collation problem

I have a database that has Korean_Wansung_CI_AS as the default collation. I
need to use this database to interact with another database (e.g. merge
tables and views) that has SQL_Latin1_General_CP1_CI_AS collation. The
first step is to change the default collation of the first databse from
Korean to Latin1. How do I do this?
Thanks, Dickuse ALTER DATABASE
--
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Dick Swager" <cswager.no.spam@.cox.net> wrote in message
news:e5PVqLpJGHA.964@.tk2msftngp13.phx.gbl...
> I have a database that has Korean_Wansung_CI_AS as the default collation.
I
> need to use this database to interact with another database (e.g. merge
> tables and views) that has SQL_Latin1_General_CP1_CI_AS collation. The
> first step is to change the default collation of the first databse from
> Korean to Latin1. How do I do this?
> Thanks, Dick
>|||Thanks. I still have a problem though because the 'alter database' fails.
The failure is probably because many of the tables were created using the
Korean collation and so there are many columns that are dependent on it.
Does that make sense?
I intend to try to alter the tables get get rid of such a dependency and try
again, but there are a lot of tables. I don't think there are any Korean
characters used int eh database though so I should be able to do it.
Dick
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:droai9$2ct$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> use ALTER DATABASE
> --
> Jack Vamvas
> __________________________________________________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Dick Swager" <cswager.no.spam@.cox.net> wrote in message
> news:e5PVqLpJGHA.964@.tk2msftngp13.phx.gbl...
>> I have a database that has Korean_Wansung_CI_AS as the default collation.
> I
>> need to use this database to interact with another database (e.g. merge
>> tables and views) that has SQL_Latin1_General_CP1_CI_AS collation. The
>> first step is to change the default collation of the first databse from
>> Korean to Latin1. How do I do this?
>> Thanks, Dick
>>
>|||I have the simillar problem. Please follow this thread.
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/896aac5b4737dc29/a4643198997e7b3f#a4643198997e7b3f|||Thanks Sai. This looks like just the thing I need. It has been put on the
back burner though so I won't have the chance to report any results for a
while.
Dick
"Sai" <sbillanuka@.gmail.com> wrote in message
news:1138784646.094858.34330@.o13g2000cwo.googlegroups.com...
>I have the simillar problem. Please follow this thread.
> http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/896aac5b4737dc29/a4643198997e7b3f#a4643198997e7b3f
>