I'm a bit confused by your function Brian. To me it looks like this would need to be called for every one of the attributes, so in the main query...

Just a side note here but in 2005 xp_cmdshell is disabled by default (Surface Area Config) and can also be a pretty dangerous thing to allow. Ever think about using...

Ah yeah then I believe the only way is with a cursor...

Jim,

Is there a finite number of possible records in table2 that match up to the records in table1? If so you could do something like derived tables to return each...

Same w/ a developer...

I would write in a stored proc that checks against sysdatabases or sys.databases (2005)

if object_id(N'dbo.uspCheckTableExists') is not null

drop proc dbo.uspCheckTableExists

go

-- exec dbo.uspCheckTableExists 'mytable'

create proc dbo.uspCheckTableExists

@table varchar(256)

as

select

case

when count(*) > 0...

W/o actually testing it I can say that "IS NULL" is better then "IS NOT NULL" although not completely sargable. Run the execution plan on the various versions of your...

Thank you sir...this works perfect...

^^Does xp_sqlinventory exist in 2000 & 2005?

Sugesh,

We are not permitted to use xp_cmdshell for the security vulnerabilities which accompany it.

Agreed...but this isn't data that is being updated frequently by an application or anything so I put nolock's on there to make sure I'm not waiting on someone else that...

"Non-sargable search arguments in the WHERE clause, such as "IS NULL", "", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" generally prevents (but not always)...

Nope...

Thanks Noel...

I did just that (converted to using base tables) and am still getting a clustered index scan. I've checked and there are non-unique indexes on all join columns. One...

I think I found the issue...

table1 is actually a view so when I join to tables 2 and 3 since it's a non indexed view it would cause the clustered...

Hmmm...the only thing in my where clause that is non-sargable i know of would be my statement. Here's the query...any ideas?

select

a.CorrBD,

a.AccountNo,

a.SubsidiaryNo,

a.Name1,

a.Name2,

a.Name3,

a.Name4,

a.Name5,

a.Name6,

a.RepID,

a.AccountClass,

b.IRAType,

b.PartBirthDate,

c.Frequency,

c.SourceCode,

c.CycleBeginDate,

c.CycleEndDate,

c.Amount,

c.TransmissionDesc

from

table1 a with (nolock)

inner join table2 b with (nolock)...