syscomments

COM+, DTC, and 80070422

Chris Miller — Thu, 11 Feb 2010 17:18:42 GMT

One of our "packaged" software bits that accesses my servers is going through an upgrade right now. Apparently this software requires DTC to be installed on my SQL Server, and able to accept remote connections. So I look up how to do that in the knowledge base:

http://support.microsoft.com/?kbid=555017

And immediately hit a roadblock. The DTC components aren't showing up in my Component Services console. The entire console's acting weird (well, weirder than usual) and when I go into the console and click "Options" it insists on having a timeout entered, and when I enter one, close the box, and go back, the setting's gone again and I'm required to re-enter it. Lots of weirdness, and no DTC tab. If you open the COM+ folders, you immediately get error 80070422.

After a lot of searching I was looking through the Services listing on the box (after restarting DTC for the twelfth time) and saw that "Com+ System Application" was disabled. I set it to manual, rebooted the box (test server) and everything started working.

So, if you're trying to follow those instructions and discover that the Component Services tool is acting odder than usual, make sure that service isn't disabled.

Star Trek Online Open Beta, Thumbnail Review

Chris Miller — Tue, 19 Jan 2010 02:13:41 GMT

I figure there's got to be some crossover between the SQL Server folks and the MMO folks, so here ya go.

I downloaded and played the new Star Trek: Online open beta this week, just to see if it was going to be worthwhile. So here's some of my thoughts on it.

It's an MMO, Massively Multiplayer Online game. Typically that means you'll buy a box at your local retail outlet for $49.99 and then pay a monthly subscription fee, probably $15.

It's also a Role Playing Game. I was looking forward to it, thinking it'd be a really interesting social experiment to get people to work together to run a ship.

Wrong.

So it's an MMO, and you're basically a one-man army, you have a ship of your very own, that you can fly using your mouse. As long as you don't want to go upside down. It's the Federation, son, YOU WILL FLY UPRIGHT AT ALL TIMES. The weapons and upgrades are the predictable "more power" upgrades you'd get at the beginning of any MMO. There seemed to be a lot more MMO jargon used in the weapon descriptions (weapons have DPS, armor/shield penetration, etc) that might be opaque to people who haven't done a lot with MMO's before. That is likely to change, this is a beta.

Basically there are 2 "modes" to the game. Ship mode, where you're flying your ship, and ground mode, where you're running around hallways as a person.

The ship combat was...well, boring. Fly around (but not upside down) and shoot things. make sure your weakest shield is facing away from your enemy. Hold down the space bar to auto-fire. It takes about 2 minutes to master. The shield thing is interesting too, if by "interesting" you mean "really simple and cheesed-out". There are 4 quadrants to your shield: fore/aft/starboard/port. If you fly above or below someone, it's pretty much *random* what's going to get hit. Your energy weapons have a 200 degree firing arc, facing either forward or aft, but ignoring up and down. Last time I checked, space combat was supposed to be 3D. So it's a 2-d based combat where you can fly in 3 dimensions, sort of.

You can customize your ship pretty much on the fly. Whenever you're out of combat, you can completely replace your weapons with any that you salvage (loot) off another ship you kill. You know, what would take a drydock and several weeks takes 2 seconds in MMO-land.

Then occasionally you'll touch down on the planet and be the away team. Now this is Borg-era Star Trek, and they let you, the captain, the person responsible for the ship, beam down into dangerous situations with the away team. Hrm. Yeah, so that regulation obviously didn't make it into the RPG. When you're on the ground (or in another ship) the game acts like the world's most dumbed-down third person shooter. Click your target, click your attack. Repeat until target dies. You have 3 attacks (2 shooting and a melee knockback) and that's...well, that's it. You have medical packs too. I never really felt in enough mortal peril to use one though.

One of the things I like about MMO's is specialization. I can pick a character class, learn that character class completely, and then I'll be a fairly good player. ST:O went a different way. You choose a character class, but I'm not exactly sure why, since no matter what class you pick you get the same skill trees, leveling track, and role. You're the captain. That's it. Except when you're not the captain, and you're having to learn the roles of all the other classes so you can populate your bridge with good officers. And then know all the other skill trees, so you can level those officers as they gain experience.

It's like they took the worst parts of most RPG's (having to micro-manage a team of NPC's), the worst space flight game ever, and a really bad 3rd person shooter and crammed them into one package.

So far: Bad character management, bad space flight, and bad third person shooter. So how's the social part?

Um. Yeah. There's a chat box, and it scrolls the Star Trek equivalent of Barrens chat. People asking where different things are. Someone actually (jokingly) asked where Mankrik's Wife was (a WoW joke) just to make the scene complete. You *can* team up and there's a system called "Fleets" which look like a guild based system. I didn't stick around long enough, the game mechanics were, frankly, boring me to tears.

The graphics are...fine. I guess. I have a midrange video card, there was no video lag (lots of server lag though). Everything looked pretty enough, which for an MMO means "about 2 generations behind any other rendering system". Phasers look like phasers, photon torpedos look like photon torpedos. Nothing earth-shatteringly pretty or mind-numbingly awful.

There are lots of loading screens. In the WoW universe you can travel from the far southern end of the world to nearly the northern tip of the world with no loading screens and smooth transitions. If you attempted to walk that distance it would take probably a couple of hours, which is why there are lots of fast-travel options in WoW. There were loading screens inside ST:O within the same building. Small building. An area that took 30-45 seconds to run across would have a loading screen to get to it.

There were lots of glitches, which I mainly account to server lag. I'm not sure that a shaky launch is going to do these guys any favors though when it comes to winning enough market share to be viable. The only thing that Age of Conan did right was get have stable servers at launch and through beta. ST:O's operations people really don't understand how to seed torrents, it took me about 2 days to download the client software through their system, and the torrent wasn't seeded at all before it went live. You know it's going to be fun when uTorrent says under "estimated time" 3y3d.

There were running glitches (run along, suddenly you rewind to 50 yards back down the hall), ship transition glitches (you beam up and you see your character running in space). Ship flight glitches (you'd rewind to about 50...uh...probably meters back sometimes). I'm going to attribute most of the glitchiness to the beta quality server software. That's fine, that's why you have betas. The gameplay mechanics, however, were just awful, and that's something you do in design, not in beta.

A pretty horrible game so far. I hope it gets better, but I'm not going to be buying it.

SQL Server 2005 SP3 install issue

Chris Miller — Tue, 29 Dec 2009 14:55:38 GMT

So I got this message:

MSP Error: 28017 SQL Server Setup cannot write to removable media. To continue, specify a directory on a hard disk attached to the computer where SQL Server will be installed.

And I'm installing to locally attached storage. After about 20 minutes of cursing, rebooting, and cursing some more, I went to My Computer and disconnected all my network drives. My logon script helpfully maps network drives, I'd forgotten about them since mapping drives sucks and I don't ever use them (UNC paths for life!). So I unmapped them and suddenly it worked.

Stupid bug, bad error message, and stupid logon script.

Installing SQL Server 2005 on a Windows 2008 R2 Cluster

Chris Miller — Mon, 28 Dec 2009 20:15:35 GMT

I just had an interesting issue with an installation I thought I'd share. I was installing SQL Server 2005 on a Windows Server 2008 R2 cluster. The cluster validates properly (use the cluster validation tool in 2008 R2, it will save you problems) and failed over properly prior to installation. When installing SQL Server 2005, I got through putting in all the information and when it came time to actaully install the software got a "User has cancelled the installation" error, and a notification to check the logs. The logs said:

Waiting for actions from remote setup(s)
Breaking wait state and aborting package due to cancel code received: 1602
Remote setup(s) are ready
Notify package action is determined: 1602
Error Code: 0x800700e9 (233)
Windows Error Text: No process is on the other end of the pipe.

Source File Name: remotemessagelib\overlappedpipelistener.cpp
Compiler Timestamp: Fri Jul 29 01:13:51 2005
Function Name: sqls::OverlappedPipeListener::writePipe
Source Line Number: 294

The installation was failing when it attempted to attach to the other cluster node. The resolution is:

1. Make sure the task scheduler service is running on the remote cluster node. If the task scheduler service is a cluster resource, fail that resource over to the remote node (the latter was not an issue on my side, I've read questions from people asking about it though).

2. Go into your registry (insert standard "don't ever edit the registry" disclaimer here) and go to HKLM/System/CurrentControlSet/Control/LSA and change the "disabledomaincreds" from 1 to 0 on EACH CLUSTER NODE and then reboot BOTH CLUSTER NODES.

Then the install worked like a champ.

Big thanks to MS Sql PSS for getting this fixed quickly.

Left-duplicate indexes

Chris Miller — Thu, 12 Nov 2009 17:05:41 GMT

At Kim Tripp's PASS session on indexing and covered indexes, she more-or-less proved that wider is better for indexes, within reason and searchability. It's better to have the index (Column1, Column2, Column3) than the index (Column1) and then a (Column1, Column2) and then a (Column1, Column2, Column3). And the first two of that set are redundant and don't provide any significant performance improvement over a wider index in most applications.

Finding cases where you have multiple indexes like that isn't a trivial problem. Here's the answer I came up with. This particular query finds cases of a (Column1, Column2....ColumnN) with a (Column1) but it could be pretty easily modified to do a multiple left side key required. It's all filtering, just add [3] and the appropriate group by/max and you're set. And once again, yes, you could do this without a temp table, but the temp table's kind of interesting on its own.

select oname, index_id, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

into #TableList

from (

select object_name(object_id) oname,

index_id, key_ordinal, Column_ID

from sys.index_columns) AS SourceTable

pivot(max(column_id)

for key_ordinal in([1], [2], [3], [4], [5],

[6], [7], [8], [9], [10], [11], [12],

[13], [14], [15], [16], [17], [18], [19])

) as PivotTable

select oname, [1], max([2]), count(*)

from #TableList

group by oname, [1]

having count(*) > 1

order by 1

Finding tables with no unique indexes

Chris Miller — Wed, 11 Nov 2009 20:30:13 GMT

Same vendor, different issue. How many tables in the database aren't really tables, but are actually just 2 dimensional data stores of unknown quality? Tables should have a unique index. Of some kind. If it's a formal PK, or a unique constraint or a unique index, it needs have *something* unique enforced, or it's not really a relational table, but is instead a pile of unknown quality data.

select so.name

from sys.objects so

left join sys.indexes si

on si.object_id = so.object_id

and si.is_unique = 1

where si.index_id is null

and so.type_desc = 'user_table'

order by 1

428 tables with no unique indexes. This goes with the 36 completely duplicated indexes and 50+ partially duplicated indexes.

And by the way, I checked. is_primary_key implies is_unique, and is_unique_constraint implies is_unique.

Finding duplicated indexes

Chris Miller — Wed, 11 Nov 2009 15:45:40 GMT

So I wanted to start being a bit more aggressive about index management. Specifically, I want to be able to find duplicated indexes inside of one of my vendor databases. So (1) I can make fun of them even more than I do already, and (2) so I can give them a script of all their issues that they might be able to resolve them.

Here’s what I came up with. Only really works if you have 19 or fewer columns per index. Why 19? I got tired of typing there. You can write this up as a code generator if you don’t like it. There are better ways to do this, but the way I’m doing it you can look at the temp table and use it to find the more subtle problem of a table with two indexes which are subsets of each other, which are usually but not always redundant. Anyway, here ya go.

drop table #tablelist

select oname, index_id, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

into #TableList

from (

select object_name(object_id) oname, index_id,

key_ordinal, Column_ID from sys.index_columns) AS SourceTable

pivot (max(key_ordinal)

for column_id in ([1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

)

) as PivotTable

select oname, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13],

[14], [15], [16], [17], [18], [19]

from #TableList

group by oname, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

having count(*) > 1

order by 1

How to specify a Windows Authentication user in T-SQL

Chris Miller — Tue, 18 Aug 2009 16:26:10 GMT

This may only be a SQL Server 2005 problem. Comments either way please.

Only one of these works:
grant execute on stored_proc to [domain1\ismom]
grant execute on stored_proc to 'domain1\ismom'
grant execute on stored_proc to '[domain1\ismom]'

Only one of these Execute As User works:

execute as user = 'domain1\ismom'
exec master..xp_sqlagent_enum_jobs 0, 'sys-admin'
revert

execute as user = '[domain1\ismom]'
exec master..xp_sqlagent_enum_jobs 0, 'sys-admin'
revert

execute as user = [domain1\ismom]
exec master..xp_sqlagent_enum_jobs 0, 'sys-admin'
revert

The execute as user doesn't give you a syntax error, by the way. Instead it goes all cryptic:
Msg 15404, Level 16, State 11, Line 1
Could not obtain information about Windows NT group/user '[domain1\ismom]', error code 0x534.

Some consistency here would be nice. Usable error messages are a plus also.

For grant execute, use the bare brackets [domain1\ismom]
For Execute As User, use the quotes without brackets: 'domain1\ismom'

Someone might not understand how CASE works....

Chris Miller — Tue, 05 May 2009 14:35:14 GMT

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,

What's the deal with SQL Server 2008 implementation?

Chris Miller — Fri, 17 Apr 2009 16:28:37 GMT

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.

Aggregate Functions

Chris Miller — Thu, 16 Apr 2009 16:25:15 GMT

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.

So, what do you call a Santa that can turn into a wolf?

Chris Miller — Tue, 07 Apr 2009 16:00:29 GMT

A Wereclaus.

Hyperbac Followup

Chris Miller — Mon, 23 Feb 2009 15:34:51 GMT

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).

My, isn't THAT intuitive.

Chris Miller — Mon, 23 Feb 2009 15:31:23 GMT

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.

SQL 2005 and Deadlocks

Chris Miller — Wed, 04 Feb 2009 16:42:21 GMT

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.

syscomments

COM+, DTC, and 80070422

Star Trek Online Open Beta, Thumbnail Review

SQL Server 2005 SP3 install issue

Installing SQL Server 2005 on a Windows 2008 R2 Cluster

Left-duplicate indexes

Finding tables with no unique indexes

Finding duplicated indexes

How to specify a Windows Authentication user in T-SQL

Someone might not understand how CASE works....

What's the deal with SQL Server 2008 implementation?

Aggregate Functions

So, what do you call a Santa that can turn into a wolf?

Hyperbac Followup

My, isn't *THAT* intuitive.

SQL 2005 and Deadlocks

My, isn't THAT intuitive.