$5 eBook Bonanza

 Packt eBooks $5 Offer

Packt Publishing just told me about their Christmas offer, get eBooks for $5.From December 19th, customers will be able to get any eBook or Video from Packt for just $5. This offer covers a myriad of titles in the 1700+ range where customers will be able to grab as many as they like until January 3rd 2014 – more information is available at http://bit.ly/1jdCr2W

Job failed with 'QUOTED_IDENTIFIER, ARITHABORT' in SQL server 2000

Recently I faced issue in SQL server 2000. The Database maintenance plan was in place on weekly basis which were doing Update Statistics on the specified database. The Update Statistics job was failing everyday. When I checked the history of the job I found below error:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.

The solution is to add a parameter in the Job:

-SupportComputedColumn: This parameter is the new feature that was introduced in SQL server 2000 Service pack4(SP4).

Add this parameter in the Job like below and excute the job, it will successfully execute.

EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt "Location of the output file" -WriteHistory  -UpdOptiStats 10 -SupportComputedColumn'

The description for Event ID '1073741877' in Source 'SQLAgent$Name' cannot be found.The following information is part of the event:'[sqagtres] LooksAlive request.

Recently I applied service pack 2 in one of the cluster environment which is SQL server 2008 R2. After applying SP2, few information logs was generating on 2008 SQL Servers which was related to SQL Agent service, these logs were filling up in system application event logs in all the SQL Server nodes.

I was getting below information message in application log:

Message

The description for Event ID '1073741877' in Source 'SQLAgent$Name' cannot be found.  The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them.  The following information is part of the event:'[sqagtres] LooksAlive request.

'

Message

The description for Event ID '1073741877' in Source 'SQLAgent$Name' cannot be found.  The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them.  The following information is part of the event:'[sqagtres] CheckServiceAlive: returning TRUE (success).

As per Microsoft articles:

The issue occurs because of a code defect in the Sqagtres.dll SQL Server Agent resource file. The information that is mentioned in the "Symptoms" section should not be logged.

The resolution for this bug has been fixed in Cumulative update 3. So if anyone is getting this informational message in application log, apply Cumulative update 3 (http://support.microsoft.com/kb/2754552).

For more information you can visit to Microsoft link:

http://support.microsoft.com/kb/2718920

How to apply SQL service pack or CU in SQL server 2005 Cluster

Here I will explain how to apply Service pack or CU in SQL server 2005 cluster.
Lets assume there are two nodes with Active/Active and SQL is rnning on both the nodes
Follow below steps to apply service pack or CU.

      1. Make sure media is available in C drive on both nodes .

      2. Make sure all SQL instances are running on the same node. If it is Active/Active node, failover instance       from one Active node (B) to other Active node (A) to make other node as passive (B).

      3. After failover validate all SQL instances are up and running

      4. Pause passive node (B) in cluster admin.

      5. First apply Service pack/CU on active node (A). Since the passive node (B) is paused, the installer won’t be able to do anything on the passive node.  

      6. Double click on media and follow steps on active node (A)

   

  Note: If you want to apply Service pack or CU only for specific instance, then select only that instance otherwise select all instance to apply patch.

      7. Once the installation is finished, and if it asks for Server reboot

      8.  Resume passive node (B) in cluster admin.

      9.     Fail over all the instances from Active node (A) to passive node (B).

     10. Now Active (A) becomes passive and Passive (B) becomes Active

     11. Reboot passive node (A).

     12.  Now We need to apply patch on current Active node (B)

     13.  Pause Passive node (A) in cluster admin

     14. Double click on media and follow steps on active node (B)

     15. Now the patching is completed on Active node (B).

     16. If reboot is required, then resume Passive node (A) then fail backup all instances from Active node (B) to          Passive node (A).

     17. Now reboot passive node (B).

     18. Once the passive node comes up, connect all SQL instances and verify the SQL Services are DBs and  also check version of all SQL instances.

     19. Now fail back all SQL resources to its intended nodes

Explore the Full range of eBooks and Videos at 50% off this Columbus Day!!

Explore something new this Columbus Day with Packt’s biggest ever sale

Packt Publishing is giving everyone the chance to explore its full range of over 1600 DRM-free eBooks this Columbus Day at a massive 50% off at www.packtpub.com, for 4 whole days. Customers simply use the code COL50 in their cart – as many times as they like until Thursday October 17^th. The offer even extends to Packt’s bestselling pre-order of 2013, the highly acclaimed Mastering Web Application Development with AngularJS.

But that’s not all – to mark the transition out of beta stage, the publisher will also be including its Packt Video product range in this limited offer. These practical screencast tutorials give users the working knowledge they need to get the job done, and all videos will be featured in the Columbus Day sale at 50% off – that includes the hugely popular Kali Linux - Backtrack Evolved: A Penetration Tester’s Guide.

The exclusive 50% discount code COL50 will be active on all eBooks and Videos until Thursday October 17^th.

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