I have a problem I really need help with... hope someone can :-)
I have MSDE installed on a live server. The main problem is that I am trying to get a view or linked table to an access db table on the same machine.
I created a view like this:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
CREATE VIEW [dbo].[mybookings]
Select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="d:\databases\uktraining\webdata.mdb";User ID=;Password=;')...bookings
I can open the view fine and see all the records in it, however whenever I try to get the application to use it I get:
Error executing query. Check SQL: 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.
All I need to do is to be able to query the access table through sql. I though about setting the ANSI settings globally but dont know how. I tried to connect to the server via enterprise manager but MSDE is installed with a blank instance name so I'm stuggling on that front as well.