Recently, I’ve been involved in a cloud provider project where I had to do a row count on a Microsoft SQL Database table with more than 50.000.000 rows in it.

Doing

SELECT COUNT(*) FROM myTable

took about 10 seconds – since it performs a full table scan.

Here’s an alternative way (still accurate) of doing it that takes under 1 sec:

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('myTable')   
AND (index_id=0 or index_id=1);

You can run this from Microsoft Entity Framework as well:

long total = dbContext.Database.SqlQuery<long>("SELECT SUM (row_count) FROM sys.dm_db_partition_stats WHERE object_id=OBJECT_ID('myTable') AND (index_id=0 or index_id=1);").First();

When trying to take a SQL Server database offline, you might find yourself waiting, waiting and waiting because some process is hugging the database. This is how you fix it. Run:

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

In some cases you might get an error saying:

ALTER DATABASE failed because a lock could not be placed on database 'dbname' Try again later.

After you get this error, run

EXEC sp_who2

Look for the database in the result and find the SPID for that connection, then run

KILL <SPID>

That should do the trick!

We recently had a problem where the generated model in Entity Framework 4 would not create all the relationships we had in our database.

We were moving from a Linq2Sql model to Entity Framework and tried to automatically generate the model from the database, but we kept missing a few of the relationships. After hours of looking at the missing relationships in the database, comparing them to see why these would go missing while all others were ok, we got a hint at StackOverflow to check the indexes.

Having a Unique Non-Clustered Index on the primary key caused a problem for the EF model and changing these to a Unique Clustered Index fixed the problem.

I recently had some problams emptying the Recycle Bin in Umbraco 4.6.1.  I clicked Empty Recycle Bin – but after a few items got removed the progress suddenly stopped showing 201 items remaining. Crap.

Internet Explorer displayed a Javascript Error msg (with a stacktrace):

No Document exists with Version '00000000-0000-0000-0000-000000000000'

Waiting, restarting IIS and trying to empty the recycle bin again did not help. Time to Google.

I found part of the solution here:

The sql

SELECT * FROM umbracoNode, cmsContent -- return nodes
WHERE
nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' -- that are of type 'Content'
AND
umbracoNode.id NOT IN (SELECT nodeId FROM cmsContent) -- but are not in the 'Content' table

helped me identidy orphan nodes that should be removed.

For each unique umbracoNode.Id in the result (you will get a lot of rows with the same id using the sql above) i ran the sql below to remove these nodes.

DECLARE @TEMPID INT
SET @TEMPID= 1843 -- TYPE IN ID HERE - e.g. 1843

DELETE
FROM         cmsContent
WHERE     (nodeId IN (Select id from umbracoNode where parentID = @TEMPID))

DELETE
FROM         cmsPropertyData
WHERE     (contentNodeId IN (Select Id from umbracoNode where parentID = @TEMPID))

DELETE
FROM         umbracoNode
WHERE     (id IN (Select id from umbracoNode where parentID = @TEMPID))

DELETE
FROM         umbracoNode
WHERE     (ID = @TEMPID)

Then I tried emptying the recycle bin again. Now it deleted 10 more items before it stopped.

I repeated the steps above again – first I ran the select sql to find the new orphan nodes, and then I ran the delete sql’s. I was able to remove 43 more items from the recycle bin this way.

I repeated this procedure until my Recycle Bin was empty (I had to do it 6-7 times or so).

Make sure to backup your database before trying this approach.

Vi gikk på ett problem med at vi ikke fikk startet search service etter installering av MOSS 2007.

Tydeligvis liker ikke WSS at man bruker FQDN(fully qualified domain name) i Sharepoint_Config databasen.

Kjapp fiks: Kjør i SQL Manager på config-databasen:
UPDATE Objects Set Name=’NETBIOS’ WHERE Id=(SELECT Id FROM Objects WHERE Name=’FQDN’)
(bytt ‘NETBIOS’ og ‘FQDN’ med servernavn etc)

Vi gutset på, men best practise er nok å kjøre en backup av databasen først

A recent request from a client included digging into some data from an old CRM system, which is using the .DBF format. A quick search online showed that there are actually people charging money for query managers supporting this format – which, by the way, is obsolete and slow. I therefore sacrificed a couple of hours of my precious time (..) and put together a very simple DBF Query Manager, written in managed C# code.

This might be developed further, but I honestly doubt it. It solved my issues, and might solve yours as well.

1. Choose a directory where the DBF files are stored (DBF is a flat file database system where a table is represented as a file within a directory). In my example the .DBF files are stored in Q:\DBFDemo:

2. Click Connect to open the Main Query Window:

3. Run your queries as you would in an overpriced, commercial, dishonest version! Including nested queries – any syntactically correct query will work.

Note: this thing is free. Free like it used to be in the old days – with no strings attached, or a “max 30 days use before we rip you off”-scheme hidden somewhere in the code. It took me a couple of hours to build. This blog post included! But for that very reason there is hardly any error handling! It will display a “Table not found” error message whenever it detects something wrong – like if you screw up the query. There is no intellisense (or intelligence..), and it is READ-ONLY! At least for now..

But it works! Like a charm! And THAT’s the main point!

Click here to download (requires .NET Framework 4.0)