How to script adding a field to a table

Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Do it in EM, and press "Save Change Script" button before exiting the window
s, and you will get the
script served on a silver plate (almost).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl..
.
> Hi - I would like to know what sql to run in Query Analyzer to add two
> fields to an existing table (I know you can do this in Enterprise
> Manager - but I'd like to be able to send a script to someone to let it
> happen automatically).
> Column Name: IDCreated
> DataType: DateTime
> Length: 8
> Allow Nulls: False
> Default Value: (getdate())
> and
> Column Name: ChangeNum
> DataType: bigint
> Length: 8
> Allow Nulls: False
> Identity: Yes
> Identity Seed: 45
> Identity Increment: 1
> Thanks for any help,
> Mark
> *** Sent via Developersdex http://www.examnotes.net ***|||Here ya go:
create table MyTable
(
PK int primary key
)
go
alter table MyTable
add
IDCreated datetime not null
constraint DF1_Myatble default (getdate())
, ChangeNum bigint not null identity (45, 1)
go
drop table MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mark" <anonymous@.devdex.com> wrote in message
news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl...
Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***|||Thinking about it, go with Tom's suggestion. EM often does these things in a
less than optimal way.
Often you see EM creating a new table, copy data etc etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23xDANtCyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Do it in EM, and press "Save Change Script" button before exiting the wind
ows, and you will get
> the script served on a silver plate (almost).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MS
FTNGP12.phx.gbl...
>|||Like this:
ALTER TABLE table_name ADD idcreated DATETIME NOT NULL
CONSTRAINT df_table_name_idcreated
DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE table_name ADD changenum BIGINT NOT NULL
IDENTITY(45,1) ;
Usually, when you add an IDENTITY column you will want to add a unique
or primary key constraint on that column. Although that's not
mandatory, IDENTITY itself won't prevent duplicates in all
circumstances because the auto-generated value can be overridden or the
seed can be changed. Also, IDENTITY is typically referenced by a
foreign key, for which a constraint is required.
Depending on your requirements you can add a constraint like this:
ALTER TABLE table_name
ADD CONSTRAINT ak_table_name_change_num UNIQUE (changenum) ;
David Portas
SQL Server MVP
--|||> Often you see EM creating a new table, copy data etc etc.
and sometimes it is the optimal way, is it not?

how to save query to file in Query Designer

Using SQL Server 2000's Query Designer (not analyzer), I can create a
query, look at the query in SQL pane. On a right click, if I select
save as, there is no file dialog to inquire where to save file. If I
select save, I can't find file.
Is the save-as a bug?
Where should I look for the file; what naming convention is used?
RitaRita,
You are correct that Save As and Save do not work here. See
http://groups.google.com/groups?q=hoegemeier%2Bsigouin
for an answer to this question.
SK
"Rita W." <riwillia@.telus.net> wrote in message
news:40130D48.2050708@.telus.net...

quote:

---

> Using SQL Server 2000's Query Designer (not analyzer), I can create a
> query, look at the query in SQL pane. On a right click, if I select
> save as, there is no file dialog to inquire where to save file. If I
> select save, I can't find file.
> Is the save-as a bug?
> Where should I look for the file; what naming convention is used?
> Rita
>

---

|||Thanks, Steve.
R
Steve Kass wrote:

quote:

---

>Rita,
> You are correct that Save As and Save do not work here. See
>http://groups.google.com/groups?q=hoegemeier%2Bsigouin
>for an answer to this question.
>SK
>"Rita W." <riwillia@.telus.net> wrote in message
>news:40130D48.2050708@.telus.net...
>
>
>

---

how to save query to file in Query Designer

Using SQL Server 2000's Query Designer (not analyzer), I can create a
query, look at the query in SQL pane. On a right click, if I select
save as, there is no file dialog to inquire where to save file. If I
select save, I can't find file.
Is the save-as a bug?
Where should I look for the file; what naming convention is used?
RitaRita,
You are correct that Save As and Save do not work here. See
http://groups.google.com/groups?q=hoegemeier%2Bsigouin
for an answer to this question.
SK
"Rita W." <riwillia@.telus.net> wrote in message
news:40130D48.2050708@.telus.net...
> Using SQL Server 2000's Query Designer (not analyzer), I can create a
> query, look at the query in SQL pane. On a right click, if I select
> save as, there is no file dialog to inquire where to save file. If I
> select save, I can't find file.
> Is the save-as a bug?
> Where should I look for the file; what naming convention is used?
> Rita
>|||Thanks, Steve.
R
Steve Kass wrote:
>Rita,
> You are correct that Save As and Save do not work here. See
>http://groups.google.com/groups?q=hoegemeier%2Bsigouin
>for an answer to this question.
>SK
>"Rita W." <riwillia@.telus.net> wrote in message
>news:40130D48.2050708@.telus.net...
>
>>Using SQL Server 2000's Query Designer (not analyzer), I can create a
>>query, look at the query in SQL pane. On a right click, if I select
>>save as, there is no file dialog to inquire where to save file. If I
>>select save, I can't find file.
>>Is the save-as a bug?
>>Where should I look for the file; what naming convention is used?
>>Rita
>>
>
>

How to save headings in query results?

In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KK
Consider running the same query via a DTS data pump to a flat file. You can
check on the option to include headers.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"KK" <anonymous@.discussions.microsoft.com> wrote in message
news:191d01c49ccc$d8ffca00$a301280a@.phx.gbl...
In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KK
sql

How to save headings in query results?

In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KKConsider running the same query via a DTS data pump to a flat file. You can
check on the option to include headers.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"KK" <anonymous@.discussions.microsoft.com> wrote in message
news:191d01c49ccc$d8ffca00$a301280a@.phx.gbl...
In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KK

how to run this proc in SQL Analyzer?

hi, guys

I have a stored procedure, like that:

CREATE PROCEDURE [dbo].[ViewTitles]
@.UID int,
@.DateStart datetime,
@.DateEnd datetime,
........

When I run this query in SQL Analyzer like this:
ViewTitles 6165, '2006-01-29 10:00:00', '2006-02-29 10:00:00'

It alwasy shows:
Error converting data type varchar to datetime.

If I run it in code, asp.net, or report, no problem at all.
What is wrong with that?

Thanks.

What is the dateformat of the session when logged in through ISQLW? You can verify this by looking at output of DBCC USEROPTIONS. Look for set option "dateformat". In addition to this, you should one of the ISO 8601 formats for datetime literals that should be interpreted correctly irrespective of the language or dateformat settings. Specify the value instead like '2006-01-29T10:00:00'.

how to roll back

is this the only way to do a rollback in SQL2000 ?
Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
undo the changes. And only applicable for UPDATE & DELETE queries within the
transaction.
just want to understand more how to use rollback and how it works.
tks
pkHi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>|||HI Greg,
the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script ne
ver make rollback.
Use the "if @.err != 0" command!
JBandi
-- Greg Linwood wrote: --
Hi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> pk|||Yes - my bad there! Thanks for picking it up (:
Regards,
Greg Linwood
SQL Server MVP
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:C4555093-2AD6-44DE-A43E-CCF5792D49E3@.microsoft.com...
> HI Greg,
> the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script
never make rollback.
> Use the "if @.err != 0" command!
> JBandi
> -- Greg Linwood wrote: --
> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation
(locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message
news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
TRAN to
within
> the|||In addition to Greg's response and to directly answer your question,
rollbacks affect not only updates and deletes, but inserts also.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>|||Hi all,
I placed the SET XACT_ABORT ON statement introduced by Greg at the beginning
of a sproc, but appearently it did not skip the t-sql errors happended with
in the sproc and rollback. I'm hoping to find a method to skip all error me
ssages and just rollback.
Is there a way to do that?
Thanks,
-Lawrence
"Greg Linwood" wrote:

> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation (locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> the
>
>

how to roll back

is this the only way to do a rollback in SQL2000 ?
Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
undo the changes. And only applicable for UPDATE & DELETE queries within the
transaction.
just want to understand more how to use rollback and how it works.
tks
pk
Hi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>
|||HI Greg,
the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script never make rollback.
Use the "if @.err != 0" command!
JBandi
-- Greg Linwood wrote: --
Hi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> pk
|||Yes - my bad there! Thanks for picking it up (:
Regards,
Greg Linwood
SQL Server MVP
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:C4555093-2AD6-44DE-A43E-CCF5792D49E3@.microsoft.com...
> HI Greg,
> the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script
never make rollback.
> Use the "if @.err != 0" command!
> JBandi
> -- Greg Linwood wrote: --
> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation
(locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message
news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
TRAN to
within
> the
|||In addition to Greg's response and to directly answer your question,
rollbacks affect not only updates and deletes, but inserts also.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>
|||Hi all,
I placed the SET XACT_ABORT ON statement introduced by Greg at the beginning of a sproc, but appearently it did not skip the t-sql errors happended within the sproc and rollback. I'm hoping to find a method to skip all error messages and just rollback.
Is there a way to do that?
Thanks,
-Lawrence
"Greg Linwood" wrote:

> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation (locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> the
>
>

How to restrict access our DB on SQL server.

Hi,
How can we restrict our DB if connecting thru other applications like SQL query analyzer, Excel & even Enterprise manager?
but this DB should be accessible by our application (from front end) only...
I learned about "Application Role" in SQL server 2000 but to apply this
we will have to change our code thousand places.
Have any idea...Pl. do suggest?
Thanx in advance!!!!
Regards,
Paresh...
Message posted via http://www.sqlmonster.com
Don't give out the passwords and they won't be able to login. If they have
a login and password then they can get in and do what ever the current
permissions allow them to.
Andrew J. Kelly SQL MVP
"paresh goyal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d15479cc26b24cf0a782a131acead60a@.SQLMonster.c om...
> Hi,
> How can we restrict our DB if connecting thru other applications like SQL
> query analyzer, Excel & even Enterprise manager?
> but this DB should be accessible by our application (from front end)
> only...
> I learned about "Application Role" in SQL server 2000 but to apply this
> we will have to change our code thousand places.
> Have any idea...Pl. do suggest?
> Thanx in advance!!!!
> Regards,
> Paresh...
> --
> Message posted via http://www.sqlmonster.com
|||Sounds like you're looking for a solution that might not exist. If your
users that currently use the applications login to SQL with their own
userids, they can use the same userids with any query tool and do whatever
they have permissions for. You could try to use different ports, server
aliases, etc... to make it tougher for them to discover where the actual
sql db sits but it's just a speed bump, not a barrier.
aK.
"paresh goyal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d15479cc26b24cf0a782a131acead60a@.SQLMonster.c om...
> Hi,
> How can we restrict our DB if connecting thru other applications like SQL
query analyzer, Excel & even Enterprise manager?
> but this DB should be accessible by our application (from front end)
only...
> I learned about "Application Role" in SQL server 2000 but to apply this
> we will have to change our code thousand places.
> Have any idea...Pl. do suggest?
> Thanx in advance!!!!
> Regards,
> Paresh...
> --
> Message posted via http://www.sqlmonster.com