To use FILESTREAM, it has to be configured at the OS and instance level. At the OS level, FILESTREAM is enabled either during the installation of SQL Server 2008 or by running SQL Server Configuration Manager. Once FILESTREAM has been setup, you have to create a FILESTREAM filegroup. You then have to create a data container in the FILESTREAM filegroup. Once all these steps are done, you can create a table in your DB to use FILESTREAM.
(From your link) Here are the steps to configure FILESTREAM at the OS level, if not done during installation:
- On the Start menu, point to All Programs, point to Microsoft SQL
Server 2008, point to Configuration Tools, and then click SQL Server
Configuration Manager.
- In the list of services, right-click SQL Server Services, and then
click Open.
- In the SQL Server Configuration Manager snap-in, locate the instance
of SQL Server on which you want to enable FILESTREAM.
- Right-click the instance and then click Properties.
- In the SQL Server Properties dialog box, click the FILESTREAM tab.
- Select the Enable FILESTREAM for Transact-SQLAccess check box.
- If you want to read and write FILESTREAM data from Windows, click
Enable FILESTREAM for file I/O streaming access.
- Enter the name of the Windows share in the Windows Share Name box.
- If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data. Click Apply.
Next configure the SQL instance for FILESTREAM access. Run the following query on the instance (SSMS):
EXEC sp_configure filestream_access_level, 2; -- Options are 0/1/2
GO
RECONFIGURE;
GO
Now add the FILESTREAM filegroup to your DB:
ALTER DATABASE Production ADD
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM;
GO
ALTER DATABASE Production ADD FILE (
NAME = FSGroup1File,
FILENAME = 'F:\Production\FSDATA') -- Location for your data container
TO FILEGROUP FileStreamGroup1;
GO
You can now use FILESTREAM in your tables.
If I try to right click on a view name and select Script -> SELECT To.... I get a message
The GUI is probably trying to access an Oracle Data Dictionary View (in order to read the column names & data types) that it does not have permission to select from, hence the error message.
Ask the Vendors Oracle DBA to trace the ODBC session so that you can track down the exact cause.
Best Answer
The fundamental issue is: Which server processes the query?
When using a linked server query, the query is executed on the LOCAL server and uses the linked server to access data on the remote server. (Linked servers are subject to several limitations, some caused by running through the Distributed Transaction Coordinator (DTC).)
When using OPENQUERY, the query is sent to the REMOTE server and is executed there in order to return data to the local server. (You will notice that in OPENQUERY the query uses a three-part name, since it all runs on the remote server, rather than the four-part name used for the linked server.)
A simple exposure to the limitations can be found in this thread: http://www.sqlservercentral.com/Forums/Topic948316-338-1.aspx
It mentions network topology, data types, type of remote server, the code in the query, etc.
EDIT: See more on the Guidelines for Using Distributed Queries at: http://technet.microsoft.com/en-us/library/ms175129(v=sql.105).aspx
The subheading Other Guidelines mentions: "To create the best query plans ..., the query processor must have data distribution statistics from the linked server. ... If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server."
If your connection to the remote server does not have access to those statistics that might cause a poor plan. Ideally not an inaccurate plan, but the query processor is imperfect.