Sample Header Ad - 728x90

cmdshell query Error "not a valid identifier"

-1 votes
1 answer
574 views
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