I guess the threshold is a configured value...

Out of curiosity, isn't this a slight contradiction on...

Here is a very well performing solution which adds a covering filtered index, simply blazing fast.;-) Even without the index it is still more than three times faster than previous...

dwain.c (8/24/2014)

---

Maybe I'm missing something here but I think there's no reason to construct a delimited list at all.

SELECT a.CVID, a.JobNoticeID, IsMatch=CASE WHEN b.JobNoticeID IS NOT NULL THEN 1 ELSE...

That's an understatement if I ever seen one:-D

😎

TomThomson (8/24/2014)

---

up to there I agree with you 100%

because, and with absolutely no malice in my...

Here is yet another solution, similar to Lutz's apart from the grouping. Tends to be slightly quicker on small sets.

😎

USE tempdb;

GO

create table #test (name nvarchar (10),point_of_time datetime)

insert #test (name,point_of_time)

select 'd','2014-08-19...

vinu512 (8/24/2014)

---

LutzM (8/24/2014)

---

What permission does the user have who's running that code?

Based on MS Technet

Only members of the sysadmin fixed server role can execute sp_OASetProperty.

If you're not a member of...

Since you wrote such a long post Tom, I guess you really got it wrong:-P

😎

BTW thanks for the question Steve.

vinu512 (8/24/2014)

---

Eirikur Eiriksson (8/24/2014)

---

vinu512 (8/24/2014)

---

Eirikur Eiriksson (8/24/2014)

---

Further on Lutz's question, what is the output of the code in your initial post? Note that all the sp_OA* procedure return 0 if...

Here is a quick code demonstrating the grouping of the set, not a solution but should get you started. BTW, Itzik Ben-Gan and Dwain Camps have written excellent articles on...

JackTimber (8/24/2014)

---

Hi Eirikur ,

Actually i was trying at my end and due to slow internet connectivity downtown, i was not able reply to you.

Thanks for the solution :-).

I got a...

vinu512 (8/24/2014)

---

Eirikur Eiriksson (8/24/2014)

---

Further on Lutz's question, what is the output of the code in your initial post? Note that all the sp_OA* procedure return 0 if successful, otherwise a...

Further on Lutz's question, what is the output of the code in your initial post? Note that all the sp_OA* procedure return 0 if successful, otherwise a non-zero value.

😎

Quick thought, looks to me that the only thing missing is to enable Ole Automation Procedures

😎

Enable Ole Automation Procedures in T-SQL

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO