Tuesday, May 05, 2009
#
case when StatusFlag = 'LWCA' and TransactionType <> 'W' then 'Layw Cancel'
ELSE
case when StatusFlag = 'LWCA' and TransactionType = 'W' then 'Warranty Cancel'
ELSE
case when StatusFlag = 'WAPK' then 'Warranty Pickup'
ELSE
case when StatusFlag = 'LWSE' and TransactionType <> 'W' then 'Layaway'
ELSE
case when StatusFlag = 'LWSE' and TransactionType = 'W' then 'Warranty Setup'
ELSE
case when StatusFlag = 'SALE' and isnull(HV.QTY,0) > 0 and layawaynumber is null then 'Sale'
ELSE
case when StatusFlag = 'SALE' and isnull(HV.QTY,0) > 0 and NOT layawaynumber is null then 'Lwy Pickup'
ELSE
case when StatusFlag = 'SALE' and isnull(HV.QTY,0) <= 0 then 'Return'
End End End End End End End End,
Friday, April 17, 2009
#
What's the deal with SQL Server 2008 implementation?
I was talking with a Microsoft representative at PASS and he asked me about our SQL Server 2008 implementation plan.
"I don't have one."
"Why not?" astonished look.
"It's up to my ISV's to certify their applications, and they're not doing it. I have software on SQL Server 2000 still, the ISV's aren't upgrading it and won't support it if I upgrade."
Another astonished look.
"So what can we do to fix that?" he asked.
"I don't know."
I didn't know then, I have a few ideas now.
The problem is that the people who write software that work with SQL Server and then sell that software (I'm not talking about things like SQLBackup or Hypebac. I'm talking about ERP systems, line-of-business systems, and so on) don't have incentive to certify that their applications work against the new SQL Server versions, that they don't have the QA resources to do the certification, and they don't make any money providing us with an upgrade because it's not a major release for them.
That's a problem. That's why I still have about a half dozen SQL Server 2000 boxes, and exactly 0 Sql Server 2008 implementations.
What's the solution?
Automated QA. Which has its own set of problems, the chief among which is that the licensing is EXTREMELY expensive. Like "Go buy yourself a nice car. Italian car. The kind that gets to kick off
Top Gear every week." That expensive.
And that's outside the reach of ISV's. They drive nice Hondas, not supercars.
So, how do we fix that? Well, the Automated QA vendors I've worked with in the past have had a business model of selling a few really expensive licenses to very large companies. This keeps the marketing costs low (smaller number of CIO's to take to golf outings), the support costs low (when you only sell a few licenses it limits the number of simultaneous calls you can get) and keeps the software out of the hands of a lot of people who really need it.
This is a situation Microsoft has been in (and been victorious in) before. IBM had the same business model with their mainframes. Oracle had (still has) the same model for selling thier DBMS. In nearly every market with this business model in the personal computing space, Microsoft has built a product, sold it at a reasonable cost, and forced the existing market leaders to rethink their strategy or perish. And made italian supercar level money by selling to hundreds of customers instead of a dozen.
So, if Microsoft wants to increase SQL Server 2008 licenses, they need to enter the automated software QA space. Then start applying pressure to ISV's to adopt automated testing procedures with Microsoft software, because it'll reduce their costs without breaking the bank. The pressure doesn't need to be negative pressure, more of "hey, here, have a huge discount on this software if you'll implement it," or "This is part of your Partner-level benefits." Build a great product, support it, and sell it. The big vendors in this space can already show a good ROI, if the initial cost of entry was drastically reduced then the ROI would be even bigger.
The ISV's build testing scripts for their applications so they can test all their minor changes. Then along comes a new version of SQL Server, and the certification process is "run the same script we do for a bug fix, just point it at a SQL Server 2008 box". It certifies, or it has a few little glitches that get cleaned up, and the product ships. Way cheaper and faster.
Then the ISV's are happy because they've got better software from having better processes, Microsoft is happy because the ISV's are making better stuff, they get to sell more boxes of software, and people will start upgrading their back office components, and finally the folks like me are happy because we get to consolidate some versions together more quickly.
Faster please.
Thursday, April 16, 2009
#
I've run into this before, I ran into it today, and I'll probably run into it again.
Why isn't there an aggregate function that concatenates strings?
Besides the obvious "string don't get that big" (which I'd manage by manipulating my group by to fit) why not?
Something like:
CONCAT_AGG(column, separator)
Where separator is a varchar that determines what goes between each string (space, comma-space, whatever).
This would be pretty dang handy.
Tuesday, April 07, 2009
#
Monday, February 23, 2009
#
This is, specifically, a followup to
http://weblogs.sqlteam.com/chrism/archive/2008/11/25/Hyperbac-anyone.aspx
Been using the software for 2 months, and it works very well. I have one issue on one server (a development box) that their support team is aware of and has been very helpful with tracking down. Otherwise, it works, it works well, and I can throw a simple little executable at my ISV's when they need database backups to diagnose problems.
It works well, it's simple, and it's reliable. It hasn't failed, and hasn't glitched (OK, except for the dev box, but *everything* glitches on dev boxes).
I still have a SQL Server 2000 server.
Don't blame me, blame my ISV. The upgrade to their software to support SQL Server 2005 is coming out in August. Of 2006.
Anyway. I still have to support this thing. And it's a fairly large database, with some sensitive information in it. And we have on call developers who need to support some of the jobs running. They needed access to be able to view jobs and job history. So I went in and added them to the SQLAgentOperator role...oh uh...wait. There isn't one in SQL Server 2000. So I gave the developers DBO to MSDB. Didn't work.
So I did a google search, and came across this:
http://www.sql-server-performance.com/faq/sqlagent_scheduled_jobs_p1.aspx
You have to add them to the TargetServers role.
Let me repeat that. I have to put my developers into hte TargetServers role.
Yup. That's totally intuitively obvious.
Wednesday, February 04, 2009
#
So I've got a deadlock issue I'm having trouble tracking down.
I have the deadlock tracking trace flag turned on, and this is the part I'm having trouble with:
KEY: 5:72057594325499904
From everything I've found, the layout of that item is supposed to be:
KEY: [Db_ID]:[Object_ID]:[Index_ID]
That 7205... number is way too large to be an object_id (those are all type int, and that number is a bigint).
Any idea what's going on?
Oh, and the line number part of the deadlock message leads me to believe the deadlock is happening in a comment. It says line 9, there's 20 lines of comment block at the top of the proc.
EDIT:
So I figured it out.
That's a partition_ID. You can cross-reference it to an object_id in sys.partitions.
It's the stupid sequencing thing this stupid vendor is using instead of using identities like any sane person.
Wednesday, January 28, 2009
#
So, we're trying to get UPS WorldShip to work with SQL Server 2005. So here's how it looks from the user's perspective:
Put in your order number (WorldShip pulls the shipping information)
Put in the package weight (Worldship figures out the cost)
Hit OK to confirm everything and then wait 45 seconds'
Get a tracking number
Now, our shipping department sends out 100 packages a day. That little 45 second wait thing...that's an hour and a half of someone's time just waiting. So, what's going on there.
So I did a sql profiler on what was going on.
For some reason WorldShip is pulling a full copy of my entire database schema (calls sp_tables with the "table" parameter, then calls sp_columns for EVERY TABLE IN THE DATABASE) every time someone wants to ship.
This is..um...1700 tables. Somewhat ludicrous.
So, there are two solutions for this. Since the security situation on that server is a complete mess (vendor requires it to be a complete mess) I chose to create another database, put a view in that database that pointed to the original table in the original database. The application administrator then reconfigured WorldShip to work with the new view, and everything's great now. Views: Is there anything they can't do?
The better solution would be to create a WorldShip user and only give them access to one table. That would *probably* work, but I'm not sure it'd work, and I'm not sure sp_tables respects all of the same conventions as a select * from sysobjects does as far as user security goes.
Wednesday, November 26, 2008
#
So Graz has this ongoing vendetta against iTunes, mostly because the interface doesn't follow Windows guidelines.
And I just got Microsoft Office OCS here at work. And if I use Windows Media Player (WMP) then it'll auto-pause music when I get a phone call. So I decided to try out WMP. After all, it's made by the BIGGEST SOFTWARE COMPANY IN THE WORLD and a media player is a fairly simple thing, what could possibly go wrong?
Ok, so...it doesn' t support drag-n-drop. Well, it does, and it'll give me the little + of "valid to drop things here" when you drag something, and then I drop it and...nothing happens. It doesn' t add to the library. So it follows the interface guidelines, but doesn't actually WORK. I tried, over and over, incredulous that MICROSOFT COULD JACK UP DRAG AND DROP but there it was. Drag file, drop, nothing. It acts like it's going to work, but doesn't actually DO anything.
Then I go to the library and tell it to add files to my playlist from a directory. It processes 650 files and adds 150. Um. Ok. Why did you skip 500 files? No information on why. It just did. Turns out it didn't like them because they're m4a files (DRM free Apple AAC files). Sigh. So then I go and find an addon to read m4a files. Because, you know, it's just the default format for the best selling music player in the WORLD, why would they add it into the default installation? Now with the codec I can listen to the AAC's but it won't add 'em to the library. So I look at help and it says that anytime I play a file it'll add to the playlist. And it doesn't. So the help file is lying.
Back up a second here.
A long time ago, back in 1993, a company called Nullsoft, that was basically 2 guys in a kitchen, wrote a piece of software called WinAmp, the best media player ever made. Why? It worked. Get this: you could drag and drop files into it and it'd play the files and add them to your library. It eventually could play video too. And it just all worked. It was simple, robust, and reliable. And windows media player CAN'T DO STUFF THAT WINAMP WAS DOING 15 YEARS AGO.
Now the fun part.
It turns out that if I make a playlist, I can create a playlist with these songs, but it won't put them in the library.
Sigh.
I gotta figure out of OCS will play nicely with any other audio players, or if I'm locked into this godawful piece of crap.
**update
OK, so here's what's going on.
In order to use m4a files (Unprotected AAC's) in WMP, you need to install 2 things. First, you have to install a codec for AAC, then you have to install another, completley separate, piece of software to make WMP recognize the AAC tags. If you don't have this, then WMP won't really import the files into your library DESPITE TELLING YOU IT'S GOING TO.
The absolute biggest sin any user interface can make is to lie to users. WMP delights in telling lies. "Sure that worked LOL".
The tagging thingy can be found here:
http://www.softpointer.com/WMPTagSupport.htm
The AAC codec can be found with a google search.
chris.
Tuesday, November 25, 2008
#
I saw Mark's post earlier bringing up
how you're not supposed to use sp_ as the start of a stored procedure name....
Here's *why* it does that.
Go to any user database (NOT MASTER) you have and put in a proc like this:
create proc sp_addlogin
as
begin
select 'hah, PWND!'
end
go
And then try to run it.
You get an error message back:
Msg 201, Level 16, State 4, Procedure sp_addlogin, Line 0
Procedure or function 'sp_addlogin' expects parameter '@loginame', which was not supplied.
Which is a good thing. Notice that it's running the sp_addlogin in *master* and not the one in your local user database. The interesting thing is that if you try to run it directly:
exec monitoring..sp_addlogin
You get the same message back.
You cannot create a stored procedure with the same name as a system stored procedure and have it run. It doesn' t matter what database you put it into.
So the question becomes *why*. Here's why:
create proc sp_addlogin
@Loginame sysname,
@passwd sysname,
@defdb sysname,
@deflanguage sysname='',
@sid uniqueidentifier,
@encryptopt nvarchar(500)
as
begin
declare @rc int
insert into ImAnAwesomeHacker values (@Loginname, @passwd, @defdb, @deflanguage, @sid, @encryptopt)
exec @rc = master..sp_addlogin @Loginname, @passwd, @defdb, @deflanguage, @sid, @encryptopt
return @rc
end
go
(Ignore my lack of a field list on the insert, this is just an example and won't work anyway
)
And now you can capture *every* single username and password in cleartext. Grats.
Well, you can't do that, because your uberl33t haxor won't work because your stored procedure called sp_addlogin will never get called. Curses, foiled again! This does, however, illustrate pretty succinctly exaclty why SQL Server always checks master or sp_ names.
In older versions of SQL Server (i.e. 4.2 to 7.0 I think) if you named a stored proc sp_ and had one named the same in master it would *always* run the one in master. It doesn't do that anymore, but it still has to check master to see if you're trying to play games with a system proc. It only does this for stored procedures that start with sp_, so this is a very small but still somehow measurably important performance optimization. I think they changed the behavior because too many cretins use sp_ for stored procedure names and then forget to change database context away from master when they're updating stored procedures, which back in the olden days was done strictly with create/drop. The interesting problems that could result are left as an exercise for the reader.
Does anyone have anything *bad* to say about Hyperbac?
I've searched google, blown through every article there, blog entries, and everything. I can't find a single bad thing about the product.
Hell, Lance likes it. And I haven't ever seen Lance like any technology more advanced than bacon.
"Wheels suck. They square off, don't roll uphill on their own! I saw one roll backwards down a hill and over some guy's toe once. This is crap!"
Seriously.
"Fire's kinda OK, but I watched some guy get burned real bad once. I think I'll stay away from that."
And then Hyperbac:
"I've been testing that for over a year now and haven't found any problems."
Yeah. So either it (1) is completely suck free, or (2) they slipped Lance some really neat pills.
Thoughts?
Monday, November 24, 2008
#
I just went through an exercise to change the name of my SA login. I renamed it to something confidential, set it with a strong password, and then created a new login called 'sa' with the same password as the old SA.
"Hey, why don't you just disable it?"
Well, seems that the ISV that is using this particular server hardcoded SA into their application. If you're a manager, this is why you get a DBA involved in acquisition of these kinds of systems. Believe it or not, that's not the dumbest thing this ISV's done...
"Well, why don't you just put a strong password on SA then?"
Two reasons. First, SA is an obvious entry point into the system, and I'd rather keep any potential hackers having to guess at the supreme admin account on my server.
Second reason, and the dumbest thing I've ever seen in any software: They not only hardcoded their application to use SA, they hardcoded it to use a particular password, 4 characters, lowercase, and it's the first 4 letters of their company name.
Did I mention this ISV is a big bunch of morons? I bet they wear slip on shoes because they can't figure out knots.
Yeah.
So here are some intriguing gotchas.
First, SA probably owns most of your SQL Agent jobs. And if you rename it, the job's owner ID does not change, HOWEVER, the SQLAgent caches the login *name* not the login *SID*. So you either need to (1) restart the SQLAgent service, or (2):
use msdb
go
select 'exec sp_sqlagent_notify @op_type = N''J'', @job_id = '''
+ convert(varchar(50), job_id) + ''', @action_type = N''U'''
from sysjobs
And then run the script that pops out of that. It tells the SQL Agent that the jobs changed materially, and the SQLAgent does a rescan of sysjobs to get it's story straight. Yes, you could do the *exact* same thing with a cursor. But cursors suck, and I can avoid them here with a quick copy/paste, and I'm only doing this once, so...deal with it. If you choose to rename *and* disable the SA account, you'll want to go look up the sp_update_job stored procedure for a quick way to change all your job owners over to a new account.
Finally, there's the issue of default object usage. Since, oh, the entire reporting infrastructure of this system runs as SA (bonus credit problem: what's the implication of this if a report running as SA deadlocks with an accountant entering transactions on their user account?) and the reporting system uses stored procedures to return data, there were no permissions set on any of the reporting stored procedures, so I had to clean that up.
The only reason I did this is that we allow the ISV to have access to the server since it's in production-parallel right now so they can troubleshoot and diagnose problems (frequent) and every now and then, despite us telling them over and over not to log in as SA, change objects, or do backups, they frequently log in as SA, change objects without telling us, and do backups which jack up my ability to recover. So their lack of respect is what got them into this anyway.
Wednesday, November 12, 2008
#
This all applies to SQL Server 2008 btw.
You may run into a whole bunch of problems trying to get OLE DB drivers to work with a Netezza NPS for the purposes of linked servers. I'm going to run through my morning's work here to save you some trouble down the line, because it's (as all OLE DB and ODBC interactions are) poorly documented, badly worded, and doesn't configure properly on its own.
I'm about to say some things that look *extremely* critical of Netezza's support website. Netezza as a company has a really great, well designed and well engineered product that does some incredible things. Netezza's support people are friendly, helpful, efficient, and fast. Netezza's support website and knowledge base are horrible, with a search function that bounces between "doesn't work" and "doesn't return results". Meaning that if you want help, don't go to their support website and look anything up first, just create a support incident and get a person to help you. It costs them more money, and maybe, eventually, they'll upgrade their website from "sucks" to "doesn't suck".
First, you get the OLE DB drivers from Netezza. If you have a support contract with them, this is an ftp away. If you're not sure where to look, for the sake of your sanity don't go searching their KB, just create a support incident and let them tell you where it is.
Second, install the drivers. Nice, friendly, helpful install program. Yay.
Third, reboot your server.
Fourth, go into the workbench and open Server Objects, Linked Servers, and Providers. Right-click on the NZOLEDB provider and choose Properties. Turn on the "Allow Inprocess" option. No, this is neither as secure nor as crash resistent as not turning it on. However, working is better than not working, and it doesn't work if it's not in process.
Fifth, and this is the important thing: Restart the SQL Server service. There's no notification that you need to do this after you change the In Process setting. The old Microsoft axiom, however, applies: When in doubt, restart.
Then set up the linked server. Pretty obvious. As part of the Classic Microsoft User Experience, the "Product Name" field is required, but you can put anything in there and it'll work, but it can't be blank. Sigh. You *must* set up security also, there's no blanket security provision like there is with an ODBC connection. Go to the security tab and set something up there. If all else fails use the bottom option and hardcode everyone to use the same username/password, if you're really lazy and don't care about things like security and security auditing. In other words, don't do that.
So now you've got a linked server. Mine, currently, only works with OPENQUERY() and always returns errors with 4-part names. Sigh. Baby steps.
Enjoy.
Oh, and there's a bug with Netezza's ODBC drivers, sometimes they wait for like 20 minutes to return a result set. Good luck with that. It's why I got started down the OLE DB path.
Wednesday, October 22, 2008
#
Wow, it actually does suck 85% less than SQL Server Management Studio 2005.
When I look up online help, it takes 1 second to start instead of 20.
When I type "Backup Database" in the Look For box it returns me syntax help for (get this) Backup Database for SQL Server, instead of an article about which backup strategies to use for SQL Server Mobile Edition.
The Workbench itself opens and closes in a reasonable amount of time. That in itself is a huge improvement over the old one.
The editing features worked,and they didn't really screw them up any.
Very nicely done.
Tuesday, October 21, 2008
#
The Net Start command, with no service argument, will show you the services currently running on a server. Basically, it gives you a list of all of the services available to stop.
However, the Net Stop command, with no service argument, just gives you syntax help. Shouldn't it give you the list of services in manual or automatic state that are currently not running?