Connecting to MSSQL

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!

MSSQL IF (multiple fields > one value)

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.