Cannot enable SQL Broker

Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
Option 'ENABLE_BROKER' cannot be set in database 'master'.?
USE [master];
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
ALTER DATABASE [master] SET ENABLE_BROKER
I am running SQL 2005 Developer Edition (SP1) with everything
installed.
Thank you,
Michael
Because Service Broker cannot run in master. What use case causes you to
want to run Service Broker in the master DB? In general master should
contain only fairly static data and not highly dynamics data such as
messages on a queue.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<mpaine@.htxml.com> wrote in message
news:1161384930.975976.134060@.m73g2000cwd.googlegr oups.com...
> Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
> Option 'ENABLE_BROKER' cannot be set in database 'master'.?
> USE [master];
> GO
> EXEC sp_configure 'clr enabled', 1;
> RECONFIGURE WITH OVERRIDE;
> GO
> ALTER DATABASE [master] SET ENABLE_BROKER
>
> I am running SQL 2005 Developer Edition (SP1) with everything
> installed.
> Thank you,
> Michael
>
|||Thank you. I was reading various internet articles that do this so I
was wondering. I will stick to using specific databases for using the
broker.
- Michael
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> Because Service Broker cannot run in master. What use case causes you to
> want to run Service Broker in the master DB? In general master should
> contain only fairly static data and not highly dynamics data such as
> messages on a queue.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> <mpaine@.htxml.com> wrote in message
> news:1161384930.975976.134060@.m73g2000cwd.googlegr oups.com...
|||If you remember where any of those articles are let me know because they're
obviously wrong and should be fixed.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<mpaine@.htxml.com> wrote in message
news:1161387628.283147.90060@.h48g2000cwc.googlegro ups.com...
> Thank you. I was reading various internet articles that do this so I
> was wondering. I will stick to using specific databases for using the
> broker.
> - Michael
> Roger Wolter[MSFT] wrote:
>

Cannot enable SQL Broker

Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
Option 'ENABLE_BROKER' cannot be set in database 'master'.?
USE [master];
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
ALTER DATABASE [master] SET ENABLE_BROKER
I am running SQL 2005 Developer Edition (SP1) with everything
installed.
Thank you,
MichaelBecause Service Broker cannot run in master. What use case causes you to
want to run Service Broker in the master DB? In general master should
contain only fairly static data and not highly dynamics data such as
messages on a queue.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<mpaine@.htxml.com> wrote in message
news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
> Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
> Option 'ENABLE_BROKER' cannot be set in database 'master'.?
> USE [master];
> GO
> EXEC sp_configure 'clr enabled', 1;
> RECONFIGURE WITH OVERRIDE;
> GO
> ALTER DATABASE [master] SET ENABLE_BROKER
>
> I am running SQL 2005 Developer Edition (SP1) with everything
> installed.
> Thank you,
> Michael
>|||Thank you. I was reading various internet articles that do this so I
was wondering. I will stick to using specific databases for using the
broker.
- Michael
Roger Wolter[MSFT] wrote:
> Because Service Broker cannot run in master. What use case causes you to
> want to run Service Broker in the master DB? In general master should
> contain only fairly static data and not highly dynamics data such as
> messages on a queue.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> <mpaine@.htxml.com> wrote in message
> news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
> > Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
> > Option 'ENABLE_BROKER' cannot be set in database 'master'.?
> >
> > USE [master];
> > GO
> >
> > EXEC sp_configure 'clr enabled', 1;
> > RECONFIGURE WITH OVERRIDE;
> > GO
> >
> > ALTER DATABASE [master] SET ENABLE_BROKER
> >
> >
> > I am running SQL 2005 Developer Edition (SP1) with everything
> > installed.
> >
> > Thank you,
> > Michael
> >|||If you remember where any of those articles are let me know because they're
obviously wrong and should be fixed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<mpaine@.htxml.com> wrote in message
news:1161387628.283147.90060@.h48g2000cwc.googlegroups.com...
> Thank you. I was reading various internet articles that do this so I
> was wondering. I will stick to using specific databases for using the
> broker.
> - Michael
> Roger Wolter[MSFT] wrote:
>> Because Service Broker cannot run in master. What use case causes you to
>> want to run Service Broker in the master DB? In general master should
>> contain only fairly static data and not highly dynamics data such as
>> messages on a queue.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> <mpaine@.htxml.com> wrote in message
>> news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
>> > Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
>> > Option 'ENABLE_BROKER' cannot be set in database 'master'.?
>> >
>> > USE [master];
>> > GO
>> >
>> > EXEC sp_configure 'clr enabled', 1;
>> > RECONFIGURE WITH OVERRIDE;
>> > GO
>> >
>> > ALTER DATABASE [master] SET ENABLE_BROKER
>> >
>> >
>> > I am running SQL 2005 Developer Edition (SP1) with everything
>> > installed.
>> >
>> > Thank you,
>> > Michael
>> >
>

Cannot enable SQL Broker

Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
Option 'ENABLE_BROKER' cannot be set in database 'master'.?
USE [master];
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
ALTER DATABASE [master] SET ENABLE_BROKER
I am running SQL 2005 Developer Edition (SP1) with everything
installed.
Thank you,
MichaelBecause Service Broker cannot run in master. What use case causes you to
want to run Service Broker in the master DB? In general master should
contain only fairly static data and not highly dynamics data such as
messages on a queue.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<mpaine@.htxml.com> wrote in message
news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...
> Why would this TSQL fail with "Msg 5058, Level 16, State 8, Line 2,
> Option 'ENABLE_BROKER' cannot be set in database 'master'.?
> USE [master];
> GO
> EXEC sp_configure 'clr enabled', 1;
> RECONFIGURE WITH OVERRIDE;
> GO
> ALTER DATABASE [master] SET ENABLE_BROKER
>
> I am running SQL 2005 Developer Edition (SP1) with everything
> installed.
> Thank you,
> Michael
>|||Thank you. I was reading various internet articles that do this so I
was wondering. I will stick to using specific databases for using the
broker.
- Michael
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> Because Service Broker cannot run in master. What use case causes you to
> want to run Service Broker in the master DB? In general master should
> contain only fairly static data and not highly dynamics data such as
> messages on a queue.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> <mpaine@.htxml.com> wrote in message
> news:1161384930.975976.134060@.m73g2000cwd.googlegroups.com...|||If you remember where any of those articles are let me know because they're
obviously wrong and should be fixed.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<mpaine@.htxml.com> wrote in message
news:1161387628.283147.90060@.h48g2000cwc.googlegroups.com...
> Thank you. I was reading various internet articles that do this so I
> was wondering. I will stick to using specific databases for using the
> broker.
> - Michael
> Roger Wolter[MSFT] wrote:
>

Cannot convert Varchar to Numeric issue

Hi again all,

I have a small issue. Here's an example dataset :

F1 F2 F3
1 0.58 Hi
2 0.70 Hello
3 Fail Bye
4 <Null> Hi

When I write this statement :

SELECT SUM(CONVERT(DECIMAL(16,8),F2)) MySum
FROM T1
WHERE IsNumeric(IsNull(F2,'X'))=1

I get "Cannot convert a Varchar value to Numeric" error. From what I
understand, it somehow tries to convert to a decimal(16,8) BEFORE filtering
the nulls and the non-numeric out. (Keep in mind that the actual table has
over 1.5Million records).

Any idea on how to get around that ?

Thanks,

MichelMichel (Michel@.askme.com) writes:
> F1 F2 F3
> 1 0.58 Hi
> 2 0.70 Hello
> 3 Fail Bye
> 4 <Null> Hi
> When I write this statement :
> SELECT SUM(CONVERT(DECIMAL(16,8),F2)) MySum
> FROM T1
> WHERE IsNumeric(IsNull(F2,'X'))=1
> I get "Cannot convert a Varchar value to Numeric" error. From what I
> understand, it somehow tries to convert to a decimal(16,8) BEFORE
> filtering the nulls and the non-numeric out. (Keep in mind that the
> actual table has over 1.5Million records).

This might help:

SELECT SUM(CASE WHEN isnumeric(F2)
THEN convert(decimal(16, 8)), F2
ELSE 0
END) MySUM
FROM T1
WHERE isnumeric(F2) = 1

Beware that isnumeric may give you false positives. Not all strings
that causes isnumeric to return 1 are convertible to decimal.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp