Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

All other Modules

Discuss all other Modules here.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

MSSQL modules


Reply

4 Posts   2538 Views

Avatar
alexanm

25 August 2009 at 4:40pm (Last edited: 25 August 2009 5:02pm), Community Member, 38 Posts

Hello

I have just downloaded and tried the mssql module.
My Configuration is:

- Windows 7 Ultimate with IIS 7.5
- PHP 5.2.10 (as 5.3 won't work now) configured as Fast-CGI
- SQL Server 2008 SP1

So my first question is: Why do I need a working mysql database in order to be able to use the mssql module?
Second (as I do not want to install mysql) I have tried to use the mssql module directly.
I can see Silverstripe connecting to the database, but when it tries to execute the first create table command, the php-cgi.exe crashes.

The command it tries to execute looks like this:

CREATE TABLE "TrackBackPing" (
   "ID" int(11) not null auto_increment,
   "ClassName" enum('TrackBackPing') character set utf8 collate utf8_general_ci default 'TrackBackPing',
   "Created" datetime,
   "LastEdited" datetime,
   "Title" varchar(50) character set utf8 collate utf8_general_ci,
   "Excerpt" mediumtext character set utf8 collate utf8_general_ci,
   "Url" varchar(50) character set utf8 collate utf8_general_ci,
   "BlogName" varchar(50) character set utf8 collate utf8_general_ci,
   "PageID" int(11) not null default '0',
   primary key ("ID")
);

but the correct way of creating this table should look like:

CREATE TABLE dbo.TrackBackPing (
   ID bigint IDENTITY(1,1) NOT NULL,
   ClassName nvarchar(50) NOT NULL DEFAULT 'TrackBackPing',
   Created datetime NULL,
   LastEdited datetime NULL,
   Title nvarchar(50) NULL,
   Excerpt nvarchar(max) NULL,
   Url nvarchar(50) NULL,
   BlogName nvarchar(50) NULL,
   PageID bigint NOT NULL DEFAULT 0,
   CONSTRAINT PK_TrackBackPing PRIMARY KEY CLUSTERED (ID),
   CONSTRAINT CHK_ValidEntriesForClassName
      CHECK (ClassName IN ('TrackBackPing' COLLATE Latin1_General_CS_AS))
)

And as far as I can see, there's a fair amount of differences between the two statements.

So did I miss anything? Are there any options I need to set on the sql server side?

TIA
Markus Alexander

Avatar
Sean

25 August 2009 at 5:09pm (Last edited: 25 August 2009 5:12pm), Forum Moderator, 921 Posts

You need to use SilverStripe trunk in order to use this module. It is not compatible with SS 2.3.

Also, make sure that you're setting the correct database configuration details in your mysite/_config.php file:

global $databaseConfig;
$databaseConfig['type'] = 'MSSQLDatabase';
$databaseConfig['server'] = 'localhost';
$databaseConfig['username'] = 'mydbusername';
$databaseConfig['password'] = 'mydbpassword';

Out of interest, are you using the SQL Server driver for PHP from Microsoft? [url]http://msdn.microsoft.com/en-us/library/cc296161(SQL.90).aspx[/url]

Cheers,
Sean

Avatar
alexanm

25 August 2009 at 5:39pm Community Member, 38 Posts

Hello Sean,

ok, thanks for clarification. I somehow missed this line on the modules page ;-).

The database config is correct, because I can see the command in the sql server profiler...

No I am using the the mssql package coming with php5. Should I switch?

Ok so I have to install mysql until silverstripe 2.4 arrives :-(...

Thanks
Markus Alexander

Avatar
philthegr81

11 June 2010 at 8:36am Community Member, 2 Posts

I was running into the same exact problem, in the same exact environment. After searching all over, I found the solution.

First, you need to install Microsoft's SQL Server Driver for PHP. It replaces the entry in php.ini that loads php_mssql.dll. The package containing the DLL's is at the following link:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ccdf728b-1ea0-48a8-a84a-5052214caad9

Simply unpack them and copy them to your PHP extensions directory.

The DLL I used was php_sqlsrv_52_nts_vc6.dll. Keep in mind I installed PHP using Microsoft's Web Platform Installer, which put PHP 5.2 in place. If you installed PHP manually, you can pick from the various other DLL's, which seem to cover threadsafe and non-threadsafe, as well as VC6 or VC9 builds of PHP 5.2 or 5.3.

So, just comment out the "extension=php_mssql.dll" line in your php.ini, and add in "extension=php_sqlsrv_52_nts_vc6.dll" below it.

Once I put this in place and restarted the World Wide Web Publishing Service service, the install got further. Not to the finish, but past the crashing point. It got all the way up to "Building database schema..." and stopped. At this point, I checked the MS SQL Server to see if it actually created the database and tables, and it certainly did.

The only thing left to do is update mysite/_config.php to point it to your MS SQL Server, which Sean's post above outlines how to do that.

I hope this all makes sense, as it seems to be working just fine, but if it goes back to crashing, I'll update the thread.