Troubleshooting SQL Scripts

From QPR ProcessAnalyzer Wiki
Revision as of 19:53, 2 December 2018 by Ollvihe (talk | contribs)
Jump to navigation Jump to search

This page goes though some common problem situations in QPR ProcessAnalyzer scripts. After a script has been run, the Script Log dialog is shown. You can use it to troubleshoot your script.

Invalid Object Name

Issue: Invalid object name exception is thrown when running a script with #GetAnalysis and #ImportEvents commands.

The issue may arise when there are no events to import, i.e. the #GetAnalysis command doesn't create any table. In this case, the missing event table needs to be created in the script between the #GetAnalysis and #ImportEvents commands: 

/*This is the GetAnalysis part */
(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '10') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis

/*Here we create the event table, if it is missing */
IF object_id('tempdb..#AnalysisResult') is  null
BEGIN
CREATE TABLE #AnalysisResult (
CaseName NVARCHAR(MAX),
EventTypeName NVARCHAR(MAX),
TimeStamp DATETIME,
Cost FLOAT,
TotalCost FLOAT
);
END

/*Then we use the ImportEvents */
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
(SELECT * FROM [#AnalysisResult])
--#ImportEvents

Incorrect Syntax When Using ' Characters

Issue: SQL error: Incorrect syntax near '

If you get this error message, check the ' characters in the parameter definitions. Note that you cannot use the ' character in the parameter value.

Incorrect Syntax When Running Scripts Copied from QPR ProcessAnalyzer Wiki

Issue: SQL error: Incorrect syntax

You may get this kind of error message when trying to run a script that you have copied and pasted directly from, for example, the examples given in Supported QPR ProcessAnalyzer Commands in Scripts. This happens because the scripts are then likely to contain non-breaking spaces (encoded as 0xA0 but not visible when pasted to the Script Code field) which cause the running of the scripts to fail as they are not accepted in the SQL syntax.

The solution is to manually remove the non-breaking spaces after copying and pasting the script, so there are no extra spaces at the beginning of each line, for example.

Using "##" in a Script

Issue: Using "##" in a script is not allowed. Did you mistype temporary table name?

This error message is displayed if the name of the project or model name contains two consecutive # characters (i.e. ##) and you try creating a new project or a model using scripts. Note that you cannot use the ## character in the name of the project or the model.

The ? Character in Project and Model Names

Issue: A project or model name contains question marks.

The problem arises if you try to, for example, create a project or a model with a name containing multibyte characters using scripts. The created project or model will then include question mark characters instead of multibyte characters. The fix is to add a prefix N' to the name containing multibyte characters (for example, N'【隱藏▲】【純文字檢視】【複製到剪貼簿】'). Another workaround is not to use multibyte characters at all in scripts but instead ProjectId and ModelId, if possible. To do that, you can view the Id column in the Models tab of the Workspace dialog in the same way as described for data tables in Adding an Id Column to the Data Tables List.

Multiple Projects with the Same Name

Issue: There are multiple projects with name '<ProjectName>'. Please, specify ProjectId in sandbox script operation: #<Operation>

This error message is displayed when trying to run a script if you have access to two or several projects with the same name and you refer to the project by name in the script. The fix for resolving this ambiguity is to refer to the project by its ID using the 'ProjectId' parameter instead of 'ProjectName'. An ID is always unique, whereas a name is not.

Multiple Models with the Same Name

Issue: There are multiple models with name '<ModelName>'. Please, specify ModelId in sandbox script operation: #<Operation>

This error message is displayed when trying to run a script if you have access to two or several models with the same name and you refer to the model by name in the script. The fix for resolving this ambiguity is to refer to the model by its ID using the 'ModelId' parameter instead of 'ModelName'. An ID is always unique, whereas a name is not.

String Comparisons Not Being Correct

Issue: Different characters are considered as similar and my analysis or script is affected.

Due to the way SQL Server handles string comparisons, comparing attribute values that are in non-western character sets may result in values that are not similar to be treated as they were. For example the following characters are considered the same as the space character: Ǹ, ǹ, Ș, ș. This can have an effect in analyses using the attributes, i.e. Profiling Analysis, Influence Analysis, as well as Benchmarking and attribute value filtering and scripts that are run. To overcome this, before importing the attribute values, cast the SQL Variants holding the values into VARBINARY(MAX).

For example, the following script will show characters that are treated similar as zero "0": 

DECLARE @referenceAscii int = 0

IF object_id('tempdb..#ids') is null 
CREATE TABLE #ids ( id int)

DECLARE @i int = 0
WHILE @i < 70000 BEGIN
    SET @i = @i + 1
    INSERT #ids VALUES (@i)
END

select * FROM (
  select 
    sys.fn_varbintohexstr(CONVERT(varBINARY(8), id)) as CharacterAsHex,
    id as ASCIICode,
    nchar(id) as Character,
   (CASE WHEN Nchar(@referenceAscii) = NCHAR(id) THEN 1 ELSE 0 END) as ShownAsSimilar 
  FROM #ids ) as x
where x.ShownAsSimilar = 1
--#ShowReport

Client Exception When Using #ImportSapQuery Command

Issue: Limitations in size of SAP query result.

The SAP system has limitations as to how much data can be queried. The workaround for the error is to reduce the amount of data to be queried using 'SapRowcount', 'SapRowskips', 'SapWhereClauseSelect', 'SapFieldNames' and 'SapFunction' parameters. See --#ImportSapQuery for parameter explanations.

Issue: Client exception is thrown when running a script with #ImportSapQuery command using the 'SapWhereClauseSelect' parameter.

For example, the following error text can be displayed: 

 #ImportSapQuery: SAP.Middleware.Connector.RfcAbapRuntimeException: SAP.Middleware.Connector.RfcAbapRuntimeException:
 Error in module RSQL of the database interface
   at SAP.Middleware.Connector.RfcConnection.ThrowRfcErrorMsg()
   at SAP.Middleware.Connector.RfcConnection.ReadBytes(Byte* buffer, Int32 count)
   at SAP.Middleware.Connector.RfcConnection.ReadRfcIDBegin(Int32& length)
   at SAP.Middleware.Connector.RfcConnection.ReadUpTo(RFCGET readState, RfcFunction function, RFCID toRid)
   at SAP.Middleware.Connector.RfcConnection.RfcReceive(RfcFunction function)
   at SAP.Middleware.Connector.RfcFunction.RfcDeserialize(RfcConnection conn, IRfcIOStream stream)
   at SAP.Middleware.Connector.RfcFunction.RfcCallReceive(RfcConnection conn, IRfcIOStream stream, RFCID rid)
   at SAP.Middleware.Connector.RfcFunction.Invoke(RfcDestination destination)
   at Qpr.ProcessAnalyzer.Common.Integration.Sap.SapQuery(RfcDestination destination, Action`2 getSapWhereClauseSelect, Boolean enableStreamCompression, IDictionary`2 parameters)
   System.ServiceModel.FaultException`1[Qpr.Utils.SapError]: SAP.Middleware.Connector.RfcAbapRuntimeException: Error in module RSQL of the database interface
   (Fault Detail is equal to SAP.Middleware.Connector.RfcAbapRuntimeException: Error in module RSQL of the database interface
This issue may arise when you are extracting data from SAP and the size of 'SapWhereClauseSelect' parameter value is too large. Reduce the size of the 'SapWhereClauseSelect' value.
Retrieved from "https://wiki.onqpr.com/pa/index.php?title=Troubleshooting_SQL_Scripts&oldid=11945"