quote:

---

I am using my Windows userid & pass word that is the same for my SQL Server running in my computer

---

quote:

---

Originally posted by rpichur

Thanks ianyates for your suggestion.

I tried by creating the Stored Procedure,using the textcopy.exe, but got into some problems as explained here:
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
First created a SP:

CREATE PROCEDURE sp_imp_exp_images
(@runpath varchar(100),
@srvr varchar(50),
@db varchar(50),
@usr varchar(50),
@pwd varchar(50),
@tbl varchar(50),
@col varchar(50),
@whr varchar(200),
@fil varchar(100),
@mod char(1))
AS
DECLARE @cmd varchar(1000)
SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +
' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +
' /F ' + @fil+ ' /' + @mod
EXEC Master..xp_cmdShell @cmd
GO
CREATE TABLE pic (pic_id int,picture image)
INSERT INTO pic VALUES (1,null)
UPDATE pic SET picture = 'xx'
%%%%%%%%%%%%%%%%%

EXEC sp_imp_exp_images
'C:\textCopy.exe',
'CIN-LT1',
'ENET',
'administrator',
'PassWord',
'pic',
'picture',
'"where pic_id = 1"',
'C:\00000009_001.tif',
'I'
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
When I ran the SP,I got these in the result panes:

1. TEXTCOPY Version 1.0
2. DB-Library version 8.00.194
3. DB-Library Error 10004: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
4. Operating System Error 51: ConnectionOpen (Connect()).
5. ERROR: Could not connect to SQL Server 'CIN-LT1'
6. NULL

6 rows affected.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
I am using my Windows userid & pass word that is the same for my SQL Server running in my computer
************************************************
I got this suggestion from this link:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=498&lngWId=5
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
I couldn't figure out the problem.

Appreciate your help, "ianyates"

Thanks in advance.

---

quote:

---

create table #files(filename varchar(256))
insert into #files
exec master..xp_cmdshell 'dir c:\ /b /a-d'

delete from #files where filename is null


select * from #files


drop table #files

---

quote:

---

Originally posted by rpichur



Brief description of the job need to be done in SQL Sever:

1)We have a table named, “Images” that has the following columns.

id_image (PK) (Forced Identity Insert)(int)
image_data (image)
image_file_name (00000049_001.tif)

Except for image_data column all others are populated.

This will be exported to SQL Sever from the image folder in Windows.

2)We have CD (Windows Folder) that has the images, with the following
images:

I have attached the file listing of the CD here.

Directory of C:\d1_sample

08/24/1999 09:10 AM 33,100 00000009_001.tif
08/24/1999 09:10 AM 45,376 00000011_001.tif
08/27/1999 09:40 AM 21,004 00000041_001.tif
08/27/1999 09:40 AM 21,310 00000047_001.tif
04/29/2002 02:43 PM 30,416 00000049-2_001.tif
04/02/2004 11:00 AM 37,882 00000049-3_001.tif
08/27/1999 09:40 AM 21,612 00000049_001.tif
08/27/1999 09:40 AM 20,816 00000115_001.tif
8 File(s) 231,516 bytes

Total Files Listed:
8 File(s) 231,516 bytes
##############################################


Sample Code that I used to load one image that worked successfully:


Create a Store procedure:


CREATE PROCEDURE sp_imp_exp_images
(@runpath varchar(100),
@srvr varchar(50),
@db varchar(50),
@usr varchar(50),
@pwd varchar(50),
@tbl varchar(50),
@col varchar(50),
@whr varchar(200),
@fil varchar(100),
@mod char(1))
AS
DECLARE @cmd varchar(1000)
SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +
' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +
' /F ' + @fil+ ' /' + @mod
EXEC Master..xp_cmdShell @cmd
GO


$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

I have to execute the above SP by supplying the following parameters:

EXEC sp_imp_exp_images
'C:\textcopy.exe',
'CIN- LT1',
'IENET',
'userid',
'password',
'images',
' image_data',
'"where image_file_name = "00000049_001.tif""',
'C:\d1_sample\00000049_001.tif',
'I'

*******************************************************************

@@ Here I tested this with these parameters and it worked;

The above parameters except in ‘where clause’, I used:
'"where id_image =10"' that corresponded to the above image.

But when I changed the where clause as: '"where image_file_name = "00000049_001.tif""', it gives me error as below:
SQL Server 'CIN-LT1' Message 170: Line 1: Incorrect syntax near '_001'. (Concerning line 1)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


REQUIREMENT:

(1) Compare the image filename “image_file_name” from this table with the corresponding file in the folder and load that image into the table: “Images” in SQL Sever into the column: “image_data”

This inert will happen until all the images in the folder are completed.

PROBLEMS FACED:

1. Do you see an error in the where clause: '"where image_file_name = "00000049_001.tif""', that generates the error as shown above?

2. Assuming this code works with the above where clause, how do I make it work for loading all the images in the folder into the tables. I have populated corresponding rows that match every image file name in the folder.

I greatly appreciate your help.

---