Hi
we moved from 2000 to 2003 (version 9)
I do Open Table to see all rows
when I try to change a value say 2 to 3 in int field it pops up dialog and
say
like driver error string or binary data will be truncated
but it is an integer field and the values are like 2 and 3!
is it isolation level related perhaps
what setting is it
we just installed it so it must be default
Sincerely
GSL
Hi
"info@.sites4agents.com" wrote:
> Hi
> we moved from 2000 to 2003 (version 9)
> I do Open Table to see all rows
> when I try to change a value say 2 to 3 in int field it pops up dialog and
> say
> like driver error string or binary data will be truncated
> but it is an integer field and the values are like 2 and 3!
> is it isolation level related perhaps
> what setting is it
> we just installed it so it must be default
> Sincerely
> GSL
This should not be an issue under normal circumstances. Have you service
packed your version of SQL 2005? Are you trying this on a client or the
server itself?
Are there any triggers on the table?
John
|||John
Thanks
No SPs as far as I know (how will I know?)
the table does have text column but then in sql2000 it was updatable
manually
No triggers
I do it on the server
also! when I do it in Query like regular update statement it works!
(so maybe it forces good habits nevertheless I am curious why it happens)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25EACFD4-9897-4D6E-AEA6-E7936186542A@.microsoft.com...
> Hi
> "info@.sites4agents.com" wrote:
>
> This should not be an issue under normal circumstances. Have you service
> packed your version of SQL 2005? Are you trying this on a client or the
> server itself?
> Are there any triggers on the table?
> John
>
|||Hi
"info@.sites4agents.com" wrote:
> John
> Thanks
> No SPs as far as I know (how will I know?)
Select @.@.VERSION will give you the version number also SELECT
SERVERPROPTERY('ProductLevel ') will tell if a service pack has been applied
or is still at the original release (RTM). See
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html for more.
> the table does have text column but then in sql2000 it was updatable
> manually
> No triggers
> I do it on the server
> also! when I do it in Query like regular update statement it works!
> (so maybe it forces good habits nevertheless I am curious why it happens)
The error message you have seems to be a SQL Server error message, if you
have the error number it would be useful. You could post DDL and example data
http://www.aspfaq.com/etiquette.asp?id=5006 to help others re-create this.
Open table in Enterprise Manager was always quirky therefore it is not
something a lot of people will use in SQL 2005 because of that.
John
Showing posts with label value. Show all posts
Showing posts with label value. Show all posts
Thursday, March 8, 2012
cannot update
Hi
we moved from 2000 to 2003 (version 9)
I do Open Table to see all rows
when I try to change a value say 2 to 3 in int field it pops up dialog and
say
like driver error string or binary data will be truncated
but it is an integer field and the values are like 2 and 3!
is it isolation level related perhaps
what setting is it
we just installed it so it must be default
Sincerely
GSLHi
"info@.sites4agents.com" wrote:
> Hi
> we moved from 2000 to 2003 (version 9)
> I do Open Table to see all rows
> when I try to change a value say 2 to 3 in int field it pops up dialog and
> say
> like driver error string or binary data will be truncated
> but it is an integer field and the values are like 2 and 3!
> is it isolation level related perhaps
> what setting is it
> we just installed it so it must be default
> Sincerely
> GSL
This should not be an issue under normal circumstances. Have you service
packed your version of SQL 2005? Are you trying this on a client or the
server itself?
Are there any triggers on the table?
John|||John
Thanks
No SPs as far as I know (how will I know?)
the table does have text column but then in sql2000 it was updatable
manually
No triggers
I do it on the server
also! when I do it in Query like regular update statement it works!
(so maybe it forces good habits nevertheless I am curious why it happens)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25EACFD4-9897-4D6E-AEA6-E7936186542A@.microsoft.com...
> Hi
> "info@.sites4agents.com" wrote:
>
> This should not be an issue under normal circumstances. Have you service
> packed your version of SQL 2005? Are you trying this on a client or the
> server itself?
> Are there any triggers on the table?
> John
>|||Hi
"info@.sites4agents.com" wrote:
> John
> Thanks
> No SPs as far as I know (how will I know?)
Select @.@.VERSION will give you the version number also SELECT
SERVERPROPTERY('ProductLevel ') will tell if a service pack has been applied
or is still at the original release (RTM). See
http://sqlserver2000.databases.aspf...-m-running.html for more.
> the table does have text column but then in sql2000 it was updatable
> manually
> No triggers
> I do it on the server
> also! when I do it in Query like regular update statement it works!
> (so maybe it forces good habits nevertheless I am curious why it happens)
The error message you have seems to be a SQL Server error message, if you
have the error number it would be useful. You could post DDL and example dat
a
http://www.aspfaq.com/etiquette.asp?id=5006 to help others re-create this.
Open table in Enterprise Manager was always quirky therefore it is not
something a lot of people will use in SQL 2005 because of that.
John
we moved from 2000 to 2003 (version 9)
I do Open Table to see all rows
when I try to change a value say 2 to 3 in int field it pops up dialog and
say
like driver error string or binary data will be truncated
but it is an integer field and the values are like 2 and 3!
is it isolation level related perhaps
what setting is it
we just installed it so it must be default
Sincerely
GSLHi
"info@.sites4agents.com" wrote:
> Hi
> we moved from 2000 to 2003 (version 9)
> I do Open Table to see all rows
> when I try to change a value say 2 to 3 in int field it pops up dialog and
> say
> like driver error string or binary data will be truncated
> but it is an integer field and the values are like 2 and 3!
> is it isolation level related perhaps
> what setting is it
> we just installed it so it must be default
> Sincerely
> GSL
This should not be an issue under normal circumstances. Have you service
packed your version of SQL 2005? Are you trying this on a client or the
server itself?
Are there any triggers on the table?
John|||John
Thanks
No SPs as far as I know (how will I know?)
the table does have text column but then in sql2000 it was updatable
manually
No triggers
I do it on the server
also! when I do it in Query like regular update statement it works!
(so maybe it forces good habits nevertheless I am curious why it happens)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25EACFD4-9897-4D6E-AEA6-E7936186542A@.microsoft.com...
> Hi
> "info@.sites4agents.com" wrote:
>
> This should not be an issue under normal circumstances. Have you service
> packed your version of SQL 2005? Are you trying this on a client or the
> server itself?
> Are there any triggers on the table?
> John
>|||Hi
"info@.sites4agents.com" wrote:
> John
> Thanks
> No SPs as far as I know (how will I know?)
Select @.@.VERSION will give you the version number also SELECT
SERVERPROPTERY('ProductLevel ') will tell if a service pack has been applied
or is still at the original release (RTM). See
http://sqlserver2000.databases.aspf...-m-running.html for more.
> the table does have text column but then in sql2000 it was updatable
> manually
> No triggers
> I do it on the server
> also! when I do it in Query like regular update statement it works!
> (so maybe it forces good habits nevertheless I am curious why it happens)
The error message you have seems to be a SQL Server error message, if you
have the error number it would be useful. You could post DDL and example dat
a
http://www.aspfaq.com/etiquette.asp?id=5006 to help others re-create this.
Open table in Enterprise Manager was always quirky therefore it is not
something a lot of people will use in SQL 2005 because of that.
John
cannot update
Hi
we moved from 2000 to 2003 (version 9)
I do Open Table to see all rows
when I try to change a value say 2 to 3 in int field it pops up dialog and
say
like driver error string or binary data will be truncated
but it is an integer field and the values are like 2 and 3!
is it isolation level related perhaps
what setting is it
we just installed it so it must be default
Sincerely
GSLHi
"info@.sites4agents.com" wrote:
> Hi
> we moved from 2000 to 2003 (version 9)
> I do Open Table to see all rows
> when I try to change a value say 2 to 3 in int field it pops up dialog and
> say
> like driver error string or binary data will be truncated
> but it is an integer field and the values are like 2 and 3!
> is it isolation level related perhaps
> what setting is it
> we just installed it so it must be default
> Sincerely
> GSL
This should not be an issue under normal circumstances. Have you service
packed your version of SQL 2005? Are you trying this on a client or the
server itself?
Are there any triggers on the table?
John|||John
Thanks
No SPs as far as I know (how will I know?)
the table does have text column but then in sql2000 it was updatable
manually
No triggers
I do it on the server
also! when I do it in Query like regular update statement it works!
(so maybe it forces good habits nevertheless I am curious why it happens)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25EACFD4-9897-4D6E-AEA6-E7936186542A@.microsoft.com...
> Hi
> "info@.sites4agents.com" wrote:
>> Hi
>> we moved from 2000 to 2003 (version 9)
>> I do Open Table to see all rows
>> when I try to change a value say 2 to 3 in int field it pops up dialog
>> and
>> say
>> like driver error string or binary data will be truncated
>> but it is an integer field and the values are like 2 and 3!
>> is it isolation level related perhaps
>> what setting is it
>> we just installed it so it must be default
>> Sincerely
>> GSL
> This should not be an issue under normal circumstances. Have you service
> packed your version of SQL 2005? Are you trying this on a client or the
> server itself?
> Are there any triggers on the table?
> John
>|||Hi
"info@.sites4agents.com" wrote:
> John
> Thanks
> No SPs as far as I know (how will I know?)
Select @.@.VERSION will give you the version number also SELECT
SERVERPROPTERY('ProductLevel ') will tell if a service pack has been applied
or is still at the original release (RTM). See
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html for more.
> the table does have text column but then in sql2000 it was updatable
> manually
> No triggers
> I do it on the server
> also! when I do it in Query like regular update statement it works!
> (so maybe it forces good habits nevertheless I am curious why it happens)
The error message you have seems to be a SQL Server error message, if you
have the error number it would be useful. You could post DDL and example data
http://www.aspfaq.com/etiquette.asp?id=5006 to help others re-create this.
Open table in Enterprise Manager was always quirky therefore it is not
something a lot of people will use in SQL 2005 because of that.
John
we moved from 2000 to 2003 (version 9)
I do Open Table to see all rows
when I try to change a value say 2 to 3 in int field it pops up dialog and
say
like driver error string or binary data will be truncated
but it is an integer field and the values are like 2 and 3!
is it isolation level related perhaps
what setting is it
we just installed it so it must be default
Sincerely
GSLHi
"info@.sites4agents.com" wrote:
> Hi
> we moved from 2000 to 2003 (version 9)
> I do Open Table to see all rows
> when I try to change a value say 2 to 3 in int field it pops up dialog and
> say
> like driver error string or binary data will be truncated
> but it is an integer field and the values are like 2 and 3!
> is it isolation level related perhaps
> what setting is it
> we just installed it so it must be default
> Sincerely
> GSL
This should not be an issue under normal circumstances. Have you service
packed your version of SQL 2005? Are you trying this on a client or the
server itself?
Are there any triggers on the table?
John|||John
Thanks
No SPs as far as I know (how will I know?)
the table does have text column but then in sql2000 it was updatable
manually
No triggers
I do it on the server
also! when I do it in Query like regular update statement it works!
(so maybe it forces good habits nevertheless I am curious why it happens)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25EACFD4-9897-4D6E-AEA6-E7936186542A@.microsoft.com...
> Hi
> "info@.sites4agents.com" wrote:
>> Hi
>> we moved from 2000 to 2003 (version 9)
>> I do Open Table to see all rows
>> when I try to change a value say 2 to 3 in int field it pops up dialog
>> and
>> say
>> like driver error string or binary data will be truncated
>> but it is an integer field and the values are like 2 and 3!
>> is it isolation level related perhaps
>> what setting is it
>> we just installed it so it must be default
>> Sincerely
>> GSL
> This should not be an issue under normal circumstances. Have you service
> packed your version of SQL 2005? Are you trying this on a client or the
> server itself?
> Are there any triggers on the table?
> John
>|||Hi
"info@.sites4agents.com" wrote:
> John
> Thanks
> No SPs as far as I know (how will I know?)
Select @.@.VERSION will give you the version number also SELECT
SERVERPROPTERY('ProductLevel ') will tell if a service pack has been applied
or is still at the original release (RTM). See
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html for more.
> the table does have text column but then in sql2000 it was updatable
> manually
> No triggers
> I do it on the server
> also! when I do it in Query like regular update statement it works!
> (so maybe it forces good habits nevertheless I am curious why it happens)
The error message you have seems to be a SQL Server error message, if you
have the error number it would be useful. You could post DDL and example data
http://www.aspfaq.com/etiquette.asp?id=5006 to help others re-create this.
Open table in Enterprise Manager was always quirky therefore it is not
something a lot of people will use in SQL 2005 because of that.
John
Sunday, February 19, 2012
Cannot set Default value for column using "Alter column"
Well here's one of those excruciatingly simple obstacles:
In SQL Server 2005 (Mgmt Studio): according to BOL, the syntax to set a default value for an existing column is:
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'
However, when I Check:
alter table CommissionPayment alter column Amount Set Default 0
I get the error message:
"Incorrect syntax near the keyword 'Set'."
No other combinations of this syntax work.
Help! What am I missing?? DEFAULT is not a column property in T-SQL -- it's actually implemented as a constraint. The correct syntax is: CREATE TABLE #x (id int) ALTER TABLE #x ADD CONSTRAINT DF_id DEFAULT (0) FOR id Where in BOL did you see that syntax? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Karl Kaiser@.discussions.microsoft.com> wrote in message news:a5caf4ff-8884-4383-960f-b4f61aad2da2@.discussions.microsoft.com...Well here's one of those excruciatingly simple obstacles:In SQL Server 2005 (Mgmt Studio): according to BOL, the syntax to set a default value for an existing column is:[b]ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'[/b]However, when I Check:[b]alter table CommissionPayment alter column Amount Set Default 0[/b]I get the error message:[b]"Incorrect syntax near the keyword 'Set'."[/b]No other combinations of this syntax work.Help! What am I missing?|||The syntax you saw was for SQL Server Mobile Edition. It is not applicable for the other SQL Server editions. So the correct way is to add default as a constraint using syntax posted by Adam.
Subscribe to:
Posts (Atom)