Irfan's World

June 30, 2012

SAP BI 4.0 – Central Management Server ports

Filed under: Live Traffic, SAP BI 4.0 Administration — Tags: — Irfan @ 10:16 am

In BI 4.0 Central Management Server (CMS) uses two different ports:

a) Name Server Port

CMS by default uses port 6400 to communicate with Designer, WebI Rich Client, Information Design Tool, Business View Manager, application server(s) and may be firewall internal to the organization. In other words CMS listens for the requests on this port, this port is referred as name server port. When the BI server starts all the other BI services will register with CMS on name server port, CMS then sends its request port # to other service to communicate after its registration with CMS.

If the name server port is changed then all the BI services that needs to communicate with CMS has to know on which port the CMS is running to register themselves.

b) Request port
Each BI server binds to a request port, for example the Web Intelligence Processing Server  select its request port number dynamically when the server starts or restarts, unless it is configured to use a specific port number. This port is used by CMS to communicate with the Web Intelligence Processing Server.

For CMS the name server port and request port must be different otherwise the server will not start. If you by mistake set these ports with the same port # and saves it, the next time when the BI server restarts then CMS will not start because of port conflict. It may throw this error in CCM.   (@IRFANCLSTR is the cluster name)

 

image

To resolve the port conflict follow the following steps: 

  stop the SIA in CCM
  go to properties and startup tab
  click the central management server
  click properties and delete the request port shown below.   

image

Click OK and restart the SIA.

In conclusion, do not assign the same port # for CMS in CMC Launchpad as it is not advisable. You can also assign static ports to each of the BI services in BI 4.0 stack but that is all together a different topic.

image

Advertisements

November 1, 2011

How to use undocumented functions of SQL Server ?

Here in this post, I am going to explain some undocumented functions of SQL Server.  These functions are very useful to get the information about a database or a table quickly as a part of maintenance and other daily tasks.  I regularly use sp_MSforeachtable to count the rows of the tables in our data warehouse and the source systems.  I also use sp_MSforeachDB  to get a count of objects in a database when a production database is refreshed on to a UAT or DEV environments.

  To get a count of objects for all the databases on a Host

sp_msforeachdb ‘select ”?” as DatabaseName, count(*) as CountObjects from ?.sys.objects’

  To get a count of rows from all the tables in a database

sp_msforeachtable ‘select ”?”, Count(*) from ?’


Note:  There are single quotes around the question mark(?) in the above syntax.

October 12, 2011

Drop Foreign Key Constraints in SQL Server 2008

Filed under: SQL Server 2000/2005/2008 — Tags: , , — Irfan @ 4:54 pm

The following SQL generates ALTER TABLE DROP CONSTRAINT sql for each table in the Database.  This script is used mostly when you try to TRUNCATE the tables in a database.

 SELECT ‘ALTER TABLE ‘ +
       ‘[‘ + s.name + ‘].[‘ + t.name + ‘]’ +
       ‘ DROP CONSTRAINT [‘ + f.name +’]’
FROM sys.foreign_keys f
INNER JOIN sys.tables t ON f.parent_object_id=t.object_id
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0;

The ALTER TABLE sql statements that it has generated on my database is shown below. I made the image little blurred on purpose.

image

Don’t forget to press Ctrl+T to generate the sql in Text format in SSMS.

February 17, 2011

How to get all indexes of table(s) in SQL Server 2008?

Filed under: SQL Scripts — Tags: , , , , — Irfan @ 11:20 pm

1) To get the indexes from a given table and schema.

Use the following script to get all the indexes for a given table and schema name.

DECLARE @TableName SYSNAME,
      
@SchemaName
SYSNAME

SET @TableName = 'Employee'
SET @SchemaName =
'HumanResources'
SELECT
  
i.OBJECT_ID
,
  
i.index_id
,
  
i.name IndexName
,
  
OBJECT_NAME(i.OBJECT_ID) TableName
,
  
c.name ColumnName
,
  
i.index_id
,
  
df.name FileGroupName
,
  
i.fill_factor
,
  
i.is_unique
,
  
i.type_desc
,
  
ic.is_included_column
,
  
schema_name(o.schema_id)
SchemaName
FROM
sys.indexes i
INNER JOIN
sys.index_columns ic
  
ON      i.index_id = ic.index_id
AND
          
i.OBJECT_ID = ic.
OBJECT_ID
INNER JOIN
sys.columns c
  
ON      ic.column_id = c.column_id
AND
          
i.OBJECT_ID = c.
OBJECT_ID
INNER JOIN
sys.database_files df
  
ON i.data_space_id =
df.data_space_id
INNER JOIN
sys.objects o
  
ON i.OBJECT_ID = o.
OBJECT_ID
WHERE
  
OBJECT_NAME(i.OBJECT_ID) = @TableName
AND
  
schema_name(o.schema_id) = @SchemaName
AND
  
i.index_id <>
1
ORDER BY
i.index_id

Following is the result when I run the above query for HumanResources Schema.

image

   2) To get all the indexes for a complete schema you can comment the line that checks for the TableName in the WHERE clause.

   DECLARE -- @TableName sysname,
      
@SchemaName
SYSNAME

   -- SET @TableName = 'customer'
   SET @SchemaName =
'sales'
   SELECT
     
i.OBJECT_ID
,
     
i.index_id
,
     
i.name IndexName
,
    
OBJECT_NAME(i.OBJECT_ID) TableName
,
    
c.name ColumnName
,
    
i.index_id
,
    
df.name FileGroupName
,
    
i.fill_factor
,
    
i.is_unique
,
    
i.type_desc
,
    
ic.is_included_column
,
    
schema_name(o.schema_id)
SchemaName
  FROM
sys.indexes i
  INNER JOIN
sys.index_columns ic
     
ON      i.index_id = ic.index_id
AND
             
i.OBJECT_ID = ic.
OBJECT_ID
  INNER JOIN
sys.columns c
    
ON      ic.column_id = c.column_id
AND
          
i.OBJECT_ID = c.
OBJECT_ID
  INNER JOIN
sys.database_files df
    
ON i.data_space_id =
df.data_space_id
  INNER JOIN
sys.objects o
    
ON i.OBJECT_ID = o.
OBJECT_ID
  WHERE
    -- object_name(i.object_id) = @TableName AND
   
schema_name(o.schema_id) = @SchemaName
AND
   
i.index_id <>
1
  ORDER BY
i.index_id

image

The above script is useful to find table column indexes and I use it when I develop filters or objects that can go to WHERE part of the query panel while developing reports using Web Intelligence (Rich Client) or Desktop Intelligence and it boost the performance of the query and that is all what we need at the end of the day.

January 24, 2011

ORA – 12514 TNS: listener does not currently know of service requested in connect descriptor

Filed under: Oracle 9i/10g/11g — Tags: , — Irfan @ 10:38 pm

When you connect using SQL Developer or any other third party tools that uses JDBC thin client would return this error, but remember it will not give you this error when you connect using SQL*PLUS.

    Error ORA - 12514 : TNS listener does not currently know of service requested in connect descriptor

As the error message suggests, the database server did not register itself with the database listener.  To troubleshoot this connectivity issue, we will start with looking at TNSNAMES.ORA and LISTENER.ORA files on the client machine from which you want to connect. In other words, I would say there is something like SERVICE NAME or HOST name is incorrect in the listerner.ora or tnsnames.ora file on the client machine.

Note : If you receive this error first contact your DBA to resolve it first.

The files that we are discussing here are Oracle systems files, take the backup of these files before you change any settings.  These files contain information regarding the HOST name or IP address of Oracle database server(s) in your network.  When a client machine tries to connect to the server using oracle client middle ware then you have to make sure that the client machine has correct information of the database server (HOST name or IP Address).  I repeat HOST or IP Address.

Follow these two steps to resolve this issue:

First of all, make sure that both of these files are in sync, you must use only one of the below options, it means either

   1.     Use HOST name in both the files (TNSNAMES.ORA and LISTENER.ORA)

       or

          Use IP Address of the Database Server in both the files (TNSNAMES.ORA and LISTENER.ORA)

   2. Register the database with the listener.



Step 1:

The following screen shots shows that I am using the HOST name as ‘irfan-PC’. Here is a sample of tnsnames.ora file from my client machine.

Here is a sample of listener.ora file from my client machine in that you will see the HOST name of the database server. 

Step 2:

Now connect to SQL*PLUS as DBA and issue the following command:

ALTER SYSTEM REGISTER

This command will register the database with the listener.

Now connect SQL Developer to oracle database :

Following screen shot shows some mandatory details when you connect using SQL Developer that will ensure to connect to an oracle database using SQL Developer.

Click ‘Test’ to check the connection and it should connect without issues. Hope you saved some time by resolving this error :).

Let me know if there are any other alternatives to resolve this error.

SQL Server Dates – Part 2 (Quarter)

In this post, I will be showing you how to generate Quarterly dates using getdate() database function.

Quarter Dates

The following code will generate First Day of the Previous Quarter, Last Day of the Previous Quarter, First Day of the Current Quarter, Last Day of the Current Quarter.

select ‘First Day of the Previous Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())-1,0)
select ‘Last Day of the Previous Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate()),0)-1
select ‘First Day of the Current Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate()),0)
select ‘Last Day of the Current Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())+1,0)-1
select ‘First Day of the Next Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())+1,0)

Wait for my next post for more Quarterly dates. Click for Date Part 3

SQL Server Dates – Part 1 (Year)

To display today’s date in SQL Server 2008 select GETDATE().

Following are some of the dates that can be used in SQL Queries to fulfill the end user needs while retrieving data from the database or creating custom objects for delivering reports in BI tools.

select ‘First Day of the Previous Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0)
select ‘Last Day of the Previous Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate()),0)-1
select ‘First Day of the Current Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate()),0)
select ‘Last Day of the Current Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)-1
select ‘First Day of the Next Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)

I will be continuing on how to generate Quarterly dates in my next post Quarter Dates

January 23, 2011

SQL Server Dates – Part 6 (Year, MI, SS)

Filed under: SQL Server 2000/2005/2008 — Tags: , , , , — Irfan @ 10:34 pm

Today, I am going to show you how to add hours, minutes or seconds to a datetime field GETDATE() function from SQL Server 2008 always returns Current Date and Time.  I am referencing Current Date and Time as "now" in this article.

To display the current date and time, type the following query in SQL Server editor:

SELECT GETDATE() as ‘Current Date and Time’

Hours

Add 4 hours from now.
SELECT DATEADD(HOUR,4, GETDATE()) as ‘Add Four hours from now’

Subtract 6 hours from now.
SELECT DATEADD(HOUR,-6, GETDATE()) as ‘Subtract 6 hours from now’

Minutes

Add 10 minutes to the current date and time
SELECT DATEADD(MI,10, GETDATE()) as ‘Add 10 min to now’

Subtract 60 minutes from the current date and time
SELECT DATEADD(MI,-60, GETDATE()) as ‘Subtract 60 min from now’

Seconds

Add 50 sec to the current date and time
SELECT DATEADD(SS,50, GETDATE()) as ‘Add 50 sec to now’

Subtract 60 minutes from the current date and time
SELECT DATEADD(SS,-40, GETDATE()) as ‘Subtract 40 sec to now’

Following are the results when we run the above queries in SQL Server Management Studio.

SQL Server Dates – Part 5 (Week)

Filed under: SQL Server 2000/2005/2008 — Tags: , , , — Irfan @ 10:28 pm

Here are some of the examples for generating weekly dates that are helpful to create weekly reports.

Week Dates

select ‘First Day of the Previous Week :’ , DATEADD(WW,DATEDIFF(WW,0,getdate())-1,0)

select ‘Last Day of the Previous Week :’ , DATEADD(WW,DATEDIFF(WW,0,getdate()),0)-1
select ‘First Day of the Current Week :’ , DATEADD(WW,DATEDIFF(WW,0,getdate()),0)
select ‘Last Day of the Current Week :’ , DATEADD(WW,DATEDIFF(WW,0,getdate())+1,0)-1
select ‘First Day of the Next Week :’ , DATEADD(WW,DATEDIFF(WW,0,getdate())+1,0)

SQL Server Dates – Part 4 (Month)

Most of the times when we develop Business Intelligence reports for trending, like trend reports, cross tabs or charts, compare analysis ( current month vs. previous month, current rolling 6 months vs. previous rolling 6 months, current rolling 12 months vs. previous rolling 12 months) which are widely used in BI industry.

Following are some date examples of how to generate those dates using GETDATE() function from SQL Server 2008 database that can be beneficial for your report development. These date examples are also very useful for scheduling reports. (i.e. something we call dynamic dates as parameters for scheduling the reports)

Month Dates

select ‘First Day of the Previous Month :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())-1,0)
select ‘Last Day of the Previous Month :’ , DATEADD(MM,DATEDIFF(MM,0,getdate()),0)-1
select ‘First Day of the Current Month :’ , DATEADD(MM,DATEDIFF(MM,0,getdate()),0)
select ‘Last Day of the Current Month :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())+1,0)-1
select ‘First Day of the Next Month :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())+1,0)

Rolling 12 Months

select ‘Current Rolling 12 Months / Begin Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())-11,0)
select ‘Current Rolling 12 Months / End Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())+1,0)-1
select ‘Previous Rolling 12 Months / Begin Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())-23,0)
select ‘Previous Rolling 12 Months / End Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())-11,0)-1

Wait for my next post on Weekly dates.

Older Posts »

Blog at WordPress.com.