July 2004 - Posts
Haven't blogged for some time due to project(s) delivery timetable, but wouldn't forgive me for not mentioning the interesting article I've just read at
Learn to Use the New XML Encryption Class in .NET 2.0
Wintellect's Jeff Prosise, Jeffrey Richter, and John Robbins are now blogging. And so is Matt Pietrek.
Sidebar updated.
[Via Yorai Aminov, from Israel]
ASP.NET 2.0 contains a new set of security-related controls, known collectively as the Login controls.
By taking advantage of the Login controls, you can create standard registration, login, and password recovery pages without writing any code.
[Via Ohad Israeli, Israel]
Suppose you have a lot of ALTER TABLE statements, like the one below (I actually had 30+) and you find out you must delete inconsistent data on child table before procceeding on constraint creation.
ALTER TABLE dbo.ChildTable
ADD CONSTRAINT FK_ChildTable_ParentTable
FOREIGN KEY (ChildField)
REFERENCES dbo.ParentTable (ParentField)
GO
What would you do? Manually add a DELETE statement before each ALTER table statement? No, in my case, I used Textpad and regular expressions. You can check just below
Find: ALTER TABLE[ ]+\(.+\)\n[ ]+ADD CONSTRAINT[ ]+\(.+\)\n[ ]+FOREIGN KEY[ ]+(\(.+\))\n[ ]+REFERENCES[ ]+\(.+\)[ ]+(\(.+\))\nGO\n
Replace with: DELETE \1\nFROM \1\n LEFT JOIN\n \4 ON \1.\3 = \4.\5\nWHERE \1.\3 IS NOT NULL AND \4.\5 IS NULL\nGO\n\n&\n
and then I got a pair of SQL statements for each ALTER TABLE, like the one below
DELETE dbo.ChildTable
FROM dbo.ChildTable
LEFT JOIN
dbo.ParentTable ON dbo.ChildTable.ChildField = dbo.ParentTable.ParentField
WHERE dbo.ChildTable.ChildField IS NOT NULL AND dbo.ParentTable.ParentField IS NULL
GO
ALTER TABLE dbo.ChildTable
ADD CONSTRAINT FK_ChildTable_ParentTable
FOREIGN KEY (ChildField)
REFERENCES dbo.ParentTable (ParentField)
GO
Next friday is holyday in Sao Paulo state. I and my wife have had planned for a trip. Today, my boss informed me I'll have to work on friday, saturday and sunday. Just not mentioned I have worked last saturday and friday. And we are said slavery is something left in the past...
I am a MSDN Brazil subscriber. As so, regularly, I receive newsletters and the last ones informed about TechEd 2004 Brazil, happening 06/07/2004 through 08/07/2004. I forwarded all of them to my boss with no replies so far. Today she informed me our company is sponsoring her to go to TechEd 2004. That is a fair play, isn't it?
I generally create bit columns as NOT NULL DEFAULT dbo.BIT_NO, which in turn is a SQL server default which translates to 0. Today, I found out somebody on my team had created lots of bit fields on several tables as NULL with no default set. So I created the SQL statement below, to list these fields and make my life a bit easier. Comments are welcome. The fields I have to take care of is the ones with '*' on DNF column.
SELECT CASE WHEN dbo.syscomments.text IS NULL THEN '*' ELSE '' END AS [DNF], -- DNF = Default not found
dbo.sysobjects.name AS tableName,
dbo.syscolumns.name AS columnName,
dbo.systypes.name AS typeName,
dbo.syscolumns.isnullable,
REPLACE(dbo.syscomments.text, CHAR(13) + CHAR(10), '\n') AS [default bound with sp_bindefault]
FROM dbo.syscolumns
INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
LEFT OUTER JOIN
dbo.syscomments ON dbo.syscolumns.cdefault = dbo.syscomments.id AND
OBJECTPROPERTY(dbo.syscolumns.cdefault, 'IsConstraint') = 0
WHERE (dbo.sysobjects.type = 'U') AND
(dbo.systypes.name = 'bit') AND
(dbo.syscolumns.isnullable = 0)
ORDER BY dbo.sysobjects.name,
dbo.syscolumns.name
More Posts