[Guide] Connecting to a Microsoft SQL server with dsn-less (dsnless) connection

[martinp]

Here is a very basic example of an SQL connection with a dsn-less connection to a Microsoft SQL Server.  (pvxplus 21)

This is through windx and for reference on linux but should work elsewhere.

I had great difficulty with the documentation finding this so I hope it can be updated for this simple case, I hope this helps someone I found numerous mailing list tips ideas on this and I wanted to just summarize it all to a very simple real world example.

SQL_Server = CONSTRUCT-SQL
SQL_Database = Artcraft_Machines
uid = SQL user id
pwd = SQL password

SQL_Table = scm_artcraft

Now because I specify "uid" and "pwd" in the connect string with the OPT=  We do not have to pass the different syntax of USER=abc, PSWD=def on the Open.  If you do not specify in connect string then you must use this USER/PSWD syntax on open.

Also it's critical to pass "ignore;"  as the first parameter on the open as it just skips this because we are using a connect string but you still need it on the open as a padding.

Code Select Expand


0010 begin
0020 !
0030 let OPT$="CONNECT='Driver={SQL Server};Server=CONSTRUCT-SQL;Database=Artcraft_Machines;uid=artmachine;pwd=artmachine'"
0040 open (1,iol=*,opt=OPT$)"[lcl][odb]ignore;scm_artcraft;Key=Code" ! ;USER=artmachine;PSWD=artmachine"
0050 !
0060 select * from 1
0070 print CODE$,"   ",THICKNESS,"x ",WIDTH," x ",LENGTH
0080 next record
0090 !
0100 print lst(iol(1))



There was also notes of using {SQL Server Native CLient 11.0} instead of {SQL Server} or whatever vers I think this is needed, I assume based on windows versions.  This may differ based on your SQL version.  But for me I was just trying a regular Microsoft SQL Server.

[martinp]

I need to add a few more important notes I came across:

The above works great for a simple single connection however if I wanted to have multiple individual channels open at once for various tables then a number of changes have to be done.

#1 I had to install the latest SQL ODBC driver from microsoft Search: ODBC Driver for SQL Server"  At time of this writing it was 18.4.1.1  After you install this you will have a new Driver data source named:

'ODBC Driver 18 for SQL Server'

Code Select Expand

let OPT$="CONNECT='Driver={ODBC Driver 18 for SQL Server};Server=CONSTRUCT-SQL;Database=InSight;TrustServerCertificate=yes;MARS_Connection=Yes;uid=artcraftinsight;pwd=insight'"
#2 A few key Added options were needed at least for my case.

MARS_Connection=Yes

is the important one which allows multiple requests at one time.

TrustServerCertificate=yes (This was for my setup)

If you are not passing a user name for a trusted connection add: ";Trusted_Connection=yes"

After I did above I could create as many concurrent opens to the SQL Database as I needed.

Hope this helps.