.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Replace all Varchar to Nvarchar

Posted By:      Posted Date: October 22, 2010    Points: 0   Category :ASP.Net

Hi all,

i want to convert all my database columns whose data type is char,varchar, nchar to nvarchar datatype. But the main problem is that some of my primary key, foreign key columns are also of varchar datatype. So when i try to change their datatypes to nvarchar then i receive the errors related to constraints of Primary Key, Unique Key and indexes. So please tell me that how to change the datatypes to nvarchar after droping the constraints and after changing all constraints will apply again. I also want that no data loss will occur. So please give me some solution. Will be thankfull to all of you

View Complete Post

More Related Resource Links

Replace string in xml without converting to varchar using SQL Server

We have a xml datatype in SQL server which gets a xml file loaded into it. We want to replace some of the content in the xml datatype, but are running into problems converting from xml to varchar(for manipulation purposes), back to xml to output. Does anyone know of a way to replace xml without converting to varchar?Thanks in advance

CLR Stored procedure SQLString (NVarchar) to varchar.


Hi trying to upgrade my current SQL 2000 database to 2008.

All the legacy code uses VARCHAR: tables columns, variables, input/output of SPs and XPs etc...

I'm trying to port an existing extended stored procedure to CLR stored procedure. The output parameter for the old xp is varchar(8000). In CLR it is obviously SQLString which maps to NVARCHAR(4000)

Now the legacy code which is expecting VARCHAR(8000) output is calling the new CLR which returns NVARCHAR(4000).  The CLR procedure can indeed return more then 4000 characters. I can use SQL facet size of -1 to force NVARCHAR(MAX). But I hear the performance is slow. Also what are the consequences of a CLR returning NVARCHAR to an SQL SP that expects VARCHAR?

Is there a way to return larger string from a CLR to SQL code that expects varchar?

This is the error I'm getting

Msg 6522, Level 16, State 1, Procedure xp_XYZ, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "xp_XYZ":

System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 8000 bytes to a T-SQL type with a smaller size limit of 8000 bytes.


 at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)

 at Syste

Quick Question in using nvarchar and varchar


Basically I am desiging a schema for datawarehouse in multi language support and iam setting up a suitable collation for this. But in some of the fields in different tables i am using nvarchar as a data type for some fileds where i need to insert different language characters. But in some fileds i am thinking of  using varchar where i am expecting just english language data.

My question is if i use varchar and nvarchar in the different fields on the same table in this database. Is there any performance issues ?

or just use nvarchar on all the fields where i am getting alphanumeric data.

What you people suggest.

Please tell me.


Search for Clustered Index in database on varchar, nvarchar column



I have very big database, i am running a Re-index job and it's keep failing throwing everyday error stating "Online index operation cannot be performed on clustered index as index contains column of data type text, nvarchar.

is there any way i can search and change all such datatype of culstered into non-clustered indexes or any better solution i have.

Bug: Does not change column type from nvarchar to ntext


Scenario: If you create a table with a column of type nvarchar, populate it with some content and try to modify it using the WebMatrix editor, from nvarchar to ntext, it does not display any error message or warning that this is not possible. It silently saves and reverts the change, back to nvarchar. Wasted some hours on this issue as I thought my column was ntext, but in fact was still nvarchar.

Expected behavior: It should change to ntext, or if SQL CE doesn't support that, it should notify the user that no change was applied.

I need help about nvarchar field



how can specified nvarchar data type without N''?

Can I replace the EntityObject as base-class in Entity Designer 3.5 sp1?


I'm using EF3.5sp1 for a project, and I've been working around many of its problems... as such, my entity partial classes have a lot of logic in them for change-tracking and whatnot.  Obviously, I'd like to factor this logic out into a common base class, but of course all my objects must derive from EntityObject.  Is it possible to tell them to derive from some class of my own invention (which would actually likely derive from EntityObject themselves)?

I'd rather avoid editing the EDMX file unless the changes will be safe on entity-model-update, since I'm still making changes to the database schema in this early state of my project.


Also, is there any good workaround for the fact that the change isn't tracked when you change EntityReference.EntityKey?

Error converting data type varchar to numeric.


I create a form to record data using ASP.NET and SQL database...
when i run the project show error "Error converting data type varchar to numeric"

what's the problem actually.i try to change in datatype in database..
but still showing error...


Regex.replace [h2] to


I try to replace [h2] to <h2>
I use this coding:

string o = "<h2>test</h2>";
o = Regex.Replace(o, "[h2]", "<h2>");
o = Regex.Replace(o, "[/h2]", "</h2>");


But the result after the first regex is:


So that is not what i want.
After the first 1, he should make:


Whats wrong?

Replace text in a word document stored in a Document Library


Hi to all,

im tying to replace some text introduced in a word document stored in a document library.

Exactly i need a web part with a button that change a key in a word template <list> (for example) and replace <list> with a text in another list.


I think that it would works with the Microsoft.Office.Word.Interop; namespace but when i call the function :

wDoc = wordApp.Documents.Open(ref fileName, ref missing,
                                ref readOnly, ref missing, ref missing, ref missing,
                                ref missing, ref missing, ref missing, ref missing,
                                ref missing, ref isVisible, ref missing, ref missing,

The replace of web application stress tool

Hi i want  know in 2010 what is the replace of web application stress tool. what is the new tool for testing application , not Microsoft test manager,  with out a tema foundatin server a application like jmeter   thamks

Change case using Find & Replace dialog box

Hi, I want to change case of link URLs using Find & Replace dialog box . Is it possible? eg changing http://www.abc.com/S pecial-E dition.aspx, http://www.abc.com/O ther-N ews.aspx to http://www.abc.com/s pecial-e dition.aspx, http://www.abc.com/o ther-n ews.aspx

Find and replace link source

I want to replace links href in the source code using regular expression in Find/Replace dialog box from http://www.abc.com/car-news/13PX5S/News1.aspx, http://www.abc.com/car-news/27JXW5/News2.aspx to http://www.abc.com/car-news/news1/13px5s/, http://www.abc.com/car-news/news2/27jxw5/   - in the resultant URL there will be no Capital letters.    

Conversion Failed when Converting the nvarchar value 'N' to data type int.

Hi.. I am facing a strange situation. when i executing the following query it is returning the error SELECT  s.row_id as shipment,    SUM(CAST(la5.attr_value AS INT)) AS Cases     FROM    shipment s WITH(NOLOCK)    INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id    INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id    INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND          s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local     INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = 'CaseQty' AND attr_grp = 3)  WHERE s.spare2 = 'RELEASED'       AND shift.shift_desc = 'c'   AND DAY(ss.production_date) = DAY('2010-08-09 00:00:00.000')    AND MONTH(ss.production_date) = MONTH('2010-08-09 00:00:00.000')    AND YEAR(ss.production_date) = YEAR('2010-08-09 00:00:00.000') GROUP BY s.row_id  Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'N' to data type int. The column  type of attr_value of lot_attr table is a USER D

How to store binary data as nvarchar(max)?

I’m trying to store a blob of binary data as nvarchar(max) and it gets truncated after just a few bytes. I wonder why and how can I prevent it. I don’t use varbinary(max) because I’m trying to keep existing DB schema intact. Current DB contains a huge chunks of text in nvarchar(max) column and I’m trying to compress (zip) this text and store it back to the same field to reduce DB size.   DB is accessed using Sync Framework (and Entity Framework) on the client side, so actual DB engine is SQL Server Compact 3.5 SP2   Thank you! KonstantinThank you! Konstantin

Replace Text in textbox in word document using interop and asp.net

Hi all,  I'm trying to create a function to replace fields in a word document using asp.net. The fields are marked in the document using ###field### and I need to replace them with the data from the DB. The code I have works fine for the body of the word document but doesn't appear to work if the text is contained within a textbox, is there a way to correct this? Here's the code I am currently using Dim Contract as string Dim word As New Word.Application        Dim doc As Word.Document        Try            word.Documents.Open("c:\FYI.doc")            word.Visible = True             doc = word.ActiveDocument            Dim myStoryRange As Microsoft.Office.Interop.Word.Range            For Each myStoryRange In doc.StoryRanges                With myStoryRange.Find                    .Text = "###field###&quo

Patch for VS 2010 Find and Replace Dialog Growing

One of the top reported Microsoft Connect issues with VS 2010 has been an issue with the Find and Replace dialog - which grows 16px each time you use it (which is pretty annoying). The Visual Studio team recently released a patch that fixes this issue. You can download and apply it here. Hope this helps, Scott P.S. A few people reported issues installing the patch if they had an older version of the Silverlight 4 tools installed.  If you see an error message that says you need an update to Visual Studio to support Silverlight 4, you can fix it by installing the latest Silverlight 4 tools release.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend