How to change the Identity value

I have a database table and it has a column called ttt_id which has an identity seed. Now the Id value is 2845746 and if I insert the new record the value would be 2845747. But I want to change that to 2865740 instead of 2845747. How can do that?
Thanks.Check out DBCC CHECKIDENT in BOL. It should give you everything you need.|||-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT <TableName> ON
GO

INSERT INTO <TableName> (<FieldName>) VALUES(2845747)
GO

how to change the default table locking type ?

Hi guys,
Is there anyway to change the default recode locking type (Table
Locking) to row locking !! ?
Thanks in help
-SniperSniper
As far as I know SQL Server begins with a low type (row locking) and
increase if it needs.
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
> Hi guys,
> Is there anyway to change the default recode locking type (Table
> Locking) to row locking !! ?
> Thanks in help
> -Sniper|||Using the sp_indexoption stored procedure you can play with the options:
AllowRowLocks, AllowPageLocks, DisAllowRowLocks, DisAllowPageLocks for each
index.
By default, SQL Server considers the locking granularity automatically, and
the default choices it makes are usually best.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
> Hi guys,
> Is there anyway to change the default recode locking type (Table
> Locking) to row locking !! ?
> Thanks in help
> -Sniper|||In addition to the other posts, supporting the query with good and usable in
dexes reduces the risk
of getting table locks.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
> Hi guys,
> Is there anyway to change the default recode locking type (Table
> Locking) to row locking !! ?
> Thanks in help
> -Sniper|||Hi Tibor,
Thanks for your reply. it's like this, When I do a start transaction and do
a row modification, it locks the hole table, it there anyway to lock only th
e
modified row ?
Thanks alot for u r time and response,
-Aruna
"Tibor Karaszi" wrote:

> In addition to the other posts, supporting the query with good and usable
indexes reduces the risk
> of getting table locks.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sniper" <kakopappa@.hotmail.com> wrote in message
> news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
>
>|||Not directly. As I said, make sure that you support the WHERE clauses you us
e in the UPDATE with
proper indexes and you are most likely to end up with row locks. How did you
determine that the
whole table is locked?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:ABBBC0D2-0273-435A-88FA-135E1F554DB4@.microsoft.com...
> Hi Tibor,
> Thanks for your reply. it's like this, When I do a start transaction and d
o
> a row modification, it locks the hole table, it there anyway to lock only
the
> modified row ?
> Thanks alot for u r time and response,
> -Aruna
>
> "Tibor Karaszi" wrote:
>|||When I open the table from the Enterprise Manager it doesn't show the table
data. or when I do a SELECT * FROM table it's waitting till the table lock t
o
be released.
thanks,
Sniper
"Tibor Karaszi" wrote:

> Not directly. As I said, make sure that you support the WHERE clauses you
use in the UPDATE with
> proper indexes and you are most likely to end up with row locks. How did y
ou determine that the
> whole table is locked?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sniper" <kakopappa@.hotmail.com> wrote in message
> news:ABBBC0D2-0273-435A-88FA-135E1F554DB4@.microsoft.com...
>
>|||Use sp_who, sp_who2, sp_lock etc to determine what types of locks the connec
tion which has modified
the data has on the table. Also, you still haven't provided us with the tabl
e structure and what
indexes you have on the table and what your UPDATE statement look like.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:5923EEE1-890F-454C-B694-7E9279D605F3@.microsoft.com...
> When I open the table from the Enterprise Manager it doesn't show the tab
le
> data. or when I do a SELECT * FROM table it's waitting till the table lock
to
> be released.
> thanks,
> Sniper
> "Tibor Karaszi" wrote:
>|||Also, if you have updated some row, and haven't committed that transaction,
any process which tries
to read *all* rows will be blocked.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:5923EEE1-890F-454C-B694-7E9279D605F3@.microsoft.com...
> When I open the table from the Enterprise Manager it doesn't show the tab
le
> data. or when I do a SELECT * FROM table it's waitting till the table lock
to
> be released.
> thanks,
> Sniper
> "Tibor Karaszi" wrote:
>

How to change the default dateformat?

My Sql Server connection has default dateformat to "ymd".
I have to change the dateformat to "dmy" each time when I insert or
update a datetime table column e.g.with '31.12.2005' using the
"set dateformat dmy" command. Does anybody know, how to change the
default dateformat to avoid to perform the set dateformat on
each connection?
thanks
Libor
Look here:
You can change the settings of the server, but if yor provider will
change the settings during the login process and sets it back to the
default language settings of the user you have to convert the date in a
explicit format or change the codepage of the user:
http://groups.google.de/group/micros...2557646f8b9a9b
http://groups.google.de/group/micros...144e7348a7b6ff
HTH, Jens Suessmeyer.

How to change the default dateformat?

My Sql Server connection has default dateformat to "ymd".
I have to change the dateformat to "dmy" each time when I insert or
update a datetime table column e.g.with '31.12.2005' using the
"set dateformat dmy" command. Does anybody know, how to change the
default dateformat to avoid to perform the set dateformat on
each connection?
thanks
LiborLook here:
You can change the settings of the server, but if yor provider will
change the settings during the login process and sets it back to the
default language settings of the user you have to convert the date in a
explicit format or change the codepage of the user:
http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/6496fbdbbd4cffd9/952557646f8b9a9b?lnk=st&q=serversettings+date+format&rnum=1&hl=en#952557646f8b9a9b
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/6f543ebd963f6e20/46144e7348a7b6ff?lnk=st&q=%22Jens+Suessmeyer%22+boss+tibor&rnum=1&hl=en#46144e7348a7b6ff
HTH, Jens Suessmeyer.

How to change the default dateformat?

My Sql Server connection has default dateformat to "ymd".
I have to change the dateformat to "dmy" each time when I insert or
update a datetime table column e.g.with '31.12.2005' using the
"set dateformat dmy" command. Does anybody know, how to change the
default dateformat to avoid to perform the set dateformat on
each connection?
thanks
LiborLook here:
You can change the settings of the server, but if yor provider will
change the settings during the login process and sets it back to the
default language settings of the user you have to convert the date in a
explicit format or change the codepage of the user:
http://groups.google.de/group/micro...52557646f8b9a9b
http://groups.google.de/group/micro...6144e7348a7b6ff
HTH, Jens Suessmeyer.

How to change the default "filter" on Executionlog?

In the ReportServer database there is a table named Executionlog, which
contains data on the reports executed. But at any given time, it only seems
to contain data for the last 2 months. How do I change this filter?Found it!
Open SQL Server Management Studio and connect to Reporting Services. Right
click on the server name in the Object Explorer and select Properties. Under
the "Logging" folder you can change days of history.
"Soren" wrote:
> In the ReportServer database there is a table named Executionlog, which
> contains data on the reports executed. But at any given time, it only seems
> to contain data for the last 2 months. How do I change this filter?

How to change the Datatype of a column in SQL 7

Hi Everyone,

I am trying to change a column for a table by using Transact-SQL 7

The table name is ActionLog and the column name is ActiveSys

Before change:

ActiveSys datatype char(12)

After change:

ActiveSys datatype varchar(30)

I use this command:

alter table ActionLog alter column ActiveSys varchar(30) not null

but the system keeps giving me this error message:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'column'.

I follow the syntax provide by the MS: (and I search on Google and found none of the standard SQL syntax works.)

Syntax

ALTER TABLE table
{ [ALTER COLUMN column_name
{ new_data_type [ (precision[, scale] ) ]
[ NULL | NOT NULL ]
| {ADD | DROP} ROWGUIDCOL
}
]
| ADD
{ [ <column_definition> ]
| column_name AS computed_column_expression
}[,...n]
| [WITH CHECK | WITH NOCHECK] ADD
{ <table_constraint> }[,...n]
| DROP
{ [CONSTRAINT] constraint_name
| COLUMN column
}[,...n]
| {CHECK | NOCHECK} CONSTRAINT
{ALL | constraint_name[,...n]}
| {ENABLE | DISABLE} TRIGGER
{ALL | trigger_name[,...n]}
}

Can anyone help me? or Point out what is wrong with my code?

Thanks in advance.

dont use the keyword column, just use

alter table ActionLog alter ActiveSys varchar(30) not null

|||Thanks Shyam. I found either your code or my code are wrong. the reason our code does not work because the compatibility issues. before running the code, we should run:

EXEC sp_dbcmptlevel '<Database name>', 70
alter table ActionLog alter ActiveSys varchar(30) not null
EXEC sp_dbcmptlevel '<Database name>', 65

Cheers.

How to change the data type of an IDENTITY column

Hi.
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
Michael
Why are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>
|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:

> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> data
> now
>
>
|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>
|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:

> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> ways!
> constraints
>
>
|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
sql

How to change the data type of an IDENTITY column

Hi.
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
MichaelWhy are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:
> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> > Hi.
> > I have an existing table with an INT IDENTITY column; it contains some
> data
> > rows.
> > I want to change the identity column type to smallint (the data values are
> > within range) but am stumped. Here's what I'm trying to do (assume the
> > identity column is called Id):
> >
> > Create tempId smallint column
> > save Id values in tempId
> > Drop Id column
> > Recreate Id column as identity smallint.
> > *** Update Id column with values from tempId
> > Drop tempId column
> >
> > As you know, it fails on the Update step above: cannot update identity
> > column (I've tried set identity_insert on and that makes no difference -
> now
> > where's the set identity_update function...)
> >
> > Can anyone suggest a way of doing it?
> > Thanks
> > Michael
> >
> >
> >
>
>|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:
> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> > Adam,
> > Thanks, but it needs to be an identity column. And I can have it both
> ways!
> > I've just found a way to do what I want:
> > Save the data into a temp table
> > delete the data
> > drop the int identity column
> > recreate the identity column as smallint
> > identity_insert on
> > insert rows from temp table
> > identity_insert off
> > drop temp table.
> >
> > A bit long winded, but it works. (I've left out minor detail re.
> constraints
> > etc.)
> > Thanks
> > Michael
> >
>
>|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict for
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!
kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!
|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!

How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict for
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||But no method to change the collation of all field in all table of a
database? if sepcify collation in the join statement, then all sql statement
need to use such method, quite trouble...
I know there is alter column..., but I also know this method can't work on
primary, foreign key, index field..., right?
"MC" wrote:
> Meanwhile, you can specify collation in the join statement. Check for
> COLLATE.
> MC
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> > when I join two table, I got the error "Cannot resolve collation conflict
> > for
> > equal to operation.", I know this is due to the collation is different for
> > two table, so I want to write a script to change all field in all table of
> > a
> > database to use the same collation, how to write that script?
> > Thx!!
>
>|||kei
Have you checked it?
"kei" <kei@.discussions.microsoft.com> wrote in message
news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
> But no method to change the collation of all field in all table of a
> database? if sepcify collation in the join statement, then all sql
> statement
> need to use such method, quite trouble...
> I know there is alter column..., but I also know this method can't work on
> primary, foreign key, index field..., right?
> "MC" wrote:
>> Meanwhile, you can specify collation in the join statement. Check for
>> COLLATE.
>> MC
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
>> > when I join two table, I got the error "Cannot resolve collation
>> > conflict
>> > for
>> > equal to operation.", I know this is due to the collation is different
>> > for
>> > two table, so I want to write a script to change all field in all table
>> > of
>> > a
>> > database to use the same collation, how to write that script?
>> > Thx!!
>>|||I still don't know the answer, could you write more explicit? I check
"Changing Collations" in BOL, and it only teach how to change collation for
one column only, and with limitation that if the column is an index..., then
the method is not work, so could you write the solution more clearly.
Thx!!
"Uri Dimant" wrote:
> kei
> Have you checked it?
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
> > But no method to change the collation of all field in all table of a
> > database? if sepcify collation in the join statement, then all sql
> > statement
> > need to use such method, quite trouble...
> > I know there is alter column..., but I also know this method can't work on
> > primary, foreign key, index field..., right?
> >
> > "MC" wrote:
> >
> >> Meanwhile, you can specify collation in the join statement. Check for
> >> COLLATE.
> >>
> >> MC
> >>
> >>
> >> "kei" <kei@.discussions.microsoft.com> wrote in message
> >> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> >> > when I join two table, I got the error "Cannot resolve collation
> >> > conflict
> >> > for
> >> > equal to operation.", I know this is due to the collation is different
> >> > for
> >> > two table, so I want to write a script to change all field in all table
> >> > of
> >> > a
> >> > database to use the same collation, how to write that script?
> >> > Thx!!
> >>
> >>
> >>
>
>|||You need to alter each column that has text datatype (varchar, char....).
Alternatively, you can script your database in enterprise manager and
specify 7.0 compatibility. That should give you script without collations.
Then you could execute that script and you get empty database. All thats
left for you is transfer data....
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:26C46269-DC86-4102-89A5-C9195EB47A57@.microsoft.com...
>I still don't know the answer, could you write more explicit? I check
> "Changing Collations" in BOL, and it only teach how to change collation
> for
> one column only, and with limitation that if the column is an index...,
> then
> the method is not work, so could you write the solution more clearly.
> Thx!!
> "Uri Dimant" wrote:
>> kei
>> Have you checked it?
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
>> > But no method to change the collation of all field in all table of a
>> > database? if sepcify collation in the join statement, then all sql
>> > statement
>> > need to use such method, quite trouble...
>> > I know there is alter column..., but I also know this method can't work
>> > on
>> > primary, foreign key, index field..., right?
>> >
>> > "MC" wrote:
>> >
>> >> Meanwhile, you can specify collation in the join statement. Check for
>> >> COLLATE.
>> >>
>> >> MC
>> >>
>> >>
>> >> "kei" <kei@.discussions.microsoft.com> wrote in message
>> >> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
>> >> > when I join two table, I got the error "Cannot resolve collation
>> >> > conflict
>> >> > for
>> >> > equal to operation.", I know this is due to the collation is
>> >> > different
>> >> > for
>> >> > two table, so I want to write a script to change all field in all
>> >> > table
>> >> > of
>> >> > a
>> >> > database to use the same collation, how to write that script?
>> >> > Thx!!
>> >>
>> >>
>> >>
>>

How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict fo
r
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!

How to change the attribut name?

Hi All,

Suppose I have created one table say "Customer"

Customer (cust_no, cust_name,cust_address)

I want to change the attribute name "cust_no" to "cust_number".

How to do it using ALTER command.

Pls give me the exact syntax for this.

Regards

Abdul

Hi abdul,

The safest way to do so is using the following:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
EXECUTE sp_rename N'dbo.Customer.cust_no', N'Tmp_cust_number', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.Customer.Tmp_cust_number', N'cust_number', 'COLUMN'
GO
COMMIT

(An easy way to generate a script like this yourself is changing the layout through SQL Server Management Studio, right-clicking the table, selecting "design", then making the change, and before doing anything else, clicking the left-most button labelled "Generate change script").

HTH.

Peter

How to change table names (Table, Table1, Table2, etc.) returned from SP

Hi all,
How do I customize table names returned from the stored procedure?
For example, I have a stored procedure that is something like this:
SELECT * FROM Employee
SELECT * FROM Employer
SELECT * FROM HealthInsurance
This SP returns multiple tables to the VB.NET application. Now, the names of
the tables retuned in the dataset are: Table, Table1, and Table2. Is there a
way to customize those 3 names so the Table is "Employee", Table1 is
"Employer", Table2 is "HealthInsurance"?
I would then get the Employee data by calling:
ds.Tables("Employee")
instead of:
ds.Tables("Table")
Thanks for your time
Goran Djuranovic
hi Goran,
Goran Djuranovic wrote:
> Hi all,
> How do I customize table names returned from the stored procedure?
> For example, I have a stored procedure that is something like this:
> SELECT * FROM Employee
> SELECT * FROM Employer
> SELECT * FROM HealthInsurance
> This SP returns multiple tables to the VB.NET application. Now, the
> names of the tables retuned in the dataset are: Table, Table1, and
> Table2. Is there a way to customize those 3 names so the Table is
> "Employee", Table1 is "Employer", Table2 is "HealthInsurance"?
> I would then get the Employee data by calling:
> ds.Tables("Employee")
> instead of:
> ds.Tables("Table")
you have to customize the in memory dataset provided by ADO.Net client side,
naming your datatable accordingly to your needs as "TableX" is the default
provided by ADO.Net when no personal provided value is available, so the
TableMapping method come to hand like
Dim da As New OleDbDataAdapter(strSQL, strConn)
da.TableMappings.Add("Tabl1e", "Customers")
Dim ds As New DataSet()
da.Fill(ds)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to change QUOTED_IDENTIFIER for table?

Hi,
I want to change QUOTED_IDENTIFIER setting for table because it makes
problem with DBCC DBREINDEX. I don't want to drop and recreate table because
it is huge. How can I change it?
I use SS2000, SP4, Win 2000 Advance, SP4.
Thanks in advance
Nikola MilicSyntax
SET QUOTED_IDENTIFIER { ON | OFF }
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Nikola Milic" wrote:

> Hi,
> I want to change QUOTED_IDENTIFIER setting for table because it makes
> problem with DBCC DBREINDEX. I don't want to drop and recreate table becau
se
> it is huge. How can I change it?
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>
>|||Hi,
You didn't understand me. Table was created with SET QUOTED_IDENTIFIER OFF
and I want to change it to be ON.
Thanks for reply
Nikola
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:1A122A1B-1631-42D2-8CB7-5E2BF2876E69@.microsoft.com...
> Syntax
> SET QUOTED_IDENTIFIER { ON | OFF }
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and
> time
> asking back if its 2000 or 2005]
>
> "Nikola Milic" wrote:
>|||On Thu, 9 Mar 2006 08:36:05 +0200, Nikola Milic wrote:

>Hi,
>You didn't understand me. Table was created with SET QUOTED_IDENTIFIER OFF
>and I want to change it to be ON.
Hi Nikola,
As far as I know, there's no easy way to do this. You'll have to
1. Drop all constraints referencing the table
2. Drop all triggers on the table
3. Rename the table
4. Create a new table with the required setting and the same structure
5. Use INSERT INTO NewTable (Col1, Col2, ...) SELECT Col1, Col2, ...
FROM OldTable to move over all existing data
6. Recreate all triggers you dropped in step 2
7. Recreate all constraints you dropped in step 1
And finally (but you could postpone this a while, to give you some other
fallback scneario options)
8. Drop the "old" table
Hugo Kornelis, SQL Server MVP|||Thanks for reply,
I said in my first post that I don't want to drop and recreate table
because it is huge.
I will avoid problem with DBCC DBREINDEX by dropping calculated column (it
makes problem) and recreating it after re-indexing.
Regards
Nikol
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:tjd112lpcn6bo75hfct2vhvhio6anj6sdc@.
4ax.com...
> On Thu, 9 Mar 2006 08:36:05 +0200, Nikola Milic wrote:
>
> Hi Nikola,
> As far as I know, there's no easy way to do this. You'll have to
> 1. Drop all constraints referencing the table
> 2. Drop all triggers on the table
> 3. Rename the table
> 4. Create a new table with the required setting and the same structure
> 5. Use INSERT INTO NewTable (Col1, Col2, ...) SELECT Col1, Col2, ...
> FROM OldTable to move over all existing data
> 6. Recreate all triggers you dropped in step 2
> 7. Recreate all constraints you dropped in step 1
> And finally (but you could postpone this a while, to give you some other
> fallback scneario options)
> 8. Drop the "old" table
> --
> Hugo Kornelis, SQL Server MVP

How to Change Precision and Scale in MS SQL Server 2000

Hello,

My table was created by importing from an Excel spreadsheet. Typical fields in the rows are a date and various stock values like Open, High, Low, and Close. Unfortunately, many of the values have the wrong characteristics. These are my problems:

1. The date field has time in addition to the date. I don't want the
time in this field.

2. Many of the amount fields have a large precision, for example,
1.9399999999999999. I want to allow for a precision of 5 and a
scale of 2.

Can I make changes to my table at this point? I looked into Design Table but I don't see any feature allowing me to make changes 'on the fly'.

Any suggestions are welcome.

JoeHowdy,

Usually you can query a datetime column to extract just the date, so dont worry too much about that.

The column precision can be changed ( on the fly as it were ) using an alter table command ( see BOL ) that will automatically change the column precision and in the process round the values to what you want.

Cheers,

SG
( PS - theres nothing quite like a V8 Holden ute.....)|||Thank you for the reply. It occurs to me that one can end up creating a great many different queries if one is interested in comparing possible results/outputs. If DBA's want to save their queries for possible use later do they typically like to store them in a standard folder? Or should one create a special folder within the 'Databases' folder?

Thanks again. I am still new to working with Query Analyzer.

Joe|||I would also like to ask anyone if he or she could advise me as to how I can display only a date (without the time) when I do a query using Query Analyzer. I really don't want to see time displayed.

Can someone assist?

Thanks again.

Joe|||Howdy,

Well, sadly SQL doesnt handle splitting out dates from datetime fields very well.

Assuming you had a column called DATE in a table called INFO, if you want to display JUST the date, you need to extract the hour, min, seconds as characher values then reconstruct into a character format ( and later change to datetime , which by the way gives a defualt date of 01/01/1900)

Now, assuming you have a small table called INFO, with one column called DATE with one value of 2003-10-10 17:23:34

If you xxtract using time the following code -

select convert(varchar(2),datepart(hh,DATE))
+':'+convert(varchar(2),datepart(mm,DATE))
+':'+convert(varchar(2),datepart(ss,DATE))
from INFO

This gives -

17:23:34 ( but in varchar format).

Note too that single digit values WILL NOT have a '0' put in front of them unless you test for it & code for it accordingly.

Unless you get all dates into the same format - e.g. all varchar/char or all in datetime, mixing & matching will give you a headache.

Cheers,

SG.

How to change owner?

What is the easiest way to change owner of some db object (table, sp, etc.)?
Thanks!from Books Online:

Syntax
sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'

Example
EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW'|||This was really fast! :)
Thank you very much!sql

How to change or alter a user defined data type?

Hi all,
I already read this: http://vyaskn.tripod.com/administration_faq.htm#q12
and it's ok for the table/columns but I can NOT drop my user defined data
type using sp_droptype because it's already used in some Stored procs :(
Is there a solution WITHOUT a "delete & re-create" of these stored procs ?
Lilian.This example should demonstrate how to swap out the type. Note that the
rename affects tables, but not stored procs (so you won't have to recompile
your procedures, just hope they don't get called during the brief period
where the type doesn't exist).
-- originally, we create a datatype called fax,
-- and made it able to accept 32 characters
EXEC sp_addType 'fax', 'VARCHAR(32)'
GO
-- so we created a table that uses this datatype
CREATE TABLE dbo.foobar0
(
[fax] fax
)
GO
-- and a simple stored procedure as well
CREATE PROC dbo.foobar1
@.fax fax
AS
BEGIN
DECLARE @.fax2 fax
END
GO
-- now, we realize that fax numbers on
-- jupiter can contain 64 characters, so we
-- have to increase the size of the UDT
-- but we can't just alter the type, and we
-- can't drop it and re-create it either, without
-- altering or dropping / re-creating tables,
-- stored procedures, etc. that use the UDT
-- but we can use a little rename trick to
-- create an interim UDT that meets our needs
-- first, rename the existing UDT. This will
-- change table definitions but it will not alter
-- the text for a procedure / function
EXEC sp_rename 'fax', 'oldfax', 'USERDATATYPE'
GO
-- let's just make sure that it affected our table
-- but not our procedure:
EXEC sp_help foobar0
EXEC sp_helptext foobar1
GO
-- okay, now let's add the larger fax UDT back
-- into the system
EXEC sp_addtype 'fax', 'VARCHAR(64)'
GO
-- alter any tables / views that reference the old
-- fax UDT
ALTER TABLE foobar0 ALTER COLUMN [fax] fax
GO
-- now we should be able to drop the interim
-- UDT:
EXEC sp_droptype 'oldfax'
GO
-- (now let's clean up my silly example)
DROP TABLE dbo.foobar0
DROP PROCEDURE dbo.foobar1
EXEC sp_droptype 'fax'
GO
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:#pSUDo9PDHA.2036@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I already read this: http://vyaskn.tripod.com/administration_faq.htm#q12
> and it's ok for the table/columns but I can NOT drop my user defined data
> type using sp_droptype because it's already used in some Stored procs :(
> Is there a solution WITHOUT a "delete & re-create" of these stored procs
?
> Lilian.
>|||Sorry, you will have to recompile stored procedures that use the datatype as
in/out parameters, but not those that only use the type for local
variables...|||Very interesting...but do you "recompile" a stored proc by script ?
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:elXMq29PDHA.2676@.TK2MSFTNGP10.phx.gbl...
> Sorry, you will have to recompile stored procedures that use the datatype
as
> in/out parameters, but not those that only use the type for local
> variables...
>

How to change MM/DD/YYYY format to DD/MM/YYYY format by using SQL query.

I have a database table named EMP and I have a column named
DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
YYYY format by using the CONVERT command by SQL query.What is the
solution ?
Thank you.
Amritendu Paul
Hello,
For mm/dd/yyyy
Select CONVERT(CHAR(10),columns_name,101) from Table_name
For DD/MM/YY
Select CONVERT(CHAR(10),columns_name,103) from Table_name
Thanks
Hari
<amripaul@.gmail.com> wrote in message
news:1181557806.349855.6210@.x35g2000prf.googlegrou ps.com...
>I have a database table named EMP and I have a column named
> DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
> the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
> YYYY format by using the CONVERT command by SQL query.What is the
> solution ?
> Thank you.
> Amritendu Paul
>
|||Thanx for your help.

How to change db schema name in DSV?

We have number of cubes which built under ‘abc’ database schema, but recently database schema has changed to ‘xyz’ (table names still are same), so every dimension tables, fact table need delete not change when refresh in DSV (BI Studio SQL server2005). If delete tables in DSV, means rebuild dimensions, and cube in the BI Studio. Is there a way to change schema name in DSV instead of rebuild cube from scratch?

Thanks in advance for advice.

Hello!

Open your DVS and select xml source and directly replace DbSchemaName="abc" with DbSchemaName="xyz".

Hth,

Radim
|||Great! It works.

Thank you so much Radim.
sql