I m using Sql Server 2005 to develop report. for that I have created linked server to proficy historian in sql server 2005. I have a following stored procedure.
declare @batch varchar(50)
set @batch='LT20623'
drop table #batchdata
create table #batchdata
(
[value] varchar(20),
[timestamp] datetime
)
drop table #batchdata2
create table #batchdata2
(
tagname varchar(50),
[timestamp] varchar(50),
[value] varchar(30)
)
insert into #batchdata select * from Batch_1 order by timestamp desc
declare @query varchar(500)
declare @starttime datetime
declare @endtime datetime
declare @subsequenttag varchar(50)
set @starttime=(select min(timestamp) from #batchdata where value=@batch)
declare @subsequentcount int
set @subsequentcount=(select count(*) from #batchdata where timestamp>@starttime)
if (@subsequentcount =0)
begin
select @endtime=getdate()
end
else
begin
select @endtime=min(timestamp) from #batchdata where value!=@batch and timestamp>@starttime
end
select @starttime as starttime, @endtime as endtime
declare @stime varchar(20)
declare @etime varchar(20)
set @stime=convert(varchar,datepart(month,@starttime))+'/'+convert(varchar,datepart(day,@starttime))+'/'+convert(varchar,datepart(year,@starttime))+' '+convert(varchar,datepart(hour,@starttime))+':'+convert(varchar,datepart(minute,@starttime))+':'+convert(varchar,datepart(second,@starttime))
set @etime = convert(varchar,datepart(month,@endtime))+'/'+convert(varchar,datepart(day,@endtime))+'/'+convert(varchar,datepart(year,@endtime))+' '+convert(varchar,datepart(hour,@endtime))+':'+convert(varchar,datepart(minute,@endtime))+':'+convert(varchar,datepart(second,@endtime))
set @query='select * from openquery(PIMS_HIST,''set fmtonly OFF starttime="'+@stime+'",endtime="'+@etime+'",samplingmode=rawbytime,rowcount=0 select tagname,timestamp,value from ihrawdata where tagname=PIMS-LOTE-1.LOTE.PIMS.Global.AIPV_28'')'
insert into #batchdata2 exec(@query)
select * from #batchdata2
when I execute the above created procedure it throws an error 7357. Please help me.