Information/ErrorCode from FireInformation/Error

Hi,
Very low priority this one...

Is there a "free" number range that we should use for the InformationCode/ErrorCode parameter to the FireInformation/FireError methods? Currently I just use 1 and -1 respectively.

On a similar note...I remember someone from MS (might have been Matt) once saying that a list of all the rror codes would be published at some point. Is this still the plan and if so, where will it be?

Thanks
JamieJamie,

I can respond only to the 2nd half of your question, on documentation.

As all of the errors are fields in the Microsoft.SqlServer.Dts.Runtime.HResults class, they are already listed in the API reference, particularly in the HResults Members topic in BOL at ms-help://sql90/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Runtime_HResults_Members.htm.

Unfortunately this and similar topics are auto-generated by our doc tools in a format that we can't modify. Therefore this particular list contains the "symbolic name" for each error (eg DTS_E_64BITVARIABLERECAST) and its description...but NOT the actual numeric HRESULT.

For the Web refresh of BOL that we're releasing around RTM, we've added a another similar topic that DOES contain the actual HRESULT codes (in hex, as you'll see them in real life), titled "Integration Services Error Reference."

Hope this helps,

-Doug|||Doug,
Frans Van Bree had a good question here: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2254.aspx?CommentPosted=true#commentmessage

Will all the event codes from the OnInformation event also be in this reference?

-Jamie|||It's my understanding that errors, warnings, and informational messages from Microsoft-provided Integration Services components are all included in the HResults class already documented (though without the hex codes) in the API reference, as mentioned in my previous post.

These will also be included, with hexadecimal codes, in the new topic that I mentioned. Its planned title, "Error Reference," may be somewhat misleading, but there could be youngsters out there for whom "hresult" is meaningless. <g>

In other words, the new list, like the existing one, will include not only the messages whose symbolic names begin DTS_E_*, but also DTS_I_*, DTS_MSG_*, etc.

(Off topic: My favorite, to date, is DTS_I_CANTRELIEVEPRESSURE.)

-Doug

Indexing XML

I have an table-valuated function with XML as parameter. I generate table from that XML. Here is an example of parameter:

<root>
<an>
<anid>5433</anid>
<tix>64</tix>
<dataid>43</dataid>
<datavalue>bc84</datavalue>
</an>
...
</root>

function body begins with:
WITH X(anid, tix, dataid, datavalue, cnt) AS
(
SELECT
new.col.query('./anid').value('.', 'nvarchar(100)') as anid,
new.col.query('./tix').value('.', 'nvarchar(100)') as tix,
new.col.query('./dataid').value('.', 'nvarchar(100)') as dataid,
new.col.query('./datavalue').value('.', 'nvarchar(100)') as datavalue,
cnt = (
SELECT count(*) as cnt
FROM @.xmldata.nodes('/root/an') new(col)
)
FROM
@.xmldata.nodes('/root/an') new(col)
)
....

after what I use X table in joins. The problem is that the X table has no indexes and joins are very slow :[. Is there any possibility to improve performance? That is function and I cannot create index.

thanks a lot

if ur using sqlserver2005 , there is an option for xml index there, though u need to have a primary index defined before u can do it...try it out...

reply if it works out ..