I've included the entire code
USE [JCIHistorianADX2New]
GO
/****** Object: StoredProcedure [dbo].[GetADX2PointsDyn] Script Date: 10/18/2021 5:45:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- =============================================
-- Author: FLG
-- Create date: 10/15/2021
-- Description: Process ADX2 Points
-- =============================================
ALTER PROCEDURE [dbo].[GetADX2PointsDyn]
-- Add the parameters for the stored procedure here
@dSDate datetime = '2021/01/01 00:00:00',
@dEDate datetime = '2021/01/01 23:45:00'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Create Table #Points
(
EMSPointName varchar(100) NOT Null
);
BULK INSERT #Points
FROM 'C:\Trend Point Map 101521.csv'
WITH
(
FIRSTROW = 2, -- as 1st one is header
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = '\n', --Use to shift the control to next row
TABLOCK
)
-- Insert statements for procedure here
Alter Table #Points
Add ID int Identity(1,1)
DECLARE
@Init int= 1,
@NumRows int
SELECT @NumRows= COUNT(*) FROM #Points WHERE ID= @Init
WHILE @Init= @dSDate and UTCDateTime <= @dEDate
AND Exists(
Select t.EMSPointName
from #Points T
Where t.EMSPointName = [dbo].RawAnalog.PointName)'
Print 'SQL'
Print @SQL
set @cmd = N'master.dbo.xp_cmdshell ''BCP ' + '"' + @SQL +'"' + ' queryout E:\Honeywell\GSA\Test\ADX2Test.txt -c -t, -S (local) -T '''
--set @cmd = 'BCP + @SQL + QUERYOUT + @path + "ADX2Test.txt" -c -t, -S (local) -T'
Print 'CMD'
Print @cmd
Exec @cmd
SET @Init= @Init + 1
END
SELECT @dSDate, @dEDate
END
Asked by gunterl
(1 rep)
Oct 17, 2021, 11:46 AM
Last activity: Oct 18, 2021, 12:47 PM
Last activity: Oct 18, 2021, 12:47 PM