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:
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.