HOWTO: How to Create a Remote Connection or View Programmatically (137944)

MORE INFORMATION

The following code creates a database in which to store the remote view and remote connection. The remote connection is created first, and then the remote view is created by using the remote connection.

The data source used in this example is Sqltest, which is the name of the corresponding SQL Server ODBC driver that exists in the ODBC driver manager. The Pubs database comes with Microsoft SQL Server and contains a table called Sales.

   CLOSE ALL
   SET DEFAULT TO c:\temp
   * A view cannot exist without a database.
   * Create a database to contain view.
   CREATE DATABASE zdb

   * Create a connection for the view to use.
   CREATE CONNECTION zconn DATASOURCE sqltest USERID sa PASSWORD ""

   * Create the view using the remote connection.
   CREATE SQL VIEW zview REMOTE CONNECTION zconn AS select * from ;
      pubs..sales

   * Open the view and browse the resulting data set.
   USE zview
   BROWSE
				

By default, a view is not updatable. For information about creating an updatable view, please see the following article in the Microsoft Knowledge Base: