3. Microsoft SQL Server
Connecting to a Named Instance
In order to connect to a named instance of SQL Server you will need to configure an alias for that instance as described here: http://support.microsoft.com/kb/265808
That will allow a direct connection to that alias from the HelpSpot Windows installer.
Full Text Search Returns No Results
Make sure the full text indexes are created as described here: http://helpspot.com/helpdesk/index.php?pg=kb.page&id=142
If they are make sure the full text service is running. In services it's called: "SQL Full-text Filter Daemon Launcher"
Before starting it make sure in properties it's set to start automatically and that under "log on" it's using the local system user. Save any changes and start/restart the service. You may also need to go into the full text indexes and run a full population and/or apply tracked changes.
- ntwdblib.dll - The most common issue is that you do not have the ntwdblib.dll file installed in your PHP directory (where php.exe is, or sometimes placing it in the ext directory works as well). This library can be found with your Enterprise Manager dll's or in your SQL servers system32 folder. It's generally best to take the file from the server where SQL Server is installed.
Related forum post: Unable to connect to the database
PHP.net: Information on the PHP MSSQL extension, lots of good tips in the user comments
- ntwdblib.dll Version - On some systems the ntwdblib.dll version is too old to work correctly with PHP. If your version does not end in 80.194.0 it's probably too old. You can download the 80.194 version here.
- SQL Client Tools - On some servers you will need to install the SQL Server client tools. This is especially true if your SQL Server is on a different server than your web server.
- (local) Registration - If your SQL Servers registered name is (local) you'll need to delete that registration and change the registration to the machine name. You should then be able to set the cDBHOSTNAME in config.php to 'localhost' and be able to connect.
- SQL Authentication - The SQL Server must be setup to use both SQL Server authentication and windows authentication. Both for the user as well as the server as a whole. This setting is in SERVER_NAME->Properties->Security
- privileges - The HelpSpot database user must have sufficient privileges to access the database. Making them the database owner will work, though other configurations may also be effective. The database user will also usually need to have public access enabled.
- port number - On some PHP/SQL Server installations you'll need to provide the port number your SQL Server listens for requests on. You can do this by adding a comma and the port number to the cDBHOSTNAME variable like this: "db.mycompany.com,1433"
- TCP/IP in SQL Server 2005 - In order to connect by IP you may need to specifically allow that protocol by going to the SQL Server Configuration Manager, go to protocals and then enabled TCP/IP.
- Registry key - In rare cases an edit to the registry may help.
Error: "The statement has been terminated."
In extremely rare cases SQL Server can lose seeding on the HS_Request table or another tables, primary key. If this occurs inserts to the database will fail with this error. You can check if this is the case by running the following in management studio:
DBCC CHECKIDENT ('HS_Request');
If the current seed value doesn't match the current column value (it will be less) than there's a problem. You can reseed the tables primary key with this command:
DBCC CHECKIDENT ('HS_Request',RESEED,######);
where ###### is the new ID value. Make this a few higher than the current highest value in the table. That should resolve the issue.