Tuesday 23 June 2015

ORA-39700: database must be opened with upgrade option


After RMAN restore for database to a test server appear this error, because on older server database version was 11.2.0.1.0
and on new server database version is 11.2.0.3.0, therefore we need:

1) open with upgrade option
2) upgrade database – it means run script $ORACLE_HOME/rdbms/admin/catupgrd.sql
3) shutdown clearly and startup
4) run script $ORACLE_HOME/rdbms/admin/utlrp.sql – for recompile all invalid PL/SQL packages

sqlplus / as sysdba
SQL> startup upgrade;
SQL> @/oracle/app/product/11.2.0/db/rdbms/admin/catupgrd.sql;
SQL> shutdown immediate;
SQL> startup;
SQL> @/oracle/app/product/11.2.0/db/rdbms/admin/utlrp.sql

Tuesday 9 June 2015

Download multiple SSRS reports rdl files

From google search I got two methods:
-- Change 'E:\BackupReports\'  to your correct path to download
1- T-SQL code:
--Replace NULL with keywords of the ReportManager's Report Path,  
--if reports from any specific path are to be downloaded 
DECLARE @FilterReportPath AS VARCHAR(500) = NULL  
  
--Replace NULL with the keyword matching the Report File Name, 
--if any specific reports are to be downloaded 
DECLARE @FilterReportName AS VARCHAR(500) = NULL 
  
--Replace this path with the Server Location where you want the 
--reports to be downloaded.. 
DECLARE @OutputPath AS VARCHAR(500) = 'E:\BackupReports\' 
  
--Used to prepare the dynamic query 
DECLARE @TSQL AS NVARCHAR(MAX) 
  
--Reset the OutputPath separator. 
SET @OutputPath = REPLACE(@OutputPath,'\','/') 
  
--Simple validation of OutputPath; this can be changed as per ones need. 
IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = '' 
BEGIN 
  SELECT 'Invalid Output Path' 
END 
ELSE 
BEGIN 
   --Prepare the query for download. 
   /* 
   Please note the following points - 
   1. The BCP command could be modified as per ones need. E.g. Providing UserName/Password, etc. 
   2. Please update the SSRS Report Database name. Currently, it is set to default - [ReportServer1] 
   3. The BCP does not create missing Directories. So, additional logic could be implemented to handle that. 
   4. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding.  
      It just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one.  
      However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF).  
      That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “”.  
      While it is supported, it can cause problems with the conversion to XML, so it is removed. 
   */ 
   SET @TSQL = STUFF((SELECT 
                      ';EXEC master..xp_cmdshell ''bcp " ' + 
                      ' SELECT ' + 
                      ' CONVERT(VARCHAR(MAX), ' + 
                      '       CASE ' + 
                      '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+ 
                      '         ELSE C.Content '+ 
                      '       END) ' + 
                      ' FROM ' + 
                      ' [ReportServer1].[dbo].[Catalog] CL ' + 
                      ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' + 
                      ' WHERE ' + 
                      ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x''' 
                    FROM 
                      [ReportServer1].[dbo].[Catalog] CL 
                    WHERE 
                      CL.[Type] = 2 --Report 
                      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/') 
                      AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name) 
                    FOR XML PATH('')), 1,1,'') 
   
  --SELECT 'Her is the query '+@TSQL 
   
  --Execute the Dynamic Query 
  EXEC SP_EXECUTESQL @TSQL 
END


--------------------------------------------------
2- Through this program:

 

Number of Visitors