Sunday, 11 January 2009
SQL 2005 cannot drop user with owned schema
i notice when i backup a database and restore it to another server, the user IDs are different and i have to delete the users and add them back in to the database. however last time i tried this the drop failed because the user owned a schema. i right-clicked on the user's properties and the tick box for 'db_owner' was shaded in a read only state. so i couldn't delete the user and couldn't remove their schema ownership. took a while to figure out that the way to do this is via the Database > Security > Schemas menu, then change the schema owner back to itself, e.g. db_owner. then the user is free to be deleted.
Sunday, 11 January 2009 15:28:04 (GMT Standard Time, UTC+00:00) Database
Tuesday, 23 December 2008
HowTo: disable the Excel prompt for opening formats different than the extension
many web sites export tabular information from grids/tables into 'Excel' format. the file is not a true Excel format and actually contains X/HTML. if the file is opened in Excel 2007 you may get a prompt that the file is in a different format than specified by the file extension. if you regularly work with files like this, there is a way to disable this prompt, add a DWORD key called ExtensionHardening with value 0 to HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security
it works straight away, no need for a reboot.
Tuesday, 23 December 2008 12:08:07 (GMT Standard Time, UTC+00:00) Asp.Net
Wednesday, 10 December 2008
Sunday, 26 October 2008
IIS6 Service Unavailable, after installing KB 958644
IIS went down this morning after KB 958644 was isntalled automatically last night. My sites were showing "Service Unavailable"
there were several system event logs like so:
- A process serving application pool '.Net 2.0 App Pool' suffered a fatal communication error with the World Wide Web Publishing Service. The process id was '2960'. The data field contains the error number.
- Application pool '.Net 2.0 App Pool' is being automatically disabled due to a series of failures in the process(es) serving that application pool.
rebooting the server did not solve the problem. so, on further research, KB article 885654
revealed the cause of the errors, lack of registry permissions for NETWORK SERVICE. i hadn't changed any configuration on the server so assumed it was caused by a change in that windows update package. i dug out ProcessMonitor
and set up a registry filter for w3wp.exe where the Result was ACCESS DENIED. there were about 50 entries during the time i enabled event capture and loaded up one of the sites that uses the affected application pool. Most of them pointed to SystemCertificates entries. I went through each one in regedit and gave NETWORK SERVICE full control where the process attempted to Create a key, and Read only access where the process attemped to Open the key. worked fine then.
hope this helps someone out.
Sunday, 26 October 2008 13:41:10 (GMT Standard Time, UTC+00:00) Asp.Net | Windows Server
Thursday, 16 October 2008
XPath problems, SelectNodes returns no nodes
thanks to Kev for his brilliant post
about solving this problem, where you know you have the correct XPath expression but .Net is not giving you any nodes :(
his solution is the correct one, an alternative approach is to remove the xmlns from the root element before you load the XmlDocument so that you can use the default empty namespace (or something like that).
Thursday, 16 October 2008 19:05:36 (GMT Daylight Time, UTC+01:00) .Net General
Wednesday, 10 September 2008
Alter a column with a default value
say you wanted to change a BIT column to a NVARCHAR column, and the BIT column has a default value set to 0 or 1. you can't run the following statement or you get a dependent object error.
alter table TABLE1 alter column COL1 NVarChar(MAX)
so i found this solution here
, after you've run the query below, you can alter the column as above.
DECLARE @df SYSNAME
SET @df =
WHERE id = OBJECT_ID('dbo.TABLE1')
AND name = 'COL1')
IF @df IS NOT NULL
EXEC sp_rename @df, 'df_to_drop', 'OBJECT'
ALTER TABLE dbo.TABLE1 DROP CONSTRAINT df_to_drop
Wednesday, 10 September 2008 10:46:01 (GMT Daylight Time, UTC+01:00) Database
Wednesday, 20 August 2008
Encoding issues with File.WriteAllText
i assumed that File.WriteAllText() would assume UTF8 encoding since all strings are unicode in .net by default, so i never bothered specifying the encoding in the 3rd parameter. But it was causing me grief with accented characters, and i finally worked it out that i needed to specify UTF8 explicitly.
File.WriteAllText(filepath, text, Encoding.UTF8);
Wednesday, 20 August 2008 15:00:46 (GMT Daylight Time, UTC+01:00) .Net General
Thursday, 17 July 2008
Euro symbol character problems...
if you ever send a string across a web service, and write it out to a file, make sure you specify Encoding.UTF8 explicitly. otherwise characters such as the EURO symbol may not render correctly in some browsers (IE6). it took me ages to pin this down, because everything i read about was about HTTP header charset values, or HTML document charsets, or database encodings. In my case i was using the default encoding and this messed up EURO symbols. I suspect it is because of the string being serialized in the web service, but haven't the time to look into it any further. it's fixed now anyhow.
Thursday, 17 July 2008 17:25:32 (GMT Daylight Time, UTC+01:00) .Net General | Asp.Net
Friday, 09 May 2008
SQL: String Split Function
I don't know why this isn't part of the built-in functions, especially in sql 2005 but anyway, here it is thanks to this groups post
CREATE FUNCTION Split(@String varchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int, Items nvarchar(4000))
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @ID int
SELECT @INDEX = 1, @ID = 1
WHILE @INDEX !=0
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
SELECT @ID = @ID + 1
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
Then you can do something like this:
select Items from dbo.Split(@List, ',')
Friday, 09 May 2008 12:40:24 (GMT Daylight Time, UTC+01:00) Database
Thursday, 01 May 2008
SonicWall Global VPN Client, Acquiring IP...
Trying to get SonicWall Global VPN Client working on vista, i kept getting stuck at "Acquring IP". Adding the program to the windows firewall made no difference, nor did opening port 443 as has been suggested by others. What did work was disabling IPv6 on the virtual adapter.
Start->Control Panel->Network and Internet->Network and Sharing Center->Mange network connections page. Select SonicWALL Virtual Adapter and right click on properties. Untick IPv6 and you should be good to go. i didn't have to configure my router or anything to get this to work.
Update Sept 2008
Install the latest client from Sonicwall and this problem goes away, the virtual network adapter is automatically disabled when not in use.
Thursday, 01 May 2008 16:09:52 (GMT Daylight Time, UTC+01:00) General