1. 

   davidsona, Blocked

   I have a report SP that is returning this error whenever I try to run it through the myDBR web app:

   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.

   However, the SP runs fine when I execute it in Microsoft SQL Server Management Studio. I have tried recreating the SP using these ANSI_NULLS and ANSI_WARNINGS options set to on, and still the same results. Since this error is specific to myDBR execution, is there a global setting etc that needs to be adjusted?

   -Austin
2. 

   myDBR Team, Key Master

   You are using linked servers? These are connection specific options and your myDBR db connection has different options set than the Management Studio.

   You could try couple of things:

   1) Add following options into the beginning of the report:
   set ansi_nulls on
   set ansi_warnings on

   2) Turn on the options before you create the procedure:
   set ansi_nulls on
   set ansi_warnings on
   go
   create procedure....

   --
   myDBR Team
3. 

   davidsona, Blocked

   Thanks. That fixed the previous error, but now i get this error:

   Could not execute the report. There was an error in the report.
   Changed database context to 'DB_MYDBR'.

   Oddly, though, the report ran ok (populated the target table correctly). Also, as with the previous error, I don't get this error in SSMS. ?

   -Austin
4. 

   brad@foreverybody, Blocked

   They run fine in management studio because you are using a login that has appropriate permissions. connect in management studio using the mydbr login and ill bet you get an error. Grant the mydbr login permission to the linked server.

   I have SQL server 2005 hosting myDBR and have reports that not only query other servers but also join tables from other servers of different types. I have mysql, MSSQL, oracle, and access data sources. This is possible.
5. 

   davidsona, Blocked

   Turns out that last error was simply b/c the report had no output (it is used as an interface to generate data in a table, and only did a select into). I added a "select 'Success!'" to the end and now it's fine. So looks like the set ansi_nulls and ansi_warnings on was all I really needed. Thanks again!

Reply

You must log in to post.