Views across a linked server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Views across a linked server

Views across a linked server

Rate Topic

Display Mode

Topic Options

Author

Message

dbv147

Posted Thursday, June 04, 2009 12:38 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 9:52 AM
Points: 10, Visits: 99

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.

Thanks,
Dan

Post #729216