Archive for category Microsoft SQL Server
How to create a new SQL Database Broker?
Posted by Maz in Databases, Microsoft SQL Server on April 11, 2012
To Enable SQL Database broker simply run the following TSQL command:
ALTER DATABASE db_name SET NEW_BROKER
The Service Broker in database “db_name” cannot be enabled because there is already an enabled Service Broker with the same ID.
Posted by Maz in Databases, Microsoft SQL Server on March 14, 2012
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
How to enable/disable SQL Database broker
Posted by Maz in Databases, Microsoft SQL Server on March 8, 2012
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
How to Enable FileStream
Posted by Maz in Databases, Microsoft SQL Server on March 8, 2012
To enable and change FILESTREAM settings
- 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.
- In the list of services, right-click SQL Server Services, and then click Open.
- In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
- Right-click the instance, and then click Properties.
- In the SQL Server Properties dialog box, click the FILESTREAM tab.
- Select the Enable FILESTREAM for Transact-SQL access check box.
- 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.
- 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.
- Click Apply.
- In SQL Server Management Studio, click New Query to display the Query Editor.
- In Query Editor, enter the following Transact-SQL code:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
- Click Execute.
- Restart the SQL Server service.
Need OSQL or other SQ Server client tools?
Posted by Maz in Microsoft SQL Server on November 7, 2011
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
SQL Server Connection Strings
Posted by Maz in Microsoft SQL Server on November 4, 2011
Website that shows all different ways to connect to SQL Server from your code:
SQL Server 2012 “Denali”
Posted by Maz in Microsoft SQL Server on October 26, 2011
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.
SQL Server Maintenance Job fails
Posted by Maz in Databases, Microsoft SQL Server on April 14, 2011
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
SQL Server: Enabling Agent XPs
Posted by Maz in Databases, Microsoft SQL Server on February 9, 2011
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
MS SQL Server 2000 Support
Posted by Maz in Databases, Microsoft SQL Server on December 7, 2010
- 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