Post reply

Force to create stored procedure

  • October 26, 2009 at 1:32 am

    #73002

    Hi all,

    I would like to create a SQL Server stored procedure which will be linked to some objects (eg:tables) of a linked servers. However, I will get error message when the execution of the sp creation since those objects will only be available when a certain scheduled job successfully completed on the linked server.

    Hence, how can I force the creation of stored procedure even though the objects are not available?

    Thanks and Regards,

    Golden

  • October 26, 2009 at 10:14 am

    #1070806

    You can create a stored procedure that references none existing objects. The procedure will be created, but you’ll get a runtime error if you won’t create those objects before you run the procedure. This is called deferred name resolution (if you want you can Google this term).

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • October 26, 2009 at 10:25 am

    #1070810

    to expound on Adi's point, the defered name resolution is for objectnames..table/proc function/etc,.

    if a table exists, but a COLUMN you reference does not exist, then it won't compile...

    so if your proc references a table that exists but any columns that do not exist yet, because something else was going to add those columns, that would prevent a proc from compiling.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 27, 2009 at 2:49 am

    #1071066

    Hi all,

    Thanks for the explanation above.

    Really appreciate it.

    Regards,

    Golden

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply