Wednesday, November 25, 2009

Error message when you open SQL Server Configuration Manager in SQL Server 2008: "Cannot connect to WMI provider. You do not have permission or the se

While opening of SQL Server Configuration Manager if you get error message of

"Cannot connect to WMI provider. You do not have permission or the server is unreachable"
Cause:
This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server 2008. The 32-bit instance and the 64-bit instance of SQL Server 2008 share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.

Workaround:
To work around this problem, type the following command, and then press ENTER:
mofcomp "%programfiles(x86)%\Microsoft\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Refer the KB# 956013

Tuesday, November 24, 2009

The SQL Server Service Broker or Database Mirroring transport is disabled or not configured

MOM raised alert of "The SQL Server Service Broker or Database Mirroring transport is disabled or not configured".

The Service Broker is enabled by default in SQL Server 2005. Service Broker is enabled by default when a new database is created. Please try to run the following query to see the Broker option on a database.

SELECT name, is_broker_enabled FROM sys.databases;

This alert is triggered by event 9666 in the Application Event log for the OpsMgr database server.However, this event is always generated when you restart SQL because neither the Service Broker nor Database Mirroring are enabled for the Master or
Model databases. "SELECT is_broker_enabled FROM sys.databases" identifies
that the Service Broker is enabled for all other DBs. The alert is a bit counter productive if the rule is there to try to identify which databases are missing this option that actually need it; e.g. the OpsMgr db.Currently my only option is to disable the rule.

Monday, November 23, 2009

How to Script Database Schema

1. On the database schema to be scripted out, right click
2. Select Tasks --> Generate Scripts
3. Select all the objects in the selected databases
4. Select the appropriate General & Table Options
5. Select the output format

Thursday, November 19, 2009

Testing a Connection from Client to SQL Server using UDL

A connection from client to SQL Server can easily be checked with the help of a .udl file. This is also helpful for building a connection string.

Step 1 - Create a .udl file
Create a text file and name it for example test.udl
Double click on test.udl to open the connection dialog.

Step 2 - Choose the type of connection
Go to the first tab “Provider”
Choose “Microsoft OLE DB Provider for SQL Server” or any other provider according to your needs.

Step 3 - Set up connection properties
Go to the second tab “Connection”, choose the server, enter username/password and choose a database from the drop down list.

Step 4 - Connection String
Once you click on “OK” a connection string based on your connection settings is written to test.udl. After you open test.udl you will find a connection string like this.

[oledb]; Everything after this line is an OLE DB initstringProvider=SQLOLEDB.1;Password=wrf%32g;Persist Security Info=True;User ID=siva;Initial Catalog=msdb;Data Source=SQLTEST