locked
TSD4001: Heterogeneous queries require the ANSI_NULLS... RRS feed

  • Question

  • I have a sproc that calls a linked server (Oracle) in the following way:

    SELECT *, getdate(), user_name() FROM OPENQUERY(OracleLinkedServer,...)

     

    I get the following error:

    TSD4001: 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. (SQL error = 7405)

     

    Following the advice in a KB article, http://support.microsoft.com/kb/296769, I add the SET ANSI_NULLS ON statement to the beginning of the sproc, but then I get this error:

    TSD7025: Unable to identify the schema type contained in the .sql file.  A schema type of 'Procedure' was expected, based on the name of the .sql file. 

     

    My project settings are ANSI_NULLS OFF so I tried setting the ANSI_NULLS to ON in the properties window that is available when you click on the file name in solution explorer.  No luck.

     

    Any ideas on how to fix?

    Tuesday, May 22, 2007 12:52 AM

Answers

  • Thanks for the update. I will get back to you if we can find a way to work around the variable resolution, my gut says there is a way around this.

    -GertD

    Tuesday, August 7, 2007 10:01 PM

All replies

  • You can set the ANSI_NULLS setting via the Properties window.

     

    Select the file of the stored procedure in Solution Explorer

    Hit F4 to open the Properties window

    And in there will be a setting to set Ansi Nulls to On. 

     

    Hope this helps,

    -Tom

     

    Tuesday, May 22, 2007 4:27 PM
  • I did set the property for ANSI Nulls to On prior to the previous post and it didn't work.  I double checked, it still doesn't work.
    Wednesday, May 23, 2007 5:41 AM
  • Can you send me the dbproj file? Please zip and send to gertd at microsoft dot com

    Based on that I can see if the property gets set or not.

    -GertD

    Thursday, May 24, 2007 4:25 AM
  • I uploaded the files for a related support ticket with Premier Support: SRZ070516000045

    You're team has all of my project files at this point.

    Wednesday, May 30, 2007 11:01 PM
  • According to the support log this issue has been closed, has your iusse been resolved?

    -GertD

    http://blogs.msdn.com/gertd

     

    Friday, July 27, 2007 9:29 PM
  • Hi, do you know what the solution was? I am facing a similar problem

    Friday, August 3, 2007 12:38 AM
  • Did you install Service Release 1? If not please do so. If that does not solve your problem, send me the file inside that project that contains the object which  causes the problem and a copy of the error message from the errorlist.
    -GertD
    http://blogs.msdn.com/gertd

    Friday, August 3, 2007 7:18 PM
  • The support issue for me was closed, but the answer is more of a limited workaround rather than a good solution.

     

    2 options were given:

     

    1. relocate the sproc that uses OPENQUERY([$(Oracle_EDI], ...) into a post-deployment script.  This works, but will show up as a difference when using schema compare against the project and a database that the project has been deployed to.

     

    2. Create a SQL Server replica of the linked Oracle database and create a reference to the SQL Server replica so the query will resolve properly during database validation. 

     

    I chose to place it in the post-deployment script and remember to ignore this object in schema compare against my project.

     

    Related to this, the $(Oracle_EDI) variable is defined in the variable page so when using the build, the ouput doesn't contain the setvar statement for the variable.  (I thought that is what the variable page is for, but it is only evaluated during deployment)  You have to remember to add it to your script or hard code it in your post-deploy (please helpl if there is a better way). 

    Saturday, August 4, 2007 6:12 AM
  • I just build a project using OpenQuery inside a Stored Procedure to an Oracle server and I can change the ANSI_NULLS setting in the property window, causing the error to appear and disappear as expected, the settings also persisted across settings.

     

    I asked the support folks to provide me your project files you uploaded.

     

    Thanks,

    -GertD

    Sunday, August 5, 2007 9:23 PM
  • I reviewed my support ticket case notes and I confused 2 issues here.  I was able to get the ANSI_NULLS warning to go away, but the [$(Oracle_EDI)] variable that I defined in the variables tab does not get resolved, resulting in an error of:

    TDS4001: Could not find server '$(Oracle_EDI)' in sysservers.

     

    I cannot use a SELECT statement to query the linked server because of performance reasons.  The above post with the support recommendations is what I was given to resolve this issue.

     

    I assumed that the Variables in the project properties would be resolved during validation.  The support engineer's explanation that these variables are resolved only during a delpoy from the IDE seemed strange to me.  Is this the way it is designed? How can I get this variable to be resolved properly?

     

    Tuesday, August 7, 2007 5:48 AM
  • Thanks for the update. I will get back to you if we can find a way to work around the variable resolution, my gut says there is a way around this.

    -GertD

    Tuesday, August 7, 2007 10:01 PM
  •  It worked me setting

    ANSI NULLS DEFAULT: TRUE, 

    ANSI NULLS ENABLED: TRUE,

    ANSI WARNINGS ENABLED: TRUE

    in the database properties >> options.


    Francisco Niño, Ingeniero de Sistemas, SQL server DBA

    Tuesday, June 5, 2012 8:08 PM