Fresh/clean/empty install of the latest SuiteCRM onto Windows 2012 with a MS Windows SQL Server 2012 db server.
Outside of the installer not working out of the box due to to the use of the MYSQL ping() call (already a thread on that; simply commented it out and it worked but really should be fixed in the base product) the install went smooth and its running well.
Had an issue pop up with Project Templates. 2 different definitions of the “assigned_user_name” field is being generated in the same query and MS SQL is barfing over the same name being used twice in the query. Any suggestions?
The resulting query is:
SELECT TOP 21 *
FROM ( SELECT am_projecttemplates.id ,
am_projecttemplates.assigned_user_id ,
am_projecttemplates.name ,
am_projecttemplates.status ,
am_projecttemplates.priority ,
LTRIM(RTRIM(ISNULL(jt0.first_name, ‘’) + N’ ’ + ISNULL(jt0.last_name, ‘’))) assigned_user_name ,
jt1.user_name assigned_user_name ,
jt1.created_by assigned_user_name_owner ,
N’Users’ assigned_user_name_mod ,
am_projecttemplates.created_by ,
ROW_NUMBER() OVER ( ORDER BY am_projecttemplates.name ASC ) AS row_number
FROM am_projecttemplates
LEFT JOIN users jt0 ON am_projecttemplates.assigned_user_id = jt0.id AND jt0.deleted = 0
LEFT JOIN users jt1 ON am_projecttemplates.assigned_user_id = jt1.id AND jt1.deleted = 0 AND jt1.deleted = 0
WHERE ( ( am_projecttemplates.assigned_user_id IN ( ‘1’ )) )
AND am_projecttemplates.deleted = 0
) AS a
WHERE row_number > 0;
I’ve still been unable to track this down. Guess I’m not familiar enough with the includes and objects. Had this happen with a fresh 7.6.4 install right out of the box on MS SQL. By the time the MS SQL LimitQuery function is called the query is already wrong and invalid for MS SQL server: Notice the generation of “assigned_user_name” as a column name twice. SQL server doesn’t like that. I didn’t see anyone else having the issue though.’
the debug listing at LimitQuery is this with element [0] being an invalid SQL statement.
Array
(
[0] => SELECT am_projecttemplates.id , am_projecttemplates.assigned_user_id , am_projecttemplates.name , am_projecttemplates.status , am_projecttemplates.priority , LTRIM(RTRIM(ISNULL(jt0.first_name,’’)+’ ‘+ISNULL(jt0.last_name,’’))) assigned_user_name , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , ‘Users’ assigned_user_name_mod, am_projecttemplates.created_by FROM am_projecttemplates LEFT JOIN users jt0 ON am_projecttemplates.assigned_user_id = jt0.id AND jt0.deleted=0 LEFT JOIN users jt1 ON am_projecttemplates.assigned_user_id=jt1.id AND jt1.deleted=0
AND jt1.deleted=0 where ((am_projecttemplates.name like ‘alantest%’)) AND am_projecttemplates.deleted=0 ORDER BY am_projecttemplates.date_entered DESC
[1] => 0
[2] => 21
)
Is no one else using Project templates on MSSQL? This is a base product issue as we are doing this on a clean install with the latest 7.6.4
More research would indicate its really project template search that is broke, both the advanced and basic searches fail with this bad SQL being generated (see above)
If I manually create the detailview URL and substitute in the templateid I can view the template fine so it appears to be a search issue.
I also did the repair which didn’t solve it either.
I´m using the same version and data base, and I´m getting the same error. I cound´t see my template projects… did you solve this issue?
Thanks
no, unfortunately I have not. I started looking into it but could never track down exactly where the query is being assembled to troubleshoot it and then I got sidetracked. it’s key to our implementation though moving forward so I need to get it solved. Not happy you have it, but thrilled someone else has it as well so its not just my installation. Maybe it will get some attention now.
P 
Project Templates remain totally broke and unusable for Window’s installations running on MS SQL server. Still getting the same bad SQL generated resulting in [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The column ‘assigned_user_name’ was specified multiple times for ‘a’.
If someone could even point me in the direction of where I could look I would be more than happy to fix and share it. I assume its however the the query is being assembled
this is true on 7.7.1 btw