Deprecated types are not supported as OUTPUT parameters
During working on one simple stored procedure I came to one interesting scenario. If we are developing a stored procedure in SQL Server 2005 that take some input or output parameters and if those parameters are of deprecated data types like Text, NText and Image then you are going to get an error when you will pass parameters to this stored procedure using .NET codes (using System.Data.SqlClient) . The error message will look like what you can see in below screen shot.
The only way to fix this error is to use new SqlDbType data types (VarChar, NVarChar and VarBinary) while passing parameters to stored procedure through .NET app.
Attached is a small sample to test this out.
Testing Steps
1. Create stored procedure in your database (see attached stored_proc.sql)
2. Open the attached solution ‘ConsoleApplication1.sln
3. Open Program.cs
4. Closely look at codes.
5. Change the data source and InitialCatlog to your server name and database name.
6. For @out_image parameter the SqlDbType is set to ‘Image’
7. Now run the codes and see what error you get.
8. Now change the SqlDbType of @outimage to ‘VarBinary’
9. Execute the codes and see now everything is fine.
Thanks,
Kuldeep Chauhan (MSFT)