cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Unexpected parameter errors after migration to SQL server

Zeiad-Alkassem
12-Amethyst

Unexpected parameter errors after migration to SQL server

Hallo community,

 

in the last week, we have migrated our data base from oracle to sql server. Every thing was fine and works flowiness.

i was used the command "im diag --diag=slowqueries"  to get the slow queries, it was worked fine, but after the migration i got in the result message under the section "Plan"the following error:

....

Plan: Unexpected parameter marker at position 340.

...

Plan: Unexpected parameter marker at position 474.

....

i tried to find the cause for that , but nothing abnormal.

Integrity : v10.8.

Any possible help will be welcome.

 

with all my respect.

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hello Zeiad,

 

This occurs because many queries within Integrity/Windchill RV&S are parameterized and these parameters get represented as a question mark, which you can see in the server log if you enable the SQL logging category. In the below example SQL statement, we can see the parameter is the item ID and its parameter value is found within the square braces at the end of the statement:

SELECT ID,J.C1/*ALM_Product Manager*/ FROM Issues LEFT JOIN (
  SELECT Issues.ID TargetID,AssignedUserID/*Assigned User*/ C1 FROM Issues
) J ON J.TargetID = Field73 WHERE ID=?  [4516]: 1/9ms+339Ms

 

When running the queryplan diag or some others which analyze the underlying SQL statements, SQL Server does not know how to handle the question marks representing the parameters and this has always been the case. To find the execution plans of specific SQL, I would suggest some different options:

     1) Use the SQLPLAN diagnostic

          im diag --diag=sqlplan --param="Insert SQL statement here"

          Note: the SQL statement entered into the command needs to have the parameter values substituted, otherwise you will get the same error message about an unexpected marker.

 

     2) Obtain the execution plan from the database directly.

          This may vary from one MS SQL version to another but we have a knowledgebase article giving some steps to collect an execution plan. See article CS144653.

View solution in original post

2 REPLIES 2

Hello Zeiad,

 

This occurs because many queries within Integrity/Windchill RV&S are parameterized and these parameters get represented as a question mark, which you can see in the server log if you enable the SQL logging category. In the below example SQL statement, we can see the parameter is the item ID and its parameter value is found within the square braces at the end of the statement:

SELECT ID,J.C1/*ALM_Product Manager*/ FROM Issues LEFT JOIN (
  SELECT Issues.ID TargetID,AssignedUserID/*Assigned User*/ C1 FROM Issues
) J ON J.TargetID = Field73 WHERE ID=?  [4516]: 1/9ms+339Ms

 

When running the queryplan diag or some others which analyze the underlying SQL statements, SQL Server does not know how to handle the question marks representing the parameters and this has always been the case. To find the execution plans of specific SQL, I would suggest some different options:

     1) Use the SQLPLAN diagnostic

          im diag --diag=sqlplan --param="Insert SQL statement here"

          Note: the SQL statement entered into the command needs to have the parameter values substituted, otherwise you will get the same error message about an unexpected marker.

 

     2) Obtain the execution plan from the database directly.

          This may vary from one MS SQL version to another but we have a knowledgebase article giving some steps to collect an execution plan. See article CS144653.

Thank you @JoeBartlett  for your reply.

This suggestion will solve the Problem

 

Regards 

Zeiad

Announcements

Windchill RVS Users


Please take our

Windchill RV&S Authoring with Formula Editor Survey