The Ji Village News

季庄新闻–Haidong Ji’s Blog

The script below is available in MSDN. I used it a few months ago but it took me some time to find it again, so I decided to put it here for my own reference.

Purpose: find out information on distributor and publisher databases, articles, and publications in Sql Server replication.

–********** Execute at the Distributor in the master database **********–

use master
go

–Is the current server a Distributor?
–Is the distribution database installed?
–Are there other Publishers using this Distributor?
exec sp_get_distributor

–Is the current server a Distributor?
select is_distributor from sys.servers where name=’repl_distributor’ and data_source=@@servername

–Which databases on the Distributor are distribution databases?
select name from sys.databases where is_distributor = 1

–What are the Distributor and distribution database properties?
exec sp_helpdistributor
exec sp_helpdistributiondb
exec sp_helpdistpublisher

–********** Execute at the Publisher in the master database **********–

–Which databases are published for replication and what type of replication?
exec sp_helpreplicationdboption

–Which databases are published using snapshot replication or transactional replication?
select name as tran_published_db from sys.databases where is_published = 1
–Which databases are published using merge replication?
select name as merge_published_db from sys.databases where is_merge_published = 1

–What are the properties for Subscribers that subscribe to publications at this Publisher?
exec sp_helpsubscriberinfo

–********** Execute at the Publisher in the publication database **********–

use AdventureWorks
go

–What are the snapshot and transactional publications in this database?
exec sp_helppublication
–What are the articles in snapshot and transactional publications in this database?
–REMOVE COMMENTS FROM NEXT LINE AND REPLACE
with the name of a publication
–exec sp_helparticle @publication=’ ‘

–What are the merge publications in this database?
exec sp_helpmergepublication
–What are the articles in merge publications in this database?
exec sp_helpmergearticle — to return information on articles for a single publication, specify @publication=’ ‘

–Which objects in the database are published?
select name as published_object, schema_id, is_published as is_tran_published, is_merge_published, is_schema_published
from sys.tables where is_published = 1 or is_merge_published = 1 or is_schema_published = 1
union
select name as published_object, schema_id, 0, 0, is_schema_published
from sys.procedures where is_schema_published = 1
union
select name as published_object, schema_id, 0, 0, is_schema_published
from sys.views where is_schema_published = 1

–Which columns are published in snapshot or transactional publications in this database?
select object_name(object_id) as tran_published_table, name as published_column from sys.columns where is_replicated = 1

–Which columns are published in merge publications in this database?
select object_name(object_id) as merge_published_table, name as published_column from sys.columns where is_merge_published = 1

#I’ve worked on and off with Perl, but really want to be more proficient with it. I will do more automation with Perl and will share it here for my reader and as a reference for myself. I just created a Perl tag on this blog.
#Here is one script I cooked out today. It takes a directory / folder name as input, and return the latest or newest modified file. Suggestions welcome!

#use File::Copy;
use File::stat;

$dirname = shift or die “Please provide a directory to search for”;
$timediff=0;

opendir DIR, “$dirname”;

while (defined ($file = readdir(DIR)))
{
if($file ne “.” && $file ne “..”)
{
$diff = time()-stat(”$dirname/$file”)->mtime;
if($timediff == 0)
{
$timediff=$diff;
$newest=$file;
}
if($diff<$timediff)
{
$timediff=$diff;
$newest=$file;
}
}
}

print $newest,”\n”;

#copy(”$dirname/$newest”, “c:/work/$newest”);

Version: Ubuntu 7.10, the Gutsy Gibbon released in Oct. 2007. I run it on VMWare Player.

Assumption: Ubuntu is connected to the web.

Do msn (Windows Live) chat on Ubuntu:

1. Application -> Add/Remove…

2. Click Internet tab on the left pane, then pick aMSN for install. As this writing, it has 4 stars under Popularity column.

3. Launch and enjoy.

By the way, so far I almost never do msn chat, but that can change if/when necessary. Once in a while, I do Skype chat.

Install and set up Chinese entry on Ubuntu, with a hat off to Nathan Sivin for his instruction. I followed his steps and just rehashed them here:

1. System -> Administration -> Language Support. Enter password as needed;

2. In Language scroll window, pick Chinese;

3. Click Apply button;

4. Check “Enable support to enter complex characters”;

5. Click Apply button. When done, close the window;

6. Open a shell, type:

locale | grep LANG=

Record the result because you will need it later. For US based Ubuntu, most likely it will be en_US.UTF-8;

7. Type:

sudo apt-get install scim-qtimm im-switch scim-pinyin

Enter password as necessary;

8. When done, type:

im-switch -z en_US.UTF-8 -s scim

Replace en_US.UTF-8 with whatever you get from step 6;

9. Reboot. For Chinese entry, press

Ctrl and Space bar

You should see a language bar in the lower right-hand corner. On the language bar, click the button immediately to the right of SCIM icon, and pick your Chinese entry methods. Press

Shift

to toggle between English (or whatever your default language) and Chinese. Press

Ctrl and Space bar

again to get rid of the language bar.

Hope this helps you. 希望这可以帮助你。

use msdb
go
select name, suser_sname(sysjobs.owner_sid) as owner from msdb..sysjobs
go

select sj.name,sjh.run_status,case sjh.run_status
when 1 then ‘Sucess’
when 2 then ‘Retry’
when 3 then ‘Cancelled’
when 4 then ‘In Progress’
when 0 then ‘Failed’
End
as ‘Run Status’,sjh.run_date as ‘Run Date’,
CASE len(run_time)
WHEN 1 THEN cast(’00:00:0′
+ cast(run_time as char) as char (8))
WHEN 2 THEN cast(’00:00:’
+ cast(run_time as char) as char (8))
WHEN 3 THEN cast(’00:0′
+ Left(right(run_time,3),1)
+’:’ + right(run_time,2) as char (8))
WHEN 4 THEN cast(’00:’
+ Left(right(run_time,4),2)
+’:’ + right(run_time,2) as char (8))
WHEN 5 THEN cast(’0′
+ Left(right(run_time,5),1)
+’:’ + Left(right(run_time,4),2)
+’:’ + right(run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(run_time,6),2)
+’:’ + Left(right(run_time,4),2)
+’:’ + right(run_time,2) as char (8))
END as ‘End Time’,
CASE len(run_duration)
WHEN 1 THEN cast(’00:00:0′
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast(’00:00:’
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast(’00:0′
+ Left(right(run_duration,3),1)
+’:’ + right(run_duration,2) as char (8))
WHEN 4 THEN cast(’00:’
+ Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
WHEN 5 THEN cast(’0′
+ Left(right(run_duration,5),1)
+’:’ + Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+’:’ + Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
END as ‘Duration’ from msdb..sysjobs sj
join sysjobhistory sjh on sj.job_id=sjh.job_id
where name like ‘NameString%’
and sjh.run_date>=’yyyymmdd’
order by run_duration

Script below. This one works better for Sql Server 2000. I will do a version for Sql Server 2005, when I get a chance. It didn’t work very well on Sql Server 2005 because Ad Hoc Distributed Queries is disabled by default.

set nocount on
IF OBJECT_ID(’tempdb..#TmpJobs’) IS NOT NULL
DROP TABLE #TmpJobs

SELECT *
INTO #TmpJobs
FROM OPENROWSET(’sqloledb’
, ’server=(local);trusted_connection=yes’
, ’set fmtonly off exec msdb.dbo.sp_help_job @enabled = 1′)

select ‘Successful run’

SELECT cast(name as varchar(59)),
LastRunDatetime = CASE last_run_date
WHEN 0 THEN ‘Never’
ELSE
CONVERT(varchar(10), CONVERT(smalldatetime, CAST(last_run_date as varchar), 120), 120)+’ ‘+
RIGHT(’0′+CAST((last_run_time/10000) AS VARCHAR), 2)+’:'+
RIGHT(’0′+CAST((last_run_time-((last_run_time/10000)*10000))/100 AS VARCHAR), 2)+’:'+
RIGHT(’0′+CAST((last_run_time-((last_run_time/10000)*10000)-((last_run_time-((last_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
END
FROM #TmpJobs where last_run_outcome = 1

select ‘Failed run’

SELECT cast(name as varchar(59)),
LastRunDatetime = CASE last_run_date
WHEN 0 THEN ‘Never’
ELSE
CONVERT(varchar(10), CONVERT(smalldatetime, CAST(last_run_date as varchar), 120), 120)+’ ‘+
RIGHT(’0′+CAST((last_run_time/10000) AS VARCHAR), 2)+’:'+
RIGHT(’0′+CAST((last_run_time-((last_run_time/10000)*10000))/100 AS VARCHAR), 2)+’:'+
RIGHT(’0′+CAST((last_run_time-((last_run_time/10000)*10000)-((last_run_time-((last_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
END
FROM #TmpJobs where last_run_outcome = 0

This is mainly for my own reference. Save it as ListSysadmin.pl. Run ListSysadmin.pl SqlServerList.txt on command line. It lists all logins that has sa (sysadmin) rights on Sql Server instance, including individual domain members in a domain group that has sysadmin rights.

SqlServerList.txt should be a text file, with each individual line contains a Sql Server instance.

This script is an improvement over Linchi’s original scrip from his Real World SQL Server Administration with Perl. The original script only takes one instance as input parameter. Note: with my current site style sheet on my web site, I think it ignores the angle brackets, so you need to fix the script when you do copy and paste.

use strict;
use Win32::ODBC;

# Import the function from the module SQLDBA::Utility
use SQLDBA::Utility qw( dbaRemoveDuplicates );

# Import functions from the module Win32::NetAdmin
use Win32::NetAdmin qw( GetDomainController
GroupGetMembers
LocalGroupGetMembersWithDomain
UsersExist );

Main: {
my $log = shift or die “***Err: $0 expects a file name.\n”;
open(LOG, “$log”) or die “***Err: couldn’t open $log.\n”;
while () {
my $line = $_;
if ($line =~ /\[?(\w+)/ix) {
my $serverConn = $1;

print “Instance: $1\n”;

# get all the accounts/logins with sysadmin role from SQL Server
my $loginRef = getSQLSysadmins($serverConn);
# in case the server name is followed by an instance name
my ($server) = $serverConn =~ /([^\\]+)(\\.+)?/;

# replace BUILTIN with the server name
foreach (@{$loginRef->{groupSet}}) {
s/BUILTIN\\/uc($server) . “\\”/e; # evaluate the right side before replace
}

# remove all local groups and get their members
$loginRef = removeLocalGroups($loginRef);

# get global group members
$loginRef = getGlobalGroupMembers($loginRef);

# now print out all the NT/SQL logins that are SQL sysadmin
print “Listing all NT/SQL logins with the sysadmin rights …\n”;
foreach (sort @{$loginRef->{resultSet}}) { print “\t$_\n”; }
}
}
close(LOG);

} # Main

#########################
sub getSQLSysadmins {
my $serverConn = shift or
die “***Err: getSQLSysadmins() expects a server.”;

my $connStr = “Driver={SQL Server};Server=$serverConn;” .
“Trusted_Connection=Yes;Database=master”;
my $conn = new Win32::ODBC ($connStr) or
die “***Err: Can’t connect to $serverConn. ” . Win32::ODBC::Error();

# get sysadmin logins (NT and SQL)
my @logins = ();
my $sql = q/SELECT loginname FROM master.dbo.syslogins
WHERE sysadmin = 1
AND isntgroup = 0/;

unless ($conn->Sql( $sql )) {
@logins = (@logins, $conn->Data) while ($conn->FetchRow());
} else {
print “***Err: executing $sql. “, Win32::ODBC::Error(), “\n”;
}

# get sysadmin nt groups
my @groups = ();
my $sql = q/select loginname from master.dbo.syslogins
where sysadmin = 1
and isntgroup = 1/;

unless ($conn->Sql( $sql )) {
@groups = (@groups, $conn->Data) while ($conn->FetchRow());
} else {
print “***Err: executing $sql. “, Win32::ODBC::Error(), “\n”;
}
$conn->Close();

return { resultSet => \@logins,
groupSet => \@groups,
userSet => undef };
} # getSQLSysadmins

###########################
sub removeLocalGroups {
my $loginRef = shift or
die “***Err: removeLocalGroups() expects a reference.”;

foreach my $group (@{$loginRef->{groupSet}}) {
if (my $rc = getLocalGroupMembers($group)) {
push @{$loginRef->{userSet}}, @{$rc};
}
else {
push @{$loginRef->{userSet}}, $group;
}
}
delete $loginRef->{groupSet};
return $loginRef;
} # removeLocalGroups

##############################
sub getGlobalGroupMembers {
my $loginRef = shift or
die “***Err: getGlobalGroupMembers() expects a reference.\n”;
my @logins = ();

foreach my $user (@{$loginRef->{userSet}}) {
if ($user =~ /^NT\s+AUTHORITY\\/) {
push @logins, $user;
next;
}

my ($domain, $name) = $user =~ /^(.+?)\\(.+)$/;
if ( UsersExist($domain, $name ) ) { # a user account
push @logins, $user;
}
else {
my $pdc;
my @users;
if (GetDomainController(undef, $domain, $pdc)) {
$pdc =~ s/\\//g;

if ( UsersExist($pdc, $name ) ) {
push @logins, $user;
}
else { # $name is a global group account
# get users in the global group
if (GroupGetMembers($pdc, $name, \@users)) {
# prefix the users with the domain name
push @logins, map { “$domain\\$_”; } @users;
}
else { # you shouldn’t reach here
print “***Err: can’t retrieve users for $domain\\$name.\n”;
next;
}
}
}
else {
print “***Err: can’t find domain controller for $domain.\n”;
next;
}
}
}

push @logins, @{$loginRef->{resultSet}};
$loginRef->{resultSet} = dbaRemoveDuplicates(\@logins);
return $loginRef;
} # getGlobalGroupMembers

################################
sub getLocalGroupMembers {
my $group = shift or
die “***Err: getLocalGroupMember() expects a group name.”;

my ($domain, $group_name) = $group =~ /^(.+?)\\(.+)$/ or
die “***Err: $group must be qualified with a domain/machine name.”;

my @users = ();
if (LocalGroupGetMembersWithDomain($domain, $group_name, \@users)) {
return \@users;
}
else {
return undef;
}
} # getLocalGroupMembers

One of the first things you want to do as a DBA is to check when was the last time a full backup was taken for all databases you manage. For Sql Server, here is a script you can run:

set nocount on
select cast(a.name as char(30)), max(b.backup_finish_date) from master..sysdatabases a left outer join msdb..backupset b on a.name = b.database_name
where a.name not in (’model’, ‘tempdb’, ‘pubs’, ‘northwind’, ‘adventureworks’)
group by a.name

Suppose we have a list of instances we manage, I cannot emphasize enough how convenient it is to be able to blast the above statement to all instances we manage. That way we get all full backup information on all instances in one fell swoop. Fortunately, Linchi Shea wrote a couple of utilities just for that: one in C#, one in Perl. Check ‘em out here.

It was a clear sky in LA, but looking down, the city seemed to be covered in smog. After working on Sql Server 2008 material in LAX airport for a while, I boarded a turbo-prop for San Jose. Landing time was windy, so it was a bit bumpy. The only flight attendant, Cindy K, joked that “United throw in a few rides free of charge”. She was helpful with my luggage, with an easy smile.

So I checked in Radisson Hotel around San Jose airport afterwards. I booked the hotel through HotWire. It was a sweet deal, around 60 dollars per night. HotWire is a great site, especially for hotels. I’ve used it three times, all pretty satisfied.

Google map is awesome. It even provided me with information on how to get to Santa Clara convention center via public transportation. So I walked to the Gish station, saw Mexican, Moroccan, Japanese, and Chinese restaurants along the way, and got to the Santa Clara convention center in a jiffy. This is the first time I stepped on the VTA, and I was impressed: it has bike racks. I also saw signs in English, Spanish, Chinese, and Vietnamese, and people seemingly from all parts of the world. Now that is the America I love!

Oh, I am here for the MySQL conference. Unlike Sql Server conferences I attended in the past, I don’t know many people in this community, so my objectives are to talk to and learn from people, schmooze, and network, and score as many free food and drinks as I can. So don’t disappoint me, MySQL!

I will take MySQL certification exams, therefore I am going through MySQL 5.0 Certification Study Guide. It is a pretty cool book in that it goes to the point right away without many wasted words. Too many technical books dance around the topic, fill the volume with screen shots, but do not deliver the goods in the end. I am reading the Safari online version. With so many pages, I can see the physical book can be bulky.

Anyway, some comments and observations:

1. Sybase, MySQL, and Sql Server’s definition and implementation of database and schema are somewhat close. I dislike Microsoft’s definition and usage of schema, especially in Sql Server 2005. It is very confusing to new or DBAs from other platforms.

Sybase, MySQL, and Sql Server all support the USE Database statement;

2. I like mysql command line tool and how the result set is displayed. It looks clean and neat to me, although, like command line tools from other database platforms, when the result set gets wider, it gets messy;

3. Similar to Sql Server, MySQL also has the system and status variables that starts with @@. And, like Sql Server, you can just do SELECT @@Variable to retrieve its value, for example, SELECT @@sql_mode;

4. Played with MySQL Query Browser a little bit. Ctrl - E is the keyboard shortcut for statement execution, the same as Sql Server. However, F5 does not execute the statement in MySQL Query Browser.

I’ve been using Jon’s excellent tools, ViEmu for Visual Studio, Outlook, Word, and Sql Server Management Studio for close to a year now. They make the default editor in those tools behaves like VI, with regular expression support. I bought the Enterprise Pack. Highly, highly recommended. Of course you need to know VI first.

I had some issues with ViEmu for Sql Server Management Studio on Sql Server 2008 CTP 6. So I wrote Jon. He got back to me very quickly. It turned out that the version I installed was an older one. I downloaded the newer one and it worked out of the box.

So I am a VI / Vim aficionado. In fact, I consider learning VI to be the second best investment I ever made so far in my life. The best ever happened about 5 years ago, when I finally taught myself touch typing. (I didn’t see or touch a computer and typewriter until my early twenties.) I tried to teach myself touch typing maybe ten years ago, using a high school typing book I borrowed from the local library here. That didn’t work out very well. Five years ago, I purchased a touch typing software off the web, maybe 25 dollars or so, and finally became a decent touch typist. Now I kick myself for not learning touch typing earlier.

After that, typing is not a hindrance anymore. Using Vim actually makes it enjoyable. It has made a huge difference in my life.

There is so much to learn in Vim. So far these are my most-used commands:

w
b
)
(
G
gg
cw and cNumberw
cfSomeAlphaNumericCharacter
ctSomeAlphaNumericCharacter
dw and dNumberw
C
cG
dd
.
yy
p
P
fSomeAlphaNumericCharacter
;
*
i
I
a
A
o
O
/
%
:g/^$/d to remove blank lines
Macros (q, then a letter, followed by actions that you want repeated later. For example, suppose I have a list of tables that need to be truncated, I will go to the first line, start macro recording, press I truncate table, Esc, j, then q to stop recording. For the rest of the lines, I just do Number@MacroName)

That’s all I can think of now. According to Vim Tips Wiki site, this collection of tips is the best. I found that most of the tips need to be followed consciously for a while before they become part of you. What are yours you can share with me?