Error using the SQL Text data type - need to output via sp
-
----posting this here because one of the errors is a SQLException
I have a web page that displays data from a sql server db. I'm using a stored procedure to return data from a field of type text as an OUTPUT parameter.
How can I use an OUTPUT parameter of datatype text being returned from a stored procedure? I need to use it's value to display comments text on a profile page. The comments will be more than 8000 characters so using varchar is not an option.
Maybe I'm on the wrong track...if so, then the goal is to display text data larger than 8000 characters on a web page alongwith various other data by using stored procedure parameters.
<SQL sp>
CREATE PROCEDURE dbo.getNote (
...other params here
@nteComments text = null OUTPUT,
@NoteId int)
AS
select @nteComments = nteComments -- this is a text column
From Notes
where NoteId = @NoteId
return 1
</SQL sp>
<VB.Net code 1>
strCon = ConfigurationSettings.AppSettings("constring")
con = New SqlClient.SqlConnection(strCon)
cmd = New SqlClient.SqlCommand("getNote", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@nteComments", SqlDbType.Text).Direction = ParameterDirection.Output
... other params here
con.Open()
cmd.ExecuteNonQuery() 'CRASH! ERROR above is returned
con.Close()
</VB.Net code 1>
<error 1>
Parameter 4: '@nteComments' of type: String, the property Size has an invalid size: 0
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: Parameter 4: '@nteComments' of type: String, the property Size has an invalid size: 0
</error 1>
Then I modify the code (after reading various articles on google) to have the size specified. doign that results in code and error sets 2...below...
<VB.Net code 2>
strCon = ConfigurationSettings.AppSettings("constring")
con = New SqlClient.SqlConnection(strCon)
cmd = New SqlClient.SqlCommand("getNote", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@nteComments", SqlDbType.Text, 10000).Direction = ParameterDirection.Output
... other params here
con.Open()
cmd.ExecuteNonQuery() 'CRASH! ERROR above is returned
con.Close()
</VB.Net code 2>
<error 2>
A severe error occurred on the current command. The results, if any, should be discarded.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded."
</error 2>
-
Text data type parameter cannot be an OUTPUT parameter. Check BOL
If you run the query in QA, you will get an error.
-
This is from BOL...says nothing about not being able to use text datatypes as OUTPUT parameters...
"...@parameter
Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed (unless a default for the parameter is defined). A stored procedure can have a maximum of 2,100 parameters.
Specify a parameter name using an at sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the procedure; the same parameter names can be used in other procedures. By default, parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects. For more information, see EXECUTE.
data_type
Is the parameter data type. All data types, including text, ntext and image, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server - supplied data types and their syntax, see Data Types. ..."
-
From BOL on Execute a stored procedure:
OUTPUT
.... Constants cannot be passed to stored procedures using OUTPUT; the return parameter requires a variable name. The variable's data type must be declared and a value assigned before executing the procedure. Return parameters can be of any data type except the text or image data types.
Based on your VB.net code, ado.net/oledb will geenrate a call to your db like this:
exec dbo.getNote @nteComments= @p1 OUTPUT, @NoteId=@Noteid
This gives you the error.
One misleading point is that it allowes you to create a SP with text data type output parameter.
But according to BOL, you can not exec it
-
Wierd that they would do that...but thanks for your help.
-
So then how does one retrieve data from a text field to display on a field on a web page? Don't want to use a dataset or any other temp storage, since that will affect performance.
-
You don't have a choice...
-
the fact that you are retrieveing a TEXT field which potentially can go up to 2GB is way more important (performance wise) that the use of a datareader for its implementation
* Noel -
Makes sense...thanks
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply