How To Use the MSRemote Provider Through Client Script (240838)

SUMMARY

This article describes how to use the MSRemote provider to connect to and retrieve data from a remote database. The MSRemote provider allows the user to access OLE DB providers running on remote computers or in separate processes on the same computer. By using the MSRemote provider, the user can get access to live data by going through a Web server. For more information on Remote Data Service (RDS) and the MSRemote provider, please see the topic "Remote Data Service in MDAC 2.0" in the Microsoft Developer Network (MSDN).

When using the MSRemote provider, the connection string used to open the ADO connection object states that it wants the OLE DB remote provider to be used. ADO treats this tag in a special manner by using the RDS.DataSpace component to send an HTTP post request to the specified Web server. The server where the OLE DB provider is installed is identified by the tag "Remote Server=". This tag is used to specify the OLE DB provider to be used to create the recordset at the specified server. If this tag is not specified, the OLE DB Provider for ODBC (MSDASQL) is used by default. The rest of the connection string contains all the information needed by the provider to create the recordset.

MORE INFORMATION

Here are some sample connection strings that can be used to access a remote database through the MSRemote provider:

SQL Server connection string (OLEDB):

Provider=MS Remote;Remote Provider=SQLOLEDB;Data Source=your_SQL_Server;Remote Server=http://your_web_server;Initial Catalog=your_database;User Id=user_id;Password=password;

MS Access connection string (Jet OLEDB):

Provider=MS Remote;Remote Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\path_to_your_MDB;Remote Server=http://your_web_server

Oracle connection string (OLEDB):

Provider=MS Remote;Remote Provider=MSDAORA;Data Source=your_Oracle_server;Remote Server=http://your_web_server;User Id=user_id;Password=password

The following are a Microsoft Visual Basic Script and JavaScript samples that demonstrate how to use this remote provider. These samples assume that you have installed the Microsoft Data Access Components and that they are configured correctly, specifically the RDS components. For more information on installing and using RDS, please see the "References" section of this article.

To run the scripts, follow these steps:

Open a text editor or create a new HTML page using Microsoft Visual InterDev.
Cut and paste the appropriate sample code (either VBScript or JavaScript) provided below into the new file.
The following samples use the Pubs database that ships with Microsoft SQL Server. You must modify the connection and query strings so that they are appropriate for your database.
Save the file with an .htm extension.
Double-click the file so that it is loaded in Internet Explorer. Click on the Command button to submit the query.

JavaScript Sample

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<INPUT id=button1 name=button1 type=button value="Create Recordset With JavaScript" onClick="go()">
<SCRIPT LANGUAGE=javascript>
<!--
	function go () 
	{	
		//---- CursorTypeEnum Values ----
		var adOpenForwardOnly = 0;
		var adOpenKeyset = 1;
		var adOpenDynamic = 2;
		var adOpenStatic = 3;
		//---- LockTypeEnum Values ----
		var adLockReadOnly = 1;
		var adLockPessimistic = 2;
		var adLockOptimistic = 3;
		var adLockBatchOptimistic = 4;
		//---- CursorLocationEnum Values ----
		var adUseServer = 2;
		var adUseClient = 3;

		//Variable Declarations
		var i;
		var REMOTECONNECTOLEDB = "Provider=MS Remote;Remote Provider=SQLOLEDB;Data Source=your_sql_server;Remote Server=http://localhost;Initial Catalog=pubs;User Id=<username>;Password=<strong password>;";
		var SQL = "SELECT * FROM Authors";		
		
		//Get the records from the database
		var rs = new ActiveXObject("ADOR.Recordset");
		rs.Open(SQL, REMOTECONNECTOLEDB, adOpenStatic, adLockBatchOptimistic);
		window.document.writeln("<BR><B>Query issued:</B> " + SQL + "<BR>");

		window.document.writeln("<BR><B>DATA:</B><BR>");
		rs.MoveFirst();
		while(!rs.EOF)
		{
				for (i = 0; i < (rs.Fields.Count - 1); i++) {
					window.document.write ( rs(i) + " | " );
				}
				window.document.write ( "<BR>" );
				rs.MoveNext();
		}
		window.document.writeln("<BR><B>Done</B>");
	}
//-->
</SCRIPT>
</BODY>
</HTML>

VBScript Sample

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<INPUT id=button2 name=button2 type=button value="Create Recordset With VBScript">
<P>&#xa0;</P>
<SCRIPT LANGUAGE=vbscript>
<!--
	Sub Button2_onClick()
	
		'---- CursorTypeEnum Values ----
		Const adOpenForwardOnly = 0
		Const adOpenKeyset = 1
		Const adOpenDynamic = 2
		Const adOpenStatic = 3
		'---- LockTypeEnum Values ----
		Const adLockReadOnly = 1
		Const adLockPessimistic = 2
		Const adLockOptimistic = 3
		Const adLockBatchOptimistic = 4
		'---- CursorLocationEnum Values ----
		Const adUseServer = 2
		Const adUseClient = 3

		'Variable Declarations
		Dim i
		Const REMOTECONNECTOLEDB = "Provider=MS Remote;Remote Provider=SQLOLEDB;Data Source=your_sql_server;Remote Server=http://localhost;Initial Catalog=pubs;User Id=<username>;Password=<strong password>;"
		Const SQL = "SELECT * FROM AUTHORS"
	
		'Get the records from the database
		Set rs = CreateObject("ADOR.Recordset")
		rs.Open SQL, REMOTECONNECTOLEDB,adOpenStatic , adLockBatchOptimistic
		window.document.writeln("<BR><B>Query issued:</B> " + SQL + "<BR><BR>")

		window.document.writeln("<BR><B>DATA:</B><BR>")
		rs.MoveFirst
		while not rs.EOF
		
				for i = 0 to (rs.Fields.Count - 1)
					window.document.write ( rs(i).value & " | " )
				next 
				window.document.write ( "<BR>" )
				rs.MoveNext
		wend
		window.document.writeln("<BR><B>Done</B>")
	End Sub
</SCRIPT>
</BODY>
</HTML>