Select the product you need help with

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 - View products that this article applies to.

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.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

Steps to reproduce the problem

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

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]

On the Data menu, point to T-SQL Editor, and then click Execute SQL.
In the Connect to Database dialog box, click the database connection in the Connection list, and then click OK.
In Solution Explorer, right-click Data Generation Plans, point to Add, and then click Data Generation Plan.
In the Name box, type DataGenerationPlan1.dgen, and then click Add.
Click to select the dbo.MaxVarChar check box.
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.
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.