Professional Web Applications Themes

Need a workaround: error when saving stored proceduce - Microsoft SQL / MS SQL Server

Hello everyone, I have written a stored procedure which selects from tables in a linked server (using openquery.) The statement runs fine from Query yzer, but when I past it into a stored procedure, and the syntax check is fine, but I get this error when I try to Save it: Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connecton. This ensures consistent query semantics. Enable these options and then reissue your query. I checked ANSI NULLS and ANSI WARNINGS for the server (database properties->connetion tab) but I still get this result. any ...

Sponsored Links
  1. #1

    Default Need a workaround: error when saving stored proceduce

    Hello everyone, I have written a stored procedure which selects from
    tables in a linked server (using openquery.) The statement runs fine
    from Query yzer, but when I past it into a stored procedure, and
    the syntax check is fine, but I get this error when I try to Save it:

    Error 7405: Heterogeneous queries require the ANSI_NULLS and
    ANSI_WARNINGS options to be set for the connecton.
    This ensures consistent query semantics. Enable these options and then
    reissue your query.

    I checked ANSI NULLS and ANSI WARNINGS for the server (database
    properties->connetion tab) but I still get this result. any ideas on
    who to make SQL Server save this query?

    Is there a sp to create an sp? Maybe I could try and create this sp
    from QA since it executes from QA just fine(?)

    here is my query in case it helps:

    SELECT *
    FROM OPENQUERY(CorpDB,
    'SELECT cdb_prod_p_cdb_site.Site_id, Site_name
    FROM cdb_prod_p_cdb_site INNER JOIN cdb_prod_p_cdb_DISTRICT
    ON cdb_prod_p_cdb_site.district_ID =
    cdb_prod_p_cdb_DISTRICT.District_ID
    INNER JOIN cdb_prod_p_cdb_DIVISION
    ON cdb_prod_p_cdb_DIVISION.Division_ID =
    cdb_prod_p_cdb_DISTRICT.DIVISION_ID
    inner join cdb_prod_p_cdb_region
    on cdb_prod_p_cdb_region.region_ID = cdb_prod_p_cdb_DIVISION.Region_ID
    INNER JOIN CDB_PROD_P_CDB_SITE_LOB1
    ON CDB_PROD_P_CDB_SITE.SITE_ID = CDB_PROD_P_CDB_SITE_LOB1.SITE_ID
    WHERE AREA_ID = 5 and lob1_id = 1 and opr_status_CD = ''A''')

    Max Hodges, MCP
    MAXconsulting
    www.maxconsulting.us
    Sponsored Links
    maxhodges Guest

  2. #2

    Default Re: Need a workaround: error when saving stored proceduce

    In the stored procedure itself:

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON

    (BTW, you can create procedures directly in QA, you don't need to use
    Enterprise Manager's kludgy stored procedure editor.)




    "maxhodges" <com> wrote in message
    news:google.com... 


    Aaron Guest

  3. #3

    Default Re: Need a workaround: error when saving stored proceduce

    Aaron Bertrand [MVP] (com) writes: 

    This is not going to help, as the setting for ANSI_NULLS is saved with
    the stored procedure. Thus, the settings must be set on before the
    procedure is created. And SET ANSI_WARNING ON should be set before
    the procedure is invoked. (Setting it in the SP works, but causes a
    recompile.)

    If one for some reason can create the procedure with SET ANSI_NULL ON,
    which I have to admit is the case in our shop, a workaround is do use
    dynamic SQL with double EXEC:

    EXEC("SET ANSI_NULLS ON EXEC('Linked query')")

    (In our shop we do run with QUOTED_IDENTIFIER ON, but we have a load
    tool which is nice to change all " to ' for us.)

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  4. #4

    Default Re: Need a workaround: error when saving stored proceduce

    Sorry, I meant prior to creating the SP.


    Aaron Guest

Similar Threads

  1. Replies: 2
    Last Post: May 18th, 08:56 PM
  2. flash form error, can't use the same name more thanonce? need workaround
    By johnnystacks in forum Coldfusion Flash Integration
    Replies: 2
    Last Post: May 16th, 04:34 PM
  3. PDF error when saving in AI 9 or 10
    By brenda_gates@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 0
    Last Post: February 9th, 05:35 PM
  4. saving to web error
    By William Throop in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 0
    Last Post: July 6th, 04:17 AM
  5. saving error
    By marionbabich in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 2
    Last Post: July 4th, 11:37 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139