MultiServer SQL 2000 SP3 Administration

Hello,
I am trying to set up multiadministration server, since my company has 2
central cluster servers, with SQL Server 2000 SP3a, and 80 local sql server
2000 SP3a.
I have recently upgraded a central to SP3, and did the same for a local sql
server. Both are in the same domain, started by an special administrator
account for SQL Server services (both SQL Server service and SQL Agent).
I'm thus using Windows authentification. I tried to set up both server for
multiadministration, using SQL queries :
==> EXEC xp_sqlagent_msx_account N'SET', N'', N'', N''
to run multiadministration server under SQL agent account, and then
==>
EXEC sp_grantlogin 'MyDomain\MyUser'
USE msdb
EXEC sp_adduser 'MyDomain\MyUser', MyDomain\MyUser', 'TargetServersRole'
on each server, both central and target (local).
Then I try to set up multiadministration using EM, but the 'Specify MSX
Account' step fails, saying the account I'm using is not a valid MSX Account.
I can't find any more help on forums and BOL.
Your help will be much appreciated...This message was posted a few weeks
ago, but no answer on the SQL server tools forum. Thanks a lot for your help.
Xavier MarcatelCheck the section on "Multi-Server Administration" at this link:
http://www.sqlservercentral.com/columnists/bkelley/sp3coresecurity.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
news:63E1D6DA-68CB-4EAA-88FC-4462FBF9C5F4@.microsoft.com...
> Hello,
> I am trying to set up multiadministration server, since my company has 2
> central cluster servers, with SQL Server 2000 SP3a, and 80 local sql
> server
> 2000 SP3a.
> I have recently upgraded a central to SP3, and did the same for a local
> sql
> server. Both are in the same domain, started by an special administrator
> account for SQL Server services (both SQL Server service and SQL Agent).
> I'm thus using Windows authentification. I tried to set up both server for
> multiadministration, using SQL queries :
> ==> EXEC xp_sqlagent_msx_account N'SET', N'', N'', N''
> to run multiadministration server under SQL agent account, and then
> ==>
> EXEC sp_grantlogin 'MyDomain\MyUser'
> USE msdb
> EXEC sp_adduser 'MyDomain\MyUser', MyDomain\MyUser', 'TargetServersRole'
> on each server, both central and target (local).
> Then I try to set up multiadministration using EM, but the 'Specify MSX
> Account' step fails, saying the account I'm using is not a valid MSX
> Account.
> I can't find any more help on forums and BOL.
> Your help will be much appreciated...This message was posted a few weeks
> ago, but no answer on the SQL server tools forum. Thanks a lot for your
> help.
> Xavier Marcatel
>

MultiServer SQL 2000 SP3 Administration

Hello,
I am trying to set up multiadministration server, since my company has 2
central cluster servers, with SQL Server 2000 SP3a, and 80 local sql server
2000 SP3a.
I have recently upgraded a central to SP3, and did the same for a local sql
server. Both are in the same domain, started by an special administrator
account for SQL Server services (both SQL Server service and SQL Agent).
I'm thus using Windows authentification. I tried to set up both server for
multiadministration, using SQL queries :
==> EXEC xp_sqlagent_msx_account N'SET', N'', N'', N''
to run multiadministration server under SQL agent account, and then
==>
EXEC sp_grantlogin 'MyDomain\MyUser'
USE msdb
EXEC sp_adduser 'MyDomain\MyUser', MyDomain\MyUser', 'TargetServersRole'
on each server, both central and target (local).
Then I try to set up multiadministration using EM, but the 'Specify MSX
Account' step fails, saying the account I'm using is not a valid MSX Account
.
I can't find any more help on forums and BOL.
Your help will be much appreciated...This message was posted a few weeks
ago, but no answer on the SQL server tools forum. Thanks a lot for your help
.
Xavier MarcatelCheck the section on "Multi-Server Administration" at this link:
http://www.sqlservercentral.com/col...oresecurity.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
news:63E1D6DA-68CB-4EAA-88FC-4462FBF9C5F4@.microsoft.com...
> Hello,
> I am trying to set up multiadministration server, since my company has 2
> central cluster servers, with SQL Server 2000 SP3a, and 80 local sql
> server
> 2000 SP3a.
> I have recently upgraded a central to SP3, and did the same for a local
> sql
> server. Both are in the same domain, started by an special administrator
> account for SQL Server services (both SQL Server service and SQL Agent).
> I'm thus using Windows authentification. I tried to set up both server for
> multiadministration, using SQL queries :
> ==> EXEC xp_sqlagent_msx_account N'SET', N'', N'', N''
> to run multiadministration server under SQL agent account, and then
> ==>
> EXEC sp_grantlogin 'MyDomain\MyUser'
> USE msdb
> EXEC sp_adduser 'MyDomain\MyUser', MyDomain\MyUser', 'TargetServersRole'
> on each server, both central and target (local).
> Then I try to set up multiadministration using EM, but the 'Specify MSX
> Account' step fails, saying the account I'm using is not a valid MSX
> Account.
> I can't find any more help on forums and BOL.
> Your help will be much appreciated...This message was posted a few weeks
> ago, but no answer on the SQL server tools forum. Thanks a lot for your
> help.
> Xavier Marcatel
>

Multiserver Job

hello
when we are trying to execute a multiserver job the following error is coming.can u please suggest the exact solution for this

Blocking error preventing further downloads: The specified @.database_name ('ibbflorida') does not exist. [SQLSTATE 42000] (Error 14262) Unable to create the local version of MSX job '0x6160950EA825544CA777E978E09ECC59'. [SQLSTATE 42000] (Error 50000)

thank you.my guess is that your job is referencing a database called 'IBBFLORIDA' and on one of your servers this database does not exist.|||Thanks for responding MR.Paul.

The database Ibbflorida is existing in one of my server but still it is giving error i could not understand what is the problem|||you didn't explain what your job does or attaach any code around this db reference so I am guessing here...

If ther should only be one copy of IBBFlorida tha I suspect that when your job runs on the linked server, the linked server incorrectly assums the IBBFlorida db is local. What happens if you add a server reference. If IBBFlorida is on ServerA and the job is running on ServerB then change all references to IBBFlorida to ServerA.IBBFlorida.|||hi paul
iam sending u the code for your convenience plz check it here i used db1, db2 in place of original databases names is there any problem with procedure.

thank you once again

CREATE procedure dbo.SP_IU_BALANCES
as
DECLARE
@.LV1 VARCHAR(255),
@.LV2 VARCHAR(255),
@.LV3 varchar(9)

declare TABLECURSOR CURSOR
FOR
select F3,F4,F1 from SERVER1.DB1.dbo.TABLE1
OPEN TABLECURSOR
FETCH NEXT FROM TABLECURSOR INTO @.LV1,@.LV2,@.LV3
WHILE @.@.FETCH_STATUS=0
BEGIN
update SERVER2.DB2.dbo.TABLE2
set TABLE2F1 = @.LV1,
TABLE2F2 = @.LV2
where TABLE2F3=@.LV3
FETCH NEXT FROM TABLECURSOR INTO @.LV1,@.LV2,@.LV3
END;

CLOSE TABLECURSOR;
DEALLOCATE TABLECURSOR;

insert into SERVER1.DB1.dbo.TABLE1(F1, F2, F3, F4, F5)
(select F1, getdate() +1 , F3, 0, F3
from SERVER1.DB1.dbo.TABLE1 where F2 = getdate())|||when you execute this are you still seeing the error from your original post?|||procedure is working fine when we executed manually. but when the same procedure is called in the job then it is giving the error which i mentioned before|||I am drwaing a blank on this one, I will continue to research the problem.

Anyone else have an idea?

MultiServer Administration with SQL 2005

Hiya, Does anyone know if SQL MultiServer Administration is compatible using SQL 2005 as the Master and 2000 sp4 as the target. I am getting compatibility errors between these versions when trying to register a Target Server

No, they have to be on the same version.

Multiserver Administration Master Server (MSX)

Hi
I am trying to delete some jobs on a SQL Server 2000
database that I have taken over and I am getting the
following error : Error 14274 - Cannot delete a job that
originated from an MSX server.
We only have one server in our organisation and I have
checked and it is not set up as an MSX server.
Please could someone tell me how I can delete these jobs.
Many thanksIt sounds like the server has been renamed at some point. You need to update
sysjobs to reflect the current server name in the originating_server column.
You can use the following procedure to do this
http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Philip" <anonymous@.discussions.microsoft.com> wrote in message
news:07c001c3d423$352cfaf0$a301280a@.phx.gbl...
> Hi
> I am trying to delete some jobs on a SQL Server 2000
> database that I have taken over and I am getting the
> following error : Error 14274 - Cannot delete a job that
> originated from an MSX server.
> We only have one server in our organisation and I have
> checked and it is not set up as an MSX server.
> Please could someone tell me how I can delete these jobs.
> Many thanks
>sql

Multiserver Administration

I want to set up Multiserver Administration.
The problem I encounter is a firewall between the master server and some
target servers.
What ports need to be set open to make this possible ?Hi Ren

See http://support.microsoft.com/defaul...kb;en-us;287932

John
"Ren Dohmen" <reply@.newsgroup.please> wrote in message
news:pan.2005.10.28.12.41.47.521258@.newsgroup.plea se...
>I want to set up Multiserver Administration.
> The problem I encounter is a firewall between the master server and some
> target servers.
> What ports need to be set open to make this possible ?

Multiserver administratie on SQL2005

Hello,
does anyone has managed to get MultiServer jobs working on SQL2005.
I have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP
server and DNS server. The two others are SQL2005, using the same
service account that has full administrative rights. I log on also as
administrator when I try to make one of the SQL2005 the master server.
During enlisting of the target server on the master server I get the
error that the target server could not logon on the master server
(altough its service account has full admin rights on the master server)
Any suggestions
Thanks a lot in advance
Marc MertensMarc
Do you mean to run a job on the source server that does the work on the
destination? Linked servers?
"Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hello,
> does anyone has managed to get MultiServer jobs working on SQL2005. I
> have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP server
> and DNS server. The two others are SQL2005, using the same service account
> that has full administrative rights. I log on also as administrator when I
> try to make one of the SQL2005 the master server. During enlisting of the
> target server on the master server I get the error that the target server
> could not logon on the master server (altough its service account has full
> admin rights on the master server)
> Any suggestions
> Thanks a lot in advance
> Marc Mertens|||No, MultiServer administration means that you create a multserver job on
a master server that is downloaded on target servers and executed there.
You enable multiserver administration by right clicking on the SQL
Server agent and choosing the correct menu entry in All Tasks. This is a
feature already available on a SQL2000 (where it worked without a
problem), but it does not seems to work anymore in SQL2005 (at least I
can not get it working).
Marc
Uri Dimant wrote:
> Marc
> Do you mean to run a job on the source server that does the work on the
> destination? Linked servers?
>
> "Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
> news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
>

Multiserver administratie on SQL2005

Hello,
does anyone has managed to get MultiServer jobs working on SQL2005.
I have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP
server and DNS server. The two others are SQL2005, using the same
service account that has full administrative rights. I log on also as
administrator when I try to make one of the SQL2005 the master server.
During enlisting of the target server on the master server I get the
error that the target server could not logon on the master server
(altough its service account has full admin rights on the master server)
Any suggestions
Thanks a lot in advance
Marc MertensMarc
Do you mean to run a job on the source server that does the work on the
destination? Linked servers?
"Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hello,
> does anyone has managed to get MultiServer jobs working on SQL2005. I
> have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP server
> and DNS server. The two others are SQL2005, using the same service account
> that has full administrative rights. I log on also as administrator when I
> try to make one of the SQL2005 the master server. During enlisting of the
> target server on the master server I get the error that the target server
> could not logon on the master server (altough its service account has full
> admin rights on the master server)
> Any suggestions
> Thanks a lot in advance
> Marc Mertens|||No, MultiServer administration means that you create a multserver job on
a master server that is downloaded on target servers and executed there.
You enable multiserver administration by right clicking on the SQL
Server agent and choosing the correct menu entry in All Tasks. This is a
feature already available on a SQL2000 (where it worked without a
problem), but it does not seems to work anymore in SQL2005 (at least I
can not get it working).
Marc
Uri Dimant wrote:
> Marc
> Do you mean to run a job on the source server that does the work on the
> destination? Linked servers?
>
> "Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
> news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> does anyone has managed to get MultiServer jobs working on SQL2005. I
>> have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP server
>> and DNS server. The two others are SQL2005, using the same service account
>> that has full administrative rights. I log on also as administrator when I
>> try to make one of the SQL2005 the master server. During enlisting of the
>> target server on the master server I get the error that the target server
>> could not logon on the master server (altough its service account has full
>> admin rights on the master server)
>> Any suggestions
>> Thanks a lot in advance
>> Marc Mertens
>