In-Memory Downloading from SQL Server (4)

This is Part 4 of an article written in March 2010 with the title "Downloading Data into a SQL Anywhere In-Memory Database from Microsoft SQL Server".

Part 1 is here.

Part 2 is here.

Part 3 is here.

---

Technique 4: Download Via Linked Server

The Linked Server feature was introduced in SQL Server 2000 to allow ad-hoc queries in SQL Server to operate on a variety of external data sources via OLEDB and ODBC. Linked servers offer similar functionality to SQL Anywhere's proxy tables, but with different syntax: instead of referring to proxy tables, direct references are made to actual tables residing elsewhere from within SQL statements running in SQL Server.

As with proxy tables, all you need to use a linked server is ODBC access to the actual data and a few lines of SQL code. Figure 20 shows the code necessary for SQL Server to define a linked server to connect to SQL Anywhere and run an INSERT SELECT to copy all the data from the local table mss_source to the target table sa_target.

Figure 20: Download Via Linked Server
Context: SQL Server

EXEC sp_addlinkedserver
@server = 'mem',
@srvproduct = 'xxx',
@provider = 'MSDASQL',
@datasrc = 'sa_system_dsn' 
GO

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'mem',
@useself = 'false',
@rmtuser = 'dba',
@rmtpassword = 'sql'
GO

INSERT INTO mem..dba.sa_target
SELECT *
FROM mss_source
GO

Lines 1 through 6 in Figure 20 call the system stored procedure that defines the linked server in SQL Server.

Line 2 specifies the logical local server name "mem" to be used in later SQL statements.

Line 3 specifies the "product name" of the linked server. Apparently, at least in this case, this parameter isn't important as long as you specify something other than the default NULL value.

Line 4 identifies the OLE DB provider to be used to communicate with SQL Anywhere. In this case MSDASQL is specified, which is the standard Microsoft OLE DB Provider for ODBC. You can also used the SAOLEDB.11 provider that ships with SQL Anywhere 11, but the MSDASQL provider is capable of handling the simple SQL used in this article.

Line 5 names the ODBC System DSN that the OLE DB provider will use to reach SQL Anywhere; the registry entry for this DSN is shown in Figure 21 below.

Lines 8 through 13 in Figure 20 call the system stored procedure that defines the login path that SQL Server will use behind the scenes to connect to SQL Anywhere.

Line 9 names the linked server that was defined earlier, on line 2.

Line 10 says that the local SQL Server login id will not be used to connect to SQL Anywhere; instead, a user id and password unique to SQL Anywhere is provided on lines 11 and 12.

Lines 15 through 18 show how all the rows are retrieved from the local SQL Server table mss_source and inserted into the SQL Anywhere table sa_target.

Line 15 uses the standard four-part SQL Server table naming convention: [server].[database].[owner].[table]. In this case the server is linked server mem defined above. The database name is omitted because, with SQL Anywhere, there's only one database per connection. The last two parts are the owner and table names: dba.sa_target.

Figure 21 shows the system DSN that is stored in the registry on the server computer. This is a straightforward SQL Anywhere DSN, with default values for everything except UID, PWD, DatabaseName, ServerName and CommLinks.

Figure 21: ODBC System DSN for Linked Server
Context: SQL Anywhere

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\sa_system_dsn]
"Driver"="C:\\Program Files\\SQL Anywhere 11\\bin64\\dbodbc11.dll"
"UID"="dba"
"PWD"="sql"
"DatabaseName"="mem"
"ServerName"="mem"
"AutoStop"="YES"
"Integrated"="NO"
"CommLinks"="TCPIP{host=PAVILION2}"
"Compress"="NO"

-- [to be continued] --