How to Troubleshoot Connecting to the SQL Server Database Engine

Link to Microsoft site

BusinessObjects-Webi Runs Fine in Modify Mode But Retrieves No Data in View Mode

Another strange situation from Business Objects.

Just create a report with a date prompt. Check the options "with list of values" and choose "Equal" as operator. In Modify mode (Java Report Panel) your report works fine. But if you try to refresh your report in View mode you will see the "No Data To Retrieve" message.

If you analyze the SQL Scripts of both mode's, you will see that in View mode BI adds some hours (eg.4 or 8) to your current selected date prompt. Therefore no data matches to your query.

This is addressed to ADAPT01710199
See note: http://service.sap.com/sap/support/notes/1930605

For a workaround change the time setting under Tomcat as follow:



Environment: SAP Business Objects BI 4.1 SP2 Patch2

BusinessObjects-Reset Administrator Password

First download http://download.sysinternals.com/files/ProcessExplorer.zip

1. Go to the following directory: <INSTALLDIR>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dfo\

2. Copy the "BusinessObjects_Administrator_dfo.xml" to the following directory: <INSTALLDIR>\SAP BusinessObjects Enterprise XI 4.0\packages

3. Launch the Process Explorer utility, and locate the "CMS.exe" process. The SAP BusinessObjects Enterprise should be running.

4. Copy the contents of the "Command line" field.

5. Use the Central Configuration Manager (CCM) to stop the SIA. Once you have confirmed that the SIA has stopped, proceed to the next step.

6. Launch a command-line console and paste the contents from step 4. Do not press Enter !!!

7. Add "-serverconsole" to the end and press Enter. For Example

For example, note that the following command has -serverconsole appended:
"C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\cms.exe" -loggingPath "C:/Program Files (x86)/SAP BusinessObjects/SAP BusinessObjects Enterprise XI 4.0/logging/" -port 6400 -restart -dbinfo "C:/Program Files (x86)/SAP BusinessObjects/SAP BusinessObjects Enterprise XI 4.0/win64_x64/_boe_ACME.dbinfo" -noauditor -autoboot -fg -name ACME.cms -pidfile "C:/Program Files (x86)/SAP BusinessObjects/SAP BusinessObjects Enterprise XI 4.0/serverpids/ACME_ACME.CentralManagementServer.pid" -serverconsole

8. Type the following query and press Enter:

select si_id from ci_systemobjects where si_name='Administrator'

Four objects are returned. Record the SI_ID for each object. One of the entries should have the SI_ID set to 12.

9. Delete the objects by typing the following command for each object:

delete <id number>

10. Type again the following query to verify that the objects have been deleted:

select si_id from ci_systemobjects where si_name='Administrator'

No objects should be returned.

11. Type the following query:

select si_id from ci_systemobjects where si_name='BusinessObjects_Administrator_dfo.xml'

One objects is returned, record the object's SI_ID.

12. Delete the object by typing the following command:

delete <id number>

13. Type "quit" to exit the command-line console.

14. Use the CCM to start the SIA. You can now log on to the administrator account without having to provide password (leave the password field blank).

BusinessObjects-Information Design Tool Backup Project Workspace

Here is the official SAP workaround for backup IDT project workspaces. Copied from "SAP BusinessObjects BI Suite 4.0 Support Pack 8 Release Notes, note 1922675"

Information design tool: Missing documentation on backing up and restoring project workspaces.

ADAPT01530987

To back up the project resources created in the information design tool:

1. Exit the information design tool.
2. On the local file system, navigate to the folder “%USERPROFILE%\.businessobjects\bimodeler_14\”. For
example:
C:\Documents and Settings\Administrator\.businessobjects\bimodeleter_14\
3. Rename the “workspace” folder to “workspace.bak”.

To restore the project resources from the backup in the information design tool:

1. Start the information design tool. A new workspace folder is created automatically.
2. Select “File/Open Project” from the main menu.
3. In the Import Existing Projects dialog box, Select the root directory and browse to the path of the
“workspace.bak” folder created in the backup procedure.
4. Select the projects you want to restore.
5. Check the “Copy projects into workspace” check box and click Finish.

BusinessObjects-The specified DSN contains an architecture mismatch between the Driver and Application

If you have already google this error message and still not satisfied with the 64 bit driver solution you are on the right place.

With SAP BI 4.0 the new BO infrastructure is based on 64 bit technology BUT only on the server side. The client tools (universe designer, webi rich client etc.) are still 32 bit applications.

Many forums offers to create both 32 and 64 bit odbc for your file with the same name. One for client tools one for server.

OK, that's fine but did you realize that e-fashion has only one odbc and it is 32 bit, and it is still running on the server???

If e-fashion can do that why can't you? Here is the answer. I assume that you are using an Excel file as source. You created an odbc (32 bit odbc) for your excel file (on the server). Now go to the universe designer choose new connection. On the drop down box select "MS Excel 2007 ODBC Driver"

click next next finish. Enjoy it. Yes, that's all. Nothing to do else.

Tweet

BusinessObjects-Backup your BI system

Here is all the steps for your full and partial(biar) backup.

Backup your system both full and partial. Use the full backup for corrupted and not working BI systems. Use the biar backup for working proper systems.

If you confused about full and biar backup please read this article:

http://bobj.sapbiblog.com/2012/11/29/backing-up-and-restoring-sap-businessobjects/

1.- Full Backup

Step1: Backup your CMS database.

 In default the BI system comes with the default SQL 2008 Express Edition. Express edition does not support SQL Jobs. So you have to write your own backup procedure and then schedule it via windows task scheduler. Here is the SQL backup code:


USE [BOE140]

 GO

 /****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/

 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO  
 -- =============================================
 -- Author: Microsoft  -- Create date: 2010-02-06
 -- Description: Backup Databases for SQLExpress
 -- Parameter1: databaseName
 -- Parameter2: backupType F=full, D=differential, L=log
 -- Parameter3: backup file location
 -- =============================================

 CREATE PROCEDURE [dbo].[sp_BackupDatabases]            
 @databaseName sysname = null,          
 @backupType CHAR(1),          
 @backupLocation nvarchar(200)
 AS        
 SET NOCOUNT ON;                        

 DECLARE @DBs TABLE          
 (                
 ID int IDENTITY PRIMARY KEY,                
 DBNAME nvarchar(500)          
 )                        

 -- Pick out only databases which are online in case ALL databases are chosen to be backed up            
 -- If specific database is chosen to be backed up only pick that out from @DBs          

 INSERT INTO @DBs (DBNAME)          
 SELECT Name FROM master.sys.databases          
 where state=0          
-- CMS AND AUDIT DATABASE NAMES
AND name in ('BOE140','BOE140_Audit')                      

 -- Declare variables          

 DECLARE @BackupName varchar(100)          
 DECLARE @BackupFile varchar(100)          
 DECLARE @DBNAME varchar(300)          
 DECLARE @sqlCommand NVARCHAR(1000)        
 DECLARE @dateTime NVARCHAR(20)          
 DECLARE @Loop int                                                    

 -- Loop through the databases one by one          
 SELECT @Loop = min(ID) FROM @DBs      
 WHILE @Loop IS NOT NULL    

 BEGIN
 -- Database Names have to be in [dbname] format since some have - or _ in their name    
 SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
 -- Set the current date and time n yyyyhhmmss format    
 SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

 -- Create backup filename in path\filename.extension format for full,diff and log backups    
 IF @backupType = 'F'          
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'    
 ELSE IF @backupType = 'D'          
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'    
 ELSE IF @backupType = 'L'          
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

 -- Provide the backup a name for storing in the media    
 IF @backupType = 'F'          
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime    
 IF @backupType = 'D'          
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime    
 IF @backupType = 'L'          
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

 -- Generate the dynamic SQL command to be executed        
 IF @backupType = 'F'                  
 BEGIN              
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'                
 END      
 IF @backupType = 'D'                
 BEGIN              
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= '''+@BackupName+''', NOSKIP, NOFORMAT'                        
 END      
 IF @backupType = 'L'                  
 BEGIN              
 SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'                        
 END

 -- Execute the generated SQL command      
 EXEC(@sqlCommand)

 -- Goto the next database
 SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

 END

BusinessObjects-Repository Scan and Repair

A clean system is the best starting point for your company. BusinessObjects has a repair tool for your environment. With reposcan you can check the inconsistency between your CMS database and your file system.

For example "An error occurred while searching: The property with ID SI_CUID does not exist in the object" error on search can occur due to file system and repository inconsistency.

You can find the reposcan.exe file under "<INSTALLDIR>\SAP BusinessObjects Enterprise XI 4.0\win64_x64\". Reposcan has a lot of parameters therefore i prefer to create a batch file with the parameters i needed.

Here is the repo_repair.bat file code:

reposcan.exe -dbdriver sqlserverdatabasesubsystem -connect "UID=XXX1;PWD=XXX2;DSN=XXX3;HOSTNAME=XXX4;PORT=XXX5" -dbkey XXX6 -inputfrsdir "C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\FileStore\Input" -outputfrsdir "C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\FileStore\Output"

For this example CMS repository database is SQL Server.

XXX1= Your SQL username for logging CMS repository database.
XXX2= Your password of the SQL user.
XXX3= ODBC DSN name of the CMS repository database.
XXX4= SQL Server hostname
XXX5= SQL Server portname
XXX6= Cluster key of your CMS

To find your cluster key open CCM. Right click on SIA and choose properties.

Now go to the configuration tab. At the bottom you will see your CMS Cluster Key.

With this information your command should look like this;

reposcan.exe -dbdriver sqlserverdatabasesubsystem -connect "UID=sqluser;PWD=sqlpass;DSN=bo_cms;HOSTNAME=server_name\instance_name;PORT=1433" -dbkey [[XX1g0FVBi9VFBiCIAnAQ]]  -inputfrsdir "C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\FileStore\Input" -outputfrsdir "C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\FileStore\Output"

After you run your batch file it creates a log file under "<INSTALLDIR>\SAP BusinessObjects Enterprise XI 4.0\reposcan". Open the xml files with your browser and you fill see the results.

List of inconsistency, by type, which can be found by RDT.

Inconsistencies between the CMS and the FRS:

• The object exists in the CMS database, but there is no corresponding file in the FRS.
• The file exists in the FRS, but there is no corresponding file in the CMS database.
• The size of the file does not match the InfoObject file size.
• The FRS folder is empty.

Inconsistencies in the CMS metadata:

• The object has a missing or invalid Parent Object ID.
• The object has a missing or invalid Owner Object ID.
• The object has a missing or invalid Submitter Object ID.
• The object's last successful instance is missing or invalid.
• The object references a calendar that doesn't exist.
• The preferred server does not exist.
• The event or events that this object is waiting on does not exist.
• This object triggers an event that does not exist.
• Orphaned Access Control entry.
• The preferred server does not exist.
• A specific user account has multiple favorites folders.

More information : http://wiki.sdn.sap.com/wiki/display/BOBJ/How+to+use+Repository+Diagnostic+Tool

For other databases and reposcan parameters refer to xi4_bip_repository_diagnostic_tool_en.pdf on SAP market.

Tweet