Visual Studio .NET Server Explorer generates parameters of incorrect data types (827993)

SYMPTOMS

Microsoft Visual Studio .NET Server Explorer generates SqlCommand objects that contain SqlParameter fields with incorrect data types when one of the following scenarios is true:

You connect to the Microsoft SQL Server database by using Visual Studio .NET Server Explorer and then you drag a database table that contains columns where the data type is text or ntext to the form designer.
You connect to the SQL Server database by using Visual Studio .NET Server Explorer and then you drag a stored procedure that contains parameters where the data type is text or ntext to the form designer.

Note This problem also occurs for columns or parameters where the data type is image, smallmoney, smalldatetime, or binary.

CAUSE

Visual Studio .NET Server Explorer uses an OLE DB provider to connect to a computer that is running SQL Server. The OLE DB provider does not distinguish between varchar columns and text columns of a data table. Therefore, the size of the parameters that are generated by Server Explorer is correct, but the data type is not correct.

RESOLUTION

To resolve the problem, manually change the data type of the parameters to text or to ntext in the generated code as appropriate for the data type on the computer that is running SQL Server.

Note If you do not manually change the data type of your text and ntext parameters in the generated code, you may receive the error that is mentioned in the following article:

827366 "Invalid Buffer Received from Client" Error Message in SQL Server Log When You Use SQL Server .NET Provider Classes

MORE INFORMATION

Steps to Reproduce the Behavior

Start SQL Query Analyzer (or SQL Server Enterprise Manager), and then create a new stored procedure in the SQL Northwind sample database as follows:
```
USE Northwind
Go
CREATE proc TestProc
@ntextParam ntext,
@textParam text
as
return 1
Go
```
Start Microsoft Visual Studio .NET.
On the File menu, point to New, and then click Project.
Click Visual Basic Projects or Visual C# Projects under Project Types, and then click Windows Application under Templates. By default, Form1 is created.
On the View menu, click Server Explorer.
In Server Explorer, right-click Data Connections, and then click Add Connection.
In the Data Link Properties dialog box, type your local SQL Server name in the Select or enter a server name text box.
Type your local SQL Server user name in the User name box, and then type your password in the Password text box.
In the Select the database on the server box, click Northwind, and then click OK.
In Server Explorer, expand your local SQL Server data connection, and then expand Stored Procedures.
In Server Explorer, drag the TestProc stored procedure from Stored Procedures to Form1.
On the View menu, click Code.
In the code editor, expand Windows Form Designer generated code.

In the SqlCommand1 object, you see that the data type of the ntextParam parameter is NVarCharand the data type of the textParam parameter is VarChar.

REFERENCES

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

316649 How to use the Server Explorer in Visual Studio .NET and Visual Studio 2005

304959 HOW TO: Use Visual Studio .NET Server Explorer to Access Data