Hi,

I am trying to execute an INSERT query. Do not have any issue with the
query, it is getting executed as desired and reflecting the changes on
the table rows.
My problem is when I am looking at the trace can find the driver going
to the Retry status. That is probably because I have an INSERT statement
which contains two SELECT statements in it, and it is getting confused
about where to put the intermediate results. The trace is looking like:

[01/12/11 15:30:46.053]:##ALM## ST:Applying policy:
%+C%14Csub-ctp-ProcessAttributesonAddandModify%-C.
[01/12/11 15:30:46.053]:##ALM## ST: Applying to add #1.
[01/12/11 15:30:46.049]:##ALM## ST: Evaluating selection criteria
for rule 'Add User: Set primary key ALM_OBJ106_Idn of the user for table
ALM_OBJ106'.
[01/12/11 15:30:46.050]:##ALM## ST: (if-class-name equal "User") =
TRUE.
[01/12/11 15:30:46.050]:##ALM## ST: (if-operation equal "add") =
TRUE.
[01/12/11 15:30:46.050]:##ALM## ST: Rule selected.
[01/12/11 15:30:46.050]:##ALM## ST: Applying rule 'Add User: Set
primary key ALM_OBJ106_Idn of the user for table ALM_OBJ106'.
[01/12/11 15:30:46.055]:##ALM## ST: Action:
do-set-local-variable("pvarQuery",scope="policy",arg-node-set(token-xml-parse("<input
xmlns:jdbc="urn:dirxml:jdbc"><jdbc:statement
jdbc:type="query"><jdbc:sql>INSERT INTO dbo.ALM_OBJ106
(ALM_OBJ106_Idn,ALM_RESOURCE_BASE_Idn)VALUES
((SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS ALM_OBJ106_Idn),
(SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS
LM_RESOURCE_BASE_Idn));"+"</jdbc:sql></jdbc:statement></input>"))).
[01/12/11 15:30:46.056]:##ALM## ST:
arg-node-set(token-xml-parse("<input
xmlns:jdbc="urn:dirxml:jdbc"><jdbc:statement
jdbc:type="query"><jdbc:sql>INSERT INTO dbo.ALM_OBJ106
(ALM_OBJ106_Idn,ALM_RESOURCE_BASE_Idn)VALUES
((SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS ALM_OBJ106_Idn),
(SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS
LM_RESOURCE_BASE_Idn));"+"</jdbc:sql></jdbc:statement></input>"))
[01/12/11 15:30:46.054]:##ALM## ST: token-xml-parse("<input
xmlns:jdbc="urn:dirxml:jdbc"><jdbc:statement
jdbc:type="query"><jdbc:sql>INSERT INTO dbo.ALM_OBJ106
(ALM_OBJ106_Idn,ALM_RESOURCE_BASE_Idn)VALUES
((SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS ALM_OBJ106_Idn),
(SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS
LM_RESOURCE_BASE_Idn));"+"</jdbc:sql></jdbc:statement></input>")
[01/12/11 15:30:46.059]:##ALM## ST: token-xml-parse("<input
xmlns:jdbc="urn:dirxml:jdbc"><jdbc:statement
jdbc:type="query"><jdbc:sql>INSERT INTO dbo.ALM_OBJ106
(ALM_OBJ106_Idn,ALM_RESOURCE_BASE_Idn)VALUES
((SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS ALM_OBJ106_Idn),
(SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS
LM_RESOURCE_BASE_Idn));"+"</jdbc:sql></jdbc:statement></input>")
[01/12/11 15:30:46.060]:##ALM## ST: token-text("<input
xmlns:jdbc="urn:dirxml:jdbc"><jdbc:statement
jdbc:type="query"><jdbc:sql>INSERT INTO dbo.ALM_OBJ106
(ALM_OBJ106_Idn,ALM_RESOURCE_BASE_Idn)VALUES
((SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS ALM_OBJ106_Idn),
(SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS
LM_RESOURCE_BASE_Idn));")
[01/12/11 15:30:46.058]:##ALM## ST:
token-text("</jdbc:sql></jdbc:statement></input>")
[01/12/11 15:30:46.058]:##ALM## ST: Arg Value: "<input
xmlns:jdbc="urn:dirxml:jdbc"><jdbc:statement
jdbc:type="query"><jdbc:sql>INSERT INTO dbo.ALM_OBJ106
(ALM_OBJ106_Idn,ALM_RESOURCE_BASE_Idn)VALUES
((SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS ALM_OBJ106_Idn),
(SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS
LM_RESOURCE_BASE_Idn));</jdbc:sql></jdbc:statement></input>".
[01/12/11 15:30:46.063]:##ALM## ST: Token Value: {/}.
[01/12/11 15:30:46.064]:##ALM## ST: Arg Value: {/}.
[01/12/11 15:30:46.064]:##ALM## ST: Action:
do-set-local-variable("pvarResult",scope="policy",arg-node-set(token-xpath("cmd:execute($destCommandProcessor,
$pvarQuery)"))).
[01/12/11 15:30:46.064]:##ALM## ST:
arg-node-set(token-xpath("cmd:execute($destCommandProcessor,
$pvarQuery)"))
[01/12/11 15:30:46.065]:##ALM## ST:
token-xpath("cmd:execute($destCommandProcessor, $pvarQuery)")
[01/12/11 15:30:46.065]:##ALM## ST: Direct command from
policy
[01/12/11 15:30:46.062]:##ALM## ST:
<nds dtdversion="3.5" ndsversion="8.x">
<input xmlns:jdbc="urn:dirxml:jdbc">
<jdbc:statement jdbc:type="query">
<jdbc:sql>INSERT INTO dbo.ALM_OBJ106
(ALM_OBJ106_Idn,ALM_RESOURCE_BASE_Idn)VALUES
((SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS ALM_OBJ106_Idn),
(SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS
LM_RESOURCE_BASE_Idn));</jdbc:sql>
</jdbc:statement>
</input>
</nds>
[01/12/11 15:30:46.067]:##ALM## ST: Fixing up association
references.
[01/12/11 15:30:46.067]:##ALM## ST: Applying schema mapping
policies to output.
[01/12/11 15:30:46.067]:##ALM## ST: Applying policy:
%+C%14CSchema+Mapping+Rule%-C.
[01/12/11 15:30:46.068]:##ALM## ST: No output transformation
policies.
[01/12/11 15:30:46.068]:##ALM## ST: Submitting document to
subscriber shim:
[01/12/11 15:30:46.068]:##ALM## ST:
<nds dtdversion="3.5" ndsversion="8.x">
<input xmlns:jdbc="urn:dirxml:jdbc">
<jdbc:statement event-id="0" jdbc:type="query">
<jdbc:sql>INSERT INTO dbo.ALM_OBJ106
(ALM_OBJ106_Idn,ALM_RESOURCE_BASE_Idn)VALUES
((SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS ALM_OBJ106_Idn),
(SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS
LM_RESOURCE_BASE_Idn));</jdbc:sql>
</jdbc:statement>
</input>
</nds>
[01/12/11 15:30:46.066]:##ALM## ST: Acquired lock on
connection 'dedicated0'.
[01/12/11 15:30:46.066]:##ALM## ST: INSERT INTO
dbo.ALM_OBJ106 (ALM_OBJ106_Idn,ALM_RESOURCE_BASE_Idn)VALUES
((SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS ALM_OBJ106_Idn),
(SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS
LM_RESOURCE_BASE_Idn));
[01/12/11 15:30:46.071]:##ALM## ST: BEGIN Instantiate
statement.
[01/12/11 15:30:46.071]:##ALM## ST: Allocating statement
from connection ConnectionID:548.
[01/12/11 15:30:46.072]:##ALM## ST: Allocated statement
SQLServerStatement:2174.
[01/12/11 15:30:46.072]:##ALM## ST: END Instantiate
statement.
[01/12/11 15:30:46.072]:##ALM## ST: BEGIN Execute Query.
[01/12/11 15:30:46.079]:##ALM## ST: Closed statement
SQLServerStatement:2174.
[01/12/11 15:30:46.079]:##ALM## ST: BEGIN Test for bad
connection.
[01/12/11 15:30:46.080]:##ALM## ST: END Test for bad
connection.
[01/12/11 15:30:46.080]:##ALM## ST: Connection is bad.
[01/12/11 15:30:46.080]:##ALM## ST: BEGIN rollback.
[01/12/11 15:30:46.080]:##ALM## ST: ROLLBACK
[01/12/11 15:30:46.081]:##ALM## ST: Unable to rollback
transaction.
[01/12/11 15:30:46.081]:##ALM## ST: Number of statements
allocated on connection 'dedicated0': 0.
[01/12/11 15:30:46.078]:##ALM## ST:
SubscriptionShim.execute() returned:
[01/12/11 15:30:46.078]:##ALM## ST:
<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20090520_1228" instance="ALM"
version="3.5.4">DirXML Driver for JDBC</product>
<contact>Novell, Inc.</contact>
</source>
<output>
<status event-id="0" level="retry" type="app-connection">
<description>An error occurred executing statement "INSERT INTO
dbo.ALM_OBJ106 (ALM_OBJ106_Idn,ALM_RESOURCE_BASE_Idn)VALUES
((SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS ALM_OBJ106_Idn),
(SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS
LM_RESOURCE_BASE_Idn));". There is a connectivity-related
problem.</description>
<jdbc:exception
jdbc:class="com.microsoft.sqlserver.jdbc.SQLServer Exception"
jdbc:error-code="0">
<jdbc:message>The statement did not return a result
set.</jdbc:message>
</jdbc:exception>
</status>
</output>
</nds>
[01/12/11 15:30:46.085]:##ALM## ST: No input transformation
policies.
[01/12/11 15:30:46.085]:##ALM## ST: Applying schema mapping
policies to input.
[01/12/11 15:30:46.081]:##ALM## ST: Applying policy:
%+C%14CSchema+Mapping+Rule%-C.
[01/12/11 15:30:46.082]:##ALM## ST: Resolving association
references.
[01/12/11 15:30:46.082]:##ALM## ST: Requesting 30 second
retry delay.
[01/12/11 15:30:46.087]:##ALM## ST: Processing returned
document.
[01/12/11 15:30:46.087]:##ALM## ST: Processing operation
<status> for .
[01/12/11 15:30:46.087]:##ALM## ST:
DirXML Log Event -------------------
Driver: \SAC_IDM_DEV1\services\IDM-DriverSet\ALM
Channel: Subscriber
Object: \SAC_IDM_DEV1\SAC\INTERNAL\EU\EMPLOYEES\b61058
Status: Retry
Message: Code(-9006) The driver returned a "retry" status
indicating that the operation should be retried later. Detail from
driver: <description>An error occurred executing statement "INSERT INTO
dbo.ALM_OBJ106 (ALM_OBJ106_Idn,ALM_RESOURCE_BASE_Idn)VALUES
((SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS ALM_OBJ106_Idn),
(SELECT MAX(ALM_OBJ106_Idn)+1 FROM dbo.ALM_OBJ106 AS
LM_RESOURCE_BASE_Idn));". There is a connectivity-related
problem.</description>
<jdbc:exception
jdbc:class="com.microsoft.sqlserver.jdbc.SQLServer Exception"
jdbc:error-code="0" xmlns:jdbc="urn:dirxml:jdbc">
<jdbc:message>The statement did not return a result
set.</jdbc:message>
</jdbc:exception>



Is there any other way to execute the same query/ the desired results.
Please help.

Sonia


--
smukher
------------------------------------------------------------------------
smukher's Profile: http://forums.novell.com/member.php?userid=95277
View this thread: http://forums.novell.com/showthread.php?t=429845