locked
"CLR type does not exist or you do not have permissions to access it." SqlDbType.UDT RRS feed

  • Question

  • I can't seem to get past this error:

    "Parameter 1 ([Parts].[].[@OrgLevel]): The CLR type does not exist or you do not have permissions to access it."

    Can anyone help me with it?  The error happens on my output parameter: @OrgLevel when it hits the ExecuteNonQuery call.

    @OrgLevel - Is an output parameter on my stored procedure of type hierarchyId:

    Here is part of my code:  

    SqlCommand sqlCommand = new SqlCommand(storedProcedure, _SqlConnection);

    sqlCommand.CommandType = CommandType.StoredProcedure;

     

    SqlParameter paramOrgLevel = new SqlParameter("@OrgLevel", partsListPart.OrgLevel);

    paramOrgLevel.UdtTypeName = "@OrgLevel";

    BaseRepository.SqlParamaters.Add(paramOrgLevel);

     

    sqlCommand.Parameters.Add(paramOrgLevel);

     

    _SqlConnection.Open();

    sqlCommand.ExecuteNonQuery(); <--Fails

    _SqlConnection.Close();

     

    Any ideas?

    Monday, July 26, 2010 10:20 PM

Answers

  • This question really belongs in the MSDN adonetproviders forum here: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/threads , because this forum is about the spatial data types. Because I don't have the ability to move it, I'll try and answer it here.

    the line:

    paramOrgLevel.UdtTypeName = "@OrgLevel";

    should be:

    paramOrgLevel.UdtTypeName = "hierarchyid"; // the name of the data type in SQL Server

    If it's an output parameter, you also need:

    paramOrgLevel.Direction = System.Data.ParameterDirection.Output;

    Hope this helps, Bob Beauchemin, SQLskills

     

    • Proposed as answer by Nemanja Matkovic Tuesday, July 27, 2010 9:54 AM
    • Marked as answer by cwales Tuesday, July 27, 2010 1:40 PM
    Monday, July 26, 2010 10:52 PM

All replies

  • This question really belongs in the MSDN adonetproviders forum here: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/threads , because this forum is about the spatial data types. Because I don't have the ability to move it, I'll try and answer it here.

    the line:

    paramOrgLevel.UdtTypeName = "@OrgLevel";

    should be:

    paramOrgLevel.UdtTypeName = "hierarchyid"; // the name of the data type in SQL Server

    If it's an output parameter, you also need:

    paramOrgLevel.Direction = System.Data.ParameterDirection.Output;

    Hope this helps, Bob Beauchemin, SQLskills

     

    • Proposed as answer by Nemanja Matkovic Tuesday, July 27, 2010 9:54 AM
    • Marked as answer by cwales Tuesday, July 27, 2010 1:40 PM
    Monday, July 26, 2010 10:52 PM
  • I really appreciated your help. Thank you. That did the trick.
    Tuesday, July 27, 2010 1:41 PM