vlad navazhylau's blog

Work around for XML data type not supported in Distributed Queries

http://www.msdner.com/dev-archive/84/19-85-847101.shtm

SELECT
Cast(a.XML_Data as XML) as XML_Data
FROM
OPENQUERY([LINKED SERVER NAME HERE],'
SELECT
Cast(XML_Data as Varchar) as XML_Data
FROM
[DATABASE NAME].[SCHEMA].[TABLE NAME]'
) A

Basically, the data is queried on the remote server, converts the XML data to a varchar, sends the data to the requesting server and then reconverts it back to XML.

StephenDudzic at 2007-9-3 22:09:22 >

"Re: XML data type not supported in Distributed Queries This is a limitation in SQL Server 2005. Columns of xml type or CLR type cannot be queried directly or referenced from one server to another - this means the following:

1. You cannot use a table or view that contains xml or clr type as 4-part name in your query
2. You need to cast the column to either nvarchar(max) or varbinary(max) or other appropriate type to use
3. If you have a table that has xml type for example then you need to create a view that contains all columns other than xml and query it instead. Or you can issue a pass-through query using OPENQUERY with the appropriate columns only."

Indexing encrypted data

http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

Using Triggers In MS SQL Server

http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/2/

CREATE TRIGGER trig_updateAuthor
ON authors
FOR UPDATE 

AS

DECLARE @oldName VARCHAR(100)
DECLARE @newName VARCHAR(100)

IF NOT UPDATE(au_fName) AND NOT UPDATE(au_lName)
BEGIN
   RETURN 
END

SELECT @oldName = (SELECT au_fName + ' ' + au_lName FROM Deleted)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)

PRINT 'Name changed from "' + @oldName + '" to "' + @newName + '"'

SQL Server 2005 new feature PIVOT Exmaple

WITH cteStatusCounts(FormsCount, SubjectID,StatusCodeID)
AS
(
    SELECT     COUNT(FormID) AS FormsCount, SubjectID,StatusCodeID
    FROM         tblFeedbackForms
    Where SubjectAreaID=2
    GROUP BY SubjectID,StatusCodeID
)

SELECT
    FormsPivot.SubjectID as KeyID,
    stTotalIncoming=ISNULL([1]+[2]+[3]+[4],0),
    stOpen=ISNULL([1], 0),
    stMoreInfo=ISNULL([2], 0),
    stWithdrawn=ISNULL([3], 0),
    stCoached=ISNULL([4], 0)
FROM
    cteStatusCounts
PIVOT(sum(FormsCount) FOR StatusCodeID in([1],[2],[3],[4])) as FormsPivot
Order by FormsPivot.SubjectID

Dynamic queries and security

If you are using dynamically built queries, then employ the following techniques:

• Delimit single quotes by replacing any instance of a single quote with two single quotes which prevents the attacker from changing the SQL command. Using the example from above, "SELECT * from Users WHERE login = ''' or ''1''=''1' AND password = ''' or ''1''=''1'" has a different result than "SELECT * from Users WHERE login = '' or '1'='1' AND password = '' or '1'='1'".
• Remove hyphens from user input to prevent the attacker from constructing a query similar to: SELECT * from Users WHERE login = 'mas' -- AND password ='' that would result in the second half of the query being commented out and ignored. This would allow an attacker that knows a valid user login to gain access without knowing the user's password.
• Limit the database permissions granted to the user account under which the query will be executing. Use different user accounts for selecting, inserting, updating, and deleting data. By separating the actions that can be performed by different accounts you eliminate the possibility that an insert, update, or delete statement could be executed in place of a select statement or vice versa.