SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Views across a linked server


Views across a linked server

Author
Message
dbv147
dbv147
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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.

Thanks,
Dan
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)

Group: General Forum Members
Points: 167101 Visits: 39480
Please don't cross post.

Please post replies here.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search