Session table contains a field data type not ...

rivalenterprises

Hi everybody,

I took an old Navision backup, done via SQL as a .bak file and I took it to my development server so I can make changes and do testing without upsetting my users.

After restoring the backup with SQL Management Studio, when I try to connect to the database I get the following error:

---

Microsoft Business Solutions-Navision

---

The Session table contains a Microsoft Business Solutions-Navision field data type that is not compatible with the SQL Server data type:

Field: Wait Time (ms)
Type: Integer
SQL type: BIGINT

---

OK

---

I'm running Navision 3.7 (3.70) on a Sql Server 2000 database.

What am I missing? How do I fix this?

Thanks,
Val

kine

I'm running Navision 3.7 (3.70) on a Sql Server 2000 database.

In my opinion it is not true. You are trying to open DB on MS SQL 2005 by 3.70 client... check it once again...

rivalenterprises

My production server is SQL 2000 server but my test server is a SQL 2005 server.

Do you think that is the problem? Is Navision 3.7 not compatible with SQL 2005?

Thanks,
Val

DenSter

That's right, SQL Server 2005 is supported only for NAV 4.0 SP1 and up.

kine

DenSter wrote:

That's right, SQL Server 2005 is supported only for NAV 4.0 SP1 and up.

Officially - but I recommend SP2 + update 1 for using MS SQL 2005. You know...

NavIndia

Hi Val,

Here is the solution for you problem, follow these steps:

1. On the computer that is running Microsoft SQL Server 2005, start the SQL Server Management Studio tool.

2. Expand Databases, expand the new Microsoft Navision database that you created, expand Views, and then expand System Views.

3. Under System Views, right-click dbo.Session, point to Script View as, point to ALTER To, and then click New Query Editor Window.

4. Locate the CAST() function, and then wrap it around the following code in the query:

SP.[waittime]

This code now appears as follows:
CAST(SP.[waittime] AS INT)

5. Press F5 to run the script.

After you follow these steps, you can open the Microsoft Navision database by using the Microsoft Navision 3.7 client.

Cheers \:D/
Sandeep

rivalenterprises

Hi everybody,

Thank you very much for all your help! I greatly appreciate it!

Val

ramiresreis

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[Sessão] AS
@SPID THEN 1 ELSE 0 END AS TINYINT) AS "Minha sessão",CONVERT(DATETIME, '1754-01-01 '+CONVERT(CHAR(8), SP.[login_time], 108), 120) AS "Hora conexão",CONVERT(DATETIME, CONVERT(CHAR(10), SP.[login_time], 120)+' 00:00:00:000', 121) AS "Data conexão",CAST (SD.[name] AS NVARCHAR(128)) COLLATE SQL_Latin1_General_CP1_CI_AS AS "Nome Base Dados",CAST (RTRIM(SP.[program_name]) AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP1_CI_AS AS "Nome aplicação",CASE WHEN SP.[nt_domain] <> '' THEN 1 ELSE 0 END AS "Tipo login",CAST(RTRIM(SP.[hostname]) AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP1_CI_AS AS "Servidor",SP.[cpu] AS "CPU Time (ms)",CASE WHEN SP.[memusage] < 0 THEN 0 ELSE SP.[memusage]*8 END AS "Utilização Memória (KB)",SP.[physical_io] AS "Physical I_O",CAST(CASE WHEN SP.[blocked] <> 0 THEN 1 ELSE 0 END AS TINYINT) AS "Bloqueado",CASE WHEN SP.[blocked] <> 0 THEN SP.[waittime] ELSE 0 END AS "Tempo Espera (ms)",CAST(SP.[blocked] AS INT) AS "Blocking Connection ID",CAST(ISNULL(RTRIM(SPB.[loginame]), '') AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP1_CI_AS AS "Blocking User ID",CAST(ISNULL(RTRIM(SPB.[hostname]), '') AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP1_CI_AS AS "Blocking Host Name",CAST(ISNULL(OBJECT_NAME(SPL.[rsc_objid]), '') AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP1_CI_AS AS "Blocking Object" FROM [master].[dbo].[sysprocesses] AS SP JOIN [master].[dbo].[sysdatabases] AS SD ON (SP.[dbid] = SD.[dbid]) LEFT OUTER JOIN [master].[dbo].[sysprocesses] AS SPB ON (SP.[blocked] = SPB.[spid]) LEFT OUTER JOIN [master].[dbo].[syslockinfo] AS SPL ON (SP.[waittime] = SPL.[req_spid] AND SP.[dbid] = SPL.[rsc_dbid] AND SPL.[req_spid] <> 0 AND SPL.[rsc_objid] <> 0 AND SPL.[req_status] = 3)
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


i had no chance!
error continues.

ramiresreis

sorry, solved with this, thanks all.

1. Start Management Studio and connect to your SQL Server.
2. Expand Databases, expand the Navision Database, and expand Views.
3. Right Click on the View dbo.Session and in the Drop Down Menu select
Script view as --> Create to --> New Editor Window.
4. Locate "THEN SP.[waittime]" and change the code to
"THEN CAST(SP.[waittime] AS INT)" (without ")
5. As the view already exists you have to change the CREATE VIEW
command to ALTER VIEW.

dayakar

Thats a great tip sandeep!

ThankQ