SQL Server Performance

Question on Compiling Procedures in SQL Server 2005 Management Studio

Discussion in 'Getting Started' started by lcerni, Sep 25, 2007.

  1. lcerni New Member

    We are not only migrating from SQL Server 2000 to SQL Server 2005 but we are also changing
    our database structure (changing column names, datatypes, table names, etc.).
    Thus our one developer who has about 100 stored procedures to change
    is attempting to find a way to change his procedures without having to go through line by
    line. What he would do is copy the procedure from SQL Server 2000 to SQL Server 2005
    and hit the execute button and let the compile tell him what was wrong; such as table does
    not exist, column doesn't exist, etc. We have tested in SQL Server 2000 and when we rename
    a column to a bad name, it compiles with an error.
    example:
    Server: Msg 207, Level 16, State 3, Procedure usp_procedure_name, Line 40
    Invalid column name 'cust_po'.
    However, in SQL Server 2005 this doesn't always happen. It will compile successfully. Is
    there a bug in SQL Server 2005/Management Studio or is there an option that I need to
    turn on or off? Or is there an issue with a fix or service pack? Or do I need to apply one?
  2. satya Moderator

    The result of specifying the missed column name should be same in SQL 2005 too, what is the service pack level on 2005 when you have tested.
  3. lcerni New Member

    I ran this command: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
    The output is: 9.00.2047.00 SP1 Developer Edition
  4. satya Moderator

    Are you executing this SP on that SQL 2005 instance?
    Do you get any error or simply a message that it is compiled.
    Better to test with SP2 for SQL 2005 (3042) in this case.
  5. lcerni New Member

    Yes.
    The message I get is: Command(s) completed successfully.
    I will download SP2 and test and see if it fixes the issue.
  6. lcerni New Member

    It didn't fix the issue (installing sp2). However, we did install Microsoft Office 2007. Could this have done something to our version of Microsoft SQL Server 2005?
  7. lcerni New Member

    Here is a copy of the old code. The table names and columns in the new database have changed. Yet it still compiles successfully. Could it be the way that it is coded?/****** Object: StoredProcedure [dbo].[usp_SRS_Open_Labor] Script Date: 09/25/2007 12:20:29 ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    /*
    AUTHOR : Boris
    DATE :12/17/04
    MODIFICATION: 08/05/05 - added @ServiceType and @ShowProblem

    For SQL Reporting Services. See: sp_rpt_Open_Labor
    Parameters:
    @ShowProblem and @ShowTech: 1-yes, 0-no
    @ServiceType: select client_service_type_cd from client_service_type: 'ALL' for all (default)
    (show to user: mid(client_service_desc,instr(client_service_desc," - ")+3))
    */
    ALTER PROCEDURE [dbo].[usp_SRS_Open_Labor]
    @client_id int,
    @contract_num VarChar(18),
    @ShowTech tinyint,
    @ServiceType char(3)='ALL',
    @ShowProblem tinyint=0
    AS
    /*
    declare
    @client_id Numeric(7,0),
    @contract_num VarChar(18),
    @ShowTech tinyint,
    @ServiceType char(3),
    @ShowProblem tinyint
    set @client_id=19
    set @contract_num='atts047'
    set @ShowTech=1
    set @ServiceType='ALL' --'ICA'
    set @ShowProblem=1
    */
    select @ServiceType=case when @ServiceType is null then 'ALL' else @ServiceType end
    SET NOCOUNT OFF
    SELECT DISTINCT
    ols.order_number,
    ols.client_id,
    ols.order_status,
    ol.client_contract_num,
    ols.ols_ticket,
    ol.due_date,
    --convert(datetime,convert(char(11),ols.dispatch_date)) dispatch_date, --06/19/06
    ols.dispatch_date,
    ols.cust_po_num,
    o.cust_ref_num,
    o.cust_site_id,
    cst.client_service_desc,
    cst.reportable_ind,
    c.client_name,
    cc.contract_name,
    s.branch,
    Case
    When (Len(o.ship_to_phone_num) = 10) Then
    '(' + Left(o.ship_to_phone_num, 3) + ') ' + Substring(o.ship_to_phone_num, 4, 3)
    + '-' + Substring(o.ship_to_phone_num, 7, 4)
    Else
    o.ship_to_phone_num
    End
    ship_to_phone_num,
    rtrim(o.ship_to_name) site_name,
    o.ship_to_state site_state,
    o.ship_to_city site_city,
    convert(datetime,convert(char(11),(SELECT MAX(ship_date)
    FROM order_product op
    WHERE op.order_number = ols.order_number AND (order_status_cd = 'c'))))
    ship_date,
    case
    WHEN @ShowTech=1 then
    ols.employee_id_num
    ELSE NULL
    end
    employee_id_num,
    case
    WHEN @ShowTech=1 then
    (SELECT left(employee_first_name,1) + '.' + upper(left(employee_last_name,1))+
    LOWER(rtrim(substring (employee_last_name, 2, len(employee_last_name)-1)))
    FROM employee
    WHERE employee_id_num=ols.employee_id_num)
    else ''
    end
    employee_name,
    case
    WHEN @ShowProblem=1 then
    rtrim(isnull(problem_comment_1,''))+rtrim(isnull(' '+problem_comment_2,''))+rtrim(isnull(' '+problem_comment_3,''))
    end
    problem
    FROM orders o inner join
    --order_labor_segment ols on
    order_labor ols on
    ols.order_num = o.order_num
    inner join client_service_type cst on
    ols.client_service_type_cd = cst.client_service_type_cd
    inner join client c on ols.client_id = c.client_id
    inner join client_contract cc on
    ols.client_id = cc.client_id AND
    ols.client_contract_num = cc.client_contract_num
    inner join site s on o.site_id = s.site_id
    left outer join order_labor_seg ol on ols.ols_ticket_number=ol.ols_ticket_number
    WHERE
    (ols.order_labor_type_cd <> 'STA' or
    (ols.order_labor_type_cd = 'STA' and ol.unit_price>0)) --bm: 09/25/06 per John
    AND ols.order_status_cd not in ('c','x')
    AND cst.reportable_ind <> 'n'
    AND ols.client_contract_num =@contract_num
    AND ols.client_id = @client_id
    AND (ols.client_service_type_cd=@ServiceType or @ServiceType='ALL') --08/05/05
    and ol.due_date>'09/01/07'
    SET NOCOUNT ON
  8. lcerni New Member

    I have ran the following test on SQL Server 2000 as well as SQL Server 2005:
    /*** Created blank database LJC_Test ***/
    select * from orders /***
    when I run the above statement I get the following error:
    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'orders'.
    ***/
    /***
    Now I wrap this same statement in a procedure, compile, and I get the statement "The command(s) completed successfully".
    ***/
    create procedure ljc_test
    as
    begin
    select * from orders
    end
    exec ljc_test /***
    When I execute the procedure I get the following error message:
    Msg 208, Level 16, State 1, Procedure ljc_test, Line 4 --Invalid object name 'orders'.
    ***/
    I would have thought that when the procedure was compiled it would have failed. Obviously I am wrong. I thought that as part of the compile process it checked valid table names, columns, etc. Can anyone provide me with some information to understand this compile process better? Thank you in advance!
  9. ndinakar Member

    When you compile the proc it only checks for syntax errors. Any missing objects/columns etc is checked at run time when you actuallye xecute stored proc, at which time the T-SQL statements are executed against the database.
  10. satya Moderator

    That is the reason I have asked you above that whether you are executing this SP on this 2005 instance. You said yes, as referred by Dinakar the compile process is differnt to run time.
  11. lcerni New Member

    Thank you for the information. I guess then I should ask the following. I am not sure if
    I should be continuing with this thread or start a new one.
    We are redoing our database. We have in an excel spreadsheet the mapping from the old to
    the new: old table name, old column names, new table name, new column names. Is there a
    tool out there or code that someone has put together that will create a new procedure from
    the old replacing the old table names and old column names with the new table names and
    new column names?
  12. ndinakar Member

    I dont recollect seeing/hearing about any such tool. Even if there was one I wouldnt rely on it. You can easily identify the procs/tables/view that use a particular table by querying syscomments.
    SELECT distinct object_name(id) From syscomments where text like '%oldtablename%'
  13. lcerni New Member

    Yes, I am aware on how to identify the procs/tables/views that use a particular table
    by querying syscomments. I was just hoping to find a process that takes a procedure
    from an old database with the old table/column names and rewrites it into a new procedure
    with the new table/column names in the new database.
  14. ndinakar Member

    You can write a little tool to get the text from all the procs into a huge text file and do a replace. Run it in a loop for each of the tables. This is however risky and not guaranteed. You might have to run/test/fix is few times before you can arrive at a proper working tool.
  15. satya Moderator

    May be a trip to Software Spotlight article section in this website might help you to identify such a tool.
    If not inclined for third party tools then you have no choice other than using Excel.
  16. lcerni New Member

  17. satya Moderator

    Nice fine, appreciate in sharing the same here.

Share This Page