ERROR !! SQL Server Configuration Manager Cannot Connect to WMI Provider

Problem:

Error Message – “Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with the SQL Server Configuration Manager. Invalid namespace[0x80041010]” when trying to launch SQL Server Configuration Manager.

 

1

 

Cause:

This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.

 

Solution:

To work around this problem, open a command prompt, type the following command, and then press ENTER:

Command:

mofcomp “%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof”

Note: For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

2

3

 

 

Hope this was helpful !!!!

Advertisements

SQL Server – Restrict snapshot generation to only newly added article to SQL Server Replication

Problem:

I have Transactional Replication configured and when I add new article into the replication and generate new snapshot, it gets generated for all the articles.  As generating snapshot for all the articles is a waste of time and resources [Disk, CPU etc], I want to generate snapshot only for newly added articles.

Solution:

This is a common request to add a new article to a publication.  To save resources we will walk through how to only create the snapshot for the new article(s).

Assuming Transactional Replication is already configured.

 

The publication has 4 objects/tables to be published.

1

Now, we are adding a new object [BillOFMaterials(Production)] to the publication.  When we add new article to the publication, the publication becomes invalid and we need to create a new snapshot.

 

 

2

When we start the snapshot, it generates snapshot for all 5 articles in the publication.

 

 

 

3

 

 

To achieve what we want, we need to disable options [allow_anonymous and immediate_sync].

Run the below commands on your publisher database for your publication to make the changes.

4

 

 

–Run on your publisher database
EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘allow_anonymous’ ,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘immediate_sync’ ,
@value = ‘false’
GO

 

Now, lets add another article [BusinessEntityAddress[Person] to the publication and generate a new snapshot.

 

5

 

This time it gets generated for only the newly added article.

 

6

 

7

 

 

Hope it was helpful !!!!

 

 

SQL Server – Query/Command to check the nodes of SQL Cluster Instance

To check the current node/Active Node where the SQL Instance is running, you can execute the below query.

select serverproperty(‘ComputerNamePhysicalNetBIOS’)

1

 

To find out all the nodes of the SQL Cluster, you can use the below DMV.

select * from sys.dm_os_cluster_nodes

2

 

To find out if the instance is clustered, you can use below query, if it returns 1, then the instance is clustered

select ‘IsClustered’, serverproperty(‘IsClustered’)

3

 

ERROR!!! “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”

Issue:

Sometimes you may get into issues while connecting to SQL Server with below error message

1

“A network-related error or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

 

Solution:

There could be several reasons for you getting this error.  Below are few things you need to check to troubleshoot and resolve this issue.

1. Make sure the server you are trying to connect is up and running.  Ping the Server IP (where SQL is installed) from the client machine.

ping

 

 

2. If the Server is up, then make sure the SQL Server is up and running. To check that, go to SQL Server configuration manager (Start > All Programs > SQL Server 2005/2008/R2> Configuration Tools > SQL Server Configuration Manager)

Click on SQL Server Serivices on the left hand side and ensure the SQL Server (MSSQLSERVER) Service is running.

 

2

 

 

3. Check that the SQL Browser service is running. If you have installed a SQL Server named instance and not configured a specific TCP/IP port, incoming requests will be listening on a dynamic port. To resolve this you will need to have the SQL Browser service enabled and running.

 

3

 

4.  Check for TCP/IP and Named Pipes protocols and port. Open SQL Server Configuration Manager and check the SQL Server Network Configuration protocols. You should enable Named Pipes (if it is applicable to your environment) and TCP/IP protocol.

 

4

 

5.  For the TCP/IP protocol, right click and select properties to check the TCP/IP communication port as well. The default port is 1433, which can be changed for security purposes if needed.

 

5

 

6.  Try to telnet the SQL Server from Client machine with SQL port. Ensure that SQL port is open in Windows Firewall.

 

7

 

7. Make sure Remote connection is allowed in SQL Server. Open SSMS, connect to server, right-click server and select Properties. Click the Connections node and then Under Remote server connections, ensure Allow remote connections to this server is checked

 

6

 

 

Hope this was helpful !!!

Changes in Management Plan overwrites owner of SQL Job

Problem: 

In one of the production SQL Servers, I created a new database.  After which I added that database into the existing Maintenance Plan, meant for Daily backup (This maintenance plan was created long back by another DBA, with his account).  Next day, I noticed that the backup job had failed.  While investigating the issue, I found that the job owner was changed from sa account to old DBA account (Old DBA moved out of organisation long back).

 

Reason:

When you change anything on a maintenance plan created by another person, the original creator will be stored as the owner of the job every time you make a change in the SSIS package. This happens because SQL will try to keep the owner of both objects in sync. But the owner of the package is not exposed, so you have to update MSDB by hand to change it to any other thing – lets say “sa” for example.

 

Solution:

Code to update the package owner.  Please refer the below link as well.

https://connect.microsoft.com/SQLServer/feedback/details/295846/job-owner-reverts-to-previous-owner-when-scheduled-maintenance-plan-is-edited

 

--view all packages
select * from msdb.dbo.sysssispackages
--update plans ownership
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] like 'MaintenancePlan%'
Note: Replace the name and owner account in the script as per your requirement.

No global profile is configured. Specify a profile name in the @profile_name parameter

Yesterday, a developer from application team sent an email saying, he is not able to send mail alerts using database mail from a production server.

I asked which DB Profile name [@profile_name] you are using.   As we have different profiles in different servers.  [Which is not a best practice.  We should use same profile name within our environment to keep uniformity]

He was using the correct profile. Then I requested him to share the error details.

Error Details were as below:

“No global profile is configured. Specify a profile name in the @profile_name parameter”

I realized the profile was not configured as default profile.  So, I made the DB mail profile as default and it resolved the issue.

 

Below is the procedure to configure Global Profile in Database Mail.

  1. Connect to the target SQL Server with SSMS (SQL Server Management Studio)
  2. Expand the Managementsection
  3. Right-click on Database mailand select Configure Database Mail

1

4.  From the Wizard, click on Next >

2

5.  From the Task screen, select Manage Profile Security and click on Next>

3

6.  From the Manage Profile Security screen, select the target profile, then click in the Default Profile tab to activate the drop-down. Select Yes from the drop-down then click on Next >

4

 

7.  From the Complete the Wizard screen, click on Finish

5

 

 

Linked Server Error–remote proc transaction promotion

Problem:  

I have two SQL Servers Server A and Server B. Both have SQL Server 2008. I needed to fetch data from server B to server A and insert it into a table on server A.  For this purpose, I had created a stored proc on server B and for calling that SP I have added server B as linked server on server A.  When I executed this SP from server A using server B, it returns data without any issue.

However when I try to insert this data to a local table on server A, I am getting following error:

OLE DB provider “SQLNCLI10” for linked server “LinkedServerName” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Server <ServerName, Procedure <SP Name>, Line 47
The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkedServerName” was unable to begin a distributed transaction.

 

Solution:

Resolved the issue by changing the linked server property with below command.

EXEC sp_serveroption @server = ‘ServerB’,@optname = ‘remote proc transaction promotion’, @optvalue = ‘false’ ;