|
Jörgen Andersson wrote: I've seen that done, minus the timezone. That was a freaking moment.
I imagine! I wanted a far-fetched example, so encountering something like that in the wild is a bit of a wtf-moment.
Codd did not say 'eliminate every repeating group', as that would imply that we cannot even store the day number '12'. It would repeat for each month, after all. Putting the day-numbers in a separate table and linking to them using a Guid would be an option, but also a bit insane.
The 'worst' modeling decision that I remember are storing everything as a varchar, including an array of bits; actually stored as a "11101010001100"-string. When I asked who wrote it (same tone as usual), my boss-for-the-moment responded saying it was an optimization 
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Bit array as varchar scores quite high on the list of wtf moments
The really stupid part of storing time in six different fields is that they are all just different representations of the same thing (time) and you can calculate one from another.
If there wasn't a date type available I'd simply store seconds in a number field, which coincidentally is what happens behind the doors in a database.
|
|
|
|
|
Jörgen Andersson wrote: If there wasn't a date type available I'd simply store seconds in a number field, which coincidentally is what happens behind the doors in a database.
The beauty of having it in seconds is that it's once again a single (atomic) fact, without any cultural formatting or localized representation 
It's hard to keep things 'simple'.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Since this is an existing application presumably with multiple international clients presumably you will already be familar with dealing with issues about how the data will be used and how it is viewed. That will impact some of the design.
I would expect that you would need to use a template which defines addressing schemes. That would be a dynamic (loaded from somewhere) part of the application to define layout for display and printing.
The following is interesting read in terms of possible variations.
http://www.columbia.edu/~fdc/postal/[^]
|
|
|
|
|
Thank you all for your time and information!
As I'm a programmer in Heart and Soul I was hoping something upon a solution similar to object inheritance where I can modify a base table into a new table , but maybe I'm way far of the planet earth.
I'm considering all your answers and try to figure out a neat solution. At this moment I'm thinking about a common table (where data resides that belongs to all variants) and a "varianttype-key-value-type" (dutchAddress-roomnumber-34-int) table in which I store differences.
|
|
|
|
|
LiQuick wrote: As I'm a programmer in Heart and Soul I was hoping something upon a solution similar to object inheritance where I can modify a base table into a new table , but maybe I'm way far of the planet earth.
I do not see OO as an information-modeling language. Every db will support foreigen keys, and you could copy the concept of inheritance;
HUMAN
-----
Id
Name
Gender
DoB
EMPLOYEE
--------
Id
HumanFk (FK to HUMAN.Id)
HiredOn
HasAccessToSuperSecretFiles
..but in a relational world, that will make things worse. You'd get *VERY* complex queries, and most UI's would not be prepared to handle an answer that comes in various forms.
If it's handled as a single entity and used as one, then store it as one.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I'd say that it depends.
It's often a balance between complex queries vs. performance. You might end up with extremely wide tables with a lot of null values where you will need many indexes instead of having one large index in the "HUMAN" table and several small optimized indexes for the "Inherited" tables.
So it is a decision that can't be generalized, but rather one you need to make depending on the situation.
|
|
|
|
|
Jörgen Andersson wrote: I'd say that it depends.
It spells trouble, because the software that will use it will have to be prepared for a lot of scenario's. It would require "communication" and "human interaction". Two very obvious failure-points.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Well, I've had to clean out faulty data to many times because the software wasn't, and the communication didn't happen. So the human interaction entered data that shouldn't have been.
Clean unduplicated data is the main reason to normalize, performance is a positive sideeffect. But you lose time during development, that's for sure.
|
|
|
|
|
Jörgen Andersson wrote: But you lose time during development, that's for sure.
It's one of the more important and underestimated parts of development. It's the reason why Microsoft Access does not solve 'everything'.
How much would an inconsistency in the data cost? 
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I just not wanting to recreate the wheel here.
I need to do the following:
1.Get two parts of the filename to use wihtin the sql. The filename look like this some name here.123456.1.txt
I need the 1 and the 123456 to populate two columns within the database table along with the data from the text file.
the text file is fixed width 1-9 is the first column 10-18 the second column etc.
I have around five hundred of these txt files I will need to loop through an process.
has anyone done anything like this is a stored proc not using ssis?
many thanks
|
|
|
|
|
Open up a console-window ([Windows Key]-[R], type cmd, press enter) and execute below command;
dir "C:\Windows\m*.log" /b >files.txt
Replace my Windows location (and the wildcard) with one that's more appropriate for you. Next step is easy, you copy and paste it into MS Access. You now have a table with filenames. You could loop those in Access, and fetch the correct file and save it with some VBA-code.
Then make a linked server (in Sql Server) to the Access database (one for generic imports like these) and simply copy the (appropriate) records over.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thank you for your reply.
I need to be able to do all of thee above wihtin a stored proc without depending on any other app or tool. any thoughts? again thank you for your reply.
|
|
|
|
|
None; yes, it could be done using a sproc. Yes, you can list directories, manipulate strings.
How much of this sproc have you written yet?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
.
modified 10-Oct-12 20:30pm.
|
|
|
|
|
What 'architect' came up with that beauty?
Can a MySql sproc call another stored procedure? If yes, then I'd suggest splitting the else-if's and the query's for readability, lest this will become a maintenance-horror very quick. I'd also recommend strongly to use the stored-procedure-name to identify the query that your architect needs to execute and not a friggin' varchar-parameter.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Please don't respond to my questions anymore.i didn't post a question for some punk kid to use profane language.Also if you have no idea what the requirements are why would you reply with such a stupid reply. Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.
|
|
|
|
|
It's not for you to decide who should and shouldn't answer your question: anything put here is fair game and your question is ridiculous: no one has the time to wade through all of that: You need to reduce the code to more manageable chunks.
SQL Ed wrote: Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.
Change the rules.
BTW; I don't see any 'profane' language in his response. You should also recognize that this is an international forum and some cultures swear far more as part of everyday language. Don't down vote just because you don't like what he says. I up-voted him to counter you and because his response was reasonable.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
It was a proper answer with good suggestions considering the info available. That we don't know the requirements should have been obvious to you as you haven't told us.
The suggestion that you need to modularize the code still stands though, but as you're limited to using only one sproc makes everything quite hard (what kind of requirement is that by the way?)
It seems though as you all the time use the same query with different filtering, so one way is to use a view for the main query, performance will suffer though.
|
|
|
|
|
SQL Ed wrote: Please don't respond to my questions anymore
This is an International forum and anyone can reply to any posts. If you don't like it, just ignore it.
SQL Ed wrote: punk kid
Nope, Eddy is a respected member here and his answers are always useful and helpful. Just see his reputation points.
SQL Ed wrote: profane language
As far as I can see, there's none.
SQL Ed wrote: if you have no idea what the requirements
Might be true, but you didn't tell us either.
SQL Ed wrote: Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.
That's not a technical constraint. A stored procedure should be able to call another many levels deeper and your 3rd party application need not even be aware of it, it just needs to know the name of the "outermost" stored procedure.
|
|
|
|
|
SQL Ed wrote: Please don't respond to my questions anymore
I could promise now, but would probably forget your name in a week.
SQL Ed wrote: i didn't post a question for some punk kid to use profane language.
The "friggin" was superfluous. It was partly an emotional response, something that simply seems to happen whenever I meet a 'solution' without a problem. As is, the requirement does not add value, on the contrary, it makes things more complex and error-prone.
SQL Ed wrote: Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.
As hard as it may sound, but IMO that's where the error is. Unless there's a very good justification (besides the remark that it's a requirement), I'd still suggest fixing the problem at it's root.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
i need a script by which i can get client IP address from sql server database when people do DML operation on table. so i was looking for solution and i found a script which does the things fine from sql server 2005. here is the script
CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
DECLARE @IP_Address varchar(255);
SELECT @IP_Address = client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;
Return @IP_Address;
END
SELECT dbo.GetCurrentIP()
but the above script do not work in sql server 2000. so i need a way to get client ip from sql server 2000. please guide. thanks
tbhattacharjee
|
|
|
|
|
Have you read this. Second answer in that thread tells you the options are very limited for IP retrieving in Sql Server 2000
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
The following code keeps erroring out in MySQL Workbench. I have not been able to locate the syntax problem. Can anyone see whats wrong with this code. Runnining the code a section at a time still dosn't work. It errors at the first ; where I placed the MAD face. Cannot completely remove the errors they just keep changing places. Also is there a good library of code snippets to view if needed?
/*
PATCH MANAGEMENT
*/
DELIMITER $$
#MachineType means Server or Workstation as determined by the word server in the os field
DROP PROCEDURE IF EXISTS usp_cbi_PatchManagement$$
CREATE PROCEDURE usp_cbi_PatchManagement(MasterGrp varchar(30),Criteria varchar(30),LocationID varchar(30),
ClientName varchar(30))
BEGIN
SELECT IFNULL(COUNT(DISTINCT A.computerid),0) AS 'Dynamic Machine Counts'
FROM (SELECT HF.computerid, HF.hotfixID
FROM hotfixdata HFD
INNER JOIN hotfix HF
ON HF.hotfixid = HFD.hotfixid
AND HFD.ignore <> 1
LEFT OUTER JOIN Computers AS C
ON C.ComputerID=HF.ComputerID
LEFT OUTER JOIN locations AS L
ON C.LocationID=L.LocationID
LEFT OUTER JOIN Clients AS CL
ON C.ClientID=CL.ClientID
JOIN agentcomputers AS AC
ON C.ComputerID=AC.computerid
JOIN mastergroups AS MG
ON AC.groupid=MG.groupId
#Enter Case When here seperating different where clauses.
WHERE
CASE
WHEN
Criteria = 'Workstations Missing Patches'
THEN
INSTR(C.os,"server")=0
AND INSTR(C.os,"microsoft")>0
AND HF.installed <> 1
AND HF.Approved = 1
AND LocationID = L.Name
AND ClientName = Cl.Name
 AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Workstations with Empty Patch Tabs'
THEN
HF.hotfixid IS NULL
AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY))
AND INSTR(C.OS,"server")=0
AND INSTR(C.OS,"microsoft")>0
AND LocationID = L.Name
AND ClientName = Cl.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Workstations Pending Reboots'
THEN
(C.Flags & 1024) = 1024
AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY))
AND INSTR(C.OS,"server")=0
AND INSTR(C.OS,"microsoft")>0
AND LocationID = L.Name
AND ClientName = C.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Total Patch Workstations'
THEN
INSTR(C.os,"server")=0
AND INSTR(C.os,"microsoft")>0
AND LocationID = L.Name
AND ClientName = CL.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Servers Missing Patches'
THEN
INSTR(C.os,"server")>0
AND INSTR(C.os,"microsoft")>0
AND HF.installed <> 1
AND HF.Approved = 1
AND LocationID = L.Name
AND ClientName = Cl.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Servers Pending Reboots'
THEN
(C.Flags & 1024) = 1024
AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY))
AND INSTR(C.OS,"server")>0
AND INSTR(C.OS,"microsoft")>0
AND LocationID = L.Name
AND ClientName = C.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Total Patch Servers'
THEN
INSTR(C.os,"server")>0
AND INSTR(C.os,"microsoft")>0
AND LocationID = L.Name
AND ClientName = CL.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Total Patch Machines'
THEN
INSTR(C.os,"microsoft")>0
AND LocationID = 'all'
AND ClientName = 'all'
AND MasterGrp = 'all';
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Parameter not found!!');
GROUP BY HF.hotfixID, HF.ComputerID) A;
END;
END$$
|
|
|
|
|
A case when clause can only return values, not conditions.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
