Notification

Icon
Error

Export custom reports (SQL Server only) - Generate SQL queries to recreate custom reports on another database. Will not work on SQL Compact.

Posted: Monday, April 13, 2015 2:55:49 PM(UTC)
Filip.V

Filip.V

Member Original PosterPosts: 7
4
Like
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.

Code:
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
friedbad
#1friedbad Member Posts: 7  
posted: 4/27/2015 5:24:09 PM(UTC)
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.
Susan.A
#2Susan.A Member Administration Posts: 1,536  
posted: 4/28/2015 10:56:59 AM(UTC)
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.
Code:
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
esr
#3esr Member Alpha Tester Posts: 69  
posted: 8/13/2015 8:19:21 PM(UTC)
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.
jmje
#4jmje Member Posts: 21  
posted: 1/29/2019 8:43:13 PM(UTC)
Originally Posted by: Susan.A Go to Quoted Post
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.
Code:
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. :)

Active Discussions

Lansweeper Edit Work Time Enhancements
by  pipapo   Go to last post Go to first unread
Last post: Yesterday at 3:41:25 PM(UTC)
Lansweeper software authorization based on "name" AND "version"
by  deejay3670  
Go to last post Go to first unread
Last post: Yesterday at 2:53:28 PM(UTC)
Lansweeper HP Warranty scan - broken for some products
by  JérômeS   Go to last post Go to first unread
Last post: Yesterday at 2:34:13 PM(UTC)
Lansweeper Helpdesk Report for unresolved tickets
by  Walter  
Go to last post Go to first unread
Last post: Yesterday at 2:08:39 PM(UTC)
Lansweeper Certificate status & end dates
by  Antoine EVRARD   Go to last post Go to first unread
Last post: Yesterday at 1:01:37 PM(UTC)
Lansweeper Assign switch port to location map
by  cycleheat  
Go to last post Go to first unread
Last post: 5/26/2020 5:21:54 PM(UTC)
Lansweeper Email Link back to ticket
by  TruckDriverTurnedIT   Go to last post Go to first unread
Last post: 5/26/2020 5:20:08 PM(UTC)
Lansweeper LSAgent not Scanning or Reporting all Software
by  cycleheat  
Go to last post Go to first unread
Last post: 5/26/2020 4:15:05 PM(UTC)