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 COLUMN NAME?

I'm starting to think that there is no way to simply ALTER a column name with
T-SQL. Is that true?
That means I need to do it in EM, where it will do all that ugly
drop/re-create table stuff?
Any help would be appreciated.
Hi
Yes, that is true.
Cheers
Mike
"Steve Z" wrote:

> I'm starting to think that there is no way to simply ALTER a column name with
> T-SQL. Is that true?
> That means I need to do it in EM, where it will do all that ugly
> drop/re-create table stuff?
> Any help would be appreciated.
|||Thanks...
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Yes, that is true.
> Cheers
> Mike
> "Steve Z" wrote:
|||> I'm starting to think that there is no way to simply ALTER a column name
with
> T-SQL. Is that true?
NO!
EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
http://www.aspfaq.com/
(Reverse address to reply.)
|||Thank you very, very much - that is much neater...
"Aaron [SQL Server MVP]" wrote:

> with
> NO!
> EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

ALTER COLUMN NAME?

I'm starting to think that there is no way to simply ALTER a column name with
T-SQL. Is that true?
That means I need to do it in EM, where it will do all that ugly
drop/re-create table stuff?
Any help would be appreciated.Hi
Yes, that is true.
Cheers
Mike
"Steve Z" wrote:
> I'm starting to think that there is no way to simply ALTER a column name with
> T-SQL. Is that true?
> That means I need to do it in EM, where it will do all that ugly
> drop/re-create table stuff?
> Any help would be appreciated.|||Thanks...
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Yes, that is true.
> Cheers
> Mike
> "Steve Z" wrote:
> > I'm starting to think that there is no way to simply ALTER a column name with
> > T-SQL. Is that true?
> >
> > That means I need to do it in EM, where it will do all that ugly
> > drop/re-create table stuff?
> >
> > Any help would be appreciated.|||> I'm starting to think that there is no way to simply ALTER a column name
with
> T-SQL. Is that true?
NO!
EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Thank you very, very much - that is much neater...
"Aaron [SQL Server MVP]" wrote:
> > I'm starting to think that there is no way to simply ALTER a column name
> with
> > T-SQL. Is that true?
> NO!
> EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

Allow zero length strings

In Microsoft Access there is the option to set a field to accept zero length
strings.
This of course means that an empty text box could be entered into the
database without an error occuring.
Is there any way I can do this in SQL Server.
I know that when developing a front end I could write some code to check
each text box and when a textbox is empty, push a " " into it before
insertion but I'm sick of this.
Is there any other way ?We use a Default in SQL Server 2000 like the following:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[EmptyString]') and OBJECTPROPERTY(id,
N'IsDefault') = 1)
drop default [dbo].[EmptyString]
GO
create default [Space] as ''
GO
>--Original Message--
>In Microsoft Access there is the option to set a field to
accept zero length
>strings.
>This of course means that an empty text box could be
entered into the
>database without an error occuring.
>Is there any way I can do this in SQL Server.
>I know that when developing a front end I could write
some code to check
>each text box and when a textbox is empty, push a " "
into it before
>insertion but I'm sick of this.
>Is there any other way ?
>
>.
>|||You can insert an empty (zero-length) string into a varchar (or nvarchar)
column:
CREATE TABLE MyTable
(
MyColumn varchar(10) NOT NULL
)
INSERT INTO MyTable VALUES('')
SELECT
MyColumn,
DATALENGTH(MyColumn)
FROM MyTable
GO
I don't know much about Access programming so there may be programming
considerations. Does an empty text box return in a zero-length string value
or does this return a NULL value?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:ePYQ%23tctDHA.980@.TK2MSFTNGP10.phx.gbl...
> In Microsoft Access there is the option to set a field to accept zero
length
> strings.
> This of course means that an empty text box could be entered into the
> database without an error occuring.
> Is there any way I can do this in SQL Server.
> I know that when developing a front end I could write some code to check
> each text box and when a textbox is empty, push a " " into it before
> insertion but I'm sick of this.
> Is there any other way ?
>