Copying a SQL Server database

The following T-SQL sequence will make a copy of a database using the BACKUP and RESTORE commands:

backup database mydb to disk = 'c:\myfile.dat'

restore database mydbcopy from disk = 'c:\myfile.dat'
with move 'mydb_dat' to 'c:\my_dbcopy.mdf'
, move 'mydb_log' to 'c:\my_dbcopy.ldf'

The magic names referenced in the MOVE...TO clauses are logical filenames.  You can determine these by running sp_helpfiles in the source database (or querying the sysfiles table).  The following commands will do the same thing using file copies instead:

exec sp_detach_db @dbname='mydb', @skipchecks='true'

exec master..xp_cmdshell 'copy c:\mydb.mdf c:\mydbcopy.mdf'
exec master..xp_cmdshell 'copy c:\mydb_log.ldf c:\mydbcopy_log.ldf'

exec sp_attach_db @dbname='mydb', @filename1='c:\mydb.mdf', @filename2='c:\mydb_log.ldf'

exec sp_attach_db @dbname='mydbcopy', @filename1='c:\mydbcopy.mdf', @filename2='c:\mydbcopy_log.ldf'

This is slightly messier, but runs a bit faster (say, 40%).

---

SQL Query HTA

I created a simple HTA for executing SQL queries against a SQL Server instance.

---

Oracle and SQL Server Table Functions

For reference, here are examples of table-valued functions in both Oracle and SQL Server

Oracle:

create or replace type range_row_type as object(n number);

create or replace type range_table_type as table of range_row_type;

create or replace function number_range(startNumber in number, endNumber in number)
return range_table_type
pipelined
is
  result_row range_row_type := range_row_type(0);
begin
  for i in startNumber .. endNumber loop
    result_row.n := i;
    pipe row(result_row);
  end loop;
  return;
end;

select * from table(number_range(10,20));

SQL Server:

create function number_range (@start int, @end int)
returns @sequence table (n int)
as begin
  declare @i int
  set @i = @start
  while @i <= @end begin
    insert into @sequence(n) values(@i)
    set @i = @i + 1
  end
  return
end
go

select * from number_range(10,20)

---

Searching SQL Server Procedure Definitions

The SQL Server 2000 implementation of INFORMATION_SCHEMA.ROUTINES only contains the first 4000 characters of the procedure definition (apparently this is fixed in SQL Server 2005).  This makes it difficult to search for strings in the procedure text.  If you search in sysobjects and syscomments instead, there will be multiple rows per procedure called, one for every 4000 characters of the procedure definition.  At least this makes the text accessible.  Here is an SQL statement that will search the definitions of functions, procedures, views, and triggers for a string (provided the string is no more than 50 characters long):

select so.name
from sysobjects so
inner join syscomments as sc on (sc.id=so.id)
left join syscomments as overlap on (overlap.id=so.id and overlap.colid=sc.colid+1)
where so.type in ('FN','IF','P','TF','TR','V')
and sc.text+coalesce(substring(overlap.text,1,50),'') like '%mystring%'

Note the neat trick of self-joining syscomments to the next row in sequence to give 50 characters worth of overlap in case the search string spans rows (adjust the 50 to taste).

This is way easier than some of the expert advice about this topic on the 'Net.

Eclipse/OSGI vs. JUnit

It is standard practice to place unit test source code in a directory tree distinct from the "real" source.  In those rare cases when you want to test package-private features, the standard trick is to put the unit test class in the same package as the class under test (albeit in a different directory).  This scheme works well in "raw" Java.

The trick also works in Eclipse using a JUnit Test launch configuration, even if the unit tests are in a separate plug-in (another standard practice).  However, if you use a JUnit Plug-in Test launch configuration instead, you will get an IllegalAccessException.  Apparently, OSGI prevents one plug-in from contributing classes to a package defined in another plug-in.  Thus, under OSGI, packages are effectively sealed as seen from other plug-ins.

---

JExcelAPI

JExcelAPI is a pure Java library for reading and writing Excel files.

---