What else happens at the time that this function gives the error?

Is there possibly a script running that DROPs and reCREATEs the function, but doesn't apply permissions for a finite...

you can grab the whole lot like this:

DECLARE @XML XML = '<MESSAGE>

<HEADER><Source_System>ACF</Source_System><Target_System>CRIMS</Target_System><System_Date>2011/01/14</System_Date><Generation_Date>2011/09/05</Generation_Date>

<Generation_Time>11:25:28</Generation_Time></HEADER><DETAIL><Account_CD>VPBN</Account_CD><Source_Security_ID>131924</Source_Security_ID><Long_Short_Code>L</Long_Short_Code><Valuation_Date>2011/01/14</Valuation_Date><Qty>N</Qty><Tax_lot_ID>N</Tax_lot_ID><Position_Or_TaxLot>P</Position_Or_TaxLot><Desc>última flor do Lácio</Desc><Source>ACF</Source></DETAIL><TRAILER><Number_of_Records>N</Number_of_Records></TRAILER></MESSAGE>';

select x.Y.value('local-name(.)','varchar(30)') as [node], x.Y.value('.','varchar(50)') as [value]

from @XML.nodes('/MESSAGE/DETAIL/*') as x(Y)

There is an undocumented procedure called sp_MSforeachDB. There's quite a bit of information on Google about how to use it. e.g. http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx

Behind the scenes it's doing...

You can't put the value of Column1 into a variable - or at least if you do the variable will only ever be able to hold the value of Column1...

Here are some other options, all of which return different answer, yet all satisfy your query. It would be much better if you could explain what you're looking for...

sp_xml_preparedocument doesn't read a file from disk, it creates a handle to an internal XML document (variable). To read the attached XML file into a variable, see...

declare @int int = 1;

declare @alert varchar(max) = '{0}{1}';

declare @N1 xml = '<AlertParameters><AlertParameter1>Param1</AlertParameter1><AlertParameter2>Param2</AlertParameter2></AlertParameters>'

declare @param varchar(50)

select @param = 'AlertParameter'+cast(@int+1 as char(1))

select @alert = replace(@alert,'{' + CAST(@int AS CHAR(1)) + '}',@N1.value('/AlertParameters[1]/*[local-name()=sql:variable("@param")][1]','varchar(max)'))

Jacob Sebastian's...

I think it would probably be a simpler solution to just add a TYPE directive to the original query (which returns the result as an explicity XML type):

SELECT id...

Like this?

DECLARE @Email VARCHAR(50) = 'a@b.com', @NodeName VARCHAR(50) = 'Email'

DECLARE @NodeXml XML

SELECT @NodeXml = '<'+ @NodeName+ '></' + @NodeName +'>'

SET @NodeXml.modify

(

'insert text{sql:variable("@Email")} into /*[local-name()=sql:variable("@NodeName")][1]'

)

SELECT @NodeXml

Jacob Sebastian's blog http://beyondrelational.com/blogs/jacob/archive/2008/08/26/xquery-lab-24-reading-value-of-an-element-specified-by-a-variable.aspx is...

Yes indeed. And the column names should really be quoted to combat SQL Injection

declare @colname varchar(100)

Set @colname = 'a1,a''2,a[3,b1,b2,a1'

declare @colout nvarchar(100)

declare @sql nvarchar(4000)

set @sql = 'Select @colstr = CAST(col...

If you put the comma into the IsNull() as well, you can lose the final stuff() as well...

declare @colname varchar(100)

Set @colname = 'a1,a2,a3,b1,b2,a1'

declare @colout nvarchar(100)

declare @sql nvarchar(4000)

set @sql = 'Select...

If converting to XML works for you, then fine, but the very simple solution to your problem as just to add an IsNull() within the SQL that you're executing:

declare @colname...

In just a couple of years, this table is only going to contain past dates.

If you're going to create a calendar table, it should contain more years than this, I...

I think you are pretty much describing exactly the functionality of the MERGE command.

Look it up in Books OnLine, the help there is comprehensive.

Easiest to have three sort fields, I would say.

One to sort the non-Nulls before the Nulls, and one each for the Ascending and Descending sorts:

;with s as (

SELECTdt.id

,dt.code

,dt.name

,casewhen@sortColumn in ('Name','Name...