i_am_diablo2000
asked on
Insert image to MSSQL using textcopy
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.
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.
ASKER
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.
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.
ASKER
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
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
c:\progra~1\micros~1\mssql
a better way would be:
'c:"\program files\microsoft sql server\mssql\binn\textcopy
Looking up the password syntax...
a better way would be:
'c:"\program files\microsoft sql server\mssql\binn\textcopy
Looking up the password syntax...
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'.
"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'.
ASKER
Hello,
Thanks for your answer. I will find it out later. Stay tuned for the points.
Thanks for your answer. I will find it out later. Stay tuned for the points.
ASKER
dhenson
You are right. I am using Windows Authenitication.
So I have created a new login user from Enterprise Manager->Security->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
You are right. I am using Windows Authenitication.
So I have created a new login user from Enterprise Manager->Security->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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
@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?
ASKER
dhenson,
Your solution works. Thanks. Now I give you the points.
Your solution works. Thanks. Now I give you the points.
'c:\mssql7\binn\textcopy /S ' + @srvname +
or
Change your system path such that the executable would be found.
dhenson