|
Hi,
I am facing issue in stored procedure
USE [RP32TEST]
GO
/****** Object: StoredProcedure [dbo].[AAAAAGPS] Script Date: 12/28/2015 4:42:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[AAAAAGPS]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @strquery as varchar(5000)
declare @routecode as nvarchar(50)
declare @weekcount as int
declare @filename as varchar(500)
declare @fileupdated as int
declare @Updatedate as date
declare @FileQuery as varchar (5000)
declare @filePath as nvarchar(max)
declare @companyCode as numeric (18)
declare @RouteCategory as numeric(18)
Declare @emailBody as nvarchar (500)
SET NOCOUNT ON;
insert into ExcelFilePath (filename)
exec master..xp_cmdshell 'DIR E:\GPS\*.* /TC'
DELETE FROM excelfilepath
WHERE filename NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR filename LIKE '%<DIR>%'
OR filename IS NULL
update ExcelFilePath set Filename = Substring(Filename,40,100),Date = SYSDATETIME(),updated = 0
set @FileQuery = 'SELECT Filename
FROM ExcelFilePath
where updated = 0'
EXEC('DECLARE route_cursor1 CURSOR FOR ' + @FileQuery)
OPEN route_cursor1
--Fetch first rec in givne variable
FETCH NEXT FROM route_cursor1
INTO @filename
WHILE @@FETCH_STATUS = 0
BEGIN
set @filePath = 'Excel 12.0;IMEX=1;Database=E:\GPS\' + @filename
print(@filepath)
exec ('INSERT INTO New_temp_table
-- Insert statements for procedure her
select [ROUTE CODE],[VSR NAME],[USER ID],[CUST NO],[CUSTOMER NAME],[LATITUDE],[LONGTITUDE],[ANCUSTOMER]
from
( SELECT a.[RT NO#] as RouteCode,a.[VSR Name] as NAME,a.[USER ID] as id,a.[CUST NO#],a.[CUSTOMER NAME],a.LAT,a.LONG,a[ANCUSTOMER]
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''' + @filepath + ''',''
select * from [JPLAN$]'') as a
where LAT > 0 )test
')
--- paste here
update ExcelFilePath set updated = 1 , UpdateDateTime = sysdatetime() where filename = @filename
declare @DelFilequery as varchar(300)
declare @DelFilePath as varchar(400)
DECLARE @hr int
DECLARE @ole_FileSystem int
set @DelFilePath = 'E:\GPS\' + @filename
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',
@ole_FileSystem OUT
EXEC @hr = sp_OAMethod @ole_FileSystem, 'DeleteFile',
NULL, @DelFilePath
EXEC @hr = sp_OADestroy @ole_FileSystem
--update CustomerMaster set ActiveCustomer = 1 where CustomerCode in (select CustomerCode from RouteSequence )
--update CustomerMaster set ActiveCustomer = 0 where CustomerCode not in (select CustomerCode from RouteSequence ) and ActiveCustomer = 1
FETCH NEXT FROM route_cursor1
INTO @filename
END --while
CLOSE route_cursor1
DEALLOCATE route_cursor1
END
Regards
Adnan
|
