ODBC Connection to MS SQL Server (step-by-step)

[josipradnik]

My VFP aplications use MS SQL Server. I never needed ODBC.
I could not find the whole story about ODBC in Lianja.
http://www.lianja.com/resources/blog/39-coding-tips/111-working-with-external-data-sources-in-lianja-beta6

After lot of time here is what I know now (sorry forum does not accept normal size PDF and there is a lot of screenshots):

(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.com/releases/view/93587
2008 version: http://msftdbprodsamples.codeplex.com/downloads/get/478218

I unziped 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

761

762

763

I tried with SQL Server authentification but no success on Lianja side.
Suppose wrong user settings. I am not a database expert.

Hope this tips will help to someone.

Josip Radnik

[hmischel@diligentsystems.com]

Hi Josip,

This is an older way of connecting to SQL Server.

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

1. Virtual Tables

Here is an example for connecting to SQL Server 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/showthread.php?929-ODBC-Connection-String-definition-and-example(s)&highlight=sqlstringconnect

Hope this helps

Herb

[CGibson]

Hi Josip,

Thanks for posting. Here are a few additional notes (based on my previous usage).

- Lianja has its own ODBC utility that will create the System DSN for you.
http://www.lianja.com/doc/index.php/Lianja_ODBC_Manager

- 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.

- SQL Server 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.

- SQL Server authentication is used more if the SQL server is not located on the main machine.
If a specific user is not setup in the SQL Server, many people will use the system administration account (which should have been configured when SQL server 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.

Cory