I'm not sure what you were trying to relate. However, I Googled for varchar(MAX) and it should hold 2GB which is far more than I need. I tried changing the table and variable varchar(MAX) to varchar(8000) and I get the same truncating error on the INSERT line.
Test how long the data you are trying to insert is by changing your query as shown below
SELECT
MAX(LEN(fctConcatPhotos(@Sku))) AS MaxLen_SKU,
MAX(LEN(FileNam) AS MaxLen_FileNam
FROM
(
SELECT Sku,
FileNam,
SortKey
FROM PhotosHighRes
GROUP BY Sku,
FileNam,
SortKey
HAVING Sku = @Sku
) i;
Now compare that with what the size of the columns in your Photos table are. You can do that either by looking at the columns in the object explorer, or via this query:
EXEC sp_help 'Photos'
What I suspect is that the size of one or the other - SKU or Photos column is too small. If that is the case, you will need to expand the size of that column accordingly.
What in the world am I doing wrong? I would expect @Sku to be 5 characters, not 629. I cut n pasted a test FileName concatenated and it's under 500 characters.
drop table PhotosHighRes
go
drop table Photos
go
create table PhotosHighRes
(
sku varchar(100),
FileName varchar(100),
SortKey int
)
go
create table photos
(
SKu varchar(100),
Photos varchar(max)
)
go
insert into PhotosHighRes select 'skua','SkuA_Main_Front_wMic.jpg', 1
insert into PhotosHighRes select 'skua','SkuA_Front_Left_wMic_wDevice.jpg', 2
insert into PhotosHighRes select 'skua','SkuA_Main_Front_woMic.jpg',3
insert into PhotosHighRes select 'skua','SkuA_Front_Left_woMic_wDevice.jpg', 4
insert into PhotosHighRes select 'skua','SkuA_Front_Right_wMic.jpg', 5
insert into PhotosHighRes select 'skua','SkuA_Front_Right_woMic.jpg', 6
insert into PhotosHighRes select 'skua','SkuA_Front_Left_wMic.jpg', 7
insert into PhotosHighRes select 'skua','SkuA_Front_Left_woMic.jpg', 8
insert into PhotosHighRes select 'skua','SkuA_Main_Back_wMic.jpg', 9
insert into PhotosHighRes select 'skua','SkuA_Main_Back_woMic.jpg', 10
insert into PhotosHighRes select 'skua','SkuA_Back_Right_wMic.jpg', 11
insert into PhotosHighRes select 'skua','SkuA_Back_Right_woMic.jpg', 12
insert into PhotosHighRes select 'skua','SkuA_Back_Left_wMic.jpg', 13
insert into PhotosHighRes select 'skua','SkuA_Back_Left_woMic.jpg', 14
insert into PhotosHighRes select 'skua','SkuA_Top_wMic.jpg', 15
insert into PhotosHighRes select 'skua','SkuA_Top_woMic.jpg', 16
insert into PhotosHighRes select 'skua','SkuA_Acc.jpg', 17
go
select * from PhotosHighRes
create function ...Fctconcatphotos
CREATE FUNCTION Fctconcatphotos (@Sku VARCHAR(10))
returns VARCHAR(max)
AS
BEGIN
RETURN
(SELECT FileName = Stuff((SELECT ',' + filename
FROM photoshighres
WHERE sku = @Sku
ORDER BY sortkey
FOR xml path('')), 1, 1, ''))
END
SQL to call FUCTION insert Photos table
DECLARE @Sku VARCHAR(100) = 'skua'
INSERT INTO photos
(sku,
photos)
SELECT @sku,
dbo.Fctconcatphotos(@Sku)
SELECT *
FROM photos
The code you posted initially is concatenating several SKU's. You are then inserting that concatenated string to the table Photos. Is that what you intended to do? When you concatenate, it is quite possible that the concatenated string is long.
Run this, and you will see what you would be inserting into the table:
SELECT
fctConcatPhotos(@Sku) as SKU,
FileNam
FROM
(
SELECT Sku,
FileNam,
SortKey
FROM PhotosHighRes
GROUP BY Sku,
FileNam,
SortKey
HAVING Sku = @Sku
) i;
My sense is that, that is not what you were trying to insert into the jsk_admin.Phots table. But, if that is indeed what you want to insert into the jsk_admin.Photos, change your table as follows:
ALTER TABLE jsk_admin.Photos ALTER COLUMN Sku VARCHAR(1024) NOT NULL;
ALTER TABLE jsk_admin.Photos ALTER COLUMN Photos VARCHAR(128) NULL;
As a general rule, when you use VARCHAR data type, ALWAYS specify a length. Otherwise it defaults to something (31 or 32, or 1 or something depending on the context).
To ALL - I really appreciate your input. I never would have figured this out without everyone's input. I tried the XML route and just ran into more issues dealing with carriage return codes. My intent was to concatenate the filenames as a string and insert into a table for convenient, speedy access to the data in more involved queries. Below is the completed function and stored procedure that is working for what I need.
Side Note: The below is created for one sku at a time. A couple edits and I run it for hundreds of skus.
FUNCTION
( @Sku varchar(10)
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @R varchar(MAX)
SELECT @R = ISNULL(@R + Char(13) + Char(10), '') + CAST(SortKey AS VARCHAR(32)) + '. ' + FileNam
FROM PhotosHighRes
WHERE Sku = @Sku
ORDER BY SortKey
RETURN @R
END
STORED PROCEDURE @Sku varchar(10)
AS
DELETE FROM Photos
INSERT INTO Photos (Sku, Photos)
SELECT Sku, fctConcatPhotos(@Sku) as Photos
FROM
(
SELECT Sku
FROM PhotosHighRes
GROUP BY Sku
) i
