Accessing U/SQL data from SQL Server Linked Server
October 26, 2010 at 9:45 pm | Posted in Transoft Products, USQL | Comments OffFor demonstration purposes we have installed U/SQL 4.31 onto a 64 bit Windows 2008 Server instance on Amazon EC2 and set up a SQL Server Linked Server to access the U/SQL demo data. Here are the steps:
First, install the U/SQL Server and the U/SQL Client and get them both licensed. Note that you’ll need a gateway client license in order to install the U/SQL client on Windows 2008 Server.
Install, license and start the U/SQL Server
The next step is to check that you can access the demo data from Win U/SQLi :
Select books32.udd, a 32 bit ODBC DSN
Test data access
Now, to access the data from 64 bit SQL Server you need to create a 64 bit ODBC DSN.
Start the Windows ODBC Administrator from Administrative Tools – this is the 64 bit Administrator so you won’t see the 32 bit DSN for books32.udd listed.
Create a new DSN and select the 64 bit Transoft ODBC driver
Create a new DSN named books64.udd
Now add books64.udd as a new data source in the U/SQL Manager:
Add books64.udd in U/SQL Manager
The next step is to start up SQL Server Managment Studio and create a new Linked Server:
Create a new Linked Server named BOOKS64
Select the Microsoft OLE DB Provider for ODBC Drivers and specify the 64 bit DSN
Now you can run a SELECT query to read the U/SQL demo data:
Example using 'select * from openquery' syntax
Example using fully qualified (four part) table names
However, if you attempt to INSERT data, SQL Server returns an error:
INSERT gives error 'The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface.'
The solution is to check ‘Non transacted updates’ in the MSDASQL provider options as follows:
Check 'Non transacted updates'
Now, create a new Linked Server, BOOKS64_1, and try the INSERT again:
INSERT into BOOKS64_1 works correctly
NOTE: INSERT into BOOKS64 still fails because the provider options are set when the Linked Server is created.
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.
