vtiger Open Source CRM Discussions

Call Us : +1 408-733-3229

Log in

Search this forum

FAQ

MS SQL Server

Installation |

Webmail

Forum Home -> Help - 4.x

Previous topic :: Next topic

Goto page Previous 1, 2

Bushwack Post subject: Re: MS SQL Server
Senior Member

Mon Sep 12, 2005 2:47 pm
Posts: 236

chuckcavaness wrote:

Can someone with knowledge post whether using MS SQL Server is possible, even though it may take some manual configuration?

Chuck

It should be possible for someone comfortable with mssql and php, as I suspect some code changes would be necessary. Check out my patch for Postgres support (in the code contribution forum) as it is probably the next step for database independant design.

chuckcavaness Post subject: Re: MS SQL Server

Tue Sep 13, 2005 4:33 pm
Posts: 3


Thanks for your feedback. I'll go check out the code. If we do port to MS SQL Server, I'll be sure to submit my code and documentation back to the project. chuck

dimitrisand Post subject: Re: MS SQL Server

Tue Sep 20, 2005 4:29 pm
Posts: 15


MS SQL Server is an excellent idea because I have problems with mysql encodings in greek.(sync mostly)

suttonsoft Post subject: Re: MS SQL Server

Wed Nov 30, 2005 4:49 pm
Posts: 4
Location: Charlotte, NC


I am very excited that so many people are looking for an installation option for MSSQL, as this is unfortunately a requirement for me. Is there anyone who has had success in a MSSQL install yet? thanks! -sutton

Guest Post subject: Re: MS SQL Server

Thu Jan 26, 2006 11:07 am

I am also interested in SQL Server installation and have worked on it a bit during the last 2 weeks. I cannot say that I have successfully intalled vtiger unless I have not tested everything, but I think it looks nice (see attachment) and I have successfully run the install process and load of demo data without errors.

What we can say is that most of the problems have already been listed in Bushwark post (see topic http://forums.vtiger.com/viewtopic.php?t=2597) regarding postgres support.

Hereafter are some of the changes needed:

1 - Ensure that adodb driver use FETCH_MODE_ASSOC, we need to add :

$adb->database->setFetchMode(ADODB_FETCH_ASSOC);

at the end of PearDatabase.php (after $adb->connect()).
It seems that all the other connections opened by checkConnection function will be defaulted to this mode.

2 - Have correct DateTime setting.

2.1 - we need to add 1 line in connect function of PearDatabse.php after this->database->PConnect(...) :

$this->database->query("set DATEFORMAT ymd");

2.2 - have to change the value for datetime values when populating data. I don't remember where I have done that but some of the queries look like :

insert into <table> (..,field,..) values (..,'20050501',..)

should be

insert into <table> (..,field,..) values (..,'2005-05-01',..)

3 - Numeric fields definition has to be changed in adodb/XMLDatabaseSchema.xml :

type="N" size="2.3"

should be

type="N" size="5.3"

4 - Have a correct use of IDENTITY fields :

4.1 - when inserting values we need to use SET IDENTITY_INSERT command so in SugarBean.php line 149, replace :

$adb->query($query, true);

by :

// Updated by MJS for mssql support (allow identity insert)
if(!$isUpdate)
{
$adb->query("set IDENTITY_INSERT ".$this->table_name." ON",true);
}
$adb->query($query, true);
if(!$isUpdate)
{
$adb->query("set IDENTITY_INSERT ".$this->table_name." OFF",true);
}

Same thing in module/users/Security.php line 40 for table "role".

4.2 - All the insert queries where identity column is not set, the value '' must be suppressed from the values list. Example in data/CRMEntity line 616, replace :

$sql = "insert into potstagehistory values('',".$this->id.",....

by

$sql = "insert into potstagehistory values(".$this->id.",....

5 - Some of the fieldnames are reserved word for MSSQL (user_name is a function) so we have to change in adodb/SugarBean.php, lines 124-132 :

if($isUpdate)
{
$updKeyValues = $updKeyValues.$field."=".$this->db->formatString($this->table_name,$field,from_html($this->$field,$isUpdate));
}
else
{
$insKeys = $insKeys.$field;
$insValues = $insValues.$this->db->formatString($this->table_name,$field,from_html($this->$field,$isUpdate));
}

by

if($isUpdate)
{
$updKeyValues = $updKeyValues."[".$field."]=".$this->db->formatString($this->table_name,$field,from_html($this->$field,$isUpdate));
}
else
{
$insKeys = $insKeys."[".$field."]";
$insValues = $insValues.$adb->formatString($this->table_name,$field,from_html($this->$field,$isUpdate));
}

6 - Queries with several joined tables :

select .. from <table> join <table1> join <table2> on <condition1> and <condition2>

is not allowed. We have to replace them by :

select .. from <table> join <table1> on <condition1> join <table2> on <condition2>

7 - select .. group by .. with more selected fields than fields in group by clause are not allowed. Most of these queries seems to be related to recuring events functionality. Example in modules/Activities/RenderRelatedListUI.php line 119 replace :

select .. from activity,crmentity where .. group by crmid

by

select DISTINCT .. from activity,crmentity where ..

One is more complex on line 152, replace :

select ..
from activity
inner join crmentity on activity.activityid = crmentity.crmid
inner join recurringevents on activity.activityid=recurringevents.activityid
where ..
group by crmid

by

select .., recurevents...
from activity
inner join crmentity on activity.activityid = crmentity.crmid
inner join (select distinct activityid,recurringtype,recurringid,recurringdate from recurringevents) recurevents on activity.activityid=recurevents.activityid
where ..

I think it gives the same result (to be confirmed!).

8 - Last but not the least, inserting empty values in numeric field :

insert into <table> (..,<numericfield>,..) values (..,'',..)

should be :

insert into <table> (..,<numericfield>,..) values (..,0,..)

I have chosen to add in CRMEntity before line 450 :

//updated MJS determine if field is numeric or not with $coltype
// ("D","I","N") are numeric, ("C","E","O") are not,
// "V" is numeric if uitype in (5,50,52,53,56,57)
// exceptions : tablename="contactdetails" columname="reportsto" uitype=57 ?
// "T" is numeric if uitype = 70
$coltype=substr($adb->query_result($result,$i,"typeofdata"),0,1);

and to replace line 489 by :

if($fldvalue=='')
{
if(in_array($coltype,array("D","I","N")) || ($coltype == "T" && $uitype == 70) || ($coltype == "V" && in_array($uitype,array(5,50,52,53,56,57)) && $columnname != "reportsto"))
{
$fldvalue ="0";
}
else
{
$fldvalue ="''";
}
}

This method has to be confirmed by VTiger developer team but they could be some errors in the datas of table "field".

Following these considerations, I think that some help and group work is needed with developer team to find a method to implement these changes in future vtiger development without bugging MySql implementation. Could we have Wiki pages for people who are working on Postgres or MsSql to document our changes?

Vtiger_MsSQL_Home.jpg

Description:

Vtiger home page on MS SQL server

Filesize:

208.23 KB

Viewed:

2002 Time(s)

mjsdev Post subject: Re: MS SQL Server

Thu Jan 26, 2006 11:09 am
Posts: 24
Location: Marseille (France)

Vtiger_MsSQL_Home.jpg

Description:

Filesize:

208.23 KB

Viewed:

1554 Time(s)

Display posts from previous:

	vtiger Forum Index -> Help - 4.x	All times are GMT Leave a Testimonial Goto page Previous 1, 2
Page 2 of 2

Quick Reply
If you are visually impaired or cannot otherwise read this code please contact the Administrator for help.
Enter the code exactly as you see it. The code is case sensitive and zero has a diagonal line through it.

Jump to: