reg. Prob with replication

HI,
Im using Mssql 2000, We are migrating the server from one server to
another.
i will list out what i have done, so pls let me know what wrong i did
and how to solve.
1. install mssql s/w
2. restore master db
3. restore users db's.
4. Run the replication script (extracted from old server)
im getting the errors, as follows
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
Line 164
Invalid object name 'msdb..MSdistributiondbs'.
Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber, Line
95
The server 'ADCPWSQLZ7' is already a Subscriber.
.............
.......
pls help me to solve the error.
thks
if you had restored your distribution and msdb databases you might have had
a chance of getting this to work. At this point your best bet is to restore
the publication database again using the keep_replication switch, script out
the publications and subscriptions, then drop the publications and
subscriptions, disable and then re-enable replication, and then recreate
your publications and subscriptions again from the script.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"calms" <calmsqurill@.gmail.com> wrote in message
news:54ef32ea-e652-41ce-a1ad-f2b90e6cb2ab@.i29g2000prf.googlegroups.com...
> HI,
> Im using Mssql 2000, We are migrating the server from one server to
> another.
> i will list out what i have done, so pls let me know what wrong i did
> and how to solve.
> 1. install mssql s/w
> 2. restore master db
> 3. restore users db's.
> 4. Run the replication script (extracted from old server)
> im getting the errors, as follows
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
> Line 164
> Invalid object name 'msdb..MSdistributiondbs'.
> Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber, Line
> 95
> The server 'ADCPWSQLZ7' is already a Subscriber.
> ............
> ......
> pls help me to solve the error.
> thks

reg. Prob with replication

HI,
Im using Mssql 2000, We are migrating the server from one server to
another.
i will list out what i have done, so pls let me know what wrong i did
and how to solve.
1. install mssql s/w
2. restore master db
3. restore users db's.
4. Run the replication script (extracted from old server)
im getting the errors, as follows
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
Line 164
Invalid object name 'msdb..MSdistributiondbs'.
Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
Line
95
The server 'ADCPWSQLZ7' is already a Subscriber.
.............
.......
pls help me to solve the error.
thks
If you wanted a hope of getting this to work you should have restored the
msdb and distribution databases as well and then renamed the new server with
the name of the old server.
you need to disable replication and then recreate it and then try to re-run
your scripts on this server.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"calms" <calmsqurill@.gmail.com> wrote in message
news:ddb85046-94b1-4e43-a006-f3d3a24bca5a@.s12g2000prg.googlegroups.com...
> HI,
> Im using Mssql 2000, We are migrating the server from one server to
> another.
>
> i will list out what i have done, so pls let me know what wrong i did
> and how to solve.
>
> 1. install mssql s/w
> 2. restore master db
> 3. restore users db's.
> 4. Run the replication script (extracted from old server)
>
> im getting the errors, as follows
>
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
> Line 164
> Invalid object name 'msdb..MSdistributiondbs'.
> Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
> Line
> 95
> The server 'ADCPWSQLZ7' is already a Subscriber.
> ............
> ......
>
> pls help me to solve the error.
>
> thks
>
sql

reg. Prob with replication

HI,
Im using Mssql 2000, We are migrating the server from one server to
another.
i will list out what i have done, so pls let me know what wrong i did
and how to solve.
1. install mssql s/w
2. restore master db
3. restore users db's.
4. Run the replication script (extracted from old server)
im getting the errors, as follows
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
Line 164
Invalid object name 'msdb..MSdistributiondbs'.
Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
Line
95
The server 'ADCPWSQLZ7' is already a Subscriber.
............
......
pls help me to solve the error.
thksIf you wanted a hope of getting this to work you should have restored the
msdb and distribution databases as well and then renamed the new server with
the name of the old server.
you need to disable replication and then recreate it and then try to re-run
your scripts on this server.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"calms" <calmsqurill@.gmail.com> wrote in message
news:ddb85046-94b1-4e43-a006-f3d3a24bca5a@.s12g2000prg.googlegroups.com...
> HI,
> Im using Mssql 2000, We are migrating the server from one server to
> another.
>
> i will list out what i have done, so pls let me know what wrong i did
> and how to solve.
>
> 1. install mssql s/w
> 2. restore master db
> 3. restore users db's.
> 4. Run the replication script (extracted from old server)
>
> im getting the errors, as follows
>
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
> Line 164
> Invalid object name 'msdb..MSdistributiondbs'.
> Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
> Line
> 95
> The server 'ADCPWSQLZ7' is already a Subscriber.
> ............
> ......
>
> pls help me to solve the error.
>
> thks
>

reg. Prob with replication

HI,
Im using Mssql 2000, We are migrating the server from one server to
another.
i will list out what i have done, so pls let me know what wrong i did
and how to solve.
1. install mssql s/w
2. restore master db
3. restore users db's.
4. Run the replication script (extracted from old server)
im getting the errors, as follows
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
Line 164
Invalid object name 'msdb..MSdistributiondbs'.
Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
Line
95
The server 'ADCPWSQLZ7' is already a Subscriber.
............
......
pls help me to solve the error.
thksIf you wanted a hope of getting this to work you should have restored the
msdb and distribution databases as well and then renamed the new server with
the name of the old server.
you need to disable replication and then recreate it and then try to re-run
your scripts on this server.
--
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"calms" <calmsqurill@.gmail.com> wrote in message
news:ddb85046-94b1-4e43-a006-f3d3a24bca5a@.s12g2000prg.googlegroups.com...
> HI,
> Im using Mssql 2000, We are migrating the server from one server to
> another.
>
> i will list out what i have done, so pls let me know what wrong i did
> and how to solve.
>
> 1. install mssql s/w
> 2. restore master db
> 3. restore users db's.
> 4. Run the replication script (extracted from old server)
>
> im getting the errors, as follows
>
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
> Line 164
> Invalid object name 'msdb..MSdistributiondbs'.
> Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
> Line
> 95
> The server 'ADCPWSQLZ7' is already a Subscriber.
> ............
> ......
>
> pls help me to solve the error.
>
> thks
>

Ref: Re: Question about stored procedure in SQL Server 2005

Hello all!

I have a (simple) question.

(Sorry for some possible wrong menus-name but I have the italian version of SQL Server 2005 and I'm trying to translate them in best way...)

I want to create a new stored procedure for my database.

I click on "Programmability", then right-click on "Stored procedure" and then click on "New stored procedure".

In the Query Analyzer I write the procedure following the correct syntax, check the sp to be sure that is correct and everything is ok.

The problem is that, when I save the sp, I can create a file with extension .sql, but I don't see the new sp created on the list of stored procedure of the database.....

What's wrong?

Thank you in advance for your help!

Bye

nino

Save is just save your script on your disk for future use (re-usable). But it will not update/save in the database.

You have to execute the script to craete the proc (the button with ! or you can press F5)..

|||

Just Execute created script for your New Stored Procedure.

|||

Just to be exact...

the sp is like this:

Code Snippet

use [db_something]

go

[...]

create procedure [dbo].[updateService]

@.idS int,

@.name varchar(20),

@.var1 varchar(10),

@.var2 int

as

update Tservices

set variable1 = @.var1, variable2 = @.var2

where idService= @.idS and nameX = @.name

go


After this I obviously executed the sp and received "query completed without errors"... But no trace of my sp !!

Bye

Nino

|||

hi,

you just have to run the create script by pressing f5 on the management studio

saving will save the scripts into a physical .sql file but not on the database.

be sure you are running the create script on the appropriate database

to do this issue the use <database> prior to the create proc script

Use northwind

create proc sp1

........

you have probably run the scripts against the master database

regards,

joey

|||

After you run the script execute the following...

Code Snippet

sp_helptext updateService

|||

Thank you all guys!

Now my sp is present.

I hope to contribute in the next not with stupid questions but with good answers!

Bye

Nino