hello friends...
i have a stored procedure as follows:
USE [cheminova]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_Firstdistil]
@SDate DateTime
AS
BEGIN
SET NOCOUNT ON;
declare @StartDate varchar(50)
declare @EndDate varchar(50)
set @EndDate=CONVERT(varchar,DATEPART(month,@SDate))+'/'+CONVERT(varchar,DATEPART(DAY,@SDate))+'/'+CONVERT(varchar,DATEPART(YEAR,@SDate))+' 06:00:00'
set @StartDate=CONVERT(varchar,DATEPART(month,DATEADD(day,-1,@SDate)))+'/'+CONVERT(varchar,DATEPART(DAY,DATEADD(day,-1,@SDate)))+'/'+CONVERT(varchar,DATEPART(YEAR,DATEADD(day,-1,@SDate)))+' 06:00:00'
Begin
create table #tempval
(
[ID] [int] IDENTITY(1,1) NOT NULL,
tagname varchar(100),
[timestamp] Varchar(50),
tagval varchar(50),
quality varchar(20)
)
create table #tempval1
(
[ID] [int] IDENTITY(1,1) NOT NULL,
tagname varchar(100),
[timestamp] Varchar(50),
tagval varchar(50),
quality varchar(20)
)
create table #tempval2
(
[ID] [int] IDENTITY(1,1) NOT NULL,
tagname varchar(100),
[timestamp] Varchar(50),
tagval varchar(50),
quality varchar(20)
)
create table #tempval3
(
[ID] [int] IDENTITY(1,1) NOT NULL,
tagname varchar(100),
[timestamp] Varchar(50),
tagval varchar(50),
quality varchar(20)
)
create table #tagavg
(
[ID] [int] IDENTITY(1,1) NOT NULL,
avgtag float
)
create table #temp1
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value][float] NULL
)
create table #temp2
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value][float] NULL
)
create table #temp3
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value][float] NULL
)
create table #temp4
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value][float] NULL
)
create table #temp5
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value][float] NULL
)
create table #tempdigival
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[timestamp] varchar(30),
tagval varchar(30)
)
create table #tempsum
(
tagval varchar(30),
calevent float
)
create table #tagtemp
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[timestamp] datetime,
tagname varchar(200),
descrip varchar(200),
unit varchar(10),
tagval varchar(38),
calevent float
)
create table #totalizervalue
(
[ID] [int] IDENTITY(1,1) NOT NULL,
tagname varchar(200),
waqt varchar(30),
tagval varchar(30)
)
create table #FinalResult
(
[ID] [int] IDENTITY(1,1) NOT NULL,
tag1 float,
tag2 float,
tag3 float,
tag4 float,
tag5 float,
tag6 float
)
DECLARE @SQL as varchar(1000)
Declare @Tagname varchar(300)
Declare @tagVal float
Declare @calval float
declare @avgtag float
declare @X1 float
declare @Y1 float
declare @Z1 float
declare @X2 float
declare @Y2 float
declare @Z2 float
declare @X3 float
declare @Y3 float
declare @Z3 float
declare @X4 float
declare @Y4 float
declare @Z4 float
declare @X5 float
declare @Y5 float
declare @Z5 float
declare @X6 float
declare @Y6 float
declare @Z6 float
declare @X7 float
declare @Y7 float
declare @Z7 float
declare @X8 float
declare @Y8 float
declare @Z8 float
declare @query varchar(500)
declare @starttime varchar(30)
declare @endtime varchar(30)
declare @coltagname varchar(300)
declare @coltimestamp datetime
declare @colavg varchar(38)
declare @prevtime varchar(30)
declare @currtime varchar(30)
declare @breakflag int
declare @firsttimeflag int
set @firsttimeflag=0
declare @final float
declare @initial float
declare @event float
Declare @digitalsql varchar(1000)
set @final=0
set @initial=0
set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''
set StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime
SELECT tagname, timestamp ,value,quality FROM ihRawData
WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1302'''' '')'
INSERT INTO #tempval1 EXEC(@SQL)
Set @Tagname='ADMIN-PC.Channel5.Device1.LIC1302'
select top 1 @X1= Cast(tagval as float) from #tempval1 order by ID asc
select top 1 @Y1=Cast(tagval as Float) from #tempval1 order by ID Desc
set @Z1=(@Y1-@X1)*11.08
Insert into #temp1 values (@Z1)
set @sql ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''
set StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=Calculated
SELECT tagname, timestamp ,value,quality FROM ihRawData
WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1304'''' '')'
INSERT INTO #tempval2 EXEC(@SQL)
select top 1 @X2= Cast (tagval as Float) from #tempval2 order by ID asc
select top 1 @Y2=Cast (tagval as Float) from #tempval2 order by ID desc
set @Z2=(@Y2-@X2)*6.02
Insert into #temp2 values (@Z2)
set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''
set StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime
SELECT tagname, timestamp ,value,quality FROM ihRawData
WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1403'''' '')'
INSERT INTO #tempval3 EXEC(@SQL)
SET @Tagname='ADMIN-PC.Channel5.Device1.LIC1403'
select top 1 @X3= Cast (tagval as Float) from #tempval3 order by ID asc
Select top 1 @Y3=Cast (tagval as Float) from #tempval3 order by ID Desc
set @Z3=(@Y3-@X3)*4.80
Insert into #temp3 values (@Z3)
set @query='select * from openquery(chemhist,''set StartTime="'+@starttime+'",EndTime="'+@endtime+'",SamplingMode=RawByTime,RowCount=0 select timestamp,value from ihrawdata where tagname=ADMIN-PC.Channel5.Device1.P65'')'
insert into #tempdigival exec(@query)
select @breakflag= COUNT(*) from #tempdigival where tagval=1
set @query='select * from openquery(chemhist,''set StartTime="'+@starttime+'",EndTime="'+@endtime+'",SamplingMode=RawByTime,RowCount=0 select tagname,timestamp,value from ihrawdata where tagname=ADMIN-PC.Channel5.Device1.LI1505'')'
insert into #totalizervalue exec(@query)
if (@breakflag>0)
begin
declare C1 cursor
for select timestamp from #tempdigival where tagval=1
open C1
fetch next from C1 into @currtime
while @@FETCH_STATUS=0
begin
if (@firsttimeflag=0)
begin
set @firsttimeflag=1
select @initial=cast(tagval as float)from #totalizervalue where CONVERT(datetime2,waqt)=CONVERT(datetime2,dateadd(SECOND,10, CONVERT(datetime2,@starttime)))
select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@currtime) order by waqt desc
set @final=(@final-@initial)
set @event=@final * 10
set @colavg=CONVERT(varchar,@event)
insert into #tempsum values(@colavg,@final)
set @prevtime=@currtime
end
else
begin
select top 1 @initial=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)>=CONVERT(datetime2,@prevtime)
select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@currtime) order by waqt desc
set @final=(@final-@initial)
set @colavg=CONVERT(varchar,@final)
insert into #tempsum values(@colavg,@final)
set @prevtime=@currtime
end
fetch next from C1 into @currtime
end
select top 1 @initial=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)>=CONVERT(datetime2,@prevtime)
select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@enddate) order by waqt desc
set @final=(@final-@initial)
set @event=@final * 10
set @colavg=CONVERT(varchar,@event)
insert into #tempsum values(@colavg,@final)
close C1
deallocate C1
select @colavg=SUM(CAST(tagval as float)) from #tempsum
set @coltagname='ADMIN-PC.Channel5.Device1.LIC1503'
set @coltimestamp=CONVERT(datetime,@endtime)
insert into #tagtemp values(@coltimestamp,@coltagname,'PDS slurry Flow Totaliser - 9A','m3',@colavg,@final)
end
else
begin
select @initial=cast(tagval as float)from #totalizervalue where CONVERT(datetime2,waqt)=CONVERT(datetime2,dateadd(SECOND,10, CONVERT(datetime2,@starttime)))
select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@enddate) order by waqt desc
set @final=(@final-@initial)
set @event=@final * 10
set @colavg=CONVERT(varchar,@event)
set @coltagname='ADMIN-PC.Channel5.Device1.LI1505'
set @coltimestamp=CONVERT(datetime,@endtime)
insert into #tagtemp values(@coltimestamp,@coltagname,'PDS slurry Flow Totaliser - 9A','m3',@colavg,@final)
end
set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''
set StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime
SELECT tagname, timestamp ,value,quality FROM ihRawData
WHERE tagname =''''ADMIN-PC.Channel5.Device1.FIQ1302'''' '')'
INSERT INTO #tempval EXEC(@SQL)
select top 1 @X5= Cast (tagval as Float) from #tempval order by ID asc
Select top 1 @Y5=Cast (tagval as Float) from #tempval order by ID Desc
set @Z5=(@Y5-@X5)*73.30
Insert into #temp5 values (@Z5)
END
set @avgtag=cast((isnull(@Z1,0)+isnull(@Z2,0)+isnull(@Z3,0)+isnull(@Z4,0)) as float)/cast((nullif((@Z5),0)) as float)
insert into #tagavg values (@avgtag)
Begin
insert into #finalResult select isnull(t1.Value,0) as R_21LT,isnull(t2.Value,0) as R_22LT,isnull(t3.Value,0) as R_24LT, isnull(t4.calevent,0) as B_22LT,isnull(t5.Value,0) as DETA_FIQ ,isnull(t6.avgtag,0) as avgtag from #temp1 as t1
Inner join #temp2 as t2
on t2.ID=t1.ID
Inner join #temp3 as t3
on t3.ID=t2.ID
Inner join #tagtemp as t4
on t4.ID=t3.ID
inner join #temp5 as t5
on t5.ID=t4.ID
inner join #tagavg as t6
on t6.ID=t5.ID
select tag1,tag2,tag3,tag4,tag5,tag6 from #FinalResult
End
END
and i have another stored procedure as follows
USE [cheminova]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_FinalYieldtemp]
@sdate datetime
AS
BEGIN
SET NOCOUNT ON;
declare @sql as varchar(1000)
Begin
create table #Temp1
(
[ID] [int] IDENTITY(1,1) NOT NULL,
tag1 float,
tag2 float,
tag3 float,
tag4 float,
tag5 float,
tag6 float
)
END
SET @SQL ='SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=ADMIN-PC;Trusted_Connection=yes;'',
''SET FMTONLY OFF; EXEC [cheminova].[dbo].[SP_Firstdistil] "'+convert(varchar(20),@sdate,103)+'" '')'
EXEC (@SQL)
END
and i execute SP_FinalYieldtemp as follows:
exec SP_FinalYieldtemp '2014-10-29'
but i m getting following error
Msg 8114, Level 16, State 5, Procedure SP_Firstdistil, Line 0
Error converting data type nvarchar to datetime.
what i have done here is, i have called SP_Firstdistil procedure with parameter @sdate in SP_FinalYieldtemp in stored procedure.
when i executed SP_Firstdistil procedure, it gave me proper output.
i m very confused in SET @SQL statement in SP_FinalYieldtemp procedure.
plz help to resolve it