data types cannot be compared or sorted¶
select name.default_text as britTripLocationName, from brit_trip_item_location with(nolock) inner join ren_text_key [name] with(nolock) on brit_trip_item_location.name_text_key_id = name.id where brit_trip_item_id=7 order by britTripLocationName
I get this error message:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
To solve the problem:
order by convert(varchar(50), name.default_text)
EXECUTE permission was denied¶
I created a database user in the normal manner, Database, Security, Users, but keep getting this error:
The EXECUTE permission was denied on the object 'check_submission_type', database 'ugc', schema 'dbo'.
To try and solve the problem:
GRANT EXECUTE ON check_submission_type TO ugc_vote_verification_user
Note: This will (obviously) only sort the problem out for a single stored procedure.
To send Database mail, users must be a user in the
msdb database and a
member of the
DatabaseMailUserRole database role in the
When you create the user, make sure their User Mapping includes the
msdbusers or groups to this role use SQL Server Management Studio (the
DatabaseMailUserRolewill probably be in the list when you select
msdb) or execute the following statement for the user or role that needs to send Database Mail:
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = '<user or role name>';
“Must declare the scalar variable” in table-valued function¶
My stored procedure was using a table variable… and threw this error. To solve the problem, just use an alias for the table name:
SELECT loyalty_location.* FROM loyalty_location WITH(NOLOCK) INNER JOIN @TempList tempList ON tempList.location_id = loyalty_location.id ORDER BY tempList.ordering