|
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
|
|
|
|
|
Looks like you got this one downvoted for posting in the wrong forum!
Use the best guess
|
|
|
|
|
hi to all
ho can i get top 1 from my table.
i have a event log table that 3 event inserted in it i want to get top 1 each event by desc in it .
my code is but this code is wrong and correct code probably similar to this code
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs
WHERE ( [Guid] = ( SELECT TOP ( 1 )
[Guid]
FROM EventLogs AS elog
WHERE ( ReferenceGuid = EventLogs.ReferenceGuid )
ORDER BY Time DESC
) )
thanks for any help
|
|
|
|
|
How about something like this:
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs AS elog
INNER JOIN (
SELECT [Guid]
FROM EventLogs elog1
INNER JOIN (
SELECT [Event], MAX([Time]) AS MTime
FROM EventLogs
GROUP BY [Event] ) AS t ON elog1.[Event] = t.[Event] AND elog1.[Time] = t.MTime
) AS elog3 ON elog.[Guid] = elog3.[Guid]
|
|
|
|
|
thanks for your answer
this problem has been fix with some few change like this:
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs
WHERE ( [Guid] = ( SELECT TOP ( 1 )
[Guid]
FROM EventLogs AS elog
WHERE ( ReferenceGuid = EventLogs.ReferenceGuid AND Event=EventLogs.Event)
ORDER BY Time DESC
) )
|
|
|
|
|
Or you can use row_number, can't remember the syntax but there are good examples in BOL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
With SQL Server 2005, we can use Row_Number() function and Partition By clause to number rows in categories
So you can partition your data in Event category and filter only the rows with row number is 1
So you will get only 1 row per category
with cte as (
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress,
rn = ROW_NUMBER() OVER (Partition By Event Order By Time Desc)
FROM EventLogs
)
select * from cte where rn = 1
Please check the following URL for similar sample code http://www.kodyaz.com/articles/top-n-random-rows-foreach-category-or-group.aspx[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
