I created a DSN on the mac and ran the test from ODBC manager and it passes.
I am also able to connect from my Mac using a SQL Server gui app that doesn’t require a DSN, just the IP, name and pw.
When I try to access the DSN from Xojo I get [Actual][SQL Server] Unable to connect: Server is unavailable or does not exist. How is it possible that the DSN can connect but not a line of code that points to the DSN?
Var db As New ODBCDatabase
db.DataSource = “cgpsusers”
Try
db.Connect
// Use the database
Catch error As DatabaseException
// Connection error
// or the user selected
// Cancel from the ODBC browser
End Try
Did you add your as a “User DSN” or a “System DSN”? I’ve had much better luck with the latter.
Also, I see you’re setting the DataSource property, but not the Username & Password property.
Try setting those properties before calling “db.Connect”.
Var db As New ODBCDatabase
db.DataSource = “cgpsusers”
//
//**new code**
//
//use the actual username and password when
//you add these lines to your project
db.UserName = "myUser"
dp.Password = "myPassword"
//
//**end new code**
//
Try
db.Connect
// Use the database
Catch error As DatabaseException
// Connection error
// or the user selected
// Cancel from the ODBC browser
End Try
I am using a system DSN and I have tried explicitly coding in the username and password. Same results. I have made this work many times with SQL Server 2005 and 2008. This is a brand new SQL Server 2019, could that be it?
I just tried the code above in SQL Server 2019 (changing dp.Password to db.Password) and it worked just fine using the Actual ODBC driver on macOS. I used the sysadmin user name and password.
I don’t have a lot of experience connecting to SQL Server over ODBC, but based on Robert’s comment, it sound like you should be able to connect over ODBC.
“I am also able to connect from my Mac using a SQL Server gui app that doesn’t require a DSN, just the IP, name and pw.”
Based on what I’ve read in the forums, @Christian_Schmitz 's SQL plugin certainly seems to make working with SQL Server easier. (and I’m 99% can make a connection without a DSN).
I do understand 3rd party plugins aren’t always an option.
Question: Is there a “Test” button in the ODBC Manager app when you’re configuring the DSN? If yes: When you run the test, does the DSN connect/test pass?
1 Like
