|
I think I was working with MSSQL 6.5!
Try creating a view of only 1 table, no joins, and inserting/editing a record. Now add an inner join and repeat.
Even though I have SSMS open I'll not bother as it is a complete WOTAM.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You can insert into views if there is 1 table in the view (how dumb is that)
It's quite useful if you want to filter the contents of a table for a certain group of users.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Jörgen Andersson wrote: It's quite useful if you want to filter the contents of a table for a certain group of users.
Why would you do that in the database versus the application layer?
And of course in general when you say 'user' if you mean an actual database user then there can be negative impacts such as licensing.
|
|
|
|
|
Because some companies are quite divided.
The right place to do it is in the application, but I've been working for companies where the database department were responsible for that one department couldn't see the same columns as another department.
It's a lot easier to keep track on this with a view than with column permissions.
When it comes to licensing it doesn't matter. It's the number of actual users that counts, not the number of users you setup in the database. It's a common misconception.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Jörgen Andersson wrote: Because some companies are quite divided.
Certainly. And some companies need to create their own database.
But in the general case neither of those are true.
Jörgen Andersson wrote: It's the number of actual users that counts, not the number of users you setup in the database. It's a common misconception.
I know how it works.
The fact that user licensing exists at all specifically indicates that support in the database per user is something that is in fact significant.
Unless you are claiming that only a single user connects at a time, then my comment about user licensing stands. And if you are claiming that then it is far from what any normal business would use.
|
|
|
|
|
Jörgen Andersson wrote: f you want to filter the contents of a table for a certain group of users
Sure but that is one of the uses of a view - a very old fashioned use, but still valid. Who puts authorisation logic at that level these days. Apps no longer have to rely on the database for authorisation, very 80s 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I know, see my answer to jschell.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Yeah I read that and thought poor bastard, I just hope you are not working with one of those old EIS systems as well!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm at a different place now, with a different set of pains.
Mycroft Holmes wrote: I just hope you are not working with one of those old EIS systems as well!
Depends on what EIS stands for. If it is Executive Information System, then yes kind of.
It's my own design though. It's the users that's the main pain now.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Hi Srs.,
I am searching in a lot of sites a way to justify my textboxes on the sql server reports, but I don't found yet.
In a site they suggest I to use html/css fields, but the SSRS don't recognize the tag "justify".
Someone here have some kind of workaround for my issue?
Thanks for your attention!
|
|
|
|
|
|
The db I am testing is locked ATM doing some data-loads so I can't get various potential methods of doing this so I figured I would ask. I have a database with each table having its own file group and each file group has one file. Is there a simple method (other than Import Data and Bulk insert type operations) to detach the file groups (ok, maybe not detach they will be read-only) and copy the table, file group, and file into another database on a completely different server.
My thought would be to just copy the physical files and then through some magical T-SQL have the files appear as the new table. I believe in My-SQL copying db files is all that is required but SQL Server is a special lady. Any thoughts? I am only dealing with about 60 million records an hour so not that much data.
|
|
|
|
|
Ennis Ray Lynch, Jr. wrote: Is there a simple method (other than Import Data and Bulk insert type operations) to detach the file groups (ok, maybe not detach they will be read-only) and copy the table, file group, and file into another database on a completely different server.
Yes. Detach, xcopy, attach.
sp_attach_db [ @dbname= ] 'dbname'
, [ @filename1= ] 'filename_n' [ ,...16 ] (From MSDN[^])
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks for your reply, unfortunately, your method requires that the other database have the tables and files and file groups already created and requires the DB to be taken offline in order to add the new files and file groups which contain the table data.
What I am looking for is some black magic to take files from a separate database and bring then into a different database without bringing it offline. Assuming both databases have the exact same configuration, this is technically possible (otherwise you couldn't add new files and file groups while SQL Server is running) but it is likely a dark-art. Bringing the database offline is not a valid solution for me needs.
|
|
|
|
|
Ennis Ray Lynch, Jr. wrote: your method requires that the other database have the tables and files and file groups already created and requires the DB to be taken offline in order to add the new files and file groups which contain the table data.
Not necessarily so; you can attach it under a different name to the same server. Next, you SELECT INTO the tables from Database1.Dbo.TableName to Database2.Dbo.TableName 
Ennis Ray Lynch, Jr. wrote: What I am looking for is some black magic
Aight, sacrifice a chicken and a donkey at the next full moon. More seriously; if you have access to a DBA, ask him/her how they'd implement horizontal partitioning. You could do something similar on a more simpeler level; you'd have to change your select-query to dynamically check for new databases, and to union it along. Could be done by querying sys.databases and building the select/union from that.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello,
I'm using VS2010 to create Data Base in SQL server.
I want to create column that will be of type enum.
For example the enum:
public enum Equipment
{
Tank,
Suit,
Fins,
Mask,
Snorkel
}
And the column will be called DiveKit, the values in that column can be one of the Equipment enumeration members.
Thanks
|
|
|
|
|
Sql Server doesn't have an Enum datatype. You can store it's value-representation (int) or it's string-representation (varchar). It wouldn't do range-checking by default.
Simple example below on having range-checking by creating a table for the enum and referencing it;
CREATE TABLE enumEquipment([Label] VARCHAR(10) PRIMARY KEY)
INSERT INTO enumEquipment (Label) VALUES ('Tank'), ('Suit'), ('Fins'), ('Mask'), ('Snorkel');
CREATE TABLE someTableUsingTheEnum
(
Id INT IDENTITY(1,1) PRIMARY KEY,
EnumValue VARCHAR(10) REFERENCES enumEquipment (Label)
)
INSERT INTO someTableUsingTheEnum (EnumValue) VALUES ('Tank')
INSERT INTO someTableUsingTheEnum (EnumValue) VALUES ('Micky')
SELECT * FROM someTableUsingTheEnum
DROP TABLE someTableUsingTheEnum
DROP TABLE enumEquipmentIt'd be better, performance-wise, to use the integer-value of the enum. Just cast it to an int, and store it in an int in Sql Server.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Perhaps my GenOmatic[^] article will provide some insight.
|
|
|
|
|
You can use a check constraint on the table if you just want the column limited to a list of values.
ALTER TABLE EQIPMENT
ADD CONSTRAINT EQUIPMENTTYPE_CHK CHECK (DIVEKIT IN ('Tank', 'Suit', 'Fins', 'Mask', 'Snorkel')
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
I'd be concerned about the need to easily add new members. And agnosticism.
Plus, having a table allows the addition of more information related to the items.
|
|
|
|
|
All true, and if one wanted to fully emulate an enum one would also need an extra column for the integer value.
But you can't add new members to an enumeration without recompiling either.
Anyway, I got the impression that the OP only wanted to limit the items in a column, and if your objections is of no concern for the OPs intended usage, this is a simple way to achieve a limited list of items.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Jörgen Andersson wrote: the OP only wanted to limit the items in a column
Which is likely true, but I hope to open his eyes to a broader solution that he may not have considered yet.
Reducing record size may not be as important with today's databases and storage availability, but it's still better to store a foreign key rather than a string, particularly if you may need to select records with given values.
Having a table with the keys and values also allows for easier loading of a list from which the user can select items.
|
|
|
|
|
Quite right.
I'm usually pushing normalization myself.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
|
One looks like you may have answered an already solutioned question another I could see no reason why and another someone explained why the down vote.
Down votes come with the territory, some will simply vote b/c you did not supply the codz (although you seem to be happy to do so!).
You may also have picked up a univoter, they are also part of the territory, relax, they get bored really easily and move on if you leave them. Bitching validates them and they feed off that.
If you have a real problem with it you can try the bugs and sugs[^] forum
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
