Altering table & trans. replication

Hi,

How can I modify table with publication (change of one column length)
without completely breaking replication.

Thanks in advance"Wagner" <wagner@.email.t-com.hr> wrote in message
news:1x0k6ml5is0vs$.mmdq6nunj5l6$.dlg@.40tude.net.. .
> Hi,
> How can I modify table with publication (change of one column length)
> without completely breaking replication.

Besides the method you found, I've also done the following:

Create a NEW column of the type you want, call it foo_temp.

Copy data into it.

Then sp_repldropcolumn on the existing column.

Then sp_repladdcolumn with the same name, but new definition.

Copy data back.

> Thanks in advance

Altering Table

Hai All.

I want to know ,is there any way to modify a table's field like adding of new field to a table.
If any one have idea plz enlighten me.
Bye

Regards,
Karthik.AYou can do this via Enterprise Manager, or via a T-SQL script (ALTER TABLE). Look in Books Online for the syntax:

You can download from here if you do not have it already:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Cheers
Ken

Alter Table Question

I'm setting up a DTS to modify a table, and I need to "reset" the identity
field. I had originally wanted to do this with an ALTER TABLE command, but
apparently it isn't as straightforward as that. So at this point I believe
I need to drop the table and recreate it. Is this the case? Assuming it
is, I've run into the following problem. I need to add a description to the
fields when I re-create the table, but I can't seem to find the syntax. For
my CREATE function, it's as simple as:
CREATE TABLE TstTable
(
ACC_ID int identity primary key,
Code varchar(50),
CodeType varchar(15),
ActionType varchar(10),
ActionBy varchar(50),
ChangeDate datetime,
ChangeTime varchar(20)
)
...but I can't figure out how to put a description for each field in.
Anyone know the syntax, or if it's impossible?
Thanks,
James
Nevermind...seems like sp_addextendedproperty will do what I'm looking for.
Thanks anyway!
"James" <cppjames@.aol.com> wrote in message
news:eYEmV862EHA.2192@.TK2MSFTNGP14.phx.gbl...
> I'm setting up a DTS to modify a table, and I need to "reset" the identity
> field. I had originally wanted to do this with an ALTER TABLE command,
but
> apparently it isn't as straightforward as that. So at this point I
believe
> I need to drop the table and recreate it. Is this the case? Assuming it
> is, I've run into the following problem. I need to add a description to
the
> fields when I re-create the table, but I can't seem to find the syntax.
For
> my CREATE function, it's as simple as:
> CREATE TABLE TstTable
> (
> ACC_ID int identity primary key,
> Code varchar(50),
> CodeType varchar(15),
> ActionType varchar(10),
> ActionBy varchar(50),
> ChangeDate datetime,
> ChangeTime varchar(20)
> )
> ...but I can't figure out how to put a description for each field in.
> Anyone know the syntax, or if it's impossible?
> Thanks,
> James
>
|||James -
Have a look at dbcc checkident for the other issue of resetting the
identity.
Mike John
"James" <cppjames@.aol.com> wrote in message
news:OE4FGA72EHA.1192@.tk2msftngp13.phx.gbl...
> Nevermind...seems like sp_addextendedproperty will do what I'm looking
> for.
> Thanks anyway!
>
> "James" <cppjames@.aol.com> wrote in message
> news:eYEmV862EHA.2192@.TK2MSFTNGP14.phx.gbl...
> but
> believe
> the
> For
>

Alter Table Question

I'm setting up a DTS to modify a table, and I need to "reset" the identity
field. I had originally wanted to do this with an ALTER TABLE command, but
apparently it isn't as straightforward as that. So at this point I believe
I need to drop the table and recreate it. Is this the case? Assuming it
is, I've run into the following problem. I need to add a description to the
fields when I re-create the table, but I can't seem to find the syntax. For
my CREATE function, it's as simple as:
CREATE TABLE TstTable
(
ACC_ID int identity primary key,
Code varchar(50),
CodeType varchar(15),
ActionType varchar(10),
ActionBy varchar(50),
ChangeDate datetime,
ChangeTime varchar(20)
)
...but I can't figure out how to put a description for each field in.
Anyone know the syntax, or if it's impossible?
Thanks,
JamesNevermind...seems like sp_addextendedproperty will do what I'm looking for.
Thanks anyway!
"James" <cppjames@.aol.com> wrote in message
news:eYEmV862EHA.2192@.TK2MSFTNGP14.phx.gbl...
> I'm setting up a DTS to modify a table, and I need to "reset" the identity
> field. I had originally wanted to do this with an ALTER TABLE command,
but
> apparently it isn't as straightforward as that. So at this point I
believe
> I need to drop the table and recreate it. Is this the case? Assuming it
> is, I've run into the following problem. I need to add a description to
the
> fields when I re-create the table, but I can't seem to find the syntax.
For
> my CREATE function, it's as simple as:
> CREATE TABLE TstTable
> (
> ACC_ID int identity primary key,
> Code varchar(50),
> CodeType varchar(15),
> ActionType varchar(10),
> ActionBy varchar(50),
> ChangeDate datetime,
> ChangeTime varchar(20)
> )
> ...but I can't figure out how to put a description for each field in.
> Anyone know the syntax, or if it's impossible?
> Thanks,
> James
>|||James -
Have a look at dbcc checkident for the other issue of resetting the
identity.
Mike John
"James" <cppjames@.aol.com> wrote in message
news:OE4FGA72EHA.1192@.tk2msftngp13.phx.gbl...
> Nevermind...seems like sp_addextendedproperty will do what I'm looking
> for.
> Thanks anyway!
>
> "James" <cppjames@.aol.com> wrote in message
> news:eYEmV862EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> I'm setting up a DTS to modify a table, and I need to "reset" the
>> identity
>> field. I had originally wanted to do this with an ALTER TABLE command,
> but
>> apparently it isn't as straightforward as that. So at this point I
> believe
>> I need to drop the table and recreate it. Is this the case? Assuming it
>> is, I've run into the following problem. I need to add a description to
> the
>> fields when I re-create the table, but I can't seem to find the syntax.
> For
>> my CREATE function, it's as simple as:
>> CREATE TABLE TstTable
>> (
>> ACC_ID int identity primary key,
>> Code varchar(50),
>> CodeType varchar(15),
>> ActionType varchar(10),
>> ActionBy varchar(50),
>> ChangeDate datetime,
>> ChangeTime varchar(20)
>> )
>> ...but I can't figure out how to put a description for each field in.
>> Anyone know the syntax, or if it's impossible?
>> Thanks,
>> James
>>
>

ALTER table queries

In Oracle, alter statements with modify clause can be given for more than one query ? Is it possible in SQL Server ?

Eg :-

ALTER TABLE test1
MODIFY col1 VARCHAR2(1024)
MODIFY col2 VARCHAR2(256)
MODIFY col3 VARCHAR2(256)

Please give the equivalent for the above in SQL Server . Can all this exists in a single query in SQL Server ?MS-SQL supports the SQL-92 standard. Under the standard, you can add multiple columns in a single operation, but you can only change one existing column at a time.

It is possible to configure SQL 2000 to allow changes to multiple columns at once, but it is NOT supported at all. I would strongly advise that you break your changes down so that they meet the SQL-92 standard instead of trying to work around the standard.

-PatP|||Hi,

Thanks for your prompt reply.

Thanks,
Sam

ALTER TABLE MODIFY

hi!

i encountered problems when running this code in SQL Query

ALTER TABLE [dbo].[amsSchedule]
MODIFY(CutOff1 datetime NULL,
[FileName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

my aim is to modify the two fields to change its data type. BUt when im trying to run this command in the query analyzer, itsays "incorrect syntax error '(' "

What do i have to do? please help me...thanks

Hi,

ALTER TABLE (...) when modifying a column only supports one change at a time.

HTH, Jens Suessmeyer.

|||

Hi Jens!

Thanks a lot for the tip...now i know what to do since u told me that alter table only supports one change at a time...

thanks a lot!

Alter Table

Hello I have another problem.
I write this line of code:
alter table film modify casting personaggi varchar2(500)
but when I execute the result is
Error: ORA-01735: invalid ALTER TABLE option
what's the problem??
Thank you ElisaHello,

can I see the table structure ?

Best regards
Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com|||the structure of the table is:

CREATE TABLE Film
(IdFilm number(10) PRIMARY KEY,
Titolo VARCHAR2(20) NOT NULL,
Regista VARCHAR2(20) NOT NULL,
Casting VARCHAR2(500) NOT NULL,
Nazione VARCHAR2(20) NOT NULL,
Durata NUMBER(3) NOT NULL,
Genere VARCHAR2(10) NOT NULL,
Trama VARCHAR2(500) NOT NULL,
Novit VARCHAR2(10) NOT NULL,
Locandina VARCHAR2(20) NOT NULL,
Note VARCHAR2(100) NOT NULL,
anno number(4))
Thank you, Elisa|||If you want to rename a column and you are on Oracle 9i use:

ALTER TABLE film RENAME COLUMN casting TO personaggi;

On previous versions of Oracle you have to drop old and create new column.

Hope it helps,
Jacek

Originally posted by trilly
the structure of the table is:

CREATE TABLE Film
(IdFilm number(10) PRIMARY KEY,
Titolo VARCHAR2(20) NOT NULL,
Regista VARCHAR2(20) NOT NULL,
Casting VARCHAR2(500) NOT NULL,
Nazione VARCHAR2(20) NOT NULL,
Durata NUMBER(3) NOT NULL,
Genere VARCHAR2(10) NOT NULL,
Trama VARCHAR2(500) NOT NULL,
Novit VARCHAR2(10) NOT NULL,
Locandina VARCHAR2(20) NOT NULL,
Note VARCHAR2(100) NOT NULL,
anno number(4))
Thank you, Elisa

Alter table

I know I can't do an alter table on a replicated table because it is
being replicated. But, in my software, if I need to modify a table I do
an alter table and add the new column.
What is the easy way, via SQL script, to see if this machine is a
distributor/publisher for replication, in which case I need to do the
sp_repladdcolumn, or a subscriber, in which case I need to do nothing
because the dist/pub will do it, or neither, in which case I need to do
the alter table?
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Darin,
I'd probably use something like this:
declare @.mytablename varchar(100)
set @.mytablename = 'testtr'
if exists(SELECT name FROM sysarticles where name =
@.mytablename)
or exists(SELECT name FROM sysarticles where name =
@.mytablename)
select 'exists'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Did you mean for both of the select statements to be the same?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Sorry - second one is for merge...
Actually it lacked a bit more code which I've added. There are 2 versions
and the second one would be more elegant (you'll need to test it).
Cheers,
Paul Ibison
declare @.mytablename varchar(100)
set @.mytablename = 'testtr'
if (select object_id('sysarticles')) is not null
begin
if exists (SELECT name FROM sysarticles where name =
@.mytablename)
select 'yes'
end
if (select object_id('sysmergearticles')) is not null
begin
if exists(SELECT name FROM sysmergearticles where name =
@.mytablename)
select 'yes'
end
if (select replinfo from sysobjects where name = @.mytablename) > 0
select 'yes'

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, Julia
The net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>
|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>
|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
David Gugick
Imceda Software
www.imceda.com

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, JuliaThe net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
David Gugick
Imceda Software
www.imceda.com

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, JuliaThe net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
--
David Gugick
Imceda Software
www.imceda.com

Alter Stored Procedure

Hi all,

I use SQL2005 and I recently noticed this...

When I right click a stored procedure and select modify I get something like this

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxxxxx]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @.statement = N'

xxx xxx xxx'

instead of the usual alter procedure...

I think that this happened after I installed SP2 (which I cannot remove)

Why this is happening and how can I revert it to the old way of altering stored procs?

In SSMS click Tools > Options then Scripting on the left. "Object Scripting options" section set "Include IF NOT EXISTS clause" to false.

ALTER DATABASE MODIFY NAME but

Leave the file location/directory the heck alone!

How do I do this?

Just issuing:

ALTER DATABASE Old_Name MODIFY NAME = New_Name

moves the mdf and ldf files to a new, unwanted location (apparently the SQL Server default as it's under program files) with the new name.

Is this possible or do I have to issue the additional ALTER DATABASE MODIFY FILE statements for this?modify name does not move the file(s).

create database [test]
on(name=test,filename='c:\test.mdf')
log on(name=test_log,filename='c:\test.ldf')
go
alter database [test] modify name=newtest
go
select *
from [newtest]..sysfiles
go
drop database [newtest]
go

==result==
1 test c:\test.mdf
2 test_log c:\test.ldf|||

Hi,

ALTER DATABASE ... MODIFY FILE command modifies the names of data files of the related database.

Please check the following article for also a sample on changing logical file names of SQL databases http://www.kodyaz.com/articles/change-sql-server-database-file-names.aspx

Eralper

|||Guess you didn't read my full post. I'm aware of this command.|||did you try my demo script. do you get the expected result?|||

Yeah, turns out it's not that part of the script but rather the Copy Database wizard that is to blame.

(I've got a post in tools on it bu gtno replies yet.)

Alter coulmn (Replication applied)

Hi All
I dont know how to modify the table schema (e.g. Alter Column) without
removing the applied replication. Would anyone please tell me the solution?
Thanks alot,
Douglas
Douglas,
With SQL2000 you can add/drop columns in a replicated table using
sp_repladdcolumn and sp_repldropcolumn but there is no facility to alter
them. One way to accomplish this would be to unsubscribe, unpublish and then
do the alter table...alter column followed by republishing and
resubscribing. One more way would be to add a new column with the required
format, update the new column with data from the column which was meant to
be altered and then drop it, all without unpublishing. I havent tried the
latter option but a quick guess on the amount of work to be done, I think it
will be a pain.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"douglas" <douglaswong@.hotmail.com> wrote in message
news:#tcohAGREHA.2932@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I dont know how to modify the table schema (e.g. Alter Column) without
> removing the applied replication. Would anyone please tell me the
solution?
> Thanks alot,
> Douglas
>

Alter coulmn (Replication applied)

Hi All
I dont know how to modify the table schema (e.g. Alter Column) without
removing the applied replication. Would anyone please tell me the solution?
Thanks alot,
DouglasDouglas,
With SQL2000 you can add/drop columns in a replicated table using
sp_repladdcolumn and sp_repldropcolumn but there is no facility to alter
them. One way to accomplish this would be to unsubscribe, unpublish and then
do the alter table...alter column followed by republishing and
resubscribing. One more way would be to add a new column with the required
format, update the new column with data from the column which was meant to
be altered and then drop it, all without unpublishing. I havent tried the
latter option but a quick guess on the amount of work to be done, I think it
will be a pain.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"douglas" <douglaswong@.hotmail.com> wrote in message
news:#tcohAGREHA.2932@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I dont know how to modify the table schema (e.g. Alter Column) without
> removing the applied replication. Would anyone please tell me the
solution?
> Thanks alot,
> Douglas
>

Alter coulmn (Replication applied)

Hi All
I dont know how to modify the table schema (e.g. Alter Column) without
removing the applied replication. Would anyone please tell me the solution?
Thanks alot,
DouglasDouglas,
With SQL2000 you can add/drop columns in a replicated table using
sp_repladdcolumn and sp_repldropcolumn but there is no facility to alter
them. One way to accomplish this would be to unsubscribe, unpublish and then
do the alter table...alter column followed by republishing and
resubscribing. One more way would be to add a new column with the required
format, update the new column with data from the column which was meant to
be altered and then drop it, all without unpublishing. I havent tried the
latter option but a quick guess on the amount of work to be done, I think it
will be a pain.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"douglas" <douglaswong@.hotmail.com> wrote in message
news:#tcohAGREHA.2932@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I dont know how to modify the table schema (e.g. Alter Column) without
> removing the applied replication. Would anyone please tell me the
solution?
> Thanks alot,
> Douglas
>

Alter column to Varchar(max) takes to long

Hi,

I need to modify existing table in my database to varchar(max) from varchar(2000)

This table contains 30 million plus rows and has more than 70 columns.

now when i am running alter command for this it take too long(more than 9 mins) which is not acceptable. . Is their any way to reduce this execution time

Following is the query i am using for this

ALTER TABLE Receipt
ALTER COLUMN CUSTOM VARCHAR(MAX) NULL

Please let me know if you have any suggestion to improve this

TAI
Prashant

Try to add a new column with the new type and then try to do something like:

UPADTE Table
SET
NewCol = Col1,
Col1 = NULL

After that drop the old column. I don′t know if that will save you the additional space the second column will need, but it should be worth a try doing this in one step. If it does not work for you, create a column first copy the data over to the new column, then drop the old one and rename the new one. You will have to do that in a maintaince window to not procude dirty write in the new column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

All stored procedures in the master database disappear? Help!

This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a lot
system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
I would greatly appreciate any help.
Bing
I doubt they are really gone since. SEM uses many of those procedures to do
it's work and I doubt SEM would even work if all the procs were gone. Of
course, I've never tried... <g>
Hopefully it's something this simple. I don't suppose you have closed and
reopened SEM?
BTW... why are you modifying procedures in master in the first place. You
should not be touching system procedures and for the most part you shouldn't
be placing 'user' procedures there.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"bing" <bing@.discussions.microsoft.com> wrote in message
news:212BA431-BCAE-425A-BF02-277B235DE3DA@.microsoft.com...
> This morning when I went to modify a stored procedure in the master
database, I noticed all the stored procedures were gone! When I clicked
'Stored Procedures', nothing returned on the right window in SQL Server
Enterprise Manager. There should be a lot system and user defined stored
procedures there. Extended Stored Procedures show up fine though. Is this
a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||From query analyzer run the following query
select * from master..sysobjects where xtype = 'S'
It will list your system sp's.
You can then be sure if they are deleted or not.
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||Sorry S for system tables P for SP's you're problem with sp's then type
xtype = 'P'
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||It's getting even weirder. I closed and reopened SEM one more time, now I can see all the stored procedures in the master database, but all the views become invisible this time. DBCC checkdb on master shows no error. Would restarting
SQL server and SQL agent help clean up some weirdness?
Bing
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing

All stored procedures in the master database disappear? Help!

This morning when I went to modify a stored procedure in the master database
, I noticed all the stored procedures were gone! When I clicked 'Stored Pro
cedures', nothing returned on the right window in SQL Server Enterprise Mana
ger. There should be a lot
system and user defined stored procedures there. Extended Stored Procedure
s show up fine though. Is this a disaster? What might have happened?
I would greatly appreciate any help.
BingI doubt they are really gone since. SEM uses many of those procedures to do
it's work and I doubt SEM would even work if all the procs were gone. Of
course, I've never tried... <g>
Hopefully it's something this simple. I don't suppose you have closed and
reopened SEM?
BTW... why are you modifying procedures in master in the first place. You
should not be touching system procedures and for the most part you shouldn't
be placing 'user' procedures there.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"bing" <bing@.discussions.microsoft.com> wrote in message
news:212BA431-BCAE-425A-BF02-277B235DE3DA@.microsoft.com...
> This morning when I went to modify a stored procedure in the master
database, I noticed all the stored procedures were gone! When I clicked
'Stored Procedures', nothing returned on the right window in SQL Server
Enterprise Manager. There should be a lot system and user defined stored
procedures there. Extended Stored Procedures show up fine though. Is this
a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing|||From query analyzer run the following query
select * from master..sysobjects where xtype = 'S'
It will list your system sp's.
You can then be sure if they are deleted or not.
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I no
ticed all the stored procedures were gone! When I clicked 'Stored Procedures', noth
ing returned on the right window in SQL Server Enterprise Manager. There should be
a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fin
e though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing|||Sorry S for system tables P for SP's you're problem with sp's then type
xtype = 'P'
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I no
ticed all the stored procedures were gone! When I clicked 'Stored Procedures', noth
ing returned on the right window in SQL Server Enterprise Manager. There should be
a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fin
e though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing|||It's getting even weirder. I closed and reopened SEM one more time, now I c
an see all the stored procedures in the master database, but all the views b
ecome invisible this time. DBCC checkdb on master shows no error. Would r
estarting
SQL server and SQL agent help clean up some weirdness?
Bing
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I no
ticed all the stored procedures were gone! When I clicked 'Stored Procedures', noth
ing returned on the right window in SQL Server Enterprise Manager. There should be
a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fin
e though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing