Mantis on SQL Server 2000 from Microsoft

Get help from other users here.

Moderators: Developer, Contributor

Post Reply
Andreas
Posts: 1
Joined: Jul 22, 2005 9:34 am

Mantis on SQL Server 2000 from Microsoft

Post by Andreas »

Hello
a customer of me want´s to run mantis on an Microsoft SQL Server.
is this possible? he says that he found an article where is stated that
this should work.

Well i´m not using mantis but i´m a mcdba with microsoft SQL Server
the script´s provided to me showed not the correct T-SQL Syntax
So when i pasted the script into Querry Analyse i got lot´s of error´s.

My customor is desperat for me to solve this problem....
is there a new T-SQL script for me so i could install the DB with the
new Table´s

Any help is greatly appreciated!
Thx in advance for your help

Andreas Hand
MGN Information Technologies

vboctor
Site Admin
Posts: 1289
Joined: Feb 13, 2005 5:11 pm
Location: Redmond, Washington
Contact:

Post by vboctor »

We have just released Mantis 1.0.0rc1 (http://www.futureware.biz/blog/index.ph ... &tb=1&pb=1) which has a new install which uses ADODB to create/upgrade database for MySQL, Pgsql, and MS SQL.

You may find some minor issues with MS SQL (since it is experimental), but i will be much easier that working with the old db_generate.sql or the old mssql.sql which were shipped with releases up to Mantis 1.0.0a3.

Please report any issues or fixes that you apply to http://bugs.mantisbt.org to that we get them into Mantis before the 1.0.0 final release.

Regards,
Victor
MantisConnect
http://www.futureware.biz/

Ingrid

Post by Ingrid »

I'm working together with Andreas on this case and just doesn't have a clue how to get it working with Microsoft SQL.
We downloaded the new version 1.0.0rc1 and I replaced all files in the web directory folder where Mantis had been installed before. We've tried a lot of changes in the php files where you can define the database, name, password, db_type and so on but we end up that the login won't be accepted. The last error message we could produce was:

APPLICATION ERROR #401
Database query failed. Error received from database was #37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Die gespeicherte Prozedur DESCRIBE konnte nicht gefunden werden. for the query: DESCRIBE mantis_project_table

Translation: The saved procedure DESCRIBE cannot be found.

Do you have any guidance what needs to be configured to get Mantis running with MS SQL version 2000 SP4? I couldn't find any helpful information in the online manuals. I don't mind starting from scratch, because a lot of people have tried to configure it and haven't done any documentation. So if I need to install the SQL server again, create a new database, whatever else, never mind. I just would like to get it running.
If it's not possible yet to get it running with this version of MS SQL, please let me know so I can tell the customer that he still has to wait for a while.
Thanks. Ingrid

Guest

Post by Guest »

Ingrid wrote:
APPLICATION ERROR #401
Database query failed. Error received from database was #37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Die gespeicherte Prozedur DESCRIBE konnte nicht gefunden werden. for the query: DESCRIBE mantis_project_table
The only time I can see where you could get that error message is - it's running an old mysql query trying to do an upgrade to version 0.13.

How did you create the database?

Ingrid

Post by Ingrid »

I can't really tell how the database had been created because 3 other people have tried to get it running and nothing had been documented. I was told that the sql scripts delivered with the 1.0.0.3a version created the database and the tables, but no fields within the tables. So they've tried the DTS wizard to get the data from an old my sql database into the new MS sql database, but some tables just couldn't be transferred. When trying to log on nto the system, I always got the message that the username/password is either blocked or not valid. After changing one of the php files, I got the error message shown above. But I actually really don't know which files I have to modify to get it running properly.

I don't want to search any longer what has been tested and implemented and changed. It's too time-consuming and I don't even know if it's supposed to work right now with this version of Mantis, PHP and SQL. I would like to know if there's a step-by-step guide how to implement Mantis using an MS SQL Server. It's not a problem of deleting the database and the complete installation of PHP and Mantis because it's still an empty database. Can anybody tell me if it's possible yet using the Mantis version 1.0.0rc1 with the MS sql database version 2000 SP4 and the actual version of PHP or is it still experimental and not recommended for productive environment? I'd like to have a clear statement. Thanks.

thraxisp
Developer
Posts: 509
Joined: Feb 13, 2005 10:38 pm
Location: Ottawa, Canada
Contact:

Post by thraxisp »

In 1.0.0rc1, there is a script (admin/install.php) that should create the database properly. There are still a few issues (in the tracker at http://bugs.mantisbt.org/ ) that have mostly been resolved. We're hoping to support MSSQL in this release, but need more people to test it.

oceanic
Posts: 1
Joined: May 08, 2005 11:29 pm
Location: Western Australia

Mantis 1.0.0a3 with MSSQL

Post by oceanic »

Ingrid & Andreas,

I too (regrettably) have an employer that wishes me to run Mantis on MSSQL instead of MySQL. It is possible.

I have (almost completely) successfully managed to get Mantis 1.0.0a3 working with MSSQL. The minor issues I am trying to work through at the moment are:

1. that some small SQL-based thing stops the Jpgraph graphs from working (I'm trying to find it, but it's well-hidden in the "token" section I think);

2. that changing some of the configuration settings in the "Manage Configuration > Workflow Thresholds" section does not work, because of a SQL syntax error (which should be easy to fix, I just haven't got there yet).

The main things I had to change to get things working were:

1. correct all the syntax in the "mssql.sql" build script, which (despite the name) is actually mostly MySQL syntax;

2. make one small change in the "database_api.php" file which makes Mantis use the included ADODB library for MSSQL string escaping.

Contact me if you would like some help.

Drew Robinson

thraxisp
Developer
Posts: 509
Joined: Feb 13, 2005 10:38 pm
Location: Ottawa, Canada
Contact:

Post by thraxisp »

Could you please document the issues in the tracker at http://bugs.mantisbt.org/ so that we can fix them in the source code?
--
Glenn Henshaw Logical Outcome Ltd.
Mantis developer and user w: http://www.logicaloutcome.ca

Randy
Posts: 1
Joined: Sep 30, 2005 10:18 am

Re: Mantis 1.0.0a3 with MSSQL

Post by Randy »

oceanic wrote:Ingrid & Andreas,

I have (almost completely) successfully managed to get Mantis 1.0.0a3 working with MSSQL. The minor issues I am trying to work through at the moment are:
Can you post ... how ?

R.

HLHarkins
Posts: 1
Joined: Nov 22, 2006 6:19 pm

MS SQL, PHP5, Windows IIS, and Mantis

Post by HLHarkins »

Sorry - this isn't in direct/complete response to posts above, but I've just gotten this to work and documented everything while it was semi-fresh in my head. I hope it helps!

I am a PHP Novice, spelled with a capital "N", so let that stand as my disclaimer.

I am a web application programmer specializing in ColdFusion, using Windows webservers almost exclusively and integrating with MSSQL servers for my database support.

I set out to install PHP because I needed it to run Mantis bugtracker, and I wanted to be able to use my SQL server vs. installing MySQL or other standard.

I knew the job would be dangerous when I saw the "(experimental)" next to the MSSQL database type in the Mantis adminstration install.

In any case, as a neophyte in both PHP and Mantis, I was certainly out of my element and it's taken me 2 days (of lost production on my contracts) to assemble every tibit I could find from googling my many woes in order to get this to work... but it DOES. So, here goes. THANKS to my MANY predecessors for each and every tip. I used them ALL, though nobody seemed to have a compendium of suggestions, so here are mine.

++++++++++++++++++++++++++++++++++++++++++++++++++++
Don't mock me -- if you know more than I do, please just move on!!
OR add some helpful responses, if you think I’ve gone astray
++++++++++++++++++++++++++++++++++++++++++++++++++++

Specs:
• WebServer: Microsoft Windows 2000 with SP4
o PHP 5
o IIS 5.0
o SQL Server client tools
o ColdFusion MX 7 Server

• SQL Server: Separate machine: Microsoft Windows 2000 with SP4
o SQL Server 2000

1. Install PHP manually, following the steps provided. I implemented PHP in ISAPI mode, so apparently PHP is compiled only once, on restart of IIS vs. every time a .php page is hit. So, if you make any changes to your php.ini, per the below, make sure to restart IIS when you’re done to make the settings take effect (or recompile, or whatever the proper vernacular is). I am assuming the same configs as mine, stated above!!

2. In php.ini, establish the settings that permit your DB to come into play. I used the php.ini-recommended as my template and then changed various items. For MSSQL Server users, the following were important settings:
a. Safe_mode = off
b. Magic_quotes_gpc = on
c. Magic_quotes_runtime = on
d. Magic_quotes_sybase = on
e. Doc_root = the physical location of your webserver’s root, such as j:\inetpub\wwwroot. I used no quotes around the string and no trailing slash.
f. Extension_dir = depends on your version and installation of PHP. This is the physical path to your extension directory, where the magical php_mssql.dll is located (the dll that permits the SQL Server interaction). The PHP installation instructions cautioned against installing to a location with a space in the directory name (such as Program Files), so I located my PHP directory in the root of a drive, such as X:\PHP. I couldn’t find anything that instructed whether or not to use quotes around the location, so I tried both ways and ended up using the path only, no quotes, and with a trailing “\” (something I didn’t find documented) so: extension_dir = X:\PHP\ext\ for my implementation of PHP 5
g. cgi.force_redirect = 0
h. uncomment (delete the leading “;” from) the entry extension=php_mssql.dll. THAT is what will tell PHP that you’re permitting connection to SQL server.
i. Under Modules, I have sql.safe_mode = off
j. Under [MSSQL], you can adjust the various settings to your liking. I increased the timeout parameters.
k. mssql.secure_connection = off For me, in my effort to establish Mantis with SQL Server, this setting was KEY! I’d originally turned it on, hoping to use NT Authentication automatically to connect to my SQL server (which is running on a separate server). However, that created more complexity than was useful and prevented me from using the Mantis administrative interface to send the username and password params necessary to create and administer the database tables it creates.

3. Note: Despite restarting IIS, I found that some changes seemed NOT to take effect. Despite having added my PHP installation directory to the Environmental PATH variables, per the instruction in the PHP manual (or elsewhere?? Can’t recall now where I found that tidbit), I seemed to have success with my recompilation of PHP once I put the php.ini into my Windows directory. So, though it seems a bad idea to have a duplicate, I have the php.ini in my PHP root and in my Windows directory. I assume that the Windows directory is searched first for it before the PHP directory.

4. NOW, RE: SQL SERVER – In order for PHP to connect to a SQL server DB that is on a separate server, SQL Server client tools MUST be installed on the webserver supporting PHP. There is some user documentation to suggest that you should copy the ntwdblib.dll from the SQL Server’s Windows\System32 directory into your webserver’s PHP directory. However, that assumes that your webserver already has SQL Server Client Tools installed. So, install them, if you haven’t already!

5. User documentation suggested a download of version 8 of ntwdblib.dll, since what comes with PHP5 is likely an earlier version. I implemented this updated file not only in my PHP directory but also in my webserver’s Windows/system32 directory. It was necessary to first STOP IIS because the original .dll is in use. You can download the file here: http://webzila.com/dll/1/ntwdblib.zip. One user mentions this: “The ntwdblib.dll should be version 2000.80.194.0, and not version 2000.2.8.0 that PHP 5 ships with.” Of course, then you will need to restart (IIS only? or your webserver entirely) again to get the .dll to take effect.

6. Now, that might be sufficient for most PHP-MSSQL purposes. I’m not sure. All I can say is that, for Mantis, I had to go several steps further…

7. Apparently, named pipes is the default method that PHP attempts when trying to connect to a SQL server. I took several steps to preclude this, including the following:
a. In my DB server’s SQL server, using the Client Configuration utility, I removed Pipes from the list of enabled protocols.
b. On my webserver, within the Client Configuration utility, I established an alias for my database server, using TCP/IP as the protocol
c. I followed the advice of another user and modified my webserver’s registry to include an entry for DSQUERY. Here’s a link and instructions for that: http://people.smu.edu/acambre/blog/2006 ... y+PHP.aspx
d. I explored my DB server’s “Properties” and made sure that, in the Security tab, Authentication was set to “SQL Server and Windows” – to correspond to the fact that I’d set mssql.secure_connection = Off in my php.ini
e. I found some useful background info here: http://docs.moodle.org/en/Installing_MSSQL_for_PHP that gave me ideas about what to try. I did not implement FreeTDS

8. ONE NOTE OF CAUTION: In my many-many-phased process of getting my PHP connection to SQL Server working, I have disrupted something related to JRun’s ability to handle my ColdFusion pages. I’m confident I can resolve this by tweaking a few things in IIS/PHP – but I wanted to mention this side-effect. As I said at the outset, I’m a PHP Newbie. The thrust of this documentation was really to compile the myriad pieces of information I required from all over the web to get PHP on Windows to interface in a decent manner with MSSQL server running on a separate machine. I hope this has helped you or at least given you some resources!!

timmi
Posts: 9
Joined: Feb 15, 2010 3:57 am

Re: Mantis on SQL Server 2000 from Microsoft

Post by timmi »

Hi guys!

We want to upgrade to 1.2.0 when it becomes final (already building a working mantis with 1.2.0rc2 to go live asap).

Our goal: Use MS SQL Server (2005).

We're wondering why it is still in an "(experimental)" state (admin/install.php), can anybody tell me why?

From a sentence of the homepage
MantisBT is a free popular web-based bugtracking system (feature list). It is written in the PHP scripting language and works with MySQL, MS SQL, and PostgreSQL databases and a webserver.
I would assume that MS SQL is fully supported...

Thanks for any help!


timmi

Post Reply