If you are using SQL 6.5 then I think I know what's causing this.........
This part of your code: (len(wire_id)-2) could well be producing a negative
result and hence when you wrap the right function around it you will get a
536 error....for more info have a look at what Microsoft say about update
statements using the substring function (same thing will happen with the
right function)
BUG: Update Query Causes an Access Violation if a SUBSTRING Encounters Error
536
----------------------------------------------------------------------------
----
BUG #: 17963 (SQLBUG_65)
SYMPTOMS
An Update query may cause an Access Violation (AV) if a SUBSTRING function
generates this error:
Msg 536, Level 16, State 1
Invalid length parameter passed to the substring function.
WORKAROUND
Make sure that you pass a valid length to the SUBSTRING function. When you
pass a valid length to the SUBSTRING function, the Access Violation no
longer occurs.
STATUS
Microsoft has confirmed this to be a problem in SQL Server version 6.5.
MORE INFORMATION
This problem is further illustrated by this example:
create table tbl_1
(col1 varchar(16) NULL,
col2 varchar(16) NULL
)
go
insert into tbl_1 values('123:456:789', null)
insert into tbl_1 values('123', null)
go
UPDATE tbl_1
SET col2 = substring(col1,1,charindex(":",col1)-1)
If you look at the details of the preceding UPDATE statement, you find that
the "charindex(":",col1)" returns a value of 0 for one of the rows. You then
proceed to subtract 1 from this value, which results in a value of -1. This
is the value that you end up passing to the SUBSTRING function, which then
creates the 536 error.
-----Original Message-----
From: Ryan Williams [mailto:[EMAIL PROTECTED]]
Sent: 17 August 2000 17:45
To: [EMAIL PROTECTED]
Subject: SQL query problem...
I have a SQL problem. I know what information I want to retrieve from
the DB,
however, I do not know what precisely is the proper SQL syntax/function is
in order to
achieve it.
The query as it is currently written in my CF template is:
select wl.*,p.*,wlg.*,wb.*,wsnd.*,wrcvd.*,ws.*
from wire_list wl
left join P_officers p on wl.reg_by = p.off_id
left join wire_status ws on wl.status_code = ws.status_code
left join wire_log wlg on wl.wire_id = wlg.wire_id
left join wire_senders wsnd on wl.sender_id = wsnd.sender_id
left join wire_rcv_modes wrcvd on wl.rcv_mode = wrcvd.rcv_mode
left join wire_banks wb on wl.bank_id = wb.bank_id
where wl.wire_id = (
select max(right(wire_id,(len(wire_id)-2)))
from wire_list)
The error this code generates when I attempt to run it in SQL query
analyzer:
Server: Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the substring function.
The content of the wire_id column looks like this format:
ab999 and is of varchar datatype.
I did not code these tables and I am in the middle of re-coding the
entire site to be FuseBox compliant. I am trying to select the top (max)
wire_id from the wire_list table, and display all records related
to the returned value of the max function, but the result that areretrieved
to date,
with the code "Select max(wire_id)" and no WHERE clause,
is ab99 when I know there are wire_id's in the table that have values
of ab999, etc. It seems that SQL does not recognize that ab999 is the
larger value when compared to ab99.
The solution I am trying to apply with the code at the top of this email is
to "get"
the "number" part of the wire_id column, minus the two letters, and then use
the max function on
the remainder to get the ab999 wire_id i know is in the table. I looked
through the
SQL books online and it seems that SQL Sever does not have the ability to
convert
the varchar datatype (of the wire_id) column to the int data type that
the max function seems to need in order to work properly.
Does anyone know of a work around for this problem? Or am I going totally
in the wrong
direction with this logic?
Any help/ideas/comments would be greatly appreciated. Thanks.
Ryan Williams
[EMAIL PROTECTED]
---------------------------------------------------------------------------
---
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
