Archives by Tag 'SQL'

SQL server 2008 SP1 Restart computer checks failed

By admin - Last updated: Tuesday, July 20, 2010

I was trying to install Biztalk server 2010 beta which requires SQL server 2008 SP1. At beginning of installing SQL server 2008 SP1, it says Restart computer required. It looks something has not been reset or cleared properly. After searching around, I realised that this could be caused by that some pending operations hasn’t been [...]

MSSQL : List all tables with size and row count

By admin - Last updated: Sunday, December 13, 2009

It comes to be very handy while analyzing the database tables. Here is a quick and dirty script lists all the tables and their rows counts, data size. DECLARE @table table(Id int IDENTITY(1,1) , Name varchar(256)) INSERT INTO @table SELECT b.name + ‘.’+ a.name FROM sys.tables a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id [...]

SQL XML, select from xml using nodes() function

By admin - Last updated: Thursday, October 1, 2009

Very handy function that helps me move small data around. DECLARE @myXml AS XML SET @myXml = ‘<ArrayOfProduct>         <Product id="1">                 <Description>test1</Description>         </Product>         <Product id="2">                 <Description>test2</Description>     [...]

Database diagram support objects cannot be installed

By admin - Last updated: Monday, March 30, 2009

When attaching a new database from someone else, you could see an error pop up when trying creating a diagram, it is caused by the dbo of the attaching database doesn’t have a login. Perform the following steps could resolve the issue, USE [master] EXEC sp_dbcmptlevel ‘DatabaseName’, ’90′; ALTER AUTHORIZATION ON DATABASE::”DatabaseName” TO “UserName” EXECUTE [...]

How does LINQ to SQL concurrency detect conflicts

By admin - Last updated: Friday, February 6, 2009

LINQ to SQL utlises optimistic concurrency control (OCC) to determine the conflicts. OCC compares the loaded record to existing record before performing an update operation on the row so that the underlying conflicts could be detected. The followings are steps to replicate the scenario, Table Fruit Id Name UnitPrice Description 1 Apple 2.5 very long [...]

LINQ InsertOnSubmit, DeleteOnSubmit and SubmitChanges not working

By admin - Last updated: Sunday, February 1, 2009

I had the same problem with SQLExpress database file. In one word, I looked at the records from the wrong database. There were actually two versions of the database file. The mdf file gets copied over to bin\debug\data folder e.g. bin\debug\data\test.mdf after compilation, but I verify the data against the one within solution. This likely [...]

SQL 2005 generates XML comment, array of elements

By admin - Last updated: Monday, November 12, 2007

SELECT         ‘nameValue’ AS “@name”,         CAST(’<!– your comment –>’ AS XML),         ‘anotherValue’ AS “another”,         (SELECT ‘i’ AS “@name”, ’1′ AS “@value” FOR XML PATH(’item’), TYPE),         (SELECT ‘j’ AS “@name”, ’2′ AS “@value” FOR XML PATH(’item’), TYPE) [...]

MSSQL Get Current Stored Procedure Name

By admin - Last updated: Thursday, May 3, 2007

In MS SQL, the current stored procedure name can be obtained from the follow SQL snippet. It is useful for logging purposes. – omitted code for SP. DECLARE @Name NVARCHAR(256) SELECT @Name = OBJECT_NAME(@@PROCID) SELECT @Name – omitted code for SP.   The value would be null if the it is not executed in stored [...]