|
But in Alter table i have to specify the Name of the table one by one i want that it will take table name automatically from some cursor or some other way
in yours case if have to write this query more then 50 times
like
alter table SalesMaster
alter table PurchaseMaster
alter table ItemMaster
alter table ItemDetail
i want it will check the field automatically in those table and will update the field length
Thanks
Best Of Regards,
Mirza Rahman
|
|
|
|
|
Use your select from systobject to identify the tables, you know the column name and create dynamic SQL to execute the alter table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Based on some business objects, I need a database model to store generic property values.
The range of possible value types is known (no binary types).
What I already have:
CREATE TABLE ObjectOne
(
Id int, -- primary key
... -- more fields
)
CREATE TABLE ObjectOneProperties
(
ObjectOneId int, -- primary key, foreign key to the object one
ObjectPropertiesId int, -- primary key, foreign key to the properties
)
CREATE TABLE ObjectTwo
(
Id int, -- primary key
... -- more fields
)
CREATE TABLE ObjectTwoProperties
(
ObjectTwoId int, -- primary key, foreign key to the object two
ObjectPropertiesId int, -- primary key, foreign key to the properties
)
CREATE TABLE ObjectProperties
(
Id int, -- primary key
PropertyType varchar(255) -- property business type
??? -- property value ? foreign key(s) ?
)
Until now, I have found two possible approaches:
Store value in one table
CREATE TABLE PropertyValues
(
Id int, -- primary key
ValueInt int, -- value as int
ValueText varchar(x), -- value as text
ValuedateTime datetime, -- value as datetime
... -- all other value types
) Store value in multiple tables
CREATE TABLE PropertyIntValues
(
Id int, -- primary key
Value int -- value as int
)
CREATE TABLE PropertyTextValues
(
Id int, -- primary key
Value varchar(x) -- value as text
)
CREATE TABLE PropertyDateTimeValues
(
Id int, -- primary key
Value datetime -- value as datetime
)
CREATE TABLE PropertyXxxxValues -- for any other value types
(
Id int, -- primary key
Value xxx -- value as xxx
) I have the following selection criteria:
- High performance is required
- Database neutral (if possible)
- Stored Procedures allowed
- No Triggers
- Storage amount is not critical
Any help, suggestion or alternative solution is greatly appreciated.
|
|
|
|
|
Hmm, why not use XML as datatype for properties and store them in the same table as the main entity?
|
|
|
|
|
Hi Mika,
I never used a XML field in a table. How is the performance of such a XML field?
Is it possible to make indexes or type specific queries (for example values within a date range)?
I have found further information on this topic Entity-attribute-value model[^].
|
|
|
|
|
Jani Giannoudis wrote: How is the performance of such a XML field
This depends on what database you use. For example SQL Server 2008 has: CREATE XML INDEX[^] on the other hand SQL Server 2005 has a bit different approach: Indexes on xml Data Type Columns[^]
But in both cases I think the performance is quite good.
Jani Giannoudis wrote: Is it possible to make indexes or type specific queries
Both would be possible. You could for example store the type in schema or in the xml itself.
The link you found also seems quite good.
|
|
|
|
|
Hi..
I have one table with columns
Id int, Message varchar, QueId int
I store messages in this table. Id is auto generated and that will be the id of message. Message will be message and queid will be the Id of the message for which your reply message is posted.
So I am storing messages as well as their replies in the same table.
The message which is not the answer of any question will have value 0 in the queid field.
One message can have multiple reply messages and these replies again can have multiple replies -- just like discussion forum / blog.
So one message can have hierarchy of messages...
Now my question is that.. I want to delete a message , now if i delete a message then this message's whole hierarchy should be deleted...
I can delete the message and the messages which are replies of this message, but what about the messages which are the replies of these deleted messages ?... i.e I also want to delete the same...
Could you tell me the solution...or how can i create stored procedure which can work recursively , for deleting records...
Awaiting for reply
Thanks
Hemant Thaker
By:
Hemant Thaker
|
|
|
|
|
Am not Sure which is the wise way to do that , i think Colin and the codeProject stuff might help you because their forums is like that. but i have seen that here at codeProject, when a user Deletes a Message, the submessages still remains , you want to get rid of that. I think when you store messages, if you say Queid holds the Parents reference, then you cna use your Primary key and the queid to delete the Message with its submessages in sql you cna do something like this
Your SQL must do two things
Delete the Main Message using the "ID"
and
Delete the Child messages based on the Parent ID and the Parents Queid
The way you have design your table is confusing, this should have been separated into tables , your table is not normalized , you will always have problems working on the table, some info needs to be separated.
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
does anybody know the best way to store case-sensitive passwords in sql server? in mysql its easy to use the varbinary type and i'm wondering if that holds for sql server
thnx
"mostly watching the human race is like watching dogs watch tv ... they see the pictures move but the meaning escapes them"
|
|
|
|
|
I only store password hashes in the db. I use the binary type but image should work as well.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego.
|
|
|
|
|
thnx for that ... i thought it might be the way to go but when i tried that a few years ago i had a terrible problem with the client md5 code not producing the same result as the server md5 code so nobody could log in ... if i md5 the password on the server when they register will it match the client hash when they try to log in?
using c# & sql server
"mostly watching the human race is like watching dogs watch tv ... they see the pictures move but the meaning escapes them"
|
|
|
|
|
MD5 will always have the same results, as will any algorithm. Usually them not matching occur from improper use or poor implementation. Every time my MD5 has not matched it turned out to be programmer error on my part.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego.
|
|
|
|
|
ok cool
thnx
"mostly watching the human race is like watching dogs watch tv ... they see the pictures move but the meaning escapes them"
|
|
|
|
|
Anyone to modify this code to work please
select* from employee where salary = MAX(salary) ; I want to get all details from the employee with highest salary.
|
|
|
|
|
select * from employee where salary=(select max(salary) from employee)
hope this helps
|
|
|
|
|
Thanks. I have a dream to be like you
|
|
|
|
|
better:
select top 1 * from employees order by salary desc
"mostly watching the human race is like watching dogs watch tv ... they see the pictures move but the meaning escapes them"
|
|
|
|
|
i am inserting a data remote database from client macine but problem is connection is not established between clint and remote
|
|
|
|
|
|
Hello,
Which provider should I use for transferring data from as400? I cannot use Microsoft provider as I am using standard edition. As for IBM providers there are three ones for native ole db:
IBM db2 udb for iseries ibmda400 ole db provider
IBM db2 udb for iseries ibmdasql ole db provider
IBM db2 udb for iseries ibmdarla ole db provider
and there are the same three for Ado.Net
Apart from ole db I can use ODBC and use the DNS I have created in data sources.
Which one will be the fastest? I believe using native will be better but which one from the three possibilities?
Also, are there any gotchas to take into consideration? Any tips and tricks?
Thank you.
modified on Saturday, February 7, 2009 6:24 AM
|
|
|
|
|
Have you checked this: http://www.itjungle.com/fhg/fhg050306-story02.html[^]
"IBM offers the OS/400 community three OLE DB providers: IBMDA400, IBMDASQL, and IBMDARLA. As a review, IBMDA400 is a general purpose, all encompassing provider that supports SQL, command and data queue interfaces. IBMDARLA is the record level access provider and is primarily for applications that work with single records instead of sets of records. The IBMDASQL provider was released with iSeries Access V5R3 and is an SQL only provider."
|
|
|
|
|
Thank you Mika. I will only need to issue select statements so I guess IBMDASQL will be most efficient.
|
|
|
|
|
Giorgi Dalakishvili wrote: I will only need to issue select statements so I guess IBMDASQL will be most efficient
Yes, I also think that would be a good choice when using selects only.
Giorgi Dalakishvili wrote: Thank you Mika
You're welcome
|
|
|
|
|
hi all,
i want to execute following on command propmpt
osql -S test\instance -U username -P password -Q"BACKUP DATABASE databaseName TO DISK='C:\My.bak'"
this command require osql tool(client tool) must be installed on client machine. I do not want to install sql server. Sql server is install on test machine .I want to install only minimum tools to use Osql.exe
so i want to ask that which tool must be installed on client machine to execute above command
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
