problem querying a TEXT field

I have a query that works, until I try to also grab one of the fields that
is set to a 'text' datatype. I can grab any field, and it works fine, but
once I try to grab the data from the 'text' field, I get the following
error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image
data types cannot be compared or sorted, except when using IS NULL or LIKE
operator.
If I add an ISNULL to this field, I still get the same error. I'm not
explicitely sorting by thie field either. I haven't found a specific
solution via google other than 'change your TEXT field to VARCHAR(7000)'
which doesn't seem like a proper solution.
-DarrelImpossible to say without seeing the query.
ML|||Are you using a DISTINCT or GROUP BY or something else that might require a
sort? Can you post the query?
HTH
Jerry
"darrel" <notreal@.hotmail.com> wrote in message
news:O8PYwpEyFHA.700@.TK2MSFTNGP11.phx.gbl...
>I have a query that works, until I try to also grab one of the fields that
> is set to a 'text' datatype. I can grab any field, and it works fine, but
> once I try to grab the data from the 'text' field, I get the following
> error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image
> data types cannot be compared or sorted, except when using IS NULL or LIKE
> operator.
> If I add an ISNULL to this field, I still get the same error. I'm not
> explicitely sorting by thie field either. I haven't found a specific
> solution via google other than 'change your TEXT field to VARCHAR(7000)'
> which doesn't seem like a proper solution.
> -Darrel
>
>sql

problem on grant permission to user

I have two kinds of functions in the database that need to grant exec permission to user appUser.
The first kind of function return ordinary datatype, let's call it funcReturnDataType here.
The second kind of function return table datatype, let's call it funcReturnTable
When I issued the folloing command to appUser, no problem.
grant exec on funcReturnDataType to appUser

However when I issued:
grant exec on funcReturnTable to appUser

I got the following error message:
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.

Any suggestions to resolve this problem?

Thank you!I'm having the same problem. Can anyone help.|||

Maybe this will help you:

http://msdn2.microsoft.com/en-us/library/ms188371.aspx

from the article:

....

permission
Specifies a permission that can be granted on a schema-contained object. For a list of the permissions, see the Remarks section later in this topic.

ALL
Granting ALL does not grant all possible permissions. Granting ALL is equivalent to granting all ANSI-92 permissions applicable to the specified object. The meaning of ALL varies as follows:

Scalar function permissions: EXECUTE, REFERENCES.
Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
Stored procedure permissions: EXECUTE, SYNONYM, DELETE, INSERT, SELECT, UPDATE.
Table permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
View permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

....

problem on grant permission to user

I have two kinds of functions in the database that need to grant exec permission to user appUser.
The first kind of function return ordinary datatype, let's call it funcReturnDataType here.
The second kind of function return table datatype, let's call it funcReturnTable
When I issued the folloing command to appUser, no problem.
grant exec on funcReturnDataType to appUser

However when I issued:
grant exec on funcReturnTable to appUser

I got the following error message:
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.

Any suggestions to resolve this problem?

Thank you!I'm having the same problem. Can anyone help.|||

Maybe this will help you:

http://msdn2.microsoft.com/en-us/library/ms188371.aspx

from the article:

....

permission
Specifies a permission that can be granted on a schema-contained object. For a list of the permissions, see the Remarks section later in this topic.

ALL
Granting ALL does not grant all possible permissions. Granting ALL is equivalent to granting all ANSI-92 permissions applicable to the specified object. The meaning of ALL varies as follows:

Scalar function permissions: EXECUTE, REFERENCES.
Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
Stored procedure permissions: EXECUTE, SYNONYM, DELETE, INSERT, SELECT, UPDATE.
Table permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
View permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

....

Problem inserting sql query into database float datatype field using SQL Transaction

I have this problem of inserting my query into database field. My code is as of below.

The @.AVERAGESCORE parameter is derived from

Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)

and the value returned is (averagescore.toString("0.00"))

However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem?

Try Dim iAs Integer For i = 0To arraySql.Count - 1 myCommand =New SqlCommandDim consolidatedobjitemAs ConsolidatedObjItem = arraySql(i) myCommand.CommandText = sqlStr myCommand.Connection = myConnection myCommand.Transaction = myTransWith myCommand.Parameters .Add(New SqlParameter("@.AVERAGESCORE", consolidatedobjitem.getaveragescore))End With myCommand.ExecuteNonQuery()Next myTrans.Commit() myConnection.Close()Catch exAs Exception Console.Write(ex.Message) myTrans.Rollback() myConnection.Close()End Try

In your code I did not where you creat the transaction. I use the code below, it works fine. I guess you shoul put the code in to a try catch block. if you want to use it

Dim averageAs Decimal = 1.23Dim mycommandAs SqlCommand =New SqlCommand()Dim myconnAs SqlConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString) mycommand.CommandText ="Insert into Table1 values(1, @.Average)" myconn.Open() mycommand.Connection = myconnDim transAs SqlTransaction = myconn.BeginTransaction("new transaction") mycommand.Transaction = trans mycommand.Parameters.Add(New SqlParameter("@.Average", average)) mycommand.ExecuteNonQuery() mycommand.Transaction.Commit() mycommand.Connection.Close()

Hope this help|||

Thanks for replying. My problem lies with the equation I got my averagescore from.

Dim averagescoreAs Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)average = average.toString("0.00)average =Decimal.Round(average, 2)average = Convert.toSingle(average)average = Math.round(average, 2)

I've in vain to convert the average score to a value which has 2 decimal places using various methods.

The value that I've gotten is 1.68. But I believe the value that is being inserted into the database is not that as I have

various errors. This is because I have tried hardcoding the inserted value like you did and it works.

Is there any other way to resolve this problem?

|||

The error that I've got most of the times are,

System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 18 ("@.AVERAGESCORE"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Hope this gives you a clearer idea, my guess is probably a rounding off error, but I just do not have any ideas left. I have even tried changing the database field datatype to decimal(10,2) to no effect.

|||

You could use SQL Server aggregate function Average with Decimal so you can set precision and scale. Try the link below for T-SQL Average aggregate function. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms173454.aspx

|||I afraid the AVG aggregate function woudn't help much in my case. Thanks anyway.|||

Can you print the type of averagescore. It might be become a decimal number after those manipulations, I do not think decimal can fit into float in sql server.

Hope this help

alienated:

The error that I've got most of the times are,

System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 18 ("@.AVERAGESCORE"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Hope this gives you a clearer idea, my guess is probably a rounding off error, but I just do not have any ideas left. I have even tried changing the database field datatype to decimal(10,2) to no effect.

|||Your error message is saying you are passing the wrong data type to the data protocol Tabular Data Stream. You have to convert Float to Decimal before passing it to SQL Server because there is only implicit conversion between Decimal and Numeric and Float and Real, any data passing between both pairs must be explict conversion. The reason is you can set precision and scale with the former and not the later. Hope this helps.