locked
Trying to Run a StoreProc; No Success Here RRS feed

  • Question


  • I’m struggling with getting this StoreProc to run.  I’ve filled with some others; all similar to this.  Non work.  I think they should work, because I copied them out of a book (trying to learn more about Stored Procedures).  So, I don’t think the SQL is wrong.  I think I’m using it wrong.

     

    Here’s the whole thing:

    Drop Procedure ap_LeaseContract_Load

    Go

    Create Procedure dbo. ap_LeaseContract_Load

    -- insert lease contract information and return id of lease

              @chvLeaseVendor varchar ( 50),

              @chvLeaseNumber varchar ( 50),

              @chvLeaseDate varchar ( 50),

              @intLeaseId int OUTPUT

    As

    Declare @intError int

     

    -- test validity of date

    if IsDate ( @chvLeaseDate) = 0

    begin

         Raiserror ( 'Unable to Convert to date.' , 16, 1)

         return - 1

    end

     

    insert into dbo. Lease( LeaseVendor, LeaseNumber, ContractDate)

    values ( @chvLeaseVendor, @chvLeaseNumber,

            Convert ( smalldatetime , @chvLeaseDate))

     

    select @intLeaseId = Scope_Identity ()

    return

     

    If I select the SQL below and hit F5:

    Drop Procedure ap_LeaseContract_Load

    Go

     

    I get this mssg:

    Msg 6263, Level 16, State 1, Line 1

    Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

    The statement has been terminated.

     

    For some reason, Drop is not dropping.

     

    When I try to run the whole thing, I get this error mssg:

    Msg 6263, Level 16, State 1, Line 1

    Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

    The statement has been terminated.

    Msg 2714, Level 16, State 3, Procedure ap_LeaseContract_Load, Line 23

    There is already an object named 'ap_LeaseContract_Load' in the database.

     

    In in SQL Server 2008 Express.  What am I doing wrong?

     

    Thanks,

    Ryan---

    Wednesday, May 5, 2010 7:36 PM

Answers

  • This is interesting. Is the current ap_LeaseContract_Load stored procedure implemented in .NET? That might explain why SQL Server requires .NET Framework Integration (aka SQLCLR) to drop this procedure.

    You can check that by looking at the properties of this stored proc in Object Explorer in SSMS. If htat's the case maybe you enable SQLCLR temporarily just to drop the procedure and then disable SQLCLR back.

     


    Krzysztof Kozielczyk, Program Manager for SQL Server Express

    If this post answers your question, please mark it as an Answer - it will help others searching the forum. This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, May 5, 2010 10:07 PM

All replies

  • This is interesting. Is the current ap_LeaseContract_Load stored procedure implemented in .NET? That might explain why SQL Server requires .NET Framework Integration (aka SQLCLR) to drop this procedure.

    You can check that by looking at the properties of this stored proc in Object Explorer in SSMS. If htat's the case maybe you enable SQLCLR temporarily just to drop the procedure and then disable SQLCLR back.

     


    Krzysztof Kozielczyk, Program Manager for SQL Server Express

    If this post answers your question, please mark it as an Answer - it will help others searching the forum. This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, May 5, 2010 10:07 PM
  • Brilliant!  Thanks!!
    Sunday, May 9, 2010 7:25 PM