Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    3

    Unhappy Unanswered: Linked servers and problem with ANSI_NULLS and ANSI_WARNINGS

    Hello everybody,

    I've configured linked server from MS SQL 7.0 to Oracle 9.2. Next I've created trigger on table in MS SQL Database. Application which I'm using inserts data to this table and should to run this trigger. But after insertion of data I can see following messages in my logs:

    SQL Server User Error: 7405, State 1, Severity: 16, Message: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

    ....and trigger doesn't do nothing ...

    I red that I should use special settings for connection via linked servers.

    SET ANSI_NULLS on
    SET ANSI_WARNINGS on

    ...but I have found this in examples about procedures not about triggers. So my question is : is it possible to solve this problem if I use triggers ? And if it's possible, how can I do this ?

    Thanks for your help.

    Pawel.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You should use like following:


    set ANSI_Nulls on
    go
    create /*or alter */Procedure uspRefreshTable
    @TableName sysname
    as
    set ANSI_Warnings on
    declare @SQL nvarchar(2000)
    ...

    Refer to books online for more information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    May 2003
    Posts
    3
    Originally posted by Satya
    You should use like following:


    set ANSI_Nulls on
    go
    create /*or alter */Procedure uspRefreshTable
    @TableName sysname
    as
    set ANSI_Warnings on
    declare @SQL nvarchar(2000)
    ...

    Refer to books online for more information.
    Hi,

    Yes, it's true but for procedures not for triggers. I've found this in example. I am looking for some example about triggers.

    Cheers,

    Pawel

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Just insert the code before :
    CREATE TRIGGER....
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    May 2003
    Posts
    3
    Originally posted by Satya
    Just insert the code before :
    CREATE TRIGGER....

    It doesn't work. I tried this before I send email to dbforums :-(

    Has anyone idea how to do it ?

    Pawel

Posting Permissions

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