Skip to content
+1-888-319-3663

COMMUNITY FORUM

Converting SQL View to SQL Script

Marc asked 4 years ago
Years ago, I made several SmartLists in SLB using custom SQL views.  After upgrading to the newest release of SLB, I found there is a new data source option, “SQL Script”.  If I had this back then, I would not have needed to create the SQL views.  So, I was looking for a way to convert from using the views to SQL script so that I could remove the views from the company databases. 
I couldn’t find a way in the UI to do this.  The only apparent option was to make a new SmartList with the same name and hide the old one.  This would create problems in that the users’ favorites would all appear to disappear.
I looked into the table structure and the SLB tables are very nice and organized.  So, I found this is possible with a direct SQL update. 
First, I run this SQL statement:
UPDATE A SET
A.External_Table = 0,
A.TBLPHYSNM = ”,
A.SQL_Database_Name = ”,
A.Use_Current_Company = 0,
A.Object_type = 0,
A.TXTFIELD = ‘SQL’
FROM SLB10100 A
WHERE A.SmartList_ID = ‘<SLB SmartList ID>’
AND A.External_Table = 1
Then I can open the SmartList in SLB and replace the “SQL” text with the actual SQL code.  This has worked well in testing so far.
If you try this, be sure to backup first.  And I recommend making changes like this off hours so you have time to recover if something goes awry.
Answers
Marc answered 4 years ago
No answer needed.  I’m just sharing with the group .

If you would like to submit an answer or comment, please sign in to the eOne portal.