Archives by Tag 'SQL'

SQL server 2008 SP1 Restart computer checks failed

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

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

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

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

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

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

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

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 […]