January 14, 2011 at 4:53 pm
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
January 20, 2011 at 7:08 pm
I tried to do it myself. Am succesful in loading images which are local computers but am not able to load if the images are in a network folder. it shows up error!!! Is that a bug in textcopy.exe
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
*/
SELECT * FROM DB2..PIC_TABLE
Run-2
-----
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
*/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply