August 28, 2007 at 11:53 am
I don't know if this is possible.
I am trying to build an stored procedure that will insert a PDF on an image column just with SQL, is it possible?
The parameter on my stored procedure will be the path of the file.
I am trying to avoid the use of coding in other software like VB or C#, just SQL.
Thanks for your responses.
August 28, 2007 at 12:17 pm
Cannot be done with SQL itself. You will have to have a tier to stream the data in.
August 28, 2007 at 1:42 pm
In the future, when your server is upgraded to SQL Server 2005 or later, you could use OPENROWSET(BULK ....
Andras
August 29, 2007 at 9:14 am
We did it in SQL2000 like this (you need system admin permissions):
EXEC usp_TEXTCOPY
@SRVNAME = 'ServerName',
@LOGIN = 'Login',
@PASSWORD = 'password',
@DBNAME = 'DatabaseName',
@TBNAME = 'TableName',
@COLNAME = 'ColumnName',
@FILENAME = 'Full UNC starting with \\',
@WHERECLAUSE = " WHERE TableKey = 12345", --specific row in the table
@DIRECTION = 'I'
-------------------------------------------------------------------------------------------
-- You can use the following stored procedure to simplify the using of textcopy.exe utility:
-- Created by Mike Fritz of Countrywide fame
CREATE PROCEDURE usp_TEXTCOPY (
@SRVNAME VARCHAR (30), -- Server Name
@LOGIN VARCHAR (30), -- Login Name
@PASSWORD VARCHAR (30), -- Password
@DBNAME VARCHAR (30), -- Database Name
@TBNAME VARCHAR (30), -- Table Name
@COLNAME VARCHAR (30), -- Column Name
@FILENAME VARCHAR (255), -- File Name
@WHERECLAUSE VARCHAR (40), -- Where Clause (See notes below)
@DIRECTION CHAR(1)) -- Direction (ie: (I)nput or (O)utput)
AS
DECLARE @EXEC_STR VARCHAR (255)
SELECT @EXEC_STR = 'TEXTCOPY /S ' + @SRVNAME +
' /U ' + @login +
' /P ' + @PASSWORD +
' /D ' + @DBNAME +
' /T ' + @TBNAME +
' /C ' + @COLNAME +
' /W "' + @WHERECLAUSE +
'" /F ' + @FILENAME +
' /' + @DIRECTION
EXEC MASTER..XP_CMDSHELL @EXEC_STR
/* This is the example to copy image into SQL Server database pubs, table pub_info,
column name logo from picture.bmp file where pub_id='0736':
sp_textcopy @srvname = 'ServerName',
@login = 'Login',
@password = 'Password',
@dbname = 'pubs',
@tbname = 'pub_info',
@colname = 'logo',
@filename = 'c:\picture.bmp',
@whereclause = " WHERE pub_id='0736' ",
@direction = 'I'
By the way, you should insert something into text/image column BEFORE copying text/image into it.
This value should not be null. You should write (for example):
INSERT INTO [dbo].[images] VALUES ('1', 0x0, null) before you run usp_TEXTCOPY
instead of:
INSERT INTO [dbo].[images] VALUES ('1', null, null)
Otherwise, you will get the following error message:
ERROR: Text or image pointer and timestamp retrieval failed.
-----------------------------------------------------------------------------------------------------------
Copies a single text or image value into or out of SQL Server. The value
is a specified text or image 'column' of a single row (specified by the
"where clause") of the specified 'table'.
If the direction is IN (/I) then the data from the specified 'file' is
copied into SQL Server, replacing the existing text or image value. If the
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.
TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]
/S sqlserver The SQL Server to connect to. If 'sqlserver' is not
specified, the local SQL Server is used.
/U login The login to connect with. If 'login' is not specified,
a trusted connection will be used.
/P password The password for 'login'. If 'password' is not
specified, a NULL password will be used.
/D database The database that contains the table with the text or
image data. If 'database' is not specified, the default
database of 'login' is used.
/T table The table that contains the text or image value.
/C column The text or image column of 'table'.
/W "where clause" A complete where clause (including the WHERE keyword)
that specifies a single row of 'table'.
/F file The file name.
/I Copy text or image value into SQL Server from 'file'.
/O Copy text or image value out of SQL Server into 'file'.
/K chunksize Size of the data transfer buffer in bytes. Minimum
value is 1024 bytes, default value is 4096 bytes.
/Z Display debug information while running.
/? Display this usage information and exit.
You will be prompted for any required options you did not specify.
*/
GO
August 30, 2007 at 6:53 am
Thanks all for your reply, I will try this last one.
August 31, 2007 at 5:01 am
We did this too, but automated it in a SP so that we could pass the filename etc.
it's a solution, but I prefer to stream them in wherever possible.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply