Archive for 'SQL' Category

Query table containing spatial datatype over linked server

By admin - Last updated: Saturday, September 22, 2012

If the target table containing column with spatial datatype, and you like to do the following query, SELECT * FROM [LinkedServerName].[DatabaseName].dbo.MyPolygon it will return Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object The work around is SELECT * FROM OPENQUERY([LinkedServerName], ‘SELECT * […]

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 + ‘.’+ 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 […]

SQL 2005 BASE64 Decode Query Syntax

By admin - Last updated: Thursday, August 21, 2008

Decode BASE64 encoded string SQL query syntax — declare vars. DECLARE @data varchar(max), @XmlData xml — set the base64 encoded varchar. SET @data = ‘SGVsbG8gV29ybGQgIQ==’ — construct an xml var. SET @XmlData = CAST(‘<data>’ + @data + ‘</data>’ as xml) — base64 decode the @data. SELECT   CONVERT(varchar(max),        @XmlData.value(‘(data)[1]’, ‘varbinary(max)’))

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