Posted by Connie on 03/07/07 22:27

On Mar 7, 4:24 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Connie (csaw...@rwbaird.com) writes:
> > exec sp_textcopy @srvname = 'MILNPPRODSQL',
> >...
> > This script runs with no issue in our Dev environment (which has a
> > restore of our production db) but when I run in production I get the
> > following error:
>
> > The system cannot find the path specified.
>
> > I can't figure out what path it is erroring on?? Both servers are
> > Windows 2003. The environmental variables on each server is
> > identical. Only difference I can think of is that our production
> > server is a clustered sql server and our dev server is not....
>
> We don't know what is in that sp_textcopy, but apparently textcopy is
> on in the path on the production server. You may have to modify this
> sp_textcopy, so that it does not assume that textcopy is in the path.
>
> And, of course, if the production server is SQL 2005, then there is
> no textcopy available.
>
> Overall, I am not very fond of the solution of calling textcopy from
> xp_cmdshell. If this is for an Agent job, I think it would be better
> to do with an Active-X task instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Erland, The reason I am doing this is to extract a blob from a sql
server table and store it into a location on our server. Here is what
is in the sp_textcopy:
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:\Progra~1\Micros~1\MSSQL\Binn\textcopy.exe /S ' + @srvname
+
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "' + @whereclause +
'" /F ' + @filename +
' /' + @direction
EXEC master..xp_cmdshell @exec_str
GO

I believe that I must be having some sort of right issue on the
Production server where I am now trying to run this. It works
perfectly in Development.

[Back to original message]