Hi to all
i have A LOT of stored procedures that i have to encrypt. There is any nice way to do it automatically without having to ALTER them with the ENCRYPTION keyword one by one?
Thanx in advanceI don't believe there is another way to do it. BOL makes it very clear the syntax required for altering a SP.
Good luck and I hope you have plenty of time on your hand.|||can i say "oh sh*t" ? :-)
i expected that anyway...just need to be sure of it
thanx|||I think you will be saying on S**t for every one you have to do manually!
Just remember to backup them up safely becaus once encrypted... you know the rest!
Good luck and have fun. Perhaps you could use this weekend to do it?! :)|||Every encrypted procedure can be decrypted, so if you just want to protect the code by doing that, don't waste your time. The code can be easily extracted even if is "encrypted".
Originally posted by Crespo-n00b
I think you will be saying on S**t for every one you have to do manually!
Just remember to backup them up safely becaus once encrypted... you know the rest!
Good luck and have fun. Perhaps you could use this weekend to do it?! :)|||mmm...not so sure of it. the only method to decrypt a sql server encryption should the the brute-force method, since encryption is irreversible.
am i wrong? if yes can you show the proof?|||Well, take a look at
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
Originally posted by manowar
mmm...not so sure of it. the only method to decrypt a sql server encryption should the the brute-force method, since encryption is irreversible.
am i wrong? if yes can you show the proof?
Showing posts with label alter. Show all posts
Showing posts with label alter. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
Enabling service broker
SQL2K5 SP1
I found this much in BOL...
alter database msdb
set ENABLE_BROKER
But when I run this on either of my 2 test servers the query never
completes...I let it run all weekend, it was still running this morning.
Am I missing something in my install? Has anyone ever seen this?
I am supposed to be getting our backup plans into place today and still
cannot get the operator alert tasks working because of this. Any help is
graciously appreciated!!See if this helps:
http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
Chances are you are logged into the database when you are trying to run the
command so it will run until everyone logs out.
--
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
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> SQL2K5 SP1
> I found this much in BOL...
> alter database msdb
> set ENABLE_BROKER
>
> But when I run this on either of my 2 test servers the query never
> completes...I let it run all weekend, it was still running this morning.
> Am I missing something in my install? Has anyone ever seen this?
>
> I am supposed to be getting our backup plans into place today and still
> cannot get the operator alert tasks working because of this. Any help is
> graciously appreciated!!
>|||Hmm...tried that and got this error:
Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does not
match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I force this GUID to match?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
> See if this helps:
> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
> Chances are you are logged into the database when you are trying to run
> the command so it will run until everyone logs out.
> --
> 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
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this morning.
>> Am I missing something in my install? Has anyone ever seen this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help is
>> graciously appreciated!!
>>
>|||Used the SET NEW_BROKER
Although, I DID follow procedure on this db. msdb was backed up from one
server and restored on this one. This is proper as I was told.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Hmm...tried that and got this error:
> Msg 9776, Level 16, State 1, Line 1
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
> not match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I force this GUID to match?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> 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
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help
>> is graciously appreciated!!
>>
>>
>|||Moving system datbases ([master], [model], [temp] and [msdb]) between
instances of the is never proper, no matter the steps involved.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
> Used the SET NEW_BROKER
> Although, I DID follow procedure on this db. msdb was backed up from one
> server and restored on this one. This is proper as I was told.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> 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
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help
>> is graciously appreciated!!
>>
>>
>>
>|||But this is supposedly the only way to move maintenance plans from one
server to the other WHILE RETAINING the ability to modify them graphically.
If you import/export through integration services it never allows you to
use the designer on the resulting package.
Is there some other way around this restriction? I need to design these
backup plans on my development machine and then deploy them. But they
should still be maintainable from the server no?
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Moving system datbases ([master], [model], [temp] and [msdb]) between
> instances of the is never proper, no matter the steps involved.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>> Used the SET NEW_BROKER
>> Although, I DID follow procedure on this db. msdb was backed up from one
>> server and restored on this one. This is proper as I was told.
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> 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
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and
>> still cannot get the operator alert tasks working because of this.
>> Any help is graciously appreciated!!
>>
>>
>>
>>
>|||I know KB224071 gives steps on how to move system databases, but those steps
ignore new functionality, like server level event notifications, dbMail and
Service Broker. Not to mention any usage of the secret storage facilities
(keys, certificates, encrypted data)...
What you did (NEW_BROKER) will solve the issue of starting up the broker in
msdb after a move, but at the cost of loosing any active dialog in in the
database, thus loosing any pending mail sent through dbMail and any pending
servel level notifications. I understand that is highly questionable if you
would have such active items and still move the database...
You have to be aware that when moving the msdb you are moving and
ovewrwritting way more than just your maintenance plan graphical designer
state. By overwriting the other's server msdb with yours, you are
overwriting the state of any feature that relies on msdb, and there are
plenty (I just mentioned server level event notifications and dbMail).
You could use the SQL Feedback at
https://connect.microsoft.com/SQLServer/Feedback to mention the issue that
the graphical designer uses a system database to store it's state, thus
tying it to a specific instance.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OABOpZ%23FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> But this is supposedly the only way to move maintenance plans from one
> server to the other WHILE RETAINING the ability to modify them
> graphically. If you import/export through integration services it never
> allows you to use the designer on the resulting package.
> Is there some other way around this restriction? I need to design these
> backup plans on my development machine and then deploy them. But they
> should still be maintainable from the server no?
>
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
>> Moving system datbases ([master], [model], [temp] and [msdb]) between
>> instances of the is never proper, no matter the steps involved.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>> Used the SET NEW_BROKER
>> Although, I DID follow procedure on this db. msdb was backed up from
>> one server and restored on this one. This is proper as I was told.
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to
>> run the command so it will run until everyone logs out.
>> --
>> 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
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and
>> still cannot get the operator alert tasks working because of this.
>> Any help is graciously appreciated!!
>>
>>
>>
>>
>>
>
I found this much in BOL...
alter database msdb
set ENABLE_BROKER
But when I run this on either of my 2 test servers the query never
completes...I let it run all weekend, it was still running this morning.
Am I missing something in my install? Has anyone ever seen this?
I am supposed to be getting our backup plans into place today and still
cannot get the operator alert tasks working because of this. Any help is
graciously appreciated!!See if this helps:
http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
Chances are you are logged into the database when you are trying to run the
command so it will run until everyone logs out.
--
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
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> SQL2K5 SP1
> I found this much in BOL...
> alter database msdb
> set ENABLE_BROKER
>
> But when I run this on either of my 2 test servers the query never
> completes...I let it run all weekend, it was still running this morning.
> Am I missing something in my install? Has anyone ever seen this?
>
> I am supposed to be getting our backup plans into place today and still
> cannot get the operator alert tasks working because of this. Any help is
> graciously appreciated!!
>|||Hmm...tried that and got this error:
Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does not
match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I force this GUID to match?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
> See if this helps:
> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
> Chances are you are logged into the database when you are trying to run
> the command so it will run until everyone logs out.
> --
> 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
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this morning.
>> Am I missing something in my install? Has anyone ever seen this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help is
>> graciously appreciated!!
>>
>|||Used the SET NEW_BROKER
Although, I DID follow procedure on this db. msdb was backed up from one
server and restored on this one. This is proper as I was told.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Hmm...tried that and got this error:
> Msg 9776, Level 16, State 1, Line 1
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
> not match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I force this GUID to match?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> 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
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help
>> is graciously appreciated!!
>>
>>
>|||Moving system datbases ([master], [model], [temp] and [msdb]) between
instances of the is never proper, no matter the steps involved.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
> Used the SET NEW_BROKER
> Although, I DID follow procedure on this db. msdb was backed up from one
> server and restored on this one. This is proper as I was told.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> 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
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help
>> is graciously appreciated!!
>>
>>
>>
>|||But this is supposedly the only way to move maintenance plans from one
server to the other WHILE RETAINING the ability to modify them graphically.
If you import/export through integration services it never allows you to
use the designer on the resulting package.
Is there some other way around this restriction? I need to design these
backup plans on my development machine and then deploy them. But they
should still be maintainable from the server no?
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Moving system datbases ([master], [model], [temp] and [msdb]) between
> instances of the is never proper, no matter the steps involved.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>> Used the SET NEW_BROKER
>> Although, I DID follow procedure on this db. msdb was backed up from one
>> server and restored on this one. This is proper as I was told.
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> 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
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and
>> still cannot get the operator alert tasks working because of this.
>> Any help is graciously appreciated!!
>>
>>
>>
>>
>|||I know KB224071 gives steps on how to move system databases, but those steps
ignore new functionality, like server level event notifications, dbMail and
Service Broker. Not to mention any usage of the secret storage facilities
(keys, certificates, encrypted data)...
What you did (NEW_BROKER) will solve the issue of starting up the broker in
msdb after a move, but at the cost of loosing any active dialog in in the
database, thus loosing any pending mail sent through dbMail and any pending
servel level notifications. I understand that is highly questionable if you
would have such active items and still move the database...
You have to be aware that when moving the msdb you are moving and
ovewrwritting way more than just your maintenance plan graphical designer
state. By overwriting the other's server msdb with yours, you are
overwriting the state of any feature that relies on msdb, and there are
plenty (I just mentioned server level event notifications and dbMail).
You could use the SQL Feedback at
https://connect.microsoft.com/SQLServer/Feedback to mention the issue that
the graphical designer uses a system database to store it's state, thus
tying it to a specific instance.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OABOpZ%23FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> But this is supposedly the only way to move maintenance plans from one
> server to the other WHILE RETAINING the ability to modify them
> graphically. If you import/export through integration services it never
> allows you to use the designer on the resulting package.
> Is there some other way around this restriction? I need to design these
> backup plans on my development machine and then deploy them. But they
> should still be maintainable from the server no?
>
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
>> Moving system datbases ([master], [model], [temp] and [msdb]) between
>> instances of the is never proper, no matter the steps involved.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>> Used the SET NEW_BROKER
>> Although, I DID follow procedure on this db. msdb was backed up from
>> one server and restored on this one. This is proper as I was told.
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to
>> run the command so it will run until everyone logs out.
>> --
>> 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
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and
>> still cannot get the operator alert tasks working because of this.
>> Any help is graciously appreciated!!
>>
>>
>>
>>
>>
>
Enabling service broker
SQL2K5 SP1
I found this much in BOL...
alter database msdb
set ENABLE_BROKER
But when I run this on either of my 2 test servers the query never
completes...I let it run all weekend, it was still running this morning.
Am I missing something in my install? Has anyone ever seen this?
I am supposed to be getting our backup plans into place today and still
cannot get the operator alert tasks working because of this. Any help is
graciously appreciated!!See if this helps:
http://blogs.msdn.com/remusrusanu/a.../30/519685.aspx
Chances are you are logged into the database when you are trying to run the
command so it will run until everyone logs out.
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
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> SQL2K5 SP1
> I found this much in BOL...
> alter database msdb
> set ENABLE_BROKER
>
> But when I run this on either of my 2 test servers the query never
> completes...I let it run all weekend, it was still running this morning.
> Am I missing something in my install? Has anyone ever seen this?
>
> I am supposed to be getting our backup plans into place today and still
> cannot get the operator alert tasks working because of this. Any help is
> graciously appreciated!!
>|||Hmm...tried that and got this error:
Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does not
match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I force this GUID to match?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
> See if this helps:
> http://blogs.msdn.com/remusrusanu/a.../30/519685.aspx
> Chances are you are logged into the database when you are trying to run
> the command so it will run until everyone logs out.
> --
> 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
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>
I found this much in BOL...
alter database msdb
set ENABLE_BROKER
But when I run this on either of my 2 test servers the query never
completes...I let it run all weekend, it was still running this morning.
Am I missing something in my install? Has anyone ever seen this?
I am supposed to be getting our backup plans into place today and still
cannot get the operator alert tasks working because of this. Any help is
graciously appreciated!!See if this helps:
http://blogs.msdn.com/remusrusanu/a.../30/519685.aspx
Chances are you are logged into the database when you are trying to run the
command so it will run until everyone logs out.
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
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> SQL2K5 SP1
> I found this much in BOL...
> alter database msdb
> set ENABLE_BROKER
>
> But when I run this on either of my 2 test servers the query never
> completes...I let it run all weekend, it was still running this morning.
> Am I missing something in my install? Has anyone ever seen this?
>
> I am supposed to be getting our backup plans into place today and still
> cannot get the operator alert tasks working because of this. Any help is
> graciously appreciated!!
>|||Hmm...tried that and got this error:
Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does not
match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I force this GUID to match?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
> See if this helps:
> http://blogs.msdn.com/remusrusanu/a.../30/519685.aspx
> Chances are you are logged into the database when you are trying to run
> the command so it will run until everyone logs out.
> --
> 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
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>
Enabling service broker
SQL2K5 SP1
I found this much in BOL...
alter database msdb
set ENABLE_BROKER
But when I run this on either of my 2 test servers the query never
completes...I let it run all weekend, it was still running this morning.
Am I missing something in my install? Has anyone ever seen this?
I am supposed to be getting our backup plans into place today and still
cannot get the operator alert tasks working because of this. Any help is
graciously appreciated!!
See if this helps:
http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
Chances are you are logged into the database when you are trying to run the
command so it will run until everyone logs out.
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
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> SQL2K5 SP1
> I found this much in BOL...
> alter database msdb
> set ENABLE_BROKER
>
> But when I run this on either of my 2 test servers the query never
> completes...I let it run all weekend, it was still running this morning.
> Am I missing something in my install? Has anyone ever seen this?
>
> I am supposed to be getting our backup plans into place today and still
> cannot get the operator alert tasks working because of this. Any help is
> graciously appreciated!!
>
|||Hmm...tried that and got this error:
Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does not
match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I force this GUID to match?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
> See if this helps:
> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
> Chances are you are logged into the database when you are trying to run
> the command so it will run until everyone logs out.
> --
> 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
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>
|||Used the SET NEW_BROKER
Although, I DID follow procedure on this db. msdb was backed up from one
server and restored on this one. This is proper as I was told.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Hmm...tried that and got this error:
> Msg 9776, Level 16, State 1, Line 1
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
> not match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I force this GUID to match?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>
|||Moving system datbases ([master], [model], [temp] and [msdb]) between
instances of the is never proper, no matter the steps involved.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
> Used the SET NEW_BROKER
> Although, I DID follow procedure on this db. msdb was backed up from one
> server and restored on this one. This is proper as I was told.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>
|||But this is supposedly the only way to move maintenance plans from one
server to the other WHILE RETAINING the ability to modify them graphically.
If you import/export through integration services it never allows you to
use the designer on the resulting package.
Is there some other way around this restriction? I need to design these
backup plans on my development machine and then deploy them. But they
should still be maintainable from the server no?
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Moving system datbases ([master], [model], [temp] and [msdb]) between
> instances of the is never proper, no matter the steps involved.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||I know KB224071 gives steps on how to move system databases, but those steps
ignore new functionality, like server level event notifications, dbMail and
Service Broker. Not to mention any usage of the secret storage facilities
(keys, certificates, encrypted data)...
What you did (NEW_BROKER) will solve the issue of starting up the broker in
msdb after a move, but at the cost of loosing any active dialog in in the
database, thus loosing any pending mail sent through dbMail and any pending
servel level notifications. I understand that is highly questionable if you
would have such active items and still move the database...
You have to be aware that when moving the msdb you are moving and
ovewrwritting way more than just your maintenance plan graphical designer
state. By overwriting the other's server msdb with yours, you are
overwriting the state of any feature that relies on msdb, and there are
plenty (I just mentioned server level event notifications and dbMail).
You could use the SQL Feedback at
https://connect.microsoft.com/SQLServer/Feedback to mention the issue that
the graphical designer uses a system database to store it's state, thus
tying it to a specific instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OABOpZ%23FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> But this is supposedly the only way to move maintenance plans from one
> server to the other WHILE RETAINING the ability to modify them
> graphically. If you import/export through integration services it never
> allows you to use the designer on the resulting package.
> Is there some other way around this restriction? I need to design these
> backup plans on my development machine and then deploy them. But they
> should still be maintainable from the server no?
>
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
>
I found this much in BOL...
alter database msdb
set ENABLE_BROKER
But when I run this on either of my 2 test servers the query never
completes...I let it run all weekend, it was still running this morning.
Am I missing something in my install? Has anyone ever seen this?
I am supposed to be getting our backup plans into place today and still
cannot get the operator alert tasks working because of this. Any help is
graciously appreciated!!
See if this helps:
http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
Chances are you are logged into the database when you are trying to run the
command so it will run until everyone logs out.
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
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> SQL2K5 SP1
> I found this much in BOL...
> alter database msdb
> set ENABLE_BROKER
>
> But when I run this on either of my 2 test servers the query never
> completes...I let it run all weekend, it was still running this morning.
> Am I missing something in my install? Has anyone ever seen this?
>
> I am supposed to be getting our backup plans into place today and still
> cannot get the operator alert tasks working because of this. Any help is
> graciously appreciated!!
>
|||Hmm...tried that and got this error:
Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does not
match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I force this GUID to match?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
> See if this helps:
> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
> Chances are you are logged into the database when you are trying to run
> the command so it will run until everyone logs out.
> --
> 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
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>
|||Used the SET NEW_BROKER
Although, I DID follow procedure on this db. msdb was backed up from one
server and restored on this one. This is proper as I was told.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Hmm...tried that and got this error:
> Msg 9776, Level 16, State 1, Line 1
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
> not match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I force this GUID to match?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>
|||Moving system datbases ([master], [model], [temp] and [msdb]) between
instances of the is never proper, no matter the steps involved.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
> Used the SET NEW_BROKER
> Although, I DID follow procedure on this db. msdb was backed up from one
> server and restored on this one. This is proper as I was told.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>
|||But this is supposedly the only way to move maintenance plans from one
server to the other WHILE RETAINING the ability to modify them graphically.
If you import/export through integration services it never allows you to
use the designer on the resulting package.
Is there some other way around this restriction? I need to design these
backup plans on my development machine and then deploy them. But they
should still be maintainable from the server no?
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Moving system datbases ([master], [model], [temp] and [msdb]) between
> instances of the is never proper, no matter the steps involved.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||I know KB224071 gives steps on how to move system databases, but those steps
ignore new functionality, like server level event notifications, dbMail and
Service Broker. Not to mention any usage of the secret storage facilities
(keys, certificates, encrypted data)...
What you did (NEW_BROKER) will solve the issue of starting up the broker in
msdb after a move, but at the cost of loosing any active dialog in in the
database, thus loosing any pending mail sent through dbMail and any pending
servel level notifications. I understand that is highly questionable if you
would have such active items and still move the database...
You have to be aware that when moving the msdb you are moving and
ovewrwritting way more than just your maintenance plan graphical designer
state. By overwriting the other's server msdb with yours, you are
overwriting the state of any feature that relies on msdb, and there are
plenty (I just mentioned server level event notifications and dbMail).
You could use the SQL Feedback at
https://connect.microsoft.com/SQLServer/Feedback to mention the issue that
the graphical designer uses a system database to store it's state, thus
tying it to a specific instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OABOpZ%23FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> But this is supposedly the only way to move maintenance plans from one
> server to the other WHILE RETAINING the ability to modify them
> graphically. If you import/export through integration services it never
> allows you to use the designer on the resulting package.
> Is there some other way around this restriction? I need to design these
> backup plans on my development machine and then deploy them. But they
> should still be maintainable from the server no?
>
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
>
Sunday, February 26, 2012
Enabling Diagram Support in SQL 2005
I get the message I must Alter Authorization. I have not been able to get
this to work. If you have successfully done this, please let me know what
you did.
Note: These are databases which have been restored from SQL 2000 backups,
if that is of any consequence.
Thanks
Migrating to SQL 2005
Since SQL 2005 is still in beta, you'll want to post this in the Community
Newsgroups:
http://communities.microsoft.com/new...r2005&slcid=us
--Brian
(Please reply to the newsgroups only.)
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:1E015795-FB82-41E7-8A39-C8B347860D35@.microsoft.com...
>I get the message I must Alter Authorization. I have not been able to get
> this to work. If you have successfully done this, please let me know what
> you did.
> Note: These are databases which have been restored from SQL 2000 backups,
> if that is of any consequence.
> Thanks
> --
> Migrating to SQL 2005
this to work. If you have successfully done this, please let me know what
you did.
Note: These are databases which have been restored from SQL 2000 backups,
if that is of any consequence.
Thanks
Migrating to SQL 2005
Since SQL 2005 is still in beta, you'll want to post this in the Community
Newsgroups:
http://communities.microsoft.com/new...r2005&slcid=us
--Brian
(Please reply to the newsgroups only.)
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:1E015795-FB82-41E7-8A39-C8B347860D35@.microsoft.com...
>I get the message I must Alter Authorization. I have not been able to get
> this to work. If you have successfully done this, please let me know what
> you did.
> Note: These are databases which have been restored from SQL 2000 backups,
> if that is of any consequence.
> Thanks
> --
> Migrating to SQL 2005
Enabling Diagram Support in SQL 2005
I get the message I must Alter Authorization. I have not been able to get
this to work. If you have successfully done this, please let me know what
you did.
Note: These are databases which have been restored from SQL 2000 backups,
if that is of any consequence.
Thanks
--
Migrating to SQL 2005Since SQL 2005 is still in beta, you'll want to post this in the Community
Newsgroups:
http://communities.microsoft.com/ne...lcid=us
--Brian
(Please reply to the newsgroups only.)
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:1E015795-FB82-41E7-8A39-C8B347860D35@.microsoft.com...
>I get the message I must Alter Authorization. I have not been able to get
> this to work. If you have successfully done this, please let me know what
> you did.
> Note: These are databases which have been restored from SQL 2000 backups,
> if that is of any consequence.
> Thanks
> --
> Migrating to SQL 2005
this to work. If you have successfully done this, please let me know what
you did.
Note: These are databases which have been restored from SQL 2000 backups,
if that is of any consequence.
Thanks
--
Migrating to SQL 2005Since SQL 2005 is still in beta, you'll want to post this in the Community
Newsgroups:
http://communities.microsoft.com/ne...lcid=us
--Brian
(Please reply to the newsgroups only.)
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:1E015795-FB82-41E7-8A39-C8B347860D35@.microsoft.com...
>I get the message I must Alter Authorization. I have not been able to get
> this to work. If you have successfully done this, please let me know what
> you did.
> Note: These are databases which have been restored from SQL 2000 backups,
> if that is of any consequence.
> Thanks
> --
> Migrating to SQL 2005
Enabling Diagram Support in SQL 2005
I get the message I must Alter Authorization. I have not been able to get
this to work. If you have successfully done this, please let me know what
you did.
Note: These are databases which have been restored from SQL 2000 backups,
if that is of any consequence.
Thanks
--
Migrating to SQL 2005Since SQL 2005 is still in beta, you'll want to post this in the Community
Newsgroups:
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us
--
--Brian
(Please reply to the newsgroups only.)
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:1E015795-FB82-41E7-8A39-C8B347860D35@.microsoft.com...
>I get the message I must Alter Authorization. I have not been able to get
> this to work. If you have successfully done this, please let me know what
> you did.
> Note: These are databases which have been restored from SQL 2000 backups,
> if that is of any consequence.
> Thanks
> --
> Migrating to SQL 2005
this to work. If you have successfully done this, please let me know what
you did.
Note: These are databases which have been restored from SQL 2000 backups,
if that is of any consequence.
Thanks
--
Migrating to SQL 2005Since SQL 2005 is still in beta, you'll want to post this in the Community
Newsgroups:
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us
--
--Brian
(Please reply to the newsgroups only.)
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:1E015795-FB82-41E7-8A39-C8B347860D35@.microsoft.com...
>I get the message I must Alter Authorization. I have not been able to get
> this to work. If you have successfully done this, please let me know what
> you did.
> Note: These are databases which have been restored from SQL 2000 backups,
> if that is of any consequence.
> Thanks
> --
> Migrating to SQL 2005
Enable/Disable Trigger
I have a stored procedure disable and enable triggers on several tables.
What would be a workaround to using the alter table with disable and enable
triggers listed below.
Thanks,
CREATE PROCEDURE dbo.K_ManualMoves
as
BEGIN
alter table T_Moves
disable trigger iu_t_Moves
alter table T_Fees_Moves
disable trigger iu_t_Fees
Begin
Body Stored Procedure
End
alter table T_Moves
enable trigger iu_t_Moves
alter table T_Fees_Moves
enable trigger iu_t_Fees
End
GOWhy do you need a workaround, if you want to disable the trigger use Alter
table as you have done, Or is there some reason you do not want to disable
the triggers? Without seeing what the rest of the code is it is difficult to
understand your problem
Mike John
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:09A5FD91-8F76-4431-B17B-0F4D4FF85B24@.microsoft.com...
> I have a stored procedure disable and enable triggers on several tables.
> What would be a workaround to using the alter table with disable and
> enable
> triggers listed below.
> Thanks,
> CREATE PROCEDURE dbo.K_ManualMoves
> as
> BEGIN
> alter table T_Moves
> disable trigger iu_t_Moves
> alter table T_Fees_Moves
> disable trigger iu_t_Fees
> Begin
> Body Stored Procedure
> End
> alter table T_Moves
> enable trigger iu_t_Moves
> alter table T_Fees_Moves
> enable trigger iu_t_Fees
> End
> GO|||That's how to do it.
I'm guessing your concern is that the triggers would be disabled while a
process that should fire them runs. In that case, the most solid option
is to have the triggers manage themselves, based on data being modified
(like a source column indicating what process changed the data), or
environment settings (like user or application) or something else
limited to the scope of the modification.
Joe K. wrote:
> I have a stored procedure disable and enable triggers on several tables.
> What would be a workaround to using the alter table with disable and enabl
e
> triggers listed below.
> Thanks,
> CREATE PROCEDURE dbo.K_ManualMoves
> as
> BEGIN
> alter table T_Moves
> disable trigger iu_t_Moves
> alter table T_Fees_Moves
> disable trigger iu_t_Fees
> Begin
> Body Stored Procedure
> End
> alter table T_Moves
> enable trigger iu_t_Moves
> alter table T_Fees_Moves
> enable trigger iu_t_Fees
> End
> GO|||Joe K. (Joe K.@.discussions.microsoft.com) writes:
> I have a stored procedure disable and enable triggers on several tables.
> What would be a workaround to using the alter table with disable and
> enable triggers listed below.
In the stored procedure create a temp table:
CREATE TABLE #trigger$disabled(a int NOT NULL)
In the trigger you would add
IF object_id('tempdb..#trigger$disabled') IS NOT NULL
RETURN
Even better you can disable only the part that you that you will violate.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
What would be a workaround to using the alter table with disable and enable
triggers listed below.
Thanks,
CREATE PROCEDURE dbo.K_ManualMoves
as
BEGIN
alter table T_Moves
disable trigger iu_t_Moves
alter table T_Fees_Moves
disable trigger iu_t_Fees
Begin
Body Stored Procedure
End
alter table T_Moves
enable trigger iu_t_Moves
alter table T_Fees_Moves
enable trigger iu_t_Fees
End
GOWhy do you need a workaround, if you want to disable the trigger use Alter
table as you have done, Or is there some reason you do not want to disable
the triggers? Without seeing what the rest of the code is it is difficult to
understand your problem
Mike John
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:09A5FD91-8F76-4431-B17B-0F4D4FF85B24@.microsoft.com...
> I have a stored procedure disable and enable triggers on several tables.
> What would be a workaround to using the alter table with disable and
> enable
> triggers listed below.
> Thanks,
> CREATE PROCEDURE dbo.K_ManualMoves
> as
> BEGIN
> alter table T_Moves
> disable trigger iu_t_Moves
> alter table T_Fees_Moves
> disable trigger iu_t_Fees
> Begin
> Body Stored Procedure
> End
> alter table T_Moves
> enable trigger iu_t_Moves
> alter table T_Fees_Moves
> enable trigger iu_t_Fees
> End
> GO|||That's how to do it.
I'm guessing your concern is that the triggers would be disabled while a
process that should fire them runs. In that case, the most solid option
is to have the triggers manage themselves, based on data being modified
(like a source column indicating what process changed the data), or
environment settings (like user or application) or something else
limited to the scope of the modification.
Joe K. wrote:
> I have a stored procedure disable and enable triggers on several tables.
> What would be a workaround to using the alter table with disable and enabl
e
> triggers listed below.
> Thanks,
> CREATE PROCEDURE dbo.K_ManualMoves
> as
> BEGIN
> alter table T_Moves
> disable trigger iu_t_Moves
> alter table T_Fees_Moves
> disable trigger iu_t_Fees
> Begin
> Body Stored Procedure
> End
> alter table T_Moves
> enable trigger iu_t_Moves
> alter table T_Fees_Moves
> enable trigger iu_t_Fees
> End
> GO|||Joe K. (Joe K.@.discussions.microsoft.com) writes:
> I have a stored procedure disable and enable triggers on several tables.
> What would be a workaround to using the alter table with disable and
> enable triggers listed below.
In the stored procedure create a temp table:
CREATE TABLE #trigger$disabled(a int NOT NULL)
In the trigger you would add
IF object_id('tempdb..#trigger$disabled') IS NOT NULL
RETURN
Even better you can disable only the part that you that you will violate.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Enable/Disable SQL Server login
Hi,
Is there a way that I can enable/disable logins using SQL Authentication? Or similar function like Oracle's ALTER user LOCK/UNLOCK.
Thanks & Regards.
DennisYes, you can disable/enable logins using:
ALTER LOGIN login_name DISABLE
ALTER LOGIN login_name ENABLE
This works with any login, not only SQL logins.
Thanks
Laurentiu
ENABLE/DISABLE primarykeys,foreignkeys & indexes
hi,
I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a table
I have tried
ALTER TABLE employees DISABLE PRIMARY KEY
but it shows the following error
Incorrect syntax near the keyword 'PRIMARY'.
can u help me?
Thanks,
SouraAs a primary key is supported by a unique index, you cannot disable it. A
unique index has to always enforce uniqueness.
Please check the proper syntax in the Books Online. DISABLE is only used for
triggers.
To disable a constraint, you must use the NOCHECK option. And the BOL
states:
{ CHECK | NOCHECK} CONSTRAINT
Specifies that constraint_name is enabled or disabled. When disabled, future
inserts or updates to the column are not validated against the constraint
conditions. This option can only be used with FOREIGN KEY and CHECK
constraints.
To no longer enforce the primary key's properties, you have to DROP the
constraint, which will drop the unique index.
Please see BOL for complete syntax details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:3CA0935E-35E9-46D4-8D2F-DD317FECFAB1@.microsoft.com...
> hi,
> I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a
> table
> I have tried
> ALTER TABLE employees DISABLE PRIMARY KEY
> but it shows the following error
> Incorrect syntax near the keyword 'PRIMARY'.
> can u help me?
> Thanks,
> Soura
>|||hi,
I need to DISABLE/ENABLE all the foreign keys in a DataBase
Is it possible through Query?
Can u help me?
Thanks
Soura
"Kalen Delaney" wrote:
> As a primary key is supported by a unique index, you cannot disable it. A
> unique index has to always enforce uniqueness.
> Please check the proper syntax in the Books Online. DISABLE is only used for
> triggers.
> To disable a constraint, you must use the NOCHECK option. And the BOL
> states:
> { CHECK | NOCHECK} CONSTRAINT
> Specifies that constraint_name is enabled or disabled. When disabled, future
> inserts or updates to the column are not validated against the constraint
> conditions. This option can only be used with FOREIGN KEY and CHECK
> constraints.
> To no longer enforce the primary key's properties, you have to DROP the
> constraint, which will drop the unique index.
> Please see BOL for complete syntax details.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:3CA0935E-35E9-46D4-8D2F-DD317FECFAB1@.microsoft.com...
> > hi,
> >
> > I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a
> > table
> >
> > I have tried
> > ALTER TABLE employees DISABLE PRIMARY KEY
> >
> > but it shows the following error
> > Incorrect syntax near the keyword 'PRIMARY'.
> >
> > can u help me?
> >
> > Thanks,
> > Soura
> >
>
>
I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a table
I have tried
ALTER TABLE employees DISABLE PRIMARY KEY
but it shows the following error
Incorrect syntax near the keyword 'PRIMARY'.
can u help me?
Thanks,
SouraAs a primary key is supported by a unique index, you cannot disable it. A
unique index has to always enforce uniqueness.
Please check the proper syntax in the Books Online. DISABLE is only used for
triggers.
To disable a constraint, you must use the NOCHECK option. And the BOL
states:
{ CHECK | NOCHECK} CONSTRAINT
Specifies that constraint_name is enabled or disabled. When disabled, future
inserts or updates to the column are not validated against the constraint
conditions. This option can only be used with FOREIGN KEY and CHECK
constraints.
To no longer enforce the primary key's properties, you have to DROP the
constraint, which will drop the unique index.
Please see BOL for complete syntax details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:3CA0935E-35E9-46D4-8D2F-DD317FECFAB1@.microsoft.com...
> hi,
> I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a
> table
> I have tried
> ALTER TABLE employees DISABLE PRIMARY KEY
> but it shows the following error
> Incorrect syntax near the keyword 'PRIMARY'.
> can u help me?
> Thanks,
> Soura
>|||hi,
I need to DISABLE/ENABLE all the foreign keys in a DataBase
Is it possible through Query?
Can u help me?
Thanks
Soura
"Kalen Delaney" wrote:
> As a primary key is supported by a unique index, you cannot disable it. A
> unique index has to always enforce uniqueness.
> Please check the proper syntax in the Books Online. DISABLE is only used for
> triggers.
> To disable a constraint, you must use the NOCHECK option. And the BOL
> states:
> { CHECK | NOCHECK} CONSTRAINT
> Specifies that constraint_name is enabled or disabled. When disabled, future
> inserts or updates to the column are not validated against the constraint
> conditions. This option can only be used with FOREIGN KEY and CHECK
> constraints.
> To no longer enforce the primary key's properties, you have to DROP the
> constraint, which will drop the unique index.
> Please see BOL for complete syntax details.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:3CA0935E-35E9-46D4-8D2F-DD317FECFAB1@.microsoft.com...
> > hi,
> >
> > I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a
> > table
> >
> > I have tried
> > ALTER TABLE employees DISABLE PRIMARY KEY
> >
> > but it shows the following error
> > Incorrect syntax near the keyword 'PRIMARY'.
> >
> > can u help me?
> >
> > Thanks,
> > Soura
> >
>
>
Enable/Disable primary keys,FK,indexes
hi,
I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a table
through query
I have tried
ALTER TABLE employees DISABLE PRIMARY KEY
but it shows the following error
Incorrect syntax near the keyword 'PRIMARY'.
can u help me?
Thanks,
SouraAnswered in first post of the exact same question...
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:7DFCB99D-2BD3-4964-A279-83E0A2683159@.microsoft.com...
> hi,
> I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a
> table
> through query
> I have tried
> ALTER TABLE employees DISABLE PRIMARY KEY
> but it shows the following error
> Incorrect syntax near the keyword 'PRIMARY'.
> can u help me?
> Thanks,
> Soura
>
I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a table
through query
I have tried
ALTER TABLE employees DISABLE PRIMARY KEY
but it shows the following error
Incorrect syntax near the keyword 'PRIMARY'.
can u help me?
Thanks,
SouraAnswered in first post of the exact same question...
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:7DFCB99D-2BD3-4964-A279-83E0A2683159@.microsoft.com...
> hi,
> I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a
> table
> through query
> I have tried
> ALTER TABLE employees DISABLE PRIMARY KEY
> but it shows the following error
> Incorrect syntax near the keyword 'PRIMARY'.
> can u help me?
> Thanks,
> Soura
>
Subscribe to:
Posts (Atom)