Trouble Configuring ODBC Connection to work with XOJO

Databases
1

Merry Christmas,

I have developed a xojo web app on my mac connecting to a remote mssql server successfully using actual tech’s mssql odbc driver for mac. Basically once I configured the connection I just connect to it’s data source name. Perfect!

I am now attempting to deploy to the live server windows 2008 (32 bit) server, on the same box as the mssql server. I have created a system DSN (using the same name as when developing so I don’t need to change code) using the Native Client 10 (32 bit) driver. I am however unable to connect to the database.

Mssql server version is:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
Jun 17 2011 00:57:23
Copyright © Microsoft Corporation
Express Edition with Advanced Services on Windows NT 6.0 (Build 6001: Service Pack 1) (Hypervisor)
and Native Client Version 10 is:
2009.100.2500.00

Does this driver / host combo work with the Xojo ODBC driver? I have been trying to get this connected for a few days, and it is getting urgent.

I have configured my standalone to run as a windows service, which is running well (just no db connection).

I did post this in the Pro section a few days ago, but probably should have posted it here.

I would be grateful to receive some advice.

Kind regards, Andrew

2

What does Xojo return for an error when you try to connect (check ODBCDatase.Error and ErrorMessage)?

3

Thanks for your reply Paul

In response to this test code

Dim db As New ODBCDatabase
db.DataSource = “WebOrders”
If db.Connect Then
MsgBox(“Connected”)
Else
MsgBox(“Connection error:” + db.ErrorMessage)
End If

The msgbox says ‘Connection error:[Microsoft][SQL Server Native Client 10.0]Dialog failed’

Kind regards, Andrew

4

A Google search on that error points out that the Native Client may be trying to open a dialog of some kind.

I’m not sure how exactly you would do that based on your config, but this post suggests adding “SQL_DRIVER_NOPROMPT” as a parameter:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4f1c2899-e481-4eb5-8f72-b97e003937b7/dialog-failed-error-while-connecting-to-sql-server-using-odbc?forum=sqldataaccess

5

Thanks again Paul,

I read that link and the docs on ODBC connection strings for mssql server.

I tried a range of things, nothing has worked so far.

In my travels I had read that when using a named DSN, “SQL_DRIVER_NOPROMPT” is the default.

Regardless, I tried not using a named DSN and using my own connection string instead like:

db.DataSource = “Driver={SQL Server Native Client 10.0};DriverCompletion=SQL_DRIVER_NOPROMPT;UID=sa;PWD=****;SERVER=SYD1-0098DSB\BIZ;Database=WebSales” but it still bombs (with the same error message).

same with
db.DataSource = “Driver={SQL Server Native Client 10.0};DriverCompletion=SQL_DRIVER_NOPROMPT;UID=sa;PWD=****;SERVER=(local)\BIZ;Database=WebSales” but it still bombs (with the same error message).

*** for security they were populated correctly in my original.

I wonder if anyone has a working connection string they might like to share? Any other ideas Paul?

Sincere thanks, Andrew

6

This is what works for me connecting to SQL 2005 through ODBC:

BAP4 = new ODBCDatabase_TT #if TargetWin32 BAP4.DataSource = "Driver={SQL Server};Server=192.168.242.118;Database=BAP4;UID=sa;Pwd=tanner" #endif #if TargetCocoa BAP4.DataSource = "Driver={Actual SQL Server};Server=192.168.242.118;Database=BAP4;UID=sa;Pwd=tanner" #endif

On Windows, it’s using the the ‘SQL Server’ driver, not the native client driver.

7

Have you configured the server for TCPIP and is the SQLServer Browser service running?

8

Yes it’s listening on standard port 1433 and the Browser service is running. These are User DSNs configured using C:\Windows\SysWOW64\ODBCAD32.EXE, running in W7-64 bit VM.

Note that the connection strings above were tested in dev, but in production I use the standard OS ODBC connection utilities. The IDE contains an inserted instance of the ODBC database. All primary dev is done on a Mac and uses the Actual SQL Server connector.

9

I am using Windows Server 2008 R2 with SQL 2008.

This is apart of my db Class I use to make queries with:

[code] dim db as new MSSQLServerDatabase
dim rs as RecordSet

db.Host = cfgServer
db.DatabaseName = cfgDatabase
db.UserName = cfgUser
db.Password = cfgPassword
db.Timeout = -1

if db.Connect then
rs = db.SQLSelect(query)

if db.Error then
  MsgBox "ERROR: " + db.ErrorMessage
  
else
  return rs
  
end if

else
MsgBox "ERROR: " + db.ErrorMessage

end if[/code]

10

Why not stick with straight ODBC since you’re already using it successfully on the Mac? An identically named DSN should work on both platforms without tweaking, provided it’s installed using the correct ODBC Manager in Windows. MS notes here. I’ve had a few issues with System DSN’s on client machines, I now generally just use User DSN’s.

11

Thank you for all your assistance,

All working now.

I did stick with straight ODBC using a similarly named System DSN. However I did (to satisfy the dialog error) pass username and password as params to the connection.

I didn’t notice that my clients server also had Cold Fusion installed which has it’s own ODBC Manager Service running which seemed to complicate matters when testing.

Eventually I reinstalled the Sql Server Native Client 10 driver, and all came good.

Besides the little db hurdle completing this project in Xojo as a web project has been a breeze and very well received by the client and their staff.

Thanks again to all that posted.

Kind regards, Andrew