2. 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.

Connection Troubles

  • 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.
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]
    "DSQUERY"="DBNETLIB"

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.

Moving from local SQL Server to SQL Server on RDS

Use SQL Server Management Studio and select the database that will be used as the source. Right click on the database and select the generate scripts task. Select advanced, scroll to types of data to script and select Schema and Data.  Once the file is saved, open it to make some minor changes for Amazon RDS compatibility.  Change the path in the FileName section of the script to be D:\RDSDBDATA\DATA\ instead of the default C:\Program Files\..... . Save the script. Next, run the script to import the data and the schema. The last step is to open security on the newly created database and ensure that the user created in the script has db_owner rights to the database.
 
Performing the above steps, allowed the user to copy the schema and the data from a SQL Server 2008R2 instance on a traditional Windows Server 2008 R2 instance and migrate it intact to an Amazon RDS instance.

Knowledge Tags


This page was: Helpful | Not Helpful

Search Knowledge Books and Forums