Invalid length parameter passed to the SUBSTRING function

Question

Post reply

Invalid length parameter passed to the SUBSTRING function

Bosco2

Ten Centuries

Points: 1240
More actions
June 6, 2010 at 8:26 pm

#219681

Hi -
I am running this code and am receiving the error:
(28400 row(s) affected)
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
(28400 row(s) affected)
(0 row(s) affected)
';WITH CTE AS
(
SELECT Field9,
RowID,
Pos1 = CharIndex(''User Account Created'', Field9),
Pos2 = CharIndex(''Account Name'', Field9),
Pos3 = CharIndex(''Target Domain'', Field9),
Pos4 = CharIndex(''Target Account ID'', Field9),
Pos5 = CharIndex(''Caller User Name'', Field9),
Pos6 = CharIndex(''Caller Domain'', Field9),
Pos7 = CharIndex(''Caller Logon Id'', Field9)
FROM evCreateUser
)
UPDATE evCreateUser
SET [User Account Created] = SubString(c1.Field9, Pos1 + 22, Pos2-Pos1-23),
[Account Name] = SubString(c1.Field9, Pos2 + 14, Pos3-Pos2-15),
[Target Domain] = SubString(c1.Field9, Pos3 + 15, Pos4-Pos3-16),
[Target Account ID] = SubString(c1.Field9, Pos4 + 20, Pos5-Pos4-21),
[Caller User Name] = SubString(c1.Field9, Pos5 + 18, Pos6-Pos5-19),
[Caller Domain] = SubString(c1.Field9, Pos6 + 15, Pos7-Pos6-16),
[Caller Logon ID] = SubString(c1.Field9, Pos7 + 17, LEN(c1.Field9)-Pos7-18)
FROM evCreateUser c
JOIN CTE c1
ON c1.RowID = c.RowID
WHERE c1.Field9 LIKE ''%User Account Created%'''
The data looks like this:
28129,Security,Security,SUCCESS AUDIT,MIS631,6/1/2010 9:35:17 PM,624,mhigh\KILGORE,"User Account Created: New Account Name: higha New Domain: KILGORE New Account ID: %{S-1-5-21-2204003968-2505502765-350501314-1244} Caller User Name: mhigh Caller Domain: KILGORE Caller Logon ID: (0x0,0x2B554F) Privileges - Attributes: Sam Account Name: higha Display Name: - User Principal Name: higha Home Directory: - Home Drive: - Script Path: - Profile Path: - User Workstations: - Password Last Set: <never> Account Expires: <never> Primary Group ID: 513 AllowedToDelegateTo: - Old UAC Value: 0x0 New UAC Value: 0x15 User Account Control: Account Disabled 'Password Not Required' - Enabled 'Normal Account' - Enabled User Parameters: - Sid History: - Logon Hours: <value not set> "
This data seems to work fine with similar code:
28132,Security,Security,SUCCESS AUDIT,MIS631,6/1/2010 9:35:19 PM,626,mhigh\KILGORE,"User Account Enabled: Target Account Name: higha Target Domain: KILGORE Target Account ID: %{S-1-5-21-2204003968-2505502765-350501314-1244} Caller User Name: mhigh Caller Domain: KILGORE Caller Logon ID: (0x0,0x2B554F) "
I need this to work ASAP....thank you all....I really speak for newbies that you have saved me many times and I think you all rock!!
Thanks in Advance....Allen

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the problem is in the data; at least one row does not have every charindex you are looking for:

select * from evCreateUser

WHERE CharIndex(''User Account Created'', Field9) = 0

OR CharIndex(''Account Name'', Field9) = 0

OR CharIndex(''Target Domain'', Field9) = 0

OR CharIndex(''Target Account ID'', Field9) = 0

OR CharIndex(''Caller User Name'', Field9) = 0

OR CharIndex(''Caller Domain'', Field9) = 0

OR CharIndex(''Caller Logon Id'', Field9) = 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bosco2 Ten Centuries Points: 1240 More actions · Answer 2

THanks Lowell. I ran the code and it does not return any rows. Could it be anythign else?

Lowell SSC Guru Points: 323518 More actions · Answer 3

then the error is in the logic where you are determining the substring; does this return any rows?:

;WITH CTE AS

(

SELECT Field9,

RowID,

Pos1 = CharIndex(''User Account Created'', Field9),

Pos2 = CharIndex(''Account Name'', Field9),

Pos3 = CharIndex(''Target Domain'', Field9),

Pos4 = CharIndex(''Target Account ID'', Field9),

Pos5 = CharIndex(''Caller User Name'', Field9),

Pos6 = CharIndex(''Caller Domain'', Field9),

Pos7 = CharIndex(''Caller Logon Id'', Field9)

FROM evCreateUser

)

select * from CTE

where Pos2-Pos1-23 < 1

or Pos3-Pos2-15< 1

or Pos4-Pos3-16< 1

or Pos5-Pos4-21< 1

or Pos6-Pos5-19< 1

or Pos7-Pos6-16< 1

or Pos7-18 < 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bosco2 Ten Centuries Points: 1240 More actions · Answer 4

No this did not return any rows either....this is csv from event viewer....I attached the file I use. I just do a bulk import and then the code I am having issues with.

THanks for your help...I really hope I can get this working.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

See if this query returns any negative value in the result set

;WITH CTE AS

(

SELECT Field9,

RowID,

Pos1 = CharIndex(''User Account Created'', Field9),

Pos2 = CharIndex(''Account Name'', Field9),

Pos3 = CharIndex(''Target Domain'', Field9),

Pos4 = CharIndex(''Target Account ID'', Field9),

Pos5 = CharIndex(''Caller User Name'', Field9),

Pos6 = CharIndex(''Caller Domain'', Field9),

Pos7 = CharIndex(''Caller Logon Id'', Field9)

FROM evCreateUser

)

SELECTPos1 + 22, Pos2-Pos1-23,

Pos2 + 14, Pos3-Pos2-15,

Pos3 + 15, Pos4-Pos3-16,

Pos4 + 20, Pos5-Pos4-21,

Pos5 + 18, Pos6-Pos5-19,

Pos6 + 15, Pos7-Pos6-16,

Pos7 + 17, LEN(c1.Field9)-Pos7-18

FROM evCreateUser c

JOIN CTE c1

ON c1.RowID = c.RowID

WHERE c1.Field9 LIKE ''%User Account Created%'' -- Change the Where Clause if its wrong, actually i didnt understand the Where Clause

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

nigel. SSChampion Points: 11636 More actions · Answer 6

The row you posted starting with '28129' does not contain the string "Target Domain" so this:

CharIndex(''Target Domain'', Field9)

will return zero, and the subsequent expression:

Pos3-Pos2-15

will be negative

Nigel
How to post questions