Blackberry Google Tools

I have a Blackberry through my work. Google has put out a bunch of great tools for the Blackberry, including really effective versions of Google Maps and GMail. You can download all of the Google Blackberry tools at the same time and keep them up to date from this URL: http://www.google.mobi When you surf to this URL on your Blackberry, it will do over-the-air installations of all of the tools that you choose. My favorite is the Google Maps application, which does a lot of things that the web version won't do. I was able to surf right to Plaza Catalunya in Barcelona and actually see people-- that's the level of detail it has.

SQL Server: How to concatenate field text across rows

Today I needed to concatenate text in a fields across rows. Specifically, given a table with columns for user accounts and quotas, report summing the user accounts, a list of the quota labels, and the summed quota amounts.

What you would want is something like this (But this won't work!):

-- WARNING: THIS WON'T WORK!
select user_accountID, concat(quotaNo), sum(quotaAmount)
  from user_accounts
 group by user_accountID

Alas, in SQL Server 2000, there isn't a built-in function to do this. About the only way I know of to do this is to custom build a user-defined function for the particular table you are working with. Here is my example:

-- Create a test table
create table user_accounts (user_accountID varchar(32), quotaNo varchar(32), quotaAmount float)

insert into user_accounts values('user_account1', 'quota1', 100)
insert into user_accounts values('user_account1', 'quota2', 200)
insert into user_accounts values('user_account1', 'quota3', 400)

insert into user_accounts values('user_account2', 'quota4', 800)
insert into user_accounts values('user_account2', 'quota5', 1600)

go

-- Create the user defined function
create function concat_quota_number(@user_account varchar(20))
returns varchar(200) as
begin
  declare @str varchar(200)
  select @str = coalesce(@str + ', ', '') + quotaNo from user_accounts where user_accountID = @user_account group by quotaNo
  return @str
end

go

-- How to use it
select user_accountID, dbo.concat_quota_number(user_accountID) quota_labels, sum(quotaAmount) quota_sum
from user_accounts
group by user_accountID, dbo.concat_quota_number(user_accountID)

user_accountID	quota_labels	quota_sum
user_account1	quota1, quota2, quota3	700.0
user_account2	quota4, quota5	2400.0

Adobe 8.0 installation error: how to beat it

This morning I decided I was going to upgrade to Adobe Acrobat Reader 8.0. I tried to do the installation directly from the Adobe web page (http://www.acrobat.com), but I continuously got this error message:

You're resuming a download, but the file on the server has changed. Would you like to restart the download process?

After about five tries at this I got annoyed. The simple solution is this (as long as you are on a Microsoft Windows machine: Download the file directly from Adobe's FTP site:

1. In explorer, open ftp://ftp.adobe.com/pub/adobe/reader/win/8.x/8.0/enu/


2. Download "AdbeRdr80_en_US.exe" by Right-clicking it and choosing "Save As..."


3. Run the resulting file

I don't know what caused this error, but this simple solution gets rid of it. If you are on a different operating system, you can still go to the FTP site, but you'll have to fish around a little to find your file.

SQL Server: How to do old and new style left joins

I like using the newer ANSI-92 standard JOIN statements, and I believe that once you get used to them, they are easier to understand. They certainly are easier to read, because they separate the function of joining tables from the filtering taking place in the where clause.

That said, I get SELECT statements out of applications sometimes by running a trace, just to see what is going on in an application. A lot of times, these SQL statements don't use the new standards. You can't use old style left joins with the new ANSI-92 standard.

So this code shows how to use both styles. I create a couple of temporary tables for this example, one called MASTER and the other SLAVE. Our objective is to see all rows of MASTER, no matter whether there are matching rows in SLAVE:

-- Create and populate our temporary tables
create table #master (id int)
create table #slave (id int, descrip varchar(32))

insert into #master values (1)
insert into #master values (2)
insert into #master values (3)

-- Note that we don't put a description for 2
insert into #slave values (1, 'one')
insert into #slave values (3, 'three')

-- old left join-- the * goes on the "MASTER" side of the equal sign
select m.id, s.descrip
  from #master m, #slave s
 where m.id *= s.id

id	descrip
1	one
2	NULL
3	three

-- ANSI-92 left join
select m.id, s.descrip
  from #master m
  left join #slave s on s.id = m.id

id	descrip
1	one
2	NULL
3	three

-- Drop our temporary tables
drop table #master
drop table #slave

These two SELECT statements produce identical output. I won't say that these two JOIN styles are identical-- there are examples of how they differ out there somewhere. But if you're stuck using the old style, it helps to know the syntax.

SQL Server: Restoring from someone else's backup device

This morning I received a SQL Server backup file from someone else. I was to restore the backup file to a new database. Before restoring it, I decided to verify it to make sure everything was okay:

1> restore filelistonly
2>    from disk='C:\datanewdb.bak'
3> go
Msg 3201, Level 16, State 2, Server SQLDB, Line 1
Cannot open backup device 'C:\datanewdb.bak'. Device error or device off-line. See the SQL Server error
log for more details.
Msg 3013, Level 16, State 1, Server SQLDB, Line 1
RESTORE FILELIST is terminating abnormally.

That was frustrating. But I noticed that the error said the device was off-line. That's a different error from what you get when the file is just corrupt.

I figured out that I had been sent the file from a backup device, not a straight backup file. I've thought for a long time now that the naming convention should be different for backup devices-- maybe .BKD instead of .BAK.

In any case, what I needed to do was to create a new backup device from the backup device file, then restore to a new database. Here are the steps I took which are suggested as a program of recovery (nudge nudge wink wink):

1. I created the backup device using the third-party's file:

1> exec sp_addumpdevice @devtype='disk',
2>                      @logicalname='newdb',
3>                      @physicalname='c:\datanewdb.bak'
4> go
(1 row affected)
'Disk' device added.

2. I verified the backup device:

1> restore verifyonly
2>    from newdb
3> go
The backup set is valid.

3. I got the names of the files in the database backup set (I'll need these later when I restore):

1> restore filelistonly
2>    from newdb

olddb	D:\Microsoft Sql Server\MSSQL\Data\olddb.mdf	D	PRIMARY	2088828928	15728640000
olddb_log	D:\Microsoft Sql Server\MSSQL\Data\olddb_log.ldf	L	NULL	1422065664	5242880000

4. I restored the database:

1> restore database newdb
2>   from newdb
3>   with move 'olddb' to 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\newdb.mdf',
4>        move 'olddb_log' to 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\newdb_log.ldf'

Processed 167088 pages for database 'newdb', file 'olddb' on file 1.
Processed 1 pages for database 'newdb', file 'olddb_log' on file 1.
RESTORE DATABASE successfully processed 167089 pages in 339.970 seconds (4.026 MB/sec).

Note that when I restore the database, I have to specify some MOVE clauses to move the physical data and log files from where the third party stored them on their old system to where I want them on my system.