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

    Unanswered: Error on Stored Procedure

    I need to perform a select statement across servers.... this statement works in query analyzer but not as a procedure. It tells me I need to enable the ANSI_NULLS and ANSI_WARNINGS options when put into a procedure (which it looks like i did!) I think it has something to do with this GO as the stored procedure isn't recognizing it. I tried even building it as a string and executing. Any ideas as to enable this in a procedure?

    CREATE PROCEDURE TESTING AS
    SET ANSI_NULLS ON
    GO
    SET ANSI_WARNINGS ON
    GO
    SELECT * FROM MIDSERV.DB000.dbo.tblcustomer
    select * from #table

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Take out GO statements after SET and re-run the SP.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jul 2003
    Posts
    37
    Originally posted by Satya
    Take out GO statements after SET and re-run the SP.
    CREATE PROCEDURE TESTING AS
    SET ANSI_NULLS ON

    SET ANSI_WARNINGS ON

    SELECT * FROM MIDSERV.DB000.dbo.tblcustomer


    I get the same error telling me to enable ansi_nulls and ansi_warnings...

  4. #4
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Aren't the ANSI_NULL and ANSI_WARNING options to has to be set at connection level?

    i belive (thought not so sure) that you cannot use it inside a stored procedure, you just have to call it BEFORE you call your stored procedure.
    Davide Mauri
    http://www.davidemauri.it

  5. #5
    Join Date
    Jul 2003
    Posts
    37
    that is a GOOD CALL!!!!!!!!!!!

    You wouldn't know how about to set those at connection level would you? I tried doing it in a job scheduled in EM and it didn't work too well

    First transact-sql statement in job:
    set ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    GO

    Second transact-sql statement in job
    exec procedure1

    also tried combining
    set ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    GO

  6. #6
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    mmm....do have already tried to remove the "GO" statement?

    to set an option at connection level you just have to call it BEFORE anything else you want to execute with that options activated.

    now i cannot try, but i'll make a test as soon as i get a SQL Server console under my hand :-)
    Davide Mauri
    http://www.davidemauri.it

Posting Permissions

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