How to resolve Error 7375 in Sql Server 2005

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.

Getting the Sum of Multiple Indicators by Row

New to the community and have limited experience with the subject. I'm trying to create a column that gets the sum of indicators row by row. So the column would total each indicator, giving me a total of 3 for the first customer, and 2 for the second. Using Microsoft Sql Server Mgmt Studio. Any help would be greatly appreciated!

Customer      Date       Ind1   Ind2   Ind3    Ind4
 12345       1-1-15       1     0      1       1
 12346       1-2-15       0     1      1       0

Someone pls, what it means this in the sql query "INDEX = PK_DPAGODOCUMENTO"

everyone ... im stuck with this query, that was lefted by another programmer, i search and search by really i had no clue, if someone can give me a guide i really appreciate:

The query says :

SELECT ... FROM dbo.DPAGODOCUMENTO AS T2 (INDEX=PK_DPAGODOCUMENTO) RIGHT OUTER JOIN dbo.MDOCUMENTO ... WHERE ...

where PK_DPAGODOCUMENTO is a CONSTRAINT [PK_DPAGODOCUMENTO] PRIMARY KEY CLUSTERED on the table [DPAGODOCUMENTO]

what really means the part "(INDEX=PK_DPAGODOCUMENTO)" and what it does, we are using MSSQL 2005, thanks in advance guys.

SQL Server: how to check inside the trigger is Insert/Update is done via stored pProcedure

Using SQL Sever 2005.

From inside a trigger on a table, is there a way to find out if the insert/update is being done via a stored procedure and if not throw an error?

How to move the root directory in sql server 2005

Can you please tell me how should I change the default root directory (used for db storage(MSSQL.1)) in SQL 2005 Std. Edition.

Currently its configured to: C:\Program Files(x86)\Microsoft SQL Server\MSSQL.1\MSSQL and I want to change the drive letter moving it to D drive

Thanks in advance.

Force "TOP 100 PERCENT" in EF "sub-query" queryable

Update: I was mistaken about top 100 percent generating a better query plan (the plan is still much better for a reasonably sized top N, and probably has to do with parameter sniffing).

While I still think this focused question has merit, it is not "a useful solution" for my problem², and might not be for yours ..

---

I am running into some queries which SQL Server optimizes poorly. The statistics appear correct, and SQL Server chooses the 'worse' plan that performs a seek over millions of records even though the estimated and actual values are the same - but this question is not about that¹.

In the problematic queries are of the simplified form:

select * from x
join y on ..
join z on ..
where z.q = ..

However (and since I know the cardinalities better, apparently) the following form consistently results in a much better query plan:

select * from x
join (
  -- the result set here is 'quite small'
  select top 100 percent *
  from y on ..
  join z on ..
  where z.q = ..) t on ..

In L2S the Take function can be used to limit to top N, but the "problem" I have with this approach is that requires a finite/fixed N such that some query could hypothetically just break, instead of just running really slow with the forced materialization.

While I could choose a 'very large' value for the top N this, ironically (wrt to the initial problem), increases the SQL query execution time as the value of N increases. The expected intermediate result is only expected to be a few dozen to a few hundred records. The current code I have runs a top 100 and then, if such was detected to contain too many results, runs the query again with a top 1000: but this feels like a kludge .. on top of a kludge.

The question is then: can a EF/L2E/LINQ query generate the equivalent of a top 100 percent on an EF Queryable?

(Forcing materialization via ToList is not an option because the result should be an EF Queryable and remain in LINQ to Entities, not LINQ to Objects.)

While I am currently dealing with EF4, if this is [only] possible in a later version of EF I would accept such as an answer - such is useful knowledge and does answer the question asked.

---

¹ If wishing to answer with "don't do that" or an "alternative", please make it is an secondary answer or aside along with an answer to the actual question being asked. Otherwise, feel free to use the comments.

---

² In addition to top 100 percent not generating a better query plan, I forgot to include the 'core issue' at stake, which is bad parameter sniffing (instance is SQL Server 2005).

The following query takes a very long to to complete while direct variable substitution runs "in the blink of an eye" indicating an issue with the parameter sniffing.

declare @x int
set @x = 19348659

select
op.*
from OrderElement oe
join OrderRatePlan rp on oe.OrdersElementID = rp.OrdersElementID
join OrderPrice op on rp.OrdersRatePlanID = op.OrdersRatePlanID
where oe.OrdersProductID = @x

The kludged-but-workable query

select
  op.*
from OrderPrice op
join (
  -- Choosing a 'small value of N' runs fast and it slows down as the
  -- value of N is increases where N >> 1000 simply "takes too long".
  -- Using TOP 100 PERCENT also "takes too long".
  select top 100
    rp.*
  from OrderElement oe
  join OrderRatePlan rp on oe.OrdersElementID = rp.OrdersElementID
  where oe.OrdersProductID = @x
) rp
on rp.OrdersRatePlanID = op.OrdersRatePlanID

SQL Check if a text contains a word

I have a Text,

'Me and you against the world' // false
'Can i have an email address'  // true
'This is an'  // true

I want to check whether the word an is inside my String.

How do I check if a text contains a specific word in SQL? I can't add a full-text catalog. Otherwies i could

SELECT * FROM TABLE WHERE CONTAINS(Text, 'an')