Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: textcopy issue

  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Unanswered: textcopy issue

    Hi guys,

    I run sql server 2000.

    I am trying to utilize this stored proc to upload images into my homepage_image table (I have initialized the hpg_image_desc column where the images will be stored)

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

    to help me upload images into my homepage_image table

    I am executing it like this :

    exec sp_textcopy @srvname = 'JRICP',
    @login = 'SA',
    @password = 'xxxxxxxx',
    @dbname = 'JRICP',
    @tbname = 'homepage_image',
    @colname = 'hpg_image_desc', --initialized already with '0x0'
    @filename = 'c:\photo_pilots.jpg',
    @whereclause = " WHERE hpg_image_id=3 ",
    @direction = 'I'

    I get this error : 'textcopy' is not recognized as an internal or external command, operable program or batch file. NULL.

    my textcopy.exe utility is stored in the
    'C:\Program Files\Microsoft SQL Server\MSSQL\Binn' folder

    When I go on the machine locally and run :


    C:\Program Files\Microsoft SQL Server\MSSQL\Binn>textcopy /S JRICP/U SA /P xxxxxxx /D JRICP /T homepage_image /C hpg_image_desc /W "where hpg_image_id=3" /F c:\photo_flags.jpg /I


    It works and gives me this result:

    TEXTCOPY Version 1.0
    DB-Library version 8.00.194
    Data copied into SQL Server image column from file 'c:\photo_flags.jpg'.



    So what could be wrong when I'm trying to run it through the proc. Should I move the executable to another location or what???? because it seems that sql server cant find my textcopy executable.....

    Thanks
    'Wale
    Last edited by Wale; 01-13-04 at 17:19.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First, I would take the password out of your post...

    Second, I think you need to supply the path in the sproc...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2002
    Posts
    162
    not to worry...the password and all other sensitive parameters are fake

  4. #4
    Join Date
    Mar 2002
    Posts
    162
    Originally posted by Brett Kaiser
    First, I would take the password out of your post...

    Second, I think you need to supply the path in the sproc...

    tried it...did not work. unless I am doing it wrongly, could u elaborate.

    I tried it like this :

    "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 =
    'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy /S ' + @srvname +
    ' /U ' + @login +
    ' /P ' + @password +
    ' /D ' + @dbname +
    ' /T ' + @tbname +
    ' /C ' + @colname +
    ' /W "' + @whereclause +
    '" /F ' + @filename +
    ' /' + @direction
    EXEC master..xp_cmdshell @exec_str "

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where are you trying to CREATE the Sproc from?

    QA?

    What's the error you're getting? Does it compile?

    The following compiles:

    Code:
    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
    BEGIN
    	DECLARE @exec_str varchar (255)
    	SELECT @exec_str =
    		  'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy /S ' + @srvname
    		+ ' /U '  + @login
    		+ ' /P '  + @password
    		+ ' /D '  + @dbname
    		+ ' /T '  + @tbname
    		+ ' /C '  + @colname
    		+ ' /W "' + @whereclause
    		+ '" /F ' + @filename
    		+ ' /'    + @direction
    
    	EXEC master..xp_cmdshell @exec_str 
    
    END
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2002
    Posts
    162
    theres no error. It complies. I am compiling it on the local Machine that the sql server install resides on

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Could it be that it can't handle the long file/folder names? Maybe it is expecting something like this:

    C:\Progra~1\Micros~1\MSSQL\Binn>textcopy ... blahblahblah

    Just a guess. Not my area of expertise.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2002
    Posts
    162
    hmmm, not to sure thats it.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Could it be that it can't handle the long file/folder names? Maybe it is expecting something like this:

    C:\Progra~1\Micros~1\MSSQL\Binn>textcopy ... blahblahblah

    Just a guess. Not my area of expertise.
    No I think he's on to something...path names with spaces are always f'ed up...you'd need to wrap it in double quotes (")

    Otherwise it thinks it a break in the parameter...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I just know it's caused me problems before, and I've never figured out the logic of when the long names are allowed and when they aren't so its usually one of the first fixes I try.

    You could also test this by moving your executable and your destination files to a new folder structure with names of 8 characters or fewer. If nothing else, simplifying the situation can help to whittle down the possible areas for error.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Mar 2002
    Posts
    162
    I tried it and all i get is an output that says "Null"
    but nothing happened.

    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
    BEGIN
    DECLARE @exec_str varchar (255)
    SELECT @exec_str = 'C:\Program~1\Micros~1\MSSQL\Binn\textcopy /S ' + @srvname
    + ' /U ' + @login
    + ' /P ' + @password
    + ' /D ' + @dbname
    + ' /T ' + @tbname
    + ' /C ' + @colname
    + ' /W "' + @whereclause
    + '" /F ' + @filename
    + ' /' + @direction

    EXEC master..xp_cmdshell @exec_str

    END
    GO

  12. #12
    Join Date
    Mar 2002
    Posts
    162
    Originally posted by blindman
    I just know it's caused me problems before, and I've never figured out the logic of when the long names are allowed and when they aren't so its usually one of the first fixes I try.

    You could also test this by moving your executable and your destination files to a new folder structure with names of 8 characters or fewer. If nothing else, simplifying the situation can help to whittle down the possible areas for error.
    Ill try that now.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    When xp_cmdshell invokes any command line utility, it executes in the security context of the service account of SQL Server. This context is subject to environment variable settings pertinent to its profile, which includes PATH. If you put double-quotes around the command that includes the path and the filename, you'll probably successfully execute it.

    Code:
    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 =
       '"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy" /S ' + 
       @srvname + ' /U ' + @login + ' /P ' + @password + ' /D ' + 
       @dbname + ' /T ' + @tbname + ' /C ' + @colname + ' /W "' + 
       @whereclause + '" /F ' + @filename + ' /' + @direction
       EXEC master..xp_cmdshell @exec_str "

  14. #14
    Join Date
    Mar 2002
    Posts
    162
    Originally posted by blindman
    I just know it's caused me problems before, and I've never figured out the logic of when the long names are allowed and when they aren't so its usually one of the first fixes I try.

    You could also test this by moving your executable and your destination files to a new folder structure with names of 8 characters or fewer. If nothing else, simplifying the situation can help to whittle down the possible areas for error.
    yaayy!! it works!

    Thanks blind man. Thanks Brett!

    I just moved the executable to the C:\ drive

  15. #15
    Join Date
    Mar 2002
    Posts
    162
    Originally posted by rdjabarov
    When xp_cmdshell invokes any command line utility, it executes in the security context of the service account of SQL Server. This context is subject to environment variable settings pertinent to its profile, which includes PATH. If you put double-quotes around the command that includes the path and the filename, you'll probably successfully execute it.

    Code:
    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 =
       '"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy" /S ' + 
       @srvname + ' /U ' + @login + ' /P ' + @password + ' /D ' + 
       @dbname + ' /T ' + @tbname + ' /C ' + @colname + ' /W "' + 
       @whereclause + '" /F ' + @filename + ' /' + @direction
       EXEC master..xp_cmdshell @exec_str "
    I had tried that and it did not work.

    I just moved the executable to my C:\ and it worked perfectly

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •