?
Solved

SQL 2000 Invalid length parameter passed to the substring function.

Posted on 2012-08-15
9
Medium Priority
?
745 Views
Last Modified: 2012-08-15
I have a query that selects the max value of a column, uses len to count and I then subtract from that so I can remove trailing characters.  the total length of the field is alwasy 35 characters.  Our newer server (sql 2008) work perfectly, our 2k servers work as long as I don't subtracto more than 12.... i cant figure out what is causing this.. any help would be great

This code works only on the sql 2008 servers - returning TUpgradeLog_3_7_3_3  - dropped the last 16 char

select max(LEFT(UpgradeLogTable,LEN(UpgradeLogTable)-16))as maxVobVer from TDatabaseVersion 

Open in new window


This code works on both dropping the last 12 - TUpgradeLog_3_7_3_3_201 - i can reduce the number without issue - as soon as I hit 13 it fails on sql 2k

select max(LEFT(UpgradeLogTable,LEN(UpgradeLogTable)-12))as maxVobVer from TDatabaseVersion 

Open in new window

0
Comment
Question by:NCSA SCADA
  • 5
  • 4
9 Comments
 

Author Comment

by:NCSA SCADA
ID: 38297422
the error I recieve is .. Invalid length parameter passed to the substring function.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38297704
The problem must be that you're getting a negative number in this expression:

LEN(UpgradeLogTable)-16


Try doing this in the SQL 2000:

select UpgradeLogTable from TDatabaseVersion
WHERE LEN(UpgradeLogTable)-16<0

you should at least get one value, and that (or those) are the records that are bugging you.
0
 

Author Comment

by:NCSA SCADA
ID: 38297882
it dosent error , but not what i'm after this if i run this -
TUpgradeLog_3_7_3_3_20120215_135509

select max(UpgradeLogTable) from TDatabaseVersion

Open in new window

I just want to drop all of the following leaving this
3_7_3_3
this value will be different on every server (might be 3_9_3_8) i run it against
so i started by just trying to drop the trailing characters and that is where i got stuck
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Expert Comment

by:LIONKING
ID: 38297934
Will the value "always" be like this:

TUpgradeLog_X_Y_Z_R......

What I mean is... Will it always be the string "TUpgradeLog_" followed by "#_#_#_#"?
0
 

Author Comment

by:NCSA SCADA
ID: 38298157
yes - same string in the front that i want to drop.... then the version info i need.. then the 16 characters at the end that I want to drop as well
0
 
LVL 13

Accepted Solution

by:
LIONKING earned 2000 total points
ID: 38298175
So out of this "TUpgradeLog_3_7_3_3_20120215_135509" all you want is this "3_7_3_3"

Have you tried substring?

Something like

SELECT SUBSTRING(UpgradeLogTable,12,7) FROM TDatabaseVersion
0
 

Author Comment

by:NCSA SCADA
ID: 38298274
I have never used that before can you explain how it works
0
 

Author Closing Comment

by:NCSA SCADA
ID: 38298372
solved the problem - thanks
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38298498
Here's a link that can explain the substring..

http://msdn.microsoft.com/en-us/library/ms187748.aspx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question