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'