Review of IBM Cognos Business Intelligence by Dustin Adkison

IBM Cognos Business Intelligence is very friendly with Business Intelligence. If you are an IBM Cognos or Business Intelligence developer and want to improve productivity and efficiency of your IBM Cognos 10 environment then I highly recommend this book to you.

A great Book written by Dustin Adkison.

Preview of IBM Cognos Business Intelligence

New book on Cognos Business Intelligence, is now available.

If you are having a basic knowledge of Cognos 10 BI and a good level of understanding of Cognos 8 then this book is for you.


Read more about IBM Cognos Business Intelligence

: http://www.packtpub.com/ibm-cognos-business-intelligence/book

SQL Server Patch Issue: Windows cannot access the specified device, path or file

I was trying to Install SQL Hotfix on one of the production server. When I tried to run the setup file I was getting below error:

Windows cannot access the specified device, path or file. You may not have appropriate permission toaccess the item

I checked all Drives and I had Full permission on these Drives. I was added in Local Admin group in the server. I also tried to run the the setup file "Run as Administrator" but I was getting the same error:

When I right clicked on the set up file and checked in General tab I found that the file was blocked. This happened because I copied the setup file from some remote location and probably there was some additional security setup on this server. I Just clicked on UNBLOCK and click Apply and Try to ran setup again and it worked.

Database Mail and Service Broker Issue

Recently I faced an issue with my Database mail in one of the test server. When I was trying to send test mail using DB mail I was getting below error:



TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:

Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery. (Microsoft SQL Server, Error: 14650)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=14650&LinkId=20476

I tried to enable Service broker using below command:

Note: You need to stop SQL server agent before running below command

ALTER DATABASE msdb SET ENABLE_BROKER

But I was getting below error:

Msg 9776, Level 16, State 1, Line 1

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (95101104-EDB7-4C9C-8218-930A18B127EC) does not match the one in sys.databases (7E03C2E9-2C9A-4384-BDDA-8BF6971D3D4C).

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

 

When I checked sys.databases and found that the Service Broker GUID of MSDB is different from the one in sys.databases.

Reason: Recently I was testing a DR test where I restored backup of MSDB Database from other server to my Test Server and that was the reason my MSDB database Service broker GUID was not matching with the one in sys.databases

Then I ran below command to create New Broker for MSDB Database.

ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE

Once I ran above command, new Service Broker GUID created and resolved my DB mail issue.

How to Use Database Mail feature in SQL Server 2000

In SQL server 2000 we don't have any option to configure DatabaseB mail, this option was introduced in SQL server 2005.

There are basically three ways to configure Mail to send mail.

1. SQL Mail:
2. Using Linked Server:
3. Using xpsmtp80.dll:

I will focus on 2 and 3 points to set up a mail on SQL server 2000.

Using Linked Server: I assume that the reader knows How to configure DB mail and Linked Server-

Follow below steps to use Database mail feature:



1. Make sure SQL server 2005/SQL server 2008 is added to SMTP exchange relay where you have DB mail feature.

2. Configure DB Mail on SQL server 2005/2008 server.

3. Create one SQL Login 'xyz' on 2005/2008 server and provide DB_owner on MSDB Database.

4. Use the same login created in 3rd step to create linked server on SQL server 2000.

5. Run below query to check if you are getting result using linked server

select * from [Linked server name].msdb.dbo.backupset

6. If you are getting result it means linked server is configured properly.

7. Then run below query to check if you are getting mail from the server or not.

exec Linked server name.msdb.dbo.sp_send_dbmail
 @profile_name = 'Test',
 @recipients='xyz@abc.com',
--@copy_recipients = 'yourgroup@email.com',
 @subject='Job test_db_mail failed on SQL Server 2000 server',
 @importance='HIGH',
 @body ='Job test_db_mail failed on SQL Server 2000 server'

Using xpsmtp80.dll:

1. Make sure SQL server 2000 server to SMTP relay.

2. Download " xpsmtp80.dll" from below link

http://www.sqlservercentral.com/Forums/Attachment5240.aspx

3.Copy "xpsmtp80.dll" to "C:\Program Files\Microsoft SQL Server\MSSQL\Binn" location

4. Register Extended Proc using below query

exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp80.dll'

5. Grant rights to the correct set of users using SQL Query Analyzer by executing:

grant execute on xp_smtp_sendmail to public

6. Use below query to check if you are getting mail from the server.

exec master..xp_smtp_sendmail   

      @TO = 'xyz@abc.com',  

       @from = 

'xyz@abc.com', 

      @subject =  @qstring,
      @message =  @qstring,
      @server = 'SMTP server name'  

How to Move Cluster Resources in Windows 2008 Server Using Command

There is a Simple command to move All Cluster resources from one node to other node using command prompt.

Command to check your Cluster Groups and nodes using command

CLUSTER group /status

If you run above command you will get below result.

There is a simple command to move Quorum from one node to other node.Where Node Name is your node(Active/Passive) which you want the cluster group to move on.
The Quorum Disk comes under Cluster Group, So once you moved Cluster Group, the Quorum Disk will move from one node to other node.

CLUSTER.exe group "Cluster group" /move: Node Name

CLUSTER.exe group "SQL Server (MSSQLSERVER)" /move: Node Name

CLUSTER.exe group "MSDTC" /move: Node Name

Kill Inactive Session using Cursor

 I had a requirement to select all the inactive session with more than 24 hours and kill the process where status is "Sleeping".

Below is the small Cursor which will select all inactive session where status is "sleeping" and kill the process based on the spid.



DECLARE @SPID VARCHAR(20)
DECLARE @STATUS VARCHAR(20)
DECLARE @QUERY VARCHAR(20)
DECLARE CUR CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE db_name(dbid)='Ashish' and STATUS='sleeping' and login_time<GETDATE()-1
OPEN CUR
FETCH NEXT FROM CUR INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @QUERY= 'KILL ' + @SPID
print (@QUERY)
EXEC (@QUERY)
FETCH NEXT FROM CUR INTO @SPID
END
CLOSE CUR
DEALLOCATE CUR