Archive for category Microsoft SQL Server

How to create a new SQL Database Broker?

To Enable SQL Database broker simply run the following TSQL command:

ALTER DATABASE db_name SET NEW_BROKER

No Comments

The Service Broker in database “db_name” cannot be enabled because there is already an enabled Service Broker with the same ID.

Getting the following error when enabling the broker.

The Service Broker in database “db_name” cannot be enabled because there is already an enabled Service Broker with the same ID.

The above error happens when the database was restored from a database that already has the broker enabled. To fix this you need to create a new broker using:

ALTER DATABASE db_name SET NEW_BROKER

No Comments

How to enable/disable SQL Database broker

To Enable SQL Database broker simply run the following TSQL command or use database properties:

ALTER DATABASE db_name SET ENABLE_BROKER

To Disable SQL Database broker run the following TSQL command or use database properties:

ALTER DATABASE db_name SET DISABLE_BROKER

No Comments

How to Enable FileStream

To enable and change FILESTREAM settings

  1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.

  2. In the list of services, right-click SQL Server Services, and then click Open.

  3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.

  4. Right-click the instance, and then click Properties.

  5. In the SQL Server Properties dialog box, click the FILESTREAM tab.

  6. Select the Enable FILESTREAM for Transact-SQL access check box.

  7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

  8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

  9. Click Apply.

  10. In SQL Server Management Studio, click New Query to display the Query Editor.

  11. In Query Editor, enter the following Transact-SQL code:

    EXEC sp_configure filestream_access_level, 2
    RECONFIGURE

  12. Click Execute.

  13. Restart the SQL Server service.

No Comments

Need OSQL or other SQ Server client tools?

Here’s the free download of Client Tools which includes the Management Studio as well as utilities such as OSQL.

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=7593

No Comments

SQL Server Connection Strings

Website that shows all different ways to connect to SQL Server from your code:

http://www.connectionstrings.com/sql-server-2008

No Comments

SQL Server 2012 “Denali”

Lots of new BI features coming out with the new version of SQL Server code named “Denali”.

The new release which is due out in March of 2012 is promised to include many improvements in BI and high availability features.

Microsoft’s BI strategy involved MS office, Sharepoint server 2010 and SQL Server 2008 R2/2012.

No Comments

SQL Server Maintenance Job fails

Issue:
The following error occurs when running SQL Server Maintenance Plan during check database integrity step. I encountered only on SQL Server 2005 environment.
Alter failed for Server ''

Solution:
Exec sp_configure 'Allow Updates', 0
reconfigure

More explanation on ‘Allow Updates’
http://msdn.microsoft.com/en-us/library/aa196704%28v=sql.80%29.aspx

No Comments

SQL Server: Enabling Agent XPs

How to enable Agent XPs:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Notice “with override” option used with RECONFIGURE. I added that in to compensate for the error message given when running RECONFIGURE alone. The Error message was: Ad hoc update to system catalogs is not supported

No Comments

MS SQL Server 2000 Support

  • SQL Server 2000 64-bit Edition
    • Mainstream Support: 4/8/2008
    • Extended Support: 4/9/2013

  • SQL Server 2000 Developer Edition
    • Mainstream Support: 4/8/2008
    • Extended Support: 4/9/2013

  • SQL Server 2000 Enterprise Edition
    • Mainstream Support: 4/8/2008
    • Extended Support: 4/9/2013

  • SQL Server 2000 Service Pack 1
    • Mainstream Support: N/A
    • Extended Support: 2/28/2002

  • SQL Server 2000 Service Pack 2
    • Mainstream Support: N/A
    • Extended Support: 4/7/2003

  • SQL Server 2000 Service Pack 3a
    • Mainstream Support: N/A
    • Extended Support: 7/10/2007

  • SQL Server 2000 Service Pack 4
    • Mainstream Support: 4/8/2008
    • Extended Support: 4/9/2013

  • SQL Server 2000 Standard Edition
    • Mainstream Support: 4/8/2008
    • Extended Support: 4/9/2013

  • SQL Server 2000 Windows CE Edition 2.0
    • Mainstream Support: 1/8/2008
    • Extended Support: 1/8/2013

  • SQL Server 2000 Workgroup Edition
    • Mainstream Support: 4/8/2008
    • Extended Support: 4/9/2013
  • http://support.microsoft.com/lifecycle/?p1=2852

No Comments