MSSQL connection fails

Just starting out? Need help? Post your questions and find answers here.
User avatar
jacdelad
Addict
Addict
Posts: 1431
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

MSSQL connection fails

Post by jacdelad »

Hi,
I know how to use MySQL, but now I'm fighting with MSSQL. I want to connect via ODBC, but it always fails. I have the corresponding connection with Python which is

Code: Select all

con = pymssql.connect(host='sqlsrv01', user='username', password='password', database='databasename', as_dict=True)
and works fine. What do I have to use in PureBasic?
I tried

Code: Select all

OpenDatabase(#PB_Any,"host=sqlsrv01 dbname=databasename","username","password",#PB_Database_ODBC)
and

Code: Select all

OpenDatabase(#PB_Any,"sqlsrv01","username","databasename",#PB_Database_ODBC)
which doesn't work. Initializing ODBC works fine. Which ODBC-entry do I need to have to use the MSSQL connection, is there a chance, that Python uses a different way and I don't have the needed ODBC driver installed?
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: MSSQL connection fails

Post by Marc56us »

Some tips:
1. Is your python sample that works is on same computer ?
2. What about DatabaseError() ?
3. This is not host or database name who need to be provide, but only ODBC connection name
4. username and password can be empty if defined in ODBC connect setting
5. Try PB sample with your data
https://www.purebasic.com/documentation ... abase.html

Code: Select all

  UseODBCDatabase()

  If OpenDatabase(0, "ODBC_connection_name", "user", "password")
    Debug "Connected to MySQL"
  Else
    Debug "Connection failed: " + DatabaseError()
  EndIf
:wink:
User avatar
jacdelad
Addict
Addict
Posts: 1431
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Re: MSSQL connection fails

Post by jacdelad »

1. Yes.
2. Tells me about a driver error.
3. Ok, so I can put in whatever I want?
4. Ok.
5. Doesn't work either.

So it seems I'm missing the driver...
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: MSSQL connection fails

Post by Marc56us »

2. The exact error is ? (Tip: Ctrl+C even on a graphic message box will copy text message in clipboard.)
3. You must use only the Name (column 1) you have set when you have created new user DSN.
Image
Screenshot Microsoft

PS. Call ODBC administrator as admin to create new connection
(Win + R "odbcad32.exe")
:wink:
User avatar
jacdelad
Addict
Addict
Posts: 1431
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Re: MSSQL connection fails

Post by jacdelad »

The error is
Connection failed: [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben
But I have MS Access Database in my ODBC sources...
Also I'm not admin on this computer.
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: MSSQL connection fails

Post by Marc56us »

?
At first, you speak about MSSQL (= Microsoft SQL Server ) and now MS Access Database.
This is not the same database.
For MSSQL you need to add SQL Server data source (at bottom of list)
User avatar
jacdelad
Addict
Addict
Posts: 1431
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Re: MSSQL connection fails

Post by jacdelad »

Humm, ok, my fault. Someone told me MS Access is just the program to access the MSSQL database. Wasn't aware of that.
Again, I can't add or modify something, I'm not an admin of the computer. Seems like I need a workaround, like calling a python script and gathering the information from there.
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: MSSQL connection fails

Post by Marc56us »

Microsoft Access is an old database (but still exist).

I just tested on my computer and it seems that you can add a new connection without being an administrator.
(Sorry, my Windows is in French, I will try to quote in English)
Try:
- Win + R
- type: odbcad32.exe
- Click Add
- Select SQL Server (at end if exist. If not: download it from MS)
- Click Done
- Set a name (without space) as you want. This was this name you will use then
- Add comment if you want
- Select Server in drop done menu
... I don't have SQL Server, so I can't help from here :|
- Click Next
User avatar
jacdelad
Addict
Addict
Posts: 1431
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Re: MSSQL connection fails

Post by jacdelad »

Hi Marc56us!
Great, that worked. I added the entry and now get a connection, very great!!!

Thanks to everyone involved and sorry for my confusing questions. I'll post some more when I'm stuck again.
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Post Reply