Intermittent Error

[Frothingslosh]

This is getting aggravating.

I have an app that has recently started randomly returning the following error from SQL Server when executing a procedure from Access:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter passed to the LEFT or SUBSTRING function.

The procedure in question is called by a pass-through query that is literally just EXEC and a series of parameters.

EXEC SRS.USP_NewCaseMedicalStaging @ContractNum = 123456789, @PatientBirthDate = '1990-01-01', @PatientFstName = 'Susan', @GrpID = '000012345', @CvgStartDate = '2009-09-01', @CvgEndDate = '9999-12-31', @UserID = 2

The problem here is that the error is not consistent. Sometimes one person gets it, and no one else does, sometimes everyone gets it. Someone running the same procedure will have it fail repeatedly for an hour, and then suddenly work.

There are no resource-intensive queries or procedures running concurrently 95% of the time.

When the EXEC statement from the P-T query is copied, pasted, and run directly in SQL server, it runs correctly 100% of the time.

The procedure in question has no SUBSTRING calls, and only the following LEFT:

[FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]
AND
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080](
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]LEFT(LEFT([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]PATIENT_NAME[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff]LEN[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]PATIENT_NAME[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])-([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff]LEN[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]PATIENT_NAME[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])-[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff]CHARINDEX[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]' '[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]PATIENT_NAME[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])+[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]1[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]))),[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]3[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])=LEFT([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]@PatientFstName[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]3[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]OR
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]LEFT(LEFT([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]PATIENT_NAME[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff]LEN[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]PATIENT_NAME[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])-([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff]LEN[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]PATIENT_NAME[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])-[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff]CHARINDEX[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]' '[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]PATIENT_NAME[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])+[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]1[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]))),[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]3[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])=LEFT([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]@AltName[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]3[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

PATIENT_NAME stores first and last name, separated by a space, and never lacks either part (so far, at least). (For those who don't want to work out the logic, it simply compares the first three letters of a person's first name against the first three letters of a first name parameter.)

Additionally, if ever no first name were provided, then I would never be able to get the procedure to run correctly, but it does most of the time. Even with the error I'm looking at, when I attempted to run it myself for the same member's data, it ran perfectly.

The users have full read-write access to the table they're inserting records into, and they have read access or better to every table and view used in the select query (I've already checked permissions).

If anyone has any suggestions, I'm at my wit's end on this one. If you want to see the entire stored procedure, I'll certainly provide it. Due to HIPAA, data will be harder to share.

If this had ANY consistent aspects, I'd have something to dig into, but the fact that it comes and goes, disregarding user, time of day, server activity, the data being pulled, and everything else is making this a stone-cold BITCH to figure out.

EDIT: And before you ask why I got so complex with the name comparison, it's because we ran into a 2-letter first name. :banghead:

 

[NauticalGent]

We just recently “upgraded” our OS and Office versions and my Sharepoint BE was no longer an option so our IT graciously offered to let me put the BE on SQL Server.

I was(am) intimidated but have managed to get everything up and running with a lot of help from Steve Bishop(YouTube) and Dr Google.

I am starting to attempt to use views and PT queries to take advantage of server-side processing but it is going slow. Been doing a LOT of reading in this and other forums and I came across a thread in another forum regarding “parameter sniffing”:

http://www.accessforums.net/showthread.php?t=68838

Sounds like a similar problem but I could be wrong. Maybe a recompile statement could be the fix...?

 

[Frothingslosh]

Yeah, I threw in the parameter sniffing fix on Thursday (I've run into it before) and it didn't do a bloody thing.

Adding a timestamp field to the table didn't help either. (I didn't think it would, but any port in a storm.)

I've done of number of alters, which trigger the creation of a new execution plan each time, but I'll certainly try a full recompile on Monday. I may also replace that series of LEFTs with a custom scalar function.

Thanks.

 

[kevlray]

The error message states that it could apply to a Left or a Substring. But back to the issue. There are only a few possibilities (other than the general weirdness) that I can think of. Somehow either there is no ' ' in PATIENT_NAME (I usually use a CHR(32) for checking), The PATIENT_NAME or @PatientFstName or @AltName are less than three characters long (I went to college with a person who's first name was Ay).

 

[Minty]

Check your data for strange characters.
You might have either a Tab or CR in between the names instead of a space ? That would probably look ok in a table - but not in data terms.

Alternatively - adjust the SP to find the error. E.g. bung a NOT (your criteria) in there, and see which records it returns?

 

[Frothingslosh]

I'm not headed to work yet so testing right now is out, but invalid characters sound like they would prevent the record from EVER processing, but what happens is that the record might fail for an hour or two and then suddenly process on a later try, or sometimes it fails for one person but works for another.

The SP itself works flawlessly when executed directly in SQL Server. it *ONLY* fails when executed via the EXEC command in a pass-through from Access.

Finally, the issue just *vanished* as of this week. At this point, I'm going to put it down to IT doing something on the server that messed things up for a while. Only thing I can think of to explain the whole 'problem started out of nowhere, lasted two weeks, and then vanished' thing, especially as there were no code code or SP changes happening around the time the issue first appeared.

That said, thanks to both of you for your assistance.

 

[Frothingslosh]

The error message states that it could apply to a Left or a Substring. But back to the issue. There are only a few possibilities (other than the general weirdness) that I can think of. Somehow either there is no ' ' in PATIENT_NAME (I usually use a CHR(32) for checking), The PATIENT_NAME or @PatientFstName or @AltName are less than three characters long (I went to college with a person who's first name was Ay).

The thing is, that running the same query with the same data later in the day worked just fine.

The very first thing I checked the first time I got this error (out of several dozen reported occurrences total) was for the lack of a space in PATIENT_NAME, but not once did I find a value that was lacking a first name, an intervening space, or a last name. The shortest name involved was four letters (Todd).

@AltName is a parameter with an empty string as a default, which is fine since it's part of a parenthesized OR clause in the WHERE statement and @PatientFstName is a required parameter; in fact, at least 95% of the time it's never provided.

My friend Herman, a systems architect for JP Morgan HK until a couple years ago, had this suggestion:

I won't go as far as memory corruption on multi threading but it does smell of some interaction with VM and context switching dropping some sheet.

(Technically, our database server is a virtual server handled by corporate IT. It was a couple hundred grand cheaper with more processing power and memory than having them build us a new physical machine.)

This was, unfortunately, the point both where his English turned into terms I know nothing about (yet), and, based on the context, puts the problem firmly in the hands of IT.

Needless to say, when the user team leader told me Wednesday that they hadn't seen this error once this week, I was just DELIGHTED to mark the issue as 'Resolved' and let it go at that. I have entirely too much to do to continue trying to nail this down after it stopped just as mysteriously as it started.

 

[Frothingslosh]

Aaaand this problem is back. Still unable to solve it. I cannot find a single reason why a specific EXEC command results in this error (but only intermittently) when run via pass through, but works perfectly when run directly in SSMS.

 

[Frothingslosh]

As there has been no response to my bump (and we weren't able to figure this out when the error was first raise), I have cross-posted this to Stack Overflow.

 

[WayneRyan]

Frosting,

I tried the following in SSMS and got:

declare @PATIENT_NAME varchar(30) = 'abcd'
--
-- Try whole expression
--
select LEFT(   LEFT(@PATIENT_NAME, (LEN(@PATIENT_NAME) - (LEN(@PATIENT_NAME)- CHARINDEX(' ', @PATIENT_NAME) + 1)))     , 3) 
Msg 537, Level 16, State 2, Line 6
Invalid length parameter passed to the LEFT or SUBSTRING function.

--
-- Remove the outer Left
--
select LEFT(@PATIENT_NAME, (LEN(@PATIENT_NAME) - (LEN(@PATIENT_NAME)- CHARINDEX(' ', @PATIENT_NAME) + 1)))
Msg 537, Level 16, State 2, Line 6
Invalid length parameter passed to the LEFT or SUBSTRING function.

--
-- Get just the length component of the inner Left expression
--
select (LEN(@PATIENT_NAME) - (LEN(@PATIENT_NAME)- CHARINDEX(' ', @PATIENT_NAME) + 1)) As StartingPoint
StartingPoint
-1

I can reccommend a couple things:

1) Make it simpler.  Wouldn't a simple Left(RTrim(Coalesce(Patient_Name, '') + '   ') be easier in your code.
2) Why not make it really simple and add a computed column:

     Alter Table YourTable
     Add   Patient_3 As Left(RTrim(Coalesce(Patient_Name, '') + '   ')

The 2nd approach has advantages:

1) You will have less code manipulating the data
2) You can index the new computed column and your queries will be faster.
The Left and RTrim functions will require a full-scan of the table ... no indexes.

If you remove the complexity of the nested functions you can use your existing technique
and use a Case Statement to find your OUTLYING data problems. You can have a when
clause for:

1) Null Patient Name
2) Short Patient Name
3) Patient Name with "weird" space characters (160 - non-breaking space).

Just some thoughts,
Wayne

 

[WayneRyan]

Couldn't resist playing with this a bit ...

use demo
go

Create Table Patients (
   Patient_Name  varchar(50),
   Patient_3 as Left(Patient_Name + '   ', 3))
go

   Delete From Patients

   Insert Into Patients (Patient_Name)
   select Char(65 + Abs(Cast(Substring(Cast(Newid() as varbinary(32)), 1, 4) as Int) % 25)) + 
          Char(65 + Abs(Cast(Substring(Cast(Newid() as varbinary(32)), 1, 4) as Int) % 25)) + 
          Char(65 + Abs(Cast(Substring(Cast(Newid() as varbinary(32)), 1, 4) as Int) % 25)) + 
          Char(65 + Abs(Cast(Substring(Cast(Newid() as varbinary(32)), 1, 4) as Int) % 25)) +
		  Char(65 + Abs(Cast(Substring(Cast(Newid() as varbinary(32)), 1, 4) as Int) % 25)) + 
		  Char(65 + Abs(Cast(Substring(Cast(Newid() as varbinary(32)), 1, 4) as Int) % 25))

   go 500000

   --
   -- Insert Some "Weird" data
   --
   Insert Into Patients (Patient_Name) Values ('AA BBBBBB')
   Insert Into Patients (Patient_Name) Values ('BB')

   --
   -- Find some Patients with "weird names"
   --
   select * from patients where patient_3 in ('BB', 'AA', 'AA ')

   --
   -- Any dups ?
   --
   select patient_3, count(*)
   from   patients
   group by patient_3
   order by count(*) desc


   -- No Indexes
   Declare @Search_Name varchar(3) = 'UVC'

   set statistics io, time on

   select *
   from   patients
   where Patient_3 = @Search_Name



------(40 rows affected)
------Table 'Patients'. Scan count 1, logical reads 1240, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

------ SQL Server Execution Times:
------   CPU time = 47 ms,  elapsed time = 48 ms.

   -- With Indexes (create index on computed field)
   Create Index ix_Short on Patients (Patient_3)


   Declare @Search_Name varchar(3) = 'UVG'

   select *
   from   patients
   where Patient_3 = @Search_Name

------(40 rows affected)
------Table 'Patients'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

------ SQL Server Execution Times:
------   CPU time = 0 ms,  elapsed time = 0 ms.

   set statistics io, time off

Big reduction in cached reads.
BIG reduction in execution time.

Overall, I'd say that the search method will bring up multiple rows.
The computed column should allow you to remove a lot of VBA/SQL code.
The index on the computed column will greatly speed things up.

hth,
Wayne

 

[Frothingslosh]

First off, it's Frothingslosh, not Frosting.

Okay, that said, the outermost LEFT is actually the important one. Because of both user error (at the POS end) and how people often don't go by their actual given names, we have found that matching by more than 3 letters of the first name causes an excessive number of false negatives. (Example: James goes by Jimmy but Jim appears on the claims.) The actual simplification is to just go with LEFT(PATIENT_NAME, 3).

The front end actually validates for a null patient name and will not accept input in that situation. The 'Find Matches' button is literally disabled until patient name, date of birth, and contract number are all filled in.

Nice idea with the added column, but it's a non-starter. The table involved has 350 fields and two billion records (don't look at me!), and the head of the SQL team is already hypersensitive enough about the space that table takes up that that solution will never make it by him, I'm afraid.

That said, what I'm still looking for is to figure out why this thing fails on just a handful of people. The data, as far as I can tell, is good, so it's really making no sense.

 

[Frothingslosh]

Well, whatever was causing it, the problem went away last weekend and now the front end loads the information just fine.

In my copious free time (HA!) I really need to find out what the problem was and what ran over the weekend to fix it.