
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-13-2016 11:33 AM
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.
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.
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-13-2016 09:33 PM
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.
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.
2 REPLIES 2

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-14-2016 09:21 AM
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
The SQL Management Studio View editor reports an error because the field 2 had no name.
Best Regards
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-13-2016 09:33 PM
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.
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.
