Folders disappear after a refresh

06 Jun 2022 14222 views 0 minutes to read Contributors

This article does not apply on version 0.8.11.24 when using the DatabaseStorage and FileStorage option.
When using tempDB storage option it will create the objects in the extended properties of the TempDB

To create folders in the object tree SQLTreeo Add-in makes use of stored procedures to store the information in the user-defined database(s) or the  Tempdb database for dynamic folders when the database storage option is active.

The user or login accessing the SQL Server instance requires several priveleges to create the folders, the user needs access to the database and permission to make use of the Stored procedure sys.sp_addextendedproperty.

When the SQLTreeo SSMS Add-in is installed (Trial period) the following script can be run against the SQL Server instance to verify if the user/login has the required permissions/priveleges to create any folders.

		
			
T-SQL
			
Transact-SQL
			

			
/* SQLTreeo SSMS Add-in script to check folder creation
Author: SQLTreeo
Execution path:
1. Open SQL Server Management Studio.
2. Open a new query window and select an user-defined database 
3. Copy-paste Batch 1,2,3 to the query window.
4. Execute the 3 batches separately in sequence (first batch 1, after success batch 2, then run batch 3 to retrieve the results).
5. Check if batch 3 returns 2 rows where the value column is identical to the @value parameter value.
6. Refresh your object explorer, database objects and SQL Server Agent jobs objects are grouped in 1 folder each.

7. Repeat step 2 to 6 again using the TempDB this time.
8. If step 1-7 return without any errors and returning 2 rows this means you have sufficient permissions to create static/dynamic folders in your SQL Server instance.
9. If step 1-7 returns any error this means you do not have enough permissions to create folders and need to contact your Database Administrator to elevate your user/login permissions to be able to execute the sys.sp_addextendedproperty procedure.



*/
--Batch 1 create a folder in the Databases object
BEGIN
	DECLARE @extended_property_name varchar(512) = N'SQLTreeo|~'+@@servername+'|~Global|~Database|~SQLTreeo Test'
	EXEC sys.sp_addextendedproperty @name=@extended_property_name, @value=N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfFolderPropertiesRulesRule>
  <FolderPropertiesRulesRule Type="SQL">%</FolderPropertiesRulesRule>
</ArrayOfFolderPropertiesRulesRule>'
END

--Batch 2 create a folder in the SQL Server agent jobs object
BEGIN
	DECLARE @extended_property_name varchar(512) = N'SQLTreeo|~'+@@servername+'|~Global|~Job|~SQLTreeo Test'
	EXEC sys.sp_addextendedproperty @name=@extended_property_name, @value=N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfFolderPropertiesRulesRule>
  <FolderPropertiesRulesRule Type="SQL">%</FolderPropertiesRulesRule>
</ArrayOfFolderPropertiesRulesRule>'
END

--Batch 3 check if the extended properties are present in the database.
SELECT ep.name, ep.value FROM sys.extended_properties ep WHERE ep.name LIKE 'SQLTreeo|~'+@@servername+'|~Global|~Job|~%'
SELECT ep.name, ep.value FROM sys.extended_properties ep WHERE ep.name LIKE 'SQLTreeo|~'+@@servername+'|~Global|~Database|~%'
			

				

					

				

					
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
				
						
/* SQLTreeo SSMS Add-in script to check folder creation
Author: SQLTreeo
Execution path:
1. Open SQL Server Management Studio.
2. Open a new query window and select an user-defined database 
3. Copy-paste Batch 1,2,3 to the query window.
4. Execute the 3 batches separately in sequence (first batch 1, after success batch 2, then run batch 3 to retrieve the results).
5. Check if batch 3 returns 2 rows where the value column is identical to the @value parameter value.
6. Refresh your object explorer, database objects and SQL Server Agent jobs objects are grouped in 1 folder each.
 
7. Repeat step 2 to 6 again using the TempDB this time.
8. If step 1-7 return without any errors and returning 2 rows this means you have sufficient permissions to create static/dynamic folders in your SQL Server instance.
9. If step 1-7 returns any error this means you do not have enough permissions to create folders and need to contact your Database Administrator to elevate your user/login permissions to be able to execute the sys.sp_addextendedproperty procedure.
 
 
 
*/
--Batch 1 create a folder in the Databases object
BEGIN
	DECLARE @extended_property_name varchar(512) = N'SQLTreeo|~'+@@servername+'|~Global|~Database|~SQLTreeo Test'
	EXEC sys.sp_addextendedproperty @name=@extended_property_name, @value=N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfFolderPropertiesRulesRule>
  <FolderPropertiesRulesRule Type="SQL">%</FolderPropertiesRulesRule>
</ArrayOfFolderPropertiesRulesRule>'
END
 
--Batch 2 create a folder in the SQL Server agent jobs object
BEGIN
	DECLARE @extended_property_name varchar(512) = N'SQLTreeo|~'+@@servername+'|~Global|~Job|~SQLTreeo Test'
	EXEC sys.sp_addextendedproperty @name=@extended_property_name, @value=N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfFolderPropertiesRulesRule>
  <FolderPropertiesRulesRule Type="SQL">%</FolderPropertiesRulesRule>
</ArrayOfFolderPropertiesRulesRule>'
END
 
--Batch 3 check if the extended properties are present in the database.
SELECT ep.name, ep.value FROM sys.extended_properties ep WHERE ep.name LIKE 'SQLTreeo|~'+@@servername+'|~Global|~Job|~%'
SELECT ep.name, ep.value FROM sys.extended_properties ep WHERE ep.name LIKE 'SQLTreeo|~'+@@servername+'|~Global|~Database|~%'