String or binary data would be truncated
-
Ok I am getting this error when trying to insert data into a table.
String or binary data would be truncated
I know I could just change the type to TEXT, but the problem is that I am using a stored procedure which does not allow TEXT variables.
I am not sure what to do in this situation.
My SP looks like this...the text in red is the one that's causing the issue:
CREATE PROCEDURE [dbo].[spCreateJonesReport]
@REPORTNUMBER VARCHAR(50)
AS
--DECLARE @REPORTNUMBER VARCHAR(50)
DECLARE @ADJUSTER VARCHAR(255)
DECLARE @RGNAMELAST VARCHAR(100)
DECLARE @CLIENTOFFICE VARCHAR(100)
DECLARE @IOOFFICENAME VARCHAR(50)
DECLARE @WRWORKOFFICEID VARCHAR(50)
DECLARE @WRID VARCHAR(30)
DECLARE @SUBJECT VARCHAR(400)
DECLARE @FILTERDATE DATETIME
DECLARE @CLAIMNUMBER VARCHAR(255)
DECLARE @VIDEOAMOUNT FLOAT
DECLARE @CASECOST FLOAT
DECLARE @RESULTS VARCHAR(8000) <-----MSSQL doesn't allow this to be TEXT
DECLARE @WRINJURY VARCHAR(8000)
DECLARE @WRNOTES VARCHAR(8000)
--SET @REPORTNUMBER = 'ZZZ'
DECLARE jones_cursor CURSOR FOR
SELECT Adjuster, RGnameLast, ClientOffice, IOofficeName, WRworkOfficeID, WRid, Subject, FilterDate, ClaimNumber, CONVERT(FLOAT,VideoAmount), CONVERT(Float, CaseCost), Results, WRinjury, WRnotes
FROM INET.dbo.jones_temp
OPEN jones_cursor
FETCH NEXT FROM jones_cursor
INTO @ADJUSTER, @RGNAMELAST, @CLIENTOFFICE, @IOOFFICENAME, @WRWORKOFFICEID, @WRID, @SUBJECT, @FILTERDATE, @CLAIMNUMBER, @VIDEOAMOUNT,
@CASECOST, @RESULTS, @WRINJURY, @WRNOTES
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO INET.dbo.jones_report (ReportNumber, Adjuster, RGnameLast, ClientOffice, IOofficeName, WRworkOfficeID, WRid, Subject, FilterDate, ClaimNumber, VideoAmount, CaseCost, Results, WRinjury, WRnotes)
VALUES
(@REPORTNUMBER, @ADJUSTER, @RGNAMELAST, @CLIENTOFFICE, @IOOFFICENAME, @WRWORKOFFICEID, @WRID, @SUBJECT, @FILTERDATE, @CLAIMNUMBER, @VIDEOAMOUNT,
@CASECOST, @RESULTS, @WRINJURY, @WRNOTES)
FETCH NEXT FROM jones_cursor
INTO @ADJUSTER, @RGNAMELAST, @CLIENTOFFICE, @IOOFFICENAME, @WRWORKOFFICEID, @WRID, @SUBJECT, @FILTERDATE, @CLAIMNUMBER, @VIDEOAMOUNT,
@CASECOST, @RESULTS, @WRINJURY, @WRNOTES
END
CLOSE jones_cursor
DEALLOCATE jones_cursor
GO
-
The easiest solution is SUBSTRING( Results, 1, 8000). But you may want everything contained within that field. I don't know...
I wasn't born stupid - I had to study.
-
I tried doing SUBSTRING(Results, 0, 7999) AS Results1, SUBSTRING(Results, 8000, 15999) AS Results2
but that did not work...
-
If the SP just does that, you do not need a cursor
INSERT INTO INET.dbo.jones_report (ReportNumber, Adjuster, RGnameLast, ClientOffice, IOofficeName, WRworkOfficeID, WRid, Subject, FilterDate, ClaimNumber, VideoAmount, CaseCost, Results, WRinjury, WRnotes)
SELECT @REPORTNUMBER, Adjuster, RGnameLast, ClientOffice, IOofficeName, WRworkOfficeID, WRid, Subject, FilterDate, ClaimNumber, CONVERT(FLOAT,VideoAmount), CONVERT(Float, CaseCost), Results, WRinjury, WRnotes
FROM INET.dbo.jones_temp
-
Hadn't even looked at that, but I agree Sql Junkie. A cursor does not look to be necessary.
What errors were you recieving when you split "Results" up? Same thing?
Is the INET.dbo.jones_report "Results" field large enough? (I know, I know, you have already looked at that...)
Have you tested the longest record within "Results"?
I wasn't born stupid - I had to study.
-
I'm using a temp table and I need the cursor. I guess I could try it without as soon as I fix this other bug.
Anyway, "Results" is not large enough. I would love to use TEXT type, but SPs don't allow that for some reason.
Anyway, the error when I try to split is:
Expression result length exceeds the maximum. 8000 max, 15999 found.
-
Unless you haven't posted all of the code, you, in fact, as the SQL Junkie suggested, do not need a cursor for this. Did you try the SQL Junkie's code? If you didn't, you should. If you did, you need to tell us why it didn't work. If there's just a gross misunderstanding about why you may or may not need a cursor, could you explain a bit more, please.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, I am writing data to a temp table with this query using an ASP script:
if exists (select * from INET.dbo.sysobjects where id = object_id(N'[INET].[dbo].[jones_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [INET].[dbo].[jones_temp]
select RGnameLast+', '+RGnameFirst AS Adjuster, RGnameLast, COcity AS ClientOffice, IOofficeName, WRworkOfficeId,
WRid, SRnameLast+', '+SRnameFirst AS Subject,
CONVERT(CHAR(8), MIN(" & filterdate & "), 112) AS FilterDate, WRbillToFileID AS ClaimNumber, SUM(WCvideoSeconds) as VideoAmount, CONVERT(VARCHAR(12), -1) AS CaseCost, CONVERT(VARCHAR(8000), '') AS Results,
WRinjury, CONVERT(VARCHAR(8000), WRnotes) AS WRnotes, 0 AS Saved
INTO inet.dbo.jones_temp
I then call the SP in ASP like this:
reportNumber = Now()
strSQL = "EXECUTE INET.dbo.spCreateJonesReport '" & reportNumber & "' "
The SP looks like this:
CREATE PROCEDURE [dbo].[spCreateJonesReport]
@REPORTNUMBER VARCHAR(50)
AS
--DECLARE @REPORTNUMBER VARCHAR(50)
DECLARE @ADJUSTER VARCHAR(255)
DECLARE @RGNAMELAST VARCHAR(100)
DECLARE @CLIENTOFFICE VARCHAR(100)
DECLARE @IOOFFICENAME VARCHAR(50)
DECLARE @WRWORKOFFICEID VARCHAR(50)
DECLARE @WRID VARCHAR(30)
DECLARE @SUBJECT VARCHAR(400)
DECLARE @FILTERDATE DATETIME
DECLARE @CLAIMNUMBER VARCHAR(255)
DECLARE @VIDEOAMOUNT FLOAT
DECLARE @CASECOST FLOAT
DECLARE @RESULTS VARCHAR(8000)
DECLARE @WRINJURY VARCHAR(8000)
DECLARE @WRNOTES VARCHAR(8000)
--SET @REPORTNUMBER = 'ZZZ'
DECLARE jones_cursor CURSOR FOR
SELECT Adjuster, RGnameLast, ClientOffice, IOofficeName, WRworkOfficeID, WRid, Subject, FilterDate, ClaimNumber, CONVERT(FLOAT,VideoAmount), CONVERT(Float, CaseCost), Results, WRinjury, WRnotes
FROM INET.dbo.jones_temp
OPEN jones_cursor
FETCH NEXT FROM jones_cursor
INTO @ADJUSTER, @RGNAMELAST, @CLIENTOFFICE, @IOOFFICENAME, @WRWORKOFFICEID, @WRID, @SUBJECT, @FILTERDATE, @CLAIMNUMBER, @VIDEOAMOUNT,
@CASECOST, @RESULTS, @WRINJURY, @WRNOTES
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO INET.dbo.jones_report (ReportNumber, Adjuster, RGnameLast, ClientOffice, IOofficeName, WRworkOfficeID, WRid, Subject, FilterDate, ClaimNumber, VideoAmount, CaseCost, Results, WRinjury, WRnotes)
VALUES
(@REPORTNUMBER, @ADJUSTER, @RGNAMELAST, @CLIENTOFFICE, @IOOFFICENAME, @WRWORKOFFICEID, @WRID, @SUBJECT, @FILTERDATE, @CLAIMNUMBER, @VIDEOAMOUNT,
@CASECOST, @RESULTS, @WRINJURY, @WRNOTES)
FETCH NEXT FROM jones_cursor
INTO @ADJUSTER, @RGNAMELAST, @CLIENTOFFICE, @IOOFFICENAME, @WRWORKOFFICEID, @WRID, @SUBJECT, @FILTERDATE, @CLAIMNUMBER, @VIDEOAMOUNT,
@CASECOST, @RESULTS, @WRINJURY, @WRNOTES
END
CLOSE jones_cursor
DEALLOCATE jones_cursor
GO
I then grab the data in that table using ASP on another page:
SELECT * FROM inet.dbo.jones_report WHERE ReportNumber = '" & reportNumber
-
CREATE PROCEDURE [dbo].[spCreateJonesReport]
@REPORTNUMBER VARCHAR(50)
AS
INSERT INTO INET.dbo.jones_report (ReportNumber, Adjuster, RGnameLast, ClientOffice, IOofficeName, WRworkOfficeID, WRid, Subject, FilterDate, ClaimNumber, VideoAmount, CaseCost, Results, WRinjury, WRnotes)
SELECT (@REPORTNUMBER, Adjuster, RGnameLast, ClientOffice, IOofficeName, WRworkOfficeID, WRid, Subject, FilterDate, ClaimNumber, CONVERT(FLOAT,VideoAmount), CONVERT(Float, CaseCost), Results, WRinjury, WRnotes
FROM INET.dbo.jones_temp
GO
That's it.
_____________
Code for TallyGenerator -
You've already established the "Results" field in your new report table as varchar(8000). Hence, you have to substring "Results" or make mutiple fields to hold the extra values...
(Also, follow Sergiy's suggestion - you do not need a cursor...)
Good luck!
I wasn't born stupid - I had to study.
-
If you need a text column for Results, tell me why this won't work...
select RGnameLast+', '+RGnameFirst AS Adjuster, RGnameLast, COcity AS ClientOffice, IOofficeName, WRworkOfficeId,
WRid, SRnameLast+', '+SRnameFirst AS Subject,
CONVERT(CHAR(8), MIN(" & filterdate & "), 112) AS FilterDate, WRbillToFileID AS ClaimNumber, SUM(WCvideoSeconds) as VideoAmount, CONVERT(VARCHAR(12), -1) AS CaseCost, CAST('' AS TEXT) AS Results,
WRinjury, CONVERT(VARCHAR(8000), WRnotes) AS WRnotes, 0 AS Saved
INTO inet.dbo.jones_temp
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
-
One more thing... this isn't Oracle and you don't need a ref cursor to get a return... I'm fairly sure that OLE DB and ODBC in conjunction with ADO both have the capability of using a results set directly from a proc...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok guys, I tried the CAST as text method and it is still giving me:
String or binary data would be truncated.
I haven't had a chance to trim down the SP and remove the cursor. I will try that once I get this bug fixed.
Thanks guys!
-
Forgot to include a script.
Hey guys, I forgot to say that I am getting the above error in an update section of the script page. The update statement looks like this:
UPDATE jones_report SET results='" & results & "', WRnotes='" & objectives & "', "
CaseCost = '" & cost & "' "
WHERE ID='" & id & "' "
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply