Connection to SQL Server Express 2014

Databases
1

I will preface this with saying I’m mostly a Mac guy so assume I’m an idiot when it comes to Windows.

I just installed SQL Server Express 2014 on my local machine. I’ll be damned if I can get a Xojo app built in 2015 R2.2 working with it. a) Either the Xojo db plugin won’t work with that version of SQL Server Express or b) I’ve got the settings messed up.

I will assume b) for now. :slight_smile:

When it comes to the various settings, Host, Port, UN, PW what should I be looking for and where? So…help!?

2

I have rarely used Express, but I think you have to enable networking when you install it. I think by default it uses a local named pipe protocol, sort of like a named socket on unix type platforms. I believe you can do this when you install, if not you may need to load the management studio program or whatever it’s called, and then use that to turn on TCP/IP. I’m sure you can figure it out with a bit of Google Fu.

3

If you wind up using ODBC to connect make sure you are using the 32-bit version of the ODBC tool in Windows. Otherwise none of your DSN’s will work and you will be scratching your head for hours.

4

I can connect without any problems.

  dim db as new MSSQLServerDatabase
  
  db.Host = "MY-PC\\SQLEXPRESS"
  db.Port = 1433
  db.UserName = "xojo"
  db.Password = "xojo"
  db.DatabaseName = "xojo"
  
  if db.Connect then
    db.Close
    MsgBox "Connected"
  else
    MsgBox db.ErrorMessage
  end if

By default SQL Server Express doesn’t allow TCP connections. You will have to change some settings using the SQL Server Configuration Manager if you haven’t already done so.

Here’s a link that shows how to configure it, better than I can tell you.

http://support.webecs.com/kb/a868/how-do-i-configure-sql-server-express-to-allow-remote-tcp-ip-connections-on-port-1433.aspx

5

Sorry, I see Kevin pretty much explained it… good luck.

6

I have a Xojo Windows app that connects to an SQL Server express database. These days I use the MBS SQLDatabaseMBS plug-in because I need access to large BLOBs but originally I used the native Xojo MSSQLServerDatabase without any issues. I just set the Host, DatabaseName, UserName and Password properties and it all worked. I’ve not tried connecting via ODBC though.

7

Thanks for all of the help. I’m probably going to write up a full blog post on this but here are the steps to connect to the DB Server in case someone else needs this (most likely me when I need to do this again in a year from now).

First, in the SQL Configuration Manager, go to the SQL Server Network Configuration settings, go to the IP Addresses tab and scroll all the way to the bottom section titled IPAll. Set the TCP Port you want. I set mine to 1433.

For the database connection string use the Server Name presented to you in the SQL Server Management Studio. On my VM this ended up being “BOBS-WINDOWS7\SQLEXPRESS”. As soon as I got this information everything worked great.