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.
| 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 |
|
|
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
*/ |
|
|
|
|
|
|
|
|
Storing images in sql 2000