cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Filip_V
Lansweeper Alumni
Generate SQL queries to recreate your custom reports on another database. This code is designed for SQL Server and will not work on SQL Compact.

SELECT 'INSERT INTO [tsysreports]
([Reportquery]
,[Reporttitle]
,[Sendmail]
,[Mailgroup]
,[Created]
,[LastChanged]
,[CreatedBy]
,[ChangedBy]
,[LastRun]
,[Permissions]
,[Total])
VALUES
('''+Reportquery+''',
'''+Reporttitle+''',
'+coalesce(''''+Convert(NVARCHAR, Sendmail)+'''', 'null')+',
'+coalesce(''''+Mailgroup+'''', 'null')+',
'+coalesce(''''+Convert(NVARCHAR, Created)+'''', 'null')+',
'+coalesce(''''+Convert(NVARCHAR, LastChanged)+'''', 'null')+',
'+coalesce(''''+CreatedBy+'''', 'null')+',
'+coalesce(''''+ChangedBy+'''', 'null')+',
'+coalesce(''''+Convert(NVARCHAR, LastRun)+'''', 'null')+',
'+coalesce(''''+Permissions+'''', 'null')+',
'+coalesce(''''+Convert(NVARCHAR, Total)+'''', 'null')+'
)' TsysReports,
OBJECT_DEFINITION (OBJECT_ID(Reportquery)) [View]
FROM tsysreports
WHERE Created IS NOT NULL
6 REPLIES 6
Alig26
Engaged Sweeper II

How would we import this into the new Database?

Mercedes_O
Community Manager
Community Manager

Hello, due to the age of this post I recommend searching through our articles here  Reporting - Lansweeper Community   or request help with a specific report here  in a forum post 

esr
Champion Sweeper
Thank you very much-
This short bit of code is worth it's weight in gold!

I'd go so far as to suggest that it should be a default report in Lansweeper.
Susan_A
Lansweeper Alumni
A sample report can be seen below. It lists the display names of custom reports, as well as the scripts used to create the report views.
Select Top 1000000 tsysreports.Reporttitle As Title,
OBJECT_DEFINITION(Object_Id(tsysreports.Reportquery)) ViewScript
From tsysreports
Where tsysreports.Created Is Not Null
Order By Title
jmje
Engaged Sweeper III
Susan.A wrote:
A sample report can be seen below. It lists the display names of custom reports, as well as the scripts used to create the report views.
Select Top 1000000 tsysreports.Reporttitle As Title,
OBJECT_DEFINITION(Object_Id(tsysreports.Reportquery)) ViewScript
From tsysreports
Where tsysreports.Created Is Not Null
Order By Title


Best report ever made. This has saved me so much time. 🙂
friedbad
Engaged Sweeper II
Is there a way to query just the Report Title and Query off in a report? I'm looking for the title and actual sql query (not the webxxxx name) text as a backup for all reports.