November 28, 2007 at 2:36 pm
Hi there everyone,
I have created a linked server that has the following view
Server Name: SERVER_DB1
Linked Server Name: SERVER_DB2\DB2
View: db2.dbo.v_job_headers
I run the following SELECT from Query Analyser (running from SERVER_DB1 and get resultset back)
SELECT *
FROM [SERVER_DB2\DB2].db2.dbo2.job_headers
I have created a view on SERVER_DB1, that has the above select statement and this run fine from Query Analyser.
When I open the view from EM or an Application i get a warning about having to SET ANSI_NULLS and ANSI_WARNINGS ON.
Does anyone know how to turn these options on in a VIEW? I created a Function, that set the ansi options to ON, the returned a table of data back to the VIEW. But this still gave me the same ANSI error messages
---------------------------
Database Error
---------------------------
Error selecting from v_job_headers (1).
Contact your system administrator.
Contact your system administrator.
Error 7405: 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.
---------------------------
OK
---------------------------
cheers
November 29, 2007 at 6:34 am
These are settings that can be found in BOL under the topic 'SET options that affect results'.
It's a pretty sticky subject, not that easy to wrap one's head around.
Here's a list of places where you can set SET settings.
The BOL article has a lot more, so it's preferred to read it entirely in order to figure out which part(s) that may apply to your situation.
-- quote BOL --
Precedence for Setting Options
The settings for the SET options can be specified at several levels. The final setting for each session option for a particular connection is determined by the highest precedence operation that sets the option. The precedence of the sessionsetting operations is (with the highest precedent at the top of the list):
Any application can explicitly override any default settings by executing a SET statement after it has connected to a server. The SET statement overrides all previous settings and can be used to turn options on and off dynamically as the application executes.
OLE DB and ODBC applications can specify the option settings that are in effect at connection time by specifying option settings in connection strings.
You can SET options to ON or OFF for any SQL Server ODBC data source by using the ODBC application in Control Panel, or the ODBC SQLConfigDataSource function. Any connection made by an ODBC application using that data source uses the specified defaults, unless the application overrides the defaults in the connect string or with SET statements after connecting.
The OLE DB Provider for SQL Server and the SQL Server ODBC driver automatically set the seven session options to the settings required for indexed views. DB-Library and Embedded SQL for C applications do not, so systems using these APIs must either code the applications to issue the proper SET statements or change the database or server defaults to the correct settings.
You can establish default settings for a database using ALTER DATABASE or SQL Server Enterprise Manager.
You can establish default settings for a server by using either sp_configure or SQL Server Enterprise Manager to set the server configuration option named user options. For more information, see user options Option.
The connection option settings required for indexed views and indexes on computed columns must be active:
For any connection that creates an index on a view or computed column.
For any INSERT, UPDATE, or DELETE statements that attempt to modify data covered by an index on a view or computed column.
Before the optimizer can consider using an index on a view or computed column to cover a query.
For indexed views, the ANSI_NULLS and QUOTED_IDENTIFIER options must be set to ON when the view is created, because these two settings are stored as object properties with the view definition.
-- end quote --
/Kenneth
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply