July 10, 2015 at 9:38 am
hi,
I'm having a problem with a insert as select to a table in a linked server, the command is inside a stored procedure, and looks like:
Insert into [linked server].[remote database].dbo.[tablaA]
select l1.field1, l2.field2, sum(field3)
from localtable1 L1 inner join localtable2 L2 .....
where L1.date >= @since
group by l1.field1, l2.field2
variable @since is loaded with first day of previous month (ie. for today: june 1, 2015)
If I run the SP from Managment Studio, results in 86239 rows inserted, querying the remote table returns the rows.
But when the stored procedure runs from scheduled job, it finish ok, logs 86239 rows affected, but queryng the remote table shows only 35016 rows :crazy:.
Verifing the data, in fact, is inserted to half way (for today up to june 20, 2015)
credentials used in the job are the same used to login in ssms.
Any one has any posible clue on what is happening?
Enviromen:
Both servers are Windows server 2008 64bits, SQL server 2008 R2 SP2
Server are connected in the same LAN.
thanks in advance
///EDIT: create reduced copy of stored procedure as folows:
CREATE PROCEDURE [dbo].[UPDATE_ADM_SIMPLFIED]
AS
declare @cantRows int =0
insert into UPD_ADM_Full_log values (getdate(), 'process start')
declare @since datetime
set @since = GETDATE()-42 --- simplified version
insert into UPD_ADM_Full_log values (getdate(), 'Since = ' + convert(varchar(26), @since, 121))
insert into UPD_ADM_Full_log values (getdate(), 'delete from [ADM].daz06.dbo.POR_COCHE')
delete from [ADM].daz06.dbo.POR_COCHE
where C_FECHA >= @since
declare @f as datetime
insert into UPD_ADM_Full_log values (getdate(), 'inserting into [ADM].daz06.dbo.POR_COCHE')
INSERT INTO [ADM].daz06.dbo.POR_COCHE
(C_EMPRESA, C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA)
select C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, SUM(C_BOLETOS_VIAJE), SUM(C_IMPORTE_VIAJE), SUM(C_BOLETOS_LIQUIDADO), SUM(C_IMPORTE_LIQUIDADO), SUM(C_BOLETOS_VENTA), SUM(C_IMPORTE_VENTA)
from (
select 6 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ06..por_coche
where c_fecha >= @since
union
select 15 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ15..por_coche
where c_fecha >= @since
union
select 18 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ18..por_coche
where c_fecha >= @since
UNION
select 27 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ27..por_coche
where c_fecha >= @since
UNION
select 32 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ32..por_coche
where c_fecha >= @since
UNION
select 37 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ37..por_coche
where c_fecha >= @since
) as todo
GROUP BY C_EMPRESA, C_LINEA, C_COCHE, C_FECHA
set @cantRows = @@rowcount --- get affected rows count
insert into UPD_ADM_Full_log values (getdate(), 'insert into [ADM].daz06.dbo.POR_COCHE = ' + cast(@cantRows as varchar(20)) + ' rows')
---- re do the same query but gettinf max date and count rows
select @f=max(x.c_fecha), @cantRows= count(1) from (
select C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, SUM(C_BOLETOS_VIAJE) BOLETOS_VIAJE, SUM(C_IMPORTE_VIAJE) IMPORTE_VIAJE, SUM(C_BOLETOS_LIQUIDADO) BOLETOS_LIQUIDADO,
SUM(C_IMPORTE_LIQUIDADO) IMPORTE_LIQUIDADO, SUM(C_BOLETOS_VENTA) BOLETOS_VENTA, SUM(C_IMPORTE_VENTA) IMPORTE_VENTA
from (
select 65 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ065..sube_por_coche with (nolock)
where c_fecha >= @since
union
select 151 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ151..sube_por_coche with (nolock)
where c_fecha >= @since
union
select 182 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ182..sube_por_coche with (nolock)
where c_fecha >= @since
UNION
select 276 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ276..sube_por_coche with (nolock)
where c_fecha >= @since
UNION
select 326 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ326..sube_por_coche with (nolock)
where c_fecha >= @since
UNION
select 327 as C_EMPRESA , C_LINEA, C_COCHE, C_FECHA, C_BOLETOS_VIAJE, C_IMPORTE_VIAJE, C_BOLETOS_LIQUIDADO, C_IMPORTE_LIQUIDADO,C_BOLETOS_VENTA, C_IMPORTE_VENTA
from DAZ327..sube_por_coche with (nolock)
where c_fecha >= @since
) as todo
GROUP BY C_EMPRESA, C_LINEA, C_COCHE, C_FECHA) x;
SET CONCAT_NULL_YIELDS_NULL OFF --- in case a value returns null
insert into UPD_ADM_Full_log values (getdate(), 'max selected c_fecha from query for POR_COCHE = ' + convert(varchar(24), @f ,120) + ' - selected rows = ' + cast(@cantRows as varchar(10)))
--- same max and count from remote table
select @f= max(c_fecha), @cantRows=count(1) from [ADM].daz06.dbo.POR_COCHE where c_fecha >= @since
insert into UPD_ADM_Full_log values (getdate(), 'max inserted c_fecha from [ADM].daz06.dbo.POR_COCHE = ' + convert(varchar(24), @f ,120) + ' - selected rows = ' + cast(@cantRows as varchar(10)))
insert into UPD_ADM_Full_log values (getdate(), 'Process end')
GO
This sp logs activity in a table, no error is returned by the procedure on any moment.
Results when run manualy from SSMS console:
fechahora log_text
2015-07-13 08:12:20.190Process start
2015-07-13 08:12:20.520Since = 2015-06-01 08:12:20.520
2015-07-13 08:12:20.527delete from [ADM].daz06.dbo.POR_COCHE
2015-07-13 08:12:34.997inserting into [ADM].daz06.dbo.POR_COCHE
2015-07-13 08:14:46.347insert into [ADM].daz06.dbo.POR_COCHE = 83197 rows
2015-07-13 08:14:46.830max selected c_fecha from query for POR_COCHE = 2015-11-11 00:00:00 - selected rows = 83197
2015-07-13 08:14:48.093max inserted c_fecha from [ADM].daz06.dbo.POR_COCHE = 2015-11-11 00:00:00 - selected rows = 83197
2015-07-13 08:14:48.093Process end
Everything is correct, but:
Results when run from job schedule:
fechahora log_text
2015-07-13 07:00:00.120process start
2015-07-13 07:00:00.130Since = 2015-06-01 07:00:00
2015-07-13 07:00:00.130delete from [ADM].daz06.dbo.POR_COCHE
2015-07-13 07:01:22.520inserting into [ADM].daz06.dbo.POR_COCHE
2015-07-13 07:01:49.180insert into [ADM].daz06.dbo.POR_COCHE = 83149 rows
2015-07-13 07:01:49.680max selected c_fecha from query for POR_COCHE = 2015-11-11 00:00:00 - selected rows = 83149
2015-07-13 07:01:51.177max inserted c_fecha from [ADM].daz06.dbo.POR_COCHE = - selected rows = 0
2015-07-13 07:01:51.177Process end
As you see the select from destination table has no rows from the 83149 inserted rows returned by previous command.
how is that posible?
EDIT2: I found that the difference happens if I run the Stored procedure with or without exec,
if run "Exec UPDATE_ADM_SIMPLFIED" the error ocurs, the insertion fails with no error reported.
But if I run "UPDATE_ADM_SIMPLFIED" from Managment studio without the "Exec" key word it works correctly.
has anyone seen this kind of behavior before?
July 10, 2015 at 9:56 am
Any chance the remote table has a constraint on the date field?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2015 at 2:43 pm
Hi, No, the destination table has no FK, no triggers, no chek constraints, only PK and a non clustered non unique index.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply