funky errors for nvarchar(max) |
|
How to Convert NVarchar(Max) to Ntext from the working database. |
Solution 1:
1.
Delete the Catalog.
2.
Manually convert the
Nvarchar(Max) columns to Ntext by below TSQL Query
----------------------------------------------------------------------------------------
--TSQL Query 1:
-- This will list
all NVarchar(Max) columns
select
sysobjects.name
as TableName, syscolumns.name
as ColumnName
from
sysobjects,syscolumns
where
sysobjects.id=syscolumns.id
and
sysobjects.xtype='u'
and
syscolumns.xtype=231
and
syscolumns.length
=
-1
order
by
sysobjects.name,syscolumns.name
----------------------------------------------------------------------------------------
--TSQL Query 2:
--Example:
--Query block 1
ALTER
TABLE
[dbo].SaveSetInfo
DROP
CONSTRAINT
DF__SaveSetIn__SaveS__382F5661
GO
--Query block 2
ALTER
TABLE
SaveSetInfo
ALTER
COLUMN
SaveSetNotes
NTEXT
GO
--Query block 3
ALTER
TABLE
[dbo].SaveSetInfo
ADD
CONSTRAINT
DF__SaveSetIn__SaveS__382F5661
DEFAULT
('')
FOR
SaveSetNotes
GO
----------------------------------------------------------------------------------------
Note:
Execute the TSQL Query 1, it will list all the
columns and tables which have NVarChar(Max). Now
execute TSQL Query 2 query block 2 (ALTER TABLE
Users ALTER COLUMN AppPermissions NTEXT), by replace
in with appropriate table name and column name, if
constrained defined it will rise an error, Now from
that error collect the constrained name and replace
in the TSQL Query 2 and run all the 3 blocks and vice
versa.
3. Create the Catalog.
Link:
http://www.cbizsoft.com/download/files/cBizOnecatalogScript.sql
Solution 2:
If there are more columns to convert Nvarchar(Max) to Ntext then prefer the below.
-
Create a New Database
-
Import the Current database tables to New
database
-
Now execute the Hosting Script on New
database
-
Delete the Current database
-
Create the catalog on New database
-
Ask the users to connect to New database
Note:
Don't follow the second solution if you have
Triggers on the database |
