Error in Session Monitor (SQL server)

roro

Hello,

when I run Form 150014 with Navision 4 SP3 (build24449) on MS SQL Server 2005 (build 9.0.3042) I get next Error:

The following SQL Server error(s) occured while accessing the Session (SQL) table:
536,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid length parameter passed to the SUBSTRING function.

Table 150014 Session (SQL) is compiled.

Does anybody know, how to solve this problem?

With Best Regards

Roman Rous

David_Singleton

roro wrote:

Hello,

when I run Form 150014 with Navision 4 SP3 (build24449) on MS SQL Server 2005 (build 9.0.3042) I get next Error:

The following SQL Server error(s) occured while accessing the Session (SQL) table:
536,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid length parameter passed to the SUBSTRING function.

Table 150014 Session (SQL) is compiled.

Does anybody know, how to solve this problem?

With Best Regards

Roman Rous

Interesting, I have never heard of Table or Form 150014. This range is close to the range used for NSC tools such as the Upgrade toolkit, so maybe its something for upgrading Czech versions.

Do the same objects run in an earlier version?

DenSter

It's the extended Client Monitor objects. Did you create the view on SQL Server?

roro

Yes, view is created by next script:

--DROP VIEW [Session (SQL)]
go
CREATE VIEW [Session (SQL)] AS
SELECT
SP.[spid] AS [Connection ID],
RTRIM(SP.[loginame]) AS [User ID],
SD.[name] AS [Database Name],
@SPID THEN 1 ELSE 0 END AS TINYINT) AS [My Session],
CONVERT(DATETIME, CONVERT(CHAR(10), SP.[login_time], 120) + ' 00:00:00:000', 121) AS [Login Date],
CONVERT(DATETIME, '1754-01-01 ' + CONVERT(CHAR(8), SP.[login_time], 108), 120) AS [Login Time],
RTRIM(SP.[program_name]) AS [Application Name],
RTRIM(SP.[hostname]) AS [Host Name],
CAST(CASE WHEN SP.[blocked] > 0 THEN 1 ELSE 0 END AS TINYINT) AS [Blocked],
CAST(SP.[blocked] AS INTEGER) AS [Blocked by Connection ID],
COALESCE(RTRIM(SP2.[loginame]),'') AS [Blocked by User ID],
COALESCE(RTRIM(SP2.[hostname]),'') AS [Blocked by Host Name],
CAST(SP.[waittime] AS decimal(38, 20)) AS [Waiting Time (ms)],
CAST(SP.[cpu] AS decimal(38, 20)) AS [CPU],
CAST(SP.[memusage] AS decimal(38, 20)) [Memory Usage],
CAST(SP.[physical_io] AS decimal(38, 20)) AS [Physical I/O],
CAST(
CASE CHARINDEX('KEY:',SP.[waitresource])
WHEN 1 THEN
CASE WHEN SP.[blocked] > 1 THEN
RTRIM(OBJECT_NAME(SUBSTRING(SUBSTRING(SUBSTRING(SP.[waitresource],CHARINDEX(':',SP.[waitresource])+1,50),CHARINDEX(':',SUBSTRING(SP.[waitresource],CHARINDEX(':',SP.[waitresource])+1,50))+1,50),1,CHARINDEX(':',SUBSTRING(SUBSTRING(SP.[waitresource],CHARINDEX(':',SP.[waitresource])+1,50),CHARINDEX(':',SUBSTRING(SP.[waitresource],CHARINDEX(':',SP.[waitresource])+1,50))+1,50))-1)))
ELSE ''
END
ELSE ''
END AS VARCHAR(100))
AS [Wait Resource]
FROM [master].[dbo].[sysprocesses] AS SP
JOIN [master].[dbo].[sysdatabases] AS SD ON (SP.dbid = SD.dbid)
LEFT OUTER JOIN [master].[dbo].[sysprocesses] AS SP2 ON (SP.[blocked] = SP2.[spid])
WHERE SP.[program_name] <> ''


I aslo can't find information how to understand value in Wait Resource column. Does anybody know it?

Roman

roro

RE: Do the same objects run in an earlier version?

Yes, on MS SQL server 2000 theese objects work good.

Roman

kine

It can be connected to MS SQL 2005 in two ways:
a) permissions (try it with sys_admin rights on MS SQL)
b) master data structure - the view is decoding some things from data and the data structure changed between MS SQL 2000 and 2005. - in this case you need some new version of the view...