Sybase ASE vs SQL Server. Hajime!

Linchi Shea recently drew a couple of comparisons between Sybase ASE and SQL Server here. It's been a while since I worked with Sybase, but there were some features that it had that I wish SQL server had.

Fortune cookies are usually junk, but...

Fortune cookie fortunes are more platitude than anything these days. I'm usually put off by them. I usually want something along the lines of "you will be rewarded for your recent hard work" or "they really are out to get you". So I was pleasantly surprised when I got the following:

Those who endure most are rewarded most.

True enough (in bed).

Create a database snapshot

I've been meaning to write this one for a while. The need arose today to be able to create a database snapshot fairly quickly. Here's what I came up with:

Relog.exe for perfmon slicing and dicing and PAL for reporting

So I'm going through a "how do we make the application better" exercise here at work. We ran a load test last night to see how the system would hold up. I always have perfmon counters running, so I wanted to see how they did during the load test window. Enter relog.

Validating one article at one subscriber

Here's a quick script before I nod off:

use [publisher_db]
go
begin tran
go
exec sp_marksubscriptionvalidation 
    @publication = N'publication_name,
    @subscriber = 'subscriber_server',
    @destination_db = 'subscriber_db'
exec sp_article_validation
    @publication = N'publication_name',
    @article = N'article_name',
    @rowcount_only = 2,
    @full_or_fast = 2,
    @shutdown_agent = 0
go
commit

I don't think this is exposed through the GUI at all.

Finding last backups with Powershell

Last weekend, I did a change control that was in the middle of when we normally do our full backups. As a result, some of the databases didn't get backed up via the normal process. Our data center guys gave me the option of just running the whole thing again, but that would have backed up already backed up databases and given that our retention policy is based on time (and not by number), the backup drive isn't sized to accommodate that. I was also without my script repository (since moved to Dropbox so that won't happen again), so I needed a quick way to determine which databases needed to be backed up. Enter powershell:

Getting the TCP port of all instances on a machine

So... we run multiple SQL instances per physical server. There also seems to be a firewall issue between my laptop and the environment that prevents port resolution for those instances. So, I decided to whip up a little Powershell script.

Get me some articles!

So, as I alluded to here, I was doing some work today. Specifically, I was adding articles to a publication. I had an idea that I should be able to come up with a way to add the articles that are missing from the publication. That is, if I have a database with 20 tables and 17 are already articles, then the script should be smart enough to add the 3 that aren't.

---

declare tablez cursor for
    select schema_name([schema_id]), t.name 
    from sys.tables t
    left join sysarticles a
       on t.[object_id] = a.[objid] 
    where OBJECTPROPERTY([object_id], 'IsMSShipped') = 0  --no system objects
       and OBJECTPROPERTY([object_id], 'TableHasPrimaryKey') = 1 --only give me tables that can be replicated, please
       and a.[objid] is null --not already an article
open tablez
declare @schema sysname, @table sysname, @fullname sysname
while (1=1)
begin
    fetch next from tablez into @schema, @table
    if (@@FETCH_STATUS <> 0)
        break
    set @fullname = @schema + '.' + @table
    exec sp_addarticle
    @publication = 'Publication',
        @article = @fullname,
        @source_object = @table,
        @destination_table = @table,
        @type = 'logbased',
        --@schema_option
        @destination_owner = @schema,
        @status = 16, 
        @source_owner = @schema

end
close tablez
deallocate tablez

---

The astute among you might wonder why I named the cursor "tablez". I'll present you with two options.

1. "tables" highlights as a system table in SSMS (a la sys.tables).
2. I'm l33t.

I leave it to the reader to choose. As always, the standard warning applies.

Small cursor usage trick

Okay... everyone knows that cursors are bad. And yet we all use them from time to time. Historically I've written them like this:

declare @table sysname 
declare curs cursor for
select name from sys.tables
fetch next from curs into @table

while (@@fetch_stats = 0)
begin
  --do stuff
  fetch next from curs into @table
end

close curs
deallocate curs

While writing some code today, I had this insight:

declare @table sysname 
declare curs cursor for
select name from sys.tables

open curs

while (1=1)
begin
  fetch next from curs into @table
  if (@@fetch_status <> 0)
    break
  --do stuff
end
close curs
deallocate curs

With the advantage that I only have to maintain one fetch line as I'm inevitably adding and removing columns from the cursor. I'm sure that I'm not the first to come up with this, but hey... I thought it was nice.

If you never hear that something's impossible...

So maybe it's a little early to go off topic, but I thought this was an awesome story.  My take away is that Dantzig didn't hear that the problems were unsolved and was thus not dissuaded from working on them.  As a result, he solved them.  So don't let people tell you what is possible and not; you may just surprise them.

Scripting your database for fun and profit

Inspired by a post by Andy Leonard here, I remembered that I'd been meaning to post a Powershell script that I wrote a while back.  What better time than now for an inaugural post!

No warranty, express or implied

So... I'm planning on using this space as a place to talk about things that I find interesting about MS SQL.  From time to time, that will involve code/scripts.  Right out of the gate, I want to lay the following ground rules: