Change local variable inside query

/*Given*/

CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)

/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.

This doesn't compile of course, but is there
a way to accomplish the same thing?
*/

DECLARE @.ndx int

SET @.ndx = 1

SELECT

(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT

FROM _T1sub a

/*Output would look like this:*/

FKplusWT
----
11
22
33

/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/

thanks, Otto PorterOn Sat, 02 Oct 2004 12:20:48 -0600, Otto Porter wrote:

>I know, I can get this output just by adding
>FK+WT. This is not about that.
>This is about setting vars inside a query

Hi Otto,

It's not possible to change the value of a variable during the execution
of a SELECT statement. At least not the way you are trying to do it.

You can of course do
SELECT @.var = ..., @.var = ...
FROM table
WHERE ...
but I assume that this is not what you want. You can't mix this format of
the SELECT statement with a SELECT that outputs a result set.

The way I read your example, it would be very easy to have queries where
the result would be dependent on the order in which rows are processed by
SQL Server. Since SQL Server is entirely free in it's choice of processing
order, the results would be unexpected and might even vary from execution
to execution.

Check out the following link to find some good examples of the possible
effects of unexpected processing order on assignments with the SELECT
statement:
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Change included columns when modifying table

Is there a reason you can't modify included columns when modifying an index on a table?

Well, the primary reason would be due to the fact that adding additional included columns would touch all of the leaf pages in an index, and probably force a fair majority of pages to split multiple times, so in reality most of the time recreating the index from scratch would probably be faster and more efficient once completed. Additionally, a heavy heavy number of disk seeks would be incurred jumping from the position last updated in the index to the table data to read the data to be added and then jump back again.

Basically, I'm sure you'd find that rebuilding the index will almost always give you both faster results, and additionally a more efficient (clean) index when the operation completes.

HTH,

|||I full appreciate that the index needs to be rebuilt, however SSMS manages that for you when you change the columns, clustering, file groups etc. So why can't I change the included columns and SSMS just do a drop_existing.

Change Identity value

I changed a column on an existing table to be a Identity column but when I
try to insert a record I get an error that the id already exists. Is there a
way for me to change the value so identity returns a higher #?Are you passing the identity value?
AMB
"Joe" wrote:

> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>
>|||You could use identity_insert to insert a row with the highest value
and then turn it off. Any inserts after that will increment sequentially.
ie.
create table test ( id int identity (1,1), col varchar(10) )
insert into test (col) values ('row1')
insert into test (col) values ('row2')
insert into test (col) values ('row3')
set identity_insert test on
insert into test (id, col) values (10,'row4')
set identity_insert test off
insert into test (col) values ('row5')
select * from test
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:O5mJERHEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>|||Joe,
Look at the DBCC CHECKIDENT command in the Books Online. If your table
containing the identity column was called jobs,
DBCC CHECKIDENT (jobs, RESEED, 30)
would force the new value to 30.
On Fri, 11 Feb 2005 14:59:57 -0500, "Joe"
<J_no_spam@._no_spam_Fishinbrain.com> wrote:

>I changed a column on an existing table to be a Identity column but when I
>try to insert a record I get an error that the id already exists. Is there
a
>way for me to change the value so identity returns a higher #?
>

change font weight

i have one doubt

how to change font weight ?Because i have two records in my table i need to change that particular records only i need to change "BOLD"

thanx

You can return this information as an additional column as part of your SQL query. Then in design mode, highlight the details row (or individual testboxes) and make the font weight an expression based on the column in you dataset. e.g.

SELECT type
, font_weight = CASE type WHEN 'special' THEN 'Bold' ELSE 'Normal' END
FROM some_table

Then the expression will read:

=Fields!font_weight.Value

You can apply this technique for most properties not just FontWeight.

Change FK error: "Unable to delete relationship..."

Hello,
I have three tables in my existing database, (in a SQL Server 2000 server).
- a "Students" table,
- a "Classes" table, and
- a "StudentIsInClass" table.
The "StudentIsInClass" table is the relationship table linking a record in
the Students table to a record in the Classes table. This is effected with
two foreign keys:
FK_StudentIsInClass_Students
FK_StudentIsInClass_Classes
Recently, there was a problem in our application when changing the
"StudentNumber" for a Student. This is the primary key and so I get an erro
r
because of the relationship. The fix I want to apply is to change the
foreign key FK_StudentIsInClass_Students. I need to turn on the function of
"Cascade Update Related Fields". I use the GUI functions in SQL Server
Enterprise Manager to do this but, when I try to save the change, I get an
error:
'Students' table
- Unable to delete relationship 'FK_StudentIsInClass_Students'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]'FK_StudentIsInClass_Students' ''
[Microsoft][ODBC SQL Server Driver][SQL Server]'''
(The ' result because my SQL Server is running on a foreign language
server, so I can't read the exact error text.)
Does anyone have a clue why I would get this error? Any suggestions for
what to do about it? I appreciate any advice and help.
Many thanks in advance,
JustinJustin Little (JustinLittle@.discussions.microsoft.com) writes:
> Recently, there was a problem in our application when changing the
> "StudentNumber" for a Student. This is the primary key and so I get an
> error because of the relationship. The fix I want to apply is to change
> the foreign key FK_StudentIsInClass_Students. I need to turn on the
> function of "Cascade Update Related Fields". I use the GUI functions in
> SQL Server Enterprise Manager to do this but, when I try to save the
> change, I get an error:
> 'Students' table
> - Unable to delete relationship 'FK_StudentIsInClass_Students'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]'FK_StudentIsInClass_Students' ''
> [Microsoft][ODBC SQL Server Driver][SQL Server]'''
> (The ' result because my SQL Server is running on a foreign language
> server, so I can't read the exact error text.)
> Does anyone have a clue why I would get this error? Any suggestions for
> what to do about it? I appreciate any advice and help.
It would certainly have helped to see the text of the error message.
I assume that the foreign language does not use the Latin script?
Anyway, rather than using Enterprise Manager for chaning tables, which
is dangerous as the table designer is buggy, use Query Analyzer and
type the DLL.
DECLARE @.err int
BEGIN TRANSACTION
ALTER TABLE StudentsIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students
SELECT @.err = @.@.error
IF @.err = 0
BEGIN
ALTER TABLE StudentsIsInClass ADD
CONSTRAINT FK_StudentIsInClass_Students FOREIGN KEY (StudentID)
REFERECENS Students(StudentID)
ON UPDATE CASCADE
ON DELETE CASCADE
SELECT @.err = @.@.error
END
IF @.err = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
It is not unlikely that that this will fail too, but at least in that
case you will see an error number. You can then look up that number in
SQL Server Books Online, provided that you have an English version of
Books Online installed on your computer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Many thanks for your advice on using Query Analyzer to get a readable error
message. (Thanks also for the tip about bugs in the Enterprise Manager GUI,
I'll bare that in mind in the future.)
I took your SQL procedure and used it in Query Analyzer. I find that there
is an error that occurs when I try to execute the line that reads "ALTER
TABLE StudentIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students".
The error number I get is Msg 3728.
Server: Msg 3728, Level 16, State 1, Line 1
'dbo.FK_StudentIsInClass_Students' 並不是條件約束。
The server, as you guessed, does not use a Latin script. It's Chinese, so
the error message comes through in Chinese. On Query Analyzer I can see the
Chinese characters instead of a bunch of question marks. Books Online gives
the English message as:
'%.*ls' is not a constraint.
This seems strange to me. Also in Query Analyzer, in the Object Browser
I've expanded the tree beneath my "StudentIsInClass" table, and opened the
Constraints sub-section. There are three constraints listed there.
- PK_StudentIsInClass (StudentNumber, ClassID)
- FK_StudentIsInClass_Students (StudentNumber)
- FK_StudentIsInClass_Classes (ClassID)
If I try to right-click and delete any of these relationships I get the same
error message. (In fact, I can't delete any of constraints on any tables in
my entire database!)
Do you have any idea why I'm getting this confusing message?
Many thanks for your support.
Justin
PS: I'm connecting to the database using a user id called "bigbyte",
supplied to me by the ISP. The tables all belong to this user id (e.g.
"bigbyte.StudentIsInClass"), however I notice that the constraints are
members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do you
think that this could be the cause of my current problems?|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> The error number I get is Msg 3728.
> Server: Msg 3728, Level 16, State 1, Line 1
> 'dbo.FK_StudentIsInClass_Students' ''
> The server, as you guessed, does not use a Latin script. It's Chinese,
> so the error message comes through in Chinese. On Query Analyzer I can
> see the Chinese characters instead of a bunch of question marks. Books
> Online gives the English message as: '%.*ls' is not a constraint.
Now, that was an interesting message...

> PS: I'm connecting to the database using a user id called "bigbyte",
> supplied to me by the ISP. The tables all belong to this user id (e.g.
> "bigbyte.StudentIsInClass"), however I notice that the constraints are
> members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do
> you think that this could be the cause of my current problems?
Most probably. I've tried to recreate this situation, but I was not
successful. When I create tables owned by a non-dbo user, and set up
a constraint, the constraint is owned by that user. The same happens
if I create the tables as owned by dbo, and then change ownership.
Maybe there was a bug in some previous version/service pack of SQL Server
that could cause this situation.
Since this is an abnormal situation, it is difficult to give sugestions
that I know work. I would try to change ownership of the table to
dbo, and then back to bigbyte.
You may have to talk to your ISP about this, as you may need someone
with dbo rights to perform this operation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
I got dbo rights from the ISP for the day. I tried what you suggested about
changing the ownership of the table. Now I'm no SQL Server guru but I found
out that the stored procedure for doing this is called sp_changeobjectowner.
So I tried running the following command:
EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
I get an error message saying:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 6
3
無法變更物件 'StudentIsInClass' 的擁有人或其所屬的_物件
_為新的擁有人 'dbo' 已有相同名稱的物件。
or in English:
Cannot change owner of object '%ls' or one of its child objects because the
new owner '%ls' already has an object with the same name.
HOWEVER, when I try to run the same sp on a table that has no constraints,
it runs through just fine and changes the ownership. Could this mean that
the database has some inconsistencies in the constraints that it is storing?
When I first created these tables (about a year or more ago) I used the
"bigbyte" user. Later, I noticed that all the ownerships had changed to
"dbo". I guessed that the ISP had been mucking around and changed it all fo
r
reasons best known to them. (Like I said, I'm no guru so I don't know what
all this ownership stuff is really about anyway.) However, later I started
having trouble, particularly in using DTS scripts to export this live data t
o
my "dev" database and I asked them to change the ownership back to "bigbyte"
.
Now it seems all my tables are owned by "bigbyte" but all the constraints
are owned by "dbo".
Is there a stored procedure or something I can use to try to track down
multiple instances of the same constraint name, possibly owned by different
owners? Perhaps my original constraints are still bouncing around somewhere
inside the db's system tables, unknown to all of us?
Many thanks,
Justin
"Erland Sommarskog" wrote:

> Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> Now, that was an interesting message...
>
> Most probably. I've tried to recreate this situation, but I was not
> successful. When I create tables owned by a non-dbo user, and set up
> a constraint, the constraint is owned by that user. The same happens
> if I create the tables as owned by dbo, and then change ownership.
> Maybe there was a bug in some previous version/service pack of SQL Server
> that could cause this situation.
> Since this is an abnormal situation, it is difficult to give sugestions
> that I know work. I would try to change ownership of the table to
> dbo, and then back to bigbyte.
> You may have to talk to your ISP about this, as you may need someone
> with dbo rights to perform this operation.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> I got dbo rights from the ISP for the day. I tried what you suggested
> about changing the ownership of the table. Now I'm no SQL Server guru
> but I found out that the stored procedure for doing this is called
> sp_changeobjectowner.
> So I tried running the following command:
> EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
> I get an error message saying:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner,
> Line 63 ' 'StudentIsInClass' '''' 'dbo' ''
> or in English: Cannot change owner of object '%ls' or one of its child
> objects because the new owner '%ls' already has an object with the same
> name.
> HOWEVER, when I try to run the same sp on a table that has no
> constraints, it runs through just fine and changes the ownership. Could
> this mean that the database has some inconsistencies in the constraints
> that it is storing?
You bet!
At this point, I would create a new set of tables copy all data over to
the new tables.
If you don't have a script saved for creating the tables, you can use
the Enterprise Manager to generate scripts. Make sure that you get triggers,
indexes and foreign keys.
Now, let's see what is the best way to have these two sets of tables in
parallel. I think the best is that youy edit the script so that all
table and trigger names are prefixed by "guest.". Constraints should not
be prefixed. Then use INSERT to copy data over. Take the occassion to add
ON UPDATE CASCADE.
Once this is done drop the existing tables - and let's hope *that* is
possible.
Finally, run sp_changeobjectowner to change ownership to bigbyte or dbo,
which ever you feel best.

> When I first created these tables (about a year or more ago) I used the
> "bigbyte" user. Later, I noticed that all the ownerships had changed to
> "dbo". I guessed that the ISP had been mucking around and changed it
> all for reasons best known to them. (Like I said, I'm no guru so I
> don't know what all this ownership stuff is really about anyway.)
Ownership of objects is like ownership of files in a file system.
But there is a second side of the coin. In SQL 2000, an owner is also a
schema. (This changes in SQL 2005). A schema is essentially a namespec.
A user has a default schema, which in SQL 2000 is always equal to his
user name. When you say "SELECT * FROM tbl" without specifying schema/owner,
SQL Server first looks in your default schema, then in the default schema of
the database, which in SQL 2000 always is dbo.
The guest user is a pre-defined user, and also has a schema, which I
suggested that you should use above, to permit you to have two sets of
tables at the same time.
I should add that in SQL 2000, having all objects owner by dbo - and thus
in the dbo schema - is what people do 99% of the time.

> Is there a stored procedure or something I can use to try to track down
> multiple instances of the same constraint name, possibly owned by
> different owners? Perhaps my original constraints are still bouncing
> around somewhere inside the db's system tables, unknown to all of us?
You would have to run queries on sysobjects to find out. But this database
seems a bit mashed, so you will need some brute force to get it working.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 characters.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.
Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>
|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:

> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
>

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 characters.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:
> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> > SQL 2005
> >
> > I have a table with a number of nvarchar fields. One field in the midde
> > was
> > 1000 in length. I ran a query with:
> >
> > ALTER TableName
> > ALTER ColumnName nvarchar(2000).
> >
> > This apeared to increase the field size to 2000 (as seen in Server
> > Management Studio), but it will still not store any more than 1000
> > characters.
> >
> > Have I done it incorrectly.
> >
> > There appears to be still space in my record as I can add a field at the
> > end
> > 2000 long and that works correctly.
> >
> > Thanks,
> > Steve.
> >
>

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 character
s.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and
I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:

> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
>

change default value of column

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

try doing an ALTER TABLE|||I tried but it was not working|||

Quote:

Originally Posted by sourabhmca

I tried but it was not working

if this is a one time thing, try creating a new field then drop the existing one.|||oh yeah, you have to rename the field to the old field name after dropping the old one.

IF this is a one time thing...|||

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Hey try like this...

create table abc (a int,b smalldatetime constraint df_abc_b default getdate())
insert into abc(a) values(1)
select * from abc

alter table abc drop constraint df_abc_b
alter table abc add constraint df_abc_b default(getdate()+0.435) for b
insert into abc(a) values(1)
select * from abc

Change Date Format from mm/dd/yyyy to dd/mm/yyyy

Hi,
I have a query to retrieve data from a table for the give two dates(from and
to) in a stored procedure.
select * from employees where (DateJoined BETWEEN @.FromDate AND @.ToDate)
now I have give the dates like this to get the results
@.FromDate =01/01/2005(mm/dd/yyyy)
@.ToDate=6/2/2005(mm/dd/yyyy)
but I would like to give the date in this format (dd/mm/yyyy) like
@.FromDate =01/01/2005(dd/mm/yyyy)
@.ToDate=2/6/2005(dd/mm/yyyy)
can someone help me with this(I know there is a way in which you can specify
in select query itself, but forgot :-) )
Thanks
KiranWhy do you need to express the date in a special format? Why not let the end
user use the format the
end user want (based on regional settings) and let the application send a fo
rmat to SQL server which
is language neutral?
See http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kiran" <kiran_nospam@.gmail.com> wrote in message news:Ot61f56ZFHA.3840@.tk2msftngp13.phx.gb
l...
> Hi,
> I have a query to retrieve data from a table for the give two dates(from a
nd to) in a stored
> procedure.
> select * from employees where (DateJoined BETWEEN @.FromDate AND @.ToDate)
> now I have give the dates like this to get the results
> @.FromDate =01/01/2005(mm/dd/yyyy)
> @.ToDate=6/2/2005(mm/dd/yyyy)
> but I would like to give the date in this format (dd/mm/yyyy) like
> @.FromDate =01/01/2005(dd/mm/yyyy)
> @.ToDate=2/6/2005(dd/mm/yyyy)
> can someone help me with this(I know there is a way in which you can speci
fy in select query
> itself, but forgot :-) )
> Thanks
> Kiran
>|||Kiran wrote:
> Hi,
> I have a query to retrieve data from a table for the give two
> dates(from and to) in a stored procedure.
> select * from employees where (DateJoined BETWEEN @.FromDate AND
> @.ToDate)
> now I have give the dates like this to get the results
> @.FromDate =01/01/2005(mm/dd/yyyy)
> @.ToDate=6/2/2005(mm/dd/yyyy)
> but I would like to give the date in this format (dd/mm/yyyy) like
> @.FromDate =01/01/2005(dd/mm/yyyy)
> @.ToDate=2/6/2005(dd/mm/yyyy)
> can someone help me with this(I know there is a way in which you can
> specify in select query itself, but forgot :-) )
> Thanks
> Kiran
Always use a portable date format in SQL... or else.
YYYYMMDD
or
YYYY-MM-DDThh:mm:ss.mmm
David Gugick
Quest Software
www.imceda.com
www.quest.com

change date format

I have a table with a date field. I need to convert the date so that it
returns in a text format of DDMMYY - no separators.
How do I do this?
I have done it like as follows: Is there a better way of doing it?
Here is what I have:
right(RTrim(convert(char(8),TrnDate,12)),2) +
substring(convert(char(8),TrnDate,12),3,2) +
left(convert(char(8),TrnDate,12),2) as DteVal
"Newbie" <noidea@.nospam.com> wrote in message
news:%23gfQUJ0zEHA.1192@.tk2msftngp13.phx.gbl...
> I have a table with a date field. I need to convert the date so that it
> returns in a text format of DDMMYY - no separators.
> How do I do this?
>
|||On Sat, 20 Nov 2004 21:57:21 -0000, Newbie wrote:

>I have done it like as follows: Is there a better way of doing it?
Hi Newbie,
Have you tried
REPLACE (CONVERT(char(8), TrnDate, 103), '/', '')
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for the reply. I gave it a go but for the date 17/04/2000 it
returned
170420 instead of
170400
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:55ivp01419rdl6enh9v0lolt1cak6a5r14@.4ax.com...
> On Sat, 20 Nov 2004 21:57:21 -0000, Newbie wrote:
>
> Hi Newbie,
> Have you tried
> REPLACE (CONVERT(char(8), TrnDate, 103), '/', '')
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo's solution needs format 3, not 103:
This should work:
REPLACE (CONVERT(char(8), TrnDate, 3), '/', '')
Steve Kass
Drew University
Newbie wrote:

>Thanks for the reply. I gave it a go but for the date 17/04/2000 it
>returned
>170420 instead of
>170400
>
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>news:55ivp01419rdl6enh9v0lolt1cak6a5r14@.4ax.com.. .
>
>
>
|||On Sun, 21 Nov 2004 09:37:38 -0000, Newbie wrote:

>Thanks for the reply. I gave it a go but for the date 17/04/2000 it
>returned
>170420 instead of
>170400
Hi Newbie,
My bad. Halfway through my post, I forgot that you didn't want all four
digits of the year and I autopiloted back to a format that uses the
complete year.
Steve is correct: 103 should have been 3.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks - works a treat!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:i342q0dcnf10r39nn5m35q4u38cblgtiib@.4ax.com...
> On Sun, 21 Nov 2004 09:37:38 -0000, Newbie wrote:
>
> Hi Newbie,
> My bad. Halfway through my post, I forgot that you didn't want all four
> digits of the year and I autopiloted back to a format that uses the
> complete year.
> Steve is correct: 103 should have been 3.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...
Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:

> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...
|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
|||We used alter table...
"Tibor Karaszi" wrote:

> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
> > I have a table with a column of datatype var and it has to be changed to nvar.
> > The table contains millions of records and with an alter table my log get's
> > full, starts growing and in the end I my query stops with no result.
> > What is the best way to accomplish this change while keeping the data and
> > having not to much logging?
> >
> > Any help is appreciated.
> > I was thinking of an insert into a new table...|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
> > Make sure you have good backup firstly before doing what I can think out:
> > 1) If there are index on the column, drop them before changing its datatype.
> > 2) change the db recovery mode to 'simple'.
> > 3) shrink the db size and some other db size (including tempdb, or
> > considering change their recovery mode to 'simple' before shrinking) to free
> > more disk space.
> > 4) do the altering
> > 5) re-create the index.
> >
> > James
> >
> > "Zekske" wrote:
> >
> > > I have a table with a column of datatype var and it has to be changed to nvar.
> > > The table contains millions of records and with an alter table my log get's
> > > full, starts growing and in the end I my query stops with no result.
> > > What is the best way to accomplish this change while keeping the data and
> > > having not to much logging?
> > >
> > > Any help is appreciated.
> > > I was thinking of an insert into a new table...|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>> Notice my suggestion might still fail finally, and you should be sure of the
>> implication of changing recovery model.
>> James
>> "James Ma" wrote:
>> > Make sure you have good backup firstly before doing what I can think out:
>> > 1) If there are index on the column, drop them before changing its datatype.
>> > 2) change the db recovery mode to 'simple'.
>> > 3) shrink the db size and some other db size (including tempdb, or
>> > considering change their recovery mode to 'simple' before shrinking) to free
>> > more disk space.
>> > 4) do the altering
>> > 5) re-create the index.
>> >
>> > James
>> >
>> > "Zekske" wrote:
>> >
>> > > I have a table with a column of datatype var and it has to be changed to nvar.
>> > > The table contains millions of records and with an alter table my log get's
>> > > full, starts growing and in the end I my query stops with no result.
>> > > What is the best way to accomplish this change while keeping the data and
>> > > having not to much logging?
>> > >
>> > > Any help is appreciated.
>> > > I was thinking of an insert into a new table...|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
> >I did change the recovery model to simple so that doesn't help.
> > That's why I was thinking about doing a bulk import or something like that
> > but I'm not sure it's possible.
> >
> > Thanks anyway
> >
> > "James Ma" wrote:
> >
> >> Notice my suggestion might still fail finally, and you should be sure of the
> >> implication of changing recovery model.
> >>
> >> James
> >>
> >> "James Ma" wrote:
> >>
> >> > Make sure you have good backup firstly before doing what I can think out:
> >> > 1) If there are index on the column, drop them before changing its datatype.
> >> > 2) change the db recovery mode to 'simple'.
> >> > 3) shrink the db size and some other db size (including tempdb, or
> >> > considering change their recovery mode to 'simple' before shrinking) to free
> >> > more disk space.
> >> > 4) do the altering
> >> > 5) re-create the index.
> >> >
> >> > James
> >> >
> >> > "Zekske" wrote:
> >> >
> >> > > I have a table with a column of datatype var and it has to be changed to nvar.
> >> > > The table contains millions of records and with an alter table my log get's
> >> > > full, starts growing and in the end I my query stops with no result.
> >> > > What is the best way to accomplish this change while keeping the data and
> >> > > having not to much logging?
> >> > >
> >> > > Any help is appreciated.
> >> > > I was thinking of an insert into a new table...
>
>

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nva
r.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:

> I have a table with a column of datatype var and it has to be changed to n
var.
> The table contains millions of records and with an alter table my log get'
s
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatyp
e.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to fr
ee
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
>|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of t
he
> implication of changing recovery model.
> James
> "James Ma" wrote:
>|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes
in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>|||We used alter table...
"Tibor Karaszi" wrote:

> Did you use EM or ALTER TABLE to do the change. EM tends to do these chang
es in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>sql

change datatype of a column

what is the best (and fast) why to change a column's datatype from varchar to decimal ?
the table has 3 million records and the column is filled with data (no problem with converting the data to numeric).alter table tablename
alter column columnname float null|||tnx

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do ?
Thanks for your kind help
> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do ?
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', it
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:

> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do '
Thanks for your kind help> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do '
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', it
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:
> > in myDatabase, over 50 tables.
> > Every table got the field 'userid' , Now I need to change the field length
> > from char(10) to char(15)
> > How Can I do that ? Any simple and fast way to do '
> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do '
Thanks for your kind help> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do '
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', i
t
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:

> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Change Database Owner

Hello there
I don't member of DBO owners of my database on sql server
After i've created new table it has my name as an owner
How can i change the owner?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilUse sp_changeobjectowner
Markus|||sp_changedbowner
Changes the owner of the current database.
Syntax
sp_changedbowner [ @.loginame = ] 'login'
[ , [ @.map = ] remap_alias_flag ]
Arguments
[@.loginame =] 'login'
Is the login ID of the new owner of the current database. login is sysname,
with no default. login must be Microsoft SQL ServerT login or a Microsoft
Windows NT user that already exists. login cannot become the owner of the
current database if it already has access to the database through an
existing alias or user security account within the database. To avoid this,
drop the alias or user within the current database first.
[@.map =] remap_alias_flag
Is the value true or false, which indicates whether existing aliases to the
old database owner (dbo) are mapped to the new owner of the current database
or dropped. remap_alias_flag is varchar(5), with a default of NULL,
indicating any existing aliases to the old dbo are mapped to the new owner
of the current database. false indicates that existing aliases to the old
database owner are dropped.
Return Code Values
0 (success) or 1 (failure)
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ODXgIbn9FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I don't member of DBO owners of my database on sql server
> After i've created new table it has my name as an owner
> How can i change the owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||Hi Roy
Do you want to change the owner of the database (as your subject suggests)
or the owner of the tables?
I will assume, based on your message, that you want to change the table
owner, or have the table owner be 'dbo'.
DBO is a user name. It is a member of a role called db_owners. Members of
the db_owners role can do everything that the owner of the database can do,
within the database.
Other users can also be members of the db_owners role, but their user will
be something other than DBO. That sounds like your situation.
Normally, when a user creates a table, the owner of the table is the user
name of whoever creates it. So if DBO creates the table, it is owned by DBO,
if Roy, who might be a member of db_owners role, creates a table, it is
owned by Roy.
Members of the db_owners role do have a special permission that they can
create tables owned by other users:
CREATE TABLE dbo.mytable
(....)
Or, members of db_owners role can change the owner of existing objects.
EXEC sp_changeobjectowner 'roy.mytable', 'dbo'
Please see more details about this command in the Books Online., You might
also want to read up about login names and user names.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ODXgIbn9FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I don't member of DBO owners of my database on sql server
> After i've created new table it has my name as an owner
> How can i change the owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!
It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!