I have had to create a few PHP sites that connect to a MSSQL server. Not being down with setting up these servers, there are a couple of caveats you should be aware of if you are not accustomed to the server yourself.
TCP/IP access is turned off by default, so if you are trying to connect via IP address, you will need to switch this on. It is in the SQL Server Configuration Manager under the Server Network Configuration > Protocols for xxx
Also, if you are a MySQL guru, you will use user accounts to connect, and MSSQL can have the same, but they are switched off by default. If you connect to your MSSQL server instance using the Management Studio and right click on the server (the top item in the filetree) and choose properties, you will find a “Security” tab, where you will find an option for allowed authentication types. Switch it to SQL Server and Windows Authentication mode.
This will save you a few hours!
My life. I installed MSSQL 2008 Evaluation for testing of a new product. Great. But after 180 days it expired.
I was told I could upgrade it to MSSQL 2012 Express (like a community free DB) – but this wouldn’t work as I didn’t have 2008 R2 SP2 installed, and I couldn’t install that as Windows told me there was nothing to install.
So, I uninstalled 2008 and installed a fresh MSSQL Express.
Either I had blocked it out, or 2012 comes with fresh problems.
By default, the following road blocks are in place:
- Windows Firewall blocks port 1433
- Server authentication is set to only allow Windows Authentication only.
- TCP/IP connections are switched off.
So, to fix all these, you need to
- Add an exception to Windows Firewall, or just the damned thing off.
- Right click the server in MSSMS, choose properties, and then security and select ‘SQL Server and Windows Authentication mode’
- Go into the Server Configuration manager and choose ‘Network Configuration’ and double click ‘Protocols for SQLEXPRESS’ and in there you’ll find a load of options for connections. Check to make sure TCP/IP is enabled. And in the IP addresses section. make sure you have the correct options…
And then, if this isn’t tough enough for you to tackle, try using it on Windows 2012 Server! Hah, what a load of tosh and an extremely frustrating morning…
In MySQL I use IF(value,then,else) quite often, but in a new project I am doing I need to do the same in MSSQL.
SELECT field1, field2, IF(field3 = '1', 'Started', 'Not Started') AS status FROM table WHERE id = x;
This won’t work in MSSQL, but this will:
SELECT field1, field2, status = (CASE WHEN field3 = '1' THEN 'Started' WHEN field3 <> '1' THEN 'Not Started' END) FROM table WHERE id = x;
Not worked out the case ELSE yet, but haven’t got time today, so just recording the above.