Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Views across a linked server Expand / Collapse
Posted Thursday, June 4, 2009 12:38 PM


Group: General Forum Members
Last Login: Monday, April 11, 2016 2:49 PM
Points: 10, Visits: 116
Hi All,

I was wondering if i could get some help with an error. I'm the DBA for a call center company & we've recently been trying to tie financial data to phone switch data. Instead of loading the financial data into our Data warehouse we're trying to create views of the financial data across a linked server. Then our reporting solution (Hyperion) hits our data warehouse via ODBC. We decided to do it this way to make it easier on our report witters. The problem is though that the 1st time a user opens one of the reports hitting the views they get the error below. Then if they stay connected & rerun the report it runs fine. But the next time they connect & run the report it errors on the 1st run every time.

[[Microsoft][ODBC SQL Server Driver][SQL Server]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.]

I've tried enabling ANSI NULLS, ANSI Warnings, & Quoted Identifiers with no luck. We've tried un-checking the options on the ODBC & also tried various versions of the ODBC drivers including the SQL native(2005) driver. Both the SQL server on the link are SQL 2005. They do have different collations though. The problem only occurs on reports that hit the views going across the linked server. The rest of the reports that hit the tables in the DB via ODBC are fine. Any help would be appreciated.


Post #729216
Posted Thursday, June 4, 2009 1:30 PM



Group: General Forum Members
Last Login: 2 days ago @ 4:50 PM
Points: 23,515, Visits: 37,731
Please don't cross post.

Please post replies here.

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #729244
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse