Storing images in sql 2000 - 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)

Storing images in sql 2000

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-01-14 : 18:51:17

Hi Friends,

Do we have capability for storing images in SQL Server 2000?

If so, i need if there is any T-SQL script to save images in SQL Server database.

Any help would be greatly appreciated.

Thanks in advance.

Regards,
Franky

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-14 : 19:12:50

You can use the image data type. As far as code to insert them, you'll need to write an application to do that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-01-15 : 13:18:02

Tara,

Can't we do a T-sql script. This one of request from a client. I would like to know more details on this.

Thank u..

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-01-20 : 21:01:31

Hi All,

Am able to load the picture into sql server but am not able to load a image stored in network path. Is that a bug or flaw in "textcopy" in sql 2000?

use master
go
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DB2')
DROP DATABASE [DB2]
GO
CREATE DATABASE [DB2]
GO

use db2
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PIC_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PIC_TABLE]
GO

CREATE TABLE [dbo].[PIC_TABLE] (
[PIC_ID] [int] NULL ,
[PICTURE] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Use db2
Go
INSERT INTO PIC_TABLE
SELECT 101,''
UNION ALL
SELECT 102,''
UNION ALL
SELECT 103,''
go

USE DB2
GO
SELECT * FROM PIC_TABLE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_copyimage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_copyimage]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create procedure sp_copyimage (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@whereclause varchar (100),
@filename varchar (100),
@direction varchar (5))
AS
begin
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'C:\UTIL\textcopy /S ' + @srvname +
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W"' + @whereclause +' "'+
' /F"' + @filename +'"'+
' /' + @direction
PRINT @exec_str
EXEC master..xp_cmdshell @exec_str
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Run1
----
use db2
go
sp_copyimage @srvname = 'WIN-SERV\sql2000',
@login = 'sa',
@password = 'sa',
@dbname = 'db2',
@tbname = 'pic_table',
@colname = 'picture',
@whereclause = 'WHERE pic_id=103',
@filename = 'C:\UTIL\PICTURES\rose.jpg',
--'D:\MyPictures\flowers\rose.jpg'
@direction = 'I'

go

/*

TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'C:\UTIL\PICTURES\rose.jpg'.
NULL

*/

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-01-20 : 21:04:44

Err

use db2
go
sp_copyimage @srvname = 'WIN-SERV\sql2000',
@login = 'sa',
@password = 'sa',
@dbname = 'db2',
@tbname = 'pic_table',
@colname = 'picture',
@whereclause = 'WHERE pic_id=103',
@filename = '\\server01\MyPictures\flowers\rose.jpg',
--'D:\MyPictures\flowers\rose.jpg'
@direction = 'I'
go
/*
TEXTCOPY Version 1.0
DB-Library version 8.00.194
ERROR: Problem with file '\\server01\MyPictures\flowers\rose.jpg',
NULL
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources