Changing the DataType of a Coluumn in an In-Memory Dataset

Here is the issue. I have ReadOnly Access to a database. All of the Columns are set to NVARCHAR(1000) by default. I cannot change them. I want to load the DataSet into memory and change the DataType of the columns from NVARCHAR(1000) to INT(4). The data is in integer (i.e. 4,5,123) format (but stored as a string), but is coming across as strings. The charting software I am using won't implicitly convert these Strings to Int or Double. How can I change an entire column to Int?

You need ANSI SQL ALTER COLUMN, I am not sure if you can do that for read only. Try the url below for your options. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

Kind regards,

Gift Peddie

|||Or, you could cast in in the SELECT statement. Like so: SELECT someColumn, otherColumn, CAST(thisColumn AS INT) AS thisColumnFROM yourTable...
But really, if you have an NVARCHAR(1000) column that's nothing butintegers, you really *should* change the underlying column in the tableto be the proper type.
|||I know, but this DB is the result of a portal tool, which I have no control over. I will try Casting the column to int on the select statement. Thanks.

Changing nVarChar lengths

I have a db with about 6.5 million records. A few
records have a set number of characters, however the
table design specifies more characters than is really
necessary. I'd like to reclaim as much space as
possible. Am I safe to change the field lengths for the
Char fields to the maximum number of characters I know
they will ever contain, without losing any data? Will I
have to change to nVarChar? If so, any data loss?If your sure the new size is larger than the largest piece of data for any
row you should not have a problem in changing it from a database standpoint.
But you may break existing code or apps if you do. That depends on how they
use it etc.
--
Andrew J. Kelly
SQL Server MVP
"Mike" <tatemike44@.hotmail.com> wrote in message
news:033201c36e6e$8574ad20$a401280a@.phx.gbl...
> I have a db with about 6.5 million records. A few
> records have a set number of characters, however the
> table design specifies more characters than is really
> necessary. I'd like to reclaim as much space as
> possible. Am I safe to change the field lengths for the
> Char fields to the maximum number of characters I know
> they will ever contain, without losing any data? Will I
> have to change to nVarChar? If so, any data loss?

Changing Datatypes

Hi All,

I have a database that contains 25 tables. In these tables the character data was stored as nvarchar datatype. Since i am not using any unicode data and the space occupied by nvarchar is more , i want to change the datatype from nvarchar to varchar. I cannot do one by one since there are 25 tables and each table has atleast 10 columns of datatype nvarchar.

How can i change the datatype from nvarchar to varchar in all the tables. Should i use any cursors or is there any other way. I am newbie to SQL server i dont know how to use cursors.

Can u please guide me the steps to be followed to convert to varchar datatype.

Thank you.Hi All,

I have a database that contains 25 tables. In these tables the character data was stored as nvarchar datatype. Since i am not using any unicode data and the space occupied by nvarchar is more , i want to change the datatype from nvarchar to varchar. I cannot do one by one since there are 25 tables and each table has atleast 10 columns of datatype nvarchar.

How can i change the datatype from nvarchar to varchar in all the tables. Should i use any cursors or is there any other way. I am newbie to SQL server i dont know how to use cursors.

Can u please guide me the steps to be followed to convert to varchar datatype.

Thank you.|||Hi All,

I have a database that contains 25 tables. In these tables the character data was stored as nvarchar datatype. Since i am not using any unicode data and the space occupied by nvarchar is more , i want to change the datatype from nvarchar to varchar. I cannot do one by one since there are 25 tables and each table has atleast 10 columns of datatype nvarchar.

How can i change the datatype from nvarchar to varchar in all the tables. Should i use any cursors or is there any other way. I am newbie to SQL server i dont know how to use cursors.

Can u please guide me the steps to be followed to convert to varchar datatype.

Thank you.

------------------------

Reply|||Create a cursor that goes through all the tables in the databases, looking at the table structure, and altering as necessary.|||

Quote:

Originally Posted by sandyboy

Should i use any cursors or is there any other way. I am newbie to SQL server i dont know how to use cursors.

You are saying to be a newbie to SQL server and dont know how to use cursors. and again u want to write cursors.

U should know the fundaments first of all .

And please don;t post the same question repeatedly.

Please follow the posting guidelines before posting.

consider it as first warning to you.