Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    37

    Unanswered: Stored Procedure

    I am logged into server1 and need to perform a remote query on server2. This query needs to be located in a stored procedure. I have created the procedure... this is not the issue. The issue is executing the procedure. In order to perform a remote query, I need to set ansi_nulls and ansi_warnings to on. By default, these are always off. Below is the code for the procedure.


    Create procedure procedure1 as
    Select * from Server2.Database2.dbo.RemoteTable

    Procedure was created in Enterprise Manager and will be edited in Enterprise Manager. Query analyzer is not an adequate resource to execute this procedure (and despite that, everything that is executed in QA tends to work in itself and nowhere else).

    I need to enable WITHIN the stored procedure Ansi_nulls and Ansi_warnings to ON... I have attempted many different ways to do this. I have tried all of the following:

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    GO
    Create procedure procedure1 as
    Select * from Server2.Database2.dbo.RemoteTable

    (error msg: create procedure must be the first statement in procedure)

    Create procedure procedure1 as
    Set ansi_nulls on
    set ansi_warnings on
    Select * from Server2.Database2.dbo.RemoteTable

    (error msg: you must enable ansi_nulls and ansi_warnings)

    Create procedure procedure1 as
    Set ansi_nulls on
    set ansi_warnings on
    go
    Select * from Server2.Database2.dbo.RemoteTable

    (error msg: go is to indicate end of procedure)

    There must be a way around this... The reason that these options need to be enabled WITHIN the procedure itself is because it is going to execute automatically; no one will be able to manually set these options and manually execute this procedure... basically it is for automation purposes. I have attempted using T-SQL code in a job to enable these prior to executing the stored procedure which will not work as well. I am assuming the problem as to setting these options in the procedure is because it requires a GO in order to actually enable these on the connection level, however, GO in a stored procedure means the end unlike in just a sql editor.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I am a bit puzzled...

    Your first solution is the correct answer

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    SET NOCOUNT ON
    GO

    Create procedure procedure1 as
    Select * from Server2.Database2.dbo.RemoteTable
    GO

    I just tried this via Enterprise Manager and everything worked. You want these setting set before you create or alter the stored procedure as the environment is recorded when the stored procedure is compiled.

    You can script this out and use I/OSQL.EXE to create or modify the sp.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jul 2003
    Posts
    37
    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    SET NOCOUNT ON
    GO

    Create procedure procedure1 as
    Select * from Server2.Database2.dbo.RemoteTable
    GO

    yep when you click okay it will accept the code... go back into it. where did the connection options go? They all dissappeared for some reason. To prove that point, execute it (don't do it in query anaylzer... you don't need to enable any connection options in query anaylzer to execute the procedure for some reason.) for instance try to schedule a job which will execute the procedure and run it. It will fail with an error... because ansi_nulls and ansi_warnings need to be enabled.

    I'm telling you Enterprise Manager is going to get thrown out the window soon lol!!!! At least I'm beginning to laugh about it.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Yup EM will drop the environment setting when you go back to edit the sp. Most people don't use EM to maintaine sp, triggers or views.

    The reason you don't need to enable connection options in QA is because they were recorded when the sp was compiled. When you schedule the job the setting should already be there. You could create the job with the settings though.

    In the job step just say:

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    SET NOCOUNT ON
    GO
    EXEC procedure1
    GO
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    In Properties of your server (where the procedure is created) go to Connections and set your ANSI settings. I haven't tried it, but it just might work

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •