How to fix Business Objects “Exception: CS, Job already in use”

Error Message: “Exception: CS, Job already in use”

The error message was received when data analyst ran a “Yearly” report

bo cs job already in use

 

The explanation I found. The BusinessObjects Desktop Intelligence client is connecting to the server using an HTTP protocol which uses the network settings in the Internet Explorer.

Internet Explorer IE6 by default set to 60 min time for send/receive timeout

Internet explorer IE7+ by default send/receive timeout set to 30 sec. Running “Yearly” report exceeded the threshold. To fix we can modify the registry manually, ReceiveTimeout DWORD value with a data value of (<number of seconds>)*1000.

Reference http://support.microsoft.com/kb/181050

 

Resolving DA0003 error

 

  1. Download registry patch fix IEFix.reg here
  2. Double click on file and “Run” to install the patch
  3. You will get a security notice about installing the update, click the required buttons to allow changes to the computer
  4. After installation complete you get a message indicating that your registry has been updated. Click the OK button to finish
  5. Restart the PC
  6. Also double check your universe’s connection parameters, and the configuration parameters (screen 3/4). Make sure you have at least the following: Pool timeout of 10 minutes, array fetch size: 10; Array bind size: 5; Login Timeout: 60 minutes.

 

SQL Saturday Melbourne

April-5th saw the first SQL Saturday in Melbourne. With more than 300 people registered and even more attended, international and local speakers and exhibitors it was really good staged and organised event for SQL Server community in Melbourne.

Here is the schedule of the event. There was a good mix of topics for all skill levels.

20 sessions on SQL Server 2014, BI, Data Warehousing, DBA, Database/Application Development and Database Administration.

 

Start Time

Keynote & Sponsor Sessions – Room: H.116

Track 1 – Room: H.125

Track 2 – Room: H.126

Track 3 – Room: H.235

Track 4 – Room: H.237

Track 5 – Room: H.238

08:45 AM Mike Ward

Keynote –

Level: Non-Technical

         
10:00 AM   Darren Gosbell

Drop your DAX

Level: Intermediate

Peter Myers

Introduction to SQL Server Data Mining

Level: Intermediate

Grant Paisley

Kick-ass Ad-hoc Cube Browser build in SSRS 2014

Level: Intermediate

Peter Ward

Hybrid Cloud Scenarios with SQL Server

Level: Intermediate

Shehap El-Nagar

T-SQL Performance Guidelines for better DB stress powers

Level: Advanced

11:30 AM   Henry Rooney

I have the data now what!

Level: Beginner

Julie Koesmarno

Building Your Myth Busting Lab With Power BI

Level: Beginner

Mohamed Kabiruddin

Agile BI with SQL Server 2014 and TFS 2013

Level: Intermediate

Matthew Winter

Introduction to SQL Server 2012 Parallel Data Warehouse

Level: Beginner

Greg Low

Why compression matters so much for SQL Server and BI

Level: Intermediate

01:15 PM   SQL Server MVPs

MVP Round Table Discussion

Level: Beginner

Peter Avenant

VARIGENCE AUSTRALIA – Build an Integrated Metadata Framework

Level: Beginner

     
02:15 PM   Paul te Braak

Analytical Data Mining – Using Data Mining to Understand Data

Level: Intermediate

Rod Colledge

Hour of Power BI

Level: Beginner

Warwick Rudd

Performance Eye for the SQL Guy

Level: Beginner

Konstantin Khomyakov

Data Quality Services (Deep dive)

Level: Intermediate

Rob Farley

The Incredible Shrinking Execution Plan

Level: Intermediate

03:45 PM   Rolf Tesmer

Leveraging SQL Spatial Analytics for Making Business Decisions

Level: Intermediate

Craig Bryden

Introducing Microsoft Power Query

Level: Intermediate

Victor Isakov

Understanding SQL Server 2014’s In-Memory OLTP Table Technology

Level: Intermediate

Martin Cairney

Automating Deployments with Powershell

Level: Advanced

Martin Catherall

Event notifications for the proactive DBA

Level: Intermediate

 

 

SSIS “Multiple-step OLE DB operation generated errors”

Product: SQL Server Integration Services SSIS

Package description: Processing text files by importing them into database. The final step in the process was to execute stored procedure to set various flags in the database for all file names processed.

 

SSIS control flow extract

 

I used “for each loop container” with ADO enumerator and Execute SQL task with OLE DB type connection.

For each loop container with ADO enumerator

 

SQL statement – direct input, stored procedure with 1 parameter where parameter mapped to the proper variable name & data type.

Problem:

Error: [Execute SQL Task] Error: Executing the query “EXEC dbo.StoredProcedire ?” failed with the following error: “Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.” Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

 

Possible Solution

In order for SSIS to properly map the query parameter to the variable, it needs the correct naming convention.  In my case, I’m using an OLEDB connection type so the first Parameter Name in the list it’s looking for is a 0

My confusion came that the parameter I used in SQL Task was assigned in For each loop container and it had index of 1.

So the set up should be like below to avoid any errors

 

Execute SQL Task parameter mapping

For Each Loop Variable Mappings

 

Here is the link from Microsoft technet site: http://technet.microsoft.com/en-us/library/ms140355.aspx

The parameter markers that you use in parameterized SQL statements depend on the connection type that the Execute SQL task uses.

Connection type Parameter marker
ADO ?
ADO.NET and SQLMOBILE @<parameter name>
ODBC ?
EXCEL and OLE DB ?

Parameter names that you use depend on the connection type that the Execute SQL task uses.

Connection type Parameter name
ADO Param1, Param2, …
ADO.NET and SQLMOBILE @<parameter name>
ODBC 1, 2, 3, …
EXCEL and OLE DB 0, 1, 2, 3, …

 

Other reasons this particular error could come up:

  1. Parameter data type is set incorrectly comparing with the datatypes of the parameters in the store procedure in the database
  2. Number of ? passed in the store procedure in Execute SQL Task don’t match with the number of parameters in the store procedure in the database
  3. Number of query parameters don’t match the number of variables mapped
  4. Order of parameters don’t match order of the variables

Business Objects – Report Conversion Tool

We all know that Business Objects Desktop Intelligence is no more part of SAP BO 4.0 release and support of DeskI would end in 2015. Even though deski is back in SAP BO 4.1 mainly to support migration of Deski reports to the new platform we have a need to migrate Deski report to Webi.

How to do it? Deski reports can be converted to Interactive Analysis Webi reports with Report conversion tool.

 

What is Report Conversion Tool?

The Report Conversion Tool converts SAP Business Objects Desktop Intelligence XI R2 and XI 3.0 reports to Interactive Analysis XI 4.x format and publishes the converted reports to the XI 4.x CMS.

It retrieves reports from the source “Central Management Server” (CMS) into the Public, Favorites or Inbox folders. After conversion, you can publish to the same folder as the original Desktop Intelligence report or to a different folder.

The RCT tool works in 2 modes

  • “Connected” mode, the Report Conversion Tool is connected to a CMS.
  • In “Standalone” mode, the Report Conversion Tool is not connected to a CMS and no security is enforced. You can work with local, unsecured documents and universes only
report conversion tool step1

 

Drawbacks:

The tool does not convert all Desktop Intelligence features and reports. The level of conversion depends on the features in the original report. Some features prevent the report from being converted. Other features are modified, re-implemented, or removed by the tool during conversion.

The tool assigns one of three statuses to each report:

  • Fully Converted
  • Partially Converted
  • Not Converted

As Report Conversion Tool allows auditing of your converted reports, this enables you to identify reports that cannot be fully converted by the tool and helps you understand reason for same.

 

Some features not converted

  • Documents based on freehand SQL or stored procedures
  • Some objects eg sections, headers cannot be hidden in Web Intelligence documents
  • Page setup options eg page order, scale, Fit to N pages
  • Some filters on objects, folded breaks do not have the same behavior in Webi
  • Global filter formulas are not migrated into a Web Intelligence report filter
  • VBA automation

PS. Also the tool have some Java incompatibility problems when run on Windows 7. Running the tool from Business Object Enterprise server Windows 2008R2 was not an issue. 

 

Report conversion Tool Errors

Below are some of the error messages you may receive while running the conversion tool. I found compiled table with errors here

 

Status Message Possible Solution

 

Fully Converted   Report Converted Successfully – Please check formatting and verify contents
Not Converted .wid document could not be generated due to unknown reason Possible permission issue
Not Converted null, Universe not found Make sure the conversion is not running on your desktop. The report should be migrated to a folder within Business Objects.
Not Converted Unsupported Data Provider  
Not Converted Unsupported user object  
Partially Converted A formula from a Desktop Intelligence block filter cannot be migrated into a Web Intelligence block filter In DeskI, on the “Format filters” panel, you can drag and drop filters among multiple blocks or make it as global filter.In WebI, during the conversion, complex block and global filter could be removed in some cases. Regarding the usability, in WebI, you cannot drag and drop filters among multiple blocks and global, you need to recreate filter for different blocks manually. Another approach is to use input control, which provides flexibility to choose the range of filter.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Across options for crosstabs are not available in Web Intelligence 1) This may be happening because of a footer across the page. Try removing the page footer on the Format -> Table dialog.2) In the original Deski report, go to the cross tab format and uncheck show footer under across edge display. Then re-convert to Webi. Open the new Webi report, bring the properties up, uncheck show footer, then check show footer and then a column will appear on the far side, put your syntax or object back into the column and then it will be the same as the original Deski.
Partially Converted Block filters on measures do not have the same behavior in Web Intelligence DeskI can filter a measure in a block.In WebI: During conversion the filter is removed. You may add the filter after conversion, or use input control to apply the filter in a different way.Http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Blocks cannot be folded in Web Intelligence In DeskI: You may fold/unfold tables, sections and breaks. In WebI: To fold/unfold vertically is delivered starting from XI3.1 SP2. After conversion, all the report elements are unfolded. In BI4.0, the feature has been enhanced to fold horizontally as well as vertically on Sections, Table and Breaks. Furthermore, you can fold/unfold over the web which is not possible in DeskI.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Breaks cannot be folded in Web Intelligence In DeskI: You may fold/unfold tables, sections and breaks. In WebI: To fold/unfold vertically is delivered starting from XI3.1 SP2. After conversion, all the report elements are unfolded. In BI4.0, the feature has been enhanced to fold horizontally as well as vertically on Sections, Table and Breaks. Furthermore, you can fold/unfold over the web which is not possible in DeskI.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Breaks in Web Intelligence can only be set on objects present in the block In DeskI: You can break a block on a column which is not part of the block.In WebI: The break is removed during the conversion. In WebI, a workaround is to create a section with the column which is not part of the block, then remove the section label.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Breaks in Web Intelligence can only be set on one dimension In DeskI: When defining a break, you can apply breaks of multiple columns within one single level.In WebI: During conversion, the setting is removed. In WebI, this can be done by using formatting options. For examples, you can firstly add two breaks, in the “Manage breaks” window, enable the break header and foot for one break, and disable them for the other.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Conditional or permanent Hide Footer does not exist in Web Intelligence  
Partially Converted Conditional or Permanent Hide Block not available in Web Intelligence  
Partially Converted Conditional or Permanent Hide Cell does not exist in Web Intelligence In DeskI: This enables hiding content based on thresholds.In WebI: On XI3.1, there is workaround that columns can be hidden by using Alerters, when condition true (set always true) then apply formula to swap object in that same column. BI 4.0 release provides the conditional hiding functionality.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Conditional or permanent Hide Header not available in Web Intelligence  
Partially Converted Detail variables have no associated dimension in Web Intelligence  
Partially Converted Function not available in Web Intelligence DeskI has functions which are not corresponding to functions in WebI, including: ApplicationValue, BlockNumber, CurrentPage, GetProfileNumber, GetProfileString, Hyperlink, OLAPQueryDescription, PageInSection. In WebI: During conversion, these functions are replaced with RepFormula(“original_syntax”). Besides, the function CountAll is changed to WebI syntex as Count([object_name]; IncludeEmpty; All); and the function “MultiCube” is renamed to “ForceMerge”.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Keyword not available in Web Intelligence  
Partially Converted Objects cannot be hidden in Web Intelligence documents In DeskI: This enables hiding content based on thresholds. In WebI: On XI3.1, there is workaround that columns can be hidden by using Alerters, when condition true (set always true) then apply formula to swap object in that same column. BI 4.0 release provides the conditional hiding functionality.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Page break footer before page break does not exist in Web Intelligence In DeskI: You can define the Page break header/footer.In WebI: During conversion, the setting is removed.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Page break header after page break is not available in Web Intelligence In DeskI: You can define the Page break header/footer.In WebI: During conversion, the setting is removed.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Page setup options are not implemented in Web Intelligence (First page number, page order, scale, Fit to N pages) In DeskI: You may setup the page options for each document. In WebI: During conversion, original page setup options are lost and default Web Intelligence page options are appliedhttp://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Sorts on query objects are not supported In DeskI: You can apply sort at the query level. WebI doesn’t apply sort at the query level, during the conversion the sort on query is removed. Anyway, you can apply the sort at the report level.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted The formula of a global filter in Desktop Intelligence cannot be migrated into a Web Intelligence report filter In DeskI: On the “Format filters” panel, you can drag and drop filters among multiple blocks or make it as global filter. During the conversion, complex block and global filter could be removed in some cases. In WebI: You cannot drag and drop filters among multiple blocks and globale, you need to recreate filter for different blocks manually. Another approach is to use input control, which provides flexibility to choose the range of filter.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence
Partially Converted Unsupported ‘Do not regenerate SQL’ option  
Partially Converted Unsupported ‘Do not retrieve data’ option In DeskI: This helps you to build the report structure without retrieving the data. The option is lost during conversion. In WebI: You can accomplish the behavior in a different approach. Beside the “Close” button on the Edit query panel, there is an arrow, click on the arrow, you will find two options: one is “Apply changes and close”, the other is “Revert changes and close”. If you choose the first one, you go to the edit report mode, and you can build the report structure without retrieving the data.http://wiki.sdn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence

 

Business Objects Query Builder

Query Builder is a web application to query Business Objects repository. It provides information which can’t be found in Central Management Console (CMC).

BusinessObjects deployment repository made up of set of tables to hold the information about the BI content such as universes, reports, users, schedules, etc. These tables are encrypted in such a way that the information stored in these tables cannot be readable using conventional SQL query tools.

Query Builder gives the opportunity for the Admin/developer to query the metadata stored in the repository.

Why you may ask, because users, auditors may ask some questions with data not ready available. Like what reports were updated since start of the year, categorise reports by kind, number and names of recurring reports etc

How to access Query Builder

To access the Query Builder, in the Internet browser of your choice type the following URL:

http://[BO SERVER]:[PORT]/AdminTools/querybuilder/ie.jsp

Access Query Builder screen

 

Log on as an Administrator to get full access to all the repository objects.

 

Query Business Object repository

 

Query Builder Objects

 There are 3 main objects:

  1. CI_INFOOBJECTS: Covers folders, reports and other content consumed by the end user
  2. CI_SYSTEMOBJECTS: Covers servers, connections, users, and user groups.
  3. CI_APPOBJECTS: Covers BusinessObjects Enterprise applications including InfoView, Desktop Intelligence etc and also objects used by documents such as Universes

 

Query Bulider Functions and operators

  • COUNT – Retrieves the number of distinct values of a property
  • TOP – specifies the maximum number of object to be returned

Operators inlcude: =,!=, >, >=, <, <= , IN, LIKE, BETWEEN, ALL

 

Simple queries Object type

1. CI_INFOOBJECTS

BO Repository System Information

 SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=4

Full Client and webi reports in the repository starting with the word “Audit” which are scheduled

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND IN ('WEBI' ,'FULLCLIENT') AND SI_NAME LIKE ‘Audit%’ AND SI_RUNNABLE_OBJECT=1

Reports updated since 1th July 2013

SELECT SI_NAME, SI_UPDATE_TS FROM CI_INFOOBJECTS WHERE SI_KIND = 'Webi' AND SI_UPDATE_TS > '2013.07.01'

All Full Client reports that use a specific universe

SELECT SI_ID, SI_NAME, SI_WEBI, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where PARENTS ("SI_NAME = 'Webi-Universe'", "SI_NAME = 'Universe Name'")

List of all the public folders (Non System Folders)

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=23 AND SI_NAME!='REPORT CONVERSION TOOL'  AND SI_NAME!= 'ADMINISTRATION TOOLS' AND SI_NAME!= 'AUDITOR'

List all the WebI reports with prompts

SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS, SI_PROCESSINFO.SI_WEBI_PROMPTS, SI_PROCESSINFO.SI_FILES, SI_PROCESSINFO.SI_PROMPTS FROM CI_INFOOBJECTS WHERE  SI_KIND = 'WEBI' and SI_INSTANCE = 0 and SI_PROCESSINFO.SI_HAS_PROMPTS=1

Which Reports Use my_Universe

SELECT SI_ID, SI_NAME, SI_UNIVERSE FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where PARENTS("SI_NAME='Webi-Universe'","SI_NAME ='Universe Name'")
and SI_KIND = 'Webi' and si_instance = 0

List of available categories

SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=45

 

2. CI_APPOBJECTS

Total Count of univeses in the deployemnt

SELECT COUNT (SI_ID) FROM CI_APPOBJECTS WHERE SI_KIND = 'UNIVERSE'

List the Universes associated with a particular Data connection

Select SI_ID,SI_NAME,SI_KIND From CI_APPOBJECTS where CHILDREN("SI_NAME='DataConnection-Universe'","SI_NAME='efashion-webi'")

List of all your universes updated since some date

SELECT SI_NAME, SI_REVISIONNUM, SI_UPDATE_TS   FROM CI_APPOBJECTS WHERE SI_KIND='UNIVERSE' AND SI_UPDATE_TS > '2013.07.01'

Count of reports per Universe

SELECT SI_NAME, SI_WEBI FROM CI_APPOBJECTS WHERE SI_KIND='Universe' AND SI_WEBI.SI_TOTAL > 0

 

3. CI_SYSTEMOBJECTS

Business Objects File Repository Server Information

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'SERVER' AND SI_NAME LIKE '%FILEREPOSITORY%'

List all the servers and their status with server category

SELECT SI_NAME, SI_SERVER_IS_ALIVE, SI_DISABLED, SI_SERVER_KIND, SI_FRIENDLY_NAME, SI_EXPECTED_RUN_STATE FROM CI_SYSTEMOBJECTS WHERE SI_KIND='SERVER'

         Possible values for SI_EXPECTED_RUN_STATE object

  • -1:  Server is currently in an invalid state due to a configuration error.
  • 0:  The expected state of the server is stopped.
  • 1:  The expected state of the server is running.
  • 2:  The expected state of the server is restarting.
  • 3:  The server is not being managed by the Server Intelligence Agent.
  • 4:  The expected state of the server is immediate shutdown.

Users in specific user group

SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='ADMINISTRATORS'")

List all the Users created between a date ranges

SELECT SI_ID,SI_NAME,  SI_CREATION_TIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'User' AND SI_CREATION_TIME BETWEEN '2013.11.01' AND '2013.11.30'

 Users member of more number of groups

SELECT SI_ID, SI_NAME, SI_USERGROUPS FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'USER' AND SI_USERGROUPS >= ALL SI_USERGROUPS

List of available Calendars 

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_PARENTID=22

Count total number of connection on a particular day

SELECT COUNT (SI_CREATION_TIME) FROM CI_SYSTEMOBJECTS WHERE SI_LASTLOGONTIME > '2013.10.15.00.00.01' AND SI_KIND = 'Connection'

Total number of unique users logged in to the system on a particular day

SELECT COUNT(SI_NAME)  FROM CI_SYSTEMOBJECTS WHERE SI_LASTLOGONTIME> '2013.10.15.00.00.01' AND SI_KIND = 'Connection'

 

NOTE:

  • Default limit for returning objects is 1000 objects. In order to get more than 1000 objects use ‘Top N’ function before the column listing in the query.
  • No sub queries allowed
  • The order of columns in the SELECT clause has no impact as the results will be rendered in its own order

System.OutOfMemoryException Error in MS Reporting Services

A client was trying to view MS SQL Reportign Services report in the IE browser.

I received the error message looking like this:

reporting services error - outofmemory

That was not much help.

I logged onto my SQL Server 2008R2 and looked in Event Log and I found the following error message:

Exception information:

Exception type: OutOfMemoryException
Exception message: Exception of type ‘System.OutOfMemoryException’ was thrown

Reporting Service was not in running state either

 

Here what I found Microsoft website MS Knowledge Base Article 909678

You may receive the “System.OutOfMemoryException” error message when you use SQL Server Reporting Services

To resolve this issue, use one of the following methods.

Method 1

Add sufficient physical memory to the computer.

Method 2

Schedule reports to run at off-hours when memory constraints are lower.

Method 3

Adjust the MemoryLimit setting accordingly.

Note: When you render a report through the Reporting Services Web service, the Reporting Services Web service obtains the MemoryLimit setting from the Machine.config file. However, a scheduled report is rendered by the Report Server Windows service. The Report Server Windows service obtains the MemoryLimit setting from the RSReportServer.config file.

Method 4

Upgrade to a 64-bit version of Microsoft SQL Server 2005 Reporting Services to allow Reporting Services to utilize more memory.

Method 5

Redesign the report. To do this, use one of the following methods.

Redesign the report queries. You can reduce memory consumption by redesigning the report queries in the following ways:

Return less data in the report queries.

Use a better restriction on the WHERE clause of the report queries.

Move complex aggregations to the data source.

Method B

Export the report to a different format. You can reduce memory consumption by using a different format to display the report.

Method C

Simplify the report design.

You can reduce memory consumption by simplifying the report design in the following ways:

Include fewer data regions or controls in the report.

Use a drill through report to display details.

 

Here my Action Plan

1. Configure Max server memory for SQL Server

 

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'max server memory (MB)', 2048

RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

Note: Only 4GB RAM was available

 

2. Set working set memory in reportsevice config file

Reporting Services can use all available memory, to override default behaviour you need to configure an upper limit on the total amount of memory resources that are allocated to Reporting Services server applications.

WorkingSetMaximum and WorkingSetMinimum define the range of available memory. This setting does not appear in the RSReportServer.config file unless you add it manually.

When the value for WorkingSetMaximum is reached, the report server does not accept new requests. Requests that are currently in progress are allowed to complete. New requests are accepted only when memory use falls below the value specified through WorkingSetMaximum

Add Line 3 to RSReportServer.config file (Line 1 & 2 should be in the file)

Line 1. <MemorySafetyMargin>80</MemorySafetyMargin>
Line 2. <MemoryThreshold>90</MemoryThreshold>
Line 3. <WorkingSetMaximum>3000000</WorkingSetMaximum>

3. Restart Reporting Service in Configuration Manager

 

No more OutofMemory errors

Database integrity check

What is database integrity check?

In general the database integrity task includes checking the allocation and structural integrity of all the objects in the specified database. It can contain checking the database indexes.

It’s a good practice to run database integrity check on a set schedule, however there are some occasions when you will need to run check in some other times eg after hardware failure, electrical surge, the server unexpected shut down.

SQL Server provides number of commands to check the logical and physical integrity of all the objects in the specified database:

 

Command

Description

Command Level

Usage

DBCC CHECKDB Check for consistency errors, validates the integrity of everything in a database Database level Run every day during low usage times
DBCC CHECKALLOC Checks the consistency of disk space allocation structures for a specified database Database level DBCC CHECKDB is a superset of DBCC CHECKALLOC (includes allocation checks in addition to checks of index structure and data integrity), DBCC CHECKTABLE (for all tables in the database) and DBCC CHECKCATALOG.

 

DBCC CHECKTABLE Checks the integrity of all the pages and structures that make up the table or indexed view. Including pages linkage, sort order, pointers, page offsets Run for table and view in the database
DBCC CHECKCATALOG Checks for catalog consistency within the specified database. Checks that every data type in syscolumns has a matching entry in systypes and that every table and view in sysobjects has at least one column in syscolumn Runs on the database

 

NOTE: Do integrity check for all system databases such as master, model, msdb, distribution. The TEMPDB database does not need an integrity check, as it gets created from scratch at each SQL Server service restart.

 

For the majority of my SQL Servers I use Ola database maintenance solution see more details here http://ola.hallengren.com/sql-server-integrity-check.html

The command I execute looks similar to below

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB'

 

What you do if you find CHECKDB error?

The answer is get on to it as soon as possible.

  1. Try to restore your database from good backup
  2. In case if no good backup available use REPAIR OPTION with DBCC CHECKDB. Only do this if you can’t find good backup as repair option may lead to the data loss

 

So what are the REPAIR OPTIONS?

  • REPAIR_FAST – does minor fast repairs on database without risk of data loss such as repairing extra keys in nonclustered indexes
  • REPAIR_REBUILD – does minor repairs and also rebuilds indexes without risk of data loss
  • REPAIR_ALLOW_DATA_LOSS – this is a time consuming process and can result in some data loss. It includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. Back up the database after repairs are completed

Note: All of these repair options require the database to be in single user mode. Hence downtime is necessary.

Use the following command

 

ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

BEGIN TRANSACTION;

  DBCC CHECKDB ('AdventureWorks2008R2',  REPAIR_ALLOW_DATA_LOSS);

  ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER;

COMMIT TRANSACTION;
GO

Linked Server Error “Cannot obtain the schema rowset”

I come across the Error Message while trying to run a linked server query for SQL Server 2000 on SQL Server 2008  & SQL server 2008 R2.

The error stated “Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10″ for linked server ‘LinkedServerName’”

After little Google search I came across two possible solution to fix this error message. Apparently that is well know issue when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server.

 

Main Cause of this issue:

  • Problem may occur because the system stored procedures were not upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4.
  • May not have manually upgraded the system stored procedures after installing SQL Server 2000 SP3 or SQL Server 2000 SP4.
  • Whenever MDAC is upgraded to a newer version during SQL Server SP3 upgrade, one needs to run INSTCAT.SQL on all SQL Servers to which it connects to, including the Server that is being upgraded. I don’t think I ever done this.

Possible resolution Solution 1:

  1. Log on to the computer (SQL server 2000 server) by using a Windows account, which is a member of the SQL Server sysadmin fixed server role.
  2. Back up the master database
  3. Click Run, type cmd.exe, and then click OK.
  4. At the command prompt, type one of the following commands, and then press ENTER
 osql -E -S <LinkedServerName> -i <Location>\instcat.sql (For windows account with default SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName > -i <Location>\instcat.sql (For SQL account with default SQL server) OR osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql (For windows account with Named Instance of SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName >\<InstanceName> -i <Location>\instcat.sql(For SQL account with Named Instance of SQL server) 
Linked server error
  • Test to see that, you can run all distributed queries

Reference Links

http://support.microsoft.com/default.aspx?scid=kb;en-us;906954#appliesto

http://technet.microsoft.com/en-us/library/aa215459%28SQL.80%29.aspx

 

Possible resolution Solution 2:

  1. Create the following stored procedure on your linked SQL Server 2000 instance
USE master

CREATE PROCEDURE sp_tables_info_rowset_64
@table_name SYSNAME,
@table_schema SYSNAME = NULL,
@table_type nvarchar(255) = NULL
AS
DECLARE @Result INT SET @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
GO

2. GRANT EXECUTE the procedure to the Public in the MASTER database

USE master
GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

Reference Links

http://blogs.xtivia.com/home/-/blogs/21924

I actually tried both suggested resolutions, however only Solution 2 successfully worked for me

fixing link server problem for SQL Server 2000

Introducing Microsoft SQL Server 2014

SQL Server 2014 was announced during Mcrosoft TechEd 2013 North America keynote.

The biggest selling points were

  • In-Memory capabilities built into the core database for OLTP and data warehousing. “Memory-optimized” tables are completely in memory and they don’t use pages for storage.
  • Increase performance by extending SQL Server in-memory buffer pool to SSDs
  • Hybrid Cloud Platform where cloud can be leveraged to extend the scalability and availability of on-premises database applications as well as reduce costs
  • Backup to Azure Storage and AlwaysOn integration with Windows Azure Infrastructure Services
  • BI Insights which simplifies access to internal or external data, analysing the data and unlocking it with powerful BI tools built into Excel and SharePoint.

It’s good to see SQL2014 is not for cloud only release. It brings new features and improvements for developers, administrators and power users. SQL Server 2014 will likely arrive early next year.

 

Sql Server Evolution through the ages

Ref: http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/03/sql-server-2014-unlocking-real-time-insights.aspx

 

Read more about the new features of the SQL Server 2014:

BOXI3 Desktop Intelligence Connection Error Troubleshooting

Problem: One of the users was unable to refresh an existing DESKI report and was getting the following error

Connection or SQL sentence error: (DA0005)

DESKi Connection Error D0005

Followed by connection unavailable error (DA004)

 DESKi connection required D0004

Possible Causes

 

  • Cause 1: You may get a connection or SQL sentence error after changing the date format in the sbo file.

 

Action: Verify that the DATE_FORMAT parameter is set to the date format that you have set in the sbo file. 

 

  • Cause 2: Your universe connection has been moved, deleted, renamed, or account accessing data changed access rights.

 

Action: Check connection and its parameters. Data Access driver, connection and login parameter and connection type

 

  • Cause 3: Your drivers have been moved, deleted, renamed, or damaged. 

Action: Install the Microsoft drivers again. 

After checking all of the above I found out that universe was using OLEDB connection and also user has moved his machine.

Solution:

I installed SQL Native Client for the appropriate version of the SQL Server universe tried to access on his machine and it worked.

That led me to the questions:

What is the difference between OLE Db and ODBC data access?

What is preferred method connecting to databases today?

Comparing OLE DB and ODBC

OLE DB and ODBC are both specifications created by Microsoft to address universal data access.

ODBC is an industry standard and a component of Microsoft® Windows®  Open Services Architecture (WOSA). The ODBC interface makes it possible for applications to access data from a variety of database management systems (DBMSs)  through a single interface. The application in this case is independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which create an interface between an application and a specific DBMS.

Microsoft’s definition of OLE DB is a strategic system-level programming interface to data across the organization. OLE DB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data.

 

OLE DB

ODBC

Component based specification Procedural based specification
Supports all forms of data stores: relational,

hierarchical, e-mail and file system stores; text, graphical, and geographical data; custom business objects; and more

Constrained to relational data stores
Richer and more flexible interface for data access Tightly bound to a command syntax
Better support Internet integration and multi-threading More established interface proven drivers

and applications available in the market place

Easy to deploy Difficult to deploy due since many software pieces must be synchronized (server, driver, OS, etc)
Accomodates several locking models that allow multiple concurrent updates No concept of record locking
Worked only in Windows work. Mostly for Microsoft data systems Access data from multiple platform

 

We have been told many times by Microsoft to start migrating applications to OLE DB because it is the industry direction to access all kinds of data and it’s fully integrated in Microsoft’s desktop environment.

However recently, Microsoft made an announcement that sounded like a cruel joke. After years of telling users that Open Database Connectivity (ODBC) was an old technology that should no longer be used and would be phased out in favour of Object Linking and Embedding Database (OLE DB), they declared ODBC the new king of data access and deprecated OLE DB.

http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx

 

Why ODBC is a winner for the data access?

The explanation is obvious — ODBC has survived and prevailed mainly because there is a strong need to access database systems from multiple platforms. OLE DB worked only in the Windows world and an OLE DB provider was usually a Microsoft product.

In the last few years, the need for cross-platform and cross-database communication greatly increased. There are lots of new database products, many of them based on NoSQL technologies. Heterogeneous database systems on multiple platforms including Windows, Linux, iOS, Android etc need to communicate with each other to satisfy the needs of business users. Structured data systems based on relational databases often need to connect to unstructured data stores.

Microsoft realized, based on surveys and customer feedback, that ODBC is the most established cross-platform data access technology.

Will this affect your existing applications?

OLE DB for SQL Server will continue to be supported for several years. Mainstream support for SQL Server 2012 will last until July 2017.

Many components of SQL Server use OLE DB, including linked servers, distributed queries, SQLCMD utility or OLE DB for OLAP. So Microsoft will now need to re-architect these components and figure out how to make them work either with ODBC or some other way of communicating at the SQL Server level.