SQL Server 2k5

The latest release of MS SQL Server has been released to manufacturing today and is available for download for Technet subscribers:
http://technet.microsoft.com/

Currently the download throughput is only around 80 Kbps, so it'll take about a full day to download.

July 27th, 2007 (Last Friday Of July)
8th Annual
System Administrator Appreciation Day

If you can read this, thank your sysadmin


http://www.sysadminday.com/

yes, you can successfully restore a database backup created on SQL Server 7 to a SQL Server 2005 database server, however, there a couple of things to remember when you do it:

1. The database will restore in Compatibilty mode 7 (you can tell by right-clicking the db, then selecting properties and going to options > Compatibility level) - this means a a couple of things:


A. if your application or the stored procs use the old ∗= syntax for left/right joins your queries will continue to work - that's good
B. you cannot use right click on the database, select Tasks > generate scripts - No database in compatibility mode will appear in the list of databases to script. that's bad - although you could probably still use ErWin to reverse engineer the database into SQL scripts.

So the answer is that you really should change the compatibility mode and fix the legacy syntax in your code (though I personally liked the old syntax better!). What that means is changing queries that may have looked like this:

select deptpermissions.departmentid, block.blockcode, block.blockname from deptpermissions, block
where block.blockcode ∗= deptpermissions.blockcode
and departmentid = &departmentid;
order by block.blockname, deptpermissions.departmentid



to look instead like this:

select deptpermissions.departmentid, block.blockcode, block.blockname from block
LEFT JOIN deptpermissions
ON block.blockcode = deptpermissions.blockcode
where departmentid = &departmentid;
order by block.blockname, deptpermissions.departmentid


Today we had an interesting problem:
Create an SSIS dtsx package that gets a file from an FTP server, puts it in a local directory, then inserts the data from the file into a table. Seems pretty easy, right? Well, the only hard part was getting the regular expression built to get the correct file from the server. The files are a daily data feed feed called "filename0419" or filnamemmdd.

We got to the point of setting the variable using this pretty quickly:

"filename" + (DT_WSTR,30)MONTH(GETDATE()) + (DT_WSTR,30)DAY(GETDATE())

The problem here is that it was returning filename419 - (and would return filename11 if it happened to be Jan. 1) we needed to pad out the dates being returned by the GETDATE() function.

Here is the solution brought to you by SpireMedia top notch developer Tina:

"filename" + SUBSTRING( (DT_WSTR,30)GETDATE() , 6, 2 ) + SUBSTRING( (DT_WSTR,30)GETDATE() , 9, 2 )

this regex returns what we need: filename0419 (and if it's Jan 1, filename0101)