textcopy - 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

SQL Server Development (2000)

textcopy

Author

Topic

Rupa
Posting Yak Master

123 Posts

Posted - 2007-06-11 : 06:30:40

Hey all
I'm trying to use textcopy utility to insert pictures into a table. I've written the following procedure:

CREATE PROCEDURE [dbo].[sp_textcopy] (
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =

'textcopy /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
'" /F ' + @filename +
' /W "' + @whereclause +
' /' + @direction

EXEC master..xp_cmdshell 'dir "c:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe"'

EXEC sp_textcopy
'dbname,
'tbl_image',
'picture',
'C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\12.jpg',
'"where id = 12"',
'I'
GO

When I execute it, it gives me an error:
Server: Msg 201, Level 16, State 4, Procedure sp_textcopy, Line 0
Procedure 'sp_textcopy' expects parameter '@dbname', which was not supplied.

I haven't used this before so wld greatly appreciate any guidelines.

Many thanks,

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-11 : 06:42:38

You seem to be misssing closing quote while passing dbname parameter value:

EXEC sp_textcopy
'dbname',
'tbl_image',
'picture',
'C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\12.jpg',
'"where id = 12"',
'I'

Also, check this article on how to use this utility:
[url]http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2007-06-11 : 06:56:23

Thx Harsh

That was just a typo error when I was copying the script onto this forum. It did have the quote on the script. Looked at the link you gave and changed the code to:

CREATE PROCEDURE [dbo].[sp_textcopy] (
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =

'textcopy /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
'" /F ' + @filename +
' /W "' + @whereclause +
' /' + @direction

EXEC master..xp_cmdshell 'dir "c:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe"'

EXEC sp_textcopy
@dbname = 'dbname',
@tbname = 'tbl_image',
@colname = 'picture',
@filename = 'C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\12.jpg',
@whereclause = '"WHERE id = 12"',
@direction='I'
GO

Still get the same error: Server: Msg 201, Level 16, State 4, Procedure sp_textcopy, Line 0
Procedure 'sp_textcopy' expects parameter '@dbname', which was not supplied.

Any ideas?

Thanks

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-11 : 07:01:48

Simple..You are building the statement to be executed but executing a different statement:

Replace this:

EXEC master..xp_cmdshell 'dir "c:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe"'

With this:

EXEC master..xp_cmdshell @exec_str

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2007-06-11 : 07:05:52

Thank you Harsh..

Tried that but it gave the same error message again.

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2007-06-11 : 09:22:48

This wrked:

The procedure:

CREATE PROCEDURE [dbo].[sp_textcopy] (
@runpath varchar(100), -- textCopy Location
@srvr varchar(50), -- server to load
@db varchar(50), -- database to load
@tbl varchar(50), -- table to load/unload
@col varchar(50), -- column to load/unload
@whr varchar(200), -- where clause
@fil varchar(100), -- filename including path
@mod char(1)) -- I for Load into Sql , O FOR output from SQL
AS
declare @cmd varchar(1000)
set @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +
' /F ' + @fil + ' /' + @mod
exec Master..xp_cmdShell 'dir "c:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe"'
GO

In the query Analyzer:

EXEC sp_textcopy 'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe',
'whatever your server name',
'whatever your dbname',
'whatever your table name',
'whatever the column name',
'whatever criteria you have',
'wherever the picture is',
'I'

Thanks anyway Harsh...

Rupa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources