|
Hi Experts
i am using sql server 2005. i Have a table with Five Field structure Give Below
Col Name Data type
Code int
Name varchar
Address varchar
Fee Decimal
AddDate DateTime
Now Suppose i change the Structure of the table
like
Col Name Data type
Code int
Name varchar
Fee Decimal
AddDate DateTime
Address varchar
and Save It
now i want to Compare Stucture of Last modified and Current Table
and know the alter position of the column name
Dinesh Sharma
|
|
|
|
|
Take a look at the system views, they have all this information. Columns have an ordinal position!
If you are looking at synchronising the databases then look at the Red-Gate tools
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm looking at an embedded database requirement, basically users need to operate unconnected so I was intending to supply SQL Compact but it seems it does not support stored procs. There goes 99% of my DB code.
Is there a single user SQL database that supports procedures (forget Access I get hives just thinking about it). I would like to have a single codebase for both the main database and the local DB and I really didn't want to install a server version.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would recommend Firebird. It has embedded and server versions, is fully featured, is pretty much ANSI-compliant and it's free!
|
|
|
|
|
Okay I made 4 tables
books, Author, bookauthor(this is the linktable), booknumber
well I wanted to take and make the books.ID indentity field set to yes but NOO NO NO sql server will not let me me. says something to do with the schema-bound dependencies. So in the diagram view I removed the links from the tables which i thought removed the relationships. Well tried again same exact error.
SO, I go alright I will take and delete the table and start over, that didn't work either same error of schema-bound dependencies. HOW THE HECK DO YOU REMOVE THE RELATIONSHIPS?
So, now when i right click on books it gives me the error that is marked for deletion. How do I unmark it or delete it or whatever? I mean is it really this complicated ? 
|
|
|
|
|
tonyonlinux wrote: I mean is it really this complicated
It sure can be, your dealing with a database after all, not Access.
Are you using management studio?
Expand the table,inspect keys and constraints and deal with the ones you need to remove. You may have a setting that will not allow you to make table dropping changes set also. Although setting a identity column should not affect this.
Make sure the ID column is int or bigint, this data change may screw you up as well.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes, I'm using the management tool provided with the express edition. In any event I got the table to drop after reading your message about looking at the constraints I just didn't know where they were listed. I see now thanks. do you recommend any free books that are really good to learn this stuff? Like library books or online stuff? Or even something not expensive so I can learn this? I bought the t-sql videos from trainingspot and i'm also a lifetime member of learnvisual.net and they help but I'm finding I get a lot of good answers and almost instant support from here.
thank you and the rest that have helped me sooooooo!!!!!! much !!!!!!!!!
|
|
|
|
|
tonyonlinux wrote: do you recommend any free books that are really good to learn this stuff
Sorry my training days are so out of date the material is probably not in print any longer 
SQL Server Central is a good resource for learning, completely sql server centric whereas CP has a wider audience. I find CP more useful for support for that reason, wider experience/knowledge
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have thousands of tables, and the fields are always ordered by date,
and i want to select bottom 60 from all the tables.
I know I can realize this function by following statement:
select top 60 from table order by date desc
but I don't want to use "order by", all my data is sorted and it will waste my time.
so can I get the bottom 60 record from the db?
Is there any function like "select bottom 60"?
|
|
|
|
|
Yes, SELECT TOP 60 FROM Table ORDER BY date DESC. If it is good enough for every database professional on every database platform, then I would suggest that it isn't as bad as seem to think! If I understand what you mean by 'sorted' then I suggest that you find an article/book about how databases store data; I think that you will be very surprised.
|
|
|
|
|
caiguosen wrote: but I don't want to use "order by", all my data is sorted and it will waste my time.
Then get a different job and don't waste our time.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you are already ordering the fields and know you need records from end, why not store them the opposite way as current. I mean order records from current to older dates. In that case all you will need is to get top xx records from the table.
This should have been thought while designing database IMHO.
"No matter how many fish in the sea; it will be so empty without me." - From song "Without me" by Eminem
|
|
|
|
|
I am using VB.net and I have a SQL database table GuestInfo with 1 column Col1 defined as Text with allow NULL & another column Col2 defined as Int with allow NULL
If I use command:
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col2 = 12345"
Dim lrd As SqlDataReader = cmd.ExecuteReader()
- There is no error happen
But if I use:
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = 'ABC'"
Dim lrd As SqlDataReader = cmd.ExecuteReader()
- There is an error pop-up said that text and varchar are incompatible operator
Then I tried the following:
Dim str As String
str = "ABC"
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = '" & str & "'"
Dim lrd As SqlDataReader = cmd.ExecuteReader()
- There is a same error display
What can I do to search a Text value in column 1?
|
|
|
|
|
I rarely use TEXT data type so have not run across this problem but you might try
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = Convert(text,'ABC')"
It is probable that the command object passes the string data type as varchar, as it is the default I think.
Do some research on stored procedures and parameters, that will solve all these problems and move your sql development and testing to Management Studio which will speed up your delivery time.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I did try as you suggested but now it is weird, the error now shows: Connect to SQL Server, data types text and text are incompatible in the equal to operator
- Might be in SQL database, I shouldn't defined Col1 as Text? If YES, what I should define to with Dim str as String in VB.net (str = "ABC"):
. varchar(50)
. varchar(MAX)
. char(10)
. nchar(10)
. ntext
Any suggestion? Meanwhile, I will also search for more solutions 
|
|
|
|
|
The default of nvarchar is for unicode and should not be used unless you intend to store non english characters. I would recommend using varchar(##), the length should be slightly larger than the max characters you expect. varchar can be used for up to 8000 characters, this generally meets most requirements. varchar(max) I would only use if you need to store a serious amount of text in a field, ie. a book or at least a story.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I re-define Col1 to varchar(50) then use:
cmd3.CommandText = "SELECT * FROM GuestInfo WHERE Trial = 'ABC' "
Dim lrd As SqlDataReader = cmd3.ExecuteReader()
Working as a charm!
Thanks 
|
|
|
|
|
Dear Friends,
i have one issue in using nested procedure, both procedure having hash table with same name because of which there is an issue for executing same procedure through ASP.net (it not showing issue in sql server)
so i need how to find nested procedure from huge database
Sasmi
|
|
|
|
|
The text of a procedure is stored in a system table in SQL server, from this you can do a string match to find the offending text and identify the procedures using it.
This search[^] throws up a number of possibilities.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi everybody
i have two tables that have Many-to-many relationship (personality-to-(Personality_PersonalitySubtypes)-PersonalitySubtypes)
in normal scenarioes , u would select all personalitySubtypes by knowing only the personalityID by inner join the three tables
but suppose that i need the reverse case , i need to select the personalityID by knowing the four combinations of personalitysubtypes knowing that i need it to be completely accurate means if one of the four combination changes the whole personalityID may be changed . how could i do that in sql ?
i tried data structures in C# to iterate and blah blah blah till my brain is down
i don't know what to do
Human knowledge belongs to the world.
|
|
|
|
|
You can use the same data/query structure but instead of filtering on where personalityid = 2 you can use where personalitysubtype in (14,1,78).
Have a look in WHERE IN in BOL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi i am working on vb.net 2008 with mysql.my application will run on lan i want to build a form where user can view all available servers on lan and connect to one he finds suitable.
earlier in sql server i used sqldmo for the same but in mysql i found no such alternatives.???
can smone help???
one more thing i would like to add that is my mysql is version 4.x ) 
|
|
|
|
|
i tried same question on main mysql forum and here also.but got no answer so i take it that it is not possible 
i shldnt hv changed from sql express edition to this  (
|
|
|
|
|
Vishal Saxena dev wrote: so i take it that it is not possible
A reasonable assumption. Why did you change from SQL Server?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
my boss told me to now i hv to hard code it
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
