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 Lansweeper Developer Administration 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,510  
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 Fonts
by  TKinnie   Go to last post Go to first unread
Last post: Today at 4:00:40 PM(UTC)
Action Schedule Reboot using AT and psshutdown with time input
by  spatchE  
Go to last post Go to first unread
Last post: 5/21/2019 5:35:46 PM(UTC)
Action Open users local Temp directory
by  studerje   Go to last post Go to first unread
Last post: 5/14/2019 7:24:24 PM(UTC)
Lansweeper CMD- CFI MS Update remover 2
by  Rodney Stowell   Go to last post Go to first unread
Last post: 4/18/2019 6:55:01 PM(UTC)
Action Skype User
by  cctech  
Go to last post Go to first unread
Last post: 4/18/2019 4:14:54 PM(UTC)
Action Email user
by  cctech   Go to last post Go to first unread
Last post: 4/18/2019 3:48:50 PM(UTC)
Lansweeper RDP to the asset
by  AZHockeyNut  
Go to last post Go to first unread
Last post: 4/9/2019 6:31:48 PM(UTC)