|
|
USE [srgprs]
|
|
|
GO
|
|
|
/****** Object: StoredProcedure [dbo].[ProcP2] Script Date: 08/25/2010 23:36:18 ******/
|
|
|
SET ANSI_NULLS ON
|
|
|
GO
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
|
GO
|
|
|
create PROCEDURE [dbo].[ProcP2]
|
|
|
-- Add the parameters for the stored procedure here
|
|
|
@provaID varchar(10),
|
|
|
@etapaID varchar(10),
|
|
|
@cnnStr nvarchar(200)
|
|
|
AS
|
|
|
BEGIN
|
|
|
SET NOCOUNT OFF;
|
|
|
-- temporary data
|
|
|
delete from tmp_sync
|
|
|
declare @str varchar(200)
|
|
|
select @str = 'select veiculoid, convert( varchar(20), max(data), 120 ) from rotaSMS where provaid = ' + @ProvaID + ' and etapaid = ' + @etapaid + ' group by veiculoid'
|
|
|
exec ('insert into tmp_sync Select * from OPENROWSET(''SQLNCLI'',''' + @cnnStr + ''',''' + @str + ''') AS a;')
|
|
|
|
|
|
|
|
|
declare @str1 varchar(max);
|
|
|
select @str1 = 'SELECT [VeiculoID],[ProvaID],[SafetyBoxID],[EtapaID],[data],[latitude],[longitude]'+
|
|
|
',[velocidade],[rumo],[altitude],[satelites],[inputs],[outputs],[customInput_1],[customInput_2],[razaoSMSID] FROM FuncTable ( ' + @provaID + ', ' + @etapaID + ') '
|
|
|
|
|
|
--[VeiculoID],[ProvaID],[SafetyBoxID],[EtapaID],[data],[latitude],[longitude],[velocidade],[rumo],[altitude],[satelites],[inputs],[outputs],[customInput_1],[customInput_2],[razaoSMSID]
|
|
|
|
|
|
exec ('insert into OPENROWSET(''SQLNCLI'',''' + @cnnStr + ''', ''select [VeiculoID],[ProvaID],[SafetyBoxID],[EtapaID],[data],[latitude],[longitude],[velocidade],[rumo],[altitude],[satelites],[inputs],[outputs],[customInput_1],[customInput_2],[razaoSMSID] from rotasms'') ' + @str1 + ' ' )
|
|
|
|
|
|
print @str1
|
|
|
END
|