Changing column from nvarchar to int

I am upgrading an Access database and need to convert some of the fields
from text to a foreign key from another table. This is no problem and I
already have the query batch to do this. I would like to know if it is
possible to change a column type from nvarchar to int.
Thanks,
Drew LaingAs long as all the NVARCHAR values can be converted to an INT, sure.
ALTER TABLE tablename ALTER COLUMN columnname INT
http://www.aspfaq.com/
(Reverse address to reply.)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>|||Drew,
You can do it if the values are numeric or null. If the value can not be
casted, then you will get an error.
Example:
use northwind
go
create table t (
colA varchar(15)
)
go
insert into t values('1')
insert into t values('2')
insert into t values(null)
go
select * from t
go
alter table t
alter column colA int
go
select * from t
go
alter table t
alter column colA varchar(15)
go
insert into t values ('a')
go
-- will fail
alter table t
alter column colA int
go
select * from t
go
drop table t
go
AMB
"Drew" wrote:

> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>
>|||Thanks to both!
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
>I am upgrading an Access database and need to convert some of the fields
>from text to a foreign key from another table. This is no problem and I
>already have the query batch to do this. I would like to know if it is
>possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>

Changing all VARCHAR to NVARCHAR database-wide?

Hi,
I have an ASP.NET application that uses VARCHAR extensively in the tables and, more importantly, stored procedures (a couple hundred of them).

This app needs to start accepting foreign language in some areas, so I was wondering if there was some way to go through the tables and, more importantly, the stored procedures and change all "VARCHAR" references to "NVARCHAR" ?

Are the stored procedures stored as a text file somewhere on the server? If so I could use some sort of "replace" software utility to go through and change all VARCHAR to NVARCHAR

thanks!

-BretYou should be able to write a script to change your tables - it will take you a while since you probably will need to:

For each table with Varchar columns
- Create a temp table
- Copy all the data to the temp table
- Remove all FK constraints to the table
- Drop the table
- Create the table with NVarchar columns
- Insert the data from the temp table
- Create the IDENTITY column if applicable
- Recreate all the FK constraints

If you have fewer than 100 tables or so it might be quicker to use EM to do this.

For the stored procs: The quickest way to do the change is to allow updates on system tables. You can change this in EM. Then you can find all the stored procedures in sysobjects, with the actual TSQL in syscomments (in the 'text' column) Of course it's not normally a good idea to update system tables directly, and I'm not 100% sure this will work, but it's worth a shot:

update C
 set C.Text = replace(C.[Text], 'varchar', 'nvarchar')
 from syscomments C
 join sysobjects O
 on C.[id] = O.[id]
 and O.Type = 'P'

Now obviously there's a lot of things that can go wrong here. You string 'varchar' may exist as a part of a variable name, or column name or some other place where it's not supposed to be replaced by 'nvarchar'. It's possible that a stored procedure is long enough to use more than one row in syscomments, and this will be a problem since 'nvarchar' is one character more than 'varchar' so the update will fail with 'string or binary data would be truncated'.

So be careful and be sure to test every SP. Again you can write a script to execute every stored proc in your DB and check for errors.

Have fun!|||You have to change to Unicode Collation, nothing serious in SQL Server 2000 you can change it down to column level but Unicode in SQL Server is Multi byte unlike .NET where you can choose UTF 8 which is equivalent to ASCII. Yes Stored Procs are stored in .SQL text files that you can open with notepad and can be seen under stored procs in the Enterprise Manager in your Database. Run a search in the BOL(books online) for Mixed collation and ALTER PROCEDURE. If you are now using UTF 8 in .NET you may not run into storage problems except disk space which will double. Stored procs in SQL Server 2000 can be up to 128 megs in size. Hope this helps.

Kind regards,
Gift Peddie

change row size

Hi everybody!!
I have a table with some nvarchar columns. When I try to put the size I can't to put it greater than 4000.
How can i change the row size?
Thanks!!That's the limit for that data type. Internally SQL Server uses 2*n bytes to store it. If you want to go up to 8000 bytes and don't need to use unicode character you should switch to varchar.

Change ntext to nvarchar(max) in a live database

I have a live SQL 2005 database that has ntext fields, when the ntext fields go over 4000 chars the record can no longer be edited. It throws a string or binary data would be truncated error. I tried turning text in row OFF, but it did not work. Can anyone forsee any problems with changing the ntext fields to nvarchar(max) in the live database? Also, I came across sp_tableoption N'MyTable', 'large value types out of row', 'ON', does this work for ntext also? sp_tableoption N'MyTable', 'text in row', 'OFF' did not do anything.

Any help would be appreciated.

Have you set TEXTSIZE option for the connection? Check this using:

DBCC USEROPTIONS

|||

Where and how do I do this? Also I changed the ntext fields in the database to NVarChar(MAX), so now I can modify the values in the database directly, but when I do it through the application and the VarChar(MAX) fields are over 4000 it does not save the changes, but does not return an error either. It returns as if the stored procedure executed successfully.

|||

I'm not sure why you felt the need to create yet another thread, but the answer remains the same (and the 3rd time I'm giving it to you):

The problem is more likely that the parameters are being declared as either the wrong type, or you are not declaring the type at all, and letting it default. Make sure your ntext parameters are declared as such.

|||

Actually it was a problem upgrading a SQL 2000 database to SQL 2005 then modifying it, I had to recreate the table. Once I recreated the table and did a Select Into, the problem was resolved.

And I created another thread because I wanted to change the datatypes from ntext to nvarchar(max) in a live database and wanted to know if it was going to have any ill effects. In my experience, when you ask a different question in an existing thread, you do not get an answer for both questions, so I wanted to keep them separated.