Funtion STUFF in Report,return error in LS editor

[WintelESDHL]

I've write a select to create a report to get in one line per asset a concatenated result of a query, using the function STUFF.
It's fully working over SQL Management Studio.

In this case this Report should Create and Output of AssetID, AssetGroups all in one field.

138 Default group,File&Print,Nagios,Backup-HDLocal
149 Default group,Nagios,Backup-Cinta,Backup-Without-GRT
153 Default group,Citrix,Nagios,Serves-Without-Backup-allowed

This is the Select:

Select MAss.AssetID,
STUFF(
(
SELECT ','+ STAG.AssetGroup
FROM tblAssetGroupLink AS AGT
Inner Join tblAssetGroups as STAG On AGT.AssetGroupID = STAG.AssetGroupID
WHERE AGT.AssetID = MAss.AssetID
FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,1,N'')
from tblAssetGroupLink AS MAss
Group by MAss.AssetID

Error "Invalid SELECT statement. Unexpected token "." at line 9, pos 3.: Unexpected token "." at line 9, column 3".
I'm running last version of Lansweeper.

Thanks in advance.

Best Regards.
Dani Garcia.

[WintelESDHL]

Great. Thanks a million. Sort it now.
The SQL Management Studio View editor reports an error because the field 2 had no name.
Best Regards

[Susan_A · Accepted solution]

I'm seeing the same behavior, i.e. the report running in SQL Server Management Studio but not in the report builder. I'm not sure why the report builder won't run the query - the builder is a third-party component - but an easy way to resolve the issue would be to save the query in Management Studio.

If you open our report builder, just give the default report that is generated a title and hit Save & Run, a view will be generated in your SQL Server instance. You can see the name of the view in the URL of the report results. You can then overwrite the query of the view in SQL Server Management Studio with your own custom query. The report will be updated in Lansweeper as well, and should run fine.