Press CTRL+SHIFT+] before the opening '(' to select code between two parenthesis.
Without the SHIFT (CTRL+] ) the cursor will jump to the closing parenthesis.
Without the SHIFT (CTRL+] ) the cursor will jump to the closing parenthesis.
wrong XML parent
Problem:
XML - node is unfer the wrong parent
Cause and solution:
Check that the node is one of the items of the 'Order By', and check the order. The XML query should be sorted by the key nodes.
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).
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:
Subscribe to:
Posts (Atom)