xp_cmdshell and Textcopy problem.

Question

Post reply

xp_cmdshell and Textcopy problem.

Jeff Trusty

SSC Journeyman

Points: 78
More actions
September 12, 2005 at 4:08 pm

#91182

I have looked at other threads and gotten the script for the following stored procedure (slightly modified).
create PROCEDURE csp_textcopy (
@srvname varchar (30) = 'servername',
@login varchar (30) = 'SA',
@password varchar (30) = 'SA_password',
@dbname varchar (30) = 'database',
@tbname varchar (30) = 'pdfinvoice',
@colname varchar (30) = 'pdf',
@filename varchar (500) = 'UNC_to_file',
@whereclause varchar (40) = 'where InvoiceNumber = 40',
@direction char(1) = 'I')
AS
begin
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'"textcopy" /S ' + @srvname +
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /' + @direction +
' /W "' + @whereclause +
'" /F ' + @filename
print @exec_str
EXEC master..xp_cmdshell @exec_str
end
GO
I also have a PDFInvoice table with the following structure:
create table pdfinvoice (InvoiceNumber int, pdf image)
insert into pdfinvoice values (40,'') -- cannot use NULL in pdf field
when I call the procedure, it generates and executes the following and I get an error stating: "The system cannot find the path specified."
exec master..xp_cmdshell '"textcopy" /S slc8-3550478 /U SA /P password /D pubs /T pdfinvoice /C pdf /I /W "where InvoiceNumber = 40" /F c:\40.pdf'
However, If I paste the text of what is passed into xp_cmdshell into a command prompt it loads the data into the table just fine.
I am running SQL 2k build 8.00.760
Any idea's on why the command works from a command prompt, but not when passed to xp_cmdshell?
Thanks for any help.
Jeff

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Meghana SSC Eights! Points: 830 More actions · Answer 1

The filename 'UNC_to_file' might not be present in the folder from where you are executing the procedure .

Give an absolute path in the Filename field and see if the procedure executes fine

Regards,

Meghana

Bob Boykin SSC Rookie Points: 42 More actions · Answer 2

There might be a problem passing the quote charcter

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 3

The environment of something running under xp_cmdshell is not identical to the environment you see in a command shell. Run "SET" in a command shell, and compare the output to "xp_cmdshell 'SET'".

The obvious difference may be that your command shell is running on your computer, and xp_cmdshell is running on a server.

Jeff Trusty SSC Journeyman Points: 78 More actions · Answer 4

Thanks for the efforts...

I am running connecting to my local instance, so I am executing from the same machine not local/server.

I have shared my c:\data folder that contains the 40.pdf. I have tried the procedure with both "c:\data\40.pdf" and "\\machine\data\40.pdf" for the "UNC_to_File" parameter.

On the environment stuff, The only differences I see are that in Query Analyzer when xp_cmdshell 'set' is run I have the following:

TEMP=C:\WINDOWS\TEMP

TMP=C:\WINDOWS\TEMP

USERPROFILE=C:\Documents and Settings\LocalService

In a cmd prompt they are:

TEMP=C:\DOCUME~1\jtrusty\LOCALS~1\Temp

TMP=C:\DOCUME~1\jtrusty\LOCALS~1\Temp

USERPROFILE=C:\Documents and Settings\jtrusty

Still hoping someone can help me.

Thanks!

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 5

The next question would be about the current directory the command is run under. When I run "xp_cmdshell 'CD'" on a server, the result is C:\WINDOWS\system32. Was that the current directory for the command prompt window where your command worked, or is textcopy in a folder in the PATH list?

Your command may need to have a full path specification for textcopy.

Jeff Trusty SSC Journeyman Points: 78 More actions · Answer 6

Scott, you rock! I had to put the full short-file-name path to textcopy.exe in and it finally worked.

Thanks ALL that tried to help.