August 22, 2013 at 9:45 am
Doctor Who 2 (8/22/2013)
I like what you wrote here, Steve, but I wonder how practical it is. However, it might just be my work environment. Having worked in a small IT shop for so many years, it does give me a somewhat stilted view of the world. Over that time I've worked with people who could easily handle the abstraction. But also over that time I've worked with people who simply cannot. Furthermore, some of them will complain bitterly to management, saying "I absolutely REQUIRE TOTAL AND COMPLETE ACCESS TO ALL TABLES AT ALL TIMES!" And management, being management, doesn't know the difference, so they order me to give access to all tables to all users, etc. Maybe if I worked in a larger environment, things would be different?
If you use "absolute" in much of anything, I find maturity to be lacking. Shades of gray dominate the world.
August 22, 2013 at 9:54 am
Steve Jones - SSC Editor (8/22/2013)
Doctor Who 2 (8/22/2013)
I like what you wrote here, Steve, but I wonder how practical it is. However, it might just be my work environment. Having worked in a small IT shop for so many years, it does give me a somewhat stilted view of the world. Over that time I've worked with people who could easily handle the abstraction. But also over that time I've worked with people who simply cannot. Furthermore, some of them will complain bitterly to management, saying "I absolutely REQUIRE TOTAL AND COMPLETE ACCESS TO ALL TABLES AT ALL TIMES!" And management, being management, doesn't know the difference, so they order me to give access to all tables to all users, etc. Maybe if I worked in a larger environment, things would be different?If you use "absolute" in much of anything, I find maturity to be lacking. Shades of gray dominate the world.
I take it you're not going to hire me then, are you Steve? 😀 And I'd be curious how banks would feel if their tellers applied shades of gray in disbursing money to clients.
August 22, 2013 at 9:56 am
Eric M Russel wrote:
Implementing business logic using a middle tier compenent, web service, or even a stored procedure solves the problem of data validation for applications, but not for ETL.
Agreed. However, discussions about abstracting business logic usual apply to business applications, which are usually written by application developers. ETL is usually the purview of DBAs.
As if you didn't know that already. 🙂
August 22, 2013 at 9:57 am
Steve Jones wrote:
If you use "absolute" in much of anything, I find maturity to be lacking. Shades of gray dominate the world.
Absolutely!
August 22, 2013 at 10:03 am
pdanes (8/22/2013)
Doctor Who 2 (8/22/2013)
I like what you wrote here, Steve, but I wonder how practical it is. However, it might just be my work environment. Having worked in a small IT shop for so many years, it does give me a somewhat stilted view of the world. Over that time I've worked with people who could easily handle the abstraction. But also over that time I've worked with people who simply cannot. Furthermore, some of them will complain bitterly to management, saying "I absolutely REQUIRE TOTAL AND COMPLETE ACCESS TO ALL TABLES AT ALL TIMES!" And management, being management, doesn't know the difference, so they order me to give access to all tables to all users, etc. Maybe if I worked in a larger environment, things would be different?If I was a DBA in such a situation, my response would be "I absolutely REFUSE TO GIVE YOU ANY ACCESS TO ANY TABLE AT ANY TIME!" Management can then take their pick. Of course, I have no dependents and no long-term payments on anything, so I can afford to be pretty cavalier about such things. However, I simply decline to do my job in a crummy way, regardless of who orders it.
The operative word here is "access" and "at all times". The BI group and data analysts typically want, and probably need, select permission on all tables, because ah-hoc querying production data is part of their job description, so I give them db_datareader. However, requests for sysadmin or dbo membership in production is a line that should not be crossed by anyone except for the DBA.
Also, if there are any special maintenace operations scheduled after hours that could be adversely affected by other users, then I'll switch database to restricted_user mode with the option to disconnect all user connections, despite whatever demands have been made that X user should have access at al times. It's easier to apologize to one user for a temporary dropped connection than it is to apologize to the department for a botched deployment or missing table that aborted during rebuild.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 22, 2013 at 10:10 am
pdanes (8/22/2013)
I take it you're not going to hire me then, are you Steve? 😀 And I'd be curious how banks would feel if their tellers applied shades of gray in disbursing money to clients.
No absolutes. You might still get an interview 😉
Tellers should apply some shades of gray. Not in amounts, but perhaps in how they work with customers. Shades of gray doesn't mean there are no rules, or that we are unfair, but that situations can call for different approaches.
Do I always need an ID? If they teller doesn't know me, yes. If they do, letting me withdraw without one might not be the end of business.
August 22, 2013 at 10:13 am
You didn't answer my question: if you believe that business logic in the database is a bad thing, do you forbid it altogether and refuse to permit any normalisation to 2NF or higher? If not, why are you saying "business logic in the database" is a bad thing instead of "business logic expressed as imperative procedural code in SQL is a bad thing" which is actually a tenable position, unlike the one you have stated which is utterly untenable.
There are business entity relationships, which are modelled by the normal forms, and there are business rules, which have no impact on database design beyond maybe the addition of certain columns. As anyone who has worked with me will tell you, I am a stickler for the implementation of a design according to the normal forms. Only once have have denormalized because it signicantly enhanced performance.
An office may have many jobsites. So a parent child table relationship. A discount for an order which needs to be apportioned across all the purchased items in a particular fashion is a business rule that needs to be dealt with in code, either at the database level or somewhere else. I prefer the somewhere else. To the greatest extent possible, I only want the database reading and writing.
Does that answer the question?
August 22, 2013 at 10:13 am
Craig-315134 (8/22/2013)
Eric M Russel wrote:Implementing business logic using a middle tier compenent, web service, or even a stored procedure solves the problem of data validation for applications, but not for ETL.
Agreed. However, discussions about abstracting business logic usual apply to business applications, which are usually written by application developers. ETL is usually the purview of DBAs.
As if you didn't know that already. 🙂
It depends on what we mean by business logic. Any rules governing data consistency and security should be in the database. Workflow logic should be in the application (avoiding the dreaded 5,000 line stored procedures), but workflow logic supported by meta-data is best. Generally speaking, anything that can be expressed as structured data should be in the database, and rules can usually be reduced to data.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 22, 2013 at 10:51 am
Steve Jones - SSC Editor (8/22/2013)
Doctor Who 2 (8/22/2013)
I like what you wrote here, Steve, but I wonder how practical it is. However, it might just be my work environment. Having worked in a small IT shop for so many years, it does give me a somewhat stilted view of the world. Over that time I've worked with people who could easily handle the abstraction. But also over that time I've worked with people who simply cannot. Furthermore, some of them will complain bitterly to management, saying "I absolutely REQUIRE TOTAL AND COMPLETE ACCESS TO ALL TABLES AT ALL TIMES!" And management, being management, doesn't know the difference, so they order me to give access to all tables to all users, etc. Maybe if I worked in a larger environment, things would be different?If you use "absolute" in much of anything, I find maturity to be lacking. Shades of gray dominate the world.
Hi Steve,
I have worked with people that don't see the world in shades of gray. You're either on board with whatever it is they're espousing, or you're an idiot (at best) or a disruptive influence (at worse). I hasten to say that this isn't my experience with everyone I've worked with, nor with most people I have worked with; just some.
Rod
August 22, 2013 at 10:54 am
Doctor Who 2 wrote:
I have worked with people that don't see the world in shades of gray. You're either on board with whatever it is they're espousing, or you're an idiot (at best) or a disruptive influence (at worse).
This seems to be a problem with all 'true believers', whether we're discussing theology or IT.
I agree with Steve that the world is a subtle place.
August 22, 2013 at 12:12 pm
And as I stated earlied how do you look at a log for a stored procedure? If I have a 1000 line SQL with 50 different SQL statements and it fails in the middle of the night how do I determine it failed on line 50 or 500?
Break out the procedure into lettered or numbered sections. Use TRY/CATCH and insert any errors generated by CATCH into a log along with the section of the procedure.
August 22, 2013 at 12:25 pm
Chrissy321 (8/22/2013)
And as I stated earlied how do you look at a log for a stored procedure? If I have a 1000 line SQL with 50 different SQL statements and it fails in the middle of the night how do I determine it failed on line 50 or 500?
Break out the procedure into lettered or numbered sections. Use TRY/CATCH and insert any errors generated by CATCH into a log along with the section of the procedure.
OK, so it's something I need to add when i design the procedure. That makes sense since I'm doing something similar in the SQL now, I've added code between each 'step' of the SQL so I can see where it failed of see how long each step has ran. This all gets put into a .log file after executing.
Something like this is what I do:
SELECT 'Step 00100 finished at' , convert(char(26), CURRENT_TIMESTAMP, 121);
I increment by 100 in case I need to add steps later.
Do you have an example of code for your suggestion?
Thanks,
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 22, 2013 at 12:40 pm
CREATE TABLE [dbo].[#Logs](
[Message] [varchar](300) NOT NULL,
[MessageType] [varchar](14) NOT NULL,
[Action] [varchar](120) NOT NULL,
[CallingProcedure] [varchar](50) NOT NULL,
[SuccessFlag] [char](1) NOT NULL,
[ErrorNumber] [int] NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [varchar](126) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [varchar](4000) NULL,
[DateTimeStamp] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[#Logs] ADD CONSTRAINT [DF_Logs_DateTimeStamp] DEFAULT (getdate()) FOR [DateTimeStamp]
GO
--A) Section-Divide by zero
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
INSERT INTO dbo.#Logs(Message,MessageType,Action,CallingProcedure,SuccessFlag,ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage)
SELECT 'Divide by zero has in section A has failed.','Alert', 'Insert','MyProcedure','N',ERROR_NUMBER(),ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(),ERROR_MESSAGE()
END CATCH
SELECT * FROM [dbo].[#Logs]
DROP TABLE [dbo].[#Logs]
August 22, 2013 at 12:53 pm
Chrissy321 (8/22/2013)
And as I stated earlied how do you look at a log for a stored procedure? If I have a 1000 line SQL with 50 different SQL statements and it fails in the middle of the night how do I determine it failed on line 50 or 500?
Break out the procedure into lettered or numbered sections. Use TRY/CATCH and insert any errors generated by CATCH into a log along with the section of the procedure.
When writing a stored procedures, I typically have something like a @Exec_Status int output parameter which I increment backward based on line number at various points in the code (-1, -22, -138, etc.). If the procedure runs to completion, then it returns with status >= 0, but if it falls into error hander, then it returns with < 0 value.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 22, 2013 at 1:05 pm
Eric M Russell (8/22/2013)
Chrissy321 (8/22/2013)
And as I stated earlied how do you look at a log for a stored procedure? If I have a 1000 line SQL with 50 different SQL statements and it fails in the middle of the night how do I determine it failed on line 50 or 500?
Break out the procedure into lettered or numbered sections. Use TRY/CATCH and insert any errors generated by CATCH into a log along with the section of the procedure.
When writing a stored procedures, I typically have something like a @Exec_Status int output parameter which I increment backward based on line number at various points in the code (-1, -22, -138, etc.). If the procedure runs to completion, then it returns with status >= 0, but if it falls into error hander, then it returns with < 0 value.
Eric,
So do you have to continually adjust your numbers if you have to add or remove code? Sorry, I'm just having a hard time visualizing this concept right now. Could be just getting tired at the end of the day.
Chrissy,
So I would need to put the 'Begin CATCH...' after each section?
Where does the results from the 'SELECT * FROM [dbo].[#Logs]' go?
Thanks,
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 15 posts - 46 through 60 (of 90 total)
You must be logged in to reply to this topic. Login to reply