XML tag ID 4 that was originally declared as is being redeclared as

Error message:

XML tag ID 4 that was originally declared as Aaaaa is being redeclared as Bbbbb.

Cause and solution:

Each group of data should be declared in other tag.

In this case, there were 2 groups with tag 4: [Aaaaa!4!CollectionID] and [Bbbbb!4!CollectionID].

The solution was to create new tag to the new group: [Bbbbb!13!CollectionID].

Of course that I had to add new UNION select in order to select the data of Bbbbb and to assign it to a parent.

XML field value is NULL

T-SQL:

DECLARE @EventXML XML

SET @EventXML=convert(xml,N'

<Event>

<ExternalID>4947</ExternalID>

<ExternalEventId>abc</ExternalEventId>

</Event>')

DECLARE @hDoc int

exec sp_XML_PrepareDocument @hDoc output,@EventXML

select ExternalID, ExternalEventID

from

OPENXML(@hDoc,'//Event',2)

with

( ExternalID nvarchar(100),

ExternalEventID nvarchar(100)

) XML

exec sp_XML_RemoveDocument @hDoc

Results:

ExternalID = 4947, ExternalEventId = NULL

Problem:

ExternalEventId should be 'abc' and not NULL.

Solution:

XML is case sensitive.

ExternalEventId was set to the XML with 'd', and in the OPENXML is 'D'.

Replace one of them to be like the other.

select ExternalID, ExternalEventID

from

OPENXML(@hDoc,'//Event',2)

with

( ExternalID nvarchar(100),

ExternalEventId nvarchar(100)

) XML

There is insufficient system memory in resource pool to run this query

Error message:

There is insufficient system memory in resource pool to run this query

Possible solutions and checks:

Check the physical memory of the server, it's probably 100% of it is being used.

Possible fixes can be:

• Limit or increase the maximum server memory of the server (SSMS-->DB properties-->Memory). If you have enough RAM on the server - increase the memory, if not - check the limitation.
• Remove applications on the server that use much of the physical memory.
• Increase the RAM on the server.
• You can also restart the SQL Server Service, but it's just a quick solution, not permanent.

The transaction log for database is full

Error message:

The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

Possible solutions and checks:

Make sure you have enough space free on the drive.
Shrink Database and/or files.
You can change the database RECOVERY model to SIMPLE (or BULK) if you want to keep log file minimal. (not recommended in production databases).

Go Further. Forward. Faster. with Microsoft SQL Server 2012

Go Further. Forward. Faster. with Microsoft SQL Server 2012:

http://www.sqlserverlaunch.com/ww/Home

Search part of words using CONTAINS command

In order to search part of words using CONTAINS command add * in the start and/or the end of the search Phrase:

select * from TABLE WHERE CONTAINS(Field4Search, N'"*copy pas*"')

If you have a parameter, you need to add those 2 *: 

(N'"*' + @TxtValue + N'*"')

Parent tag ID 1 is not among the open tags

Error message:

Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set

Cause and solution:

Wrong Parent Tag:

Check the parent field, make sure that the parent is not NULL (instead of the root), and that the Parent ID is already created and/or it's among the open tags.

Comment out the FOR XML EXPLICIT part of the query to get the results and understand the ordering.

Maybe you need to add/remove/change the order of the tag and/or the parent fields in the ORDER BY part of the query.