Quick SQL Script Question need a fix 1

[grippy]

CREATE TABLE #files(filename varchar(255)) INSERT INTO #files EXEC master..xp_cmdshell 'dir C:\Inetpub\[URL unfurl="true"]wwwroot\website\html\images\productSpecific\large1\*.jpg[/URL] /b'
                                                                                                                            UPDATE    P
                                                                                                                             SET              [image] = 1
                                                                                                                             FROM         tblProduct P INNER JOIN
                                                                                                                                                    tblCompany C ON P.company = C.ID INNER JOIN
                                                                                                                                                    #files F ON F.filename = C.name + ' ' + CONVERT(varchar(6), P.productCode) + '.jpg'

Right now I am using this script to basically put a 1 in the field for the product where an image exists in the folder on the server. However it does not work for all the images and I have narrowed it down to this:

WORKS ON

CompanyName 11134.jpg
(product code in table is 11134)

DOES NOT WORK ON

CompanyName 21 432 P.jpg
(product code in table is 21-432-P)

So it looks like it does not like the dashes and there needs to be some sort of conversion. I did not write this script so I am not sure on what needs to change. Any help will be greatly appreciated.

Thank you,

Russ

 

[jrbarnett]

Your script converts the filename to varchar (6)
CONVERT(varchar(6), P.productCode)

therefore '21 432 P' is being evaluated as '21 432'

So if you change the 6 to a larger number to fit with the maximum number of characters you permit in the filename on your server, excluding the file extension and path, that should fix the problem.

John

 

[SQLBill]

Also, SQL Server does sometimes have issues with dashes/hyphens in object names. You can resolve that by putting square brackets around the name. [Table-name].

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875