Database level lock: column

Showing posts with label column. Show all posts

Thursday, March 29, 2012

database in SQL2K need help in upading/adding 2 memo columns

I have database in SQL2K. I have a memo column called memo1. I want to
update memo with "dicount % "+memo1.
I want to insert "discount %" in front of memo1.
This is going to be just one time shot. There are more 20,000 records need
to be updated.
I would appreciate if someone can help me.
I can run VB program and script so if anyone can help me to create something
in VB, if it is not possible in SQL2K,
I would really appreciate.
ThanksHi,
IN SQL 2000 you could write a simple Update statement
Update Tablename
set memo1 = 'discount % ' + memo1
This will update all the 20K records in a single shot.
Did I answered your question?
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:u9x4e.14059$Fh2.5711@.trnddc04...
>I have database in SQL2K. I have a memo column called memo1. I want to
>update memo with "dicount % "+memo1.
> I want to insert "discount %" in front of memo1.
> This is going to be just one time shot. There are more 20,000 records
> need to be updated.
> I would appreciate if someone can help me.
> I can run VB program and script so if anyone can help me to create
> something in VB, if it is not possible in SQL2K,
> I would really appreciate.
> Thanks
>|||Hari:
Thanks for your quick response.
I already tried this and I am getting data type error.
Thx
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
> Hi,
> IN SQL 2000 you could write a simple Update statement
> Update Tablename
> set memo1 = 'discount % ' + memo1
> This will update all the 20K records in a single shot.
> Did I answered your question?
> Thanks
> Hari
> SQL Server MVP
> "Mac" <mac@.hotmail.com> wrote in message
> news:u9x4e.14059$Fh2.5711@.trnddc04...
>>I have database in SQL2K. I have a memo column called memo1. I want to
>>update memo with "dicount % "+memo1.
>> I want to insert "discount %" in front of memo1.
>> This is going to be just one time shot. There are more 20,000 records
>> need to be updated.
>> I would appreciate if someone can help me.
>> I can run VB program and script so if anyone can help me to create
>> something in VB, if it is not possible in SQL2K,
>> I would really appreciate.
>> Thanks
>|||Mac,
Looks like your datatype is TEXT. Inthat case you need to use UPDATETEXT
command to update.
See the below archive posts:-
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/11549ff4637eff45
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:3Jx4e.25741$k66.15806@.trnddc03...
> Hari:
> Thanks for your quick response.
> I already tried this and I am getting data type error.
> Thx
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
>> Hi,
>> IN SQL 2000 you could write a simple Update statement
>> Update Tablename
>> set memo1 = 'discount % ' + memo1
>> This will update all the 20K records in a single shot.
>> Did I answered your question?
>> Thanks
>> Hari
>> SQL Server MVP
>> "Mac" <mac@.hotmail.com> wrote in message
>> news:u9x4e.14059$Fh2.5711@.trnddc04...
>>I have database in SQL2K. I have a memo column called memo1. I want to
>>update memo with "dicount % "+memo1.
>> I want to insert "discount %" in front of memo1.
>> This is going to be just one time shot. There are more 20,000 records
>> need to be updated.
>> I would appreciate if someone can help me.
>> I can run VB program and script so if anyone can help me to create
>> something in VB, if it is not possible in SQL2K,
>> I would really appreciate.
>> Thanks
>>
>

Sunday, March 11, 2012

Database Diagrams : Showing Relations Column to Column

Hi to all,

As I am going to deal with a huge number of database tables, I thought that drawing their diagrams will be the most professional way of keeping track of what I am doing. So In Enterprise Manager using the Wizard I made it to be drawn nicely.

But I have a problem now. I see that the relations defined among the tables are represented correctly but the line connecting the two table is drawn randomly. I mean the starting point of the line doesnt start from the column having the primary key and doesnt end near to the column having the foreign key. Viewing my diagram I want to see the lines to start and end showing the 2 related key columns.

Is there a way to accomplish this like setting an option, clicking somewhere or should I try to drag the lines to manually?

Thanks in advance

The quick way is to generate the create table statements of the tables and you will see the constraints and if you have constraints you may also check if you have DRI(declarative referential integrity). You can have constraints without DRI so check. Hope this helps.

Wednesday, March 7, 2012

Database Design Questions...

quote:

> I am using SQL Server 2000.
> I got two tables.
> Table1:- PriceList
> Column are ProductId(Primary Key) and rest of the column.
> Table2:- PriceListHistory
> Column are same columns as "PriceList" plus one more column to keep track

quote:

> change history.
> I can add one more column to keep track of price change history and make
> that as part of the primary key.
> Questions is what should be the data type of this extra column. Can use

data

quote:

> type as datetime or use interger ? Is it not a
> good idea to use datetime as part of the pimary key.
> Any suggestions ?
>

Database Design Questions...

I am using SQL Server 2000.
I got two tables.
Table1:- PriceList
Column are ProductId(Primary Key) and rest of the column.
Table2:- PriceListHistory
Column are same columns as "PriceList" plus one more column to keep track of
change history.
I can add one more column to keep track of price change history and make
that as part of the primary key.
Questions is what should be the data type of this extra column. Can use data
type as datetime or use interger ? Is it not a
good idea to use datetime as part of the pimary key.
Any suggestions ?I'd go an integer with the identity property to make life very easy. Dates
as part of a PK can be problematic - dates can be reset on machines etc...
Also if 2 recs for the same part come in together they might get the same
time.
"Gary Smith" <GarySmith_77@.yahoo.com> wrote in message
news:OjkYNN81DHA.1760@.TK2MSFTNGP10.phx.gbl...
> I am using SQL Server 2000.
> I got two tables.
> Table1:- PriceList
> Column are ProductId(Primary Key) and rest of the column.
> Table2:- PriceListHistory
> Column are same columns as "PriceList" plus one more column to keep track
of
> change history.
> I can add one more column to keep track of price change history and make
> that as part of the primary key.
> Questions is what should be the data type of this extra column. Can use
data
> type as datetime or use interger ? Is it not a
> good idea to use datetime as part of the pimary key.
> Any suggestions ?
>

Saturday, February 25, 2012

Database design question

I have a database design questions. How can we specify one of two
columns in a table should be not null? In other words, if one column is
null then another column should not be null. Here is an example. Let's
say we have document and we need to specify permissions to the
document. The document has either individual or group level
permissions.
Document table
doc_id int primary,
doc_name varchar(50) not null
User table
user_id varchar(50) primary,
user_name varchar(100) not null
Group table
group_id int primary,
group_name varchar(50) not null
UserGroup table
user_group_id int primary,
group_id int not null,
user_id varchar(50) not null
DocumentPermission table
doc_id int,
group_id int,
user_id varchar(50)
So, either group_id or user_id should not be null in the
DocumentPermission table. Is there anyway we can have constraint like
this on the table? Is creating another group for that user the only
solution (Involves more administration)? Any help would be appreciated.
Thanks,
SreedharNot sure if this could be handled using a constraint or not but you
could create a trigger to ROLLBACK the transaction (and raise an
error), if both the column values are null.
sreedhardasi@.gmail.com wrote:
> I have a database design questions. How can we specify one of two
> columns in a table should be not null? In other words, if one column is
> null then another column should not be null. Here is an example. Let's
> say we have document and we need to specify permissions to the
> document. The document has either individual or group level
> permissions.
> Document table
> doc_id int primary,
> doc_name varchar(50) not null
> User table
> user_id varchar(50) primary,
> user_name varchar(100) not null
> Group table
> group_id int primary,
> group_name varchar(50) not null
> UserGroup table
> user_group_id int primary,
> group_id int not null,
> user_id varchar(50) not null
> DocumentPermission table
> doc_id int,
> group_id int,
> user_id varchar(50)
> So, either group_id or user_id should not be null in the
> DocumentPermission table. Is there anyway we can have constraint like
> this on the table? Is creating another group for that user the only
> solution (Involves more administration)? Any help would be appreciated.
> Thanks,
> Sreedhar|||Hi
If you had a superset of Users and Groups you would not need two columns and
a foreign key constraint could be added. Why are is user_id varchar(50)?
John
"sreedhardasi@.gmail.com" wrote:
> I have a database design questions. How can we specify one of two
> columns in a table should be not null? In other words, if one column is
> null then another column should not be null. Here is an example. Let's
> say we have document and we need to specify permissions to the
> document. The document has either individual or group level
> permissions.
> Document table
> doc_id int primary,
> doc_name varchar(50) not null
> User table
> user_id varchar(50) primary,
> user_name varchar(100) not null
> Group table
> group_id int primary,
> group_name varchar(50) not null
> UserGroup table
> user_group_id int primary,
> group_id int not null,
> user_id varchar(50) not null
> DocumentPermission table
> doc_id int,
> group_id int,
> user_id varchar(50)
> So, either group_id or user_id should not be null in the
> DocumentPermission table. Is there anyway we can have constraint like
> this on the table? Is creating another group for that user the only
> solution (Involves more administration)? Any help would be appreciated.
> Thanks,
> Sreedhar
>|||I guess your suggestion is that I have a superset (assuming both
group_id and user_id are same datatype) and a type column (specifies if
group or individual) in a table and reference this to UserPermission
table. Am I correct? I have user_id varchar(50) because I am storing
windows user names. I guess I just need to add another integer column
to User table and use it as primary. Thanks for your response.
John Bell wrote:
> Hi
> If you had a superset of Users and Groups you would not need two columns and
> a foreign key constraint could be added. Why are is user_id varchar(50)?
> John
> "sreedhardasi@.gmail.com" wrote:
> > I have a database design questions. How can we specify one of two
> > columns in a table should be not null? In other words, if one column is
> > null then another column should not be null. Here is an example. Let's
> > say we have document and we need to specify permissions to the
> > document. The document has either individual or group level
> > permissions.
> >
> > Document table
> > doc_id int primary,
> > doc_name varchar(50) not null
> >
> > User table
> > user_id varchar(50) primary,
> > user_name varchar(100) not null
> >
> > Group table
> > group_id int primary,
> > group_name varchar(50) not null
> >
> > UserGroup table
> > user_group_id int primary,
> > group_id int not null,
> > user_id varchar(50) not null
> >
> > DocumentPermission table
> > doc_id int,
> > group_id int,
> > user_id varchar(50)
> >
> > So, either group_id or user_id should not be null in the
> > DocumentPermission table. Is there anyway we can have constraint like
> > this on the table? Is creating another group for that user the only
> > solution (Involves more administration)? Any help would be appreciated.
> >
> > Thanks,
> > Sreedhar
> >
> >|||Hi
Yes you are correct! It should be alot simpler to use than your current
design and you can add a FK constraint, which you would only be able to do
with a CHECK constraint and function in the current design (which would be
slow!). If necessary you could create views that correspond to the current
users and groups tables.
John
"sreedhardasi@.gmail.com" wrote:
> I guess your suggestion is that I have a superset (assuming both
> group_id and user_id are same datatype) and a type column (specifies if
> group or individual) in a table and reference this to UserPermission
> table. Am I correct? I have user_id varchar(50) because I am storing
> windows user names. I guess I just need to add another integer column
> to User table and use it as primary. Thanks for your response.
> John Bell wrote:
> > Hi
> >
> > If you had a superset of Users and Groups you would not need two columns and
> > a foreign key constraint could be added. Why are is user_id varchar(50)?
> >
> > John
> >
> > "sreedhardasi@.gmail.com" wrote:
> >
> > > I have a database design questions. How can we specify one of two
> > > columns in a table should be not null? In other words, if one column is
> > > null then another column should not be null. Here is an example. Let's
> > > say we have document and we need to specify permissions to the
> > > document. The document has either individual or group level
> > > permissions.
> > >
> > > Document table
> > > doc_id int primary,
> > > doc_name varchar(50) not null
> > >
> > > User table
> > > user_id varchar(50) primary,
> > > user_name varchar(100) not null
> > >
> > > Group table
> > > group_id int primary,
> > > group_name varchar(50) not null
> > >
> > > UserGroup table
> > > user_group_id int primary,
> > > group_id int not null,
> > > user_id varchar(50) not null
> > >
> > > DocumentPermission table
> > > doc_id int,
> > > group_id int,
> > > user_id varchar(50)
> > >
> > > So, either group_id or user_id should not be null in the
> > > DocumentPermission table. Is there anyway we can have constraint like
> > > this on the table? Is creating another group for that user the only
> > > solution (Involves more administration)? Any help would be appreciated.
> > >
> > > Thanks,
> > > Sreedhar
> > >
> > >
>

Thursday, March 29, 2012

database in SQL2K need help in upading/adding 2 memo columns

database in SQL2K need help in upading/adding 2 memo columns

database in SQL2K need help in upading/adding 2 memo columns

Sunday, March 11, 2012

Database Diagrams : Showing Relations Column to Column

Wednesday, March 7, 2012

Database Design Questions...

Database Design Questions...

Saturday, February 25, 2012

Database design question

Database level lock

Blog Archive

About Me