Go Back  Bike Forums > The Lounge > Foo
Reload this Page >

Any DBA's here?

Foo Off-Topic chit chat with no general subject.

Any DBA's here?

Old 03-23-09, 10:16 PM
  #1  
crackerjab
WTF is that smell?
Thread Starter
 
Join Date: Dec 2005
Location: Charlotte, NC
Posts: 449
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Likes: 0
Liked 0 Times in 0 Posts
Any DBA's here?

I have a question for you. SQL specific involving executables from a query.
crackerjab is offline  
Old 03-24-09, 04:09 AM
  #2  
mlts22 
Senior Member
 
Join Date: Aug 2006
Posts: 998
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Likes: 0
Liked 0 Times in 0 Posts
Not sure what is asked... Embedding SQL commands, or trying to do a query which returns an executable BLOB which is run?
mlts22 is offline  
Old 03-24-09, 07:38 AM
  #3  
crtreedude 
Third World Layabout
 
crtreedude's Avatar
 
Join Date: Dec 2005
Location: Costa Rica
Posts: 3,109

Bikes: Cannondale F900 and Tandem

Mentioned: 2 Post(s)
Tagged: 0 Thread(s)
Quoted: 341 Post(s)
Likes: 0
Liked 0 Times in 0 Posts
More clarity, and if it is truly and executable, then you will have to state which server, since that isn't part of the SQL spec, but extensions.

You will also have to have near god permissions to do so, due to the dangers involved in having a hacker destroy the system.
crtreedude is offline  
Old 03-24-09, 08:02 AM
  #4  
crackerjab
WTF is that smell?
Thread Starter
 
Join Date: Dec 2005
Location: Charlotte, NC
Posts: 449
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Likes: 0
Liked 0 Times in 0 Posts
Originally Posted by mlts22 View Post
Not sure what is asked... Embedding SQL commands, or trying to do a query which returns an executable BLOB which is run?
A query that runs an executable blob.

Originally Posted by crtreedude View Post
More clarity, and if it is truly and executable, then you will have to state which server, since that isn't part of the SQL spec, but extensions.

You will also have to have near god permissions to do so, due to the dangers involved in having a hacker destroy the system.
I think a use command will suffice and I have god permissions.

I'm working on a project that will allow all of my field guys to run a specific query to gather information about some of our customers' error rates involving some equipment we have. SQL is running on everybody's computer and connect to a centralized database with the required information via VPN. The problem with this is that these people really shouldn't have full access to a database of this nature. I could easily run a DTS package but that still gives them access to the database. Here is the query that I have created to gather the information.



Use mxp0001

SET NoCount On

DECLARE @DtTmDayIdx DateTime,
@StartDtTm DateTime,
@EndDtTm DateTime

SET @StartDtTm = dateadd (week, -2, getdate())
SET @EndDtTm = dateadd (week, 0, getdate())



SELECT DISTINCT ComputerName

FROM
EvtEvent

WHERE Module = 'Rbt'

SELECT
Value AS SWVersions

FROM
dbo.sysproperty

WHERE
keyword like 'Gantry_FirmwareVersion%' or keyword='SwVersion'



Set @DtTmDayIdx = @StartDtTm


Create Table
#DtTmDayBlks
(StartDtTm DateTime,
EndDtTm DateTime)


While (@DtTmDayIdx < @EndDtTm)
Begin
Insert Into
#DtTmDayBlks
Values
(@DtTmDayIdx, DateAdd(Day, 1, @DtTmDayIdx))

Set @DtTmDayIdx = DateAdd(Day, 1, @DtTmDayIdx)
End

Select
EventDtTm,
event,
notes,
computername

Into
#EventDtTms

From
EvtEvent

Where
EventDtTm Between @StartDtTm And @EndDtTm

Select
Convert(VarChar(10), D.StartDtTm, 120) As Day,
(
Select Count(*)
From #EventDtTms
Where EventDtTm Between D.StartDtTm And D.EndDtTm
and event='VialDeliverySuccessf'
--and notes like 'VialSize=16%'
--and notes like 'VialSize=30%'
--and notes like 'VialSize=40%'

) As EvtCount
From
#DtTmDayBlks As D

Drop Table #DtTmDayBlks
Drop Table #EventDtTms


SELECT
Convert(varchar(19), EventDtTm, 120) AS FmtdEventDtTm,
Event,
RxNum,
Notes

FROM
EvtEvent

WHERE
EventDtTm Between @StartDtTm AND @EndDtTm
AND Event = 'VialDeliveryError'

ORDER BY
FmtdEventDtTm


SELECT
Convert(varchar(19), EventDtTm, 120) AS FmtdEventDtTm,
Event,
RxNum,
Notes

FROM
EvtEvent

WHERE
EventDtTm Between @StartDtTm AND @EndDtTm
AND Event = 'MainGantryError'

ORDER BY
FmtdEventDtTm


SELECT
Convert(varchar(19), EventDtTm, 120) AS FmtdEventDtTm,
Event,
RxNum,
Notes

FROM
EvtEvent

WHERE
EventDtTm Between @StartDtTm AND @EndDtTm
AND Event = 'OutputGantryError'

ORDER BY
FmtdEventDtTm
crackerjab is offline  
Old 03-24-09, 08:12 AM
  #5  
austropithicus
Guest
 
Posts: n/a
Mentioned: Post(s)
Tagged: Thread(s)
Quoted: Post(s)
Originally Posted by crackerjab View Post
A query that runs an executable blob.
No offense, but that just seems backwards to me.
 
Old 03-24-09, 08:18 AM
  #6  
crackerjab
WTF is that smell?
Thread Starter
 
Join Date: Dec 2005
Location: Charlotte, NC
Posts: 449
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Likes: 0
Liked 0 Times in 0 Posts
Originally Posted by austropithicus View Post
No offense, but that just seems backwards to me.
Ok. Elaborate please. I'm asking for help. And haven't really done any programming in the better part of a decade.
crackerjab is offline  
Old 03-24-09, 08:23 AM
  #7  
austropithicus
Guest
 
Posts: n/a
Mentioned: Post(s)
Tagged: Thread(s)
Quoted: Post(s)
Originally Posted by crackerjab View Post
Ok. Elaborate please. I'm asking for help. And haven't really done any programming in the better part of a decade.
I think that the database should contain no business logic. It should just be a well designed datastore that models your business data. Applications should use the database and not vice-versa.
 
Old 03-24-09, 08:28 AM
  #8  
crackerjab
WTF is that smell?
Thread Starter
 
Join Date: Dec 2005
Location: Charlotte, NC
Posts: 449
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Likes: 0
Liked 0 Times in 0 Posts
Originally Posted by austropithicus View Post
I think that the database should contain no business logic. It should just be a well designed datastore that models your business data. Applications should use the database and not vice-versa.
While I agree, I don't want to spend that much time on it and ,due to the politics of my office, I would have to submit it through SW development.
crackerjab is offline  
Old 03-24-09, 08:43 AM
  #9  
austropithicus
Guest
 
Posts: n/a
Mentioned: Post(s)
Tagged: Thread(s)
Quoted: Post(s)
Originally Posted by crackerjab View Post
While I agree, I don't want to spend that much time on it and ,due to the politics of my office, I would have to submit it through SW development.
Yeah, I can understand that. Compromises are the norm.
 
Old 03-24-09, 08:45 AM
  #10  
tjwarren
call me T.J.
 
Join Date: Jul 2008
Posts: 361

Bikes: trek 820

Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Likes: 0
Liked 0 Times in 0 Posts
Originally Posted by crackerjab View Post
Originally Posted by crtreedude View Post
More clarity, and if it is truly and executable, then you will have to state which server, since that isn't part of the SQL spec, but extensions.
I think a use command will suffice and I have god permissions.
He means you'll need to tell us what server it is, not your query engine, because many things are platform-specific.

Based on your code and comments, I'm assuming SQL Server. Knowing the version would also help.


Originally Posted by crackerjab View Post
I'm working on a project that will allow all of my field guys to run a specific query to gather information about some of our customers' error rates involving some equipment we have. SQL is running on everybody's computer and connect to a centralized database with the required information via VPN. The problem with this is that these people really shouldn't have full access to a database of this nature. I could easily run a DTS package but that still gives them access to the database. Here is the query that I have created to gather the information.
This isn't very clear. You'd like your field techs to have the ability to run pre-configured queries against the customers data? Is that it?

When you say "full access to a database of this nature" and "still gives them access to the database", who are we talking about and what database are you referring to? Your techs and your central database? Your customers and the central database? Your techs and the customer's database?
tjwarren is offline  
Old 03-24-09, 08:50 AM
  #11  
crackerjab
WTF is that smell?
Thread Starter
 
Join Date: Dec 2005
Location: Charlotte, NC
Posts: 449
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Likes: 0
Liked 0 Times in 0 Posts
Originally Posted by tjwarren View Post
He means you'll need to tell us what server it is, not your query engine, because many things are platform-specific.

Based on your code and comments, I'm assuming SQL Server. Knowing the version would also help.




This isn't very clear. You'd like your field techs to have the ability to run pre-configured queries against the customers data? Is that it?

When you say "full access to a database of this nature" and "still gives them access to the database", who are we talking about and what database are you referring to? Your techs and your central database? Your customers and the central database? Your techs and the customer's database?
Yes, I want my field techs to be able to run pre-configured queries.

Our software dumps the database every night to a centralized server. My tech's have access to this database. I use the "use" command to specify the DB. This is all Microsoft SQL 8.0.
crackerjab is offline  
Old 03-24-09, 09:33 AM
  #12  
chipcom 
Infamous Member
 
chipcom's Avatar
 
Join Date: Sep 2005
Location: Ohio
Posts: 24,365

Bikes: Surly Big Dummy, Fuji World, 80ish Bianchi

Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 1 Post(s)
Likes: 0
Liked 0 Times in 0 Posts
One word - Infopath. No handoffs to your developers required, the users have at least some abstraction from the database...and a decent UI.
__________________
"Let us hope our weapons are never needed --but do not forget what the common people knew when they demanded the Bill of Rights: An armed citizenry is the first defense, the best defense, and the final defense against tyranny. If guns are outlawed, only the government will have guns. Only the police, the secret police, the military, the hired servants of our rulers. Only the government -- and a few outlaws. I intend to be among the outlaws" - Edward Abbey
chipcom is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Thread Tools
Search this Thread

Contact Us - Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.