Q:
Is there an article walking through use of MS Sql Server?
Not to Import data but to read and write an MS Sql Server database directly?
A:
Lianja does this via Virtual Tables.


Lianja Cloud Server 1.3 Release for Windows will include data source independent data access to Lianja SQL Server, MSSQL, PostgreSQL, Oracle and MySQL using Lianja Virtual Tables.
The Lianja APaaS Developer and App Builder distributions will include the sample App 'Lianja Web Virtual Tables Demo' (example_webvirtualtables) using virtual tables in the included cloudlibtest database to connect to MySQL and MS SQL Server.
To run the App, you will need to have ODBC access set up to a MySQL Server with the sakila sample database and to the AdventureWorks sample database on MS SQL Server.
link: http://www.lianja.com/doc/index.php/...a_Cloud_Server


Q:
I can create a system DSN to the SQL server using the SQL Server Native Client 11, the SQL server or ODBC driver 11 for SQL Server. However if I use Lianja ODBC driver, it can't connect (no response is the answer) In the app Builder, it can't use any of the connection I create with the other drivers. (Failed to connect to DB source.)
Windows 7, tried 32 and 64 bit options. Tried using the ODBC manager.
How are connections to MSSQL v 11 (2014) handled?
A:
Lianja currently only supports 32 bit ODBC drivers so you will want to verify that you are using the 32 bit version of the SQL Server ODBC driver as well as the 32 bit ODBC Administrator (usually found here: C:\Windows\SysWOW64\odbcad32.exe).
You can test the connection in the ODBC tab of the Console WorkSpace by entering your DSN name as the Connstring and supplying any necessary user credentials.
The Lianja ODBC driver is used to connect to the Lianja SQL Server.


Here what I was able to connect.
Driver={SQL Server Native Client 11.0};Server=myServerAddress;
Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Instead of a local or system DSN, it is the actual string that is saved by the control panel that worked for me.


You can always specify the full connection string if you do not want to setup an odbc dsn on each machine.
As dave has pointed out you need to create a 32 bit odbc dsn and also have the 32 bit odbc driver installed.
Many people are using virtual tables with odbc.
Later as detailed in the roadmap we will be providing a 64-bit version on windows which will use the normal 64 bit odbc drivers but in the meanwhile you need to use the 32 bit ones.

(?A):
One tip on installing the 32-bit drivers on 64-bit machines: regardless of your SQL Server version (back to 2005), get the 64-bit "ODBC Driver 11 For SQL Server" 64-bit install https://www.microsoft.com/en-us/down....aspx?id=36434 and it will install both the 64-bit driver and the 32-bit driver. If you try to install the 32-bit install on an x64 machine, it will fail.

When building an installer, run both installs for silent install, using the directions here: https://msdn.microsoft.com/en-us/lib...=sql.110).aspx (This does not appear to be searchable by Google, so store it away somewhere for when you need it). The installation command will look like this, but you will want to rename the installers to identify them. This command line is different than all prior versions in a couple of ways:

msiexec /quiet /passive /qn /i msodbcsql.msi IACCEPTMSODBCSQLLICENSETERMS=YES
The ODBC Driver 11 will work against all SQL Servers back to 2005, so this simplifies your installer. Further simplifying matters, as mentioned above, you can have the installer run both the 64-bit (which includes 32-bit) and the 32-bit installs. If on x64, the 33-bit install will fail to run (but do so silently and quickly), but the 64-bit install will install the 32-bit driver. The reverse is true if you are installing on a 32-bit server.


1. SET DEBUG ON in the console.
2. Select the ODBC tab
3. Open a MySQL ODBC DSN
4. Create a table called ftest with a float(10,2) column then insert one row into it
5. select * from test
6. quit Lianja
Now look in c:\lianja\debug\debug_client.txt


Lianja Cloud Server 1.3 Release for Windows will include data source independent data access to Lianja SQL Server, MSSQL, PostgreSQL, Oracle and MySQL using Lianja Virtual Tables.
The Lianja APaaS Developer and App Builder distributions will include the sample App 'Lianja Web Virtual Tables Demo' (example_webvirtualtables) using virtual tables in the included cloudlibtest database to connect to MySQL and MS SQL Server.
To run the App, you will need to have ODBC access set up to a MySQL Server with the sakila sample database and to the AdventureWorks sample database on MS SQL Server.
For more information on this, please see Virtual Tables - Lianja Cloud Server.
We have just added a 'Troubleshooter' wiki page for this for anyone who is having difficulty accessing the AdventureWorks sample database on MS SQL Server. Follow the link on the original Virtual Tables - Lianja Cloud Server page, or go directly to the Troubleshooter here.


ODBC Steps:

(1) Sample SQL Server database
I needed a sample MS SQL Server database mentioned in text above. So I downloaded AdventureWorks from http://msftdbprodsamples.codeplex.co...ses/view/93587
2008 version: http://msftdbprodsamples.codeplex.co...ads/get/478218
I unzipped it into folder with my other databases.
In MS SQL Server Management Studio I attached this database

(2) ODBC
Now setting ODBC according https://www.youtube.com/watch?v=tUiaK5fRH7k







There are several better ways, but I'd suggest the following 2 which allow you to connect to SQLServer without setting up an ODBCconnection .

1. Virtual Tables

Here is an example for connecting to SQLServer using a trusted connection.

create virtualtable <tablename> connstr 'driver={SQL server};server=<servername>;Trusted_Connection=Yes ;database=<dbName>' as select * from <tablename> where 1 =2

You should look up cursoradaptors to understand how to handle large data sets.

2. The other suggestion is SQLStringConnect.

It is a DSNless connection, so you done need to use a named connection . There are examples on the forum.
http://www.lianja.com/community/show...lstringconnect

- The windows ODBC utility will be located in different locations depending if you are using a 32 bit or 64 bit system.
32 bit: C:\Windows\System32\odbcad32.exe
64 bit: C:\Windows\SysWOW64\odbcad32.exe

Note: 64 bit systems will have the utility in the same path as the 32 bit system although it is only configured for 64 bit programs.
To ensure compatibility with other systems and programs, it is recommended to use the 64 bit utility from the path mentioned above.

- SQLServer is typically used for older connections (older programs). New connection might be able to use any of the various versions of Native Client.

- When selecting the server from the drop down box, if the server is not listed, you can manually enter the IP address for the server name.

- SQLServer authentication is used more if the SQLServer is not located on the main machine.

If a specific user is not setup in the SQLServer , many people will use the system administration account (which should have been configured when SQLServer was installed).
In this case the login ID (user name) would be 'sa'.

- In Lianja, you can test the connection by selecting the ODBC tab.


Q:
differencies and/or benefits of Lianja Server in comparison to PostGres.
A:
PostgreSQL is a good database and can be used with Lianja.
Lianja SQL offers better JSON support and stored procedures can be written in Lianja/VFP.


Q:
ODBC test from console fails while "Datasource" test from "Administrative Tools" panel succeeds. This does'nt happen on Vista 32bits.
Driver is "MySQL ODBC 5.2 ANSI DRIVER" for Windows 64bits and Windows Lianja RC7.
A:
Lianja on Windows is a 32 bit application, so you will need the 32 bit MySQL ODBC Driver and the Datasource set up in the 32 bit ODBC Administrator (%windir%\SysWOW64\odbcad32.exe).
Did you use the correct version of odbcad32.exe, referenced in Yvonne's post, above?

Using the ODBC manager from the control panel will bring up the 64-bit ODBC manager, which is the wrong one. You can create a shortcut to the correct one to make access to it easier.


Q:
are you suggesting using MSSQL views rather than creating a Virtual Table?

If so, this will limit your ability to move between backend databases. It will also make source control more difficult: with a VT created through a prg (or deconstructed into a prg, which is easily doable in Lianja). Done in Lianja, versioning, merging, etc. all remain within your control. In most cases, creating a VT using a PRG is the quickest way to create, and debug/fine-tune, a Virtual Table. The exception would be the simplest VT's.
A:
There is no performance advantage (beyond the first run) between using an MSSQL view and a Virtual table. The MSSQL Plan Cache will be the same for each after the first run of the VT.

Q:
I am replacing a system that I built prior to Lianja that has hundreds of stored procedures already in MS SQL.
A:
There's no reason to rewrite that code.

Q2:
Additionaly, I am building systems that interact with other vendor products that are already on our SQL Server.
A2:
Vt allow you to really take advantage of all the built in processes like relating sections, auto insert /update/ delete etc.

​All topics in [Answers] alphabetically: http://www.lianja.com/community/show...p?2717-Answers