If you have only one root node in the XML then the following gives better performance:

SELECT Nodes.value(N'@code', N'nvarchar(10)') AS CodeValue

FROM @XmlStr.nodes(N'/XObjects[1]/Objects') XObjects(Nodes);

The change here is in the "...

One way to do this would be:

DECLARE @XmlStr XML;

SET @XmlStr = '<XObjects><Objects Type="Alp" code="Alpa125" pChild="0" grp="0" /></XObjects>' ;

SELECT Nodes.value(N'@code', N'nvarchar(10)') AS CodeValue

FROM @XmlStr.nodes(N'/XObjects/Objects') XObjects(Nodes);

Note that the...

I wanted to recheck whether the estimated number of rows returned by a multi line table valued function is really always 1 - here is the sample code attached (since...

Do you have multi-line table valued functions?

For multi-line table valued functions the optimizer always estimates the number of rows returned as 1 - it is better at estimating the...

This might not be a SQL Server related error. Do you have IIS installed on the box along with SQL Server?

This link might be of help: http://forums.iis.net/t/1148965.aspx

Regarding the script to create/alter objects - I normally tend to have a script that checks if the object exists, drop it if it exists and then create it. Sort...

You are right - the WHILE(@@TRANCOUNT>0) is not needed...I did not know that ROLLBACK TRANSACTION decremented the transaction count to 0 even with nested transactions...

I am not too sure about...

First issue:

The behaviour you are seeing is due to SQL Server's error handling features.

From information available in http://www.sommarskog.se/error-handling-I.html and http://www.sommarskog.se/error-handling-II.html

Since you are setting XACT_ABORT - most statement abort...

Does this do what you want?

I was trying to find a way to determine the position of a given ResID in the sequence, and remove the value at that position...

It does look like a bug. There is a bug already logged on connect (http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=356617) that seems to be the same issue

I haven't really seen the database field being empty for T-SQL job steps. Even if you create a job via T-SQL commands and don't provide the @database value I think...

This has now become a string replacement sort of problem. From what I understand - you need to replace the existing value at a particular position in the string with...

Nice!! I never thought of using STR()...I can now use this in a lot of places...Thanks Jeff!

I normally go to http://www.sqlskills.com/whitepapers.asp which has a very nice listing of whitepapers from MSDN and Technet.

Yes - you will have to change the datatype to decimal (unless you specifically need float).

Assuming you want to track the % increase between products and productspurchase for the same...