Instead of working, I blog.

OK, I've been promising this for a long time now, but I finally got the RSS ready for IT Bookworm. So you guys go on and subscribe and I'll update it with book and video reviews as well as press releases for new books and videos. I may even put some training discounts up there if they come my way.

I'm actually double posting this here and at Underground just to help get the word out.

OK, from time to time this topic comes up so I thought I'd publish this info in the hopes that someone will find it useful.

The problem is with getting non-admins the ability to admin jobs in SQL2K. It's widely known that only SAs can do this, but there's a special DB role in msdb that lets you do this w/o having to be an admin. Rather than give you a big explanation, here's the code so you can test it yourself. This will let anyone in this group admin SQL jobs just like they were SA.


use msdb
exec master..sp_addlogin 'testme', 'testme', 'msdb'
exec msdb..sp_adduser 'testme', 'testme', 'TargetServersRole'
grant execute on sp_add_job to TargetServersRole
grant execute on sp_add_jobstep to TargetServersRole
grant execute on sp_update_job to TargetServersRole
grant execute on sp_add_jobserver to TargetServersRole
grant execute on sp_add_jobschedule to TargetServersRole
grant execute on sp_delete_jobschedule to TargetServersRole
grant execute on sp_delete_job to TargetServersRole
grant execute on sp_start_job to TargetServersRole
grant execute on sp_stop_job to TargetServersRole

I hope this helps somebody. If you want to give the ability to admin only specific jobs though, you'll still have to use one of the intermediate steps you did before, or make the user the owner of the job.
Too bad groups can't own jobs.

Today on SSWUG, Steven talked about the new DB Architect cert from MS. I have no problem with what he said, and in fact, I even agree with most of it. However, it's really going to be hard to make any of us care about it when pretty much every company out there acts like DB work is so easy that pretty much anyone can do it. Companies go through DBAs like candy with this laissez-faire attitude that any DBA is as good as the next.

It's really getting kinda sickening. Somehow they think that their IT staff is so replacable that they can do anything to us they want and they can just go get another one. Well, I don't know about any of you guys, but I don't remember seeing any DBAs standing on the work wall at 7-11.

I really think that most of the problem is that companies still don't see the real benefit to putting the time in on their apps before they hit production. Most companies still just rush things together and test their apps under fire. They have the coders write the DB schema, and architect the entire solution. Well, I'm sure that reading a couple .Net books qualifies you as an expert in networks, storage platforms, DB schemas, indexing, etc. And until companies start to realize that all of these things can't be done by one person, and that they're all a lot harder than they sound, we won't get any real respect. See, once they make that leap, and start to finally see that these things not only matter, but they'd actually start listening to their DBAs and actually treat them like their valued members of the company with something very important to contribute.

Now, nothing has really spawned this post except me thinking about how trivial this new cert is going to be. If companies don't even see the need for this stuff, then our precious new cert certainly isn't going to mean anything to them. So basically, we're getting certs to impress other geeks at conferences. It's almost like we're displaying our Klingon ranks for each other to see.

And I know what you're thinking... I'm complete wrong and companies are really into architecting real solutions. Well, if that were true, then we wouldn't need to be compliance audits for basic IT processes. Hell, we have to fight companies for even an extra week to benchmark a new app, much less for the money to buy the benchmark software. It's pathetic. I realize that it's a pain in the ass to have to hold off on your deployment for something as worthless as benchmarking, but you're just gonna have to start sucking it up.

Anyway, that's all I've got.

We're currently setting up Informatica and we got some scripts from them to create the DB. There were 3 scripts in all. The first script went ok. The 2nd had some problems, but it was small enough that I was able to just fix them and get past it in fairly short order.

The 3rd however was just pathetic. Not only was it huge, but it was plagued with dozens upon dozens of errors. The header of the scripts says that they were written and tested in SQL Server 7.0. WHAT? You haven't updated your scripts in 10yrs? Not only that, but with the errors I was getting, there's just NO WAY they ever even ran on SQL 7. It's mathematically impossible. Here are the problems I remember... there may have been more.

1. Deleting tables before they're even created. They should have put an IF clause in there.
2. They were deleting PK tables before their FK tables were deleted. Excuse me guys... have you ever heard of referential integrity?
3. They were creating relationships with data types that didn't match, so I got TONS of errors that the 2 column in the relationship had different data types. I don't even see how this is possible.

Frankly, I'm not only disappointed, but somewhat shocked at the lack of skill a company like Informatica displays here. My mother could write better SQL than this. There's no way this code ever ran. I've even got a VMWare image with SQL 7 on it, and I ran it in there, and it bombed too, so don't give me any of that wrong version of SQL crap.

It really does make you wonder though. How many people are actually using Informatica? Because for them to send a customer such pathetic code, it clearly hasn't been tested... EVER.

And this isn't the only bad code I've ever received from a vendor. It seems like somehow none of them can write SQL that runs worth a damn. It really doesn't seem that hard does it? Because I've written hundreds of scripts, and when I'm done, I run it. It either runs well or it doesn't. If it doesn't run without errors, I fix it. Go figure! And some mistakes are understandable, but like I said, there's no way that dropping a table before it's created has ever run without an error. The code was simply never run against a DB.

I can probably tell you what happened though. The developer already had the schema in his DB and he was getting errors when he needed to create the tables so he added drop statements. I guess it never crossed his mind that someone might be installing for the first time. The other errors however... there's just no way he ever got the rest of that script to run.