Problem reading sql table from nav.

makina

Hi Guys!!!
We are facing some problems when we try to read from nav a table that is shared with other applications and it is filled every day with dat from POS. It seems that findset/findsfirst instructions don't work. Furthermore, if we open page and we try to set filters they don't work either. The primary key of the table is an integer value - record number - , and I have checked that there aren't two rows with the same record number.

Nav version : 2013R2.
SQL Version: 2008R2.

Any idea of what is wrong?

Thanks in advance.

P.S.
The table is filled with the following sql server sentences....



DECLARE @Jornada nvarchar(8)
SET @Jornada = '20140226'--convert(varchar(8), GETDATE(), 112)

DECLARE @i int

DELETE FROM RCSG2013R2.dbo.[RCGS$Ventas TPV NEO] WHERE Fecha=@Jornada


SELECT @i = coalesce(MAX([Nº Registro]),0) FROM [RCSG2013R2].[dbo].[RCGS$Ventas TPV NEO]

INSERT INTO RCSG2013R2.dbo.[RCGS$Ventas TPV NEO] ([Nº Registro], Tipo, Fecha, Factura, Cliente, Importe, TipoPago, ImportePago,
CodigoArt, CantidadArt, ImporteArt, TipoIVA, [Origen Venta], [Nº linea], Traspasado)
SELECT (@i + ROW_NUMBER() over (order by (select 1))), 'Cabecera', Jornada, Serie + '-' + NumeroFactura, Cliente, Importe, CajaCobro, 0,
Zona + ' ' + PuestoConsumo, 0, 0, '', 'Restaurante', 1, 0
FROM NEODB.dbo.TL_TicketsCabecera WHERE Jornada=@Jornada AND NumeroFactura > 0 AND UnidadOperativa='0001'

SELECT @i = coalesce(MAX([Nº Registro]),0) FROM [RCSG2013R2].[dbo].[RCGS$Ventas TPV NEO]

INSERT INTO RCSG2013R2.dbo.[RCGS$Ventas TPV NEO] ([Nº Registro], Tipo, Fecha, Factura, Cliente, Importe, TipoPago, ImportePago,
CodigoArt, CantidadArt, ImporteArt, TipoIVA, [Origen Venta], [Nº linea], Traspasado)
SELECT (@i + ROW_NUMBER() over (order by (select 1))), 'Detalle', L.Jornada, C.Serie + '-' + C.NumeroFactura, C.Cliente, 0, '', 0, L.Codigo,
L.Cantidad, L.Importe, COALESCE(L.TipoGestionImpuesto, ''), 'Restaurante', L.Linea, 0
FROM NEODB.dbo.TL_TicketsLineas L INNER JOIN NEODB.dbo.TL_TicketsCabecera C ON L.Jornada = C.Jornada AND L.Numero = C.Numero AND
L.UnidadOperativa = C.UnidadOperativa
WHERE L.Jornada=@Jornada AND C.NumeroFactura > 0 AND L.AfectaImporte = 'S' AND C.UnidadOperativa='0001'


SELECT @i = coalesce(MAX([Nº Registro]),0) FROM [RCSG2013R2].[dbo].[RCGS$Ventas TPV NEO]

INSERT INTO RCSG2013R2.dbo.[RCGS$Ventas TPV NEO] ([Nº Registro], Tipo, Fecha, Factura, Cliente, Importe, TipoPago, ImportePago,
CodigoArt, CantidadArt, ImporteArt, TipoIVA, [Origen Venta], [Nº linea], Traspasado)
SELECT (@i + ROW_NUMBER() over (order by (select 1))), 'Pagos', P.Jornada, C.Serie + '-' + C.NumeroFactura, C.Cliente, 0, P.Tipo, P.Importe,
'', 0, 0, '', 'Restaurante', P.Linea, 0
FROM NEODB.dbo.TL_TicketsPagos P INNER JOIN NEODB.dbo.TL_TicketsCabecera C ON P.Jornada = C.Jornada AND P.Numero = C.Numero AND
P.UnidadOperativa = C.UnidadOperativa
WHERE P.Jornada=@Jornada AND C.UnidadOperativa='0001' AND C.NumeroFactura > 0

kriki

NAV2013 (R2) caches the data in the servicetier and doesn't expect someone else (another servicetier is ok) to change the data in its tables.

Try to use SELECTLATESTVERSION before doing anything else in NAV. This tells the service tier to get the data from the database and not from its cache (at least I hope it does).