Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-14-2018, 10:53 AM   #1
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,686
Thanks: 67
Thanked 378 Times in 341 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Intermittent Error

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

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

AND
(
LEFT(LEFT(PATIENT_NAME,(LEN(PATIENT_NAME)-(LEN(PATIENT_NAME)-CHARINDEX(' ',PATIENT_NAME)+1))),3)=LEFT(@PatientFstName,3)
OR
LEFT(LEFT(PATIENT_NAME,(LEN(PATIENT_NAME)-(LEN(PATIENT_NAME)-CHARINDEX(' ',PATIENT_NAME)+1))),3)=LEFT(@AltName,3)
)
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.

__________________
Doctor House was entirely too much of a 'people person'.

Last edited by Frothingslosh; 03-14-2018 at 11:04 AM.
Frothingslosh is offline   Reply With Quote
Old 03-17-2018, 03:17 AM   #2
NauticalGent
Pristine Curmudgeon
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,289
Thanks: 291
Thanked 166 Times in 146 Posts
NauticalGent will become famous soon enough NauticalGent will become famous soon enough
Re: Intermittent Error

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...?
__________________
...see, what had happened was...
NauticalGent is offline   Reply With Quote
Old 03-17-2018, 05:50 AM   #3
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,686
Thanks: 67
Thanked 378 Times in 341 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Intermittent Error

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.

__________________
Doctor House was entirely too much of a 'people person'.
Frothingslosh is offline   Reply With Quote
Old 03-21-2018, 09:27 AM   #4
kevlray
Newly Registered User
 
Join Date: Apr 2010
Location: Central California
Posts: 569
Thanks: 9
Thanked 50 Times in 48 Posts
kevlray will become famous soon enough
Re: Intermittent Error

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).
kevlray is offline   Reply With Quote
Old 03-21-2018, 09:47 AM   #5
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,210
Thanks: 123
Thanked 1,421 Times in 1,393 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Intermittent Error

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?
__________________
If we have helped;
Please click the 'reputation' scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 03-22-2018, 01:23 AM   #6
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,686
Thanks: 67
Thanked 378 Times in 341 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Intermittent Error

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.
__________________
Doctor House was entirely too much of a 'people person'.
Frothingslosh is offline   Reply With Quote
Old 03-22-2018, 04:30 AM   #7
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,686
Thanks: 67
Thanked 378 Times in 341 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Intermittent Error

Quote:
Originally Posted by kevlray View Post
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:

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


__________________
Doctor House was entirely too much of a 'people person'.

Last edited by Frothingslosh; 03-22-2018 at 04:42 AM.
Frothingslosh is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Runtime error 2162 - intermittent fat controller Modules & VBA 11 09-18-2014 11:28 AM
Intermittent #ERROR on unbound control Sketchin Forms 2 03-22-2013 06:30 AM
Intermittent Error Message Stang70Fastback Queries 3 01-30-2013 11:59 AM
Intermittent error with jpg on report DataMinerHome Modules & VBA 3 05-04-2012 05:27 PM
[SOLVED] Intermittent fatal error when using forms ginas Forms 2 05-30-2002 01:35 PM




All times are GMT -8. The time now is 09:03 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World