April 2007 Entries
The Problem:
I find myself quite often in the following situation: I add a couple of tables in a development database, add some data in that table and then I need to script the changes and apply them to the staging and then to the production db.
If you dont create everything by script SQL Server Management Studio can script the Create Table for us, but there is no way to script the data! For this reason I created a stored procedure, which yeah you guessed it, creates all the INSERT statements containing the data of the table.
The Solution:
The following script works on SQL 2005 since it makes use of the coalesce function (sql 2000 fix follows script)
Here you go:
CREATE Proc dbo.ScriptInsertStatements(
@TableName varchar(128),
@WhereClause varchar(400) = Null
)
AS
DECLARE @sql varchar(Max)
DECLARE @sqlColumns varchar(Max)
DECLARE @SqlColumnValues varchar(Max)
SELECT @sqlColumns = Coalesce(@SqlColumns + ',','') + Column_Name from Information_Schema.Columns where Table_name = @TableName
SELECT @SqlColumnValues = Coalesce(@SqlColumnValues + '+ '','' + ','') +
CASE
WHEN Data_Type in ('varchar','nvarchar', 'char', 'nchar', 'datetime' ) THEN 'QuoteName(' + Column_Name + ', '''''''')'
ELSE 'Cast(' + Column_Name + ' as varchar(MAX))'
END
FROM
Information_Schema.Columns where Table_name = @TableName
SELECT @sql = 'Select ''Insert Into ' + @TableName + ' (' + @SqlColumns + ') Values ('' + ' + @SqlColumnValues +' + '')'' FROM ' + @TableName + IsNull(' WHERE ' + @WhereClause, '')
Exec(@sql)
To use the above stored proc simply call it passing the Table Name
e.g.
Exec dbo.ScriptInsertStatements 'Users'
You may also want to partially script a table based on a condition. You can supply that condition in the whereclause like so:
--only script insert statements of Users Table for records with field 'Active' set to 1
Exec dbo.ScriptInsertStatements 'Users', 'Active=1'
I have recently finished reading Joel on Software and thought id share what I think of this book.
What I liked about this book:
Writing in a casual and informal style, Joel has a humoristic way of getting his points through. You will find yourself chuckling at some of his arguments, while he makes some pretty good points and observations. What makes this book easy to read is that it is not a tech savvy book, instead it covers topics such as company politics and project management (but also goes into technical details
This book seems to be a compilation of Joels online writing. Some of the books interesting points is what the author calls the Joel Test, and his strategy letters I, II, III, IV, V, which you can find on his website too. Joel really has some good points made out in those articles. Of course there are plenty more in his book.
What I did not like about the book:
Joel's informal style allows him to refer to his previous work experience, praising himself every now and then for saving the day as well as marketing his current products and software company (over and over again). His style can get a little cocky at times. He worked for Microsoft (where he was one of the smaaaaaaartest guys microsoft had according to this book) Well, if joel is such a great project manager he should have come up with some kick ass product that people would like to use, or at least something that I would have heard of before reading this book. (well, i heard it eventually so i guess thats a success for him!).
Overall: This is a book well worth reading and belongs in the shelf of anyone involved with software project management. This may not be a book with formal certified processes, however it contains what I would call common knowledge, which sadly enough is not followed by the majority of companies.
It is a book that draws upon the life experience of a project manager along with explanations of what worked and what didnt work for him, all presented to the reader in simple terms.
Of course you can find most of the books content published on the author's site: www.joelonsoftware.com but I would recommend getting the book instead, its worth it.
Given a file, we would like to check if it is a valid .Net assembly file.
How would you go about it?
1. A couple of words about the PE file format
.Net assemblies are valid PE files. A PE file consists of:
* MS-DOS header
* Stub Program
* PE file signature
* PE file header (This is where we position our stream)
* PE optional header
* Section headers (This is where the RVA15 is)
* Section bodies
The PE file header is where we position our file stream at byte offset 60. The 32 bits at this position are the magical number whose value determine if this is a 32 bit (value = 0x010B) or 64 bit (value=0x020B) PE image. This is important since there is a different offset to the data dictionary for these different types of images. 32 bit images have 0x60 offset to dictionary while 64bit images have a 32 bit offset to the data dictionary. The RVA dictionary is a sequence of 16 pairs of 32 bit. Each RVA entry is 8 bytes. Skipping to RVA15 means skipping 14*8 = 112 = 0x70
A PE file is considered a .Net assembly when RVA15 contains a non zero value. RVA 15 points to the CLI header.
For Further details about the CLI header:
http://dotnet.di.unipi.it/EcmaSpec/PartitionII/cont24.html
Microsoft PE file format:
http://www.microsoft.com/whdc/system/platform/firmware/PECOFF.mspx
2. IsDotNetAssembly - Delphi version
function IsDotNetAssembly(FileName: string):boolean;
var
fs: TFileStream;
peHeader: LongWord;
peMagicNumber: Word; //contains if it is 32bit or 64bit image
RVA15Value: LongWord;
DictionaryOffset: LongWord;
begin
result := false;
fs := TFileStream.Create(FileName, fmOpenRead or fmShareDenyNone);
try
fs.Position := $3C; //PE Header start offset.
fs.ReadBuffer(peHeader,sizeOf(peHeadeR));
fs.Position := peHeader + $18;
fs.ReadBuffer(peMagicNumber, sizeOf(peMagicNumber));
case peMagicNumber of
$010B: DictionaryOffset := $60; //32 bit Image
$020B: DictionaryOffset := $70; //64 bit Image
else
raise Exception.Create('Invalid Image Format');
end;
//Position to RVA 15 of the DataDictionary.
fs.Position := peHeader + $18 + DictionaryOffset + $70;
//Read the value.
fs.ReadBuffer(RVA15Value,sizeOf(RVA15Value));
//If this value is non zero this is a clr assembly
result := RVA15Value <> 0;
finally
fs.free;
end;
end;
3. IsDotNetAssembly C# Implementation
Just for the fun of it, here is the C# version.
private bool IsDotNetAssembly(string fileName)
{
using (FileStream fs =
new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
try
{
using (BinaryReader binReader = new BinaryReader(fs))
{
try
{
fs.Position = 0x3C; //PE Header start offset
uint headerOffset = binReader.ReadUInt32();
fs.Position = headerOffset + 0x18;
UInt16 magicNumber = binReader.ReadUInt16();
int dictionaryOffset;
switch (magicNumber)
{
case 0x010B: dictionaryOffset = 0x60; break;
case 0x020B: dictionaryOffset = 0x70; break;
default:
throw new Exception("Invalid Image Format");
}
//position to RVA 15
fs.Position = headerOffset + 0x18 +
dictionaryOffset + 0x70;
//Read the value
uint rva15value = binReader.ReadUInt32();
return rva15value != 0;
finally
{
binReader.Close();
}
}
}
finally
{
fs.Close();
}
}
}
If you have managed to reach this post for some reason, it happens to be my very first blog post on my subtext powered blog.
So, why blog? Its been on my mind for some time now, but its about time, got myself the domain and up went subtext!
I decided to use a preinstalled skin with a little of my own touch on it, played a little with css changed header and added a nifty little cube at the top right! Well, thats all for now...