error converting the char value BUT IT IS A CHAR ALREADY

SQL Server 2000 SP3a
Access 2000 SP3 (using adp)
Im going mildly loopy, hopefully someone can aid my sanity.
Table in SQL Server had a column (column_a tinyint), data contained is just
ones and zeros. In Enterprise Manager I changed the data type of column_a t
o
char(1), so that I could then go and replace the ones and zeros with 'Y' and
'N'
When I try and change one of the values, through either Access or Enterprise
Manager, it errors with:
"Syntax error converting the char value 'Y' to a column of data type int"
I don't understand, I've changed the columns data type from tinyint to
char(1), so surely these updates should work. Whats the problem with this?
I've taken the db offline, I've even tried booting the server (clutching at
straws). What might not have been updated correctly?
Any suggestions would be great!
Its these stupid things which should be a two second job, but end up taking
me hours. And yes, if I'd got it right in the first instance, then this
wouldn't have been a problem :)
Thanks for any replies.I would have thought that the logical way to proceed would have been:
1. Add a field of Char(1) to the table with a different name than the source
field
2. Update this new field to 'Y' and 'N' based on the source field's values
3. Remove the source field
4. Rename the new field to that of the source field.
I'm surprised that SQLEM would even let you change a TinyInt to a Char in th
e
first place. If it did, what values are in that field?
Thomas|||1- Be sure it has been changed. See deifnition using view
information_schema.columns
2 - Try doing the update from query analyzer.
update table1
set colB = substring('NY', cast(colB as int) + 1, 1)
AMB
"Steve'o" wrote:

> SQL Server 2000 SP3a
> Access 2000 SP3 (using adp)
> Im going mildly loopy, hopefully someone can aid my sanity.
> Table in SQL Server had a column (column_a tinyint), data contained is jus
t
> ones and zeros. In Enterprise Manager I changed the data type of column_a
to
> char(1), so that I could then go and replace the ones and zeros with 'Y' a
nd
> 'N'
> When I try and change one of the values, through either Access or Enterpri
se
> Manager, it errors with:
> "Syntax error converting the char value 'Y' to a column of data type int"
> I don't understand, I've changed the columns data type from tinyint to
> char(1), so surely these updates should work. Whats the problem with this
?
> I've taken the db offline, I've even tried booting the server (clutching a
t
> straws). What might not have been updated correctly?
> Any suggestions would be great!
> Its these stupid things which should be a two second job, but end up takin
g
> me hours. And yes, if I'd got it right in the first instance, then this
> wouldn't have been a problem :)
> Thanks for any replies.|||> Table in SQL Server had a column (column_a tinyint), data contained is
just
> ones and zeros. In Enterprise Manager I changed the data type of column_a
to
> char(1), so that I could then go and replace the ones and zeros with 'Y'
and
> 'N'
> When I try and change one of the values, through either Access or
Enterprise
> Manager, it errors with:
> "Syntax error converting the char value 'Y' to a column of data type int"
While I don't have any obvious suggestion to help with EM, Access has a bit
of odd behavior. If you "Link" a SQL Server table into an Access database,
Access "remembers" the schema of the table, so that if you then do something
to the schema of the SQL Server table, Access is not aware of the change.
The workaround that I have used is to delete the linked table in Access and
re-link to it. I also like to rename it to get rid of the stupid "dbo_"
prefix that Access adds.
--
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||Wow, several replies, thanks.
Thomas
I did not mention this, but there are several triggers and check consraints,
basically several dependencies to the column_a. I did try creating a new
column_a_new (char1), then tried renaming column_a to column_a_old, but it
wasn't having it because of the dependencies. In an Access .mdb you can get
away with this, but sql server appears a bit more rigid in this type of
workaround.
Alejandro
I made the changes a few days ago (I have been trying to fix it in that
time) and the server and cient machine have been booted a few times.
I double checked information_scheme.columns, great suggestion thanks, and
got this which seems a little odd to me as I set it to char(1), which is doe
s
display as in Access and EM.
ORDINAL_POSITION
16
COLUMN_DEFAULT
('N')
IS_NULLABLE
No
DATA_TYPE
nvarchar
CHARACTER_MAXIMUM_LENGTH
50
CHARACTER_OCTET_LENGTH
100
QA gives the same error when doing an update set
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'N' to a column of data type int.
Mike
Yeah, there are lots of little tricks which need to be remembered with
Access (especially 2000) and SQL Server :)
I am using an .adp which is a bit different to an .mdb with linked tables,
but F5 is definitely your freind :)
I have booted the client and server, as I've been messing around for a few
days, so it should be refreshed. And it occurs in EM+QA too.
Thanks to all replies, its sounding horribly like Im going to have to drop
all dependencies, then re-create them after deleting the old column and
adding a new one. Maybe use EM to create a .sql script of the objects
including drop_all first, then alter the script to use char, then run
it....What a hassle, certainly won't make this mistake again, hopefully ;)
PS, is it better to reply to each individual post, or top post with a msg
like this, or bottom post with a msg like this ie including replies to
several people?
Or does it not really matter?
"Mike Labosh" wrote:

> just
> to
> and
> Enterprise
> While I don't have any obvious suggestion to help with EM, Access has a bi
t
> of odd behavior. If you "Link" a SQL Server table into an Access database
,
> Access "remembers" the schema of the table, so that if you then do somethi
ng
> to the schema of the SQL Server table, Access is not aware of the change.
> The workaround that I have used is to delete the linked table in Access an
d
> re-link to it. I also like to rename it to get rid of the stupid "dbo_"
> prefix that Access adds.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>|||> I did not mention this, but there are several triggers and check consraints,d">
> basically several dependencies to the column_a. I did try creating a new
> column_a_new (char1), then tried renaming column_a to column_a_old, but it
> wasn't having it because of the dependencies. In an Access .mdb you can g
et
> away with this, but sql server appears a bit more rigid in this type of
> workaround.
The obvious answer is to drop the triggers and check constraints before you
run
through the steps I suggested and then re-add the triggers and check constra
ints
afterwards. It's not that difficult. You can use the query analyzer to scrip
t
the creation of the appropriate triggers and check constraints as well as sc
ript
the drop scripts for these. Besides, it is likely that these triggers and ch
eck
constraints need to change anyway.

> PS, is it better to reply to each individual post, or top post with a msg
> like this, or bottom post with a msg like this ie including replies to
> several people?
> Or does it not really matter?
Doubt it matters much.
Thomas|||On Mon, 11 Apr 2005 10:00:01 -0700, Steve'o wrote:

>PS, is it better to reply to each individual post, or top post with a msg
>like this, or bottom post with a msg like this ie including replies to
>several people?
>Or does it not really matter?
Hi Steve'o,
Trying to start a flame-war? <grin>
Top-posting vs bottom-posting can turn into a heated debate. I believe
that most Usenet regulars prefer bottom-posting or inline reply (quote,
reply, quote, reply, ...). However, MS Outlook defaults to top-posting,
as does (if I recall correctly) MS' internet portal to the MS related
groups.
Since this group is about an MS product, a relatively large number of
users use Outlook or MS' internet portal for access. As a result, you'll
see lots of top-posting here.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)