Image Related Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Image Related Problem

Author

Topic

ismail_issac
Starting Member

22 Posts

Posted - 2005-12-21 : 04:31:24

Hi All.

I have a follwoing problem while filtering out those employees who does not have their photos updated into the table.

I have a table named PHOTOS with following structure
Col 1 - TheID - Datatype Varchar --- This is the Employee ID
Col 2 - FileName - Datatype Varcha ---This stores the File Name (0001.BMP for example for the employee whose TheId = 1 )
Col 3 - Photo - Datatype Image -- in This column the photo is updated

i have written a procedure to update the photo of each employee with their respective photos from the hard disk to the table.

CREATE Procedure photoupdate
as
set nocount on
declare @svrname varchar(100), @dbname varchar(100), @photopath varchar(300),@theid int, @filename varchar(100),
@cmd varchar(200)
declare cur cursor local forward_only for select CONVERT(varchar(50), SERVERPROPERTY('ServerName')) As ServName,
db_name() As DBName, pg.PhotoPath, ph.theid, ph.filename From PrgDefault pg, photos ph order by ph.theid

open cur
fetch next from cur into @svrname,@dbname, @photopath, @theid,@filename
WHILE @@FETCH_STATUS = 0
begin
update photos set photo='xx' where theid= @theid
set @cmd = 'TextCopy /S ' + @svrname + ' /D ' + @dbname + ' /U DBUserId /P DBUserPassword /T Photos /C Photo /W "Where TheID = ' + convert(varchar(15),@theid) + '" /F ' + @photopath + '\' + @filename + ' /I'
exec Master..xp_cmdShell @cmd
fetch next from cur into @svrname,@dbname, @photopath, @theid,@filename
end
GO

Now in my table there are 1000 employee records but at present we have only 400 employee photos. So with the above procedure we update 400 employee with their respective photos while rest of the employees the value stored in photo column is 'xx'.

So can any one please help me to retrieve all the employees from the PHOTOS table whose Photo column value is 'xx'

that is i should retrieve number of record
1000 - 400 = 600

Thanks in Advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-21 : 04:35:36

Select employee from yourTable where photoCol='xx'

Madhivanan

Failing to plan is Planning to fail

ismail_issac
Starting Member

22 Posts

Posted - 2005-12-21 : 05:48:43

Hi Madhivanan

Thanks for the quick reply.

The Photo column is of datatype image so we can't write this

"Select employee from yourTable where photoCol='xx'"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-21 : 06:21:37

Refer this
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources