Converting from nvarchar(4000) to ntext

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Converting from nvarchar(4000) to ntext

Author

Topic

Zippeh
Starting Member

9 Posts

Posted - 2007-01-31 : 04:34:43

Hi there,

I have a nvarchar(4000) field, and it's not long enough. The only place I can go therefore is for a ntext field. When I attempt to do the conversion I get warned about possibly losing information. Since this information is very sensitive and there is no way to validate it afterwards, how guaranteed can I be that nothing will get lost after the field conversion?

Thanks in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 04:38:34

How did you try the convert?

UPDATE <YourTableNameHere>
SET <YourNewNTextColumnNameHere> = <YourOldNVarCharColumnNameHere>

Peter Larsson
Helsingborg, Sweden

Zippeh
Starting Member

9 Posts

Posted - 2007-01-31 : 04:39:18

I was doing it in the "Design Table" bit on Enterprise Manager.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 04:39:35

And later do a

select *
from <yourtablenamehere>
where datalength(<YourNewNTextColumnNameHere>) <> datalangth(<YourOldNVarCharColumnNameHere>)
or <YourNewNTextColumnNameHere> <> <YourOldNVarCharColumnNameHere>

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 04:41:48

You can, but add a new column in EM first and then run the update query and the check query.
If satisfied with result, delete the old column in EM and rename the new column name to the old column name.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-01-31 : 05:23:42

"When I attempt to do the conversion I get warned about possibly losing information"

That doesn't sound right ...

"I was doing it in the "Design Table" bit on Enterprise Manager."

In the Design Table is a "Save changes Script" tool. Press that, save the Script, then ABANDON the changes, and post the script here. I reckon there is something goofy going on, there is no way I can think of that you could lose data going from Nvarchar(4000) to Ntext.

Kristen

Zippeh
Starting Member

9 Posts

Posted - 2007-01-31 : 06:22:08

Here is the generated SQL:


BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_tbl_proffil_gwybodaeth_pellach
	(
	rhif_defnyddiwr int NOT NULL,
	gwybodaeth_pellach ntext COLLATE Latin1_General_CI_AS NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.tbl_proffil_gwybodaeth_pellach)
	 EXEC('INSERT INTO dbo.Tmp_tbl_proffil_gwybodaeth_pellach (rhif_defnyddiwr, gwybodaeth_pellach)
		SELECT rhif_defnyddiwr, CONVERT(ntext, gwybodaeth_pellach) FROM dbo.tbl_proffil_gwybodaeth_pellach TABLOCKX')
GO
DROP TABLE dbo.tbl_proffil_gwybodaeth_pellach
GO
EXECUTE sp_rename N'dbo.Tmp_tbl_proffil_gwybodaeth_pellach', N'tbl_proffil_gwybodaeth_pellach', 'OBJECT'
GO
ALTER TABLE dbo.tbl_proffil_gwybodaeth_pellach ADD CONSTRAINT
	PK_tbl_proffil_gwybodaeth_pellach PRIMARY KEY CLUSTERED 
	(
	rhif_defnyddiwr
	) WITH FILLFACTOR = 90 ON [PRIMARY]

GO
COMMIT

Zippeh
Starting Member

9 Posts

Posted - 2007-01-31 : 06:23:09

And here is the original error message:


'tbl_proffil_gwybodaeth_pellach' table
- Warning: Data may be lost converting column 'gwybodaeth_pellach' from 'nvarchar(4000)'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-31 : 06:28:39

1. There is no need for explicit conversion
2. There doesn't seem to be need of Dynamic SQL

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-01-31 : 07:25:29

"1. There is no need for explicit conversion
2. There doesn't seem to be need of Dynamic SQL"

Yeah, that's what Enterprise Manager generates though - I've never understood why it uses Dynamic SQL.

Either way, they will do no harm, and I have no idea why it reports that a possible data loss could occur.

Will this automatically trim any trailing spaces perhaps?

I presume the COLLATION has not changed at the same time??

My suggestion is you run those statements one at a time. If you get any errors then immediately do a ROLLBACK and then we can have another go!

If you want to be certain you could add (after the IF EXISTS and before the DROP TABLE):


SELECT S.rhif_defnyddiwr
FROM dbo.tbl_proffil_gwybodaeth_pellach AS S
     JOIN dbo.Tmp_tbl_proffil_gwybodaeth_pellach AS D
         ON S.rhif_defnyddiwr = D.rhif_defnyddiwr
WHERE DATA_LENGTH(S.gwybodaeth_pellach <> D.gwybodaeth_pellach)

which will show you any row where the length has changed.

Kristen

Zippeh
Starting Member

9 Posts

Posted - 2007-01-31 : 09:49:35

How would i incorporate the check into one statement that either carried on and committed or did a rollback?

i.e.


IF NOT EXISTS (SELECT S.rhif_defnyddiwr
FROM dbo.tbl_proffil_gwybodaeth_pellach AS S
     JOIN dbo.Tmp_tbl_proffil_gwybodaeth_pellach AS D
         ON S.rhif_defnyddiwr = D.rhif_defnyddiwr
WHERE DATA_LENGTH(S.gwybodaeth_pellach <> D.gwybodaeth_pellach))
BEGIN
DROP TABLE dbo.tbl_proffil_gwybodaeth_pellach
GO
EXECUTE sp_rename N'dbo.Tmp_tbl_proffil_gwybodaeth_pellach', N'tbl_proffil_gwybodaeth_pellach', 'OBJECT'
GO
ALTER TABLE dbo.tbl_proffil_gwybodaeth_pellach ADD CONSTRAINT
	PK_tbl_proffil_gwybodaeth_pellach PRIMARY KEY CLUSTERED 
	(
	rhif_defnyddiwr
	) WITH FILLFACTOR = 90 ON [PRIMARY]

GO
COMMIT
END
ELSE
BEGIN
ROLLBACK
END

Would something like that do?

Kristen
Test

22859 Posts

Posted - 2007-01-31 : 11:53:41

Do you really need this to be automated to run safely, or can you just do it by hand (i.e. just this once)

Kristen

Zippeh
Starting Member

9 Posts

Posted - 2007-02-01 : 04:07:51

My problem is that the tables could be written to at any time, as they are used by the public on our website. There is no way to lock the system. So I thought a full script that would do it all would be a lot quicker and no one would notice!

I've got my script to do work now on 2 tables in my test database. It's just having the balls to do it on the live that I'm missing at the moment!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 04:15:22

Do something like my customer has done.
Create a TRIGGER that has some timebased logic within.
On one table, all the TRIGGER does, is "ROLLBACK TRAN" if time between 12:00 (noon) and 12:30 monday, wednesday and friday.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-01 : 05:18:09

"It's just having the balls to do it on the live that I'm missing at the moment!"

I have to do this type of task whilst the system is in use sometimes.

0) Take a DIFF or TLOG backup to assist recovery process if needed.

1) BEGIN TRANSACTION

2) Lock the table

3) Perform the DDL actions one at a time

4) COMMIT if OK, or ROLLBACK if any of the statements give errors. Query the comparison of lengths in there if you want to be sure on that point.

The importance of one at a time is that a number of statements, with a GO in between them, can rollback the transaction block and then the remainder of the statements will be running WITHOUT a transaction block!! But if you run them one at a time you can use a ROLLBACK the moment any of them fail.

For example: if you run it all in one go and the INSERT into the temporary table fails, the transaction block will rollback, and then the "DROP TABLE dbo.tbl_proffil_gwybodaeth_pellach" will run outside the transaction. Very bad news!

For this sort of hot-fix-whilst-live job I think the statement-by-statement process, done manually, is safer. if you build an automated package and it fails unexpected half way through what then?

For a big rollout, fully tested and so on, then I would run a script and expect it to catch any problems etc. But I would have the system offline whilst I did that so that I could restore back if it went pear shaped.

Kristen

Zippeh
Starting Member

9 Posts

Posted - 2007-02-01 : 07:26:24

Thank you very much for your help Kristen! I've learnt a lot whilst trying to do this! Even so, I managed to mess it up, but have recovered now after suffering a mini heart attack!

Kristen
Test

22859 Posts

Posted - 2007-02-01 : 07:44:19

"Even so, I managed to mess it up, "

Yeah, well I do that too and I've got years of experience ....

... the rollout I did the other day had two separate foul-ups. Each took 45 minutes to do the Restore. For a 3 hours maintenance window, which actually needed 4 hours work doing, the additional 1.5 hours recovery time wasn't good

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources