Solved

Insert image to MSSQL using textcopy

Posted on 2004-03-23
10
1,082 Views
Last Modified: 2007-12-19
Ok, I know this question has been asked dozen of time. I did do some research but I encountered some difficulties. Please help me and I promise all the reward points that I have.

Below is my procedure :
1) I created a stored procedure with the following source from http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm

CREATE PROCEDURE sp_textcopy (
  @srvname     varchar (30),
  @login       varchar (30),
  @password    varchar (30),
  @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 /S ' + @srvname +
         ' /U ' + @login +
         ' /P ' + @password +
         ' /D ' + @dbname +
         ' /T ' + @tbname +
         ' /C ' + @colname +
         ' /W "' + @whereclause +
         '" /F ' + @filename +
         ' /' + @direction
EXEC master..xp_cmdshell @exec_str

2) Then I go to SQL Query Analyser and select the database where my tables and the stored procedure located.

3) My database is called "paramount", table is 'movie' and has a field called "image" and its type is "image".

4) I issued this command :
sp_textcopy @srvname = 'local',
            @login = '', @password = '',
            @dbname = 'paramountSilver', @tbname = 'movie',
            @colname = 'image',
            @filename = 'd:\paramount\antz.jpg',
            @whereclause = "title = 'antz'",
            @direction = 'I'

5) I got this message from the output panel
'textcopy' is not recognized as an internal or external command,operable program or batch file.


Please tell me what is wrong and how to rectify the error.

Thank you very much.
0
Comment
Question by:i_am_diablo2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 2

Expert Comment

by:dhenson
ID: 10665143
The path that the textcopy.exe executable is in must not be in the %Path system variable.  Either preface the textcopy statement with the location of the file on your server i.e.

'c:\mssql7\binn\textcopy /S ' + @srvname +

or

Change your system path such that the executable would be found.

dhenson
0
 

Author Comment

by:i_am_diablo2000
ID: 10665300
Hi, Thanks for the reply.

I guess that is the answer. However, I need to hardcode the full path in the stored procedure.

And my path is "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"

As you see, there is a space in between the path and I encoutered error again while executing the statement.

'C:\Program' is not recognized as an internal or external command,


 How should I escape the white space in the path?

Come on, I want to give you the 200 points.
0
 

Author Comment

by:i_am_diablo2000
ID: 10665338
Hello dhenson,

I have another problem. I fixed the above problem by copying the "textcopy.exe" file to a directory without space and it works.

But I encountered the following error when executing.

SQL Server Message 18452: Login failed for user 'null'. Reason: Not associated with a trusted SQL Server connection.

In fact, my database does not set any userid or password so I am not sure what data should I provide in this case.

Could you please enlighten me? Thanks
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 2

Expert Comment

by:dhenson
ID: 10669759
c:\progra~1\micros~1\mssql\binn (assuming there are no other folders that start with "microsoft" in your program files folder.

a better way would be:

'c:"\program files\microsoft sql server\mssql\binn\textcopy.exe" /S ' + @srvname +

Looking up the password syntax...

0
 
LVL 2

Expert Comment

by:dhenson
ID: 10669833
When you say:
"In fact, my database does not set any userid or password so I am not sure what data should I provide in this case."

You must be authenticating using Windows Authenitication.  Your passing your windows credentials to Query analyser when you connect.

Create a SQL user account and assign it to the paramountSilver Database.  Then Right click your Movie table and grant the new account 'Insert'.

0
 

Author Comment

by:i_am_diablo2000
ID: 10675093
Hello,

Thanks for your answer. I will find it out later. Stay tuned for the points.
0
 

Author Comment

by:i_am_diablo2000
ID: 10685088
dhenson

You are right. I am using Windows Authenitication.

So I have created a new login user from Enterprise Manager->Security->Logins->New Logins.

I also set the database to my paramount.

But yet, when I the script from query analyser, I still got the same error message :

SQL Server Message 18452: Login failed for user 'test'. Reason: Not associated with a trusted SQL Server connection.

What have I overlooked?
Please help.
Many thanks



0
 
LVL 2

Accepted Solution

by:
dhenson earned 200 total points
ID: 10689593
http://www.visual-basic-data-mining.net/Forum/showpost.aspx?PostID=251
http://www.banmanpro.com/support/sql2k.asp

It's possible that the properties on your database isn't allowing sql server registration using a method other than 'Windows Authentication'.

see the above links... let me know if that was it.
0
 

Author Comment

by:i_am_diablo2000
ID: 10701286
sp_textcopy @srvname = 'DIABLO',  @login = 'sa', @password = 'sa',    @dbname = 'paramountSilver', @tbname = 'movie',
            @colname = 'image',
            @filename = 'd:\paramount\antz.jpg',
            @whereclause = "title = 'antz'",
            @direction = 'I'

dhenson, thank you for your answer. I tried your second method and it seems login problem is solved. But I got another error.

SQL Server 'DIABLO' Message 170: Line 1: Incorrect syntax near '='. (Concerning line 1)
DB-Library Error 10007: General SQL Server error: Check messages from the SQL Server.

I am desperate. Do you have any idea?



0
 

Author Comment

by:i_am_diablo2000
ID: 10701793
dhenson,

Your solution works. Thanks. Now I give you the points.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question