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 the datatype of a column which is part of a PK

Hi All
I have a table made up of 2 columns as the PK. I am trying to write a script
that changes the datatype of one of the columns which is part of the primary
key. This is what i have tried but i seem not to be getting anywhere:
ALTER TABLE TB1 DROP CONSTRAINT PK_TB1
ALTER COLUMN ACCOUNT VARCHAR(20) NOT NULL
Is there a way to do this? I tried to drop the PKs and then do the changes
and then recreate the PK again. All this gave me errors. Thank you in
advance.> Is there a way to do this? I tried to drop the PKs and then do the changes
> and then recreate the PK again. All this gave me errors.
What does "tried" mean? What errors?
This is the way you do it. You drop the constraints (both primary *and*
foreign key), you change the column, you re-apply the constraints.|||Thank you. It worked.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "tried" mean? What errors?
> This is the way you do it. You drop the constraints (both primary *and*
> foreign key), you change the column, you re-apply the constraints.
>
>

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.

Changing datatype: smallint to int in a large table..

Hi,

I need to change the datatype of a very large table from smallint to int...
What would be an ideal solution to get this done in least amount of time. May be I can try with ALTER but , I am not sure about the time it would take ...and the page splits etc..

pls help on the same!!

Thanks
Cheriyan.Hi,
If the table has thousands of records,definitely it will take some time to alter the datatype and in the mean time make sure that no application is running which is interacting with that table.

Madhivanan|||Thanks Madhivanan

Is there any way to get around this...any alternate solution with would reduce the total time required..|||Not really. You need to do the ALTER TABLE. Dropping any indexes that hit this column would speed it up.|||rdjabarov would suggest that you BCP the data out, create a new table, and then BCP the data back in. It's not something I've done a lot, but its worth a shot.|||How can I alter my settings in dbForums so that I can be notified when an answer that I would have come up with and is known to everybody can be sent to me as if I posted it, and at the same time increase MY postings counter?|||Go to the dbforums/forumstore page and select a 1 point item and I will get it and have it sent to you. They have some new stuff in, so check it out.|||I checked. All they have left is gag balls and gimp masks in gift wrappers. Give me your address I'll send you a set ;)|||Oh, that's nasty.|||rdjabarov would suggest that you BCP the data out, create a new table, and then BCP the data back in. It's not something I've done a lot, but its worth a shot.

Are you kidding...that's the only way to FLY!

bcp out...need to do it in CHAR format,drop table, recreate with new types, and bcp back in...|||Out of curiosity, how big is the table (i.e. number of rows)? I am not sure where the turning point is for the performance of BCP vs. just issuing the command. Naturally for a table of 10 rows, you would just issue the alter table command. For a table of 10,000,000 rows, you would probably bcp. Has anyone experimented with the two methods to see where the turnover point is?|||I agree...however...the more paractice the better, especially with less data...the screw ups won't take as long to undo....

But yes, 10 would be silly|||Yes, I found the exact number of rows where BCP becomes more efficient, but I have to let rdjabarov tell you. I will tell you, however, that it is neither a prime number, palindromic, or a perfect square. ;)|||Who cares what the number is, it's how long your mgmt will allow you to keep users from production, that's the determinant. Besides, it depends on what field you ALTER (change, add, or remove), so you (blindman) don't really KNOW the exact number, alright?!|||Oh, brother....|||What? He started it again! Why do I have to come up with the number? It's Brett's job to write code!|||There is no number in these parts...get the scrubs to practice bcp all day long...|||No, no, no. There is no SPOON. There are plenty of numbers. Maybe I should have asked for an order of ten, instead?|||Yes. It's true. I don't really know the number.

Mea culpa.

But I'd still bet its not a prime...

Speaking of no spoons; I've got a stint at a bank in a division called NEO, and they just issued me a generic ID badge that just has NEO where the name should be. I'm half expecing Dr. Smith to leap out at me every time I round a corner.|||Folks, plz clear me here. Will changing the recovery model from full to BULK or Simple have effect on the performance of the alter command? SIMPLE would log all the trans through but clear the log and what about BULK-LOGGED? For i am not sure if each record for that column is written into tran-log or not. sorry for askin stupid questions. :o|||I don't think it is a stupid question. The recovery model setting would help the BCP method, but I do not think it would help the alter table method, as alter table is fully logged, anyway. The only "non"-logged operations that I know of (off the top of my head first thing in the morning) are BCP/bulkinsert, truncate, and select into. Each of these, of course has some logging to them, and the indexes on the involved table would also have changes logged, so make sure you have the drop and create index scripts handy.|||The only "non"-logged operations that I know of (off the top of my head first thing in the morning) are BCP/bulkinsert, truncate, and select into.

Ahhh...be careful...that's a common misconception that even M$ doesn't make too clear...it's logged at the page level...

Each of these, of course has some logging to them, and the indexes on the involved table would also have changes logged, so make sure you have the drop and create index scripts handy.

Very good...

It's not about the recovery model...it's about the logging

BUT..the recovery model dictates how you should be performing maintenance|||Yes. I should have made that a bit clearer, rather than just putting in the quotes around "non". My lawyer said I could get away with it, though ;-).|||OK, let's just close this "logged/non-logged/page-level-logged" subject once and for all, shall we? NON-LOGGED means that it does not get written to the log file, and that's per Microsoft and all MVP's that know what they are talking about (don't remember names, but have read enough of their stuff and experimented with it to believe it to be true). The fact that you can ROLLBACK any non-logged operation does not mean that it's logged, partially logged, logged at the page level (who came up with this?), etc. Besides, size allocation for a table is not measured in pages, so when a table is truncated for example, it's the EXTENTs that get released back into the unused portion of the database. And again, going back to ROLLBACK, - you can rollback a truncate, but not because it's "partially logged or logged at the page level", but because of the ACID qualities of a transaction.|||OK, let's just close this "logged/non-logged/page-level-logged" subject once and for all, shall we? NON-LOGGED means that it does not get written to the log file, and that's per Microsoft and all MVP's that know what they are talking about (don't remember names, but have read enough of their stuff and experimented with it to believe it to be true). The fact that you can ROLLBACK any non-logged operation does not mean that it's logged, partially logged, logged at the page level (who came up with this?), etc. Besides, size allocation for a table is not measured in pages, so when a table is truncated for example, it's the EXTENTs that get released back into the unused portion of the database. And again, going back to ROLLBACK, - you can rollback a truncate, but not because it's "partially logged or logged at the page level", but because of the ACID qualities of a transaction.

Ok then...but extents for a truncate?

What about bulk insert (or bcp)? Are you saying that it doesn't try to place the data on the appropriate page for a cluster?

So after those processes, the need to be rebuilt?

Thanks fo the info...still clear as mud...

ever find an article that discusses this...

Damn, another reason why I need to buy Kalens book...|||It's an extent, then "an appropriate page", both for BCP and BULK INSERT. If it was on a page level, - can you imagin the amount of defragmentation even in pubs?

Changing Datatype length

Hi all,
I need to change a varchar from 35 to 50. In the SQL Server books on
line it says that SQL Server actually creates a new table when you
change the length. I ran a test in a test database and it appears the
only thing that changes is the length. All the data remains in tact.

The table with the column I want to modify is very critical. Is there
any chance I would loose data if I change the length to a larger size? I
am making a back up of the table just in case. Thanks,
KellyKelly Prendergast (kelly.prendergast@.noaa.gov) writes:
> I need to change a varchar from 35 to 50. In the SQL Server books on
> line it says that SQL Server actually creates a new table when you
> change the length. I ran a test in a test database and it appears the
> only thing that changes is the length. All the data remains in tact.
> The table with the column I want to modify is very critical. Is there
> any chance I would loose data if I change the length to a larger size? I
> am making a back up of the table just in case. Thanks,

If you use "ALTER TABLE tbl ALTER COLUMN col varchar(50)"
all that will happens is that metadata will be updated, which will occur
in a snap. If you were to change a char(35) column to char(50), I
would expect it to be different, because in this case SQL Server would
move around data to leave room for the value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9442489D4AB0Yazorman@.127.0.0.1...
> Kelly Prendergast (kelly.prendergast@.noaa.gov) writes:
> > I need to change a varchar from 35 to 50. In the SQL Server books on
> > line it says that SQL Server actually creates a new table when you
> > change the length. I ran a test in a test database and it appears the
> > only thing that changes is the length. All the data remains in tact.
> > The table with the column I want to modify is very critical. Is there
> > any chance I would loose data if I change the length to a larger size? I
> > am making a back up of the table just in case. Thanks,
> If you use "ALTER TABLE tbl ALTER COLUMN col varchar(50)"
> all that will happens is that metadata will be updated, which will occur
> in a snap. If you were to change a char(35) column to char(50), I
> would expect it to be different, because in this case SQL Server would
> move around data to leave room for the value.

I want to add to Erland's answer to address the final question. You will
NOT lose data.

SQL Server treats this as a transactional change so either the change will
complete in full, or nothing will change.

If it DOES create a new table the pseudo-SQL is:

Begin Tran
select into TEMP from FOO
drop table FOO
sp_renameobject TEMP to FOO
if error ROLLBACK Tran
else End tran

So the change is completely atomic. Nothing to worry about.

>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Changing datatype from int to decimal

hi

how to Change datatype from int to decimal with precission 8 and scale 3.in ms sql

Quote:

Originally Posted by war

hi

how to Change datatype from int to decimal with precission 8 and scale 3.in ms sql

Moved to MS SQL forum.
And please do not double post.|||In Enterprise Manager right click on table name and choose 'Design Table'

Good Luck.|||Alter table <tableName>
Alter Column <ColumnName> Decimal(8,3)
Go

Changing datatype from char to datetime

I am trying to run the following query:

ALTER TABLE dnb_profile
ALTER COLUMN [family update date] datetime

and I keep getting the following error:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
The statement has been terminated.

Can anyone tell me how I can do this successfully??

Thanks,

Connie Sawyer
Foley & Lardner
clsawyer@.foley.comOn 27 Sep 2004 09:15:29 -0700, Connie Sawyer wrote:

>I am trying to run the following query:
>ALTER TABLE dnb_profile
>ALTER COLUMN [family update date] datetime
>and I keep getting the following error:
>Server: Msg 242, Level 16, State 3, Line 1
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value.
>The statement has been terminated.

Hi Connie,

This indicates that at least one value currently in the [family update
date] column is of a format that won't convert to SQL Server properly.
There may be various explanations:

1. Someone managed to enter some gibbledygook in the column - possible,
since it's of the char data type. True rubbish would result in another
error message, but dates like february 30, december 53 or some date in
month number 17 would yield this message.

2. The contents of the column may look like normal dates to you, but not
to SQL Server. The error message you got is quite common if SQL Server
interprets day as month and month as day. Remember that there are manu
different notation styles for dates. The only unambiguous date formats are
yyyymmdd (for date only) or yyyy-mm-ddThh:mm:ss.mmm (for date and time,
where .mmm, denoting the milliseconds, is optional).

In each case, you'll have to inspect your data to find the cause and
either manually fix the offending rows (if there are just a few) or do
some string massaging to change from a misunderstood date format to one of
the standard formats before converting.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Connie Sawyer wrote:

> I am trying to run the following query:
> ALTER TABLE dnb_profile
> ALTER COLUMN [family update date] datetime
> and I keep getting the following error:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> The statement has been terminated.
> Can anyone tell me how I can do this successfully??
> Thanks,
> Connie Sawyer
> Foley & Lardner
> clsawyer@.foley.com

What you should do is to run this:

SELECT * FROM dnb_profile
WHERE ISDATE([family update date])=0

This will return you all the records
where value of [family update date] can't be converted to date.
And you should fix those records before altering the column.

Here's the link to the isdate function:

http://msdn.microsoft.com/library/d..._ia-iz_8ov9.asp

WYGL,
Andrey

changing data types

hi!
I'd like to change the datatype of one tablefield from varchar to text.
Is this possible when the table already is populated, or will there be trouble?
obscurrDepending the version you're using.

In any way

exec sp_rename MyTable, MyOldTable
select Col1, Col2, convert(TEXT, ColVarchar) ColVarchar
into MyTable
from MyOldTable
drop table MyOldTable|||I'm using SQL Server version 7.0