Microsoft KB Archive/928965

Knowledge Base

You may receive an error message when you try to populate a SQL Server 2000 table with a Data Generation plan in Visual Studio 2005 Team Edition for Database Professionals: "System.Data.SqlClient.SqlError: Disallowed implicit conversion from data type nte

Article ID: 928965

Article Last Modified on 12/13/2006

---

APPLIES TO

• Microsoft Visual Studio 2005 Team Edition for Database Professionals

---

SYMPTOMS

When you try to populate a Microsoft SQL Server 2000 table with a Data Generation plan in Microsoft Visual Studio 2005 Team Edition for Database Professionals, you may receive an error message that resembles the following:

Table TableName : System.Data.SqlClient.SqlError: Disallowed implicit conversion from data type ntext to data type varchar, table 'TableName', column 'ColumnName'. Use the CONVERT function to run this query.

Note This problem occurs when you try to populate the field for a varchar or nvarchar field that contains more than 4000 characters.

CAUSE

This problem occurs because the SqlClient provider converts the varchar or nvarchar data type to a text or ntext data type. However, you cannot convert a text or ntext data type to a varchar or nvarchar data type in SQL Server 2000.

Note In Microsoft SQL Server 2005, the SqlClient provider uses a varchar(max) or nvarchar(max) data type instead of the text or ntext data type. Therefore, this problem does not occur in SQL Server 2005.

WORKAROUND

To work around this problem, make sure that varchar columns in a SQL Server 2000 database do not contain more than 4000 characters.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Steps to reproduce the problem

1. Start Microsoft Visual Studio 2005.
2. On the File menu, point to New, and then click Project.
3. Expand Database Projects, and then click Microsoft SQL Server.
4. Click SQL Server 2000, type Database1 in the Name box, and then click OK.
5. In Solution Explorer, expand Schema Objects, right-click Tables, click Add, and then click Table.
6. In the Name box, type MaxVarChar, and then click Add.

7. Replace the code in the dbo.MaxVarChar.table.sql file with the following code example.

   CREATE TABLE [dbo].[MaxVarChar]
   (
   [VarChar_maxLength] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
   ) ON [PRIMARY]

8. On the Data menu, point to T-SQL Editor, and then click Execute SQL.
9. In the Connect to Database dialog box, click the database connection in the Connection list, and then click OK.
10. In Solution Explorer, right-click Data Generation Plans, point to Add, and then click Data Generation Plan.
11. In the Name box, type DataGenerationPlan1.dgen, and then click Add.
12. Click to select the dbo.MaxVarChar check box.
13. On the Data menu, point to Data Generator, and then click Generate Data.
    
    Note Click Yes if the Do you want to clear the contents of the selected tables before inserting new rows? dialog box appears.
14. In the Connect to Database dialog box, click the database connection in the Connection list, and then click OK.
    
    You receive the error message that is mentioned in the "Symptoms" section.

Keywords: kbtshoot kbpubtypekc kberrmsg kbprb KB928965

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.